CS 431 homework 6

DUE: Monday Mar 13 at 5pm. Submit to Desire2Learn Dropbox.

Super Simple Database: Enrollment Analytics II Solution

Materials ==> You may start fresh (hw05-enrollment-db.zip) or build on your completed work from hw 5 (recommended).

Description

A. Reuse the enrollment analytics db you built in HW5.

B. Develop queries that meet the following criteria.

Show which semesters CS 345 was taught. Show semester title, course level, and course name. Order the query by semester.
Show which semesters any course name containing “machine learning” was taught. Show semester title, course level, and course name. Order the query by semester.
Show which semesters any course name containing “data struct” was taught. Show semester title, course level, and course name. Order the query by semester.

These queries will require the use of inner join. Why do you need join?

What to Turn In:

You will be turning your sqlite db file, an sql file containing your create table statements, and an sql file containing your two queries.

Solution

-- hw 6 queries cs4/531

-- Show which semesters CS 345 was taught. Show semester title, course level, and course name. Order the query by semester.

select semesters.title, courses.level, courses.title
from schedules inner join courses inner join semesters
on schedules.course_pk = courses.pk and schedules.semester_pk = semesters.pk 
where courses.level = 345 
order by semesters.code;

-- results should return 8 rows. E.g.,

-- 'Spring Semester 2006',345,'Data & File Structures'
-- 'Spring Semester 2006',345,'Data & File Structures'
-- 'Spring Semester 2008',345,'Data & File Structures'
-- 'Spring Semester 2008',345,'Data & File Structures'
-- 'Fall Semester 2013',345,'Data & File Structures'
-- 'Fall Semester 2013',345,'Data & File Structures'
-- 'Fall Semester 2014',345,'Data & File Structures'
-- 'Fall Semester 2014',345,'Data & File Structures'


-- Show which semesters any course name containing “machine learning” was taught. Show semester title, course level, and course name. Order the query by semester.

select semesters.title, courses.level, courses.title
from schedules inner join courses inner join semesters
on schedules.course_pk = courses.pk and schedules.semester_pk = semesters.pk
where courses.title like "%machine learning%"
order by semesters.code;

-- results should return 9 rows. E.g.,

-- 'Fall Semester 2006',474,'Machine Learning Algorithms'
-- 'Fall Semester 2006',574,'Machine Learning Algorithms'
-- 'Spring Semester 2009',474,'Machine Learning Algorithms'
-- 'Spring Semester 2009',574,'Machine Learning Algorithms'
-- 'Fall Semester 2010',474,'Machine Learning Algorithms'
-- 'Fall Semester 2010',574,'Machine Learning Algorithms'
-- 'Spring Semester 2014',474,'Machine Learning Algorithms'
-- 'Spring Semester 2014',474,'Machine Learning Algorithms'
-- 'Spring Semester 2014',574,'Machine Learning Algorithms'

-- OR ... 4 rows if you grouped by semester which may not be as accurate because of wild cards.

-- 'Fall Semester 2006',574,'Machine Learning Algorithms'
-- 'Spring Semester 2009',574,'Machine Learning Algorithms'
-- 'Fall Semester 2010',574,'Machine Learning Algorithms'
-- 'Spring Semester 2014',574,'Machine Learning Algorithms'


-- Show which semesters any course name containing “data struct” was taught. Show semester title, course level, and course name. Order the query by semester.

select semesters.title, courses.level, courses.title
from schedules inner join courses inner join semesters
on schedules.course_pk = courses.pk and schedules.semester_pk = semesters.pk
where courses.title like "%data struct%"
group by semesters.code
order by semesters.code;

-- results should return 43 rows. E.g.,

-- 'Fall Semester 2005',516,'Adv Comp Prg Data Structure'
-- 'Fall Semester 2005',516,'Adv Comp Prg Data Structure'
-- 'Spring Semester 2006',421,'Adv Data Struct & Alg Dev'
-- 'Spring Semester 2006',421,'Adv Data Struct & Alg Dev'
-- 'Spring Semester 2006',516,'Adv Comp Prg Data Structure'
-- 'Spring Semester 2006',516,'Adv Comp Prg Data Structure'
-- ...

-- OR ... 18 rows if you grouped by semester which may not be as accurate because of wild cards

-- 'Fall Semester 2005',516,'Adv Comp Prg Data Structure'
-- 'Spring Semester 2006',521,'Adv Data Struct & Algorith Dev'
-- 'Summer Semester 2006',516,'Adv Comp Prg Data Structure'
-- 'Fall Semester 2006',516,'Adv Comp Prg Data Structure'
-- 'Spring Semester 2007',516,'Adv Comp Prg Data Structure'
-- 'Spring Semester 2008',521,'Adv Data Struct & Algorith Dev'
-- 'Fall Semester 2008',516,'Adv Comp Prg Data Structure'
-- 'Spring Semester 2009',521,'Adv Data Struct & Algorith Dev'
-- 'Fall Semester 2009',516,'Adv Comp Prg Data Structure'
-- 'Spring Semester 2010',521,'Adv Data Struct & Algorith Dev'
-- 'Fall Semester 2010',516,'Adv Comp Prg Data Structure'
-- 'Spring Semester 2011',516,'Adv Comp Prg Data Structure'
-- 'Fall Semester 2011',516,'Adv Comp Prg Data Structure'
-- 'Spring Semester 2012',516,'Adv Comp Prg Data Structure'
-- 'Fall Semester 2012',516,'Adv Comp Prg Data Structure'
-- 'Spring Semester 2013',516,'Adv Comp Prg Data Structure'
-- 'Spring Semester 2014',516,'Adv Comp Prg Data Structure'
-- 'Fall Semester 2014',516,'Adv Comp Prg Data Structure'
--