INFO20003 Database Systems
INFO20003 Database Systems 1© University of Melbourne 2018
INFO20003 Database Systems
Lecture 9
SQL Summary
Semester 2 2018, Week 5
Dr Renata Borovica-Gajic
INFO20003 Database Systems 2© University of Melbourne 2018
Homework solutions
1. Find the name of all sailors whose rating is above 9
2. Find all sailors who reserved a boat prior to November 1, 1996
3. Find (the names of) all boats that have been reserved at least once
𝜋𝑠𝑛𝑎𝑚𝑒(𝜎𝑟𝑎𝑡𝑖𝑛𝑔>9(𝑆𝑎𝑖𝑙𝑜𝑟𝑠))
𝜋𝑠𝑛𝑎𝑚𝑒(𝑆𝑎𝑖𝑙𝑜𝑟𝑠 𝜎𝑑𝑎𝑦<"11/01/96"(𝑅𝑒𝑠𝑒𝑟𝑣𝑒𝑠)) 𝜋𝑏𝑛𝑎𝑚𝑒(𝐵𝑜𝑎𝑡𝑠 𝑅𝑒𝑠𝑒𝑟𝑣𝑒𝑠) INFO20003 Database Systems 3© University of Melbourne 2018 Homework solutions 4. Find all pairs of sailors with the same rating 𝜋𝑠𝑛𝑎𝑚𝑒1,𝑠𝑛𝑎𝑚𝑒2 𝑆1 𝑟𝑎𝑡𝑖𝑛𝑔1=𝑟𝑎𝑡𝑖𝑛𝑔2 𝑠𝑖𝑑1!=𝑠𝑖𝑑2 𝑆2ν 𝜌(𝑆1 1 → 𝑠𝑖𝑑1, 2 → 𝑠𝑛𝑎𝑚𝑒1, 3 → 𝑟𝑎𝑡𝑖𝑛𝑔1,4 → 𝑎𝑔𝑒1 , 𝑆𝑎𝑖𝑙𝑜𝑟𝑠) 𝜌(𝑆2 1 → 𝑠𝑖𝑑2, 2 → 𝑠𝑛𝑎𝑚𝑒2, 3 → 𝑟𝑎𝑡𝑖𝑛𝑔2,4 → 𝑎𝑔𝑒2 , 𝑆𝑎𝑖𝑙𝑜𝑟𝑠) INFO20003 Database Systems 5© University of Melbourne 2018 Examples for marks deduction: • Entity / Attribute incorrect or missing • Relationship cardinality incorrect (e.g. one - many) • Poor naming of object (e.g. T1, T2) • Wrong data type (e.g. varchar for placing ‘age’) • Incorrect primary or foreign key • Not Null is wrong • Unresolved M-M exists, or associative entity is incorrect • Business rules can’t be supported (e.g. you cannot obtain the exchange rate on a particular date) Assignment 1: Examples for marks deduction NOTE: These are EXAMPLES, not a contract set in stone INFO20003 Database Systems 6© University of Melbourne 2018 Coverage - - • Extending your knowledge – DML • Comparison & Logic Operators • Set Operations • Subquery • Multiple record INSERTs • INSERT from a table, UPDATE, DELETE, REPLACE • Views – DDL • ALTER and DROP, TRUNCATE, RENAME • How to think about SQL – Problem Solving INFO20003 Database Systems 7© University of Melbourne 2018 Things to Remember about SQL - - • SQL keywords are case insensitive – We try to CAPITALISE them to make them clear • Table names are Operating System Sensitive – If case sensitivity exists in the operating system, then the table names are case sensitive! (i.e. Mac, Linux) • Account <> ACCOUNT
• Field names are case insensitive
– ACCOUNTID == AccountID == AcCoUnTID
• You can do maths in SQL…
– SELECT 1*1+1/1-1;
INFO20003 Database Systems 10© University of Melbourne 2018
Comparison and Logic Operators
– –
• Comparison:
• Logic:
• AND, NOT, OR
• Example: SELECT * FROM Furniture WHERE ((Type=“Chair” AND
Colour = “Black”) OR (Type = “Lamp” AND Colour = “Black”))
Operator Description
= Equal to
< Less than > Greater than
<= Less than or equal to >= Greater than or equal to
<> OR != Not equal to (depends on DBMS as to which is used)
INFO20003 Database Systems 12© University of Melbourne 2018
Set Operations
– –
• UNION
– Shows all rows returned from the queries (or tables)
• INTERSECT
– Shows only rows that are common in the queries (or the tables)
• [UNION/INTERSECT] ALL
– If you want duplicate rows shown in the results you need to use the
ALL keyword.. UNION ALL etc.
• In MySQL only UNION and UNION ALL are supported
INFO20003 Database Systems 13© University of Melbourne 2018
UNION Example
– –
INFO20003 Database Systems 14© University of Melbourne 2018
JOINS depicted as Venn Diagrams
– –
T1.ID T2.ID
INFO20003 Database Systems 15© University of Melbourne 2018
JOINS depicted as Venn Diagrams
– –
T1.ID T2.ID
• T1 INNER JOIN T2 ON T1.ID = T2.ID
• T1 NATURAL JOIN T2
✅
INFO20003 Database Systems 16© University of Melbourne 2018
JOINS depicted as Venn Diagrams
– –
• T1 LEFT OUTER JOIN T2 ON T1.ID = T2.ID
✅✅
T1.ID T2.ID
INFO20003 Database Systems 17© University of Melbourne 2018
JOINS depicted as Venn Diagrams
– –
• T1 RIGHT OUTER JOIN T2 ON T1.ID = T2.ID
✅ ✅
T1.ID T2.ID
INFO20003 Database Systems 18© University of Melbourne 2018
JOINS depicted as Venn Diagrams
– –
• T1 FULL OUTER JOIN T2 ON T1.ID = T2.ID
✅✅ ✅
T1.ID T2.ID
INFO20003 Database Systems 20© University of Melbourne 2018
Query Nesting
– –
• SQL provides the ability to nest subqueries
• A nested query is simply another select query you write to
produce a table set
– Remember that all select queries return a table set of data
• A common use of subqueries is to perform set tests
– Set membership, set comparisons
INFO20003 Database Systems 21© University of Melbourne 2018
Sub-Query Comparison Operators
– –
• IN / NOT IN
– Used to test whether the attribute is IN/NOT IN the subquery list
• ANY
– True if any value returned meets the condition
• ALL
– True if all values returned meet the condition
• EXISTS
– True if the subquery returns one or more records
• For more info:
• https://www.w3schools.com/sql/sql_any_all.asp
• https://www.w3schools.com/sql/sql_exists.asp
• General help with SQL: https://www.w3schools.com/sql/ (great tutorial)
https://www.w3schools.com/sql/sql_any_all.asp
https://www.w3schools.com/sql/sql_exists.asp
https://www.w3schools.com/sql/
INFO20003 Database Systems 22© University of Melbourne 2018
Auction Bids – Physical Model
– –
SellerID INT
BuyerID INT
ArtefactID INT
INFO20003 Database Systems 23© University of Melbourne 2018
Tables
– –
Seller
Buyer
Offer
Artefact
INFO20003 Database Systems 24© University of Melbourne 2018
Example: Subquery
– –
• List the BuyerID, Name and Phone number for all bidders on
artefact 1
INFO20003 Database Systems 25© University of Melbourne 2018
More examples using subqueries
– –
• Which Artefacts don’t have offers made on them
SellerID INT
BuyerID INT
ArtefactID INT
INFO20003 Database Systems 26© University of Melbourne 2018
INFO20003 Database Systems 29© University of Melbourne 2018
Do we need to use IN? Is there another way…
• List the BuyerID, Name and Phone number for all bidders on
artefact 1
SELECT * FROM Buyer
WHERE BuyerID IN (SELECT BuyerID FROM Offer
WHERE ArtefactID = 1)
Equals to
SELECT BuyerID, Name and Phone
FROM Buyer NATURAL JOIN Offer
WHERE ArtefactID = 1
This is a more efficient way
INFO20003 Database Systems 30© University of Melbourne 2018
Exists example
• Returns true if the subquery returns one or more records
• Example: List the BuyerID, Name and Phone number for all bidders on artefact 1
SELECT * FROM Buyer
WHERE EXISTS
(SELECT * FROM Offer
WHERE Buyer.BuyerID = Offer.BuyerID
AND ArtefactID = 1)
SellerID INT
BuyerID INT
ArtefactID INT
INFO20003 Database Systems 31© University of Melbourne 2018
More on INSERT
– –
• Inserting records from a table:
– Note: table must already exist
• Multiple record inserts: All columns must be inserted
Specific columns will be inserted
INFO20003 Database Systems 32© University of Melbourne 2018
The UPDATE Statement
– –
• Changes existing data in tables
– Order of statements is important
– Specifying a WHERE clause is important
• Unless you want it to operate on the whole table
• Example: Increase all salaries greater than $100000 by 10% and all other
salaries by 5%
Any problems with this?
INFO20003 Database Systems 33© University of Melbourne 2018
The UPDATE Statement: CASE
– –
• A better solution in this case is to use the CASE command
If salary is lower than 100000 increase it by 5%,
otherwise increase it by 10%
INFO20003 Database Systems 34© University of Melbourne 2018
DELETE, REPLACE
– –
• REPLACE
– REPLACE works identically as INSERT
• Except if an old row in a table has a key value the same as the new row
then it is overwritten…
• DELETE
– The DANGEROUS command – deletes ALL records
• The better version (unless you are really, really sure)
– Be aware of the foreign key constraints
• ON DELETE CASCADE or ON DELETE RESTRICT (lab practice)
INFO20003 Database Systems 35© University of Melbourne 2018
Views
– –
• Any relation that is not in the physical models, but is made available to the
“user” as a virtual relation is called a view.
• Views are good because:
– They help hide the query complexity from users
– They help hide data from users
• Different users use different views
– Prevents someone from accessing the employee tables to see salaries for
instance
• One way of improving database security
• Create view statement:
CREATE VIEW nameofview AS validsqlstatement
• Once a view is defined
– Its definition is stored in the database (not the data, but metadata – schema
information)
– Can be used just like any other table
INFO20003 Database Systems 36© University of Melbourne 2018
Create View Example
– –
INFO20003 Database Systems 37© University of Melbourne 2018
Using a View
– –
INFO20003 Database Systems 40© University of Melbourne 2018
More DDL Commands
– –
• There are more than CREATE!
• ALTER
– Allows us to add or remove attributes (columns) from a relation
(table)
• ALTER TABLE TableName ADD AttributeName AttributeType
• ALTER TABLE TableName DROP AttributeName
• RENAME
– Allows the renaming of tables (relations)
• RENAME TABLE CurrentTableName TO NewTableName
INFO20003 Database Systems 43© University of Melbourne 2018
How to Think like SQL
– –
• It’s going to be critical for you to think like SQL to handle the
queries you will need to write…
• Hopefully the following discussion will help you in this
endeavour:
1. USE the database design as a MAP to help you when you are
formulating queries
2. USE the structure of the SELECT statement as a template
3. FILL out parts of the SELECT structure and BUILD the query
• Let’s try it!
INFO20003 Database Systems 44© University of Melbourne 2018
Example
– –
Example: Which employers employ students who are doing a course in
locations where the capacity is greater than 20 persons, and what are those
locations?
INFO20003 Database Systems 45© University of Melbourne 2018
How to approach writing queries
– –
Which employers employ students
who are doing a course in locations
where the capacity is greater than 20 persons,
and what are those locations?
• What is the query asking for:
– Which fields & tables:
F: Employer, Location, Capacity
T: Student, Stream, StudentStream
– But only if the capacity > 20 (condition)
• Lets try to use the structure of the SELECT statement now:
SELECT Employer, Location, Capacity
FROM Student INNER JOIN StudentStream
ON Student.StudentID = StudentStream.StudentID
INNER JOIN Stream
ON StudentStream.StreamID = Stream.StreamID
WHERE Capacity > 20;
SELECT Employer, Location, Capacity
FROM Student
NATURAL JOIN StudentStream
NATURAL JOIN Stream
WHERE Capacity > 20;
INFO20003 Database Systems 46© University of Melbourne 2018
You try it…
What is the phone number of the instructor who teaches a course that costs
over 10000$ attended by studentID 202.
INFO20003 Database Systems 48© University of Melbourne 2018
SQL Video
• A very good overview:
https://www.youtube.com/watch?v=uRdIdd-
UkTc&index=7&list=PLdQddgMBv5zHcEN9RrhADq3CBCol
hY2hl
INFO20003 Database Systems 49© University of Melbourne 2018
What’s Examinable
– –
• You need to know how to write SQL
Enhancing career prospects
for women in STEM
50
Internship Information Session:
Monday 20 August, 3.15pm
Greenwood Theatre
Increase your graduate employability with an
internship during your degree!
If you are a female student majoring in Computer
Science/IT, come along to this session to find out
how to approach your internship search and what
additional support is available to you.
Register at: go.unimelb.edu.au/mc46
Theatre, Physics South Building
INFO20003 Database Systems 51© University of Melbourne 2018
Next lecture
• Storage and indexing
– Learn how data is stored and accessed within a DBMS
– Alternative types of indexes
– Going “under the hood” of a DBMS