SQL: Interactive Queries (2)Aggregate FunctionsAggregate Functions (cont.)Group By ClauseGroup By Clause (cont.)Having ClauseOrder By ClauseSome Complex QueriesInteractive SQL SummaryInteractive SQL Summary (count.)Expressive Power of SQLCreate Table Re-visitedUpdate By QueriesUpdate StatementTruncate vs Delete *ViewsViews (cont.)Query ModificationWhy Use Views?Example of Using ViewsExample of Using Views (cont.)Views and UpdatesView Update Example *Maintaining Materialized ViewsAssertionsRecursionWith StatementExample of WithRecursion in SQLRestrictions & FeaturesCommit and RollbackGrant StatementSample Grant StatementsFeatures of GrantRolesCreate External Schema *Revoke StatementSequence in Oracle SQLSample SequencesUse SequencesOracle SQL*LoaderA Sample Control FileA Sample Data FileLook AheadSQL: Interactive Queries (2)Prof. Weining ZhangLecture 12 SQL: Interactive Queries (2) 2Aggregate FunctionsFunctions that take a set of tuples and compute an aggregated value.Five standard functions: count, min, max, avg, sumThey ignore null values.Find the total number, the average, minimum, and maximum GPA of students whose age is 17. select count(*), avg(GPA), min(GPA), max(GPA) from Students where Age = 17Lecture 12 SQL: Interactive Queries (2) 3Aggregate Functions (cont.)Find id and name of students who take 5 or more courses. select SID, Name from Students s where 5 <= (select count(distinct Cno) from Enrollment where SID = s.SID)Count(distinct Cno) distinct count(Cno). Why?Must make sure the subquery generates a value comparable in the predicate.Lecture 12 SQL: Interactive Queries (2) 4Group By ClauseList id and name of students together with the number of hours still needed to graduate, assuming 120 hours are required. select s.SID, Name, 120 - sum(Hours) Hours-Needed from Students s, Enrollment e, Courses c where s.SID = e.SID and e.Cno = c.Cno and Grade <= ‘C’ group by s.SID, NameEnrolled courses are grouped by students.Lecture 12 SQL: Interactive Queries (2) 5Group By Clause (cont.) Aggregate functions often applied to groups. One tuple is generated per groupWhen using group by, select clause can contain only grouping attributes and aggregate func. Every grouping attribute must be in the select clause.The following is an illegal query (why?): select Age, SID, avg(GPA) from Students group by AgeLecture 12 SQL: Interactive Queries (2) 6Having ClauseFor each student age group with more than 50 members, list the age and the number of students with that age. select Age, count(*) from Students group by Age having count(*) > 50Conditions on aggregate functions are specified in the having clause.Select & Having may have different functions.Lecture 12 SQL: Interactive Queries (2) 7Order By Clause List student names in ascending order. select Name from Students order by Name ascThe default is ascending order.List students with GPA higher than 3.5, first in descending order of GPA, and then in ascending order of name. select * from Students where GPA > 3.5 order by GPA desc, Name ascLecture 12 SQL: Interactive Queries (2) 8Some Complex QueriesFind the average number of CS courses a student takes.For non-CS major students who take more CS courses than he does with his major courses, and have taken at lease 2 CS courses, list their id, name, number of CS courses, number of major courses, sorted first in descending order of number of CS courses, then in ascending order of name.Lecture 12 SQL: Interactive Queries (2) 9Interactive SQL SummaryA query may have six clauses: select, from, where, group by, having, order by.Conceptual evaluation of the query:1. Evaluate From (cross product)2. Evaluate Where (selection)3. Evaluate Group By (form groups)4. Evaluate Aggregate functions on groups5. Evaluate Having (choose groups to output)6. Evaluate Order By (sorting)7. Evaluate remaining Select (projection)Lecture 12 SQL: Interactive Queries (2) 10Interactive SQL Summary (count.)Many ways to express a query. Flat queries may be more efficient.Nested queries may be easier to understand.Duplicate elimination may be costly.<> (not equal) at predicate level often gives a wrong answer. Use set difference, not in, not exists, etc. instead.Need to handle null values explicitly.DBMSs often provide many convenient functions. But need to check the compatibility.Lecture 12 SQL: Interactive Queries (2) 11Expressive Power of SQLSQL is relational complete.Can express any relational algebraic query.SQL is more powerful then relational algebra.Can express aggregation, ordering, recursion, etc.SQL is not computational complete.Can not do everything a general programming language can do.Lecture 12 SQL: Interactive Queries (2) 12Create Table Re-visitedCan combine table creation with insertion of tuples using a query. create table Full-Professors as select FID, Name, Office from Faculty where Rank = ‘Full Professor’Lecture 12 SQL: Interactive Queries (2) 13Update By QueriesRelation: Top_Students (SID, Name, GPA)Insert students with a GPA 3.8 or higher into the Top_Students table. insert into Top_Students select SSN, Name, GPA from Students where GPA >= 3.8 Delete all students who take no courses. delete from Students where SID not in (select SID from Enrollment)Lecture 12 SQL: Interactive Queries (2) 14Update StatementFor every student who takes Database I, set the Grade to ‘A’. update Enrollment set Grade = 'A' where Cno in (select Cno from Courses where Title = ‘Database I')Lecture 12 SQL: Interactive Queries (2) 15Truncate vs Delete *Use delete to remove data and keep the table storage space. delete from Departments;Use truncate to remove data and release table storage space. truncate table Departments;Lecture 12 SQL: Interactive Queries (2) 16ViewsA view is a virtual table (as opposed to stored base table) defined by a query, directly or indirectly, on base tables. create view Top_Students as select SSN, Name, GPA from Students where GPA >= 3.8A view may be defined in terms of other views.Lecture 12 SQL: Interactive Queries (2) 17Views (cont.)The query in view definition is usually not executed until the view is queried. Typically, no data is stored for a view. A view is queried as if it is a base table. Find name and GPA of top students whose name starts with a `K'. select Name,
View Full Document