Table of Contents

1 database = set of named relations(or tables)

  • set of columns
  • each tuple has a value for each column
  • each attribute has a type(or domain)

1.1 schema

  • structural description of relations in dbname
  • instance
  • key: attribute whose value is unique in each tuple

2 Querying Relational Database

2.1 Query Language

  • Relational Algebra

formal

  • SQL

actual / implemented

3 Relational Algebra

3.1 select, project, join

  • query on set of reltations produces relations as a result
  • use operators to filter, slice, combine results.
  • Select operator:

\[\sigma_condition(Expression)\] \[\s GPA > 3.7 ^ HS < 1000 Student\]

  • Project operators: picks cerntain columns

\[\pi_Apply1,Apply2,...,Applyn(Expression)\] \[ \pi_ID apply\] \[\pi_ID,StudentName(\sigma_GPA > 3.7 Student)\]

3.1.1 cross-product:

  • E1 * E2

3.1.2 natural join

3.1.3 theta join

3.2 Set operators, renaming, notation

3.2.1 Union operator

  • List of college and student names

3.2.2 Intersection operator

  • Join: \(E1 \join E2\)

*

4 SQL

4.1 Basic select statement

  • Three tables.

College(cName, state, enrollment) Student(sID, sName, GPA, sizeHS) Apply(sID, cName, major, decision) unique index: cName, sID, and sID+cName+major.

  • Select decision of students whose high school size is less than 1000, major is computer science and the application college is Standford.
select sname, GPA, decision
from Student, Apply
where Student.sID = Apply.sID
and sizeHS < 1000 and major = 'CS' and cname = 'Standford'
order by GPA desc;

4.2 Table variables and set operators

4.2.1 Table variables

using for alternative name readable, express relation with other prompts.

4.2.2 Set operators

  1. Union, intersect, except
    • select student enrollment.
    select S.sID, sName, GPA, A.cName, enrollment
from Student S, College C, Apply A
where A.sID = S.sID and A.cName = C.cName;
    • select students having the same GPA.
    select S1.sID, S1.sName, S1.GPA, S2.sID, S2.sName, S2.GPA,
from Student S1, Student S2
where S1.GPA = S2.GPA and S1.sID > S2.sID;
    1. Union
      • select union of college names and students names.

      Remember: union will remove duplicates for us. 

      select cName as name from College
union
select sName as name from Student;
    2. Intersect
      • select students both select cs and ee.
      select sID from Apply where major = 'CS'
intersect
select sID from Apply where major = 'EE';
    3. Except
      • select students who apply cs but not ee.
      select sID from Apply where major = 'CS'
except
select sID from Apply where major = 'EE';
  2. Subqueries in WHERE
    • select IDs and names of students who have aplied to major in CS at some college.
    select sID, sName
from Student
where sID in (select sID from Apply where major = 'CS');
    • select max value from a column.
    select cName
from College C1
where not exists (select * from College C2
where C2.enrollment > C1.enrollment);
    • count
    SELECT COUNT(aggregate_expression)
FROM tables
[WHERE conditions];
    1. all, any, exists, not exists.
      • Students not from the smallest high school.
      select sID, sName, sizeHS
from Student
where sizeHS > any(select sizeHS from Student);
  3. Subqueries from select and from
    • Students whoses scaled GPA changes GPA by more than 1.
    select *
from (select sID, sName, GPA, GPA*(sizeHS/1000.0) as scaledGPA
from Student) as G
where (G.scaledGPA - GPA) > 1.0;