## Practical Session 2 Answers

### Exercise 1

```sql

--a. 

SELECT DNAME, COUNT (*)
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER=DNO
GROUP BY DNAME
HAVING AVG (SALARY) > 30000


--     dname      | count 
-- ----------------+-------
-- Administration |     3
-- Headquarters   |     1
-- Hardware       |    10
-- Sales          |    14
-- Research       |     4
-- Software       |     8
-- (6 rows)

--b.

SELECT fname, lname 
FROM employee
WHERE lname like 'S%';

--c.

SELECT DISTINCT fname, lname
FROM employee, works_on, project
WHERE employee.ssn = works_on.essn AND
    works_on.pno = project.pnumber AND
    project.pname LIKE '%Product%';

```

### Exercise 2

```sql
--a.
CREATE VIEW ex_2a AS
SELECT dname, fname, lname, SALARY
FROM employee, department
WHERE department.mgr_ssn = employee.ssn;

--b.

CREATE VIEW ex_2b AS
SELECT e1.fname AS e_fname, e1.lname AS e_lname, sup.fname, sup.lname, e1.salary 
FROM employee AS e1, employee AS sup, department as dept  
WHERE e1.superssn = sup.ssn AND
    e1.dno = dept.dnumber AND
    dept.dname = 'Research';   
    
--c. 
CREATE VIEW ex_2c AS
SELECT project.pname, COUNT(employee.ssn), sum(works_on.hours), department.dname
FROM project, department, employee, works_on
WHERE works_on.essn = employee.ssn AND
    works_on.pno = project.pnumber AND
    project.dnum = department.dnumber
GROUP BY department.dname, project.pname;

```

### Exercise 3
```sql

CREATE TABLE student (
 student_number int PRIMARY KEY,
 name varchar(20),
 class int,
 major varchAR(10)
);

CREATE TABLE course (
 course_number VARCHAR(10) PRIMARY KEY,
 course_name VARCHAR(30),
 credit_hours int,
 department VARCHAR(10)
);

CREATE TABLE section (
 section_identifier int PRIMARY KEY,
 course_number VARCHAR(10),
 semester VARCHAR(10),
 year VARCHAR(2),
 instructor VARCHAR(20),
 FOREIGN KEY (course_number) REFERENCES course (course_number)
 ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE grade_report (
 student_number int,
 section_identifier int,
 grade char,
 FOREIGN KEY (student_number) REFERENCES student (student_number)
 ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY (section_identifier) REFERENCES section (section_identifier)
 ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE prerequisite (
 course_number VARCHAR(10),
 prerequisite_number VARCHAR(10),
 FOREIGN KEY (course_number) REFERENCES course (course_number)
 ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY (prerequisite_number) REFERENCES course (course_number)
 ON DELETE CASCADE ON UPDATE CASCADE
);


INSERT INTO student VALUES(17,'Smith',1,'CS');
INSERT INTO student VALUES(8,'Brown',2,'CS');

INSERT INTO course VALUES('CS1310','Intro to Computer Science',  4, 'CS');
INSERT INTO course VALUES('CS3320','Data Structures',  4, 'CS');
INSERT INTO course VALUES('MATH2410','Discrete Mathematics',  3, 'MATH');
INSERT INTO course VALUES('CS3380','Database',  3, 'CS');

INSERT INTO section VALUES(85,'MATH2410', 'Fall', '07', 'King');
INSERT INTO section VALUES(92,'CS1310', 'Fall', '07', 'Anderson');
INSERT INTO section VALUES(102,'CS3320', 'Spring', '08', 'Knuth');
INSERT INTO section VALUES(112,'MATH2410', 'Fall', '08', 'Chang');
INSERT INTO section VALUES(119,'CS1310', 'Fall', '08', 'Anderson');
INSERT INTO section VALUES(135,'CS3380', 'Fall', '08', 'Stone');


INSERT INTO grade_report VALUES(17,112, 'B');
INSERT INTO grade_report VALUES(17,119, 'C');
INSERT INTO grade_report VALUES(8,85, 'A');
INSERT INTO grade_report VALUES(8,92, 'A');
INSERT INTO grade_report VALUES(8,102, 'B');
INSERT INTO grade_report VALUES(8,135, 'A');

INSERT INTO prerequisite VALUES('CS3380', 'CS3320');
INSERT INTO prerequisite VALUES('CS3380', 'MATH2410');
INSERT INTO prerequisite VALUES('CS3320', 'CS1310');

```