# Test SQL (MySQL) data for connecting tables together

# Remove tables from any previous demonstrations
drop table if exists dm_subjects;
drop table if exists dm_courses;

# Create a table for (potential) course subjects
create table dm_subjects (
        su_id int primary key not null,
        su_name text,
        su_author text);

# Create a table of courses
create table dm_courses (
        co_id int primary key not null,
        co_su int,
        co_title text,
        co_level int,
        co_key char(2));

# Add in the potential subjects. Note that there are some
# subjects here where we do NOT offer courses - we'll find
# these during the demonstration

insert into dm_subjects values
        (1,"Perl","Larry Wall"),
        (2,"PHP","Rasmus Lerdorf"),
        (3,"Python","Guido Van Rossum"),
        (4,"Ruby","Yukihiro \"Matz\" Matsumoto"),
        (5,"Lua","Roberto Ierusalimschy"),
        (7,"Scala","Martin Odersky"),
        (8,"Tcl","John Ousterhout"),
        (10,"Go!","Robert Griesemer, Ken Thompson, Rob Pike"),
        (11,"Java","");

# Add in the courses. Most of these refer to subjects which are
# in the list above, but there are some which relate to subjects
# that are missing from our lists. Again, with a careful use of
# SQL we can identify these

insert into dm_courses values
        (101,1,"Learning to program in Perl",1,"lp"),
        (102,1,"Perl for Larger Projects",3,"pl"),
        (103,1,"Using Perl on the Web",3,"pw"),
        (104,2,"Learning to Program in PHP",1,"pf"),
        (105,1,"Perl Programming",2,"pp"),
        (106,2,"PHP Programming",2,"ph"),
        (107,2,"OO Programming in PHP",3,"po"),
        (108,2,"PHP Techniques Workshop",3,"pt"),
        (109,3,"Learning to Program in Python",1,"yl"),
        (121,3,"Python Programming",2,"yp"),
        (110,4,"Learning to program in Ruby",1,"rl"),
        (111,4,"Ruby Programming",2,"rp"),
        (112,4,"Rails for Ruby",3,"rr"),
        (114,6,"MySQL",1,"mq"),
        (115,5,"Learning to program in Lua",1,"ul"),
        (116,5,"Lua Programming",2,"ub"),
        (117,8,"Learning to program in Tcl",1,"tl"),
        (119,8,"Tcl Programming",2,"tb"),
        (120,8,"The Tk Toolkit",3,"tk"),
        (123,9,"Linux Basics",1,"lb"),
        (124,9,"Introduction to Linux Administration",2,"la"),
        (125,9,"Linux Web Server",3,"lw"),
        (126,11,"Learning to Program in Java",1,"sj"),
        (127,11,"Java Bootcamp",2,"bj"),
        (128,11,"Apache httpd and Tomcat Deployment",3,"at");

# All records that are matched up across the tables

select * from dm_subjects join dm_courses on su_id = co_su;

# All records that are matched up across the tables PLUS
# those which appear only in the left hand table

select * from dm_subjects left join dm_courses on su_id = co_su;

# All records that are matched up across the tables PLUS
# those which appear only in the right hand table

select * from dm_subjects right join dm_courses on su_id = co_su;

# Only those records which appear in the left hand table

select * from dm_subjects left join dm_courses on su_id = co_su where co_su is NULL;

# Only those records which appear in the right hand table

select * from dm_subjects right join dm_courses on su_id = co_su where su_id is NULL;

# All records that are matched up across the tables PLUS
# those which appear only in the left hand table PLUS
# those records which appear only in the right hand table

(select * from dm_subjects left join dm_courses on su_id = co_su)
union
(select * from dm_subjects right join dm_courses on su_id = co_su where su_id is NULL);

(select * from dm_subjects left join dm_courses on su_id = co_su)
union
(select * from dm_subjects right join dm_courses on su_id = co_su where su_id is NULL)
order by su_name desc, co_key;