程序代写代做代考 database INFO20003 Database Systems

INFO20003 Database Systems
Dr Renata Borovica-Gajic
Lecture 9 SQL Summary
Week 5
1
INFO20003 Database Systems © University of Melbourne

In this lecture
• Extending your knowledge
– DML
• Comparison&LogicOperators
• SetOperations
• Subquery
• MultiplerecordINSERTs,INSERTfromatable • UPDATE,DELETE,REPLACE
• Views
– DDL
• ALTERandDROP,TRUNCATE,RENAME
– DCL
• How to think about SQL
– Problem Solving
INFO20003 Database Systems © Univers-it-y of Melbourne
6

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 © Univers-it-y of Melbourne
7

Comparison and Logic Operators
• Comparison:
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 which is used)
• Logic:
• AND, NOT, OR
• Example: SELECT * FROM Furniture WHERE ((Type=“Chair” AND Colour = “Black”) OR (Type = “Lamp” AND Colour = “Black”))
INFO20003 Database Systems © Univers-it-y of Melbourne
10

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 © Univers-it-y of Melbourne
12

UNION Example
INFO20003 Database Systems © Univers-it-y of Melbourne
13

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 © Univers-it-y of Melbourne
20

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!)
INFO20003 Database Systems © Univers-it-y of Melbourne
21

Auction Bids – Physical Model
SellerID INT BuyerID INT ArtefactID INT
INFO20003 Database Systems © Univers-it-y of Melbourne
22

Tables
Seller
Offer
Artefact
Buyer
INFO20003 Database Systems © Univers-it-y of Melbourne
23

Example: Subquery
List the BuyerID, Name and Phone number for all bidders on artefact 1
Offer
Buyer
Result
INFO20003 Database Systems © Univers-it-y of Melbourne
24

More examples using subqueries (NOT IN)
Which Artefacts don’t have offers made on them?
Offer
Artefact
Result
INFO20003 Database Systems © Univers-it-y of Melbourne
25

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 © University of Melbourne
26

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
Offer
Buyer
AND ArtefactID = 1)
Result
INFO20003 Database Systems © University of Melbourne
30

ANY/ALL/EXISTS (differences)
• Great tutorial about these:
–http://www.sqltutorial.org/sql-all/ –http://www.sqltutorial.org/sql-any/ –http://www.sqltutorial.org/sql-exists/
• All: must satisfy all inner conditions
SELECT empno, sal
FROM emp
WHERE sal > ALL (200, 300, 400);
Equiv. SELECT empno, sal FROM emp
WHERE sal > 200 AND sal > 300 AND sal> 400;
• Any: must satisfy at least one of the inner conditions (any of)
Equiv.
SELECT empno, sal
FROM emp
WHERE sal > ANY (200, 300, 400);
SELECT empno, sal
FROM emp
WHERE sal > 200 OR sal > 300 OR sal> 400;
• Exists: the inner query returns at least one record
SELECT empid, first_name, last_name FROM employees AS E
WHERE
“Print all employees who have at least one dependent”
EXISTS( SELECT * FROM dependents AS D WHERE D. empid = E. empid);
INFO20003 Database Systems © University of Melbourne
31

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 © Univers-it-y of Melbourne
32

The UPDATE Statement
• Changes existing data in tables
– Order of statements is important
– Specifying a WHERE clause is important
• Unlessyouwantittooperateonthewholetable
• Example: Increase all salaries greater than $100000 by 10% and all other salaries by 5%
Any problems with this?
INFO20003 Database Systems © Univers-it-y of Melbourne
33

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 © Univers-it-y of Melbourne
34

DELETE, REPLACE
• REPLACE
– REPLACE works identically as INSERT
• Exceptifanoldrowinatablehasakeyvaluethesameasthenewrow then it is overwritten…
• DELETE
– The DANGEROUS command – deletes ALL records • Thebetterversion(unlessyouarereally,reallysure)
– Be aware of the foreign key constraints
• ONDELETECASCADEorONDELETERESTRICT(labpractice)
INFO20003 Database Systems © Univers-it-y of Melbourne
35

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:
– Theyhelphidethequerycomplexityfromusers
– Theyhelphidedatafromusers
• 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)
– Canbeusedjustlikeanyothertable
INFO20003 Database Systems © Univers-it-y of Melbourne
36

Create View Example
INFO20003 Database Systems © Univers-it-y of Melbourne
37

Using a View
INFO20003 Database Systems © Univers-it-y of Melbourne
38

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 © Univers-it-y of Melbourne
41

More DDL Commands
• TRUNCATE
– Same as DELETE * FROM table;
– Faster but cannot ROLL BACK a TRUNCATE command
• Have to get data back from backup…
• DROP
– Potentially DANGEROUS
• Kills a relation – removes the data, removes the relation
– There is NO UNDO COMMAND! (have to restore from backup)
• DROP TABLE TableName
INFO20003 Database Systems © Univers-it-y of Melbourne
42

Data Control Language / Other Commands
• DCL
– Users and permissions
• CREATE USER, DROP USER • GRANT, REVOKE
• SET PASSWORD
• OtherCommands
– Database administration
• BACKUP TABLE, RESTORE TABLE • ANALYZE TABLE
– Miscellaneous
• DESCRIBE tablename • USE db_name
• Theyaretypicallycalled‘DatabaseAdministrationStatements’
INFO20003 Database Systems © University of Melbourne
43

How to think about 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 © Univers-it-y of Melbourne
44

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 © Univers-it-y of Melbourne
45

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:
– Whichfields&tables:
F: Employer, Location
T: Student, Stream, StudentStream
– Butonlyifthecapacity>20(condition)
• Lets try to use the structure of the SELECT statement now:
SELECT Employer, Location
FROM Student INNER JOIN StudentStream
ON Student.StudentID = StudentStream.StudentID INNER JOIN Stream
ON StudentStream.StreamID = Stream.StreamID WHERE Capacity > 20;
SELECT Employer, Location FROM Student
NATURAL JOIN StudentStream NATURAL JOIN Stream WHERE Capacity > 20;
INFO20003 Database Systems © Univers-it-y of Melbourne
46

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 © University of Melbourne
47

SQL Video
• A very good overview:
https://www.youtube.com/watch?v=uRdIdd- UkTc&index=7&list=PLdQddgMBv5zHcEN9RrhADq3CBCol hY2hl
INFO20003 Database Systems © University of Melbourne
49

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
INFO20003 Database Systems © University of Melbourne
51

Related Posts