SQL

Data Definition language (DDL)

• Create table
• Drop table

Data Manipulation Language (DML)

• Select
• Insert
• Delete
• Update

Other Commands indexes, constraints, views, triggers, transactions, authorization, ...

The SELECT statement

Select A1, A2, ..., Am  # What to return
From R1, R2, ..., Rm  # Relations
Where condition # Combine and filter

Our tables

College(cNAme, state, enrollment) Student(sID, sName, GPA, sizeHS) Apply(sID, cName, major, decision)

Join condition

select sName, major  # This returns duplicates. Why?
from Student, Apply
where student.sID = Apply.sID; # This gives a natural join on sID

To get unique elements

select **distinct** sName, major 
from Student, Apply
where student.sID = Apply.sID;

select cName       # This will break. Why?                
from College, apply
where college.cname = apply.cname 
and enrollment > 20000 and major = 'CS';

Sorting

select Student.sID, sName, GPA, Apply.cName, enrollment
from Student, College, Apply
where Apply.sID = Student.sID and Apply.cName = College.cName
**order by GPA desc, enrollment**; # default is ascending

Pattern matching

select sID, major  
from Apply
**where major like '%bio%'**;

select *
from Apply
**where major like '%bio%'**;

Arithmetic

select sID, sName, GPA, sizeHS, GPA*(sizeHS/1000.0)
from Student;

select sID, sName, GPA, sizeHS, **GPA*(sizeHS/1000.0) as scaledGPA** # give the new column a name
from Student;

Table Variables and Set Operators

Select A1, A2, ..., Am  
From R1, R2, ..., Rm  # Table variables
Where condition

We can use table variables to make queries more readable and concise

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;

Find all students who have the same GPA

select S1.sID, S1.sName, S2.GPA, S2.sID, S2.sName, S2.GPA # What's the problem here?
from Student S1, Student S2
where S1.GPA = S2.GPA;

select S1.sID, S1.sName, S2.GPA, S2.sID, S2.sName, S2.GPA # Are we done yet?
from Student S1, Student S2
**where S1.GPA = S2.GPA and S1.sID <> S2.sID**; # Note that <> is not equals in SQL

select S1.sID, S1.sName, S2.GPA, S2.sID, S2.sName, S2.GPA 
from Student S1, Student S2
**where S1.GPA = S2.GPA and S1.sID < S2.sID**;

Set operators

Union

Removes duplicates

select cName as name from College
union
select sName as name from Student;

Keep duplicates

select cName as name from College
union all 
select sName as name from Student;

Intersect

select sID from Apply where major = 'CS'
intersect
select sID from Apply where major = 'EE';

Not all DBs support intersection

select distinct A1.sID
from Apply A1, Apply A2
where A1.sID = A2.sID and A1.major = 'CS' and A2.major = 'EE';

Difference

select sID from Apply where major = 'CS'
except
select sID from Apply where major = 'EE';

Not all DBs support the except operator. We haven't learned the tools to write this without an except yet.