Reminders and Important Dates
Mar 10, 2020
Upcoming deadlines
• March 9 to 13: Tutorial 6 is due
• March 16 to 20: Tutorial 7 is due
• March 17 @ 7PM: Midterm 2
• March 30 to April 3: Tutorial 9 is due
• April 5 @ 11:59PM: Milestone 4- Implementation (no late submissions allowed)
• April 7-8: Milestone 5- Demos
• April 8 @ 11:59PM: Tutorial 10 is due April 14 @ 12PM: Final exam
Reminders
• Java/Oracle, PHP/Oracle and PHP/MYSQL resources are available
• Mason and Aditya have office hours dedicated for project
• If your project group would like to meet with your project TA within the next week, then
please email them to set up an appointment
• RA and Datalog practice exercises are available on canvas – Please
check them out!
Please check piazza post @7 for Important Dates and Deadlines
Outline
1. Recap
2. SQL
• Set Operators (Union, Intersection, Except)
• ALL and ANY
• IS NULL
• Self referencing joins
• Outer joins
• Exists
• Not
3. Practice Exercise
2
Previously…
• Having
• Like
• IN
• JOIN
• Renaming(Aliases)
• View
3
Clicker question: views
Suppose relation R(a,b,c): a b c
Define the view V by: 1 1 3
CREATE VIEW V AS 1 2 3
SELECT a+b AS d, c 2 1 4
FROM R; 2 3 5
What is the result of the query: 2 4 1
3 2 4
SELECT d, SUM(c)
3 3 6
FROM V
GROUP BY d
HAVING COUNT(*) <> 1;
Identify, from the list below, a tuple in the result of the query:
A. (2,3)
B. (3,12)
C. (5,9)
D. All are correct
E. None are correct
SQL 4
Clicker question: views
Suppose relation R(a,b,c): V
Define the view V by:
a b c d c d Sum(C)
CREATE VIEW V AS
1 1 3 2 3 3 7
SELECT a+b AS d, c
1 2 3 3 3 5 9
FROM R;
2 1 4 3 4 6 7
What is the result of the query: 2 3 5 5 5
SELECT d, SUM(c) 2 4 1 6 1
FROM V 3 2 4 5 4
GROUP BY d 3 3 6 6 6
HAVING COUNT(*) <> 1;
Identify, from the list below, a tuple in the result of the query:
A. (2,3) Wrong. Count of 2 is 1 so will not be in result
B. (3,12)
C is Right
C. (5,9)
5
D. All are correct
SQL 5
Views – Try!
For each product that has more than For each product whose items were sold
three items sold within all sales in more than one sales transaction.
transactions
CREATE VIEW
CREATE VIEW
products_in_multiple_trnsc AS
products_more_than_3_sold AS SELECT productid,
SELECT productid, productname, productname, productprice
productprice FROM product
FROM product WHERE productid IN
WHERE productid IN (SELECT productid
(SELECT productid FROM soldvia
FROM soldvia GROUP BY productid
GROUP BY productid HAVING COUNT(*) > 1);
HAVING SUM(noofitems) > 3);
SQL 6
Views
CREATE VIEW
products_in_multiple_trnsc AS CREATE VIEW
SELECT productid, products_in_multiple_trnsc AS
productname, productprice SELECT [Link],
FROM product [Link], [Link]
WHERE productid IN FROM product p, soldvia sv
(SELECT productid where [Link]=[Link]
FROM soldvia GROUP BY [Link],
GROUP BY productid [Link], [Link]
HAVING COUNT(*) > 1); HAVING COUNT(*) > 1
SQL 7
SET OPERATORS
• Standard set operators: union, intersection, and difference
• Used to combine the results of two or more SELECT statements that are
union compatible
• Two sets of columns are union compatible if they contain the same
number of columns, and if the data types of the columns in one set match
the data types of the columns in the other set
• Each of the above operations automatically eliminates duplicates.
• To retain all duplicates use : union all, intersect all and except all
SQL 8
SET OPERATOR – WITHOUT UNION
Query 20 text: Retrieve the customer Name who have purchased Zzz Bag or a
Cosy Sock
Query 20: SELECT [Link],[Link]
FROM Customer C, Product P, SalesTransaction T, Soldvia S
WHERE [Link] = T. CustomerID AND [Link]=[Link] AND
[Link]=[Link] AND
([Link]=‘Zzz Bag’ OR [Link]=‘Cosy Sock’)
Query 20 result:
CUSTOMERNAME PRODUCTNAME
Tina Zzz Bag
Tina Zzz Bag
Tina Cosy Sock
SQL 9
SET OPERATORS
• UNION
• Used to combine the union compatible results of two SELECT
statements by listing all rows from the result of the first SELECT
statement and all rows from the result of the other SELECT
statement
• If two or more rows are identical only one of them is shown
(duplicates are eliminated from the result)
SQL 10
SET OPERATOR - UNION
Query 21: Retrieve the customer Name who have purchased Zzz Bag or a Cosy
Sock
Query 21:
SELECT [Link], [Link]
FROM Customer C, Product P, SalesTransaction T, Soldvia S
WHERE [Link] = T. CustomerID AND [Link]=[Link] AND
[Link]=[Link] AND [Link] = 'Zzz Bag'
UNION
SELECT [Link], [Link]
FROM Customer C, Product P, SalesTransaction T, Soldvia S
WHERE [Link] = T. CustomerID AND [Link]=[Link] AND
[Link]=[Link] AND [Link] = 'Cosy Sock';
CUSTOMERNAME PRODUCTNAME
Tina Cosy Sock
Tina Zzz Bag
SQL 11
SET OPERATOR – UNION USING VIEWS
Query 22 : Retrieve the product ID, product name, and product price for
each product that has more than three items sold within all sales
transactions or whose items were sold in more than one sales
transaction
Query 22: SELECT *
FROM products_more_than_3_sold
UNION
SELECT *
FROM products_in_multiple_trnsc;
Query 22 Result
PRODUCTI PRODUCT PRODUCT
D NAME PRICE
1X1 Zzz Bag 100
2X2 Easy Boot 70
3X3 Cosy Sock 15
4X4 Dura Boot 90
SQL 12
SET OPERATORS
• INTERSECT
• Used to combine the results of two SELECT statements that are union
compatible by listing every row that appears in the result of both of the
SELECT statements
SQL 13
SET OPERATORS
Query 23 text: Retrieve the product ID, product name, and product price for each
product that has more than three items sold within all sales transactions and whose
items were sold in more than one sales transaction
Query 23: SELECT *
FROM products_more_than_3_sold
INTERSECT
SELECT *
FROM products_in_multiple_trnsc;
Query 24 result:
PRODUCTID PRODUCTNAME PRODUCTPRICE
4X4 Dura Boot 90
SQL 14
SET OPERATORS
• EXCEPT (MINUS)
• Used to combine the results of two SELECT statements that are union
compatible by listing every row from the result of the first SELECT
statement that does not appear in the result of the other SELECT
statement
SQL 15
SET OPERATORS
Query 24: Retrieve the product ID, product name, and product price for each
product that has more than three items sold within all sales transactions but
whose items were not sold in more than one sales transaction
Query 24: SELECT *
FROM products_more_than_3_sold
EXCEPT
SELECT *
FROM products_in_multiple_trnsc;
Query 24 result:
PRODUCTID PRODUCTNAME PRODUCTPRICE
3X3 Cosy Sock 15
SQL 16
ALL and ANY
• Set comparison:
• Op ANY or Op ALL
• where Op >, < , <=, >=, <> =
• For each outer query tuple, check the qualification by computing the
subquery.
SQL 17
ALL
Query 25 : Find the product Id and Product Price of the products having price greater than that
of every product with the name ‘Boot’.
SELECT [Link],
[Link]
FROM Product P
WHERE > ALL
[Link] >
ALL (SELECT The ALL operator returns true if all of the subquery
[Link] values meet the condition.
FROM Product P2 1x1 100.00 TRUE
WHERE 2x2 70.00 FALSE
[Link] LIKE 3x3 15.00 FALSE
'%Boot%'); 4x4 90.00 FALSE
5x5 150.00 TRUE
6x6 250.00 TRUE
ANSWER
SELECT [Link], [Link]
FROM Product P
WHERE [Link] > (SELECT max([Link])
FROM Product P2 WHERE [Link] LIKE '%Boot%');
SQL 18
ANY
Query 26 : Find the product ID and Product Price of the products having price greater than that
of any product with the name ‘Boot’.
SELECT [Link],
[Link]
FROM Product P
WHERE [Link] > > ANY
ANY (SELECT
[Link] The ALL operator returns true if any of the subquery
FROM Product P2 WHERE values meet the condition.
[Link] LIKE 1x1 100.00 TRUE
'%Boot%');
2x2 70.00 FALSE
3x3 15.00 FALSE
4x4 90.00 TRUE
SELECT [Link], 5x5 150.00 TRUE
ANSWER
[Link] 6x6 250.00 TRUE
FROM Product P
WHERE [Link] > (SELECT min([Link])
FROM Product P2 WHERE [Link] LIKE '%Boot%');
SQL 19
Relational schema: HAFH Realty Company Property Management Database
optional
unique
Unary relationship
Data records: HAFH Realty Company Property Management Database (part 1)
Data records: HAFH Realty Company Property Management Database (part 2)
IS NULL
Query 27 text: Retrieve records for all managers who do not have a bonus
Query 27: SELECT *
FROM manager
WHERE mbonus IS NULL;
Query 27 result:
SQL 23
IS NULL
• Used in queries that contain comparisons with an empty value in
a column of a record
• The result of any arithmetic expression involving null is null
• E.g. 5 + null returns null.
• All aggregate operations except count(*) ignore tuples with null
values on the aggregated attributes.
SQL 24
Clicker Null query
Determine the result of: Scores:
SELECT COUNT(*), COUNT(Runs) Team Day Opponent Runs
Dragons Sun Swallows 4
FROM Scores
Tigers Sun Bay Stars 9
WHERE Team = 'Carp'
Carp Sun NULL NULL
Swallows Sun Dragons 7
Which of the following is in the result: Bay Stars Sun Tigers 2
A. (1,0) Giants Sun NULL NULL
Dragons Mon Carp NULL
B. (2,0)
Tigers Mon NULL NULL
C. (1,NULL) Carp Mon Dragons NULL
D. All of the above Swallows Mon Giants 0
E. None of the above Bay Stars Mon NULL NULL
Giants Mon Swallows 5
SQL 25
Clicker Null query
Determine the result of: Scores:
Team Day Opponent Runs
SELECT COUNT(*), COUNT(Runs)
Dragons Sun Swallows 4
FROM Scores
Tigers Sun Bay Stars 9
WHERE Team = 'Carp' Carp Sun NULL NULL
Which of the following is in Swallows Sun Dragons 7
the result: Bay Stars Sun Tigers 2
A. (1,0) Giants Sun NULL NULL
B. (2,0) Right Dragons Mon Carp NULL
Tigers Mon NULL NULL
C. (1,NULL)
Carp Mon Dragons NULL
D. All of the above Swallows Mon Giants 0
E. None of the above Bay Stars Mon NULL NULL
Giants Mon Swallows 5
SQL 26
Try 1!
Query: Display the BuildingID, AptNo, and ANoOfBedrooms for all apartments
that are not leased.
SELECT buildingid, aptno, anoofbedrooms
FROM apartment
WHERE ccid IS NULL
SQL 27
Try 2!
Query: Display the MFName, MLName, MSalary, MBDate, and
number of buildings that the manager manages for all managers with a
salary less than $55,000.
SELECT [Link], [Link], [Link], [Link],
count(*)
FROM building b, manager m
WHERE [Link] = [Link] AND [Link] < 55000
GROUP BY [Link], [Link], [Link], [Link];
SQL 28
SELF-JOIN
Self-JOIN
• A join statement that includes a relation that contains a foreign
key referring to itself, and joins a relation with itself in a query
SQL 29
SELF-JOIN
Query 28 text: For all corporate clients that were referred by other corporate
clients, retrieve the name of the corporate client and the name of the corporate
client that referred it
Query 28: SELECT [Link] AS client, [Link] AS
recommender
FROM corpclient c, corpclient r
WHERE [Link] = [Link];
Query 28 result:
The use of aliases is mandatory for a self join.
SQL 30
INNER and OUTER JOINS
• Inner join
• What we’ve been doing so far.
• Outer join
• left outer join
• right outer join
• full outer join
SQL 31
INNER JOIN
Query : SELECT [Link], [Link], [Link]
FROM apartment a, corpclient c
WHERE [Link] = [Link];
Query result:
SQL 32
INNER JOIN
• The INNER JOIN keyword selects all rows from both tables as
long as there is a match between the columns in both tables.
• SELECT column_name(s)
FROM table1
INNER JOIN/ JOIN table2
ON table1.column_name=table2.column_name;
SQL 33
INNER JOIN
• Which apartments are rented by which corporate clients?
SELECT [Link], [Link], [Link]
FROM apartment a
INNER JOIN corpclient c
ON [Link] = [Link];
Retrieve only the
rows that match.
apartment ⨝ corpclient
SQL 34
Outer Joins
LEFT JOIN: Return all rows RIGHT JOIN : Returns all rows FULL OUTER JOIN:
from the left table, and the from the right table, with the Returns all rows from the
matched rows from the right matching rows in the left table. left table and from the right
table. The result is NULL in The result is NULL in the left table.
the right side when there is side when there is no match.
no match. SELECT column_name(s)
SELECT column_name(s) FROM table1
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2
FROM table1 RIGHT JOIN/ RIGHT OUTER ON table1.column_name=tabl
LEFT JOIN/ LEFT OUTER JOIN table2 e2.column_name
JOIN table2 ON table1.column_name=table2.c
ON table1.column_name=table2 olumn_name;
.column_name; Table Table
Table Table 1 2
Table Table 1 2
1 2
SQL 35
Left Outer Join
SELECT [Link], [Link], [Link]
FROM apartment a LEFT OUTER JOIN corpclient c
ON [Link] = [Link];
Include all the rows on the left part of the
relation (APARTMENT) whether or not there
is a match.
No matches.
RA : apartment ⟕ corpclient
SQL 36
Right Outer Join
SELECT [Link], [Link], [Link]
FROM apartment a RIGHT OUTER JOIN corpclient c
ON [Link] = [Link];
Include all the rows on the right part of the relation
(CORPCLIENT) whether or not there is a match.
RA : apartment ⟖ corpclient
No match.
SQL 37
Full Outer Join
SELECT [Link], [Link], [Link]
FROM apartment a FULL OUTER JOIN
corpclient c
ON [Link] = [Link];
Include all the rows from both relations whether
or not there is a match.
RA: apartment ⟗ corpclient
SQL 38
Nested Queries
Query : List the first name of managers that were living in building having
more than four floors
select [Link] , m. [Link] Boris B1
from manager m Austin B2
where [Link] IN
(select [Link] from building b subquery
where [Link] > 4)
SQL 39
Correlated Nested Queries
• Correlated: inner query (query in the WHERE clause) uses one or more
attributes from relation(s) specified in the outer query
• Uncorrelated: inner query is a stand-alone query that can be executed
independently from the outer query
EXISTS
- Checks whether the result of a correlated nested query is empty (contains no
tuples) or not
- The EXISTS operator returns true if the subquery returns one or more records.
SQL 40
EXISTS
Query 30 text: Retrieve records for all buildings that have managers
living in them
Building
Query 30:
SELECT *
FROM building b
Manager
WHERE EXISTS
(SELECT *
FROM manager m
WHERE [Link] =[Link]);
Query 30 result:
SQL 41
Clicker Question
• Query : List the first name of managers that were living in building having more
than four floors.
Query 1: Query 2:
select [Link], Query 3:
select [Link],
[Link] select [Link], [Link]
[Link]
from manager m from manager m
from manager m, building b
where Exists where [Link] IN
where [Link] > 4 and
(select [Link] (select [Link] from building
[Link]=[Link]
from building b b where [Link] > 4)
where [Link] > 4 and
[Link]=[Link])
Which queries are equivalent?
A. Query 1 and Query 2 only
B. Query 1, Query 2 and Query 3
C. Query 1 and 3 only
D. None
SQL 42
Clicker Question
• Query : List the first name of managers that were living in building having more
than four floors.
Query 2:
select [Link], Query 3:
Query 1: select [Link], [Link]
[Link]
select [Link], from manager m
from manager m
[Link] where [Link] IN
where Exists
from manager m, building b (select [Link] from building
(select [Link]
where [Link] > 4 and b where [Link] > 4)
from building b
[Link]=[Link]
where [Link] > 4 and
[Link]=[Link])
SQL 43
Equivalent Queries
Query : List the first name of managers that were living in building having more than four
floors
select [Link], [Link]
select [Link], [Link] from manager m, building b
from manager m where [Link] > 4 and
where [Link] IN
[Link]=[Link]
(select [Link] from building b where
[Link] > 4)
select [Link], [Link]
from manager m
where Exists
(select [Link]
from building b
where [Link] > 4 and
[Link]=[Link])
SQL 44
NOT
• Tests whether a value is not contained in a set or not a null value
respectively
• Syntax looks similar to the IN and IS NULL operators:
• attr NOT IN ( set of values)
• attr IS NOT NULL
SQL 45
NOT
Query 31: Retrieve the information about other customers apart from Tina
and Tony
SELECT *
from customer
where customername NOT IN ('Tina', 'Tony');
CUSTOMERID CUSTOMERNAME CUSTOMERZIP
3-4-555 Pam 35401
SQL 46
NOT
Query text 32: Retrieve the record for each product whose product name does not
contains the phrase ’Boot’
SELECT *
FROM product
WHERE productname NOT LIKE '%Boot%';
PRODUCTID PRODUCTNAME PRODUCTPRICE VENDORID CATEGORYID
1X1 Zzz Bag 100 PG CP
3X3 Cosy Sock 15 MK FW
5X5 Tiny Tent 150 MK CP
6X6 Biggy Tent 250 MK CP
SQL 47