1 / 65

SQL (Chapter 2: Simple queries; Chapter 7 and 8: Nested and DML queries)

SQL (Chapter 2: Simple queries; Chapter 7 and 8: Nested and DML queries). Many of the examples in this document are based on the tables in the next slide. These are similar to the tables in the test database CS451 Supplier.

analiese-nicholas
Download Presentation

SQL (Chapter 2: Simple queries; Chapter 7 and 8: Nested and DML queries)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL (Chapter 2: Simple queries; Chapter 7 and 8: Nested and DML queries) • Many of the examples in this document are based on the tables in the next slide. • These are similar to the tables in the test database CS451Supplier. • Just about any query involving two tables connected by a many-many relationship will be similar to something from the following slides (adapted from a book by C. J. Date).

  2. S P SP

  3. SQL: one or more tables  view or logical table. • SQL: Query • View: Answer or resulting table

  4. Nice reference at [http://www.w3schools.com/sql] • Book has numerous examples in Chapter 2. • Book also shows how to use SQL in Access, Oracle, and MySQL; • just go through them for the SQL examples; we’ll describe how to use SQL in SQL-Server.

  5. General format: Select stuff From table(s) Where conditions stuff can be specific attributes or the character * to indicate all attributes. The result is a list of all rows that satisfy the conditions.

  6. Using SQL Server to see result of SQL commands • Using Management Studio, select the desired database • Right click on it and select new Query • Type your SQL command into the resulting text window • Click on the “! Execute” option just above the window’s tab.

  7. Row subsets • List all suppliers with a status larger than 20 Select * From S Where status > 20

  8. NOTE: if status is NULL then status > 20; status = 20; and status < 20 are all false!! • However, an SQL query could ask whether status is equal to NULL and, if it was, would return True.

  9. Column Subsets combined with row subsets: • List the name and status of each London supplier Select Sname, Status From S Where City = ‘London’ (may have to retype the quote marks if copying the above and pasting into SQL Server)

  10. All SQL commands generate a logical table. • The table may be displayed immediately or buffered internally depending on the context in which it is used.

  11. Results may contain duplicate rows – it’s time consuming to remove them Select city From S Where status = 40 Can put distinct after the Select to eliminate the duplicate rows.

  12. Compound conditions: • List the numbers of Paris suppliers with a status bigger than 20 Select S# From S Where city = ‘Paris’ and Status > 20; Could use OR also

  13. Ordering: • List the number and status of Paris suppliers in descending order of status. Select S#, status From S Where city = ‘Paris’ Order by Status Desc Could also use ASC or just leave out.

  14. Aggregates: • How many suppliers supply part ‘P2’ Select count(*) From SP Where P# = ‘P2’ If we left out the Where clause, we get a count of ALL entries.

  15. What is the average quantity supplied by ‘P2’ suppliers Select avg(qty) From SP Where P# = ‘P2’ Could use sum, max, min also, among others How can we get the average quantity for all parts?

  16. Group by options Select P#, avg(Qty) From SP Group by P# Gets an average for each P# and display each in table format.

  17. Results of the previous query displays no column name for the avg. Can change this using Select P#, avg(Qty) as 'average Quantity' From SP Group by P# ‘average Quantity’ becomes a column name in the result

  18. Find the supplier with the largest status value Select S# From S Where status = ( Select MAX (Status) from S) This is an example of a nested query – a select within a select.

  19. Having Clause • The HAVING clause was added to SQL because the WHERE keyword is limited in its use with aggregate functions . • [http://www.w3schools.com/SQL/sql_having.asp]

  20. SELECT attribute, function(attribute)FROM tableWHERE conditionGROUP BY attribute HAVING function(attribute) operator value

  21. List suppliers with an average quantity above 200 SELECT S#, avg(qty) as average FROM SP group by S# having avg(qty) >200

  22. List suppliers with above average quantity SELECT S#, avg(qty) as ‘average’ FROM SP group by S# having avg(qty) > (SELECT avg(qty) FROM SP)

  23. Like keyword: allows queries to look for strings similar to (but not equal to) a specified criterion Select stuff from tables where attribute like somestring

  24. Common use similar to that below Select * from S where Sname like 'Ada%‘ % is a wildcard. Looks for any name that starts with ‘Ada’ Can use % prior to or after a string

  25. Can replace ‘%’ with ‘_’ • ‘_’ is a wildcard for just ONE character. • Select * from S where Sname like 'Ada_‘ Looks for a string that starts with ‘Ada’ and contains just one more character NOTE: different DBMS’s may use different symbols!

  26. Multitable queries: • Find the names of suppliers who supply part ‘P2’ Select Sname From S, SP Where S.S# = SP.S# and SP.P# = ‘P2’

  27. How do you negate this? • That is: Find the names of suppliers who do NOT supply part ‘P2’.

  28. Nested queries: • Select Sname From S Where S# IN ( Select S# From SP Where P# = ‘P2’)

  29. Or • Select Sname From S Where EXISTS ( Select * From SP Where S# = S.S# and P# = ‘P2’)

  30. EXISTS is an existential qualifier • NOTE: queries involving IN can be converted to queries involving EXISTS but not necessarily vice-versa.

  31. NOTE: In theory there’s no inherent advantage to any of the above nested queries or the previous JOIN-type query. • A good optimizer will treat all equally. • HOWEVER not all database systems have comparable or good optimizers!! • Some report poor performance with nested queries on mySQL [http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html]

  32. Find the names of suppliers who do NOT supply part ‘P2’. • You can negate nested queries by using NOT IN or NOT EXISTS

  33. Find the names of suppliers who do NOT supply part ‘P2’. Select Sname From S Where S# Not IN ( Select S# From SP Where P# = ‘P2’)

  34. Select Sname From S Where not EXISTS ( Select * From SP Where S# = S.S# and P# = ‘P2’)

  35. List suppliers who supply at least one red part. Select Sname from S, SP, P where S.S#=SP.S# and SP.P#=P.P# and P.color=‘Red’

  36. Multiple nested queries: List suppliers who supply at least one red part. • Select Sname From S Where S# IN ( Select S# from SP where P# IN ( Select P# from P where color = ‘Red’))

  37. Which is better? • The former is probably more clear – but maybe not to all. • The latter allows variations more easily.

  38. Negating multiple nested queries: • NOT prior to first IN: List suppliers who do NOT supply a red part. • NOT prior to second IN: List suppliers who supply parts that are NOT red (non-red parts). • NOT prior to both: List suppliers who do NOT supply parts that are NOT red • i.e. ONLY red parts. • NOTE: Two negations do NOT cancel

  39. Select Sname From S Where EXISTS ( Select * from P where color=’Red’ and EXISTS ( Select * from SP where SP.S#=S.S# and P.P#=SP.P#))

  40. Negating multiple nested queries: • NOT prior to first EXISTS: List suppliers who do NOT supply a red part. • NOT prior to second EXISTS: List suppliers for which there is a red part they don’t supply. • NOT prior to both: List suppliers for which there is no red part they do not supply (i.e. supply ALL red parts). • NOTE: Two negations do NOT cancel

  41. List supplier names of those who supply all parts: • An alternative form is: List supplier names of those for whom there is no part they do not supply.

  42. Select Sname From S Where NOT EXISTS ( Select * from P where NOT EXISTS ( Select * from SP where SP.S# = S.S# and SP.P# = P.P#))

  43. Nested queries and multiple queries on the same table: • Find supplier numbers for suppliers who supply at least all those part supplied by S2. NOTE: Nothing outside of SP is needed. • Create view Temp as ( Select P# from SP where S# = ‘S2’)

  44. Find numbers of suppliers who supply all parts in temp • Select Distinct S# From SP SPX Where NOT EXISTS ( Select * from Temp where NOT EXISTS ( Select * from SP SPY where SPY.S# = SPX.S# and SPY.P# = Temp.P#))

  45. NOTE: if both of the previous SQL commands are in the same SQL Server window, place the word go between then. • It’s a syntax thing.

  46. Alternative with NO temp table: • Select Distinct S# From SP SPX Where NOT EXISTS ( select * from SP SPY where S# = ‘S2’ and NOT EXISTS ( Select * from SP where S# = SPX.S# and P# = SPY.P#))

  47. This uses aliases SPX and SPY to be able to state conditions related to a specific selection when there is more than one selection on the same table.

  48. Alternative syntax for SQL and join operations • A join operation combines two entries from two (or more) tables based on a condition, typically involving the equality of a common attribute from each table. • See examples starting on page 261. • Sometimes these are called inner joins.

  49. A join or inner join operation concatenates two entries when a common attribute from each has the same value. • However consider: select * from S, SP where S.S# = SP.S#

  50. Where is S8? • If there is an S8 that supplies NO parts, then S8 is not listed. • If there is NO S8 then S8 is not listed. • Should these be distinguished? • Perhaps with an indication that S8 supplies NO partsif there IS an S8?

More Related