This Lecture • More SQL SELECT • Aliases SQL SELECT II • ‘Self - Joins’ • Subqueries • IN, EXISTS, ANY, ALL Database Systems • LIKE • Further reading Michael Pound • The Manga Guide to Databases, Chapter 4 • Database Systems, Chapter 6 Last Lecture SQL SELECT Overview SELECT • WHERE Clauses [DISTINCT | ALL] <column-list> • SELECT from multiple tables FROM <table-names> SELECT * FROM TA, TB; • JOINs [WHERE <condition>] • CROSS JOIN (Cartesian Product) [ORDER BY <column-list>] SELECT * FROM TA CROSS JOIN TB; [GROUP BY <column-list>] • INNER JOIN (Specifies a column or condition) [HAVING <condition>] SELECT * FROM TA INNER JOIN TB USING (Col1); SELECT * FROM TA INNER JOIN TB ON ( α ); ([] optional , | or ) • NATURAL JOIN (Compares columns with identical names) SELECT * FROM TA NATURAL JOIN TB; Aliases Alias Example • Aliases rename columns • Two forms: SELECT Employee E.ID AS empID, or tables • Column alias ID First E.Name, W.Dept • Can make names more SELECT column [AS] 123 John newName meaningful 124 Mary FROM • Can shorten names, Employee E, • Table alias WorksIn making them easier to WorksIn W, SELECT table [AS] ID Department use WHERE newName 123 Marketing • Can resolve ambiguous E.ID = W.ID 124 Sales names ([] optional ) 124 Marketing Note: You cannot use a column alias in a WHERE clause 1
Alias Example Aliases and ‘Self - Joins’ SELECT • Aliases can be used to E.ID AS empID, copy a table, so that it can Employee be combined with itself: E.Name, W.Dept Name Dept empID Name Department FROM John Marketing 123 John Marketing Mary Sales 124 Mary Sales Employee E, SELECT A.Name FROM Peter Sales 124 Mary Marketing Employee A, WorksIn W, Andy Marketing Employee B WHERE Anne Marketing WHERE A.Dept = B.Dept E.ID = W.ID AND B.Name = „Andy‟ Note: You normally cannot use a column alias in a WHERE clause Aliases and ‘Self - Joins’ Aliases and ‘Self - Joins’ SELECT ... FROM Employee A, Employee B ... Employee A Employee B A.Name A.Dept B.Name B.Dept A B John Marketing John Marketing Name Dept Name Dept Mary Sales John Marketing John Marketing John Marketing Peter Sales John Marketing Mary Sales Mary Sales Andy Marketing John Marketing Peter Sales Peter Sales Anne Marketing John Marketing Andy Marketing Andy Marketing John Marketing Mary Sales Anne Marketing Anne Marketing Mary Sales Mary Sales Peter Sales Mary Sales Andy Marketing Mary Sales Anne Marketing Mary Sales Aliases and ‘Self - Joins’ Aliases and ‘Self - Joins’ SELECT ... FROM Employee A, Employee B SELECT ... FROM Employee A, Employee B WHERE A.Dept = B.Dept WHERE A.Dept = B.Dept AND B.Name = „Andy‟ A.Name A.Dept B.Name B.Dept John Marketing John Marketing A.Name A.Dept B.Name B.Dept Andy Marketing John Marketing John Marketing Andy Marketing Anne Marketing John Marketing Andy Marketing Andy Marketing Mary Sales Mary Sales Anne Marketing Andy Marketing Peter Sales Mary Sales Mary Sales Peter Sales Peter Sales Peter Sales John Marketing Andy Marketing Andy Marketing Andy Marketing Anne Marketing Andy Marketing 2
Aliases and ‘Self - Joins’ Subqueries SELECT A.Name FROM Employee A, Employee B • A SELECT statement can • For example, retrieve a WHERE A.Dept = B.Dept AND B.Name = „Andy‟ be nested inside list of names of people another query to form a who are in Andy’s subquery department: A.Name John • The results of the SELECT Name Andy subquery are passed Anne FROM Employee back to the containing WHERE Dept = query (SELECT Dept • The result is the names of all employees who work in the FROM Employee same department as Andy. WHERE Name = „Andy‟) Subqueries Subqueries • Often a subquery will • Options for handling SELECT Name • First the subquery is return a set of values sets FROM Employee evaluated, returning rather than a single • IN – checks to see if a WHERE Dept = ‘Marketing’ value value is in a set (SELECT Dept • This value is passed to • EXISTS – checks to see if • We cannot directly FROM Employee the main query a set is empty compare a single value WHERE • ALL/ANY – checks to see to a set. Doing so will Name = „Andy‟) if a relationship holds for SELECT Name result in an error every/one member of a FROM Employee set WHERE Dept = • NOT can be used with „Marketing‟ any of the above IN IN • Using IN we can see if a SELECT <columns> SELECT * FROM <tables> FROM Employee given value is in a set of Employee values WHERE <value> WHERE Department IN Name Dept Manager IN <set> („Marketing‟, • NOT IN checks to see if John Marketing Chris Mary Marketing Chris „Sales‟) a given value is not in Chris Marketing Jane SELECT <columns> the set Employee Peter Sales Jane FROM <tables> • The set can be given Name Dept Manager Jane Management WHERE <value> John Marketing Chris explicitly or can be NOT IN <set> Mary Marketing Chris produced in a subquery Chris Marketing Jane Peter Sales Jane 3
(NOT) IN (NOT) IN • First the subquery • This gives SELECT * SELECT Manager Employee FROM Employee SELECT * Name Department Manager FROM Employee John Marketing Chris FROM Employee WHERE Name NOT • is evaluated giving Mary Marketing Chris WHERE Name NOT IN IN („Chris‟, Chris Marketing Jane „Jane) (SELECT Manager Manager Peter Sales Jane Chris FROM Employee) Jane Management Name Deptartment Manager Chris Jane John Marketing Chris Mary Marketing Chris Jane Peter Sales Jane EXISTS EXISTS • Using EXISTS we see SELECT <columns> SELECT * FROM <tables> FROM Employee AS E1 that there is at least Employee WHERE EXISTS <set> WHERE EXISTS ( one element in a set Name Dept Manager SELECT * FROM • NOT EXISTS is true if the John Marketing Chris SELECT <columns> Mary Marketing Chris Employee AS E2 set is empty Chris Marketing Jane FROM <tables> WHERE E2.Name = • The set is always given Peter Sales Jane WHERE NOT EXISTS E1.Manager) by a subquery Jane Management <set> Name Dept Manager Chris Marketing Jane Jane Management ANY and ALL ALL • ANY and ALL compare a • Find the names of the • val = ANY (set) is Name Salary single value to a set of true if there is at least employee(s) who earn Mary 20,000 values one member of the set the highest salary John 15,000 equal to value Jane 25,000 • They are used with Paul 30,000 SELECT Name comparison operators • val = ALL (set) is FROM Employee like = , >, <, <>, >=, <= true if all members of WHERE Salary >= the set are equal to the Name ALL ( value Paul SELECT Salary FROM Employee) 4
ANY Word Searches • Find the names of the • Word Searches • For example: Given a Name Salary employee(s) who earn • Commonly used for database of books, Mary 20,000 more than someone else searching product searching for “crypt” John 15,000 catalogues etc. Jane 25,000 might return • Need to search by Paul 30,000 SELECT Name • “ Cryptonomicon ” by Neil keywords Stephenson FROM Employee • Might need to use • “ Applied Cryptographer ” Name WHERE Salary > partial keywords by Bruce Schneier Mary ANY ( Jane SELECT Salary Paul FROM Employee) LIKE LIKE • The ‘%’ character can • The ‘_’ character • We can use the LIKE keyword to perform represent any number of represents exactly one string comparisons in queries characters, including character • Like is not the same as ‘=’ because it allows none wildcard characters bookName LIKE “cloud_” bookName LIKE “crypt%” • It is not normally case sensitive • Will return “Cryptography • Will return “Clouds” but not “Cloud” or “Cloud Engineering” and SELECT * FROM books Computing” “ Cryptonomicon ” but not “Applied Cryptography” WHERE bookName LIKE “%crypt%”; LIKE Examples Student • Sometimes you might SELECT * FROM sID sName sAddress sYear books 1 Smith 5 Arnold Close 2 need to search for a set Enrolment 2 Brooks 7 Holly Avenue 2 sID mCode of words WHERE bookName 3 Anderson 15 Main Street 3 1 G52ADS • To find entries with all LIKE “%crypt%”; 4 Evans Flat 1a, High Street 2 2 G52ADS words you can link OR bookName LIKE 5 Harrison Newark Hall 1 5 G51DBS conditions with AND 6 Jones Southwell Hall 1 “%cloud%”; 5 G51PRG • To find entries with any 5 G51IAI Module words use OR 4 G52ADS mCode mCredits mTitle 6 G51PRG G51DBS 10 Database Systems 6 G51IAI G51PRG 20 Programming G51IAI 10 Artificial Intelligence G52ADS 10 Algorithms 5
Examples Next Lecture • Write SQL statements to do the following: • More SQL SELECT • Find a list of students in the 2 nd or 3 rd year • ORDER BY • Find a list of student IDs and Names for students • Aggregate functions studying G52ADS, but without using a JOIN • GROUP BY and HAVING • Find a list of names of any students who are • UNION enrolled on at least one module alongside ‘Evans’ • Further reading • The Manga Guide to Databases, Chapter 4 • Database Systems, Chapter 6 6
Recommend
More recommend
