1. Create a new database called lab01

2. In database lab01, create 4 tables: course, advisor, student, and department

course (course_id varchar(8), title varchar(50), dept_name varchar(20), credits numeric(2,0), primary key (course_id),
       foreign key (dept_name) references department (dept_name))

advisor (s_ID varchar(5), i_ID varchar(5), primary key (s_ID), foreign key (s_ID) references student (ID))

student (ID varchar(5), name varchar(20) not null, dept_name varchar(20), tot_cred numeric(3,0), primary key (ID),
        foreign key (dept_name) references department (dept_name))

department (dept_name varchar(20), building varchar(15), budget numeric(12,2), primary key (dept_name))

3. Populate tables

Populate the table "student" with the data below:

('23121', 'Chavez', 'Finance', '110');
('44553', 'Peltier', 'Physics', '56');
('45678', 'Levy', 'Physics', '46');

Populate the table "course" with the data below:

('FIN-201', 'Investment Banking', 'Finance', '3');
('HIS-351', 'World History', 'History', '3');
('MU-199', 'Music Video Production', 'Music', '3');

Populate the table "department" with the data below:

('Finance', 'Painter', '120000');
('History', 'Painter', '50000');
('Music', 'Packard', '80000');
('Physics', 'Watson', '70000');

 

4. Please run the sql statements below:

  select * from student where dept_name = 'physics'

  select * from course where dept_name = 'Finance'