23/11/20171 Lecture 9 2 ORDER BY clause may not be used in asubquery (although it may be used in outermostSELECT). Subquery SELECT list must consist of a singlecolumn name or expression, except forsubqueries that use EXISTS. By default, column names refer to table name inFROM clause of subquery. Can refer to a tablein FROM using … Continue reading “an operand in a comparison | My Assignment Tutor”
23/11/20171 Lecture 9 2 ORDER BY clause may not be used in asubquery (although it may be used in outermostSELECT). Subquery SELECT list must consist of a singlecolumn name or expression, except forsubqueries that use EXISTS. By default, column names refer to table name inFROM clause of subquery. Can refer to a tablein FROM using an alias.Pearson Education © 2009Subquery Rules 23/11/20172 3 When subquery is an operand in a comparison,subquery must appear on right-hand side. A subquery may not be used as an operand in anexpression.Pearson Education © 2009Subquery Rules 4List properties handled by staff at ‘163 Main St’.SELECT propertyNo, street, city, postcode, type, rooms, rentFROM PropertyForRentWHERE staffNo IN(SELECT staffNoFROM StaffWHERE branchNo =(SELECT branchNoFROM BranchWHERE street = ‘163 Main St’));Pearson Education © 2009Example 6.21 Nested subquery: use of IN 23/11/20173 5Pearson Education © 2009Example 6.21 Nested subquery: use of IN 6 ANY and ALL may be used with subqueries thatproduce a single column of numbers. With ALL, condition will only be true if it issatisfied by all values produced by subquery. With ANY, condition will be true if it is satisfiedby any values produced by subquery. If subquery is empty, ALL returns true, ANYreturns false. SOME may be used in place of ANY.Pearson Education © 2009ANY and ALL 23/11/20174 7Find staff whose salary is larger than salary of atleast one member of staff at branch B003.SELECT staffNo, fName, lName, position, salaryFROM StaffWHERE salary > SOME(SELECT salaryFROM StaffWHERE branchNo = ‘B003’);Pearson Education © 2009Example 6.22 Use of ANY/SOME 8 Inner query produces set {12000, 18000, 24000}and outer query selects those staff whose salariesare greater than any of the values in this set.Pearson Education © 2009Example 6.22 Use of ANY/SOME 23/11/20175 9Find staff whose salary is larger than salary ofevery member of staff at branch B003.SELECT staffNo, fName, lName, position, salaryFROM StaffWHERE salary > ALL(SELECT salaryFROM StaffWHERE branchNo = ‘B003’);Pearson Education © 2009Example 6.23 Use of ALL 10Pearson Education © 2009Example 6.23 Use of ALL 23/11/20176 11 Can use subqueries provided result columns comefrom same table. If result columns come from more than one tablemust use a join. To perform join, include more than one table inFROM clause. Use comma as separator and typically includeWHERE clause to specify join column(s).Pearson Education © 2009Multi-Table Queries 12 Also possible to use an alias for a table named inFROM clause. Alias is separated from table name with a space. Alias can be used to qualify column names whenthere is ambiguity.Pearson Education © 2009Multi-Table Queries 23/11/20177 13List names of all clients who have viewed aproperty along with any comment supplied.SELECT c.clientNo, fName, lName,propertyNo, commentFROM Client c, Viewing vWHERE c.clientNo = v.clientNo;Pearson Education © 2009Example 6.24 Simple Join 14 Only those rows from both tables that haveidentical values in the clientNo columns(c.clientNo = v.clientNo) are included in result. Equivalent to equi-join in relational algebra.Pearson Education © 2009Example 6.24 Simple Join 23/11/20178 15 SQL provides alternative ways to specify joins:FROM Client c JOIN Viewing v ON c.clientNo = v.clientNoFROM Client JOIN Viewing USING clientNoFROM Client NATURAL JOIN Viewing In each case, FROM replaces original FROM andWHERE. However, first produces table with twoidentical clientNo columns.Pearson Education © 2009Alternative JOIN Constructs 16For each branch, list numbers and names ofstaff who manage properties, and propertiesthey manage.SELECT s.branchNo, s.staffNo, fName, lName,propertyNoFROM Staff s, PropertyForRent pWHERE s.staffNo = p.staffNoORDER BY s.branchNo, s.staffNo, propertyNo;Pearson Education © 2009Example 6.25 Sorting a join 23/11/20179 17Pearson Education © 2009Example 6.25 Sorting a join 18For each branch, list staff who manageproperties, including city in which branch islocated and properties they manage.SELECT b.branchNo, b.city, s.staffNo, fName, lName,propertyNoFROM Branch b, Staff s, PropertyForRent pWHERE b.branchNo = s.branchNo ANDs.staffNo = p.staffNoORDER BY b.branchNo, s.staffNo, propertyNo;Pearson Education © 2009Example 6.26 Three Table Join 23/11/201710 19 Alternative formulation for FROM and WHERE:FROM (Branch b JOIN Staff s USING branchNo) ASbs JOIN PropertyForRent p USING staffNoPearson Education © 2009Example 6.26 Three Table Join 20Find number of properties handled by each staffmember.SELECT s.branchNo, s.staffNo, COUNT(*) AS myCountFROM Staff s, PropertyForRent pWHERE s.staffNo = p.staffNoGROUP BY s.branchNo, s.staffNoORDER BY s.branchNo, s.staffNo;Pearson Education © 2009Example 6.27 Multiple Grouping Columns 23/11/201711 21Pearson Education © 2009Example 6.27 Multiple Grouping Columns 22Procedure for generating results of a join are:1. Form Cartesian product of the tables named inFROM clause.2. If there is a WHERE clause, apply the searchcondition to each row of the product table,retaining those rows that satisfy the condition.3. For each remaining row, determine value of eachitem in SELECT list to produce a single row inresult table.Pearson Education © 2009Computing a Join 23/11/201712 234. If DISTINCT has been specified, eliminate anyduplicate rows from the result table.6. If there is an ORDER BY clause, sort result tableas required. SQL provides special format of SELECT forCartesian product:SELECT [DISTINCT | ALL] {* | columnList}FROM Table1 CROSS JOIN Table2Pearson Education © 2009Computing a Join 24If one row of a joined table is unmatched,row is omitted from result table.Outer join operations retain rows that donot satisfy the join condition.Consider following tables:Pearson Education © 2009Outer Joins 23/11/201713 25 The (inner) join of these two tables:SELECT b.*, p.*FROM Branch1 b, PropertyForRent1 pWHERE b.bCity = p.pCity;Pearson Education © 2009Outer Joins 26 Result table has two rows where cities are same. There are no rows corresponding to branches inBristol and Aberdeen. To include unmatched rows in result table, usean Outer join.Pearson Education © 2009Outer Joins 23/11/201714 27List branches and properties that are in samecity along with any unmatched branches.SELECT b.*, p.*FROM Branch1 b LEFT JOINPropertyForRent1 p ON b.bCity = p.pCity;Pearson Education © 2009Example 6.28 Left Outer Join 28 Includes those rows of first (left) table unmatchedwith rows from second (right) table. Columns from second table are filled withNULLs.Pearson Education © 2009Example 6.28 Left Outer Join 23/11/201715 29List branches and properties in same city and anyunmatched properties.SELECT b.*, p.*FROM Branch1 b RIGHT JOINPropertyForRent1 p ON b.bCity = p.pCity;Pearson Education © 2009Example 6.29 Right Outer Join 30 Right Outer join includes those rows of second(right) table that are unmatched with rows fromfirst (left) table. Columns from first table are filled with NULLs.Pearson Education © 2009Example 6.29 Right Outer Join 23/11/201716 31List branches and properties in same city andany unmatched branches or properties.SELECT b.*, p.*FROM Branch1 b FULL JOINPropertyForRent1 p ON b.bCity = p.pCity;Pearson Education © 2009Example 6.30 Full Outer Join 32 Includes rows that are unmatched in both tables. Unmatched columns are filled with NULLs.Pearson Education © 2009Example 6.30 Full Outer Join 23/11/201717 33 EXISTS and NOT EXISTS are for use only withsubqueries. Produce a simple true/false result. True if and only if there exists at least one row inresult table returned by subquery. False if subquery returns an empty result table. NOT EXISTS is the opposite of EXISTS.Pearson Education © 2009EXISTS and NOT EXISTS 34 As (NOT) EXISTS check only for existence or nonexistence of rows in subquery result table,subquery can contain any number of columns. Common for subqueries following (NOT) EXISTSto be of form:(SELECT * …)Pearson Education © 2009EXISTS and NOT EXISTS 23/11/201718 35Find all staff who work in a London branch.SELECT staffNo, fName, lName, positionFROM Staff sWHERE EXISTS(SELECT *FROM Branch bWHERE s.branchNo = b.branchNo ANDcity = ‘London’);Pearson Education © 2009Example 6.31 Query using EXISTS 36Pearson Education © 2009Example 6.31 Query using EXISTS 23/11/201719 37 Note, search condition s.branchNo = b.branchNois necessary to consider correct branch record foreach member of staff. If omitted, would get all staff records listed outbecause subquery:SELECT * FROM Branch WHERE city=‘London’ would always be true and query would be:SELECT staffNo, fName, lName, position FROM StaffWHERE true;Pearson Education © 2009Example 6.31 Query using EXISTS 38 Could also write this query using join construct:SELECT staffNo, fName, lName, positionFROM Staff s, Branch bWHERE s.branchNo = b.branchNo ANDcity = ‘London’;Pearson Education © 2009Example 6.31 Query using EXISTS 23/11/201720 39 Can use normal set operations of Union,Intersection, and Difference to combine results oftwo or more queries into a single result table. Union of two tables, A and B, is table containingall rows in either A or B or both. Intersection is table containing all rows commonto both A and B. Difference is table containing all rows in A butnot in B. Two tables must be union compatible.Pearson Education © 2009Union, Intersect, and Difference (Except) 40 Format of set operator clause in each case is:op [ALL] [CORRESPONDING [BY {column1 [, …]}]] If CORRESPONDING BY specified, set operationperformed on the named column(s). If CORRESPONDING specified but not BYclause, operation performed on common columns. If ALL specified, result can include duplicate rows.Pearson Education © 2009Union, Intersect, and Difference (Except) 23/11/201721 41Pearson Education © 2009Union, Intersect, and Difference (Except) 42List all cities where there is either a branch officeor a property.(SELECT cityFROM BranchWHERE city IS NOT NULL) UNION(SELECT cityFROM PropertyForRentWHERE city IS NOT NULL);Pearson Education © 2009Example 6.32 Use of UNION 23/11/201722 43 Or(SELECT *FROM BranchWHERE city IS NOT NULL)UNION CORRESPONDING BY city(SELECT *FROM PropertyForRentWHERE city IS NOT NULL);Pearson Education © 2009Example 6.32 Use of UNION 44 Produces result tables from both queries andmerges both tables together.Pearson Education © 2009Example 6.32 Use of UNION 23/11/201723 45List all cities where there is both a branch officeand a property.(SELECT city FROM Branch)INTERSECT(SELECT city FROM PropertyForRent);Pearson Education © 2009Example 6.33 Use of INTERSECT 46 Or(SELECT * FROM Branch)INTERSECT CORRESPONDING BY city(SELECT * FROM PropertyForRent);Pearson Education © 2009Example 6.33 Use of INTERSECT 23/11/201724 47 Could rewrite this query without INTERSECToperator:SELECT b.cityFROM Branch b PropertyForRent pWHERE b.city = p.city; Or:SELECT DISTINCT city FROM Branch bWHERE EXISTS(SELECT * FROM PropertyForRent pWHERE p.city = b.city);Pearson Education © 2009Example 6.33 Use of INTERSECT 48List of all cities where there is a branch office butno properties.(SELECT city FROM Branch)EXCEPT(SELECT city FROM PropertyForRent); Or(SELECT * FROM Branch)EXCEPT CORRESPONDING BY city(SELECT * FROM PropertyForRent);Pearson Education © 2009Example 6.34 Use of EXCEPT 23/11/201725 49 Could rewrite this query without EXCEPT:SELECT DISTINCT city FROM BranchWHERE city NOT IN(SELECT city FROM PropertyForRent); OrSELECT DISTINCT city FROM Branch bWHERE NOT EXISTS(SELECT * FROM PropertyForRent pWHERE p.city = b.city);Pearson Education © 2009Example 6.34 Use of EXCEPT 50INSERT INTO TableName [ (columnList) ]VALUES (dataValueList) columnList is optional; if omitted, SQL assumes alist of all columns in their original CREATETABLE order. Any columns omitted must have been declared asNULL when table was created, unless DEFAULTwas specified when creating column.Pearson Education © 2009INSERT 23/11/201726 51 dataValueList must match columnList as follows:– number of items in each list must be same;– must be direct correspondence in position ofitems in two lists;– data type of each item in dataValueList mustbe compatible with data type ofcorresponding column.Pearson Education © 2009INSERT 52Insert a new row into Staff table supplying datafor all columns.INSERT INTO StaffVALUES (‘SG16’, ‘Alan’, ‘Brown’, ‘Assistant’,‘M’, Date‘1957-05-25’, 8300, ‘B003’);Pearson Education © 2009Example 6.35 INSERT … VALUES 23/11/201727 53Insert a new row into Staff table supplying datafor all mandatory columns.INSERT INTO Staff (staffNo, fName, lName,position, salary, branchNo)VALUES (‘SG44’, ‘Anne’, ‘Jones’,‘Assistant’, 8100, ‘B003’); OrINSERT INTO StaffVALUES (‘SG44’, ‘Anne’, ‘Jones’, ‘Assistant’, NULL,NULL, 8100, ‘B003’);Pearson Education © 2009Example 6.36 INSERT using Defaults 54 Second form of INSERT allows multiple rows tobe copied from one or more tables to another:INSERT INTO TableName [ (columnList) ]SELECT …Pearson Education © 2009INSERT … SELECT 23/11/201728 55Assume there is a table StaffPropCount thatcontains names of staff and number of propertiesthey manage:StaffPropCount(staffNo, fName, lName, propCnt)Populate StaffPropCount using Staff andPropertyForRent tables.Pearson Education © 2009Example 6.37 INSERT … SELECT 56INSERT INTO StaffPropCount(SELECT s.staffNo, fName, lName, COUNT(*)FROM Staff s, PropertyForRent pWHERE s.staffNo = p.staffNoGROUP BY s.staffNo, fName, lName)UNION(SELECT staffNo, fName, lName, 0FROM StaffWHERE staffNo NOT IN(SELECT DISTINCT staffNoFROM PropertyForRent));Pearson Education © 2009Example 6.37 INSERT … SELECT 23/11/201729 57 If second part of UNION is omitted, excludes thosestaff who currently do not manage any properties.Pearson Education © 2009Example 6.37 INSERT … SELECT 58UPDATE TableNameSET columnName1 = dataValue1[, columnName2 = dataValue2…][WHERE searchCondition] TableName can be name of a base table or anupdatable view. SET clause specifies names of one or morecolumns that are to be updated.Pearson Education © 2009UPDATE 23/11/201730 59 WHERE clause is optional:– if omitted, named columns are updated for allrows in table;– if specified, only those rows that satisfysearchCondition are updated. New dataValue(s) must be compatible with datatype for corresponding column.Pearson Education © 2009UPDATE 60Give all staff a 3% pay increase.UPDATE StaffSET salary = salary*1.03;Give all Managers a 5% pay increase.UPDATE StaffSET salary = salary*1.05WHERE position = ‘Manager’;Pearson Education © 2009Example 6.38/39 UPDATE All Rows 23/11/201731 61Promote David Ford (staffNo=‘SG14’) toManager and change his salary to £18,000.UPDATE StaffSET position = ‘Manager’, salary = 18000WHERE staffNo = ‘SG14’;Pearson Education © 2009Example 6.40 UPDATE Multiple Columns 62DELETE FROM TableName[WHERE searchCondition] TableName can be name of a base table or anupdatable view. searchCondition is optional; if omitted, all rowsare deleted from table. This does not delete table.If search_condition is specified, only those rowsthat satisfy condition are deleted.Pearson Education © 2009DELETE 23/11/201732 63Delete all viewings that relate to property PG4.DELETE FROM ViewingWHERE propertyNo = ‘PG4’;Delete all records from the Viewing table.DELETE FROM Viewing;Pearson Education © 2009Example 6.41/42 DELETE Specific Rows 64 QBE – Applies to MS Access DBMS You may want to read further on– The main features of Query-By-Example(QBE).– The types of queries provided by the MicrosoftAccess DBMS QBE facility.– How to use QBE to build queries to select fieldsand records.– How to use QBE to target single or multipletables.Pearson Education © 2009QBE 23/11/201733 65ViewDynamic result of one or more relationaloperations operating on base relations toproduce another relation. Virtual relation that does not necessarily actuallyexist in the database but is produced upon request,at time of request.Pearson Education © 2009Views 66 Contents of a view are defined as a query on oneor more base relations. With view resolution, any operations on view areautomatically translated into operations onrelations from which it is derived. With view materialization, the view is stored as atemporary table, which is maintained as theunderlying base tables are updated.Pearson Education © 2009Views 23/11/201734 67CREATE VIEW ViewName [ (newColumnName [,…]) ]AS subselect[WITH [CASCADED | LOCAL] CHECK OPTION]Can assign a name to each column in view.If list of column names is specified, it must havesame number of items as number of columnsproduced by subselect.If omitted, each column takes name ofcorresponding column in subselect.Pearson Education © 2009SQL – CREATE VIEW 68 List must be specified if there is any ambiguity ina column name. The subselect is known as the defining query. WITH CHECK OPTION ensures that if a rowfails to satisfy WHERE clause of defining query, itis not added to underlying base table. Need SELECT privilege on all tables referenced insubselect and USAGE privilege on any domainsused in referenced columns.Pearson Education © 2009SQL – CREATE VIEW 23/11/201735 69Create view so that manager at branch B003can only see details for staff who work in his orher office.CREATE VIEW Manager3StaffWHERE branchNo = ‘B003’;Pearson Education © 2009Example 7.3 – Create Horizontal View AS SELECT *FROM Staff 70Create view of staff details at branch B003excluding salaries.CREATE VIEW Staff3AS SELECT staffNo, fName, lName, position, sexFROM StaffWHERE branchNo = ‘B003’;Pearson Education © 2009Example 7.4 – Create Vertical View 23/11/201736 71Create view of staff who manage properties forrent, including branch number they work at, staffnumber, and number of properties they manage.CREATE VIEW StaffPropCnt (branchNo, staffNo, cnt)AS SELECT s.branchNo, s.staffNo, COUNT(*)FROM Staff s, PropertyForRent pWHERE s.staffNo = p.staffNoGROUP BY s.branchNo, s.staffNo;Pearson Education © 2009Example 7.5 – Grouped and Joined Views 72Pearson Education © 2009Example 7.3 – Grouped and Joined Views 23/11/201737 73DROP VIEW ViewName [RESTRICT | CASCADE] Causes definition of view to be deleted fromdatabase. For example:DROP VIEW Manager3Staff;Pearson Education © 2009SQL – DROP VIEW 74 With CASCADE, all related dependent objects aredeleted; i.e. any views defined on view beingdropped. With RESTRICT (default), if any other objectsdepend for their existence on continued existenceof view being dropped, command is rejected.Pearson Education © 2009SQL – DROP VIEW 23/11/201738 75 Data independence Currency Improved security Reduced complexity Convenience Customization Data integrityPearson Education © 2009Advantages of Views 76 Update restriction Structure restriction PerformancePearson Education © 2009Disadvantages of Views 23/11/201739 Laboratory Lab SQL Server
