More sql

1
More sql

• Session 4 subselects, union, aggregate functions

2
(No Transcript)
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
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.

17
Programming assignment 2

• Will be posted Thursday, February 12.
• It has to be submitted via blackboard no later
  than Thursday, Feb 26, 11.59 pm