CS4.
301 Data & Applications
Ponnurangam Kumaraguru (“PK”)
#ProfGiri @ IIIT Hyderabad
[Link] /in/ponguru @ponguru [Link]
Figure 6.5 The results of SQL multiset operations. (a) Two
tables, R(A) and S(A). (b) R(A)UNION ALL S(A). (c) R(A) EXCEPT
ALL S(A). (d) R(A) INTERSECT ALL S(A).
Each tuple whether duplicate or not is
considered as different tuple when applying
these operations
Substring Pattern Matching and Arithmetic
Operators
LIKE comparison operator
Used for string pattern matching
% replaces an arbitrary number of zero or more characters
underscore (_) replaces a single character
Examples: WHERE Address LIKE ‘%Houston,TX%’;
WHERE Ssn LIKE ‘_ _ 1_ _ 8901’
WHERE text LIKE ‘ka_ _ _ ka%’
BETWEEN comparison operator
WHERE (Salary BETWEEN 30000 AND 40000)
AND Dno = 5;
3
Arithmetic Operations
Standard arithmetic operators:
Addition (+), subtraction (–), multiplication (*), and division (/)
may be included as a part of SELECT
Query 13. Show the resulting salaries if every employee working on the ‘ProductX’
project is given a 10 percent raise.
4
Arithmetic Operations
Standard arithmetic operators:
Addition (+), subtraction (–), multiplication (*), and division (/)
may be included as a part of SELECT
Query 13. Show the resulting salaries if every employee working on the ‘ProductX’
project is given a 10 percent raise.
SELECT [Link], [Link], 1.1 * [Link] AS Increased_sal
FROM EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P
WHERE [Link]=[Link] AND [Link]=[Link] AND [Link]=‘ProductX’;
5
Ordering of Query Results
Use ORDER BY clause
Keyword DESC to see result in a descending order of values
Keyword ASC to specify ascending order explicitly
Typically placed at the end of the query
ORDER BY [Link] DESC, [Link] ASC, [Link] ASC
6
How did the quiz go?
7
This Lecture
8
Order by
9
Order by
10
11
Basic SQL Retrieval Query Block
12
INSERT, DELETE, and UPDATE Statements in SQL
Three commands used to modify the database:
INSERT, DELETE, and UPDATE
INSERT typically inserts a tuple (row) in a relation (table)
UPDATE may update a number of tuples (rows) in a relation (table)
that satisfy the condition
DELETE may also update a number of tuples (rows) in a relation
(table) that satisfy the condition
13
INSERT
In its simplest form, it is used to add one or more tuples to a relation
Attribute values should be listed in the same order as the attributes
were specified in the CREATE TABLE command
Constraints on data types are observed automatically
Any integrity constraints as a part of the DDL specification are enforced
14
The INSERT Command
Specify the relation name and a list of values for the tuple. All values
including nulls are supplied.
15
The INSERT Command
Specify the relation name and a list of values for the tuple. All values
including nulls are supplied.
16
The INSERT Command
The variation below inserts multiple tuples where a new table is
loaded values from the result of a query.
17
18
BULK LOADING OF TABLES
Another variation of INSERT is used for bulk-loading of several tuples into
tables
A new table TNEW can be created with the same attributes as T and using
LIKE and DATA in the syntax, it can be loaded with entire data.
EXAMPLE:
CREATE TABLE D5EMPS LIKE EMPLOYEE
(SELECT E.*
FROM EMPLOYEE AS E WITH DATA specifies that the table will
WHERE [Link]=5) be created & loaded with the data
WITH DATA; specified in the query
19
DELETE
Removes tuples from a relation
Includes a WHERE-clause to select the tuples to be deleted
Referential integrity should be enforced
Tuples are deleted from only one table at a time (unless CASCADE is specified
on a referential integrity constraint)
A missing WHERE-clause specifies that all tuples in the relation are to be
deleted; the table then becomes an empty table
The number of tuples deleted depends on the number of tuples in the relation
that satisfy the WHERE-clause
20
The DELETE Command
Removes tuples from a relation
Includes a WHERE clause to select the tuples to be deleted. The number of
tuples deleted will vary.
21
UPDATE
Used to modify attribute values of one or more selected tuples
A WHERE-clause selects the tuples to be modified
An additional SET-clause specifies the attributes to be modified and
their new values
Each command modifies tuples in the same relation
Referential integrity specified as part of DDL specification is enforced
22
UPDATE (contd.)
Example: Change the location and controlling department number of
project number 10 to 'Bellaire' and 5, respectively
U5: UPDATE PROJECT
SET PLOCATION = 'Bellaire', DNUM = 5
WHERE PNUMBER=10
23
24
UPDATE (contd.)
Example: Give all employees in the 'Research' department a 10% raise in salary.
25
UPDATE (contd.)
Example: Give all employees in the 'Research' department a 10% raise in salary.
U6: UPDATE EMPLOYEE
SET SALARY = SALARY *1.1
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME='Research')
In this request, the modified SALARY value depends on the original SALARY value
in each tuple
The reference to the SALARY attribute on the right of = refers to the old SALARY
value before modification
The reference to the SALARY attribute on the left of = refers to the new SALARY
value after modification
26
27
Specifying Joined Tables in the FROM Clause of
SQL
Joined table
Permits users to specify a table resulting from a join operation in the FROM
clause of a query
The FROM clause in Q1A
Contains a single joined table. JOIN may also be called INNER JOIN
SELECT fname, lname, address
FROM employee, department
WHERE dno = dnumber
AND dname = 'Research';
28
Specifying Joined Tables in the FROM Clause of
SQL
Joined table
Permits users to specify a table resulting from a join operation in the FROM
clause of a query
The FROM clause in Q1A
Contains a single joined table. JOIN may also be called INNER JOIN
SELECT fname, lname, address Select fname, lname, address
FROM employee, department from (employee join department
WHERE dno = dnumber on dno=dnumber) where
AND dname = 'Research'; dname='research';
29
Specifying Joined Tables in the FROM Clause of
SQL
Joined table
Permits users to specify a table resulting from a join operation in the FROM
clause of a query
The FROM clause in Q1A
Contains a single joined table. JOIN may also be called INNER JOIN
Select fname, lname, address
from (employee join department
on dno=dnumber) where
dname='research';
30
Different Types of JOINed Tables in SQL
Specify different types of join
NATURAL JOIN
Various types of OUTER JOIN (LEFT, RIGHT, FULL )
NATURAL JOIN on two relations R and S
Automatically joins two tables based on all columns that have the same name
in both tables.
It eliminates duplicate columns in the result. So, you don’t have to specify the
join condition manually — SQL does it for you based on matching column
names.
The columns must be the same data type
31
SELECT fname, lname, dname, location
FROM employee NATURAL JOIN department;
32
SELECT fname, lname, dname, location
FROM employee NATURAL JOIN department;
33
NATURAL JOIN
34
NATURAL JOIN
35
INNER and OUTER Joins
INNER JOIN (versus OUTER JOIN)
Default type of join in a joined table
Tuple is included in the result only if a matching tuple exists in the other relation
LEFT OUTER JOIN
Every tuple in left table must appear in result
If no matching tuple
Padded with NULL values for attributes of right table
RIGHT OUTER JOIN
Every tuple in right table must appear in result
If no matching tuple
Padded with NULL values for attributes of left table
36
Natural join & Inner join difference
37
38
Joins differences
39
[Link]
Multiway JOIN in the FROM clause
Can nest JOIN specifications for a multiway join:
SELECT Pnumber, Dnum, Lname,
Address, Bdate FROM ((PROJECT
JOIN DEPARTMENT ON
Dnum=Dnumber) JOIN EMPLOYEE
ON Mgr_ssn=Ssn) WHERE
Plocation='Stafford';
40
Multiway JOIN in the FROM clause
Can nest JOIN specifications for a multiway join:
SELECT Pnumber, Dnum, Lname,
Address, Bdate FROM ((PROJECT
JOIN DEPARTMENT ON
Dnum=Dnumber) JOIN EMPLOYEE
ON Mgr_ssn=Ssn) WHERE
Plocation='Stafford';
41
Activity
Try all the queries all yourself if you have not tried it
Write 3 join statements using any of the Company DB
Submit the query and results
Try the Multiway JOIN statement with any of the Company DB
Submit the query and results
42
Bibliography / Acknowledgements
Instructor materials from Elmasri & Navathe 7e
43
[Link]
[Link]
/in/ponguru
ponguru
Thank you
[Link]@[Link]
for attending
the class!!!