資料庫作業三

• 姓名：張議隆
• 學號：F74082125

TOC

• 資料庫作業三
  • 5.6
  • 5.7
  • 5.8
  • 5.9

5.6

a. Retrieve the names and major departments of all straight-A students (students who have a grade of A in all their courses).

SELECT S.Student_number, S.Major
FROM STUDENT AS S, GRADE_REPORT AS G
WHERE S.Student_number=G.Student_number
GROUP BY G.Student_number
HAVING COUNT(G.Grade)=1 AND G.Grade='A'

---

b. Retrieve the names and major departments of all students who do not have a grade of A in any of their courses.

SELECT S.Student_number, S.Major
FROM STUDENT AS S, GRADE_REPORT AS G
WHERE S.Student_number=G.Student_number 
AND NOT EXISTS(
    SELECT Grade
    FROM GRADE_REPORT
    WHERE Grade='A')

---

5.7

a. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees.

SELECT Fname, Lname
FROM EMPLOYEE
WHERE Dno IN(
    SELECT Dno
    FROM EMPLOYEE
    WHERE MAX(Salary))

---

b. Retrieve the names of all employees whose supervisor’s supervisor has ‘888665555’ for Ssn.

SELECT Fname, Lname
FROM EMPLOYEE
WHERE Super_ssn IN(
    SELECT Ssn
    FROM EMPLOYEE
    WHERE Super_ssn=Ssn AND Super_ssn=888665555)

---

c. Retrieve the names of employees who make at least $10,000 more than the employee who is paid the least in the company.

SELECT Fname, Lname
FROM EMPLOYEE
WHERE Salary - MIN(Salary) > 10000

---

5.8

a. A view that has the department name, manager name, and manager salary for every department.

CREATE VIEW DEPT_INFO(Dept_name, Manager_first_name, Manager_last_name, Manager_salary)
AS
SELECT D.Dname, E.Fname, E.Lname, E.Salary
FROM DEPARTMENT AS D, EMPLOYEE AS E
WHERE D.Dnumber=E.Dno AND D.Mgr_ssn=E.Ssn

---

b. A view that has the employee name, supervisor name, and employee salary for each employee who works in the ‘Research’ department.

CREATE VIEW REASEARCH_INFO(Employee_first_name, Employee_last_name, Employee_salary, Manager_first_name, Manager_last_name)
AS
SELECT E.Fname, E.Lname, E.Salary
FROM DEPARTMENT AS D, EMPLOYEE AS E
WHERE D.Dnumber=E.Dno AND D.Dname='Research'
UNION
SELECT E.Fname, E.Lname
FROM DEPARTMENT AS D, EMPLOYEE AS E
WHERE D.Dnumber=E.Dno AND D.Mgr_ssn=E.Ssn

---

c. A view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project.

CREATE VIEW PROJECT_INFO(Project_name, Dept_name, Number_of_employees, Hours_per_week)
AS
SELECT P.Pname, D.Dname, COUNT(E.*), SUM(W.Hours)
FROM PROJECT AS P, DEPARTMENT AS D, EMPLOYEE AS E, WORKS_ON AS W
WHERE P.Dnum=D.Dnumber AND D.Dnumber=E.Dno AND W.Pno=P.Pnumber
GROUP BY P.Pname

---

d. A view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project with more than one employee working on it.

CREATE VIEW PROJECT_INFO_2(Project_name, Dept_name, Number_of_employees, Hours_per_week)
AS
SELECT P.Pname, D.Dname, COUNT(E.*), SUM(W.Hours)
FROM PROJECT AS P, DEPARTMENT AS D, EMPLOYEE AS E, WORKS_ON AS W
WHERE P.Dnum=D.Dnumber AND D.Dnumber=E.Dno AND W.Pno=P.Pnumber
GROUP BY P.Pname
HAVING COUNT(E.*)>1

---

5.9

CREATE VIEW DEPT_SUMMARY (D, C, Total_s, Average_s)
AS SELECT Dno, COUNT(*), SUM(Salary), AVG(Salary)
FROM EMPLOYEE
GROUP BY Dno; 

---

a.

a. SELECT *
FROM DEPT_SUMMARY; 

接受，輸出為

D	C	Total_s	Average_s
1	1	55000	5500
4	3	93000	64333.33
5	4	133000	33250

---

b.

SELECT D, C
FROM DEPT_SUMMARY
WHERE TOTAL_S > 100000; 

接受，輸出為

D	C
5	4

---

c.

SELECT D, AVERAGE_S
FROM DEPT_SUMMARY
WHERE C > (SELECT C FROM DEPT_SUMMARY WHERE D=4); 

接受，但是輸出內容沒有任何 Tuple

D	Average_s

---

d.

UPDATE DEPT_SUMMARY
SET D=3
WHERE D=4; 

拒絕，DEPARTMENT.Dnumber 會發生重複。

---

e.

DELETE FROM DEPT_SUMMARY
WHERE C > 4; 

刪除不存在的資料我不確定會接受還是拒絕，我猜測是可以執行，但是沒有任何結果。

tags: 1112_courses database