Use the SQL statements provided to create your tables. Write one SQL statement for each of...

Question

Question

Use the SQL statements provided to create your tables. Write one SQL statement for each of the following problems. You can only use the conditions listed in the tasks. E.g., in task 1, you cannot manually look up pid of Information Systems undergraduate program.

Here is the given code:

drop table textbook_schedule cascade constraints;

drop table textbook_author cascade constraints;

drop table schedule cascade constraints;

drop table course cascade constraints;

drop table textbook cascade constraints;

drop table author cascade constraints;

drop table teacher cascade constraints;

drop table program cascade constraints;

create table program

(pid int, --- program id

pname varchar(50), --- program name

ptype int, --- program type (1: undergraduate, 2: graduate)

primary key (pid));

insert into program values(1, 'Information Systems', 1);

insert into program values(2, 'Information Systems', 2);

insert into program values(3, 'Human Centered Computing', 2);

create table teacher

(tid int,

tname varchar(50),

primary key (tid)

);

insert into teacher values(1,'Dr. Chen');

insert into teacher values(2, 'Dr. Karabatis');

insert into teacher values(3,'Komlodi');

insert into teacher values(4, 'Kuber');

insert into teacher values(5, 'Dr. Janeja');

create table author

(

aid int,

aname varchar(50),

primary key (aid)

);

insert into author values

(1, 'Joel Murach');

insert into author values

(2, 'Dan Sullivan');

insert into author values

(3, 'Jeff Hoffer');

insert into author values

(4, 'Ramesh Venkataraman');

insert into author values

(5, 'Heikki Topi');

insert into author values

(6,'YVonne Rogers');

insert into author values

(7,'Helen Sharp');

insert into author values

(8,'Jenny Preece');

insert into author values

(9,'Ramez Elmasri');

insert into author values

(10,'Shamkant B. Navathe');

--- add more

create table textbook

(

tbid int, --- text book

title varchar(50), ---- title of book

publisher varchar(50), --- publisher name

edition int, --- edition, e.g., 3 means 3rd edition

quantity int, --- number of copies in store

price number, --- price of the book

primary key (tbid)

);

insert into textbook values

(1, 'Oracle SQL and PL/SQL', 'Murach Press',2,100,50);

insert into textbook values

(2, 'NoSQL for Mere Mortals', 'Addison-Wesley',1,100,40);

insert into textbook values

(3, 'Modern Database Management','Pearson',12,100,225);

insert into textbook values

(4, 'Interaction Design','Wiley',3,30,45);

insert into textbook values

(5, 'Fundamentals of Database Systems', 'Pearson',7,30,153);

create table course

(

cid int, --- course id

cname varchar(50), --- course name

pid int, --- program id

primary key (cid),

foreign key(pid) references program

);

insert into course values(1, 'IS 420: Database Application Development',1);

insert into course values(2, 'IS 410: Introduction to Database Design',1);

insert into course values(3, 'IS 620: Advanced Database Project',2);

insert into course values(4, 'IS 722: Database Integration',2);

insert into course values(5, 'HCC 629: Fundamentals of Human-Centered Computing',3);

insert into course values(6, 'HCC 729: Human-Centered Design',3);

--- insert some HCC courses

create table schedule

(sid int, --- schedule id

cid int, --- course id

snumber int, --- section number

semester varchar(10), --- spring, fall, summer, winter,

year int, --- year of the class

tid int, --- teach id

num_registered int, --- number of registered students for this class section

primary key (sid),

foreign key (cid) references course,

foreign key(tid) references teacher

);

-- IS 420 scheduled fall 18 by Dr. Karabatis, 2 sections

insert into schedule values

(1,1,1, 'fall',2018,2,30);

insert into schedule values

(2,1,2, 'fall',2018,2,30);

--spring 19 2 sections by Dr. Chen

insert into schedule values

(3,1,1, 'spring',2019,1,30);

insert into schedule values

(4,1,2, 'spring',2019,1,30);

--- IS 620 scheduled for each semester one section

insert into schedule values

(5,3,1, 'fall',2018,1,40);

insert into schedule values

(6,3,1, 'spring',2019,2,40);

-- IS 410 scheduled for each semester

insert into schedule values

(7,2,1,'spring',2019,5,30);

insert into schedule values

(8,2,2,'spring',2019,5,30);

-- HCC 629 scheduled each semester

insert into schedule values

(9,5,1,'fall',2018,4,30);

insert into schedule values

(10,5,1,'spring',2019,3,30);

-- HCC 729 scheduled each year

insert into schedule values

(11,6,1,'fall',2018,4,20);

create table textbook_author

(

aid int, --- author id

tbid int, --- text book id

primary key(aid, tbid),

foreign key(aid) references author,

foreign key(tbid) references textbook

);