1 / 102

SQL (slides by Dr. Li Bai)

SQL (slides by Dr. Li Bai). SQL is based on relational algebra. An algebra is a system of operations where each operation creates an item of the same type . This means that every single SQL DML (data manipulation) command takes relations as inputs and gives relations as outputs.

zlhna
Download Presentation

SQL (slides by Dr. Li Bai)

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 (slides by Dr. Li Bai) • SQL is based on relational algebra. • An algebra is a system of operations where each operation creates an item of the same type. • This means that every single SQL DML (data manipulation) command takes relations as inputs and gives relations as outputs. • These outputs can then be used as inputs in another operation.

  2. Base Relations • Base Relations: Tables as stored on disk in the database. • Virtual Relations: Tables generated in the ether, derived by an SQL operation on base relations and other virtual relations. • Views: Virtual relations whose definition is stored by the DBMS.

  3. The SQL command you will use most often Queries a set of tables and returns results as a table Lots of options, we will look at many of them Usually more than one way to do any given query SQL’s SELECT is different from the relational algebra’s Select: SELECT in SQL has all the functionality of its equivalent in relational algebra But it is a bit different because SQL differs from the relational model, adding extra qualifiers… SELECT

  4. 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)

  5. 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 Example Tables

  6. ID S103 S104 S105 S106 S107 Sample SELECTs SELECT ID FROM Student

  7. ID First Last S103 John Smith S104 Mary Jones S105 Jane Brown S106 Mark Jones S107 John Brown Sample SELECTs SELECT * FROM Student

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

  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 Code Mark S103 DBS 72 S104 PR1 68 S104 IAI 65 S107 PR1 76 S107 PR2 60 ID S103 S104 S107 WHERE Examples

  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. 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 One Solution 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 First, Last, Mark FROM Student, Grade WHERE (Student.ID = Grade.ID) AND (Mark >= 40) 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 from Multiple Tables

  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. 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 Student.ID Grade.ID SELECT from Multiple Tables SELECT ... FROM Student, Grade WHERE (Student.ID = Grade.ID) AND ...

  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 You have joined tables by listing them with commas. 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 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 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 Enrolment ID Code 123 DBS 124 PRG 124 DBS 126 PRG CROSS JOIN

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

  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 * FROM A, B WHERE A.col1 = B.col1 AND A.col2 = B.col2 AND... Equivalencies…

  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 ID Name ID Code 123 John 123 DBS 124 Mary 124 PRG 124 Mary 124 DBS 126 Jane 126 PRG Enrolment ID Code 123 DBS 124 PRG 124 DBS 126 PRG INNER JOIN

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

  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 lead to better, conciser queries NATURAL JOINs are very common No, because Support for JOINs varies a fair bit among SQL dialects – it can be inconsistent. 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 users query and figure out how to efficiently execute it A simple query is easier to optimise We’ll look at some ways to improve efficiency later Writing Queries

  31. Act Name Sex Al M Uma F Jon M Samuel M Robert M Harvey M Scarlet F Direct Name Age Francis 67 Quentin 43 Steven 60 Woody 71 Work Actor Director Al Francis Uma Quentin Jon Quentin Jon Steven Samuel Quentin Robert Francis Harvey Quentin Harvey Francis Multiple Joins Example

  32. What is the sex of the actors who have worked with directors under the age of 65? Act Name Sex Al M Uma F Jon M Samuel M Robert M Harvey M Scarlet F Direct Name Age Francis 67 Quentin 43 Steven 60 Woody 71 Work Actor Director Al Francis Uma Quentin Jon Quentin Jon Steven Samuel Quentin Robert Francis Harvey Quentin Harvey Francis Multiple Join Query

  33. SELECT Act.Name, Act.Sex FROM Direct INNER JOIN Work ON (Direct.name = Work.director) INNER JOIN Act ON (Work.actor = Act.name) WHERE Direct.age < 65 Solution • What is the sex of the actors who have worked with directors under the age of 65?

  34. This looks like an intimidating query at first sight, but it can be built up step by step: Thinking with brackets SELECT Act.Name, Act.Sex FROM ( ... ) WHERE Direct.age < 65 SELECT Act.Name, Act.Sex FROM ( ( Direct INNER JOIN Work ON (Direct.name = Work.director) ) ... ) WHERE Direct.age < 65 SELECT Act.Name, Act.Sex FROM ( ( Direct INNER JOIN Work ON (Direct.name = Work.director) ) INNER JOIN Act ON (Work.actor = Act.name) ) WHERE Direct.age < 65

  35. 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... SQL says ‘AS’ is optional, but Oracle doesn’t even accept it. Aliases • Table alias • SELECT ... • FROM table • AS newName

  36. SELECT E.ID AS empID, E.Name, W.Dept FROM Employee E WorksIn W WHERE E.ID = W.ID ID Name 123 Tinky Winky 124 Dipsy 125 La La 126 Po WorksIn ID Dept 124 Marketing 125 Sales 125 Marketing Business Alias Example Employees

  37. SELECT E.ID AS empID, E.Name, W.Dept FROM Employee E WorksIn W WHERE E.ID = W.ID Business Alias Example result: ID Name Dept 124 Dipsy Marketing 125 La La Sales 125 La La Marketing

  38. 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... SQL says ‘AS’ is optional, but Oracle doesn’t even accept it. Aliases • Table alias • SELECT ... • FROM table • AS newName

  39. SELECT E.ID AS empID, E.Name, W.Dept FROM Employee E WorksIn W WHERE E.ID = W.ID ID Name 123 Tinky Winky 124 Dipsy 125 La La 126 Po WorksIn ID Dept 124 Marketing 125 Sales 125 Marketing Business Alias Example Employees

  40. SELECT E.ID AS empID, E.Name, W.Dept FROM Employee E WorksIn W WHERE E.ID = W.ID Business Alias Example Result: empID Name Dept 124 Dipsy Marketing 125 La La Sales 125 La La Marketing

  41. Aliases can be used to copy a table, so that a it can be combined with itself: Aliases and ‘Self-Joins’ Employee Name Dept Dipsy Marketing La La Sales Tinky W Sales Po Marketing Sun Marketing Who works with Po? SELECT A.Name FROM Employee A, Employee B WHERE A.Dept=B.Dept AND B.Name=‘Po’

  42. B Name Dept Dipsy Marketing La La Sales Tinky Sales Po Marketing Sun Marketing A Name Dept Dipsy Marketing La La Sales Tinky Sales Po Marketing Sun Marketing Aliases and Self-Joins Employee Employee

  43. Aliases and Self-Joins SELECT … FROM Employee A, Employee B … A.Name A.Dept B.Name B.Dept Dipsy Marketing Dipsy Marketing La La Sales Dipsy Marketing Tinky Sales Dipsy Marketing Po Marketing Dipsy Marketing Sun Marketing Dipsy Marketing Dipsy Marketing La La Sales La La Sales La La Sales Tinky Sales La La Sales Po Marketing La La Sales Sun Marketing La La Sales

  44. Aliases and Self-Joins SELECT … FROM Employee A, Employee B WHERE A.Dept = B.Dept A.Name A.Dept B.Name B.Dept Dipsy Marketing Dipsy Marketing Po Marketing Dipsy Marketing Sun Marketing Dipsy Marketing La La Sales La La Sales Tinky Sales La La Sales La La Sales Tinky Sales Tinky Sales Tinky Sales Dipsy Marketing Po Marketing Po Marketing Po Marketing Sun Marketing Po Marketing

  45. Aliases and Self-Joins SELECT … FROM Employee A, Employee B WHERE A.Dept = B.Dept AND B.Name = ‘Po’ A.Name A.Dept B.Name B.Dept Dipsy Marketing Po Marketing Po Marketing Po Marketing Sun Marketing Po Marketing

  46. A.Name Dipsy Po Sun Aliases and Self-Joins SELECT A.Name FROM Employee A, Employee B WHERE A.Dept = B.Dept AND B.Name = ‘Po’ The result is the names of all employees who work in the same department as Po.

  47. 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 Po’s department: SELECT Name FROM Employee WHERE Dept = (SELECT Dept FROM Employee WHERE Name=‘Po’) Subqueries

  48. SELECT Name FROM Employee WHERE Dept = (SELECT Dept FROM Employee WHERE Name=‘Po’) First the subquery is evaluated, returning the value ‘Marketing’ This result is passed to the main query SELECT Name FROM Employee WHERE Dept = ‘Marketing’ Subquery Order

  49. 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 Subquery Results

  50. 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 clause SELECT <columns> FROM <tables> WHERE <value> IN <set> SELECT <columns> FROM <tables> WHERE <value> NOT IN <set>

More Related