Sqool

Which column is the departments table is intended to be uniquely identifying data?
> id

Which column is being used as a foreign key? Why might we be using a foreign key here rather than storing the data directly in this table? 
> Department. It makes more sense to store on a separate table since that multiple tables may need that data

Look at the remaining table schemas in this database. Whiteboard each of the 5 tables, representing them as simple spreadsheet like grids, using arrows to indicate where a particular column is referring to data stored in another table.



sqlite> select name from students;                              
name                                   
----------                             
lauren                                 
dan                                    
naomi                                  
kim                                    
sam                                    
chris                                  


sqlite> select * from teachers;
id          name        department
----------  ----------  ----------
1           fred        1
2           pamela      2
3           beth        1
4           sunny       2


sqlite> select * from departments;
id          name
----------  ----------
1           cs
2           psy


sqlite> select name from students where name != "naomi";
name
----------
lauren
dan
kim
sam
chris

sqlite> select name, department from teachers where department > 2 OR name = "fred";
name        department
----------  ----------
fred        1



sqlite> select id, name from students where name like "%m";
id          name
----------  ----------
4           kim
5           sam


sqlite> select * from students where name NOT LIKE "%s%";
id          name
----------  ----------
1           lauren
2           dan
3           naomi
4           kim


sqlite> select name from teachers where id not in (1, 2, 4);
name
----------
beth


sqlite> select name from teachers where department in (1, 4);
name
----------
fred
beth


sqlite> select name from teachers where department in (select id from departments where name = 'cs');
name
----------
fred
beth


sqlite> select id, name from teachers where department in (select id from departments where name = 'psy');
id          name
----------  ----------
2           pamela
4           sunny


sqlite> select name from departments where id in (select department from teachers where name = 'sunny');
name
----------
psy


sqlite> select * from departments, classes
   ...> ;
id          name        id          name        department  teacher
----------  ----------  ----------  ----------  ----------  ----------
1           cs          1           javascript  1           1
1           cs          2           communicat  2           2
1           cs          3           node        1           3
1           cs          4           compromise  2           4
2           psy         1           javascript  1           1
2           psy         2           communicat  2           2
2           psy         3           node        1           3
2           psy         4           compromise  2           4


sqlite> select departments.id, classes.id from departments, classes;
id          id
----------  ----------
1           1
1           2
1           3
1           4
2           1
2           2
2           3
2           4


sqlite> select students.*, teachers.name from students, teachers;
id          name        name
----------  ----------  ----------
1           lauren      fred
1           lauren      pamela
1           lauren      beth
1           lauren      sunny
2           dan         fred
2           dan         pamela
2           dan         beth
2           dan         sunny
3           naomi       fred
3           naomi       pamela
3           naomi       beth
3           naomi       sunny
4           kim         fred
4           kim         pamela
4           kim         beth
4           kim         sunny
5           sam         fred
5           sam         pamela
5           sam         beth
5           sam         sunny
6           chris       fred
6           chris       pamela
6           chris       beth
6           chris       sunny



sqlite> select * from teachers, departments where departments.name = "cs" and teachers.department = departments.id;
id          name        department  id          name
----------  ----------  ----------  ----------  ----------
1           fred        1           1           cs
3           beth        1           1           cs



sqlite> SELECT teachers.name FROM teachers, departments   WHERE departments.name = "cs" AND teachers.department = departments.id;
name
----------
fred
beth


sqlite> select teachers.id, teachers.name from teachers, departments where departments.name = "psy" AND teachers.department = departments.id;
id          name
----------  ----------
2           pamela
4           sunny


sqlite> select departments.name from teachers, departments where teachers.name = 'sunny' AND teachers.department = departments.id;
name
----------
psy


sqlite> select teachers.name, teachers.id from teachers inner join departments on departments.name = 'psy' AND teachers.department = departments.id;
name        id
----------  ----------
pamela      2
sunny       4


sqlite> select departments.name from departments inner join teachers on teachers.name = 'sunny' AND teachers.department = departments.id;
name
----------
psy


sqlite> select students.name, classes.name from students inner join classes, classes_students on students.name = "sam" AND classes_students.student_id = students.id AND classes_students.classes_id = classes.id;
name        name
----------  -------------
sam         communication



sqlite> select students.name from students inner join classes, classes_students on students.id = classes_students.student_id AND classes.name = 'compromise' AND classes_students.classes_id = classes.id;
name
----------
naomi
chris
kim


sqlite> select students.name from students inner join classes, classes_students, departments on departments.name = 'cs' and classes.id = classes_students.classes_id and students.id = classes_students.student_id and classes.department = departments.id;
name
----------
lauren
dan
dan
naomi
kim
chris


sqlite> select distinct students.name from students inner join classes, classes_students, departments on departments.name = 'cs' and classes.id = classes_students.classes_id and students.id = classes_students.student_id and classes.department = departments.id;
name
----------
lauren
dan
naomi
kim
chris