Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Intermediate SQL

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
  • List all the students and their classes and rename the columns to "student" and "class"
  • List all the students and their average grade
  • List all the students and a count of how many classes they are currently enrolled in
  • List all the students and their class count IF they are in more than 2 classes
  • List all the teachers for each student
  • List all the teachers for each student grouped by each student
  • Find the average grade for a each class
  • List students' name and their grade IF their grade is lower than the average.
@samlandfried
Copy link

samlandfried commented Apr 26, 2017

On line 136 we're instructed to rename a column to 'class', but we can't since 'class' is a reserved word in SQL. Maybe change it to 'class_name'?

@TravisGM92
Copy link

TravisGM92 commented Sep 29, 2020

On line 136 we're instructed to rename a column to 'class', but we can't since 'class' is a reserved word in SQL. Maybe change it to 'class_name'?

I'm guessing you're still waiting for an answer on this? ;) jk

@priyapower
Copy link

priyapower commented Oct 1, 2020

@TravisGM92 - Mine worked with changing the name to just 'class' - I didn't get errors or anything flagged that it was a reserved word.

Unsure what the comment above you is about: is it saying they got an error? Is it saying this isn't best practice/convention?

@TravisGM92
Copy link

TravisGM92 commented Oct 1, 2020

@TravisGM92 - Mine worked with changing the name to just 'class' - I didn't get errors or anything flagged that it was a reserved word.

Unsure what the comment above you is about: is it saying they got an error? Is it saying this isn't best practice/convention?

@priyapower, yeah sometimes SQL won't let me use a particular word to rename a column to cause it'll confuse it with something else. I'm imagining they had that issue, calling a column 'class' would confuse SQL so it prompts you to use a different word. I've had it happen, although it didn't happen with me this time.

And I think they're saying they got an error...

@leehopper
Copy link

leehopper commented Aug 20, 2021

I believe the varying issue depends on whether you are using the shorthand. If you specify SELECT c.name AS class it runs without an error, but it does error if you try to use the shorthand SELECT c.name class.

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