Honors 1000, Productive Data Manipulation in Python and SQL (Fall 2022)

Homework 3 (Due: 6 November 2022 - Sunday)

Consider the classroom database described in the following schema (please replace raj by your db name and save the following in a file called db.sql):

use raj

SET FOREIGN_KEY_CHECKS = 0;
drop table if exists BUILDING;
drop table if exists MEDIA;
drop table if exists ROOM;
drop table if exists ROOMMEDIA;
SET FOREIGN_KEY_CHECKS = 1;

create table BUILDING (
  bcode varchar(6),
  bname varchar(30),
  primary key (bcode)
);

create table MEDIA (
  mcode varchar(6),
  description varchar(50),
  primary key (mcode)
);

create table ROOM (
  bcode varchar(6),
  rnumber varchar(4),
  cap int,
  layout varchar(20),
  type enum ('P','G'),
  dept varchar(4),
  primary key (bcode,rnumber),
  foreign key (bcode) references BUILDING(bcode)
);

create table ROOMMEDIA (
  bcode varchar(6),
  rnumber varchar(4),
  mcode varchar(6),
  primary key (bcode,rnumber,mcode),
  foreign key (bcode,rnumber) references ROOM(bcode,rnumber),
  foreign key (mcode) references MEDIA(mcode)
);

source building.sql
source media.sql
source room.sql

The data for these tables is given in building.sql, media.sql, and room.sql. Please save these files also in the same folder as db.sql. Then, you can run the db.sql script inside MySQL and the tables will be created and data loaded into the tables.

Write SQL queries and execute them against the MySQL database for the classroom database for the following:

1. Get bcode and bname of buildings whose bname contains the word 'SOUTH'
2. Get bcode and rnumber for rooms with capacity greater than 100
3. Get cap, layout, type, and dept for room 'CLSO' '206'
4. Get mcode, description for media in room 'CLSO' '206'
5. Get rnumber of rooms in building with bcode='CLSO'
6. Get bcode and rnumber for rooms with media description containing term 'DVD'
7. Get bcode and rnumber for rooms that are 'P' type and owned by dept 'CSC'
8. Get number of rooms in 'CLSO' building
9. Get number of rooms for each building
10. Get number of rooms that have 'ELMO' media
11. Get number of rooms for each media type
12. Get total number of seats in all classrooms in 'CLSO'
13. Get total number of seats in each building
14. Get total number of seats in all classrooms