SOLUTION TO DATABASE ASSIGNMENTS
QUESTION ONE
i.
SELECT *
FROM Repayment
WHERE borrower_id=42 AND loanamount>1000;
ii.
SELECT address, SUM(repayment_amount)
FROM Repayment
GROUP BY address;
iii.
DELETE FROM Repayment A
WHERE loanamount=
(SELECT SUM(repayment_amount)
FROM Repayment B
WHERE B.borrower_id=A.borrower_id AND [Link]=[Link]);
QUESTION TWO
i.
INSERT INTO employee (EMP-ID, NAME, POSITION, HIRE-DATE, SAL-CODE, BRA-
CODE, DEPT, SUPERVISOR)
VALUE (“1373”, “Tope”, “Manager”, “15-Dec 89”, “32000”, “02”, “Admin”, “1323”);
ii.
1045 Smith Salesman 12-MAY-94 18000 04 Sales 3691
4936 Moore Salesman 30-JUN-85 19500 02 Sales 4206
5833 Bradley Technician 08-SEP-88 14500 Technical 7663
iii.
BRANCH DEPARTMENT MAX (SALARY)
01 Accounts 30000
02 Sales 31000
03 Technical 28000
04 Admin 27000
iv.
NAME DEPARTMEN SALARY
Stewart Accounts 16000
Young Accounts 30000
1
Fletcher Accounts 18000
Nicholson Accounts 15000
Hamilton Accounts 20000
Bell Admin 20000
Monaghan Admin 17000
v.
NAME HIRE-DATE POSITION DEPT SALARY
Adams 01-Oct86 Supervisor Sales 23000
Hill 27-Jan90 Manager Admin 27000
Moore 30-Jan85 Salesman Sales 19500
Bradley 08-Sep88 Technician Technical 14500
Hamilton 21-Feb88 Accountant Accounts 20000
Fletcher 29-Oct87 Chief Clerk Accounts 18000
QUESTION THREE
QUESTION FOUR
1) Write an SQL query that returns the project number and name for projects with a
budget greater than $100,000.
SELECT pno, pname
FROM proj
WHERE budget > 100000
2) Write an SQL query that returns all works on records where hours worked is less than
2
10 and the responsibility is 'Manager'.
SELECT *
FROM workson
WHERE hours < 10 AND resp = 'Manager'
3) Write an SQL query that returns the employees (number and name only) who have a
title of 'EE' or 'SA' and make more than $35,000.
SELECT eno, ename
FROM emp
WHERE (title = 'EE' OR title = 'SA') AND salary > 35000
Note: Parentheses are required.
4) Write an SQL query that returns the employees (name only) in department 'D1'
ordered by decreasing salary.
SELECT ename
FROM emp
WHERE dno = 'D1'
ORDER BY salary DESC
Note: Can sort on field not in output - did not need to put salary
in SELECT.
5) Write an SQL query that returns the departments (all fields) ordered by ascending
department name.
SELECT *
FROM dept
ORDER BY dname ASC
6) Write an SQL query that returns the employee name, department name, and employee
title.
SELECT ename, dname, title
FROM emp, dept
WHERE [Link] = [Link]
7) Write an SQL query that returns the project name, hours worked, and project number
for all works on records where hours > 10.
SELECT pname, hours, [Link]
FROM workson, proj
WHERE hours > 10 AND [Link] = [Link]
8) Write an SQL query that returns the project name, department name, and budget for
all projects with a budget < $50,000.
3
SELECT pname, dname, budget
FROM proj, dept
WHERE budget < 50000 AND [Link] = [Link]
9) Write an SQL query that returns the employee numbers and salaries of all employees
in the 'Consulting' department ordered by descending salary.
SELECT eno, salary
FROM emp, dept
WHERE dname = 'Consulting'
ORDER BY salary DESC
10) Write an SQL query that returns the employee name, project name, employee title,
and hours for all works on records.
SELECT ename, pname, title
FROM emp, proj, workson
WHERE [Link] = [Link] and [Link] = [Link]
QUESTION FIVE
QUESTION SIX
i. select name from employee e, books b, loan l
where [Link] = [Link] and [Link] = [Link] and [Link] =
‘McGrawHill’
ii. select name from employee,loan
4
where [Link]=[Link] and isbn in
(select distinct isbn from books where publisher='McGraw-Hill')
group by [Link],name having count(isbn) >=5
iii. select name from employee,loan,books
where [Link]=[Link] and [Link]=[Link]
group by [Link], name,[Link] having
count([Link]) >=5
QUESTION SEVEN