SQLize | PHPize | SQLtest

-- Exercise 1 -- 1. Create BRANCH table CREATE DATABASE JEO; USE JEO; CREATE TABLE branch ( branchno VARCHAR2(4) CONSTRAINT pk_branch PRIMARY KEY, street VARCHAR2(30), city VARCHAR2(20) NOT NULL, postcode VARCHAR2(7) UNIQUE ); -- 2. Create STAFF table CREATE TABLE staff ( staffno VARCHAR2(4) CONSTRAINT pk_staff PRIMARY KEY, fname VARCHAR2(20) NOT NULL, lname VARCHAR2(20) NOT NULL, position VARCHAR2(20), sex CHAR(1), dob DATE, salary NUMBER(8,2) DEFAULT 9000, branchno VARCHAR2(4), CONSTRAINT fk_staff_branch FOREIGN KEY(branchno) REFERENCES branch(branchno) ON DELETE CASCADE ); -- 3. Display structure DESC branch; DESC staff; -- 4. Insert records into BRANCH INSERT INTO branch(branchno, street, city, postcode) VALUES('B005','22 Deer Rd','London','SW1 4EH'); INSERT INTO branch(branchno, street, city, postcode) VALUES('B007','16 Argyll St','Aberdeen','AB2 3SU'); INSERT INTO branch(branchno, street, city, postcode) VALUES('B003','163 Main St','Glasgow','G11 9QX'); INSERT INTO branch(branchno, street, city, postcode) VALUES('B004','32 Manse Rd','Bristol','BS99 1NZ'); INSERT INTO branch(branchno, street, city, postcode) VALUES('B002','56 Clover Dr','London','NW10 6EU'); -- 4. Insert records into STAFF INSERT INTO staff(staffno,fname,lname,position,sex,dob,salary,branchno) VALUES( 'SL21','John','White','Manager','M',TO_DATE('01-OCT-1945','DD-MON-YYYY'),3000,'B005'); INSERT INTO staff(staffno,fname,lname,position,sex,dob,salary,branchno) VALUES( 'SG37','Ann','Beech','Assistant','F',TO_DATE('10-NOV-1960','DD-MON-YYYY'),12000,'B003'); INSERT INTO staff(staffno,fname,lname,position,sex,dob,salary,branchno) VALUES( 'SG14','David','Ford','Supervisor','M',TO_DATE('24-MAR-1958','DD-MON-YYYY'),18000,'B003'); INSERT INTO staff(staffno,fname,lname,position,sex,dob,salary,branchno) VALUES( 'SA9','Mary','Howe','Assistant','F',TO_DATE('19-FEB-1970','DD-MON-YYYY'),9000,'B007'); INSERT INTO staff(staffno,fname,lname,position,sex,dob,salary,branchno) VALUES( 'SG5','Susan','Brand','Manager','F',TO_DATE('03-JUN-1940','DD-MON-YYYY'),24000,'B003'); INSERT INTO staff(staffno,fname,lname,position,sex,dob,salary,branchno) VALUES( 'SL41','Julie','Lee','Assistant','F',TO_DATE('13-JUN-1965','DD-MON-YYYY'),9000,'B005'); COMMIT; -- 5. Remove STAFF table DROP TABLE staff CASCADE CONSTRAINTS; -- Exercise 2: SELECT statements -- 1. List full details of all staff. SELECT * FROM staff; -- 2. StaffNo, FName, LName, Salary SELECT staffno, fname, lname, salary FROM staff; -- 3. Properties: propertyno, rooms, rent, branchno SELECT propertyno, rooms, rent, branchno FROM property; -- 4. Property numbers viewed SELECT DISTINCT propertyno FROM viewing; -- 5. Monthly salaries (same as salary here) SELECT staffno, fname, lname, salary FROM staff; -- 6. Staff with salary > 10000 SELECT * FROM staff WHERE salary > 10000; -- 7. Branch addresses in London or Glasgow SELECT * FROM branch WHERE city IN ('London','Glasgow'); -- 8. Staff with salary between 20000 and 30000 SELECT * FROM staff WHERE salary BETWEEN 20000 AND 30000; -- 9. All managers and supervisors SELECT * FROM staff WHERE position IN ('Manager','Supervisor'); -- 10. Owners with 'Glasgow' in address SELECT * FROM owner WHERE address LIKE '%Glasgow%'; -- 11. Viewings on 'PG4' with no comment SELECT * FROM viewing WHERE propertyno = 'PG4' AND comment IS NULL; -- 12. Salaries descending SELECT staffno, fname, lname, salary FROM staff ORDER BY salary DESC; -- 13. 3- or 4-room properties, rent < 450, ascending rent SELECT * FROM property WHERE rooms IN (3,4) AND rent < 450 ORDER BY rent; -- 14. Count of properties rent > 350 SELECT COUNT(*) AS cnt FROM property WHERE rent > 350; -- 15. Distinct properties viewed in May 2013 SELECT COUNT(DISTINCT propertyno) AS cnt FROM viewing WHERE view_date BETWEEN DATE '2013-05-01' AND DATE '2013-05-31'; -- 16. Number of Managers and sum of their salaries SELECT COUNT(*) AS mgr_count, SUM(salary) AS total_salary FROM staff WHERE position = 'Manager'; -- 17. Min, Max, Avg staff salary SELECT MIN(salary) AS min_sal, MAX(salary) AS max_sal, AVG(salary) AS avg_sal FROM staff; -- Exercise 3: GROUPING, HAVING, SUBQUERIES -- 1. Average rent for flat properties SELECT AVG(rent) AS avg_flat_rent FROM property WHERE type = 'Flat'; -- 2. Number of staff and sum of salaries per branch SELECT branchno, COUNT(*) AS staff_count, SUM(salary) AS total_salary FROM staff GROUP BY branchno; -- 3. Number of properties each staff manages, max rent, where total rent > 400 SELECT staffno, COUNT(*) AS prop_count, MAX(rent) AS max_rent, SUM(rent) AS sum_rent FROM property GROUP BY staffno HAVING SUM(rent) > 400; -- 4. Branch offices with more than one staff: count and sum SELECT branchno, COUNT(*) AS staff_count, SUM(salary) AS total_salary FROM staff GROUP BY branchno HAVING COUNT(*) > 1; -- 5. Staff in branch '163 Main St' SELECT s.* FROM staff s JOIN branch b ON s.branchno = b.branchno WHERE b.street = '163 Main St'; -- 6. Owner name and address where rent > 400 SELECT o.ownerno, o.name, o.address FROM owner o JOIN property p ON o.ownerno = p.ownerno WHERE p.rent > 400; -- 7. Who gets the highest salary? SELECT * FROM staff WHERE salary = (SELECT MAX(salary) FROM staff); -- 8. Staff whose salary > average, and by how much SELECT staffno, fname, lname, salary, salary - (SELECT AVG(salary) FROM staff) AS above_avg FROM staff WHERE salary > (SELECT AVG(salary) FROM staff); -- 9. Properties handled by staff in branch '163 Main St' SELECT p.* FROM property p JOIN staff s ON p.staffno = s.staffno JOIN branch b ON s.branchno = b.branchno WHERE b.street = '163 Main St'; -- Exercise 4: JOINS -- 1. Clients who have viewed a property with comment SELECT c.clientno, c.name, v.propertyno, v.view_date, v.comment FROM client c JOIN viewing v ON c.clientno = v.clientno; -- 2. For each branch: staff who manage properties and the properties SELECT b.branchno, s.staffno, s.fname||' '||s.lname AS staff_name, p.propertyno FROM branch b JOIN staff s ON b.branchno = s.branchno JOIN property p ON s.staffno = p.staffno ORDER BY b.branchno, s.staffno; -- 3. Same as (2) plus branch city SELECT b.branchno, b.city, s.staffno, s.fname||' '||s.lname AS staff_name, p.propertyno FROM branch b JOIN staff s ON b.branchno = s.branchno JOIN property p ON s.staffno = p.staffno ORDER BY b.branchno, s.staffno; -- 4. For each owner: number of properties and max view date SELECT o.ownerno, COUNT(p.propertyno) AS num_props, MAX(v.view_date) AS last_view FROM owner o LEFT JOIN property p ON o.ownerno = p.ownerno LEFT JOIN viewing v ON p.propertyno = v.propertyno GROUP BY o.ownerno; -- 5. Number of properties handled by each staff, with branchno SELECT s.staffno, s.branchno, COUNT(p.propertyno) AS prop_count FROM staff s LEFT JOIN property p ON s.staffno = p.staffno GROUP BY s.staffno, s.branchno; -- Exercise 5: DML -- 1. Insert new staff row INSERT INTO staff(staffno,fname,lname,position,salary,branchno) VALUES( 'SG44','Anne','Jones','Assistant',8100,'B003'); -- 2. Give all staff a 3% pay increase UPDATE staff SET salary = salary * 1.03; -- 3. Give all managers a 5% pay increase UPDATE staff SET salary = salary * 1.05 WHERE position = 'Manager'; -- 4. Promote David Ford to Manager and change salary to 21000 UPDATE staff SET position = 'Manager', salary = 21000 WHERE staffno = 'SG14'; -- 5. Delete all rows from viewing and then undo DELETE FROM viewing; ROLLBACK; -- 6. Delete all viewings for property PG4 DELETE FROM viewing WHERE propertyno = 'PG4'; -- Exercise 6: ALTER TABLE -- 1. Modify STAFF: add default 'Assistant' for POSITION ALTER TABLE staff MODIFY position DEFAULT 'Assistant'; -- Then remove the default ALTER TABLE staff MODIFY position DEFAULT NULL; -- 2. Modify VIEWING: set default NULL for COMMENT ALTER TABLE viewing MODIFY comment DEFAULT NULL; -- 3. Define CLIENTNO as primary key on CLIENT ALTER TABLE client ADD CONSTRAINT pk_client PRIMARY KEY(clientno); -- 4. Add FK constraint to VIEWING.ClientNo referencing CLIENT ALTER TABLE viewing ADD CONSTRAINT fk_viewing_client FOREIGN KEY(clientno) REFERENCES client(clientno); -- 5. Drop the FK constraint just created ALTER TABLE viewing DROP CONSTRAINT fk_viewing_client; -- 6. Increase size of SALARY in STAFF to NUMBER(8,2) ALTER TABLE staff MODIFY salary NUMBER(8,2); ; show status like 'Last_query_cost';