Skip to content

Instantly share code, notes, and snippets.

@Arique1104

Arique1104/SQL_II.md Secret

Last active February 13, 2024 00:27
Show Gist options
  • Save Arique1104/10593f0adbd358dfb1ec2bfa092cf152 to your computer and use it in GitHub Desktop.
Save Arique1104/10593f0adbd358dfb1ec2bfa092cf152 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
SQL_II.md

Intermediate SQL Workshop

  • Run psql
  • You may need to run CREATE DATABASE intermediate_sql;
  • To exit this shell, you can run CTRL d

Create tables

Let's create some tables in the database.

CREATE TABLE students(id SERIAL, name TEXT);
CREATE TABLE classes(id SERIAL, name TEXT, teacher_id INT);
CREATE TABLE teachers(id SERIAL, name TEXT, room_number INT);
CREATE TABLE enrollments(id SERIAL, student_id INT, class_id INT, grade INT);

Add data

Let's insert some students.

INSERT INTO students (name)
VALUES ('Penelope'),
       ('Peter'),
       ('Pepe'),
       ('Parth'),
       ('Priscilla'),
       ('Pablo'),
       ('Puja'),
       ('Patricia'),
       ('Piper'),
       ('Paula'),
       ('Pamela'),
       ('Paige'),
       ('Peggy'),
       ('Pedro'),
       ('Phoebe'),
       ('Pajak'),
       ('Parker'),
       ('Priyal'),
       ('Paxton'),
       ('Patrick');

Let's add some teachers.

INSERT INTO teachers (name, room_number)
VALUES ('Phillips', 456),
       ('Vandergrift', 120),
       ('Mauch', 101),
       ('Patel', 320),
       ('Marquez', 560),
       ('Boykin', 200),
       ('Phlop', 333),
       ('Pendergrass', 222),
       ('Palomo', 323),
       ('Altshuler', 543),
       ('Aleman', 187),
       ('Ashley', 432),
       ('Bonacci', 399),
       ('Brazukas', 287),
       ('Brockington', 299),
       ('Brizuela', 376),
       ('Burkhart', 199),
       ('Choi', 463),
       ('Shah', 354),
       ('Dimaggio', 251);

Let's add some classes.

INSERT INTO classes (name, teacher_id)
VALUES ('Cooking Pasta', 1),
       ('Yoga', 1),
       ('How to Guitar', 2),
       ('Gym', 3),
       ('Football', 4),
       ('Calculus', 5),
       ('Fruit', 6),
       ('Social Studies', 7),
       ('English', 8),
       ('Programming', 9),
       ('Singing', 10),
       ('Fashion', 11);

Lastly, let's add some enrollments!

INSERT INTO enrollments (student_id, class_id, grade)
VALUES (1, 1, 60),
       (2, 2, 70),
       (2, 4, 100),
       (3, 2, 74),
       (4, 3, 82),
       (5, 3, 45),
       (5, 4, 50),
       (7, 11, 62),
       (7, 10, 76),
       (7, 9, 81),
       (7, 8, 91),
       (8, 8, 84),
       (9, 8, 88),
       (9, 7, 83),
       (10, 7, 93),
       (10, 5, 95),
       (11, 5, 95),
       (11, 11, 80),
       (11, 6, 95),
       (11, 1, 94),
       (11, 2, 60),
       (12, 6, 55),
       (13, 7, 97),
       (14, 10, 86),
       (15, 9, 77),
       (15, 4, 93),
       (15, 1, 73),
       (16, 2, 79),
       (16, 6, 73),
       (17, 7, 86),
       (17, 8, 91),
       (17, 9, 93),
       (18, 10, 94),
       (19, 4, 84),
       (20, 1, 85),
       (20, 11, 89),
       (20, 3, 98);

Practice!!

  • List all the students and their classes
ariqueaguilar=# SELECT students.name as student_name, classes.name as class_name from students
INNER JOIN enrollments
ON students.id = enrollments.student_id
INNER JOIN classes
ON enrollments.class_id = classes.id;
 student_name |   class_name
--------------+----------------
 Penelope     | Cooking Pasta
 Peter        | Yoga
 Peter        | Gym
 Pepe         | Yoga
 Parth        | How to Guitar
 Priscilla    | How to Guitar
 Priscilla    | Gym
 Priscilla    | Gym
 Puja         | Singing
 Puja         | Programming
 Puja         | English
 Puja         | Social Studies
 Patricia     | Social Studies
 Piper        | Social Studies
 Piper        | Fruit
 Paula        | Fruit
 Paula        | Football
 Pamela       | Cooking Pasta
 Pamela       | Yoga
 Pamela       | Football
 Pamela       | Calculus
 Pamela       | Singing
 Paige        | Calculus
 Peggy        | Fruit
 Pedro        | Programming
 Phoebe       | English
 Phoebe       | Gym
 Phoebe       | Cooking Pasta
 Pajak        | Yoga
 Pajak        | Calculus
 Parker       | Social Studies
 Parker       | English
 Parker       | Fruit
 Priyal       | Programming
 Paxton       | Gym
 Patrick      | Cooking Pasta
 Patrick      | Singing
 Patrick      | How to Guitar
(37 rows)
  • List all the students and their classes and rename the columns to "student" and "class"
ariqueaguilar=# SELECT students.name as student, classes.name as class from students
INNER JOIN enrollments
ON students.id = enrollments.student_id
INNER JOIN classes
ON enrollments.class_id = classes.id;

  student  |     class
-----------+----------------
 Penelope  | Cooking Pasta
 Peter     | Yoga
 Peter     | Gym
 Pepe      | Yoga
 Parth     | How to Guitar
 Priscilla | How to Guitar
 Priscilla | Gym
 Puja      | Singing
 Puja      | Programming
 Puja      | English
 Puja      | Social Studies
 Patricia  | Social Studies
 Piper     | Social Studies
 Piper     | Fruit
 Paula     | Fruit
 Paula     | Football
 Pamela    | Cooking Pasta
 Pamela    | Yoga
 Pamela    | Football
 Pamela    | Calculus
 Pamela    | Singing
 Paige     | Calculus
 Peggy     | Fruit
 Pedro     | Programming
 Phoebe    | English
 Phoebe    | Gym
 Phoebe    | Cooking Pasta
 Pajak     | Yoga
 Pajak     | Calculus
 Parker    | Social Studies
 Parker    | English
 Parker    | Fruit
 Priyal    | Programming
 Paxton    | Gym
 Patrick   | Cooking Pasta
 Patrick   | Singing
 Patrick   | How to Guitar
(37 rows)
  • List all the students and their average grade
ariqueaguilar=# SELECT students.name as student, AVG(enrollments.grade) from students
LEFT JOIN enrollments
ON students.id = enrollments.student_id
GROUP BY student;
  student  |         avg
-----------+---------------------
 Penelope  | 60.0000000000000000
 Pedro     | 86.0000000000000000
 Piper     | 85.5000000000000000
 Patrick   | 90.6666666666666667
 Paula     | 94.0000000000000000
 Peter     | 85.0000000000000000
 Paige     | 55.0000000000000000
 Paxton    | 84.0000000000000000
 Pepe      | 74.0000000000000000
 Pamela    | 84.8000000000000000
 Pablo     |
 Parker    | 90.0000000000000000
 Patricia  | 84.0000000000000000
 Priscilla | 47.5000000000000000
 Priyal    | 94.0000000000000000
 Parth     | 82.0000000000000000
 Pajak     | 76.0000000000000000
 Puja      | 77.5000000000000000
 Peggy     | 97.0000000000000000
 Phoebe    | 81.0000000000000000
(20 rows)

# LOSE PABLO WHO'S NOT ENROLLED BY MAKING IT A RIGHT JOINED TABLE

ariqueaguilar=# SELECT students.name as student, AVG(enrollments.grade) from students
RIGHT JOIN enrollments
ON students.id = enrollments.student_id
GROUP BY student;
  student  |         avg
-----------+---------------------
 Penelope  | 60.0000000000000000
 Pedro     | 86.0000000000000000
 Piper     | 85.5000000000000000
 Patrick   | 90.6666666666666667
 Paula     | 94.0000000000000000
 Peter     | 85.0000000000000000
 Paige     | 55.0000000000000000
 Paxton    | 84.0000000000000000
 Pepe      | 74.0000000000000000
 Pamela    | 84.8000000000000000
 Parker    | 90.0000000000000000
 Patricia  | 84.0000000000000000
 Priscilla | 47.5000000000000000
 Priyal    | 94.0000000000000000
 Parth     | 82.0000000000000000
 Pajak     | 76.0000000000000000
 Puja      | 77.5000000000000000
 Peggy     | 97.0000000000000000
 Phoebe    | 81.0000000000000000
(19 rows)
  • List all the students and a count of how many classes they are currently enrolled in
ariqueaguilar=# SELECT students.name as student, COUNT(enrollments.grade) from students
RIGHT JOIN enrollments
ON students.id = enrollments.student_id
GROUP BY student;
  student  | count
-----------+-------
 Penelope  |     1
 Pedro     |     1
 Piper     |     2
 Patrick   |     3
 Paula     |     2
 Peter     |     2
 Paige     |     1
 Paxton    |     1
 Pepe      |     1
 Pamela    |     5
 Parker    |     3
 Patricia  |     1
 Priscilla |     2
 Priyal    |     1
 Parth     |     1
 Pajak     |     2
 Puja      |     4
 Peggy     |     1
 Phoebe    |     3
(19 rows)

#BUT IT FELT APPROPRIATE TO INCLUDE PABLO IN THIS COUNT, SO I MADE IT A LEFT JOIN INSTEAD

ariqueaguilar=# SELECT students.name as student, COUNT(enrollments.grade) from students
LEFT JOIN enrollments
ON students.id = enrollments.student_id
GROUP BY student;
  student  | count
-----------+-------
 Penelope  |     1
 Pedro     |     1
 Piper     |     2
 Patrick   |     3
 Paula     |     2
 Peter     |     2
 Paige     |     1
 Paxton    |     1
 Pepe      |     1
 Pamela    |     5
 Pablo     |     0
 Parker    |     3
 Patricia  |     1
 Priscilla |     2
 Priyal    |     1
 Parth     |     1
 Pajak     |     2
 Puja      |     4
 Peggy     |     1
 Phoebe    |     3
(20 rows)
  • List all the students and their class count IF they are in more than 2 classes
ariqueaguilar=# SELECT students.name, count(enrollments.grade)
FROM students
LEFT JOIN enrollments
ON students.id = enrollments.student_id
GROUP BY students.name
HAVING count(enrollments.grade) > 2;
  name   | count
---------+-------
 Patrick |     3
 Pamela  |     5
 Parker  |     3
 Puja    |     4
 Phoebe  |     3
(5 rows)
  • List all the teachers for each student
 SELECT students.name, teachers.name
 FROM students
 JOIN enrollments
 ON students.id = enrollments.student_id
 JOIN classes
 ON enrollments.class_id = classes.id
 JOIN teachers
 ON classes.teacher_id = teachers.id;
   name    |    name
-----------+-------------
 Pamela    | Phillips
 Penelope  | Phillips
 Patrick   | Phillips
 Phoebe    | Phillips
 Pajak     | Phillips
 Pamela    | Phillips
 Pepe      | Phillips
 Peter     | Phillips
 Patrick   | Vandergrift
 Parth     | Vandergrift
 Priscilla | Vandergrift
 Paxton    | Mauch
 Phoebe    | Mauch
 Peter     | Mauch
 Priscilla | Mauch
 Paula     | Patel
 Pamela    | Patel
 Pamela    | Marquez
 Paige     | Marquez
 Pajak     | Marquez
 Paula     | Boykin
 Peggy     | Boykin
 Piper     | Boykin
 Parker    | Boykin
 Patricia  | Phlop
 Piper     | Phlop
 Puja      | Phlop
 Parker    | Phlop
 Parker    | Pendergrass
 Phoebe    | Pendergrass
 Puja      | Pendergrass
 Puja      | Palomo
 Priyal    | Palomo
 Pedro     | Palomo
 Pamela    | Altshuler
 Patrick   | Altshuler
 Puja      | Altshuler
(37 rows)
  • List all the teachers for each student grouped by each student
ariqueaguilar=# SELECT students.name as student, teachers.name as teacher
FROM students
JOIN enrollments
ON students.id = enrollments.student_id
JOIN classes
ON enrollments.class_id = classes.id
JOIN teachers
ON classes.teacher_id = teachers.id
order by students.name;
  student  |   teacher
-----------+-------------
 Paige     | Marquez
 Pajak     | Phillips
 Pajak     | Marquez
 Pamela    | Phillips
 Pamela    | Phillips
 Pamela    | Patel
 Pamela    | Marquez
 Pamela    | Altshuler
 Parker    | Boykin
 Parker    | Pendergrass
 Parker    | Phlop
 Parth     | Vandergrift
 Patricia  | Phlop
 Patrick   | Vandergrift
 Patrick   | Altshuler
 Patrick   | Phillips
 Paula     | Patel
 Paula     | Boykin
 Paxton    | Mauch
 Pedro     | Palomo
 Peggy     | Boykin
 Penelope  | Phillips
 Pepe      | Phillips
 Peter     | Phillips
 Peter     | Mauch
 Phoebe    | Phillips
 Phoebe    | Mauch
 Phoebe    | Pendergrass
 Piper     | Phlop
 Piper     | Boykin
 Priscilla | Vandergrift
 Priscilla | Mauch
 Priyal    | Palomo
 Puja      | Pendergrass
 Puja      | Phlop
 Puja      | Altshuler
 Puja      | Palomo
(37 rows)
  • Find the average grade for a each class
  • List students' name and their grade IF their grade is lower than the average.

SQL Joins

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment