Scribe: Lisa Maria Schiestel and Vitor Carmo Vannuchi
1st Query: We want the students that take at least one required class.
SELECT STUDENT FROM TAKEN, REQUIRED WHERE TAKEN.COURSE == REQUIRED.COURSE
2nd Query: We want the student that has both required courses.
TAKEN/REQUIRED
for this one is a bit trick because we don’t have this division operator in SQL, and for this we need to do some steps.
1) Find all students - (CREATE TABLE ALL_STUDENTS AS) SELECT STUDENT FROM TAKEN
2) Find all students who can’t graduate (NEGATION).
a- Find all students AND required courses — (CREATE TABLE STUDENTS_AND_REQUIRED AS) SELECT * FROM ALL_STUDENTS, REQUIRED
b- Find students AND courses needed to graduate — CREATE TEMP TABLE STUDENTSANDREQUIREDNOTTAKEN AS SELECT * FROM STUDENTS_AND_REQUIRED WHERE NOT EXISTS (SELECT * FROM TAKEN WHERE STUDENTS_AND_REQUIRED.STUDENT = TAKEN.STUDENT AND STUDENT_AND_REQUIRED.COURSE = TAKEN.COURSE
c- Find students who CAN'T graduate — CREATE TEMP TABLE CANTGRADUATE AS SELECT STUDENT FROM STUDENTANDREQUIREDNOTTAKEN
3) Find all students who CAN graduate — CREATE TEMP TABLE CAN GRADUATE AS SELECT * FROM ALL STUDENTS WHERE NOT EXISTS (SELECT * FROM CANNOT GRADUATE WHERE CANNOT GRADUATE.STUDENT = ALLSTUDENTS.STUDENT)
Single query way: SELECT DISTINCT X.STUDENT FROM TAKEN AS X WHERE NOT EXISTS (SELECT * FROM REQUIRED AS Y WHERE NOT EXISTS ( SELECT * FROM TAKEN AS Z WHERE (Z.STUDENT=X.STUDENT) AND (Z.COURSE = Y.COURSE)));
*/How can we count the number of people that can graduate?
*/There’s a count operator in SQL → AGGREGATE
Slide Set: 05_SQL_As_Data_Manipulation_Language
In the data manipulation language in SQL, unknown (null) values are treated as false
<> is equivalent to NOT EQUAL in SQL (in Microsoft Access)
Different implementations in different database softwares (e.g. Oracle, etc.)
Agregators:
sum(A) → 5
count(A) → 4
max(A) → 2
min(A) → 1
Queries with agregators
SELECT COUNT(*)
FROM Invoice
WHERE Idate = # sth missing here → Slide Set: 05_SQL_As_Data_Manipulation_Language, pg.?
GROUP BY: Used with aggregate operators to group the result set by one or more attributes (columns)
SELECT AVG(Grade)
FROM SCORES
GROUP BY Students
/* averages per student */
Slide Set: 05_SQL_As_Data_Manipulation_Language pg. 122
Example:
WHERE X > ALL
Select grades
FROM scores
Slide Set: 05_SQL_As_Data_Manipulation_LanguagepPg. 144: Intersection
Query can be rewritten as
SELECT *
FROM R
INTERSECT
SELECT *
FROM S