1 / 16

More sql

More sql. Session 4 – subselects, union, aggregate functions. Sub-selects. The search criteria in the where clause may itself contain a select statement. Such a select statement is referred a a sub-select. sub-select operators. in and not in

rall
Download Presentation

More 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. More sql Session 4 – subselects, union, aggregate functions

  2. Sub-selects • The search criteria in the where clause may itself contain a select statement. Such a select statement is referred a a sub-select.

  3. sub-select operators • in and not in Get the sid values of students who have enrolled in csc226. select fname,lname from students where sid in (select sid from enrolls, courses where enrolls.term = courses.term and enrolls.lineno = courses.lineno and cno = 'csc226'); Ex: Get the names of students who have enrolled in csc226 or csc227.

  4. sub-select operators(cont) • any – the comparison succeeds if it matches any one value in the subselect • all - the comparison succeeds if it matches all the values in the subselect Get the students name with the lowest student id: select fname,lname from students where sid <= all (select sid from students); Get the names of students who took at least one course in the fall semester of 1996 select distinct fname, lname from students, enrolls where students.sid=enrolls.sid and students.sid = any ( select distinct sid from enrolls where term=‘f96’);

  5. sub-select operators(cont) • exists and not exists • exists predicate is true if the sub-select result is a non-empty set of values and is false otherwise Get the ids of student who did not enroll in any course in the fall 1996 semester. select sid from students where not exists ( select ’a’ from enrolls where term=“f96” and students.sid=enrolls.sid);

  6. Union • Computes the union of two sub-queries Get the student ids enrolled in f96 or sp97 select sid from enrolls where term=“f96” union select sid from enrolls where term=“sp97” • Union all does not remove duplicates select sid from enrolls where term=“f96” Union all select sid from enrolls where term=“sp97”

  7. Aggregate functions

  8. Aggregate functions examples • Get the total number of students select count(*) sid from students; • Get the un-weighted average of the number of points for all tests in section 1031, fall 1996. select avg(maxpoints) from components where lineno=“1031” and term=“f96”;

  9. Group by and having • The group by clause is used to form groups of rows of a resulting table based on column values • When the group by clause is used all aggregate operations are computed on the individual groups , not on the entire table • The having clause is used to eliminate certain groups

  10. Group and having example • Get the average number of points for all the tests in each term/section select term,lineno,avg(maxpoints) from components group by term,lineno; • Get the average number of points for all the tests in each term/section, but only if the average is higher then 99.99 select term,lineno,avg(maxpoints) AVG from components group by term,lineno having AVG >=100

  11. Sql functions • String functions : • lower(string) • upper(string) • char_lenth(string) • substring(string,start [,n]) • trim(string) – removes spaces from the string (ltrim, rtrim)

  12. Sql functions (cont) • Numeric functions • +,-,*, / • abs – absolute value • ceil, floor • mod • power • sqrt

  13. Sql functions (cont) • Date functions • current_date() • +, - • '1997-12-31 23:59:59' + INTERVAL 1 SECOND = '1998-01-01 00:00:00‘ • SELECT '1998-01-01' - INTERVAL 1 DAYS = '1997-12-31' • PERIOD_DIFF(P1,P2) – months between P1 and P2 (YYMM) • > < = • trunc(d) returns the same day but with the time truncated to 12:00AM • More information : http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

  14. More information of MySql functions : http://dev.mysql.com/doc/refman/5.0/en/functions.html

  15. In class exercises • Get the sid values of students who did not enroll in any class during the f96 term. • Get the names of students who have enrolled in the highest number of courses.

More Related