Unit-III

Relational Algebra and Database Programming: Relational

Algebra, Basic Operations, Relational calculus: Tuple
Calculus, Domain Calculus,
Introduction to SQL, Characteristics and advantages of SQL,
SQL Data Types, DDL Commands, DCL Commands. SQL
Queries: DML Queries with Select Query Clauses, Creating,
Modifying, Deleting. Views: Creating, Dropping, Updating,
Indexes, SQL DML Queries, Set Operations, Predicates and
Joins, Set membership, Grouping and Aggregation,
Aggregate Functions, Nested Queries
DATABASE MANAGEMENT 1
SYSTEMS
Relational Algebra

DATABASE MANAGEMENT 2
SYSTEMS
 Relational Algebra

▪ Basic Relational Algebra Operations:

⮚Select σ
▪ What is “algebra ?
⮚Project ∏
▪ Mathematical model consisting of: ⮚Union ∪
▪ Operands --- Variables or values; ⮚Set Difference (or Subtract or minus) –
▪ Operators --- Symbols denoting procedures
that construct new values from a given values ⮚Cartesian Product X

▪Relational Algebra : is an algebra whose ⮚Natural Join

operands are relations and operators are
designed to do the most commons things that
we need to do with relations

DATABASE MANAGEMENT SYSTEMS 3

 Relational Algebra: Select Operation

Example of selection:
▪ Notation: σ p(r)
Account(account_number, branch_name,balance)
p is called the selection predicate σ branch-name=“Perryridge”(account)
⮚Defined as:
A B C D A B C D
σp(r) = {t | t ∈r and p(t)} α 1 7 α 1 7
α α
⮚ Where p is a formula in propositional
calculus consisting of terms connected by : ∧ α β 5 7 β β 23 10
(and), ∨(or), ¬ (not)
Each term is one of: β β 12 3
<attribute> op <attribute> or <constant> σA=B ^ D > 5 (r)
β β 23 10
where op is one of: =, ≠, >, ≥. <. ≤
Relation r
DATABASE MANAGEMENT SYSTEMS 4
 Relational Algebra: Project Operation

▪ Notation: ∏A1, A2, …, Ak (r) Example of Project Operation :

where A1, A2 are attribute names and r is a relation. A B C A C

⮚The result is defined as the relation of k columns α 10 1 1
α
obtained by erasing the columns that are not listed A C
⮚Duplicate rows removed from result, since relations α 20 1
α 1 α 1
are sets
=
E.g. to eliminate the branch-name attribute of β 30 1 β 1 β 1
account
∏account-number, balance (account) 2
β 40 2 β 2
β
⮚If relation Account contains 50 tuples, how many
tuples contains ∏account-number, balance (account) ? Relation r ∏A,C (r)
That is, the projection of a relation on a set of attributes is
a set of tuples

DATABASE MANAGEMENT SYSTEMS 5

 Relational Algebra: Union Operation

▪ Notation: r ∪s Example of Union:

A B
⮚Consider relational schemas: A B
α 1
Depositor(customer_name, account_number) α 1
A B
Borrower(customer_name, loan_number) α 2
α 2 α 2
⮚For r ∪ s to be valid. β 1
β 1 β 3
1. r, s must have the same number of attributes β 3
[Link] attribute domains must be compatible (e.g., 2nd
column of r deals with the same type of values as does Relation r Relation s
the 2nd column of s) r ∪s
⮚ Find all customers with either an account or a loan
∏customer-name (depositor) ∪ ∏customer-name (borrower)

DATABASE MANAGEMENT SYSTEMS 6

 Relational Algebra: Set Difference Operation

Example of Set Difference:

▪ Notation : r – s
A B
Set differences must be taken between
α 1
compatible relations. A B A B
▪ r and s must have the same number 2 1
α 2 α α
of attributes
▪ attribute domains of r and s must be β 1 β 3 β 1
compatible
Relation r Relation s r-s

DATABASE MANAGEMENT SYSTEMS 7

 Relational Algebra: Cartesian Product Operation

▪ Notation : rxs A B C D E
C D E
α 1 α 10 a
α 10 a
α 1 β 10 a
Assume that attributes of r(R) and s(S) are disjoint.
(That is, R ∩ S = ∅). β 10 a
A B r ⅹs α 1 β 20 b
If attributes of r(R) and s(S) are not disjoint, then 1
renaming must be used. α β 20 b
α 1 γ 10 b
β 2 γ 10 B β 2 α 10 a

β 2 β 10 a
Relation r
Relation s β 2 β 20 b
β 2 γ 10 b

DATABASE MANAGEMENT SYSTEMS 8

 Relational Algebra: Natural Join Operation

▪ Notation : r ⋈s
▪ We can perform a Natural Join only if there is
at least one common attribute that exists
between two relations
▪ The common attributes must have the same
name and domain. A B A C r ⋈s A B C
▪ Natural join acts on those matching attributes
α 1 α 10 α 1 10
where the values of attributes in both the
relations are same. β 30 β 2 30
β 2
▪ It avoids duplication of columns while
providing the result as compared to other
joins/cartesian-product. Relation r Relation s

DATABASE MANAGEMENT SYSTEMS 9

Relational Algebra Operators

DATABASE MANAGEMENT 10
SYSTEMS
 Database Languages and
Programming

School of Computer Engineering and technology

8/6/2020 DBMS 1
1
 Syllabus

• Introduction to SQL: Characteristics and advantages of SQL, SQL Data Types

• DDL Commands, DCL Commands.
• SQL Queries: DML Queries with Select Query Clauses, Creating, Modifying,
Deleting.
• Views: Creating, Dropping, Updating, Indexes,
• Set Operations, Predicates and Joins, Set membership, Grouping and
Aggregation, Aggregate Functions, Nested Queries

8/6/2020 DBMS 1
2
 Characteristics of SQL

 SQL stands for Structured Query Language

 SQL is an ANSI and ISO standard computer language for creating and
manipulating databases.
 SQL allows the user to create, update, delete, and retrieve data from a database.
 SQL is very simple and easy to learn.
 SQL works with database programs like DB2, Oracle, MS Access, Sybase,
MySQL, MS SQL Sever etc.
 SQL is a declarative language, not a procedural language.
 All keywords of SQL are case insensitive.

DBMS 1
8/6/2020
3
 Advantages of SQL
▪High Speed: SQL Queries can be used to retrieve large amounts of records from a
database quickly and efficiently.

▪Well Defined Standards Exist: SQL databases use long-established standard,

which is being adopted by ANSI & ISO. Non-SQL databases do not adhere to any
clear standard.

▪No Coding Required: Using standard SQL it is easier to manage database systems
without having to write substantial amount of code.

▪ Easy to learn and understand

▪ Portable: SQL can be run on any platform, Databases using SQL can be moved
8/6/2020 from a device to another without anDyBpMroSblems. 4
 SQL Data Types and Literals

char(n). Fixed length character string, with user-specified length n.

varchar(n). Variable length character strings, with user-specified maximum length n.
Boolean. Accepts value true or false.
int. Integer (a finite subset of the integers that is machine-dependent).
smallint. Small integer (a machine-dependent subset of the integer domain type).
decimal(p,d). Fixed point number, with user-specified precision of p digits, with d digits to the
right of decimal point. (ex., decimal(3,1), allows 44.5 to be stored exactly, but not 444.5 or
0.32)
Double(p,d). Floating point and double-precision floating point numbers, with machine-
dependent precision. Decimal precision can go to 53 places for a DOUBLE.
float(p,d). Floating point number, with user-specified precision of at least n digits. Decimal
precision can go to 24 places for a FLOAT.

8/6/2020 DBMS 15
 SQL language statements

 Data Definition Language (DDL)

 Data Manipulation Language
(DML)
 Data Control Language (DCL)
 Transaction Control Language
(TCL)

8/6/2020 DBMS 16
 Data Definition Language (DDL)

 The SQL data-definition language (DDL) allows

 Database tables to be created or deleted
 Define indexes (keys)
 Specify links between tables
 Impose Integrity constraints between database tables
• Some of the most commonly used DDL statements in SQL are
– CREATE TABLE : creates a new database table.
– ALTER TABLE : Alters(changes) a database table.
– DROP TABLE : Deletes a database table.
– RENAME TABLE : Renames a database table.
– TRUNCATE TABLE : Deletes all the records in the table.
8/6/2020 DBMS 17
 Create Table Construct

 SQL relation is defined using the create table command:

create table r (A1 D1, A2 D2, ..., An Dn,
(integrity-constraint1),
...,
(integrity-constraintk))
• Example:
create table instructor (
ID char(5),
name varchar(20),
dept_name varchar(20),
salary decimal(8,2))

8/6/2020 DBMS 18
 Integrity Constraints

 Constraints are the rules enforced on the

data columns of a table. These are used to
limit the type of data that can go into a table.
This ensures the accuracy and reliability of
the data in the database.
 Constraints could be either column level or
table level.
1. Column Level: Column level constraints are
applied to only one column.
2. Table Level: Table level constraints are
applied to the whole table.
 There are 3 types of Integrity Constraints:

8/6/2020 DBMS 19
 Domain Integrity Constraints

 Domain Integrity constraints can be defined as the definition of a valid set of values
for an attribute.
1. NOT NULL Constraint:
2. Unique Constraint :
3. Default Constraint :
4. Check Constraint :

1. NOT NULL : Roll_No Name

• Ensures that a column cannot have NULL value. 1 ABC
• E.g. Roll_no int not null, NULL value is not 2 XYZ
allowed
Name varchar(20) AAA
8/6/2020 DBMS 20
 Domain Integrity Constraints (Cont..)

2. Unique Constraint :
• Ensures that all values in a column are different.
• E.g. Emp_ID varchar(20) not null unique,
Emp_ ID Name Salary
Not allowed
as Emp_ID E101 ABC 20000
has unique E102 XYZ 20000
constraint
E102 PQR 18000
Roll_No Name Marks
3. Default Constraint:
•
1
Provides a default value for a column when none is s pecified. ABC NULL
• E.g. Marks int default NULL, 2 XYZ NULL
8/6/2020 DBMS 21
 Domain Integrity Constraints (Cont..)
4. Check Constraint:
• The CHECK constraint ensures that all the values in a column satisfies certain conditions.

Roll_No Name Age

CREATE TABLE student ( 1 ABC 18 Domain Constraint
Roll_No int NOT NULL,
Name varchar(255) NOT NULL, 2 XYZ 20 (Age>=18)
Age int CHECK (Age>=18)
);
3 PQR 25
Not Allowed
4 MNP 10

8/6/2020 DBMS 22
 Entity Integrity Constraints
 Primary Key constraint:
• states that primary key value can't be null.
• Because primary key value is used to identify individual rows in relation and
if the primary key has a null value, then we can't identify those rows.
• A table can contain a null value other than the primary key field.
Primary Key
Emp_ ID Name Salary
Not allowed E101 ABC 20000
as Emp_ID
is a primary E102 XYZ 20000
key. PQR 18000

8/6/2020 DBMS 23
 Referential Integrity Constraints
 Foreign Key constraint:
• A foreign key is a key
used to link two tables
together.
• A Foreign Key is a
column or a combination
of columns whose values
match a Primary Key in a
different table.
• The relationship between
2 tables matches the
Primary Key in one of
the tables with a Foreign
Key in the second table.
8/6/2020 DBMS 24
 Referential Integrity Constraints(Cont..)
 There are two type foreign key Syntax:
integrity constraints: CREATE TABLE child_table(
1. cascade delete
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ], ...
2. cascade update
CONSTRAINT fk_name
1. Cascade Delete :
FOREIGN KEY (child_col1, child_col2, ...
A foreign key with cascade delete
child_col_n)
means that if a record in the parent
REFERENCES parent_table (parent_col1, parent_col2,
table is deleted, then the
corresponding records in the child ... parent_col_n)
table will automatically be deleted. ON DELETE CASCADE
[ ON DELETE { NO ACTION | CASCADE | SET
NULL | SET DEFAULT } ] );
8/6/2020 DBMS 25
 Referential Integrity Constraints(Cont..)

2. Cascade Update : Syntax:

A foreign key with cascade update CREATE TABLE child_table(
means that if a record in the parent column1 datatype [ NULL | NOT NULL ],
table is updated, then the column2 datatype [ NULL | NOT NULL ], ...
corresponding records in the child CONSTRAINT fk_name
table will automatically be updated.
FOREIGN KEY (child_col1, child_col2, ... child_col_n)
REFERENCES parent_table (parent_col1, parent_col2,
 DROP a FOREIGN KEY
... parent_col_n)
Constraint
ON UPDATE CASCADE
ALTER TABLE ORDERS [ ON UPDATE { NO ACTION | CASCADE | SET
DROP FOREIGN KEY; NULL | SET DEFAULT } ] );

8/6/2020 DBMS 26
 Integrity Constraints in Create Table

 not null
 primary key (A1, ..., An )
 Foreign key (Am, ..., An ) references r

Example:

create table instructor (

ID char(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2),
primary key (ID),
foreign key (dept_name) references department);

 primary key declaration on an attribute automatically ensures

not null.

8/6/2020 DBMS 27
 Alter Command

Alter command is used for altering the table structure, such as,
1. to add a column to existing table
2. to rename any existing column
3. to change datatype of any column or to modify its size.
4. to drop a column from the table.
5. To add a column
alter table <table name> add <column name> datatype
• All exiting tuples in the relation are assigned null as the value for the new
attribute, if default value is not specified.
• E.g. ALTER TABLE Customers
ADD Email varchar(255);

8/6/2020 DBMS 28
 Alter Command (Cont..)
• By setting default value for new column

ALTER TABLE table_name ADD(column-name1 datatype1

DEFAULT some_value);

E.g. ALTER TABLE student ADD(

dob DATE DEFAULT ‘2020-07-10‘ );

2. To modify a column datatype/size.

ALTER TABLE table_name modify Column(

column_name datatype);

8/6/2020 DBMS 29
 Alter Command (Cont..)

E.g. ALTER TABLE student MODIFY Column(

address varchar(300));

3. To Rename a Column

ALTER TABLE table_name RENAME

old_column_name TO new_column_name;

E.g. ALTER TABLE student RENAME

address TO location;

8/6/2020 DBMS 30
 Alter Command (Cont..)

4. To drop a column
• Dropping of attributes not supported by many databases.

ALTER TABLE table_name DROP Column(

column_name);

• E.g. ALTER TABLE Customers

DROP COLUMN Email;

8/6/2020 DBMS 31
 Drop Command

DROP TABLE command is used to drop an existing table in a database.

DROP TABLE table_name;

E.g. DROP TABLE Customers;

8/6/2020 DBMS 32
 Rename Command

RENAME command is used to rename a table.

RENAME TABLE {tbl_name} TO {new_tbl_name};

E.g. RENAME TABLE Customers TO Customers _new;

8/6/2020 DBMS 33
 Truncate Command

TRUNCATE TABLE command is used to delete complete data from an existing

table.

TRUNCATE TABLE table_name;

E.g. TRUNCATE TABLE Customers;

8/6/2020 DBMS 34
 Data Control Language (DCL)

• DCL commands control the level of access that users have on database objects.
• GRANT – provides access privileges to the users on the database objects. The
privileges could be select, delete, update and insert on the tables and views. On the
procedures, functions and packages it gives select and execute privileges.

 In DCL we have two commands,

1. GRANT: Used to provide any user access privileges or other privileges for the
database.
2. REVOKE: Used to take back permissions from any user.

8/6/2020 DBMS 35
 GRANT Command

 Syntax for the GRANT command :

GRANT privilege_name ON object_name
TO {user_name | PUBLIC | role_name} [with GRANT option];

 Allow User to create table:

• To allow a user to create tables in the database, we can use the below command,
GRANT CREATE TABLE TO username;
 Grant Select privileges to user on customer table:
GRANT SELECT ON customer TO username;
 Grant permission to drop any table:
GRANT DROP ANY TABLE TO username;
 To GRANT ALL privileges to a user
GRANT ALL PRIVILEGES ON database_name TO username
8/6/2020 DBMS 36
 DCL Example

• mysql> CREATE USER 'finley'@'localhost‘ IDENTIFIED BY 'password';

• mysql> GRANT ALL ON *.* TO 'finley'@'localhost‘
• mysql> SHOW GRANTS FOR 'finley'@'localhost';
• From cmd prompt change to folder
C:\Program Files\MySQL\MySQL Server 8.0\bin> mysql -u finley -p
Enter password: ******** (password)
• mysql> create database a;
• mysql> use a;
• mysql> create table abc(a1 int);

8/6/2020 DBMS 37
 REVOKE Command

 Syntax for the REVOKE command:

REVOKE privilege_name ON object_name
FROM {User_name | PUBLIC | Role_name}

 To take back Permissions from user

REVOKE CREATE TABLE FROM username;
 Revoke SELECT privilege on employee table from user1.
REVOKE SELECT ON employee FROM user1;

8/6/2020 DBMS 38
 Continued…….
• From Root
•

• mysql> REVOKE ALL ON *.* FROM 'finley'@'localhost';

• mysql> REVOKE CREATE,DROP ON *.* FROM 'finley'@'localhost';

8/6/2020 DBMS 39
 Transaction Control Language (TCL)

 TCL commands are used to manage transactions in the database.

 These are used to manage the changes made to the data in a table by DML
statements.
1) Commit
2) Rollback
3) Savepoint

8/6/2020 DBMS 40
 TCL (Cont..)

1) Commit Command:
 used to permanently save any transaction into the database.
 When we use any DML command like INSERT, UPDATE or DELETE, the
changes made by these commands are not permanent, until the current session
is closed, the changes made by these commands can be rolled back.
 To avoid that, we use the COMMIT command to mark the changes as
permanent.
 Syntax:
COMMIT;

8/6/2020 DBMS 41
 TCL (Cont..)

2) ROLLBACK Command:
 restores the database to last committed state.
 Can be used to cancel the last update made to the database, if those changes are
not committed using the COMMIT command.
 Syntax:
ROLLBACK TO savepoint_name;

3) SAVEPOINT command:
 used to temporarily save a transaction so that we can rollback to that point
whenever required.
 Syntax:
8/6/2020
SAVEPOINT savepoint_name;
DBMS 42
 Data Manipulation Language (DML)

DML commands are used to make modifications of the Database like,

 Insertion of new tuples into a given relation

 Deletion of tuples from a given relation.
 Updation of values in some tuples in a given relation

8/6/2020 DBMS 43
 INSERT Query

 Add a new tuple to course

insert into course
values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);

 or equivalently
insert into course (course_id, title, dept_name, credits)
values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);

 Add a new tuple to student with tot_creds set to null

insert into student
values (’3003’, ’Green’, ’Finance’, null);
8/6/2020 DBMS 44
 DELETE Query

 Delete all instructors from the Finance department

delete from instructor
where dept_name= ’Finance’;

 Delete all tuples in the student relation.

delete from student;

8/6/2020 DBMS 45
 UPDATE Query

 Increase salaries of instructors whose salary is over $100,000 by 3%, and all others by
a 5%
• Write two update statements:

update instructor
set salary = salary * 0.03
where salary > 100000;

update instructor
set salary = salary * 0.05
where salary <= 100000;

8/6/2020 36
DBMS
 SELECT Query

 The SELECT statement is used to select data from a database tables.

Select ------ Mandatory Clause

From
Where
Group by Optional Clauses(Use as per need)
Having
Order by
E.g. SELECT * FROM Student;

– The result of an SQL query is a relation.

8/6/2020 DBMS 47
 SELECT Query (Cont..)

 An attribute can be a literal with from clause

select ‘A’ from instructor

– Result is a table with one column and N rows (number of tuples in the
instructors table), each row with value “A”.

8/6/2020 DBMS 48
 The FROM Clause

 The from clause lists the relations involved in the query

o Corresponds to the Cartesian product operation of the relational algebra.
 Find the Cartesian product instructor X teaches
select *
from instructor, teaches
o generates every possible instructor – teaches pair, with all attributes from both
relations.
o For common attributes (e.g., ID), the attributes in the resulting table are renamed
using the relation name (e.g., [Link])
 Cartesian product not very useful directly, but useful combined with where-clause
condition (selection operation in relational algebra).
8/6/2020 DBMS 49
 The WHERE Clause
 The where clause specifies conditions that the result must satisfy
– Corresponds to the selection predicate of the relational algebra.
 To find all instructors in Comp. Sci. dept
select name
from instructor
where dept_name = ‘Comp. Sci.'
 Comparison results can be combined using the logical connectives and, or, and
not
– To find all instructors in Comp. Sci. dept with salary > 80000
select name
from instructor
where dept_name = ‘Comp. Sci.' and salary > 80000
 Comparisons can be applied to results of arithmetic expressions.
8/6/2020 DBMS 40
 Where Clause Predicates

 SQL includes a between AND comparison operator

 Example: Find the names of all instructors with salary between $90,000 and
$100,000 (that is, >= $90,000 and <= $100,000)
select name
from instructor
where salary between 90000 and 100000

8/6/2020 DBMS 51
 Null Values

 It is possible for tuples to have a null value, denoted by null, for some of their
attributes
 null signifies an unknown value or that a value does not exist.
 The result of any arithmetic expression involving null is null
 Example: 5 + null returns null
 The predicate is null can be used to check for null values.
– Example: Find all instructors whose salary is null.
select name
from instructor
where salary is null

8/6/2020 DBMS 52
 Renaming table in Select clause

 The SQL allows renaming relations and attributes using the as clause:

old-name as new-name

 Find the names of all instructors who have taught some course and the
course_id,
select name, course_id
from instructor as T , teaches as S
where [Link] = [Link]
 Keyword as is optional and may be omitted
instructor as T ≡ instructor T

8/6/2020 DBMS 53
 SQL Operators

• SQL Arithmetic Operators

• SQL Comparison Operators
• SQL Logical Operators

8/6/2020 DBMS 54
 Arithmetic Operators

 The select clause can contain arithmetic expressions involving the operation, +, –, *, and
/, and operating on constants or attributes of tuples.
o The Query:
select ID, name, salary/12
from instructor
would return a relation that is the same as the instructor relation, except that the
value of the attribute salary is divided by 12.
o Can rename “salary/12” using the as clause:
select ID, name, salary/12 as monthly_salary

8/6/2020 DBMS 55
 SQL Comparison Operators

• Select * from employee

where salary = 90000;

• Select * from employee

where salary <>100000;

• Select * from employee

where salary >=90000 and salary<=100000

8/6/2020 DBMS 56
 SQL Logical Operators

8/6/2020 DBMS 57
 Logical Operators(Cont..)
• ALL
SELECT * FROM Products WHERE Price > ALL (SELECT Price FROM
Products WHERE Price > 500);
• AND
SELECT * FROM Customers WHERE City = "London" AND Country = "UK";
• ANY
SELECT * FROM Products WHERE Price > ANY (SELECT Price FROM
Products WHERE Price > 50);
• BETWEEN AND
SELECT * FROM Products WHERE Price BETWEEN 50 AND 60;
• EXISTS
SELECT * FROM Products WHERE EXISTS (SELECT Price FROM Products
WHERE Price > 50);
8/6/2020 DBMS 58
 Logical Operators(Cont..)

• IN
SELECT * FROM Customers WHERE City IN ('Paris','London');
• LIKE
SELECT * FROM Customers WHERE City LIKE 's%';
• NOT
SELECT * FROM Customers WHERE City NOT LIKE 's%';
• OR
SELECT * FROM Customers WHERE City = "London" OR Country = "UK";
• SOME
SELECT * FROM Products WHERE Price > SOME (SELECT Price FROM Products
WHERE Price > 20);

8/6/2020 DBMS 59
 SQL Functions
 Single Row Functions : Operate on each row and return one output for each row.
 Date Functions, String Functions such as length or case conversion functions like
UPPER, LOWER.
 Number functions such as ROUND, TRUNC, and MOD etc.
 Multi Row Functions : Aggregate Function/Group Functions : Operates on Group of rows
and return output for the complete set of rows. Also known as Group functions.
 Min, Max, Count, Sum, Avg etc.
 SQL Single Row Functions can be used in Select Clause, Where Clause, Group By Clause,
Order By clause

 SQL Multi Row Functions can be used in Select Clause, Group By Clause, Having Clause.

8/6/2020 DBMS 60
 String Function : Use in Select, Where, group by , having , order by
Clause
Function Meaning
Char_length(string) Return number of characters in argument
Concat(expr1,expr2) Return concatenated string
Instr(expr1,expr2) Return the index of the first occurrence of substring
Lower(expr1) Return the argument in lowercase
Left(expr1,count) Return the leftmost number of characters from string

Lpad(expr1,length,expr2) left-pads a string with another string, to a certain length

Ltrim() Remove leading spaces

Substr(string,startpos,length) extracts a substring from a string (starting at any
position).
LOCATE(substring, string, start) returns the position of the first occurrence of a substring
in a string
STRCMP(string1, string2) compares two strings. Returns 0,1,-1
Upper(string) Convert the text to upper-case
Trim(string) removes leading and trailing spaces from a string.
8/6/2020 DBMS 61
 DATE Function : Use in Select, Where, group by having Clause, order by
clause
Function Meaning
DATE_ADD(date, INTERVAL Adds a specified time interval to a date.
value addunit)
CURDATE() function returns the current date. as "YYYY-MM-DD" (string)
DATEDIFF(date1, date2) returns the number of days between two date values
DATE_SUB(date, INTERVAL subtracts a time/date interval from a date and then
value interval) returns the date
DAY(date) returns the day of the month for a given date

DAYNAME(date) returns the weekday name for a given date.

SYSDATE() returns the current date and time.

8/6/2020 DBMS 62
 Single row Function : String (Pattern matching )

 Patterns are case sensitive; that is, uppercase characters do not match lowercase characters,
or vice-versa.
 To illustrate pattern matching, we consider the following examples:
 Percent ( % ): The % character matches any substring.
 Underscore ( _ ): The character matches any character in the string.
 ’Intro%’ matches any string beginning with “Intro”.
 ’%Comp%’ matches any string containing “Comp” as a substring, for example, ’Intro. to
Computer Science’, and ’Computational Biology’.
 ’---’ matches any string of exactly three characters.
 ’ ---%’ matches any string of at least three characters.

8/6/2020 DBMS 63
 Pattern matching examples……
• Syntax is
select <column name> from <table_name >where <column_name > like/ not like 'pattern';
• To find the records starting with ‘Luck’
• SELECT * FROM student WHERE city LIKE 'Luck%';
• To find the names not starting with ‘Luck’
• SELECT name FROM student WHERE city NOT LIKE 'Luck%';
• To find the names ending with ‘ly’
• SELECT * FROM student WHERE city LIKE '%fy’ ;
• Find names containing a y
• SELECT * FROM student WHERE city LIKE '%y%‘;
• To find names containing exactly five characters
• SELECT * FROM student WHERE city LIKE ' ';
8/6/2020 DBMS 64
 Ordering the Display of Tuples

 List in alphabetic order the names of all instructors

select distinct name
from instructor
order by name
 We may specify desc for descending order or asc for ascending order, for
each attribute; ascending order is the default.
Example: order by name desc
 Can sort on multiple attributes
Example: order by dept_name, name

8/6/2020 DBMS 65
 Aggregate Functions

Type Use Functions

Single –row Operate on a single column of a relation of String functions, Date
functions single row n the table returning single value Functions
as an output
Multiple –row Act on a multiple row in the relation Avg, min, max, sum, count
functions returning single value as an output
avg: average value
min: minimum value
max: maximum value
sum: sum of values
count: number of values

8/6/2020 DBMS 66
 Aggregate Functions Examples
Find the average salary of instructors in the Computer Science department
◦ select avg (salary),min(salary), max(salary),sum(salary)
from instructor
where dept_name= 'Comp. Sci.';
Find the number of tuples in the course relation
◦ select count (*) from instructor;

8/6/2020 DBMS 67
 Aggregate Functions – Group By
Find the average salary of instructors in each department
◦ select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name;

8/6/2020 DBMS 68
 Aggregation (Cont.)
Attributes in select clause outside of aggregate functions must appear in group by list
◦ /* erroneous query */
select dept_name, ID, avg (salary)
from instructor
group by dept_name;

Discuss why query is erroneous, [Hint :refer last table]

8/6/2020 DBMS 69
 Aggregate Functions – Having Clause

Find the names and average salaries of all departments whose average salary is
greater than 42000

select dept_name, avg (salary) as avg_salary

from instructor
group by dept_name
having avg (salary) > 42000;

Note: predicates in the having clause are applied

after the formation of groups whereas predicates in the
where clause are applied before forming groups

8/6/2020 DBMS 70
 Null Values and Aggregates
• To find the total all salaries
select sum (salary) from instructor

◦ Above statement ignores null amounts

◦ Result is null if there is no non-null amount
• All aggregate operations except count(*) ignore tuples with null values on
the aggregated attributes
◦ What if collection has only null values?
◦ count returns 0
◦ all other aggregates return null

8/6/2020 DBMS 71
 SQL Joins
▪ Join operations take two relations and return as a result another relation.
▪A join operation is a Cartesian product which requires that tuples in the two relations match
(under some condition). It also specifies the attributes that are present in the result of the join
▪ The join operations are typically used as subquery expressions in the from clause
▪Join condition – defines which tuples in the two relations match, and what attributes are
present in the result of the join.
▪ Join type – defines how tuples in each relation that do not match any tuple in the other relation
(based on the join condition) are treated.

8/6/2020 DBMS 72
v1
 SQL Joins : Cross Join

• Cross JOIN is a simplest form of JOINs which matches each row from one database table
to all rows of another as a Cartesian product.
• The cross join does not establish a relationship between the joined tables.
• SELECT * FROM `Movies` CROSS JOIN `Artist` OR
• SELECT * FROM `Movies` ,`Artist`;

Artist
Movies

Movie_id Title Category Id First_name Last_name Movie_id

1 ASSASSIN'S CREED: Animations 1 Adam Smith 1

2 Real Steel(2012) Animations 2 Ravi Kumar 2

8/6/2020 DBMS 73
v1
 Cross Join of 2 tables

Movie_id Title Category Id First_name Last_name Movie_id

1 ASSASSIN'S CREED: Animations 1 Adam Smith 1

1 ASSASSIN'S CREED: Animations 2 Ravi Kumar 2
2 Real Steel(2012) Animations 1 Adam Smith 1
2 Real Steel(2012) Animations 2 Ravi Kumar 2

8/6/2020 DBMS 74
 SQL Joins : Inner Join
• The inner JOIN is used to return rows from both tables that satisfy the given condition(join
condition on common column ).
• SELECT * FROM movies INNER JOIN `Artist` on movies.movie_id = Artist.movie_id
OR
SELECT * FROM movies ,Artist WHERE movies.movie_id = Artist.movie_id

Movie_id Title Category Id First_name Last_name Movie_id

1 ASSASSIN'S CREED: Animations 1 Adam Smith 1

2 Real Steel(2012) Animations 2 Ravi Kumar 2

8/6/2020 DBMS 75
v1
 SQL Joins : Outer Join
• MySQL Outer JOINs return all records matching from both tables. It can detect records
having no match in joined table. It returns NULL values for records of joined table if no
match is found.
The LEFT JOIN returns all
SELECT [Link] , B.first_name , B.last_name
the rows from the table on the
FROM movies “A” LEFT OUTER JOIN Artist “ B” left even if no matching rows
have been found in the table
ON B.`movie_id` = A. ‘movie_id’ on the right.
Where no matches have
# Some SQL Support keyword : Left join/natural left outer join
been found in the table on
OR the right, NULL is returned.

What will Right Outer

SELECT [Link] , B.first_name , B.last_name return?

FROM movies “A” LEFT OUTER JOIN Artist “ B” USING ( `movie_id` ) What will full outer return?
*Use Using keyword for left and right join queries only not for full outer join queries
8/6/2020 DBMS 76
 Left outer join Output (contd..)
Movie_id Title Category Id First_name Last_name Movie_id
1 ASSASSIN'S CREED: Animations 1 Adam Smith 1

2 Real Steel(2012) Animations 2 Ravi Kumar 2

3 Jurassic Park Animation

Title First_name Last_name

ASSASSIN'S CREED: Adam Smith
Real Steel(2012) Ravi Kumar
Jurassic Park Null Null

8/6/2020 DBMS 77
 SQL Joins - Revision

8/6/2020 DBMS 78
v1
 SQL Joins on multiple tables

8/6/2020 DBMS 79
v1
 Join operations – Example

Relation course Relation prereq

● Observe that
prereq relation is missing for CS-315 and
course relation is missing for CS-347

8/6/2020 DBMS 80
v2
 Left Outer Join And Right Outer Join

• Select * from course natural left outer join prereq

course

● Select * from course natural right outer join prereq prereq

8/6/2020 DBMS 81
v4
 Full outer Join

● Full Outer Join is implemented as union of left

outer and right outer join in MYSQL.
course

prereq
●select * from course left outer join prereq on
course.course_id = prereq.course_id
union
select * from course right outer join prereq on
course.course_id = prereq.course_id

8/6/2020 DBMS 72
v6
 Inner join Vs. Natural Join
• Select * from course inner join prereq on
course.course_id = prereq.course_id course

What is the difference between above query, and a natural join?

prereq

• The difference is in natural join no need to specify

condition but in inner join condition is mandatory.
• The repeated column is avoided in the output of
natural join.
• Select * from course natural join prereq

8/6/2020 DBMS 73
v7
 Subqueries (Nested Query)

• A Subquery or Inner query or a Nested query is a

query within another SQL query and embedded
within the WHERE clause.
• A subquery is used to return data that will be used in
the main query as a condition to further restrict the
data to be retrieved.
• Subqueries can be used with the SELECT, INSERT,
UPDATE, and DELETE statements along with the
operators like =, <, >, >=, <=, IN, BETWEEN, etc.

8/6/2020 DBMS 84
 Examples of Subquery in DML and Select
• SQL> SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM
CUSTOMERS WHERE SALARY > 4500) ;

• Insert data in new table[table should be existing]

• SQL> INSERT INTO CUSTOMERS_BKP SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID FROM CUSTOMERS) ;

• SQL> UPDATE CUSTOMERS SET SALARY = SALARY * 0.25 WHERE

AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 );

• SQL> DELETE FROM CUSTOMERS WHERE AGE IN (SELECT AGE FROM

CUSTOMERS_BKP WHERE AGE >= 27 );
8/6/2020 DBMS 85
 Subqueries in the From Clause
 Find the average instructors’ salaries of those departments where the average salary is
greater than $42,000.”

select dept_name, avg_salary

from ( select dept_name, avg (salary)
from instructor
group by dept_name)
as dept_avg (dept_name,avg_salary)
where avg_salary > 42000;

8/6/2020 DBMS 76
 Test for Empty Relations

▪EXISTS and NOT EXISTS are used with a subquery in WHERE clause to
examine if the result the subquery returns is TRUE or FALSE.
▪ The true or false value is then used to restrict the rows from outer query select.
▪As EXISTS and NOT EXISTS only return TRUE or FALSE in the subquery, the
SELECT list in the subquery does not need to contain actual column name(s).

8/6/2020 DBMS 87
 Continued…

 SELECT * FROM customers WHERE EXISTS (SELECT * FROM order_details

WHERE customers.customer_id = order_details.customer_id);
 SELECT * FROM customers WHERE NOT EXISTS (SELECT * FROM order_details
WHERE customers.customer_id = order_details.customer_id);

 Insert, update, delete commands can also be used with EXISTS commands
 INSERT INTO contacts (contact_id, contact_name) SELECT supplier_id,
supplier_name FROM suppliers WHERE EXISTS (SELECT * FROM orders WHERE
suppliers.supplier_id = orders.supplier_id);
 Delete from contacts SELECT supplier_id, supplier_name FROM suppliers WHERE
EXISTS (SELECT * FROM orders WHERE suppliers.supplier_id =
orders.supplier_id);
8/6/2020 DBMS 88
 Set Operations

Set operations are union, intersect(inner join), and minus(left

join/right join) table1
◦ Each of the above operations automatically eliminates duplicates ID NAME
1 ABHI
To retain all duplicates use the keyword all
2 SAMEER
◦ union all, ID NAME
3 SAMEER
◦ intersect all 1 ABHI
◦ Minus 2 SAMEER
3 SAMEER table2
4 JAVED
ID NAME

3 SAMEER
 Select * from table1 union select * from table 2;
4 JAVED
8/6/2020 DBMS 89
 Set Operations -examples
ID NAME

• Select distinct id from t1 inner join t2 using(id);(intersect) 1 ABHI

2 SAMEER table1
ID NAME
3 SAMEER
3 SAMEER

• select id from t1 left join t2 using (id) where [Link] is null; ID NAME
(minus) 3 SAMEER
ID NAME
4 JAVED table2
1 ABHI
2 SAMEER ID NAME
1 ABHI
• Select * from table1 union all select * from table 2; 2 SAMEER
3 SAMEER
3 SAMEER
8/6/2020 DBMS 4 JAVED 90
 Set Membership

Find courses offered in Fall 2017 and in Spring 2018

select distinct course_id
from section
where semester = 'Fall' and year= 2017 and
course_id in (select course_id
from section
where semester = 'Spring' and year= 2018);

Find courses offered in Fall 2017 but not in Spring 2018

select distinct course_id
from section
where semester = 'Fall' and year= 2017 and
course_id not in (select course_id
from section
where semester = 'Spring' and year= 2018);
8/6/2020 DBMS 91
 Set Membership (Cont.)

▪ Name all instructors whose name is neither “Mozart” nor Einstein”

instructor

select distinct name from instructor

where name not in ('Mozart', 'Einstein')

8/6/2020 DBMS 92
 Views : Uses and Importance

 In some cases, it is not desirable for all users to see the entire logical model
(i.e., all the actual relations stored in the database.)
 Consider a person who needs to know an instructors name and department, but
not the salary. This person should see a relation described, in SQL, by
•
select ID, name, dept_name from instructor

 A view provides a mechanism to hide certain data from the view of certain
users thus providing security.
 Any relation that is not of the conceptual model but is made visible to a user as
a “virtual relation” is called a view.

8/6/2020 DBMS 93
 View -Syntax
 A view is defined using the create view statement which has the form
create view v as < query expression >
view name any legal SQL expression.

 Once a view is defined, the view name can be used to refer to the virtual
relation that the view generates.
 Can provide huge time savings in writing queries by already having a group of
frequently accessed tables joined together in a view .

8/6/2020 DBMS 94
 Example of Views
 A view of instructors without their salary
create view faculty as
select ID, name, dept_name
from instructor
 Find all instructors in the Biology department
select name
from faculty
where dept_name = ‘Biology’
 Create a view of department salary totals
create view departments_total_salary(dept_name, total_salary) as
select dept_name, sum (salary)
from instructor
group by dept_name;
8/6/2020 DBMS 95
 Inserting a new tuple into a View

 Add a new tuple to faculty view which we defined earlier

insert into faculty values (’30765’, ’Green’, ’Music’);
 This insertion must be represented by the insertion of the tuple
(’30765’, ’Green’, ’Music’, null)
into the instructor relation

8/6/2020 DBMS 96
 Update of a View

 Update query is used to Update the tuples of a view.

UPDATE faculty
set dept_name=“Biology”
where name=“ABC”

 Updation in view reflects the original table . Means the changes will be done
in the original table.

8/6/2020 DBMS 97
 Dropping a View

 DROP query is used to delete a view.

Syntax:
DROP view view_name;
Example:
DROP view faculty;

8/6/2020 DBMS 98
 Index

 Indices are data structures used to speed up access of records with specified values for
index attributes.
 Indexes are used to find rows with specific column values quickly.
 Without an index, MySQL must begin with the first row and then read through the entire
table to find the relevant rows. (Sequential Scan)
 If the table has an index for the columns , MySQL can quickly determine the position to
seek to in the middle of the data file without having to look at all the data.
 This is much faster than reading every row sequentially
 MySQL create default indexes on PRIMARY KEY, UNIQUE KEY
 User defined index can be created using CREATE INDEX COMMAND although they are
not visible to the user.
 MySQL indices are stored in B-trees.

8/6/2020 DBMS 99
 Example

Syntax:
• CREATE INDEX <index_name> ON < table_name >(column1, column2, ...);

• CREATE UNIQUE INDEX <index_name > ON <table_name> (column1, column2, ...);

• ALTER TABLE <table_name> DROP INDEX <index_name;>

Example:
• create table person(pid int primary key , pnm varchar(10));
• create index id1 on person(pnm); // indexes help to retrieve the data faster.
• Create unique index id2 on person(pid,pnm) ; // rows will have unique value
• alter table person drop index id1;
8/6/2020 DBMS 10
0
 References
[Link], R. and Gherke, J., “Database Management Systems”, 3rd Ed., McGraw-
Hill.

2. Connally T, Begg C.,”Database Systems”,Pearson Education

8/6/2020 DBMS 10
1
 References

Text Books:
• Abraham Silberschatz, Henry F. Korth and S. Sudarshan, Database System Concepts 6th
Ed, McGraw Hill, 2010.
• Elmasi, R. and Navathe, S.B., “Fundamentals of Database Systems”, 4th
• Ed., Pearson Education.
Reference Books :
• Ramakrishnan, R. and Gherke, J., “Database Management Systems”, 3rd Ed., McGraw-
Hill.
• Connally T, Begg C.,”Database Systems”,Pearson Education

DATABASE MANAGEMENT 10
SYSTEMS 2
 End of Unit 2

8/6/2020 DBMS 10
3