Write an SQL statement to create a new table program with th

Write an SQL statement to create a new table \'program\' with the information below: You need to decide the appropriate data type and length. Degree Type must be (BSc, BA, or BCom). Write an SQL command to modify the table \'student\' to add one more column \"programe_code\" as below: Draw an ER diagram using Crow\'s Foot notation that captures the given information. Primary keys and foreign keys must be indicated in the diagram. Write an SQL to show all staff (staff name, hour_salary) whose hour_salary is higher than the staff May. Write an SQL to show all students (student id, module_code) who had not taken exam(s), that is, the exam_scores is NULL. Write an SQL to show all students (student id, name, module_code, module_name, credits) who take module(s) with a total credits over 30. Write an SQL to show all students (student id, name, module_code, teach name) take module taught by the staff May. Write an SQL to show all students (student id, module_code, average assign_scores, average exam_scores) whose overall average assign_scores is higher than overall average exam_scores. Write an SQL to show all staff (staff id, name, extra_pay) whose extra_pay is higher than 900.

Solution

(a)CREATE TABLE program
(
program_code varchar2(255),
program_name varchar2(255),
degree_type varchar2(255)
);
INSERT INTO program (program_code, program_name,degree_type)
VALUES (\'P001\', \'Business Information\',\'BSc\');

INSERT INTO program (program_code, program_name,degree_type)
VALUES (\'P002\', \'Comparative Literature\',\'BA\');

INSERT INTO program (program_code, program_name,degree_type)
VALUES (\'P003\', \'Electronic Commerce\',\'BSc\');
INSERT INTO program (program_code, program_name,degree_type)
VALUES (\'P004\', \'Finance\',\'BCom\');

(b)ALTER TABLE student
ADD program_code varchar2(45);
(d)write an sql to show all staff(staff_name,hour_salary) whose hour_salary is higher than the staff May

select staffname,hour_salary from staff where hour_salary>(select hour_salary from staff where staffname=\'May\');

(e)select studentid,module_code from students where exam_score=NULL;
(f)select studentid,module_code,name,module_name,credits from students where credits>30;
(g)select studentid,module_code,name,teachname from students where teachname=\'May\';
(h)select studentid,module_code,averageassign_scores,averageexam_scores from students where averageassign_score>AVG(exam_scores);
(i)select staffid,name,hour_salary * extra_hour extra_pay from staff where extra_pay>9000;