1 / 23

Advanced SQL

Advanced SQL. Chapter 8 (Part A) Multiple Table Queries. Objectives. Be able to retrieve data from multiple tables Be able to use an alias Be able to join a table to itself. Retrieving Data from Multiple Tables.

menefer
Download Presentation

Advanced SQL

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. Advanced SQL Chapter 8 (Part A) Multiple Table Queries

  2. Objectives • Be able to retrieve data from multiple tables • Be able to use an alias • Be able to join a table to itself

  3. Retrieving Data from Multiple Tables • Tables may be joined when a column from one table is the same length and type as a column in another table. • Typically, the primary key in one table is matched to the foreign key in another table. • The WHERE clause is used to join the two columns together. • Often the column names must be qualified: TableName.ColumnName

  4. Querying Multiple Tables • Join • Indicate in the SELECT clause all the columns to display. • Indicate in the FROM clause all tables involved in the query. • Give condition(s) in the WHERE clause to restrict the data to be retrieved to only those rows that have common values in matching columns.

  5. Join • Join creates a new table (virtual table) from two existing tables by matching on a column common to both tables • Equi-join - The new table contains the two identical columns • Natural join –Duplicate columns are eliminated. • Most commonly used join • Outer join – rows that do not have matching values in common are nevertheless included in the result table • Returns all the values in one of the tables included in the join, regardless of whether a match exists in the other table or not • Left outer join, right-outer join, full-join

  6. Join: Equijoin • The new table contains the two identical columns Join the Student and Major tables SELECT * FROMStudent, Major WHEREStudent.Major = Major.Code ORDER BY Student.ID;

  7. Join: Natural Join • Duplicate columns are not included Join the Student and Major tables SELECTID, Name, Major, Description FROMStudent, Major WHEREStudent.Major = Major.Code ORDER BY Student.ID; • Specify the columns to include. • If the same name in both columns, specify table: Student.ID, Student.Name etc.

  8. Join: Natural Join • The most common join Display the number and names of students taking English 101 SELECTID, Name FROMStudent, English101 WHEREStudent.ID = English101.Student_ID ORDER BY Student.ID; Either English101.Student_ID or Student.ID could have been projected.

  9. Join: Natural Join Display the number, name and major description of students taking English 101 SELECTID, Name, Description FROMStudent, English101, Major WHEREStudent.ID = English101.Student_ID ANDStudent.Major = Major.Code ORDER BYStudent.ID;

  10. Join: Natural Join Display the ID, name, major and exam average for all students in English101 SELECTID, Name, Major, ((Exam1+Exam2+Exam3)/3) AS TestAvg FROMStudent, English101 WHEREStudent.ID = English101.Student_ID ORDER BYStudent.ID; Named Calculated Column, TestAvg

  11. Join: Natural Join Group all majors together and calculate the test average per major in English101 SELECTMajor, AVG ((Exam1+Exam2+Exam3)/3) AS TestAvg FROMStudent, English101 WHEREStudent.ID = English101.Student_ID GROUP BY Major ORDER BYMajor;

  12. Join: Outer Join • Rows that do not have matching values in common are nevertheless included in the result table SELECTEnglish101.Student_ID, Student.Name FROMStudent, English101 WHERE Student.ID = English101.Student_ID (+) ORDER BYEnglish101.Student_ID; plus (+) indicates outer join

  13. Join: Outer Join • Display all students who are NOT enrolled in English101 SELECTStudent.Name FROMStudent, English101 WHERE Student.ID = English101.Student_ID (+) AND English101.Student_ID IS NULL ORDER BYName;

  14. Join: Outer Join • LEFT JOIN – Include all values of left table in query • RIGHTJOIN – Include all values of right table in query • FULL JOIN – Include all rows (rarely used) SELECTEnglish101.Student_ID, Student.Name FROMStudentLEFT JOIN English101 ONStudent.ID = English101.Student_ID ORDER BYEnglish101.Student_ID; Alternative SQL notation

  15. An Alias • Tables listed in the FROM clause can be given an alternative name • An ALIAS is an alternative name for a table that may be used when qualifying column names in order to reduce your typing. SELECTEng.Student_ID, Stud.Name FROM English101 Eng, Student Stud WHEREEng.Student_ID = Stud.ID;

  16. Joining A Table To Itself • Find every student who has the same name as another student. SELECT Stud1.ID, Stud1.Name, Stud1.Major FROM Student Stud1, Student Stud2 WHERE Stud1.Name = Stud2.Name AND Stud1.ID <> Stud2.ID ORDER BY Stud1.ID;

  17. Join vs Subquery • Joining tables on matching columns produces the same results as using a subquery with IN. SELECT partdesc FROM Part, OrderLine WHERE Part.partno = OrderLine.partno AND ordno = ‘12491’; SELECT partdesc FROM Part WHERE partno IN (SELECT partno FROM OrderLine WHERE ordno = ‘12491’);

  18. Subquery within a Subquery • A nested query may be used within a nested query or multiple tables may be joined. Find the order number & date for every order that includes a part located in warehouse 3. SELECT ordno, orddate FROM orders WHERE ordno IN (SELECT ordno FROM orderline WHERE partno IN (SELECT partno FROM part WHERE warehouseno = ‘3’) );

  19. Nested Subquery Evaluation Step 1: Innermost subquery is evaluated first, producing a temporary table of part numbers located in warehouse 3 Step 2: Intermediate subquery is evaluated, producing a second temporary table with a list of order numbers Step 3: Outer query is evaluated last, producing the desired list of order numbers and order dates using only those orders whose numbers are in the temporary table produced in step 2

  20. ALL & ANY Operators • ALL and ANY may be used in front of a subquery. • If the subquery is preceded by ALL, the condition is true only if it satisfies ALL values produced by the subquery. • If the subquery is preceded by ANY, the condition is true if it satisfies ANY value produced by the subquery

  21. ANY Example • Find the customer# of every customer whose balance is larger than the balance of at least one customer represented by salesman# 10. SELECT custno FROM Customer WHERE balance > ANY (SELECT balance FROM Customer WHERE salesrepno = ‘10’);

  22. Correlated Subquery • A correlated subquery is when the inner query refers to a table used in the outer query (as the previous example did). • The inner query is computed for each outer row List the description of the product with the highest unit price. SELECT Description, Price FROM Product PA WHERE Price > ALL (SELECT Price FROM Product PB WHERE PA.id <> PB.id);

More Related