More sql

1
More sql

• Session 4 subselects, union, aggregate functions

2
Catalog
cno varchar(7) ltpkgt ctitle varchar(50)
Components
term varchar(10) ltpk,fkgt lineno int(4) ltpk,fkgt compname varchar(15) ltpkgt maxpoints int(4) weight int(2)
Courses
term varchar(10) ltpkgt lineno int(4) ltpkgt cno varchar(7) ltfkgt a int(2) b int(2) c int(2) d int(2)
Enrolls
sid varchar(5) ltpk,fk1gt term varchar(10) ltpk,fk2gt lineno varchar(4) ltpk,fk2gt
Scores
sid varchar(5) ltpk,fk1gt term varchar(10) ltpk,fk1,fk2gt lineno varchar(4) ltpk,fk1,fk2gt compname varchar(15) ltpk,fk2gt points int(4)
Students
sid varchar(5) ltpkgt fname varchar(20) lname varchar(20) minit char
3
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.

4
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.

5
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 lt 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.sidenrolls.sid
• and students.sid any ( select distinct sid
  from enrolls where termf96)

6
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 termf96 and students.sidenroll
  s.sid)

7
Union

• Computes the union of two sub-queries
• Get the student ids enrolled in f96 or sp97
• select sid from enrolls
• where termf96
• union
• select sid from enrolls
• where termsp97
• Union all does not remove duplicates
• select sid from enrolls
• where termf96
• Union all
• select sid from enrolls
• where termsp97

8
Aggregate functions
Name Argument type Result Type Description
count any( can be ) numeric count of occurrences
sum numeric numeric sum of arguments
avg numeric numeric Average of arguments
max char or numeric same as argument maximum value
min char or numeric same as argument minimum value
9
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 lineno1031 and termf96

10
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

11
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 gt100

12
Sql functions

• String functions
• lower(string)
• upper(string)
• char_lenth(string)
• substring(string,start ,n)
• trim(string) removes spaces from the string
  (ltrim, rtrim)

13
Sql functions (cont)

• Numeric functions
• ,-,, /
• abs absolute value
• ceil, floor
• mod
• power
• sqrt

14
Sql functions (cont)

• Date functions
• current_date()
• , -
• '1997-12-31 235959' INTERVAL 1 SECOND
  '1998-01-01 000000
• SELECT '1998-01-01' - INTERVAL 1 DAYS
  '1997-12-31'
• PERIOD_DIFF(P1,P2) months between P1 and P2
  (YYMM)
• gt lt
• trunc(d) returns the same day but with the time
  truncated to 1200AM
• More information http//dev.mysql.com/doc/refman
  /5.0/en/date-and-time-functions.html

15

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

16
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.