1 / 89

(T) - SQL SELECT

(T) - SQL SELECT. The basics of getting data. Peter Gfader. Specializes in C# and .NET (Java not anymore) Testing Automated tests Agile, Scrum Certified Scrum Trainer Technology aficionado Silverlight ASP.NET Windows Forms. Agenda. Part 1 - Basic SQL Part 2 - Intermediate

leane
Télécharger la présentation

(T) - SQL SELECT

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. (T) - SQL SELECT The basics of getting data

  2. Peter Gfader • Specializes in • C# and .NET (Java not anymore) • TestingAutomated tests • Agile, ScrumCertified Scrum Trainer • Technology aficionado • Silverlight • ASP.NET • Windows Forms

  3. Agenda • Part 1 - Basic SQL • Part 2 - Intermediate • Part 3 - Advanced SQL

  4. The simplest example SELECT * FROM Production.Product SELECT *FROM [AdventureWorks].[Production].[Product] SELECT *FROM [AdventureWorks].[Production].[Product]

  5. How to read SELECT *FROM [AdventureWorks].[Production].[Product]WHERE WHERE Name ='Road End Caps'

  6. SQL SELECT Overview SELECT [DISTINCT | ALL] <column-list> FROM <table-names> [WHERE <condition>] [ORDER BY <column-list>] [GROUP BY <column-list>] [HAVING <condition>] • ([]- optional, | - or)

  7. Example Tables Grade ID Code Mark S103 DBS 72 S103 IAI 58 S104 PR1 68 S104 IAI 65 S106 PR2 43 S107 PR1 76 S107 PR2 60 S107 IAI 35 Course Code Title DBS Database Systems PR1 Programming 1 PR2 Programming 2 IAI Intro to AI Student ID First Last S103 John Smith S104 Mary Jones S105 Jane Brown S106 Mark Jones S107 John Brown

  8. SELECT ALL Last FROM Student SELECT DISTINCT Last FROM Student DISTINCT and ALL Last Smith Jones Brown Jones Brown Last Smith Jones Brown • Sometimes you end up with duplicate entries • Using DISTINCT removes duplicates • Using ALL retains them - this is the default

  9. Usually you don’t want all the rows A WHERE clause restricts the rows that are returned It takes the form of a condition - only those rows that satisfy the condition are returned Example conditions: Mark < 40 First = ‘John’ First <> ‘John’ First = Last (First = ‘John’) AND (Last = ‘Smith’) (Mark < 40) OR (Mark > 70) WHERE Clauses

  10. SELECT * FROM Grade WHERE Mark >= 60 SELECT DISTINCT ID FROM Grade WHERE Mark >= 60 ID S103 S104 S107 WHERE Examples ID Code Mark S103 DBS 72 S104 PR1 68 S104 IAI 65 S107 PR1 76 S107 PR2 60

  11. Given the table Write an SQL query to find a list of the ID numbers and marks in IAI of students who have passed (scored 40 or higher) IAI Grade ID Code Mark S103 DBS 72 S103 IAI 58 S104 PR1 68 S104 IAI 65 S106 PR2 43 S107 PR1 76 S107 PR2 60 S107 IAI 35 WHERE Example ID Mark S103 58 S104 65

  12. One Solution We only want the ID and Mark, not the Code Single quotes around the string We’re only interested in IAI We’re looking for entries with pass marks SELECT ID, Mark FROM Grade WHERE (Code = ‘IAI’) AND (Mark >= 40)

  13. Often you need to combine information from two or more tables You can get the effect of a product by using SELECT * FROM Table1, Table2... If the tables have columns with the same name ambiguity results You resolve this by referencing columns with the table name TableName.Column SELECT from Multiple Tables

  14. SELECT from Multiple Tables Grade ID Code Mark S103 DBS 72 S103 IAI 58 S104 PR1 68 S104 IAI 65 S106 PR2 43 S107 PR1 76 S107 PR2 60 S107 IAI 35 Student ID First Last S103 John Smith S104 Mary Jones S105 Jane Brown S106 Mark Jones S107 John Brown SELECT First, Last, Mark FROM Student, Grade WHERE (Student.ID = Grade.ID) AND (Mark >= 40)

  15. Are matched with the first entry from the Student table... All of the entries from the Grade table And then with the second… and so on SELECT from Multiple Tables SELECT ... FROM Student, Grade WHERE... ID First Last ID Code Mark S103 John Smith S103 DBS 72 S103 John Smith S103 IAI 58 S103 John Smith S104 PR1 68 S103 John Smith S104 IAI 65 S103 John Smith S106 PR2 43 S103 John Smith S107 PR1 76 S103 John Smith S107 PR2 60 S103 John Smith S107 IAI 35 S104 Mary Jones S103 DBS 72 S104 Mary Jones S103 IAI 58 S104 Mary Jones S104 PR1 68 S104 Mary Jones S104 IAI 65 S104 Mary Jones S106 PR2 43

  16. Student.ID Grade.ID SELECT from Multiple Tables SELECT ... FROM Student, Grade WHERE (Student.ID = Grade.ID) AND ... ID First Last ID Code Mark S103 John Smith S103 DBS 72 S103 John Smith S103 IAI 58 S104 Mary Jones S104 PR1 68 S104 Mary Jones S104 IAI 65 S106 Mark Jones S106 PR2 43 S107 John Brown S107 PR1 76 S107 John Brown S107 PR2 60 S107 John Brown S107 IAI 35

  17. SELECT from Multiple Tables SELECT ... FROM Student, Grade WHERE (Student.ID = Grade.ID) AND (Mark >= 40) ID First Last ID Code Mark S103 John Smith S103 DBS 72 S103 John Smith S103 IAI 58 S104 Mary Jones S104 PR1 68 S104 Mary Jones S104 IAI 65 S106 Mark Jones S106 PR2 43 S107 John Brown S107 PR1 76 S107 John Brown S107 PR2 60

  18. SELECT from Multiple Tables SELECT First, Last, Mark FROM Student, Grade WHERE (Student.ID = Grade.ID) AND (Mark >= 40) First Last Mark John Smith 72 John Smith 58 Mary Jones 68 Mary Jones 65 Mark Jones 43 John Brown 76 John Brown 60

  19. When selecting from multiple tables you almost always use a WHERE clause to find entries with common values SELECT * FROM Student, Grade, Course WHERE Student.ID = Grade.ID AND Course.Code = Grade.Code SELECT from Multiple Tables

  20. ID First Last ID Code Mark Code Title S103 John Smith S103 DBS 72 DBS Database Systems S103 John Smith S103 IAI 58 IAI Intro to AI S104 Mary Jones S104 PR1 68 PR1 Programming 1 S104 Mary Jones S104 IAI 65 IAI Intro to AI S106 Mark Jones S106 PR2 43 PR2 Programming 2 S107 John Brown S107 PR1 76 PR1 Programming 1 S107 John Brown S107 PR2 60 PR2 Programming 2 S107 John Brown S107 IAI 35 IAI Intro to AI SELECT from Multiple Tables Grade Student Course Student.ID = Grade.ID Course.Code = Grade.Code

  21. JOINs can be used to combine tables There are many types of JOIN CROSS JOIN INNER JOIN NATURAL JOIN OUTER JOIN OUTER JOINs are linked with NULLs - more later A CROSS JOIN B returns all pairs of rows from A and B A NATURAL JOIN B returns pairs of rows with common values for identically named columns and without duplicating columns A INNER JOIN B returns pairs of rows satisfying a condition JOINs

  22. SELECT * FROM Student CROSS JOIN Enrolment Student ID Name 123 John 124 Mary 125 Mark 126 Jane Enrolment ID Code 123 DBS 124 PRG 124 DBS 126 PRG CROSS JOIN ID Name ID Code 123 John 123 DBS 124 Mary 123 DBS 125 Mark 123 DBS 126 Jane 123 DBS 123 John 124 PRG 124 Mary 124 PRG 125 Mark 124 PRG 126 Jane 124 PRG 123 John 124 DBS 124 Mary 124 DBS

  23. SELECT * FROM Student NATURAL JOIN Enrolment Student ID Name 123 John 124 Mary 125 Mark 126 Jane ID Name 123 John 124 Mary 124 Mary 126 Jane Enrolment ID Code 123 DBS 124 PRG 124 DBS 126 PRG NATURAL JOIN Code DBS PRG DBS PRG

  24. SELECT * FROM A CROSS JOIN B is the same as SELECT * FROM A, B SELECT * FROM A NATURAL JOIN B is the same as SELECT A.col1,… A.coln, [and all other columns apart from B.col1,…B.coln] FROM A, B WHERE A.col1 = B.col1 AND A.col2 = B.col2 ...AND A.coln = B.col.n (this assumes that col1… coln in A and B have common names) CROSS and NATURAL JOIN

  25. INNER JOINs specify a condition which the pairs of rows satisfy SELECT * FROM A INNER JOIN B ON <condition> Can also use SELECT * FROM A INNER JOIN B USING (col1, col2,…) Chooses rows where the given columns are equal INNER JOIN

  26. SELECT * FROM Student INNER JOIN Enrolment USING (ID) Student ID Name 123 John 124 Mary 125 Mark 126 Jane Enrolment ID Code 123 DBS 124 PRG 124 DBS 126 PRG INNER JOIN ID Name ID Code 123 John 123 DBS 124 Mary 124 PRG 124 Mary 124 DBS 126 Jane 126 PRG

  27. SELECT * FROM Buyer INNER JOIN Property ON Price <= Budget Buyer Name Budget Smith 100,000 Jones 150,000 Green 80,000 Property Address Price 15 High St 85,000 12 Queen St 125,000 87 Oak Row 175,000 INNER JOIN Name Budget Address Price Smith 100,000 15 High St 85,000 Jones 150,000 15 High St 85,000 Jones 150,000 12 Queen St 125,000

  28. SELECT * FROM A INNER JOIN B ON <condition> is the same as SELECT * FROM A, B WHERE <condition> SELECT * FROM A INNER JOIN B USING(col1, col2,...) is the same as SELECT * FROM A, B WHERE A.col1 = B.col1 AND A.col2 = B.col2 AND ... INNER JOIN

  29. JOINs (so far) are not needed You can have the same effect by selecting from multiple tables with an appropriate WHERE clause So should you use JOINs or not? Yes, because They often lead to concise queries NATURAL JOINs are very common No, because Support for JOINs varies a fair bit among SQL dialects JOINs vs WHERE Clauses

  30. When writing queries There are often many ways to write the query You should worry about being correct, clear, and concise in that order Don’t worry about being clever or efficient Most DBMSs have query optimisers These take a user’s query and figure out how to efficiently execute it A simple query is easier to optimise Writing Queries

  31. How to read SELECT *FROM [AdventureWorks].[Production].[Product]WHERE WHERE Name ='Road End Caps'

  32. Exam!!!!

  33. Products ProductSubcategory ProductReview

  34. Exam! Find a list of all categories (1 mark) Find a list of products that are black (2 marks) Find a list of the product names of those products who have a review rating greater than 4 (4 marks) Make a nice list of categories and subcategories (2 marks)

  35. Part 2 - Intermediate

  36. Aliases rename columns or tables to Make names more meaningful Make names shorter and easier to type Resolve ambiguous names Two forms: Column alias SELECT column AS newName... Table alias SELECT ... FROM table AS newName This ‘AS’ is optional, but Oracle doesn’t accept it at all Aliases More SQL SELECT

  37. SELECT E.ID AS empID, E.Name, W.Dept FROM Employee E WorksIn W WHERE E.ID = W.ID Employee ID Name 123 John 124 Mary WorksIn ID Dept 123 Marketing 124 Sales 124 Marketing Example More SQL SELECT

  38. SELECT E.ID AS empID, E.Name, W.Dept FROM Employee E WorksIn W WHERE E.ID = W.ID empID Name Dept 123 John Marketing 124 Mary Sales 124 Mary Marketing Example More SQL SELECT

  39. Aliases can be used to copy a table, so that it can be combined with itself: SELECT A.Name FROM Employee A, Employee B WHERE A.Dept=B.Dept AND B.Name=‘Andy’ Employee Name Dept John Marketing Mary Sales Peter Sales Andy Marketing Anne Marketing Aliases and ‘Self-Joins’ More SQL SELECT

  40. A Name Dept John Marketing Mary Sales Peter Sales Andy Marketing Anne Marketing B Name Dept John Marketing Mary Sales Peter Sales Andy Marketing Anne Marketing Aliases and Self-Joins Employee A Employee B More SQL SELECT

  41. Aliases and Self-Joins SELECT … FROM Employee A, Employee B … A.Name A.Dept B.Name B.Dept John Marketing John Marketing Mary Sales John Marketing Peter Sales John Marketing Andy Marketing John Marketing Anne Marketing John Marketing John Marketing Mary Sales Mary Sales Mary Sales Peter Sales Mary Sales Andy Marketing Mary Sales Anne Marketing Mary Sales More SQL SELECT

  42. Aliases and Self-Joins SELECT … FROM Employee A, Employee B WHERE A.Dept = B.Dept A.Name A.Dept B.Name B.Dept John Marketing John Marketing Andy Marketing John Marketing Anne Marketing John Marketing Mary Sales Mary Sales Peter Sales Mary Sales Mary Sales Peter Sales Peter Sales Peter Sales John Marketing Andy Marketing Andy Marketing Andy Marketing Anne Marketing Andy Marketing More SQL SELECT

  43. Aliases and Self-Joins SELECT … FROM Employee A, Employee B WHERE A.Dept = B.Dept AND B.Name = ‘Andy’ A.Name A.Dept B.Name B.Dept John Marketing Andy Marketing Andy Marketing Andy Marketing Anne Marketing Andy Marketing More SQL SELECT

  44. A.Name John Andy Anne Aliases and Self-Joins SELECT A.Name FROM Employee A, Employee B WHERE A.Dept = B.Dept AND B.Name = ‘Andy’ The result is the names of all employees who work in the same department as Andy. More SQL SELECT

  45. A SELECT statement can be nested inside another query to form a subquery The results of the subquery are passed back to the containing query E.g. get the names of people who are in Andy’s department: SELECT Name FROM Employee WHERE Dept = (SELECT Dept FROM Employee WHERE Name=‘Andy’) Subqueries More SQL SELECT

  46. SELECT Name FROM Employee WHERE Dept = (SELECT Dept FROM Employee WHERE Name=‘Andy’) First the subquery is evaluated, returning the value ‘Marketing’ This result is passed to the main query SELECT Name FROM Employee WHERE Dept = ‘Marketing’ Subqueries More SQL SELECT

  47. Often a subquery will return a set of values rather than a single value You can’t directly compare a single value to a set Options IN - checks to see if a value is in the set EXISTS - checks to see if the set is empty or not ALL/ANY - checks to see if a relationship holds for every/one member of the set Subqueries More SQL SELECT

  48. Using IN we can see if a given value is in a set of values NOT IN checks to see if a given value is not in the set The set can be given explicitly or from a subquery (NOT) IN SELECT <columns> FROM <tables> WHERE <value> IN <set> SELECT <columns> FROM <tables> WHERE <value> NOT IN <set> More SQL SELECT

  49. (NOT) IN SELECT * FROM Employee WHERE Department IN (‘Marketing’, ‘Sales’) Employee Name Department Manager John Marketing Chris Mary Marketing Chris Chris Marketing Jane Peter Sales Jane Jane Management Name Department Manager John Marketing Chris Mary Marketing Chris Chris Marketing Jane Peter Sales Jane More SQL SELECT

  50. (NOT) IN SELECT * FROM Employee WHERE Name NOT IN (SELECT Manager FROM Employee) Employee Name Department Manager John Marketing Chris Mary Marketing Chris Chris Marketing Jane Peter Sales Jane Jane Management More SQL SELECT

More Related