Question: GROUP BY EXERCISES : USING THE ORACLE DEMO TABLES Write SQL scripts to retrieve the following using the Oracle demo tables: 1. The total number

GROUP BY EXERCISES : USING THE ORACLE DEMO TABLES Write SQL scripts to retrieve the following using the Oracle demo tables:

1. The total number of customers in each sales area.

2. List the minimum salary for each job.

3. Show the total number of customers for each rep (repid).

4. List the average customer credit limit for each sales area.

5. Provide a figure for the total salaries of all employees.

6. List the total number of orders placed by each custid.

7. Show the total quantity of each product (prodid) ordered to date.

8. As per (1) above, but excluding customers in the SW area.

9. Show only cities with more than two customers.

10. The total number of customers for each repid but excluding any customers living in Leeds.

11. A script to prompt the user for a sales area and then to display the total number of customers in that area.

12. Show the total number of customers with names starting with B living in each city.

13. Show the total number of customers for each rep (repid), but only for reps with over 2 customers.

14. List the average salary for each job title, but include only job titles with more than two people, and exclude any jobs in Department 20. 15. For each rep, show the total number of customers with a credit limit of over 2000.

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

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

USING THE ORACLE DEMO TABLES Write SQL scripts to retrieve the following using the Oracle demo tables: 1. The total number of customers in each sales area. 2. List the minimum salary for each job. 3....

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...

Mom and POP database: Project 2 Based on the business requirements provided, the ERD would include the following entities: -Customers -Videos/DVDs -Movie -Distributors -Transactions Assignment to be...

Project SEE attatchment of mom and pop database In this assignment you will create the physical database from your design created in Project 2. Project 2 was a logical model of the Mom & Pop...

Project 3 In this assignment you will create the physical database from your design created in Project 2. Project 2 was a logical model of the Mom & Pop database. We need to transition the logical...

A Case Study : Vine Estate Agency Ltd (VEA) is a licensed estate agent, providing a variety of services such as property management, sales, auction, etc. The following is a brief description of the...

In this assignment you will perform the physical design and implementation using SQL Data Definition Language (DDL) and proceed with populating the Mom and Pop Johnson Video Store database via Data...

Project 2 was a logical model of the Mom & Pop database. Step One: CREATE THE SQL DDL FOR YOUR DATABASE For your logical database model created in Project 2 , create the SQL DDL commands that will be...

CMIS 320 Project 3 In this assignment you will perform the physical design and implementation using SQL Data Definition Language (DDL) and proceed with populating the Mom and Pop Johnson Video Store...

he banking schema is provided in the file make-banking.sql. For reference, here is the banking database schema: branch (branch_name, branch_city, assets) customer (customer_name, customer_street,...

Online health information questions please answer the following questions: 1. How often do you search for health information systems on the internet? 2. Online health information may be from...

Theres no reason for me to get excited about the choice between the weighted-average and FIFO methods in my process-costing system. I have long term contracts with my materials suppliers at fixed...

The default risk of a Fixed - rate mortgage ( FRM ) is higher than the default risk of an ARM because ARM's tend to have lower initial interest rate, hence lower payment.

What is the price of a $60 strike put? Assume S = $63.75, = 0.20, r = 0.055, the stock pays no dividend and the option expires in 50 days?

Discuss three reasons why recruiting is important to organizational success.

Evaluate the importance of diversity in the workforce.

Identify the legal standards of the recruitment process.