Write SQL scripts to retrieve the following using the Oracle demo tables:

7. The name of all customers living in a postcode area starting with BS.

8. The customer ids of all customers whose credit limit is between 7000 and 12000.

9. List the employee numbers of all employees who were hired in December of any

year.

10. List the names and job title of all employees, with the most junior employee listed

first. (use HIREDATE to determine seniority)

11. List the names of all employees supervised by managers (mgr) 7902, 7839 or 7566.

Order the list by employee name. 12. Full details of the customer whose customer id is GRE301.

13. A list of all salesmen and their total salary and commission as a single field. Rename

this column as Total Salary.

14. The customer id and credit limit of all customers in Bristol, Swindon or Leeds,

ordered by credit limit, starting with the highest credit value.

15. The name of all customers with a customer id ending in 002 or with W as the third

letter of their surname.

16. List the name of all employees who work in department 10 or who are managers.

17. Produce an alphabetic listing of the name and salary of all employees with names

begin with S or W.

18. The name of all customers who do not live in Manchester.

19. Produce a list showing the customer name, the customers rep and the customers

area. Display the area in full, where

SW = South West

NW = North West

NE = North East

SE = South East

20. Orders shipped more than 10 days after their order date are to be given a refund of

10% of the total order price. List the orderid of these orders, the number of days

taken to ship the order, and the discount applicable. (uses the ORD table)

The demo table:

SET TERMOUT ON PROMPT Building demonstration tables. Please wait. SET TERMOUT OFF DROP TABLE EMP; DROP TABLE DEPT; DROP TABLE SALGRADE; DROP TABLE CUSTOMER; DROP TABLE ORD; DROP TABLE PRICE; DROP TABLE ITEM; DROP TABLE PRODUCT; CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7, 2), COMM NUMBER(7, 2), DEPTNO NUMBER(2)); INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-2015','DD-MON-YYYY'), 18000, NULL, 20); INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-2007', 'DD-MON-YYYY'), 24000, 900, 30); INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-2007', 'DD-MON-YYYY'), 25500, 500, 30); INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('2-APR-2007', 'DD-MON-YYYY'), 49750, NULL, 20); INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('28-SEP-2011', 'DD-MON-YYYY'), 24500, 1400, 30); INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('01-MAY-2007', 'DD-MON-YYYY'), 48500, NULL, 30); INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('9-JUN-2010', 'DD-MON-YYYY'), 44500, NULL, 10); INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('09-DEC-2009', 'DD-MON-YYYY'), 45000, NULL, 20); INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('17-NOV-2008', 'DD-MON-YYYY'), 80000, NULL, 10); INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('08-SEP-2008', 'DD-MON-YYYY'), 25000, 0, 30); INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('12-JAN-2011', 'DD-MON-YYYY'), 11000, NULL, 20); INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, TO_DATE('3-DEC-2012', 'DD-MON-YYYY'), 19500, NULL, 30); INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('3-DEC-2011', 'DD-MON-YYYY'), 40500, NULL, 20); INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, TO_DATE('23-JAN-2010', 'DD-MON-YYYY'), 17000, NULL, 10); CREATE TABLE DEPT (DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13) ); INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS'); INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON'); CREATE TABLE SALGRADE (GRADE NUMBER, LOSAL NUMBER, HISAL NUMBER); INSERT INTO SALGRADE VALUES (1, 700, 11200); INSERT INTO SALGRADE VALUES (2, 11201, 21400); INSERT INTO SALGRADE VALUES (3, 21401, 42000); INSERT INTO SALGRADE VALUES (4, 42001, 63000); INSERT INTO SALGRADE VALUES (5, 63001, 99999); CREATE TABLE CUSTOMER (CUSTID VARCHAR2(6) NOT NULL, NAME VARCHAR2(30), ADDRESS VARCHAR2(40), CITY VARCHAR2(20), POSTCODE VARCHAR2(15), AREA VARCHAR2(2), PHONE VARCHAR2(15), REPID NUMBER(5), CREDITLIMIT NUMBER(6), COMMENTS VARCHAR2(50)); INSERT INTO CUSTOMER VALUES ('BR0001','BROWN P','1 ACACIA AVE','BRISTOL','BS9 1BT','SW','01234 123445',7499,5000,'Deliver before 1pm'); INSERT INTO CUSTOMER VALUES ('BR0002','BROWN J','2 HIGH AVE','BRISTOL','BS6 1TT','SW','01234 234123',7499,15000,NULL); INSERT INTO CUSTOMER VALUES ('BIG001','BIGGLES R','10 MAIN ST','BATH','BA9 1SH','SW','01123 298011',7499,5000,'Morning deliver only'); INSERT INTO CUSTOMER VALUES ('PAT301','PATEL JR','21 BRICK LANE','MANCHESTER','M15 9BT','NW','0161 123456',7521,7000,'Morning deliver only'); INSERT INTO CUSTOMER VALUES ('BR0004','BROWN SW','57 HORN LANE','MANCHESTER','M2 4BT','NW','0161 155666',7521,1000,'No part deliveries'); INSERT INTO CUSTOMER VALUES ('SOL021','SOLTRA P','105 HENLEY CLOSE','MANCHESTER','M16 3BT','NW','0161 879233',7521,20500,'No part deliveries'); INSERT INTO CUSTOMER VALUES ('GRE301','GREEN R','10 BIRCH GROVE','LEEDS','LS12 1RT','NE','01723 812398',7654,1000,'Morning deliver only'); INSERT INTO CUSTOMER VALUES ('MAR011','MARR A','28 WIGMORE AVE','SWINDON','SN3 2RT','SW','01793 245677',7844,2000,'Check delivery address'); INSERT INTO CUSTOMER VALUES ('SPA003','SPALEK J','10 DONNINGION RD','HIGHBURY','SN1 3LP','SE','01792 123099',7844,5000,NULL); INSERT INTO CUSTOMER VALUES ('SPA004','SPALEK B','1 HEAVEN WAY','HIGHPLACE','UP1 3LU','SW','01793 456222',7844,5000,NULL); INSERT INTO CUSTOMER VALUES ('HOW002','HOWARD D','3 BROADWAY CLOSE','SWINDON','SN1 1B','SW','01793 452341',7844,12000,'Must be signed for'); CREATE TABLE ORD (ORDID NUMBER(6) NOT NULL, CUSTID VARCHAR2(6), ORDERDATE DATE, COMMPLAN VARCHAR2(1), SHIPDATE DATE, TOTAL NUMBER(7, 2)); INSERT INTO ORD VALUES(100001,'BR0002',TO_DATE('03-Jan-2016', 'DD-MON-YYYY'),'A',TO_DATE('06-Jan-2016', 'DD-MON-YYYY'),64); INSERT INTO ORD VALUES(100002,'BR0002',TO_DATE('06-Jan-2016', 'DD-MON-YYYY'),'B',TO_DATE('09-Jan-2016', 'DD-MON-YYYY'),12); INSERT INTO ORD VALUES(100003,'BR0002',TO_DATE('10-Feb-2016', 'DD-MON-YYYY'),'A',TO_DATE('15-Feb-2016', 'DD-MON-YYYY'),372); INSERT INTO ORD VALUES(100004,'BIG001',TO_DATE('03-Jan-2016', 'DD-MON-YYYY'),'A',TO_DATE('07-Jan-2016', 'DD-MON-YYYY'),222); INSERT INTO ORD VALUES(100005,'PAT301',TO_DATE('03-Jan-2016', 'DD-MON-YYYY'),'B',TO_DATE('06-Jan-2016', 'DD-MON-YYYY'),91.5); INSERT INTO ORD VALUES(100006,'PAT301',TO_DATE('10-Feb-2016', 'DD-MON-YYYY'),'B',TO_DATE('24-Feb-2016', 'DD-MON-YYYY'),313.1); INSERT INTO ORD VALUES(100007,'PAT301',TO_DATE('24-Jan-2016', 'DD-MON-YYYY'),'A',TO_DATE('06-Feb-2016', 'DD-MON-YYYY'),1910); INSERT INTO ORD VALUES(100008,'SOL021',TO_DATE('01-Jan-2016', 'DD-MON-YYYY'),'C',TO_DATE('09-Feb-2016', 'DD-MON-YYYY'),110); INSERT INTO ORD VALUES(100009,'SOL021',TO_DATE('03-Feb-2016', 'DD-MON-YYYY'),'A',TO_DATE('06-Feb-2016', 'DD-MON-YYYY'),2150); INSERT INTO ORD VALUES(100010,'SOL021',TO_DATE('21-Jan-2016', 'DD-MON-YYYY'),'C',TO_DATE('06-Mar-2016', 'DD-MON-YYYY'),536.7); INSERT INTO ORD VALUES(100011,'GRE301',TO_DATE('31-Jan-2016', 'DD-MON-YYYY'),'B',TO_DATE('12-Feb-2016', 'DD-MON-YYYY'),168.5); INSERT INTO ORD VALUES(100012,'MAR011',TO_DATE('24-Feb-2016', 'DD-MON-YYYY'),'A',TO_DATE('06-Mar-2016', 'DD-MON-YYYY'),45); INSERT INTO ORD VALUES(100013,'MAR011',TO_DATE('29-Feb-2016', 'DD-MON-YYYY'),'A',TO_DATE('16-Mar-2016', 'DD-MON-YYYY'),65); INSERT INTO ORD VALUES(100014,'SPA003',TO_DATE('01-Jan-2016', 'DD-MON-YYYY'),'B',TO_DATE('09-Feb-2016', 'DD-MON-YYYY'),900); INSERT INTO ORD VALUES(100015,'HOW002',TO_DATE('15-Feb-2016', 'DD-MON-YYYY'),'C',TO_DATE('14-Mar-2016', 'DD-MON-YYYY'),68.1); CREATE TABLE PRICE (PRODID NUMBER(3) NOT NULL, STARTDATE DATE, STDPRICE NUMBER(6, 2), MINPRICE NUMBER(6, 2), ENDDATE DATE); INSERT INTO PRICE VALUES(110,TO_DATE('01-Jan-2015', 'DD-MON-YYYY'),10.5,9.5,TO_DATE('31-Dec-2015', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(110,TO_DATE('01-Jan-2016', 'DD-MON-YYYY'),10.7,10.2,TO_DATE('31-Dec-2018', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(120,TO_DATE('01-Jan-2015', 'DD-MON-YYYY'),22,15.5,TO_DATE('31-Dec-2015', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(120,TO_DATE('01-Jan-2016', 'DD-MON-YYYY'),14.2,17.2,TO_DATE('31-Dec-2018', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(130,TO_DATE('01-Jan-2015', 'DD-MON-YYYY'),30.5,20.5,TO_DATE('31-Dec-2015', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(130,TO_DATE('01-Jan-2016', 'DD-MON-YYYY'),32.7,22.7,TO_DATE('31-Dec-2018', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(210,TO_DATE('01-Jan-2015', 'DD-MON-YYYY'),46.5,30.5,TO_DATE('31-Dec-2015', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(210,TO_DATE('01-Jan-2016', 'DD-MON-YYYY'),50.2,44.4,TO_DATE('31-Dec-2018', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(220,TO_DATE('01-Jan-2015', 'DD-MON-YYYY'),65.5,45.5,TO_DATE('31-Dec-2015', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(220,TO_DATE('01-Jan-2016', 'DD-MON-YYYY'),70.7,60.5,TO_DATE('31-Dec-2018', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(230,TO_DATE('01-Jan-2015', 'DD-MON-YYYY'),95.5,60.5,TO_DATE('31-Dec-2015', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(230,TO_DATE('01-Jan-2016', 'DD-MON-YYYY'),100.3,70.77,TO_DATE('31-Dec-2018', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(330,TO_DATE('01-Jan-2015', 'DD-MON-YYYY'),120.2,100,TO_DATE('31-Dec-2015', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(330,TO_DATE('01-Jan-2016', 'DD-MON-YYYY'),13,120,TO_DATE('31-Dec-2018', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(320,TO_DATE('01-Jan-2015', 'DD-MON-YYYY'),100,85,TO_DATE('31-Dec-2015', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(320,TO_DATE('01-Jan-2016', 'DD-MON-YYYY'),120.25,110.4,TO_DATE('31-Dec-2018', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(310,TO_DATE('01-Jan-2015', 'DD-MON-YYYY'),50,40,TO_DATE('31-Dec-2015', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(310,TO_DATE('01-Jan-2016', 'DD-MON-YYYY'),40,30,TO_DATE('31-Dec-2018', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(410,TO_DATE('01-Jan-2015', 'DD-MON-YYYY'),350,300,TO_DATE('31-Dec-2015', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(410,TO_DATE('01-Jan-2016', 'DD-MON-YYYY'),400,370,TO_DATE('31-Dec-2018', 'DD-MON-YYYY')); INSERT INTO PRICE VALUES(420,TO_DATE('01-Jan-2016', 'DD-MON-YYYY'),420,390,TO_DATE('31-Dec-2018', 'DD-MON-YYYY')); CREATE TABLE ITEM (ORDID NUMBER(6) NOT NULL, ITEMID NUMBER(2), PRODID NUMBER(3), ACTUALPRICE NUMBER(6, 2), QTY NUMBER(2), ITEMTOT NUMBER(6, 2)); INSERT INTO ITEM VALUES(100001,1,110,10,3,30); INSERT INTO ITEM VALUES(100001,2,120,16,2,32); INSERT INTO ITEM VALUES(100002,1,110,12,1,12); INSERT INTO ITEM VALUES(100003,1,210,46.5,8,372); INSERT INTO ITEM VALUES(100004,1,130,40,1,40); INSERT INTO ITEM VALUES(100004,2,210,76,2,152); INSERT INTO ITEM VALUES(100004,3,110,30,1,30); INSERT INTO ITEM VALUES(100005,1,110,30.5,3,91.5); INSERT INTO ITEM VALUES(100006,1,120,93,1,93); INSERT INTO ITEM VALUES(100006,2,220,110,2,220); INSERT INTO ITEM VALUES(100007,1,120,30,10,300); INSERT INTO ITEM VALUES(100007,2,230,230,7,1610); INSERT INTO ITEM VALUES(100008,1,110,22,5,110); INSERT INTO ITEM VALUES(100009,1,230,215,10,2150); INSERT INTO ITEM VALUES(100010,1,110,18,12,216); INSERT INTO ITEM VALUES(100010,2,230,320.7,1,320.7); INSERT INTO ITEM VALUES(100011,1,210,110,1,110); INSERT INTO ITEM VALUES(100011,2,110,19.5,3,58.5); INSERT INTO ITEM VALUES(100012,1,120,45,1,45); INSERT INTO ITEM VALUES(100013,1,220,65.6,1,65.5); INSERT INTO ITEM VALUES(100014,1,210,90,10,900); CREATE TABLE PRODUCT (PRODID NUMBER(3) NOT NULL, DESCRIP VARCHAR2(50)); INSERT INTO PRODUCT VALUES(110,'Sprocket 10mm'); INSERT INTO PRODUCT VALUES(120,'Sprocket 20mm'); INSERT INTO PRODUCT VALUES(130,'Sprocket 30mm'); INSERT INTO PRODUCT VALUES(210,'Widget 10mm'); INSERT INTO PRODUCT VALUES(220,'Widget 20mm'); INSERT INTO PRODUCT VALUES(230,'Widget 30mm'); INSERT INTO PRODUCT VALUES(330,'Bolt 5mm'); INSERT INTO PRODUCT VALUES(320,'Bolt 10mm'); INSERT INTO PRODUCT VALUES(310,'Bolt 15mm'); INSERT INTO PRODUCT VALUES(410,'Spring 2cm'); INSERT INTO PRODUCT VALUES(420,'Spring 3cm'); INSERT INTO PRODUCT VALUES(430,'Spring 4cm'); COMMIT; SET TERMOUT ON PROMPT Demonstration table build is complete. EXIT