SQL

1
SQL

• What have we learnt so far?
• SQL has statements to create tables and various
  constraints in the tables
• We will learn in the next couple of lectures, how
  to query the tables and how to change their
  content.

2
SQL - refresher

• Create table statement creates a new table in the
  system.
• CREATE TABLE SECTION (
• CrsCode INTEGER,
• Semester CHAR(10),
• SectionNum INTEGER
• CHECK (
  SectionNum gt 0 AND SectionNum lt 99 ) ,
• Classroom CHAR(10) NOT NULL,
• CONSTRAINT section_pk
• PRIMARY KEY (CrsCode, Semester),
• CONSTRAINT section1_fk
• FOREIGN KEY (CrsCode) REFERENCES
  Course(CrsCode),
• CONSTRAINT section_uk UNIQUE (CrsCode,
  Semester, SectionNum)
• )

3
SQL

• A logical/declarative query language
• Express what you want, not how to get it
• Each SQL expression can be translated to multiple
  equivalent relational algebra expressions
• SQL is tuple based, each statement refers to
  individual tuples in relations
• SQL has bag semantics
• Recall RDMS implementations of relations as
  tables do not require tables to always have a
  key, hence allowing the possibility of duplicate
  tuples
• Same is true for SQL, an SQL expression may
  return duplicate tuples, unless they are removed
  explicitly.

4
Example Database

• STUDENT(Id, Name, Password, Address)
• FACULTY(Id, Name, DeptId, Password, Address)
• COURSE(CrsCode, DeptId, CrsName, CreditHours)
• REQUIRES(CrsCode, PrereqCrsCode, EnforcedSince)
• CLASS(CrsCode, SectionNo, Semester,
  Year,Textbook, ClassTime, Enrollment,
  MaxEnrollment, ClassroomId, InstructorId)
• CLASSROOM(ClassroomId, Seats)
• TRANSCRIPT(StudId, CrsCode, SectionNo, Semester,
  Year, Grade)

5
SQL

• SELECT C.CrsCode, C.SectionNo
• FROM CLASS C
• WHERE C.SemesterFall AND C.Year2002 AND
  Enrollment gt MaxEnrollment
• Select from C all tuples that satisfy the WHERE
  clause
• For these tuples, project C.CrsCode and
  C.SectionNo
• ? CrsCode, SectionNo ( ?SemesterFall AND
  C.Year2002 AND Enrollment gt MaxEnrollment CLASS
  )

6
SQL - simple expressions

• SELECT R.PrereqCrsCode Reqfor4380
• FROM Requires R
• WHERE R.CrsCode CSCI4380
• For each tuple R of the Requires relation
  (relation alias) check the WHERE clause
• Reqfor4380 is an alias for the output (or
  projection) column -gt renaming operator

7
SQL - distinct

• SELECT DISTINCT T.StudId
• FROM Transcript T
• WHERE T.grade A AND CrsCode CSCI4380
• SELECT DISTINCT C.InstructorId
• FROM CLASS C
• WHERE C.CrsCode 4380 AND
• C.Year IN (1998, 2000, 2002) AND
• C.Textbook LIKE Transaction

8
SQL - set/bag operators

• (SELECT S.Name
• FROM Student S)
• UNION
• (SELECT F.Name
• FROM Faculty F)
• Union compatibility is still needed for this
  operation

9
SQL - set/bag operators

• SQL set operators are UNION, INTERSECT and EXCEPT
  (set difference)
• Each operator is a set operator, i.e. removes
  duplicate tuples -even if SQL does not
  automatically.
• In cases where the duplicate information is
  important, then you can tell SQL not to remove
  duplicates by UNION ALL.
• UNION is supported by all DBMSs, the
  implementation for others may differ from system
  to system.

10
SQL - multiple tables

• Recall
• FACULTY(Id, Name, DeptId, Password, Address)
• CLASS(CrsCode, SectionNo, Semester,
  Year,Textbook, ClassTime, Enrollment,
  MaxEnrollment, ClassroomId, InstructorId)
• What does this query do?
• SELECT F.Name, C.CrsCode
• FROM Faculty F, Class C

11
SQL - multiple tables

• To find Faculty and the classes they teach
• SELECT F.Name, C.CrsCode
• FROM Faculty F, Class C
• WHERE C.InstructorId F.Id

12
SQL

• Let SELECT A1 B1, A2 B2, , Am Bm
• FROM R1, R2, , Rn
• WHERE selection-condition
• be a valid SQL statement where
• A1,,Am are attributes in R1,,Rn (disregarding
  relation aliases) and B1,,Bm are attribute
  aliases
• Selection-condition is a valid boolean expression
  involving only relations R1,,Rn
• Then, the result of this statement is equivalent
  to the relational algebra expression
• (? A1,,Am (?selection-condition (R1 ? R2 ? ?
  Rn))) B1,,Bm

13
SQL - multiple tables

• SELECT DISTINCT T.StudId, C.DeptId
• FROM Course C, Transcript T
• WHERE T.Semester Spring AND T.Year 2002 AND
  T.CrsCode C.CrsCode AND T.Grade A

14
Examples

• Find all the courses student named Jill Pecan
  has completed.
• SELECT T.CrsCode, T.Semester, T.Year
• FROM Student S, Transcript T
• WHERE S.IdT.StudId AND T.Grade IS NOT NULL AND
• T.Grade ltgt I AND S.name Jill
  Pecan
• Suppose when you register for a course, you have
  no value for grade. Note that the incomplete
  grade (I) is different than this.

15
Examples

• Find all faculty who taught courses both in
  Fall and Spring 2002.
• SELECT DISTINCT F.Name
• FROM CLASS C1, CLASS C2, Faculty F
• WHERE C1.InstructorId C2.InstructorId AND
• C1.Semester Fall AND
  C1.Year2002 AND
• C2.SemesterSpring AND
  C2.Year2002 AND
• F.Id C1.InstructorId

16
Examples

• Find all faculty who taught courses both in
  Fall and Spring 2002.
• (SELECT DISTINCT F.Name
• FROM CLASS C, Faculty F
• WHERE C.Semester Fall AND C.Year2002 AND
• F.Id C.InstructorId)
• INTERSECT
• (SELECT DISTINCT F.Name
• FROM CLASS C, Faculty F
• WHERE C.Semester Spring AND C.Year2002
  AND
• F.Id C.InstructorId)

17
Examples

• Find all students who are taking a course by
  Prof. Acorn in Fall 2002.
• SELECT DISTINCT S.Name
• FROM Student S, CLASS C, Faculty F,
  Transcript T
• WHERE S.Id T.StudId AND C.InstructorId F.Id
  AND
• C.CrsCode T.CrsCode AND
  C.Semester T.Semester AND
• C.Year T.Year AND F.name LIKE
  Acorn AND
• C.SectionNo T.SectionNo AND
• T.Semester Fall AND T.Year
  2002

18
Examples

• Find all faculty who teach a course offered by a
  department other than their own. List the name of
  the faculty, and the name of the courses they
  teach from other departments
• SELECT F.Name, C.CrsCode
• FROM Faculty F, Course C, Class CL
• WHERE F.Id C.InstructorId AND
• C.CrsCode CL.CrsCode AND
• C.DeptId ltgt F.DeptId

19
What is next?

• Find all faculty who teach a course offered by a
  department other than their own. List the name of
  the faculty, and the name of the courses they
  teach from other departments
• What if we wanted to find the number of such
  courses? GROUP BY, COUNT
• What if we wanted to return the faculty to never
  taught a course from another department? WE NEED
  A TYPE OF SET DIFFERENCE OPERATOR
• What if we wanted to return all faculty, but if
  the faculty never taught a course from another
  department, simply return no course for them?
  OUTER JOIN