Standard Query LanguageSQL

1
Comp 231 Database Management Systems
7. Structured Query Language Exercises 3
2
Aggregate Operators
Sailors (sid, sname, rating, age), Reserves
(sid, bid, date), Boats (bid, bname, color)
Find the number of sailor records SELECT COUNT
() FROM Sailors Find the average age of
sailors with rating 10 SELECT AVG (S.age) FROM
Sailors as S WHERE S.rating10 Find how may
different ratings exist for sailor whose name is
Bob SELECT COUNT (DISTINCT S.rating) FROM
Sailors as S WHERE S.snameBob
3
Sailors (sid, sname, rating, age), Reserves
(sid, bid, date), Boats (bid,bname,color)

• Find name and age of the oldest sailor(s)
• SELECT S.sname, MAX (S.age)
• FROM Sailors as S
• Illegal! If there is no group by, we can only
  have aggregates (without attributes). The correct
  solution is
• SELECT S.sname, S.age
• FROM Sailors as S
• WHERE S.age
• (SELECT MAX (S2.age)
• FROM Sailors as S2)

4
Sailors (sid, sname, rating, age), Reserves
(sid, bid, date), Boats (bid, bname, color)
For each red boat, find the number of
reservations for this boat SELECT B.bid, COUNT
() AS scount FROM Boats as B, Reserves as
R WHERE R.bidB.bid AND B.colorred GROUP BY
B.bid We cannot remove B.colorred from the
WHERE clause and add a HAVING clause with this
condition. Only columns that appear in the
Group-By can appear in HAVING, unless they are
arguments of an aggregate operator in HAVING.
5
Sailors (sid, sname, rating, age), Reserves
(sid, bid, date), Boats (bid, bname, color)

• Find the age of the youngest sailor with age
  18, for each rating with at least 2 such sailors
• SELECT S.rating, MIN (S.age)
• FROM Sailors as S
• WHERE S.age 18
• GROUP BY S.rating
• HAVING COUNT () 1

Answer relation
6
Sailors (sid, sname, rating, age), Reserves
(sid, bid, date), Boats (bid, bname, color)

• Find the age of the youngest sailor with age
  18, for each rating with at least 2 sailors (of
  any age)
• SELECT S.rating, MIN (S.age)
• FROM Sailors as S
• WHERE S.age 18
• GROUP BY S.rating
• HAVING 1
• FROM Sailors as S2
• WHERE
  S.ratingS2.rating)

7
Sailors (sid, sname, rating, age), Reserves
(sid, bid, date), Boats (bid, bname, color)

• Find those ratings for which the average age is
  the minimum over all ratings
• SELECT Temp.rating, Temp.avgage
• FROM
• (SELECT S.rating, AVG (S.age) as avgage
• FROM Sailors as S
• GROUP BY S.rating) as Temp
• WHERE Temp.avgage (SELECT MIN (Temp.avgage)
• FROM
  Temp)

8
Example Conversion of E-R Diagram into tables.
9
Entities (Not Weak)
10
Entities (Not Weak)

• CREATE TABLE Student (
• student-id INTEGER,
• name CHAR(20),
• program CHAR(20),
• PRIMARY KEY (student-id))
• Similarly for Course, Instructor
• Course (courseno, title, syllabus, credits)
• Instructor (instructor-id, name, dept, title)

11
Weak Entities
12
Weak Entities

• CREATE TABLE Course-offering
• (courseno INTEGER NOT NULL, //not necessary since
  courso belong to the key
• secno INTEGER,
• year INTEGER,
• semester CHAR(6),
• time TIME,
• room INTEGER,
• PRIMARY KEY (courseno,secno,year,semester)
• FOREIGN KEY (courseno) REFERENCES Course
• ON DELETE CASCADE
• )

13
Relationships
14
Relationships

• CREATE TABLE Enroll
• (student-id INTEGER,
• courseno INTEGER,
• secno INTEGER,
• year INTEGER,
• semester CHAR(6),
• grade INTEGER,
• PRIMARY KEY (student-id,courseno,secno,year,semest
  er)
• FOREIGN KEY (student-id) REFERENCES Student
• ON DELETE CASCADE
• FOREIGN KEY (courseno,secno,year,semester)
  REFERENCES Course-Offerings
• ON DELETE CASCADE)
• Similar for
• Teaches (courseno, secno, semester, year,
  instructor-id)
• Requires (maincourse, prerequisite)