#
# Create the schedule database
#
create database if not exists scheduleDb;
use scheduleDb;
# If at SIMS: Comment the above two lines and uncomment next line.
#use bhayes;
#
# Create the profs and courses tables
drop table if exists profs;
create table profs (prof_ID INT,lastName VARCHAR(15),firstName VARCHAR(15),title VARCHAR(30),office VARCHAR(30),email VARCHAR(30));
#
describe profs;
#
drop table if exists courses;
create table courses(class_ID INT,courseNum INT, courseName VARCHAR(80), days VARCHAR(5),time VARCHAR(20),semester VARCHAR(15), prof1 INT, prof2 INT);
#
# Load the profs table
#
insert into profs values (-1,'None','','','','');
insert into profs values (1,'Braunstein','Yale','Professor','203B South Hall','yale@sims.berkeley.edu');
insert into profs values (2,'Buckland','Michael','Professor','203A South Hall','buckland@sims.berkeley.edu');
insert into profs values (3,'Chuang','John','Assistant Professor','312 South Hall', 'chuang@sims.berkeley.edu');
insert into profs values (4,'Davis','Marc','Assistant Professor','314 South Hall','marc@sims.berkeley.edu');
insert into profs values (5,'Hearst','Marti','Associate Professor','212 South Hall','hearst@sims.berkeley.edu');
insert into profs values (6,'Larson','Ray  ','Associate Professor','207B South Hall','ray@sims.berkeley.edu');
insert into profs values (7,'Lyman','Peter','Associate Dean and Professor','303A South Hall','plyman@sims.berkeley.edu');
insert into profs values (8,'Samuelson','Pamela','Professor','305A South Hall','pam@sims.berkeley.edu');
insert into profs values (9,'Tygar','Doug','Professor','307B South Hall','tygar@cs.berkeley.edu');
insert into profs values (10,'Van House','Nancy','Professor','307A South Hall','vanhouse@sims.berkeley.edu');
#
# Load the courses table
#
insert into courses values (1,202,'Information Organization and Retrieval','TTh','10:30-12','fall',4,6);
insert into courses values (2,204,'Information Users and Society','TTh','12:30-2','fall',8,10);
insert into courses values (3,230,'Economic Methods for Decision Making','W','2-5','fall',1,-1);
insert into courses values (4,250,'Computer-Based Communications Systems and Networks','MW','11-12:30','fall',3,-1);
insert into courses values (5,255,'Foundations of Software Design','TTh','9-10:30','fall',5,-1);
insert into courses values (6,290,'Section 2: Classification and Bibliographical Representation','TTh','12:30-2','fall',2,-1);
insert into courses values (7,206,'Distributed Computing Applications and Infrastructure','TTh','10:30-12','spring',3,9);
insert into courses values (8,208,'Analysis of Information Organizations and Systems','MW','10:30-12','spring',1,7);
insert into courses values (9,212,'Information in Society','M','3:30-6','spring',10,-1);
insert into courses values (10,213,'User Interface Design and Development','TTh','9-10:30','spring',5,-1);
insert into courses values (11,214,'Needs and Usability Assessment','TTh','12:30-2','spring',10,-1);
insert into courses values (12,245,'Organization of Information in Collections','MW','9-10:30','spring',2,-1);
insert into courses values (13,246,'Multimedia Information','MW','2-3:30','spring',4,-1);
#
# Display profs table 
#
select * from profs;
#
# Display only the profs that are Associate Professors
#
select * from profs where title='Associate Professor';
#
# Count the number of Associate Professors in the profs table
#
select count(*) from profs where title='Associate Professor';
#
# View the courses sorted by course number
#
select courseNum, courseName from courses order by courseNum;
