1 / 28

While you are waiting for class to start...

While you are waiting for class to start. Login to SQL Server 2012 Management Studio (2) Execute the file called “ SQLLab4.sql ”. It is located on the k: drive in the IS475 LabFiles folder.

clare
Download Presentation

While you are waiting for class to start...

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. While you are waiting for class to start... Login to SQL Server 2012 Management Studio (2) Execute the file called “SQLLab4.sql”. It is located on the k: drive in the IS475\LabFiles folder. You will see errors, and then the tables will create and populate. There are six tables created and populated with this file. (3) Look at the content of the tables

  2. Topics for today – 03/13/2014 • Review and reinforce knowledge of joins. • Discuss another twist on the join – a self-join. • Practice multiple-table joins. • Review non-correlated sub-queries. • Learn about correlated sub-queries. • Work on HW#7.

  3. Know your tables How many relationships in the database? Which table is the child and which is the parent in each relationship? What are the data types of the primary keys? How many rows are in each table? Are there any concatenated keys in the database?

  4. This is the result table I want to produce. Which underlying tables do I need to use?

  5. Review joins • Purpose is to combine more than one underlying table into a single result table. • A join operation multiplies the two underlying tables: the rows are multiplied and the columns are added together to create a single result table. • Cross join demonstration: SELECT * FROM xemp, xjobtitle

  6. Types of joins: INNER JOIN • Inner join: Combines the rows from two tables where the foreign key in the child table equals the primary key in the parent table. • Result table: Consists only of those rows from both tables where the primary key = foreign key. • Example: SELECT * FROM xempemp INNER JOIN xjobtitlejt ON emp.jobtitleID = jt.jobtitleID

  7. Types of joins: OUTER JOIN • Outer join: Combines the rows from two tables where the foreign key in the child table equals the primary key in the parent table. In addition, an outer join will include those rows in one (left, right) or both (full) tables that do not match the other table. • Result table: Consists of those rows from both tables where the primary key = foreign key plus those rows from one or both tables where the primary key does not have a direct match. • Example: SELECT * FROM xempemp LEFT OUTER JOIN xjobtitlejt ON emp.jobtitleID = jt.jobtitleID

  8. Limit the columns to get the desired result SELECTemp.lastname, emp.firstname, emp.hiredate, emp.billingrate, emp.jobtitleid, ISNULL(jt.title,'No Matching Title')title FROM xempemp LEFTOUTERJOIN xjobtitlejt ON emp.jobtitleID=jt.jobtitleID ORDER BYemp.lastname

  9. What do the two other outer joins do?? SELECTemp.lastname, emp.firstname, emp.hiredate, emp.billingrate, emp.jobtitleid, ISNULL(jt.title,'No Matching Title')title FROM xempemp RIGHTOUTERJOIN xjobtitlejt ON emp.jobtitleID=jt.jobtitleID ORDER BY emp.lastname SELECTemp.lastname, emp.firstname, emp.hiredate, emp.billingrate, emp.jobtitleid, ISNULL(jt.title,'No Matching Title')title FROM xempemp FULLOUTERJOIN xjobtitlejt ON emp.jobtitleID=jt.jobtitleID ORDER BY emp.lastname

  10. Self-join What if you want to add the last name of the manager for each employee?

  11. Become familiar with the xEmptable. select empid, lastname, firstname, managerid from xEmp; Realize that the ManagerID in the EMP table represents the EmpID of the person who is the manager for that employee. To get the name of the manager (look at previous slide for the desired output from the query) requires that you search the EMP table twice: Reading sequentially for each employee in the database; Reading again to find the name of the manager for each employee. SQL does not let you read a table twice in a single query, so you must have two copies of the EMP table in memory in order to accomplish this operation. Those two copies must have table name aliases so that SQL Server (our DBMS) can differentiate them. In order to identify the name of the manager for a given employee, we need to join the two tables. There must be a foreign key to self-join two tables. In this sample, the foreign key is the ManagerID. Type in the code on the next slide to join two copies of the EMP table on the foreign key of ManagerID and primary key of EmpID.

  12. SQL Self-Join Type in this code to join two copies of the same table: SELECTemp.lastname, emp.firstname, emp.hiredate, emp.billingrate, emp.jobtitleid, ISNULL(jt.title,'No Matching Title')title, ISNULL(manager.lastname,'**No Manager**')'Manager Last Name' FROM xempemp LEFTOUTERJOIN xjobtitlejt ON emp.jobtitleID=jt.jobtitleID INNERJOINxempmanager ONemp.managerid=manager.empid ORDER BYemp.lastname DOES IT WORK???

  13. Let’s join some more tables! SELECTemp.lastname, emp.firstname, emp.hiredate, emp.billingrate, emp.jobtitleid, ISNULL(jt.title,'No Matching Title')title, ISNULL(manager.lastname,'**No Manager**')'Manager Last Name', tw.StartWork, tw.Minutes FROM xempemp LEFTOUTERJOIN xjobtitlejt ON emp.jobtitleID=jt.jobtitleID LEFTOUTERJOINxempmanager ONemp.managerid=manager.empid INNERJOINxtimeworkedtw ON tw.empid=emp.empid WHEREemp.lastnameIN('Chu','Jenkins') ORDER BYemp.lastname

  14. SELECTemp.lastname, emp.firstname, emp.billingrate, ISNULL(jt.title,'No Matching Title')title, ISNULL(manager.lastname,'**No Manager**')'Manager Last Name', tw.StartWork, tw.Minutes, work.stdbillrate, work.description FROM xempemp LEFTOUTERJOIN xjobtitlejt ON emp.jobtitleID=jt.jobtitleID LEFTOUTERJOINxempmanager ONemp.managerid=manager.empid INNERJOINxtimeworkedtw ON tw.empid=emp.empid INNERJOINxworkwork ONtw.worktypeid=work.worktypeid WHEREemp.lastnameIN('Chu','Jenkins') ORDER BY emp.lastname

  15. SELECTemp.lastname, emp.firstname, emp.billingrate, ISNULL(jt.title,'No Matching Title')title, ISNULL(manager.lastname,'**No Manager**')'Manager Last Name', tw.StartWork, tw.Minutes, work.stdbillrate, work.description, contract.datesigned, contract.datedue FROM xempemp LEFTOUTERJOINxjobtitlejt ON emp.jobtitleID=jt.jobtitleID LEFTOUTERJOINxempmanager ONemp.managerid=manager.empid INNERJOINxtimeworkedtw ON tw.empid=emp.empid INNERJOINxworkwork ONtw.worktypeid=work.worktypeid INNERJOIN xcontractcontract ON tw.contractid=contract.contractid WHEREemp.lastnameIN('Chu','Jenkins') ORDERBY emp.lastname DOES IT WORK???

  16. SELECTemp.lastname, emp.firstname, emp.billingrate, ISNULL(jt.title,'No Matching Title')title, ISNULL(manager.lastname,'**No Manager**')'Manager Last Name', tw.StartWork, tw.Minutes, work.stdbillrate, work.description, contract.datesigned, contract.datedue, client.name'Client Name' FROM xempemp LEFTOUTERJOIN xjobtitlejt ON emp.jobtitleID=jt.jobtitleID LEFTOUTERJOINxempmanager ONemp.managerid=manager.empid INNERJOINxtimeworkedtw ON tw.empid=emp.empid INNERJOINxworkwork ONtw.worktypeid=work.worktypeid LEFTOUTERJOIN xcontractcontract ON tw.contractid=contract.contractid LEFTOUTERJOINxclientclient ON contract.clientid=client.clientid WHEREemp.lastnameIN('Chu','Jenkins') ORDERBY emp.lastname

  17. Summarize time worked by contract

  18. SELECTemp.lastname, emp.firstname, emp.billingrate, ISNULL(jt.title,'No Matching Title')title, ISNULL(manager.lastname,'**No Manager**')'Manager Last Name', contract.datesigned, contract.datedue, client.name'Client Name', SUM(tw.Minutes)'Total Minutes Worked' FROM xempemp LEFTOUTERJOIN xjobtitlejt ON emp.jobtitleID=jt.jobtitleID LEFTOUTERJOINxempmanager ONemp.managerid=manager.empid INNERJOINxtimeworkedtw ON tw.empid=emp.empid LEFTOUTERJOIN xcontractcontract ON tw.contractid=contract.contractid LEFTOUTERJOINxclientclient ON contract.clientid=client.clientid WHEREemp.lastnameIN('Chu','Jenkins') GROUPBYemp.lastname, emp.firstname, emp.billingrate, jt.title, manager.lastname, contract.datesigned, contract.datedue, client.name ORDERBY emp.lastname

  19. What is a sub-query? • A sub-query is a query embedded inside another query. • The sub-query is executed in the normal operation of the query in which it is embedded. • The sub-query will return an “answer” result table to the query in which it is embedded.

  20. Find the employees who are not in the TIMEWORKED table via a subquery. Outer Query SELECT * FROM xEMP WHERE empid NOT IN (SELECT empid FROM xtimeworked) Inner Query

  21. Find the employee who has the highest billing rate via a subquery. Outer Query SELECT * FROM xEMP WHERE billingrate = (SELECT max(billingrate) FROM xemp) Inner Query

  22. Find which employees have a billing rate higher than the average billing rate using a subquery. SELECT empID, lastname, billingrate FROM xemp WHERE billingrate > (SELECT AVG(billingrate) FROM xemp) The preceding sub-queries are called “non-correlated” sub-queries.

  23. Example of a correlated sub-query Which employees have a higher BillingRate than the average BillingRate for their job title? Let’s start by understanding the query requirements: SELECT jobtitleID, AVG(billingrate) AverageBillRate FROM xemp GROUP BY jobtitleID;

  24. Now put together a query using a correlated sub-query: SELECT empID, lastname, billingrate, jobtitleID FROM xemp empOuter WHERE billingrate > (SELECT AVG(billingrate) FROM xemp empInner WHERE empOuter.jobtitleID = empInner.jobtitleID) Must alias the tables since two tables of the same name will be in main memory concurrently. Must alias the tables since two tables of the same name will be in main memory concurrently. Query passes the jobtitleID from the outer query to the inner query

  25. To get the job title from the job title table requires the use of a join SELECT empID, lastname, billingrate, empOuter.jobtitleID, title FROM xemp empOuter LEFT JOINxjobtitle ONempOuter.jobtitleID = xjobtitle.jobtitleID WHERE billingrate > (SELECT AVG(billingrate) FROM xemp empInner WHERE empOuter.jobtitleID = empInner.jobtitleID)

  26. Where are sub-queries placed in SQL code? • Sub-queries can be embedded in the SELECT list, FROM, WHERE or HAVING clauses. • So far, all I’ve shown are sub-queries in the WHERE clause… • Sub-queries can be used in INSERT, UPDATE or DELETE statements.

  27. Example of a sub-query in the SELECT list: SELECTempID, lastname, empOuter.jobtitleID, title, billingrate "Employee Billing Rate", (SELECTAVG(billingrate) FROMxemp empSelect WHEREempOuter.jobtitleID = empSelect.jobtitleID) "Average Billing Rate" FROMxemp empOuter LEFT OUTER JOINxjobtitle ONempOuter.jobtitleID = xjobtitle.jobtitleID WHERE billingrate > (SELECTAVG(billingrate) FROMxemp empInner WHEREempOuter.jobtitleID = empInner.jobtitleID) How many copies of the EMP table are in memory when this query runs?

More Related