Agenda – 10/23/2012

1. Agenda – 10/23/2012 • Provide answers for HW#7 if requested. • Present SQL View database object.

2. Database for Sample Code in this Handout

3. What is a SQL View? • A “virtual” table. • A set of SQL statements that creates a result table which can be accessed by other SQL statements. • Similar to a function in another programming language, except that the end-product of the view is a named result table. • A database object. • The code for a view is stored in the database. • A view contains no data of its own. • A view relies on the data in the base tables used to create the view. • A set of stored SQL code. • Stores code; not data.

4. Let’s say we frequently look at all the information about time in our database and we always convert the time to hours and we always like to include the name of the employee who worked the time as well as the description of the type of work performed: SELECT ISNULL(emp.empid,tw.empid) empid, emp.lastname, tw.contractid, tw.startwork, tw.worktypeid, work.description, ISNULL(amount/60,0) FROMemp FULL OUTER JOIN timeworkedtw ON emp.empid = tw.empid LEFT OUTER JOIN work ON work.worktypeid = tw.worktypeid ORDER BY 1;

5. Create a view out of the code Must add the CREATE VIEW statement CREATE VIEW vEmptime AS SELECT ISNULL(emp.empid,tw.empid) empid, emp.lastname, tw.contractid, tw.startwork, tw.worktypeid, work.description, ISNULL(amount/60,0) hoursworked FROM emp FULL OUTER JOIN timeworkedtw ON emp.empid = tw.empid LEFT OUTER JOIN work ON work.worktypeid = tw.worktypeid; Must alias any field with a calculation, aggregation or function Must eliminate the ORDER BY clause

6. How is the view used via SQL? • Views are used just as tables are used in SQL. In this example, the join is predefined, so it is easier to write queries to access the data. SELECT * FROM VEmpTime ORDER BY empid; SELECT * FROM VEmpTime WHERE hoursworked = 0; SELECT lastname FROM VEmpTime;

7. Can a view be joined with tables? SELECT VEmpTime.empid, VEmpTime.lastname, VEmpTime.startwork, VEmpTime.contractid, client.name FROM VEmpTime INNER JOIN contract ON contract.contractid = VEmpTime.contractid INNER JOIN client ON contract.clientid = client.clientid ORDER BY VEmpTime.empid, VEmpTime.contractiddesc; Must have a “shared” column to access the data between tables and view – same as using a foreign key between tables.

8. Last week in lab, we discussed a correlated sub-query SELECTempID, lastname, empOuter.jobtitleID, description, billingrate "Employee Billing Rate", (SELECTAVG(billingrate) FROMemp empSelect WHEREempOuter.jobtitleID = empSelect.jobtitleID) "Average Billing Rate" FROMemp empOuter LEFT OUTER JOIN jobtitle ONempOuter.jobtitleID = jobtitle.jobtitleID WHERE billingrate > (SELECTAVG(billingrate) FROMemp empInner WHEREempOuter.jobtitleID = empInner.jobtitleID)

9. Group Functions and Joins are Complex • Must have all non-group attributes that are in the SELECT list also in the GROUP BY statement. • Difficult to do a group function of a group function. Examples: • The maximum of the sum of hours. • The minimum of a count of products. • Joining multiple tables can yield full or partial cartesian products making it difficult to trouble-shoot the SQL code.

10. Create a “view” database object CREATE VIEW vAvgRateByTitle AS SELECT jobtitleID, AVG(billingrate) AverageBillRate FROM emp GROUP BY jobtitleID; Remember: when using a VIEW, any derived column (calculated, aggregate and/or with a SQL function) must have an alias

11. Look at the VIEW results and use the VIEW in another query SELECT * FROM vAvgRateByTitle; SELECT emp.empid, emp.lastname, emp.billingrate, emp.jobtitleid, vAvgRateByTitle.AverageBillRate FROM emp LEFT JOIN vAvgRateByTitle ON emp.jobtitleid = vAvgRateByTitle.jobtitleid WHERE emp.billingrate > AverageBillRate;

12. So, what’s the big deal? • Views allow you to break down difficult queries into smaller pieces for easier design, coding and debugging. • Views allow you to create a layer of abstraction between the data structure and the user or programmer allowing greater security. • Programmers do not know the structure of the base tables. Less risk of fraud. • Users can see “pre-joined” tables for their queries. • Users don’t have to understand the complexity of the actual database. • No one sees data that is secure (salary, for example). • Views allow you to access the results of aggregate functions more easily.

13. Examples of more complex questions • Which employee worked the most total hours in September? • What is the description of the work type with the most time in the time table and how much time was reported for that work type description? • For which contract have we spent the most time? • What is the name of the client for whom we worked the most time during the month of August in the current year? • Which contracts have had more time worked than the estimated time? • Which contracts have more than 25% time worked than the estimated time?

14. Let’s find out which employee worked the most hours in September Where do the columns come from (which table)? What is the basic logic of the query? What is the simplest component that can be written to accomplish the basic logic?

15. Write the basic logic in pseudocode SELECT employee stuff FROM timeworked WHERE sum(timeworked) for month of September = max(sum(timeworked)) for month of September This code doesn’t work!! It is just written to get an understanding of the basic logic necessary to accomplish the query.

16. Use a view to summarize data CREATE VIEW vEmpHours AS SELECT empID, month(startwork) MonthWork, sum(amount/60) TotalHours FROM timeworked GROUP BY empid, month(startwork)

17. Maybe create another view if you want... CREATE VIEW vEmpHoursSept AS SELECT * FROM vEmphours WHERE monthwork= 9;

18. Use the view to work on the basic logic SELECT * FROM vEmpHoursSept WHERE totalhours= (SELECT MAX(totalhours) FROM vEmpHoursSept)

19. Now add the “extra” stuff, piece at a time... SELECT vEHS.empid, firstname+ ' ' + lastname "Employee Name", officephone, totalhours FROM vEmpHoursSeptvEHS LEFT OUTER JOIN emp on vEHS.empid= emp.empid WHERE totalhours= (SELECT MAX(totalhours) from vempHourssept)

20. Finish it up! SELECT vEHS.empid, firstname+ ' ' + lastname "Employee Name", officephone, description, totalhours FROM vEmpHoursSeptvEHS LEFT OUTER JOIN emp on vEHS.empid = emp.empid LEFT OUTER JOIN jobtitlejt on jt.jobtitleid = emp.jobtitleid WHERE totalhours= (SELECT MAX(totalhours) from vempHourssept)

21. Let’s do another example! I want to know information about the contract for which we have worked the most hours. I want the output to look like this:

22. Let’s do it in pieces • Identify the underlying tables or calculations that will be used to find and/or calculate the columns in the result table. • Define the basic “logic” of the query. Use pseudocode to help make that definition. • Don’t focus on the columns that are “pretty,” i.e. provide information to the user of the query but don’t affect the basic logic of the query. • The basic “logic” usually relies on some kind of relational condition. • If you don’t understand the basic “logic” of the query, then play around with SQL to figure it out. • First, write a query that summarizes the amount of time (divided by 60 so that the sum is in hours) in the TIMEWORKED table by contractid. • Second, use that query to figure out how to find the MAX of the SUM.

23. Conclusion: Uses of views • Views are used to: • Provide easier access to data. • Enhance security. • Lessen the visible complexity of the database. • Views are usually created by the DBA for a defined workgroup of people. • Programmers. • Users. • Users in a specific functional area.