comp0034_SQL/simpsons_queries.sql at master · UCLComputerScience/comp0034_SQL · GitHub

Repository navigation

History

70 lines (57 loc) · 1.82 KB

Raw

SELECT *

FROM students

JOIN grades ON id = student_id;

--table.column can be used to disambiguate column names:

SELECT *

FROM students

JOIN grades ON students.id = grades.student_id

--Filtering columns in a join

SELECT name, course_id, grade

FROM students

JOIN grades ON id = student_id;

--Filtered join (JOIN with WHERE)

SELECT name, course_id, grade

FROM students

JOIN grades ON id = student_id

WHERE name = 'Bart';

--Poor query design

SELECT name, id, course_id, grade

FROM students

JOIN grades ON id = 123

WHERE id = student_id;

--Improved query design - filter on WHERE not ON

SELECT name, id, course_id, grade

FROM students

JOIN grades ON id = student_id

WHERE id = 123;

--Giving names to tables

SELECT s.name, g.*

FROM students s

JOIN grades g ON s.id = g.student_id

WHERE g.grade <= 'C';

--Multi-table join

SELECT c.name

FROM courses c

JOIN grades g ON g.course_id = c.id

JOIN students bart ON g.student_id = bart.id

WHERE bart.name = 'Bart'

AND g.grade <= 'B-';

--Courses taken by Bart and Lisa?

-- suboptimal solution: requires us to know Bart/Lisa's Student IDs, and only returns course IDs, not names

SELECT bart.course_id

FROM grades bart

JOIN grades lisa ON lisa.course_id = bart.course_id

WHERE bart.student_id = 123

AND lisa.student_id = 888;

--improved solution

SELECT DISTINCT c.name

FROM courses c

JOIN grades g1 ON g1.course_id = c.id

JOIN students bart ON g1.student_id = bart.id

JOIN grades g2 ON g2.course_id = c.id

JOIN students lisa ON g2.student_id = lisa.id

WHERE bart.name = 'Bart'

AND lisa.name = 'Lisa';

--Write queries to answer the following questions:

--1. What are the names of all teachers Bart has had?

--2. How many total students has Ms. Krabappel taught, and what are their names?

comp0034_SQL/simpsons_queries.sql at master · UCLComputerScience/comp0034_SQL · GitHub

Repository navigation

Expand file tree

Breadcrumbs

simpsons_queries.sql

Latest commit

History

Breadcrumbs

simpsons_queries.sql

File metadata and controls