Construct SQL Statements to Find

1: / Write a compound query to find the customers who have placed an order.
A: / SELECT CUST_ID FROM CUSTOMER_TBL
INTERSECT
SELECT CUST_ID FROM ORDERS_TBL;
2: / Write a compound query to find the customers who have not placed an order.
A: / SELECT CUST_ID FROM CUSTOMER_TBL
EXCEPT
SELECT CUST_ID FROM ORDERS_TBL;
3: / Using a subquery, write a query that lists all products that cost more than the average cost of all products.
A: / SELECT PROD_DESC
FROM PRODUCTS_TBL
WHERE COST > (SELECT AVG(COST)
FROM PRODUCTS_TBL);
1: / Write an SQL statement to return the emp_id,last_name, andfirst_name from the EMPLOYEE_TBL and SALARY,BONUS from the EMPLOYEE_PAY_TBL.
A: / SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME
EP.SALARY, EP.BONUS
FROM EMPLOYEE_TBL E,
EMPLOYE_PAY_TBL EP
WHERE E.EMP_ID = EP.EMP_ID
2: / Select from the CUSTOMERS_TBL the columns: CUST_ID,CUST_NAME. Select from the PRODUCTS_TBL the columns: PROD_ID,COST. Select from the ORDERS_TBL the columns: ORD_NUM,QTY. Join all three of the tables into one SQL statement.
A: / SELECT C.CUST_ID, C.CUST_NAME, P.PROD_ID, P.COST,
O.ORD_NUM, O.QTY
FROM CUSTOMER_TBL C,
PRODUCT_TBL P,
ORDERS_TBL O
WHERE C.CUST_ID = O.CUST_ID
AND P.PROD_ID = O.PROD_ID
1: / Write an SQL statement that returns the employee ID, employee name, and city from the EMPLOYEE_TBL. Group by the city column first.
A: / SELECT EMP_ID, LAST_NAME, FIRST_NAME, CITY
FROM EMPLOYEE_TBL
GROUP BY CITY, EMP_ID, LAST)NAME, FIRST_NAME;
2: / Write an SQL statement that returns the city and a count of all employees per city from EMPLOYEE_TBL. Add a HAVING clause to display only those cities that have a count of more than two employees.
A: / SELECT CITY, COUNT(EMP_ID)
FROM EMPLOYEE_TBL
GROUP BY CITY
HAVING COUNT(EMP_ID) > 2;

Construct SQL statements to find:

The average salary.

The maximum bonus.

The total salaries.

The minimum pay rate.

The total rows in the table.

A: The average salary is $30,000.00. The SQL statement to return the data is

SELECT AVG(SALARY)

FROM EMPLOYEE_PAY_TBL;

The maximum bonus is $3000.00. The SQL statement to return the data is

SELECT MAX(BONUS)

FROM EMPLOYEE_PAY_TBL;

The sum of all the salaries is $60,000.00. The SQL statement to return the data is

SELECT SUM(SALARY)

FROM EMPLOYEE_PAY_TBL;

The minimum pay rate is $11.00 an hour. The SQL statement to return the data is

SELECT MIN(PAY_RATE)

FROM EMPLOYEE_PAY_TBL;

The total row count of the table is six. The SQL statement to return the data is

SELECT COUNT(*)

FROM EMPLOYEE_PAY_TBL;

Write a SELECT statement that returns the product ID, PROD_DESC, and the product cost. Limit the product cost to range from $1.00 to $12.50.
SELECT *
FROM PRODUCTS_TBL
WHERE COST BETWEEN 1.00 AND 12.50
2: Write a statement that creates a summarized view containing the average pay rate and average salary for each city in the EMPLOYEE_TBL table.
CREATE VIEW AVG_PAY_VIEW AS
SELECT E.CITY, AVG(P.PAY_RATE), AVG(P.SALARY)
FROM EMPLOYEE_PAY_TBL P,
EMPLOYEE_TBL E
WHERE P.EMP_ID = E.EMP_ID
GROUP BY E.CITY;
2: Write a statement to create a stored procedure that deletes an entry from the PRODUCTS_TBL table; it should be similar to the example used in this hour to insert a new product.
A: CREATE PROCEDURE DELETE_PRODUCT
(OLD_PROD_ID IN VARCHAR2)
AS
BEGIN
DELETE FROM PRODUCTS_TBL
WHERE PROD_ID = OLD_PROD_ID;
COMMIT;
END;
/
3: Write a statement that executes the stored procedure that you created in Exercise 2 to delete the row for PROD_ID '9999'.
A: EXECUTE DELETE_PRODUCT ('9999');

EMPLOYEE_TBL

CREATE TABLE EMPLOYEE_TBL

{

EMP_ID VARCHAR2(9) NOT NULL,

LAST_NAME VARCHAR2(15) NOT NULL,

FIRST_NAME VARCHAR2(15) NOT NULL,

MIDDLE_NAME VARCHAR2(15),

ADDRESS VARCHAR2(30) NOT NULL,

CITY VARCHAR2(15) NOT NULL,

STATE CHAR(2) NOT NULL,

ZIP NUMBER(5) NOT NULL,

PHONE CHAR(10),

PAGER CHAR(10),

CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID)

}

EMPLOYEE_PAY_TBL

CREATE TABLE EMPLOYEE_PAY_TBL

{

EMP_ID VARCHAR2(9) NOT NULL PRIMARYKEY,

POSITION VARCHAR2(15) NOT NULL,

DATE_HIRE DATE,

PAY_RATE NUMBER(4,2),

DATE_LAST_RAISE DATE,

SALARY NUMBER(8,2),

BONUS NUMBER(6,2),

CONSTRAINT EMP_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID)

}

CUSTOMER_TBL

CREATE TABLE CUSTOMER_TBL

}

CUST_ID VARCHAR2(10) NOT NULL PRIMARYKEY,

CUST_NAME VARCHAR2(30) NOT NULL,

CUST_ADDRESS VARCHAR2(20) NOT NULL,

CUST_CITY VARCHAR2(15) NOT NULL,

CUST_STATE CHAR(2) NOT NULL,

CUST_ZIP NUMBER(5) NOT NULL,

CUST_PHONE NUMBER(10),

CUST_FAX NUMBER(10),

}

ORDERS_TBL

CREATE TABLE ORDERS_TBL

{

ORD_NUM VARCHAR2(10) NOT NULL PRIMARYKEY,

CUST_ID VARCHAR2(10) NOT NULL,

PROD_ID VARCHAR2(10) NOT NULL,

QTY NUMBER(6) NOT NULL,

ORD_DATE DATE,

}

PRODUCTS_TBL

CREATE TABLE PRODUCTS_TBL

}

PROD_ID VARCHAR2(10) NOT NULL PRIMARYKEY,

PROD_DESC VARCHAR2(40) NOT NULL,

COST NUMBER(6,2) NOT NULL,

}

INSERT Statements

EMPLOYEE_TBL

INSERT INTO EMPLOYEE_TBL VALUES

('311549902', 'STEPHENS', 'TINA', 'DAWN', 'RR 3 BOX 17A', 'GREENWOOD',

'IN', '47890', '3178784465', NULL)

INSERT INTO EMPLOYEE_TBL VALUES

('442346889', 'PLEW', 'LINDA', 'CAROL', '3301 BEACON', 'INDIANAPOLIS',

'IN', '46224', '3172978990', NULL)

INSERT INTO EMPLOYEE_TBL VALUES

('213764555', 'GLASS', 'BRANDON', 'SCOTT', '1710 MAIN ST', 'WHITELAND',

'IN', '47885', '3178984321', '3175709980')

INSERT INTO EMPLOYEE_TBL VALUES

('313782439', 'GLASS', 'JACOB', NULL, '3789 WHITE RIVER BLVD',

'INDIANAPOLIS', 'IN', '45734', '3175457676', '8887345678')

INSERT INTO EMPLOYEE_TBL VALUES

('220984332', 'WALLACE', 'MARIAH', NULL, '7889 KEYSTONE AVE',

'INDIANAPOLIS', 'IN', '46741', '3173325986', NULL)

INSERT INTO EMPLOYEE_TBL VALUES

('443679012', 'SPURGEON', 'TIFFANY', NULL, '5 GEORGE COURT',

'INDIANAPOLIS', 'IN', '46234', '3175679007', NULL)

EMPLOYEE_PAY_TBL

INSERT INTO EMPLOYEE_PAY_TBL VALUES

('311549902', 'MARKETING', '23-MAY-89', NULL, '01-MAY-99', '40000', NULL)

INSERT INTO EMPLOYEE_PAY_TBL VALUES

('442346889', 'TEAM LEADER', '17-JUN-90', '14.75', '01-JUN-99', NULL, NULL)

INSERT INTO EMPLOYEE_PAY_TBL VALUES

('213764555', 'SALES MANAGER', '14-AUG-94', NULL, '01-AUG-99', '30000', '2000')

INSERT INTO EMPLOYEE_PAY_TBL VALUES

('313782439', 'SALESMAN', '28-JUN-97', NULL, NULL, '20000', '1000')

INSERT INTO EMPLOYEE_PAY_TBL VALUES

('220984332', 'SHIPPER', '22-JUL-96', '11.00', '01-JUL-99', NULL, NULL)

INSERT INTO EMPLOYEE_PAY_TBL VALUES

('443679012', 'SHIPPER', '14-JAN-91', '15.00', '01-JAN-99', NULL, NULL)

CUSTOMER_TBL