# The following commands load the requiste modules.
# **NOTE: If there is a warning, it doesn't seem to affect things.**

%load_ext sql
%sql postgresql://postgres:postgres@localhost/university

%config SqlMagic.style = '_DEPRECATED_DEFAULT'

We can now run SQL commands using magic commands, which is an extensibility mechanism provided by Jupyter.

%sql is for single-line commands
%%sql allows multi-line SQL commands

University Database¶

Below we will use the University database from the class textbook. The University Dataset is the same as the one discussed in the book, and contains randomly populated information about students, courses, and instructors in a university.

You should follow the rest of the Notebook along with the appropriate sections in the book. Each section in the notebook is tagged with the corresponding section in the book.

The schema diagram for the database is as follows:

One drawback of this way of accessing the database is that we can only run valid SQL -- the commands like \d provided by psql are not available to us.

Instead, we will need to query the system catalog (metadata) directly

The first command below is equivalent to \d
The second one is similar to \d instructor.

%%sql
-- Print all the tables.
SELECT table_schema, table_name FROM information_schema.tables
    WHERE table_type = 'BASE TABLE' AND
    table_schema NOT IN ('pg_catalog', 'information_schema', 'priv');

 * postgresql://postgres:***@localhost/university
11 rows affected.

You can see that there are:

some tables that describe objects (e.g., student, course, time_slot, classroom, instructor); and
other tables that describe “relationships” between objects (e.g., takes)
department: info about department
course: info about courses
instructor: info about instructors
takes: binds student with taken courses
section: binds courses with time and location
student: info about students
advisor: binds students and instructors
time_slot: schedule of each time slot
classroom: info about the classrooms
teaches: binds instructors with classes
prereq: relationship between courses

%%sql
-- Print schema for instructor.
SELECT column_name, data_type
    FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'instructor';

 * postgresql://postgres:***@localhost/university
4 rows affected.

%%sql
--SELECT * FROM takes LIMIT 4;
--SELECT * FROM student LIMIT 4;
--SELECT * FROM section LIMIT 4;
--SELECT * FROM course LIMIT 4;
--SELECT * FROM department LIMIT 4;
--SELECT * FROM advisor LIMIT 4;
--SELECT * FROM time_slot LIMIT 4;
--SELECT * FROM classroom LIMIT 4;
--SELECT * FROM teaches LIMIT 4;
--SELECT * FROM prereq LIMIT 4;
SELECT * FROM instructor LIMIT 4;

 * postgresql://postgres:***@localhost/university
4 rows affected.

%%sql
-- Print table instructor.
SELECT * FROM instructor;

 * postgresql://postgres:***@localhost/university
12 rows affected.

Creating schema¶

You can take a look at the DDL.sql file to see how the tables we are using are created. We won’t try to run those commands here since they will only give errors.

!cat DDL.sql

drop table if exists prereq;
drop table if exists time_slot;
drop table if exists advisor;
drop table if exists takes;
drop table if exists student;
drop table if exists teaches;
drop table if exists section;
drop table if exists instructor;
drop table if exists course;
drop table if exists department;
drop table if exists classroom;

create table classroom
	(building		varchar(15),
	 room_number		varchar(7),
	 capacity		numeric(4,0),
	 primary key (building, room_number)
	);

create table department
	(dept_name		varchar(20), 
	 building		varchar(15), 
	 budget		        numeric(12,2) check (budget > 0),
	 primary key (dept_name)
	);

create table course
	(course_id		varchar(8), 
	 title			varchar(50), 
	 dept_name		varchar(20),
	 credits		numeric(2,0) check (credits > 0),
	 primary key (course_id),
	 foreign key (dept_name) references department
		on delete set null
	);

create table instructor
	(ID			varchar(5), 
	 name			varchar(20) not null, 
	 dept_name		varchar(20), 
	 salary			numeric(8,2) check (salary > 29000),
	 primary key (ID),
	 foreign key (dept_name) references department
		on delete set null
	);

create table section
	(course_id		varchar(8), 
         sec_id			varchar(8),
	 semester		varchar(6)
		check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), 
	 year			numeric(4,0) check (year > 1701 and year < 2100), 
	 building		varchar(15),
	 room_number		varchar(7),
	 time_slot_id		varchar(4),
	 primary key (course_id, sec_id, semester, year),
	 foreign key (course_id) references course
		on delete cascade,
	 foreign key (building, room_number) references classroom
		on delete set null
	);

create table teaches
	(ID			varchar(5), 
	 course_id		varchar(8),
	 sec_id			varchar(8), 
	 semester		varchar(6),
	 year			numeric(4,0),
	 primary key (ID, course_id, sec_id, semester, year),
	 foreign key (course_id,sec_id, semester, year) references section
		on delete cascade,
	 foreign key (ID) references instructor
		on delete cascade
	);

create table student
	(ID			varchar(5), 
	 name			varchar(20) not null, 
	 dept_name		varchar(20), 
	 tot_cred		numeric(3,0) check (tot_cred >= 0),
	 primary key (ID),
	 foreign key (dept_name) references department
		on delete set null
	);

create table takes
	(ID			varchar(5), 
	 course_id		varchar(8),
	 sec_id			varchar(8), 
	 semester		varchar(6),
	 year			numeric(4,0),
	 grade		        varchar(2),
	 primary key (ID, course_id, sec_id, semester, year),
	 foreign key (course_id,sec_id, semester, year) references section
		on delete cascade,
	 foreign key (ID) references student
		on delete cascade
	);

create table advisor
	(s_ID			varchar(5),
	 i_ID			varchar(5),
	 primary key (s_ID),
	 foreign key (i_ID) references instructor (ID)
		on delete set null,
	 foreign key (s_ID) references student (ID)
		on delete cascade
	);

create table time_slot
	(time_slot_id		varchar(4),
	 day			varchar(1),
	 start_hr		numeric(2) check (start_hr >= 0 and start_hr < 24),
	 start_min		numeric(2) check (start_min >= 0 and start_min < 60),
	 end_hr			numeric(2) check (end_hr >= 0 and end_hr < 24),
	 end_min		numeric(2) check (end_min >= 0 and end_min < 60),
	 primary key (time_slot_id, day, start_hr, start_min)
	);

create table prereq
	(course_id		varchar(8), 
	 prereq_id		varchar(8),
	 primary key (course_id, prereq_id),
	 foreign key (course_id) references course
		on delete cascade,
	 foreign key (prereq_id) references course
	);

Populating data¶

The DB is populated with one of the scripts

smallRelationsInsertFile.sql
largeRelationsInsertFile.sql

!cat smallRelationsInsertFile.sql

delete from prereq;
delete from time_slot;
delete from advisor;
delete from takes;
delete from student;
delete from teaches;
delete from section;
delete from instructor;
delete from course;
delete from department;
delete from classroom;
insert into classroom values ('Packard', '101', '500');
insert into classroom values ('Painter', '514', '10');
insert into classroom values ('Taylor', '3128', '70');
insert into classroom values ('Watson', '100', '30');
insert into classroom values ('Watson', '120', '50');
insert into department values ('Biology', 'Watson', '90000');
insert into department values ('Comp. Sci.', 'Taylor', '100000');
insert into department values ('Elec. Eng.', 'Taylor', '85000');
insert into department values ('Finance', 'Painter', '120000');
insert into department values ('History', 'Painter', '50000');
insert into department values ('Music', 'Packard', '80000');
insert into department values ('Physics', 'Watson', '70000');
insert into course values ('BIO-101', 'Intro. to Biology', 'Biology', '4');
insert into course values ('BIO-301', 'Genetics', 'Biology', '4');
insert into course values ('BIO-399', 'Computational Biology', 'Biology', '3');
insert into course values ('CS-101', 'Intro. to Computer Science', 'Comp. Sci.', '4');
insert into course values ('CS-190', 'Game Design', 'Comp. Sci.', '4');
insert into course values ('CS-315', 'Robotics', 'Comp. Sci.', '3');
insert into course values ('CS-319', 'Image Processing', 'Comp. Sci.', '3');
insert into course values ('CS-347', 'Database System Concepts', 'Comp. Sci.', '3');
insert into course values ('EE-181', 'Intro. to Digital Systems', 'Elec. Eng.', '3');
insert into course values ('FIN-201', 'Investment Banking', 'Finance', '3');
insert into course values ('HIS-351', 'World History', 'History', '3');
insert into course values ('MU-199', 'Music Video Production', 'Music', '3');
insert into course values ('PHY-101', 'Physical Principles', 'Physics', '4');
insert into instructor values ('10101', 'Srinivasan', 'Comp. Sci.', '65000');
insert into instructor values ('12121', 'Wu', 'Finance', '90000');
insert into instructor values ('15151', 'Mozart', 'Music', '40000');
insert into instructor values ('22222', 'Einstein', 'Physics', '95000');
insert into instructor values ('32343', 'El Said', 'History', '60000');
insert into instructor values ('33456', 'Gold', 'Physics', '87000');
insert into instructor values ('45565', 'Katz', 'Comp. Sci.', '75000');
insert into instructor values ('58583', 'Califieri', 'History', '62000');
insert into instructor values ('76543', 'Singh', 'Finance', '80000');
insert into instructor values ('76766', 'Crick', 'Biology', '72000');
insert into instructor values ('83821', 'Brandt', 'Comp. Sci.', '92000');
insert into instructor values ('98345', 'Kim', 'Elec. Eng.', '80000');
insert into section values ('BIO-101', '1', 'Summer', '2009', 'Painter', '514', 'B');
insert into section values ('BIO-301', '1', 'Summer', '2010', 'Painter', '514', 'A');
insert into section values ('CS-101', '1', 'Fall', '2009', 'Packard', '101', 'H');
insert into section values ('CS-101', '1', 'Spring', '2010', 'Packard', '101', 'F');
insert into section values ('CS-190', '1', 'Spring', '2009', 'Taylor', '3128', 'E');
insert into section values ('CS-190', '2', 'Spring', '2009', 'Taylor', '3128', 'A');
insert into section values ('CS-315', '1', 'Spring', '2010', 'Watson', '120', 'D');
insert into section values ('CS-319', '1', 'Spring', '2010', 'Watson', '100', 'B');
insert into section values ('CS-319', '2', 'Spring', '2010', 'Taylor', '3128', 'C');
insert into section values ('CS-347', '1', 'Fall', '2009', 'Taylor', '3128', 'A');
insert into section values ('EE-181', '1', 'Spring', '2009', 'Taylor', '3128', 'C');
insert into section values ('FIN-201', '1', 'Spring', '2010', 'Packard', '101', 'B');
insert into section values ('HIS-351', '1', 'Spring', '2010', 'Painter', '514', 'C');
insert into section values ('MU-199', '1', 'Spring', '2010', 'Packard', '101', 'D');
insert into section values ('PHY-101', '1', 'Fall', '2009', 'Watson', '100', 'A');
insert into teaches values ('10101', 'CS-101', '1', 'Fall', '2009');
insert into teaches values ('10101', 'CS-315', '1', 'Spring', '2010');
insert into teaches values ('10101', 'CS-347', '1', 'Fall', '2009');
insert into teaches values ('12121', 'FIN-201', '1', 'Spring', '2010');
insert into teaches values ('15151', 'MU-199', '1', 'Spring', '2010');
insert into teaches values ('22222', 'PHY-101', '1', 'Fall', '2009');
insert into teaches values ('32343', 'HIS-351', '1', 'Spring', '2010');
insert into teaches values ('45565', 'CS-101', '1', 'Spring', '2010');
insert into teaches values ('45565', 'CS-319', '1', 'Spring', '2010');
insert into teaches values ('76766', 'BIO-101', '1', 'Summer', '2009');
insert into teaches values ('76766', 'BIO-301', '1', 'Summer', '2010');
insert into teaches values ('83821', 'CS-190', '1', 'Spring', '2009');
insert into teaches values ('83821', 'CS-190', '2', 'Spring', '2009');
insert into teaches values ('83821', 'CS-319', '2', 'Spring', '2010');
insert into teaches values ('98345', 'EE-181', '1', 'Spring', '2009');
insert into student values ('00128', 'Zhang', 'Comp. Sci.', '102');
insert into student values ('12345', 'Shankar', 'Comp. Sci.', '32');
insert into student values ('19991', 'Brandt', 'History', '80');
insert into student values ('23121', 'Chavez', 'Finance', '110');
insert into student values ('44553', 'Peltier', 'Physics', '56');
insert into student values ('45678', 'Levy', 'Physics', '46');
insert into student values ('54321', 'Williams', 'Comp. Sci.', '54');
insert into student values ('55739', 'Sanchez', 'Music', '38');
insert into student values ('70557', 'Snow', 'Physics', '0');
insert into student values ('76543', 'Brown', 'Comp. Sci.', '58');
insert into student values ('76653', 'Aoi', 'Elec. Eng.', '60');
insert into student values ('98765', 'Bourikas', 'Elec. Eng.', '98');
insert into student values ('98988', 'Tanaka', 'Biology', '120');
insert into takes values ('00128', 'CS-101', '1', 'Fall', '2009', 'A');
insert into takes values ('00128', 'CS-347', '1', 'Fall', '2009', 'A-');
insert into takes values ('12345', 'CS-101', '1', 'Fall', '2009', 'C');
insert into takes values ('12345', 'CS-190', '2', 'Spring', '2009', 'A');
insert into takes values ('12345', 'CS-315', '1', 'Spring', '2010', 'A');
insert into takes values ('12345', 'CS-347', '1', 'Fall', '2009', 'A');
insert into takes values ('19991', 'HIS-351', '1', 'Spring', '2010', 'B');
insert into takes values ('23121', 'FIN-201', '1', 'Spring', '2010', 'C+');
insert into takes values ('44553', 'PHY-101', '1', 'Fall', '2009', 'B-');
insert into takes values ('45678', 'CS-101', '1', 'Fall', '2009', 'F');
insert into takes values ('45678', 'CS-101', '1', 'Spring', '2010', 'B+');
insert into takes values ('45678', 'CS-319', '1', 'Spring', '2010', 'B');
insert into takes values ('54321', 'CS-101', '1', 'Fall', '2009', 'A-');
insert into takes values ('54321', 'CS-190', '2', 'Spring', '2009', 'B+');
insert into takes values ('55739', 'MU-199', '1', 'Spring', '2010', 'A-');
insert into takes values ('76543', 'CS-101', '1', 'Fall', '2009', 'A');
insert into takes values ('76543', 'CS-319', '2', 'Spring', '2010', 'A');
insert into takes values ('76653', 'EE-181', '1', 'Spring', '2009', 'C');
insert into takes values ('98765', 'CS-101', '1', 'Fall', '2009', 'C-');
insert into takes values ('98765', 'CS-315', '1', 'Spring', '2010', 'B');
insert into takes values ('98988', 'BIO-101', '1', 'Summer', '2009', 'A');
insert into takes values ('98988', 'BIO-301', '1', 'Summer', '2010', null);
insert into advisor values ('00128', '45565');
insert into advisor values ('12345', '10101');
insert into advisor values ('23121', '76543');
insert into advisor values ('44553', '22222');
insert into advisor values ('45678', '22222');
insert into advisor values ('76543', '45565');
insert into advisor values ('76653', '98345');
insert into advisor values ('98765', '98345');
insert into advisor values ('98988', '76766');
insert into time_slot values ('A', 'M', '8', '0', '8', '50');
insert into time_slot values ('A', 'W', '8', '0', '8', '50');
insert into time_slot values ('A', 'F', '8', '0', '8', '50');
insert into time_slot values ('B', 'M', '9', '0', '9', '50');
insert into time_slot values ('B', 'W', '9', '0', '9', '50');
insert into time_slot values ('B', 'F', '9', '0', '9', '50');
insert into time_slot values ('C', 'M', '11', '0', '11', '50');
insert into time_slot values ('C', 'W', '11', '0', '11', '50');
insert into time_slot values ('C', 'F', '11', '0', '11', '50');
insert into time_slot values ('D', 'M', '13', '0', '13', '50');
insert into time_slot values ('D', 'W', '13', '0', '13', '50');
insert into time_slot values ('D', 'F', '13', '0', '13', '50');
insert into time_slot values ('E', 'T', '10', '30', '11', '45 ');
insert into time_slot values ('E', 'R', '10', '30', '11', '45 ');
insert into time_slot values ('F', 'T', '14', '30', '15', '45 ');
insert into time_slot values ('F', 'R', '14', '30', '15', '45 ');
insert into time_slot values ('G', 'M', '16', '0', '16', '50');
insert into time_slot values ('G', 'W', '16', '0', '16', '50');
insert into time_slot values ('G', 'F', '16', '0', '16', '50');
insert into time_slot values ('H', 'W', '10', '0', '12', '30');
insert into prereq values ('BIO-301', 'BIO-101');
insert into prereq values ('BIO-399', 'BIO-101');
insert into prereq values ('CS-190', 'CS-101');
insert into prereq values ('CS-315', 'CS-101');
insert into prereq values ('CS-319', 'CS-101');
insert into prereq values ('CS-347', 'CS-101');
insert into prereq values ('EE-181', 'PHY-101');

%%sql
-- Test connection showing one table.
SELECT * FROM takes;

 * postgresql://postgres:***@localhost/university
22 rows affected.

%%sql
-- Find the names of all instructors.

 * postgresql://postgres:***@localhost/university
(psycopg2.ProgrammingError) can't execute an empty query
[SQL: -- Find the names of all instructors.]
(Background on this error at: https://sqlalche.me/e/20/f405)

(3.2) SQL Data definition¶

%%sql
-- Delete the relation.
DROP TABLE IF EXISTS department_tmp;
-- Create a relation.
CREATE TABLE department_tmp (
    dept_name varchar(20),
    building varchar(15),
    -- 12 digits, 2 digits after decimal point.
    budget numeric(12, 2),
    PRIMARY KEY (dept_name)
);

 * postgresql://postgres:***@localhost/university
Done.
Done.

[]

%%sql
-- Empty relation.
DELETE FROM department_tmp;
-- Insert.
INSERT INTO department_tmp VALUES ('Packard', '101', '500');
SELECT * FROM department_tmp;

 * postgresql://postgres:***@localhost/university
0 rows affected.
1 rows affected.
1 rows affected.

%%sql
-- Empty relation.
DELETE FROM department_tmp;

 * postgresql://postgres:***@localhost/university
0 rows affected.

[]

%%sql
SELECT * FROM department_tmp;

 * postgresql://postgres:***@localhost/university
0 rows affected.

%%sql
-- Insert.
INSERT INTO department_tmp VALUES ('Packard', '101', '500');
SELECT * FROM department_tmp;

 * postgresql://postgres:***@localhost/university
1 rows affected.
1 rows affected.

%%sql
-- Add an attribute.
ALTER TABLE department_tmp ADD city VARCHAR(20);
SELECT * FROM department_tmp;

 * postgresql://postgres:***@localhost/university
Done.
1 rows affected.

%%sql
-- Remove an attribute.
ALTER TABLE department_tmp DROP city;
SELECT * FROM department_tmp;

 * postgresql://postgres:***@localhost/university
Done.
1 rows affected.

(3.3.1) Queries on a single relation¶

%%sql
-- Projection.
SELECT name FROM instructor;

 * postgresql://postgres:***@localhost/university
12 rows affected.

%%sql
SELECT dept_name FROM instructor;

 * postgresql://postgres:***@localhost/university
12 rows affected.

%%sql
SELECT DISTINCT dept_name FROM instructor;

 * postgresql://postgres:***@localhost/university
7 rows affected.

%%sql
SELECT id, name, dept_name, salary FROM instructor;

 * postgresql://postgres:***@localhost/university
12 rows affected.

%%sql
SELECT id, name, dept_name, salary * 1.1 FROM instructor LIMIT 4;

 * postgresql://postgres:***@localhost/university
4 rows affected.

%%sql
SELECT name FROM instructor WHERE dept_name = 'Comp. Sci.';

 * postgresql://postgres:***@localhost/university
3 rows affected.

%%sql
SELECT name FROM instructor WHERE dept_name = 'Comp. Sci.' AND salary > 70000;

 * postgresql://postgres:***@localhost/university
2 rows affected.

(3.3.2) Queries on multiple relations¶

%%sql
SELECT * FROM instructor LIMIT 4;

 * postgresql://postgres:***@localhost/university
4 rows affected.

%%sql
SELECT * FROM department LIMIT 4;

 * postgresql://postgres:***@localhost/university
4 rows affected.

%%sql
-- Find the name of instructors with their dept name and dept building name.
-- It is a join.
SELECT name, instructor.dept_name, building
    FROM instructor, department
    WHERE instructor.dept_name = department.dept_name;

 * postgresql://postgres:***@localhost/university
12 rows affected.

%%sql
-- Cartesian product of two relations.
SELECT * FROM instructor, teaches;

 * postgresql://postgres:***@localhost/university
180 rows affected.

%%sql
-- Find instructors who have taught some course and the courses they taught.
-- Note that the duplicates are not removed.
SELECT name, course_id
    FROM instructor, teaches
    WHERE instructor.ID = teaches.ID;

 * postgresql://postgres:***@localhost/university
15 rows affected.

%%sql
-- Removing the duplicates.
SELECT DISTINCT name, course_id
    FROM instructor, teaches
    WHERE instructor.ID = teaches.ID;

 * postgresql://postgres:***@localhost/university
14 rows affected.

%%sql
-- Find instructors who have taught some course in the CS dept and courses they taught.
SELECT DISTINCT name, course_id
    FROM instructor, teaches
    WHERE instructor.ID = teaches.ID AND
        instructor.dept_name = 'Comp. Sci.';

 * postgresql://postgres:***@localhost/university
7 rows affected.

(3.4) Additional basic operations¶

%%sql
-- Rename in the SELECT clause.
-- name can be confusing so we can rename it
SELECT DISTINCT name AS instructor_name, course_id
    FROM instructor, teaches
    WHERE instructor.ID = teaches.ID;

 * postgresql://postgres:***@localhost/university
14 rows affected.

%%sql
-- Rename relations in the WHERE clause.
SELECT DISTINCT T.name, S.course_id
    FROM instructor AS T, teaches AS S
    WHERE T.ID = S.ID;

 * postgresql://postgres:***@localhost/university
14 rows affected.

%%sql
-- Find the names of all instructors whose salary is greater than at least one instructor in the Biology dept.
-- E.g., the minimum salary in the biology dept.
SELECT DISTINCT T.name, T.salary
    FROM instructor AS T, instructor AS S
    WHERE T.salary > S.salary AND S.dept_name = 'Biology';

 * postgresql://postgres:***@localhost/university
7 rows affected.

%%sql
-- Regex matching.
SELECT dept_name, building
    FROM department
    WHERE building like '%Wats%';

 * postgresql://postgres:***@localhost/university
2 rows affected.

%%sql
-- Get the name of all the fields after a join.
SELECT DISTINCT instructor.*, teaches.*
    FROM instructor, teaches
    WHERE instructor.ID = teaches.ID;

 * postgresql://postgres:***@localhost/university
15 rows affected.

%%sql
SELECT name
    FROM instructor
    WHERE dept_name = 'Physics'
    ORDER BY name;

 * postgresql://postgres:***@localhost/university
2 rows affected.

%%sql
-- Sorting on multiple attributes.
SELECT * FROM instructor
    ORDER BY salary DESC, name ASC;

 * postgresql://postgres:***@localhost/university
12 rows affected.

(3.5) Set operations¶

%%sql
SELECT * FROM course LIMIT 4;

 * postgresql://postgres:***@localhost/university
4 rows affected.

%%sql
SELECT * FROM section LIMIT 4;

 * postgresql://postgres:***@localhost/university
4 rows affected.

%%sql
-- Set of all courses taught in Fall 2009 semester.
SELECT DISTINCT c.course_id
    FROM course AS c, section AS s
    WHERE s.semester = 'Fall' AND s.year = '2009'
    ORDER BY c.course_id;

 * postgresql://postgres:***@localhost/university
13 rows affected.

%%sql
-- Set of all courses taught in Spring 2009 semester.
SELECT DISTINCT c.course_id
    FROM course AS c, section AS s
    WHERE s.semester = 'Spring' AND s.year = '2009';

 * postgresql://postgres:***@localhost/university
13 rows affected.

%%sql
(SELECT DISTINCT c.course_id
     FROM course AS c, section AS s
     WHERE s.semester = 'Spring' AND s.year = '2009')
UNION
(SELECT DISTINCT c.course_id
     FROM course AS c, section AS s
     WHERE s.semester = 'Fall' AND s.year = '2009')

 * postgresql://postgres:***@localhost/university
13 rows affected.

%%sql
(SELECT DISTINCT c.course_id
     FROM course AS c, section AS s
     WHERE s.semester = 'Spring' AND s.year = '2009')
INTERSECT
(SELECT DISTINCT c.course_id
     FROM course AS c, section AS s
     WHERE s.semester = 'Fall' AND s.year = '2007')

 * postgresql://postgres:***@localhost/university
0 rows affected.

(3.6) NULL values¶

(3.7) Aggregate functions¶

Count¶

%%sql
SELECT * FROM instructor;

 * postgresql://postgres:***@localhost/university
12 rows affected.

%%sql
-- Count instructors by department.
SELECT dept_name, count(*)
    FROM instructor
    GROUP BY dept_name
    ORDER BY count;

 * postgresql://postgres:***@localhost/university
7 rows affected.

%%sql
-- Compute the average salary of instructors in the CS dept.
SELECT AVG(salary) AS avg_salary
    FROM instructor
    WHERE dept_name = 'Comp. Sci.';

 * postgresql://postgres:***@localhost/university
1 rows affected.

%%sql
-- Count the elements in a table.
SELECT COUNT(*) FROM instructor;

 * postgresql://postgres:***@localhost/university
1 rows affected.

%%sql
-- Count the distinct ids.
SELECT COUNT(DISTINCT ID) FROM instructor;

 * postgresql://postgres:***@localhost/university
1 rows affected.

%%sql
SELECT *
    FROM teaches
    WHERE semester = 'Spring' and year = '2009';

 * postgresql://postgres:***@localhost/university
3 rows affected.

%%sql
-- COUNT() counts the number of elements in a group by.
SELECT COUNT (DISTINCT ID)
    FROM teaches
    WHERE semester = 'Spring' and year = '2009';

 * postgresql://postgres:***@localhost/university
1 rows affected.

%%sql
SELECT COUNT (*) FROM course;

 * postgresql://postgres:***@localhost/university
1 rows affected.

# %%sql
# -- Distinct doesn't work with count.
# -- SELECT COUNT (DISTINCT *) FROM course;

%%sql
-- Find the average dept in each department.
SELECT dept_name, AVG(salary) AS avg_salary
    FROM instructor
    GROUP BY dept_name;

 * postgresql://postgres:***@localhost/university
7 rows affected.

%%sql
-- Find the number of instructors in each dept who teach a course in Spring 2007.
SELECT dept_name, COUNT(DISTINCT instructor.ID) AS instr_count
    FROM instructor, teaches
    WHERE instructor.ID = teaches.ID
        AND semester = 'Spring' AND year = 2009
    GROUP BY dept_name;

 * postgresql://postgres:***@localhost/university
2 rows affected.

Having¶

%%sql
-- Get the department having instructors with an average salary larger than $42k.
SELECT dept_name, AVG(salary) AS avg_salary
    FROM instructor
    GROUP BY dept_name
    HAVING AVG(salary) > 42000;

 * postgresql://postgres:***@localhost/university
6 rows affected.

%%sql
-- Report the average total credits of students taking courses in 2009
-- with at least 2 students.
SELECT course_id, semester, year, sec_id, AVG(tot_cred)
    FROM student, takes
    WHERE student.ID = takes.ID AND year = 2009
    GROUP BY course_id, semester, year, sec_id
    HAVING COUNT(student.ID) >= 2;

 * postgresql://postgres:***@localhost/university
3 rows affected.

(3.8) Nested subqueries¶

%%sql
SELECT course_id FROM section WHERE semester = 'Fall' and year=2009

 * postgresql://postgres:***@localhost/university
3 rows affected.

%%sql
SELECT course_id FROM section WHERE semester = 'Spring' and year=2009

 * postgresql://postgres:***@localhost/university
3 rows affected.

%%sql
-- Find all the courses in either fall 2009 or spring 2009, using nested subquery.
SELECT course_id
    FROM section
    WHERE semester = 'Fall' AND year=2009
        OR course_id IN
            -- Nested query.
            (SELECT course_id FROM section
                WHERE semester = 'Spring' AND year=2009)

 * postgresql://postgres:***@localhost/university
6 rows affected.

%%sql
-- Find all the instructors that are not Mozart or Einstein.
SELECT DISTINCT name
    FROM instructor
    WHERE name NOT IN ('Mozart', 'Einstein');

 * postgresql://postgres:***@localhost/university
10 rows affected.

%%sql
-- Find the dept with an average salary per instruction larger than $42k.
-- This is an alternative query to the HAVING query.
SELECT tmp.dept_name, tmp.avg_salary
    FROM
        (SELECT dept_name, AVG(salary) AS avg_salary
          FROM instructor
          GROUP BY dept_name) AS tmp
    WHERE avg_salary > 42000

 * postgresql://postgres:***@localhost/university
6 rows affected.

In many cases you might find it easier to create temporary tables, especially for queries involving finding “max” or “min”. This also allows you to break down the full query AND makes it easier to debug. It is preferable to use the WITH construct for this purpose. The syntax AND support differs across systems, but here is the link to PostgreSQL: http://www.postgresql.org/docs/9.0/static/queries-with.html

These are also called Common Table Expressions (CTEs).

%%sql
-- Find department with the maximum budget.
WITH max_budget(value) as (
        SELECT MAX(budget) FROM department)
    SELECT department.dept_name, budget
        FROM department, max_budget
        WHERE department.budget = max_budget.value

 * postgresql://postgres:***@localhost/university
1 rows affected.

(3.9) Modification of the DB¶

Other queries¶

%%sql
SELECT * FROM course;

 * postgresql://postgres:***@localhost/university
13 rows affected.

%%sql
-- Reports the courses with titles containing Biology.
SELECT *
    FROM course
    WHERE title LIKE '%Biology%';

 * postgresql://postgres:***@localhost/university
2 rows affected.

%%sql
-- There are two  courses. How many students are enrolled in the first one (ever)?
SELECT *
    FROM takes
    WHERE course_id = 'BIO-101';

 * postgresql://postgres:***@localhost/university
1 rows affected.

%%sql
-- What about in Summer 2009?
SELECT *
    FROM takes
    WHERE course_id = 'BIO-101' AND year = 2009 AND semester = 'Summer';

 * postgresql://postgres:***@localhost/university
1 rows affected.

Aggregates¶

%%sql
--  Count the number of instructors in Finance.
SELECT COUNT(*)
    FROM instructor WHERE dept_name = 'Finance';

 * postgresql://postgres:***@localhost/university
1 rows affected.

%%sql
-- Find the instructor with the maximum salary using subquery.
SELECT *
    FROM instructor
    WHERE salary =
        (SELECT MAX(salary) FROM instructor);

 * postgresql://postgres:***@localhost/university
1 rows affected.

(3.3.2) Joins AND Cartesian Product¶

%%sql
-- To find building names for all instructors, we must do a join between two relations.
SELECT name, instructor.dept_name, building
    FROM instructor, department
    WHERE instructor.dept_name = department.dept_name;

 * postgresql://postgres:***@localhost/university
12 rows affected.

%%sql
-- Since the join here is a equality join on the common attributes in the two relations:
SELECT name, instructor.dept_name, building
    FROM instructor NATURAL JOIN department;

 * postgresql://postgres:***@localhost/university
12 rows affected.

%%sql
-- On the other hand, just doing the following (i.e., just the Cartesian Product) will lead to a large number of tuples, most
-- of which are not meaningful.
SELECT name, instructor.dept_name, building
    FROM instructor, department;

 * postgresql://postgres:***@localhost/university
84 rows affected.

Renaming using “as”¶

%%sql
-- AS can be used to rename tables AND simplify queries.
EXPLAIN
    -- ANALYZE
    SELECT DISTINCT T.name
        FROM instructor AS T, instructor AS S
        WHERE T.salary > S.salary AND S.dept_name = 'Biology';

 * postgresql://postgres:***@localhost/university
8 rows affected.

Self-joins (WHERE two of the relations in the FROM clause are the same) are impossible without using as. The following query associates a course with the pre-requisite of one of its pre-requisites. There is no way to disambiguate the columns without some form of renaming.

%%sql
EXPLAIN
    ANALYZE
        SELECT p1.course_id, p2.prereq_id AS pre_prereq_id
            FROM prereq p1, prereq p2
            WHERE p1.prereq_id = p2.course_id;

 * postgresql://postgres:***@localhost/university
8 rows affected.

The small University database doesn’t have any chains of this kind. You can try adding a new tuple using a new tuple. Now the query will return an answer.

%sql insert into prereq values ('CS-101', 'PHY-101');

 * postgresql://postgres:***@localhost/university
1 rows affected.

[]

%%sql
SELECT p1.course_id, p2.prereq_id AS pre_prereq_id
    FROM prereq p1, prereq p2
    WHERE p1.prereq_id = p2.course_id;

 * postgresql://postgres:***@localhost/university
4 rows affected.

LIMIT¶

PostgreSQL allows you to limit the number of results displayed which is useful for debugging etc. Here is an example.

%sql SELECT * FROM instructor limit 2;

 * postgresql://postgres:***@localhost/university
2 rows affected.

Try your own queries¶

Feel free to use the cells below to write new queries. You can also just modify the above queries directly if you’d like.