#nappy-pronghorn Somaya Bounouar Goal : http://jsdev.learnersguild.org/goals/178-Core_SQL.html
Exercises to help exercise the SQL muscles.
Base repository for the core-sql goal.
Create a Postgres database called sql-exercises
. Use this database for all of the exercises.
Create the schema for the following tables.
01: Table name: student
, columns: id
, name
, grade
id
is the primary keyname
is only their first namegrade
is an integer- write the SQL in the file
sql/01-create-table-student.sql
02: Table name: friend
, columns: id1
, id2
id1
is a foreign key reference to a studentid2
is a foreign key reference to a student- write the SQL in the file
sql/02-create-table-friend.sql
03: Table name: student_like
, columns: liker_id
, likee_id
liker_id
is a foreign key reference to a studentlikee_id
is a foreign key reference to a student- write the SQL in the file
sql/03-create-table-like.sql
Run the SQL for the schema against the sql-exercises
database to create the tables.
Load the data into the tables (make sure you have created the tables above).
04: SQL query to load the CSV file data/students.csv
for the student
table
Write the SQL in the file sql/04-load-table-student.sql
05: SQL query to load the CSV file data/friends.csv
for the friend
table
Write the query in the file sql/05-load-table-friend.sql
06: SQL query to load the CSV file data/likes.csv
for the student_like
table
Write the SQL in the file sql/06-load-table-like.sql
Run the queries to load the data into the tables.
Make sure each question is copied and pasted in the file which contains the SQL, so that it is easy for the reviewer to know what the SQL is supposed to do.
07: Find the names of all students who are friends with someone named Gabriel.
Write the SQL in the file sql/07-query-friends-gabriel.sql
08: For every student who likes someone 2 or more grades younger than themselves, return that student's name and grade, and the name and grade of the student they like.
Write the SQL in the file sql/08-query-likes-grade-two-or-more.sql
09: For every pair of students who both like each other, return the name and grade of both students. Include each pair only once, with the two names in alphabetical order.
Write the SQL in the file sql/09-mutual-likes.sql
10: Find all students who do not appear in the like table (as a student who likes or is liked) and return their names and grades. Sort by grade, then by name within each grade.
Write the SQL in the file sql/10-not-liked.sql
11: For every situation where student A likes student B, but we have no information about whom B likes (that is, B's id does not appear in the liker_id
column of the like table), return A and B's names and grades.
Write the SQL in the file sql/11-liked-but-does-not-like.sql
12: For each student A who likes a student B where the two are not friends, find if they have a friend C in common (who can introduce them!). For all such trios, return the name and grade of A, B, and C.
Write the SQL in the file sql/12-find-friends-in-common.sql
13: Find the name and grade of all students who are liked by more than one other student.
Write the SQL in the file sql/13-popular-students.sql