Please see below two example databases I created, each has individual queries and questions that have all been executed.
TimKay Database
DROP DATABASE IF EXISTS TIMKAY;
CREATE DATABASE TIMKAY;
USE TIMKAY;
DROP TABLE IF EXISTS SALES_REP;
CREATE TABLE SALES_REP
(
REP_ID CHAR(2) PRIMARY KEY,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(20),
ADDRESS VARCHAR(20),
CITY VARCHAR(15),
STATE CHAR(2),
POSTAL CHAR(5),
CELL_PHONE CHAR(12),
COMMISSION DECIMAL(7,2),
RATE DECIMAL(3,2)
);
insert into sales_rep values('05','Susan','Garcia', '42 Mountain Ln','Cody', 'WY', '82414', '307-824-1245', 12743.16, 0.04);
insert into sales_rep values('10','Richard','Miller','87 Pikes Dr', 'Ralston', 'WY', '82440', '307-406-4321', 20872.11, 0.06);
insert into sales_rep values('15','Donna','Smith', '312 Oak Rd', 'Powell', 'WY', '82440', '307-982-8401', 14912.92, 0.04);
insert into sales_rep values('20','Daniel','Jackson','19 Lookout Dr', 'Elk Butte','WY', '82433', '307-883-9481', 0.00, 0.04);
DROP TABLE IF EXISTS CUSTOMER;
CREATE TABLE CUSTOMER
(
CUST_ID CHAR(3) PRIMARY KEY,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(20),
ADDRESS VARCHAR(20),
CITY VARCHAR(15),
STATE CHAR(2),
POSTAL CHAR(5),
EMAIL VARCHAR(30),
BALANCE DECIMAL(7,2),
CREDIT_LIMIT DECIMAL (7,2),
REP_ID CHAR(2)
);
INSERT INTO CUSTOMER VALUES('125','Joey' ,'Smith', '17 Fourth St', 'Cody', 'WY','82414','jsmith17@example.com', 80.68 ,500.00 ,'05');
INSERT INTO CUSTOMER VALUES('182','Billy' ,'Rufton', '21 Simple Cir', 'Garland', 'WY','82435','billyruff@example.com', 43.13 ,750.00 ,'10');
INSERT INTO CUSTOMER VALUES('227','Sandra' ,'Pincher', '53 Verde Ln', 'Powell', 'WY','82440','spinch2@example.com', 156.38,500.00 ,'15');
INSERT INTO CUSTOMER VALUES('294','Samantha','Smith', '14 Rock Ln', 'Ralston', 'WY','82440','ssmith5@example.com', 58.60 ,500.00 ,'10');
INSERT INTO CUSTOMER VALUES('314','Tom' ,'Rascal', '1 Rascal Farm Rd', 'Cody', 'WY','82414','trascal3@example.com', 17.25 ,250.00 ,'15');
INSERT INTO CUSTOMER VALUES('375','Melanie' ,'Jackson', '42 Blackwater Way', 'Elk Butte','WY','82433','mjackson5@example.com', 252.25,250.00 ,'05');
INSERT INTO CUSTOMER VALUES('435','James' ,'Gonzalez', '16 Rockway Rd', 'Wapiti', 'WY','82450','jgonzo@example.com', 230.40,1000.00 ,'15');
INSERT INTO CUSTOMER VALUES('492','Elmer' ,'Jackson', '22 Jackson Farm Rd','Garland', 'WY','82435','ejackson4@example.com', 45.20 ,500.00 ,'10');
INSERT INTO CUSTOMER VALUES('543','Angie' ,'Hendricks', '27 Locklear Ln', 'Powell', 'WY','82440','ahendricks7@example.com', 315.00,750.00 ,'05');
INSERT INTO CUSTOMER VALUES('616','Sally' ,'Cruz', '199 18th Ave', 'Ralston', 'WY','82440','scruz5@example.com', 8.33 ,500.00 ,'15');
INSERT INTO CUSTOMER VALUES('721','Leslie' ,'Smith', '123 Sheepland Rd', 'Elk Butte','WY','82433','lsmith12@example.com', 166.65,1000.00 ,'10');
INSERT INTO CUSTOMER VALUES('795','Randy' ,'Blacksmith','75 Stream Rd', 'Cody', 'WY','82414','rblacksmith6@example.com',61.50 ,500.00 ,'05');
DROP TABLE IF EXISTS ITEM;
CREATE TABLE ITEM
(
ITEM_ID CHAR(4) PRIMARY KEY,
DESCRIPTION VARCHAR(30),
ON_HAND NUMERIC,
CATEGORY CHAR(3),
LOCATION CHAR(1),
PRICE DECIMAL(7,2)
);
INSERT INTO ITEM VALUES('AD72','Dog Feeding Station' ,'12','DOG','B',79.99 );
INSERT INTO ITEM VALUES('BC33','Feathers Bird Cage (12x24x18)' ,'10','BRD','B',79.99 );
INSERT INTO ITEM VALUES('CA75','Enclosed Cat Litter Station' ,'15','CAT','C',39.99 );
INSERT INTO ITEM VALUES('DT12','Dog Toy Gift Set' ,'27','DOG','B',39.99 );
INSERT INTO ITEM VALUES('FM23','Fly Mask with Ears' ,'41','HOR','C',24.95 );
INSERT INTO ITEM VALUES('FS39','Folding Saddle Stand' ,'12','HOR','C',39.99 );
INSERT INTO ITEM VALUES('FS42','Aquarium (55 Gallon)' ,'5','FSH','A',124.99);
INSERT INTO ITEM VALUES('KH81','Wild Bird Food (25 lb)' ,'24','BRD','C',19.99 );
INSERT INTO ITEM VALUES('LD14','Locking Small Dog Door' ,'14','DOG','A',49.99 );
INSERT INTO ITEM VALUES('LP73','Large Pet Carrier' ,'23','DOG','B',59.99 );
INSERT INTO ITEM VALUES('PF19','Pump & Filter Kit' ,'5','FSH','A',74.99 );
INSERT INTO ITEM VALUES('QB92','Quilted Stable Blanket' ,'32','HOR','C',119.99);
INSERT INTO ITEM VALUES('SP91','Small Pet Carrier' ,'18','CAT','B',39.99 );
INSERT INTO ITEM VALUES('UF39','Underground Fence System' ,'7','DOG','A',199.99);
INSERT INTO ITEM VALUES('WB49','Insulated Water Bucket' ,'34','HOR','C',79.99 );
DROP TABLE IF EXISTS INVOICES;
CREATE TABLE INVOICES
(
INVOICE_NUM CHAR(5) PRIMARY KEY,
INVOICE_DATE DATE,
CUST_ID VARCHAR(3)
);
INSERT INTO INVOICES VALUES('14216','2021-11-15','125');
INSERT INTO INVOICES VALUES('14219','2021-11-15','227');
INSERT INTO INVOICES VALUES('14222','2021-11-16','294');
INSERT INTO INVOICES VALUES('14224','2021-11-16','182');
INSERT INTO INVOICES VALUES('14228','2021-11-18','435');
INSERT INTO INVOICES VALUES('14231','2021-11-18','125');
INSERT INTO INVOICES VALUES('14233','2021-11-18','435');
INSERT INTO INVOICES VALUES('14237','2021-11-19','616');
DROP TABLE IF EXISTS INVOICE_LINE;
CREATE TABLE INVOICE_LINE
(
INVOICE_NUM CHAR(5),
ITEM_ID CHAR(4),
QUANTITY NUMERIC,
QUOTED_PRICE DECIMAL(7,2),
PRIMARY KEY (INVOICE_NUM, ITEM_ID)
);
INSERT INTO INVOICE_LINE VALUES('14216','CA75',3,37.99 );
INSERT INTO INVOICE_LINE VALUES('14219','AD72',2,79.99 );
INSERT INTO INVOICE_LINE VALUES('14219','DT12',4,39.99 );
INSERT INTO INVOICE_LINE VALUES('14222','LD14',1,47.99 );
INSERT INTO INVOICE_LINE VALUES('14224','KH81',4,18.99 );
INSERT INTO INVOICE_LINE VALUES('14228','FS42',1,124.99);
INSERT INTO INVOICE_LINE VALUES('14228','PF19',1,74.99 );
INSERT INTO INVOICE_LINE VALUES('14231','UF39',2,189.99);
INSERT INTO INVOICE_LINE VALUES('14233','KH81',1,19.99 );
INSERT INTO INVOICE_LINE VALUES('14233','QB92',4,109.95);
INSERT INTO INVOICE_LINE VALUES('14233','WB49',4,74.95 );
INSERT INTO INVOICE_LINE VALUES('14237','LP73',3,54.95 );
COMMIT;
-- Assignments
-- 1) List the number, first name, last name, and balance for all customers.
SELECT CUST_ID, FIRST_NAME, LAST_NAME, BALANCE
FROM CUSTOMER;
-- 2) List the complete ITEM table.
SELECT *
FROM ITEM;
-- 3) What is the last name of the customer with the customer ID 125?
SELECT LAST_NAME
FROM CUSTOMER
WHERE CUST_ID = '125';
-- 4) Find the last name of each customer located in the city Cody.
SELECT LAST_NAME
FROM CUSTOMER
WHERE CITY = "CODY";
-- 5) Find first name, last name, balance, and credit limit for all customers with balances that exceed their credit limit.
SELECT FIRST_NAME, LAST_NAME, BALANCE, CREDIT_LIMIT
FROM CUSTOMER
WHERE BALANCE > CREDIT_LIMIT;
-- 6) List the descriptions of all items that are stored in location B and for which there are more than 15 units on hand.
SELECT DESCRIPTION
FROM ITEM
WHERE LOCATION = 'B'
AND ON_HAND > 15;
-- 7) List the descriptions of all items that are stored in location B or for which there are more that 15 units on hand.
SELECT DESCRIPTION
FROM ITEM
WHERE LOCATION = 'B'
OR ON_HAND > 15;
-- 8) List the descriptions of all items that are not stored in location B.
SELECT DESCRIPTION
FROM ITEM
WHERE LOCATION <> 'B';
-- Variation
SELECT DESCRIPTION
FROM ITEM
WHERE NOT LOCATION = 'B';
-- 9) List the customer ID, first name, last name, and balance of all customers with balances greater than or equal
-- to $125 and less than or equal to $250.
SELECT CUST_ID,FIRST_NAME,LAST_NAME,BALANCE
FROM CUSTOMER
WHERE BALANCE BETWEEN 125 AND 250;
-- Variation
SELECT CUST_ID,FIRST_NAME,LAST_NAME,BALANCE
FROM CUSTOMER
WHERE BALANCE >= 125
AND BALANCE <= 250;
-- 10) Find the number, first and last name, and available credit (the credit limit minus the balance) for each customer.
SELECT CUST_ID, FIRST_NAME, LAST_NAME, (CREDIT_LIMIT - BALANCE) AS AVAILABLE_CREDIT
FROM CUSTOMER;
-- 11) Find the customer ID, first name, last name, and available credit for each customer with at least $400 of available
-- credit.
SELECT CUST_ID, FIRST_NAME, LAST_NAME, (CREDIT_LIMIT - BALANCE) AS AVAILABLE_CREDIT
FROM CUSTOMER
WHERE (CREDIT_LIMIT - BALANCE) >= 400;
-- 12) List the customer ID, first name, last name, and complete address of each customer located at an address that
-- contains the letters "Rock."
SELECT CUST_ID, FIRST_NAME, LAST_NAME, ADDRESS
FROM CUSTOMER
WHERE ADDRESS LIKE '%ROCK%';
-- Variation
SELECT CUST_ID, FIRST_NAME, LAST_NAME, CONCAT(ADDRESS,' ',CITY,', ',STATE,' ',POSTAL)
FROM CUSTOMER
WHERE ADDRESS LIKE '%ROCK%';
-- 13) List the customer ID, first name, last name, and credit limit for each customer with a credit limit of $500,
-- $750, or $1,000.
SELECT FIRST_NAME, LAST_NAME, CREDIT_LIMIT
FROM CUSTOMER
WHERE CREDIT_LIMIT = 500
OR CREDIT_LIMIT = 750
OR CREDIT_LIMIT = 1000;
-- Variation
SELECT FIRST_NAME, LAST_NAME, CREDIT_LIMIT
FROM CUSTOMER
WHERE CREDIT_LIMIT IN (500, 750, 1000);
-- 14) List the customer ID, first name, last name, and balance of each customer. Order (sort) the output in ascending
-- (increasing) order by balance.
SELECT CUST_ID, FIRST_NAME, LAST_NAME, BALANCE
FROM CUSTOMER
ORDER BY BALANCE;
-- 15) List the customer ID, first name, last name, and credit limit of each customer. Order the customers by last name
-- within defending credit limit.
SELECT CUST_ID, FIRST_NAME, LAST_NAME, CREDIT_LIMIT
FROM CUSTOMER
ORDER BY CREDIT_LIMIT DESC, LAST_NAME;
-- 16) How many items are in the category DOG?
SELECT CATEGORY
FROM ITEM
WHERE CATEGORY = 'DOG';
-- Variation
SELECT COUNT(*)
FROM ITEM
WHERE CATEGORY = 'DOG';
-- 17) Find the total number of KimTay Pet Supplies customers and the total of their balances.
SELECT COUNT(*), SUM(BALANCE)
FROM CUSTOMER;
-- 18) Find the sum of all balances, the average balance, the maximum balance, and the minimum balance of all KimTay
-- Pet Supplies customers.
SELECT COUNT(*), SUM(BALANCE), AVG(BALANCE), MAX(BALANCE), MIN(BALANCE)
FROM CUSTOMER;
-- 19) Find the customer ID of each customer that currently have an invoice (i.e., an invoice currently in the INVOICES table).
SELECT CUST_ID
FROM INVOICES;
-- 20) Find the number of each customer that currently have an open order. List each customer only once.
SELECT DISTINCT CUST_ID
FROM INVOICES;
-- 21) Count the number of customers that currently have invoices.
SELECT DISTINCT COUNT(CUST_ID)
FROM INVOICES;
-- 22) List the item ID of each item in category HOR.
SELECT ITEM_ID
FROM ITEM
WHERE CATEGORY = "HOR";
-- 23) List the invoice numbers that contain an invoice line for an item in category HOR.
SELECT INVOICE_NUM
FROM INVOICE_LINE
WHERE ITEM_ID IN ('FM23', 'FS29', 'QB92', 'WB49');
-- 24) Find the answer to Example 22 and 23 in one step.
SELECT INVOICE_NUM
FROM INVOICE_LINE
WHERE ITEM_ID IN (SELECT ITEM_ID
FROM ITEM
WHERE CATEGORY = "HOR");
-- 25) List the customer ID, first name, last name, and balance for each customer whose balance exceeds the average balance
-- of all customers.
SELECT CUST_ID, FIRST_NAME, LAST_NAME, BALANCE
FROM CUSTOMER
WHERE BALANCE > (SELECT AVG(BALANCE)
FROM CUSTOMER);
-- 26) For each sales rep, list the rep, list the rep ID and the average balance of the rep's customers.
SELECT REP_ID, AVG(BALANCE)
FROM CUSTOMER
GROUP BY REP_ID;
-- 27) Repeat the previous example, but list only those reps whose customers have an average balance greater than $100.
SELECT REP_ID, AVG(BALANCE)
FROM CUSTOMER
GROUP BY REP_ID
HAVING AVG(BALANCE) > 100;
-- 28) List each credit limit and the number of customers having each credit limit.
SELECT CREDIT_LIMIT, COUNT(*)
FROM CUSTOMER
GROUP BY CREDIT_LIMIT;
-- 29) Repeat Example 28, but list only those credit limits held by more than two customers.
SELECT CREDIT_LIMIT, COUNT(*)
FROM CUSTOMER
GROUP BY CREDIT_LIMIT
HAVING COUNT(*) > 2;
-- 30) List each credit limit and the number of customers of sales rep 05 that have this limit.
SELECT CREDIT_LIMIT, COUNT(*)
FROM CUSTOMER
WHERE REP_ID = '05'
GROUP BY CREDIT_LIMIT;
-- 31) Repeat Example 30, but list only those credit limits held by fewer than two customers
SELECT CREDIT_LIMIT, COUNT(*)
FROM CUSTOMER
WHERE REP_ID = '05'
GROUP BY CREDIT_LIMIT
HAVING COUNT(*) < 2;
-- 32) List the number and name of each customer with a null (unknown) address value.
SELECT CUST_ID, FIRST_NAME, LAST_NAME
FROM CUSTOMER
WHERE ADDRESS IS NULL;
-- 33) List the ID, first name, last name of each customer, together with the ID, first name, and last name of the sales
-- rep who represents the customer
SELECT C.CUST_ID, C.FIRST_NAME, C.LAST_NAME, S.FIRST_NAME, S.LAST_NAME
FROM CUSTOMER C, SALES_REP S
WHERE S.REP_ID = C.REP_ID;
-- 34) List the ID, first name, and last name of each customer whose credit limit is $500, together with the ID,
-- first name, and last name of the sales rep who represents the customer.
SELECT C.CUST_ID, C.FIRST_NAME, C.LAST_NAME, S.FIRST_NAME, S.LAST_NAME
FROM CUSTOMER C, SALES_REP S
WHERE S.REP_ID = C.REP_ID
AND C.CREDIT_LIMIT = 500;
-- 35) For every item on an invoice, list the invoice number, item ID, description, quantity ordered, quoted prices
-- and unit price.
SELECT IL.INVOICE_NUM, IL.ITEM_ID, I.DESCRIPTION, IL.QUANTITY, IL.QUOTED_PRICE, I.PRICE
FROM ITEM I, INVOICE_LINE IL
WHERE I.ITEM_ID = IL.ITEM_ID;
-- 36) Find the description of each item included in invoice number 14233. Write following query using Join and IN operators.
SELECT I.DESCRIPTION
FROM ITEM I, INVOICE_LINE IL
WHERE I.ITEM_ID = IL.ITEM_ID
AND IL.INVOICE_NUM = '14233';
-- Variation
SELECT DESCRIPTION
FROM ITEM
WHERE ITEM_ID IN (SELECT ITEM_ID
FROM INVOICE_LINE
WHERE INVOICE_NUM = '14233');
-- 37) Find the invoice number and invoice date for each invoice that contains item ID KH81.
SELECT I.INVOICE_NUM, I.INVOICE_DATE
FROM INVOICES I
WHERE EXISTS (SELECT *
FROM INVOICE_LINE IL
WHERE I.INVOICE_NUM = IL.INVOICE_NUM
AND IL.ITEM_ID = 'KH81');
-- 38) Find the invoice number and invoice date for each invoice that includes an item stored in location C.
SELECT INVOICE_NUM, INVOICE_DATE
FROM INVOICES
WHERE INVOICE_NUM IN (SELECT INVOICE_NUM
FROM INVOICE_LINE
WHERE ITEM_ID IN (SELECT ITEM_ID
FROM ITEM
WHERE LOCATION = 'C'));
-- 39) List the customer ID, invoice number, invoice date, invoice total for each invoice with a total that exceeds $250.
-- Assign the column name INVOICE_TOTAL to the column that displays invoice totals. Order the results by invoice number.
SELECT I.CUST_ID, I.INVOICE_NUM, I.INVOICE_DATE, SUM(IL.QUANTITY * IL.QUOTED_PRICE)
FROM INVOICES I, INVOICE_LINE IL
WHERE I.INVOICE_NUM = IL.INVOICE_NUM
GROUP BY I.CUST_ID, I.INVOICE_NUM, I.INVOICE_DATE
HAVING SUM(IL.QUANTITY * IL.QUOTED_PRICE) > 250
ORDER BY I.INVOICE_NUM;
-- 40) List the ID, first name, and last name for each sales rep together with the ID, first name, and last name for each
-- customer the sales rep represents.
SELECT C.CUST_ID, C.FIRST_NAME, C.LAST_NAME, S.FIRST_NAME, S.LAST_NAME
FROM CUSTOMER C, SALES_REP S
WHERE S.REP_ID = C.REP_ID;
-- 41) For each pair of customers located in the same city, display the customer ID, first name, last name, and city.
SELECT F.CUST_ID, F.FIRST_NAME, F.LAST_NAME, S.CUST_ID, S.FIRST_NAME, S.LAST_NAME, F.CITY
FROM CUSTOMER F, CUSTOMER S
WHERE F.CITY = S.CITY
AND F.CUST_ID < S.CUST_ID
ORDER BY F.CITY;
-- 42) For each item on an invoice, list the item ID, quantity ordered, invoice number, invoice date, customer ID,
-- customer first name, and customer last name, along with the last name of the sales rep who represents each customer.
SELECT IL.ITEM_ID, IL.QUANTITY, IL.INVOICE_NUM, I.INVOICE_DATE, C.CUST_ID, C.FIRST_NAME, C.LAST_NAME,
SR.LAST_NAME
FROM SALES_REP SR, CUSTOMER C, INVOICES I, INVOICE_LINE IL
WHERE SR.REP_ID = C.REP_ID
AND C.CUST_ID = I.CUST_ID
AND I.INVOICE_NUM = IL.INVOICE_NUM
ORDER BY IL.ITEM_ID, IL.INVOICE_NUM;
-- 43) List the ID and names of each customer that is either represented by sales rep 10 or currently has invoices on
-- file, or both.
SELECT C.CUST_ID, C.FIRST_NAME, C.LAST_NAME
FROM CUSTOMER C, SALES_REP S
WHERE C.REP_ID = S.REP_ID
AND S.REP_ID = '10';
-- Variation
SELECT C.CUST_ID, C.FIRST_NAME, C.LAST_NAME
FROM CUSTOMER C
WHERE C.REP_ID = '10'
UNION
SELECT C.CUST_ID, C.FIRST_NAME, C.LAST_NAME
FROM CUSTOMER C, INVOICES I
WHERE C.CUST_ID = I.CUST_ID;
-- 44) Find the customer ID, full name, current balance, and sales rep ID of each customer whose balance exceeds the
-- maximum balance of all customers represented by sales rep 10.
SELECT CUST_ID, FIRST_NAME, LAST_NAME, BALANCE, REP_ID
FROM CUSTOMER
WHERE BALANCE > (SELECT MAX(BALANCE)
FROM CUSTOMER
WHERE REP_ID = 10);
-- Variation
SELECT CUST_ID, FIRST_NAME, LAST_NAME, BALANCE, REP_ID
FROM CUSTOMER
WHERE BALANCE > ALL (SELECT BALANCE
FROM CUSTOMER
WHERE REP_ID = 10);
-- 45) Find the customer ID, full name, current balance, and sales rep ID of each customer whose balance is greater
-- than the balance of at least one customer of sales rep 10.
SELECT CUST_ID, FIRST_NAME, LAST_NAME, BALANCE, REP_ID
FROM CUSTOMER
WHERE BALANCE > (SELECT MAX(BALANCE)
FROM CUSTOMER
WHERE REP_ID = 10);
-- Variation
SELECT CUST_ID, FIRST_NAME, LAST_NAME, BALANCE, REP_ID
FROM CUSTOMER
WHERE BALANCE > ALL (SELECT BALANCE
FROM CUSTOMER
WHERE REP_ID = 10);
-- 46) Display the ID, first name, and last name for each customer, along with the invoice number, invoice date for
-- each invoice. Sort the results by customer ID.
SELECT C.CUST_ID, C.FIRST_NAME, C.LAST_NAME, I.INVOICE_NUM, I.INVOICE_DATE
FROM CUSTOMER C, INVOICES I
WHERE C.CUST_ID = I.CUST_ID;
SELECT C.CUST_ID, C.FIRST_NAME, C.LAST_NAME, I.INVOICE_NUM, I.INVOICE_DATE
FROM CUSTOMER C INNER JOIN INVOICES I ON (C.CUST_ID = I.CUST_ID);
-- 47) Display the ID, first name, and last name for each customer, along with the invoice number, and invoice date for
-- all invoices. Include all customers in the results. For customers that do not have invoices, omit the invoice number
-- and invoice date.
SELECT C.CUST_ID, C.FIRST_NAME, C.LAST_NAME, I.INVOICE_NUM, I.INVOICE_DATE
FROM CUSTOMER C LEFT OUTER JOIN INVOICES I ON (C.CUST_ID = I.CUST_ID);
-- CUST AND INVOICE WERE CHANGED IN THE FROM FUNCTION AND IT WAS CHANGED LEFT TO RIGHT
SELECT C.CUST_ID, C.FIRST_NAME, C.LAST_NAME, I.INVOICE_NUM, I.INVOICE_DATE
FROM INVOICES I RIGHT OUTER JOIN CUSTOMER C ON (C.CUST_ID = I.CUST_ID);
-- 48) Form the product of the CUSTOMER and INVOICES tables. Display the customer ID, first name, and last name from the
-- CUSTOMER table, along with the invoice number and invoice date from the INVOICES table.
SELECT C.CUST_ID, C.FIRST_NAME, C.LAST_NAME, I.INVOICE_NUM, I.INVOICE_DATE
FROM INVOICES I, CUSTOMER C;
USE TIMKAY;
-- 49) Create a new table named LEVEL1_CUSTOMER that contain the following columns from the CUSTOMER table: CUST_ID,
-- FIRST_NAME, LAST_NAME, BALANCE, CREDIT_LIMIT, and REP_ID. The column in the new LEVEL1_CUSTOMER table should have the
-- same characteristics as the corresponding columns in the CUSTOMER table.
DROP TABLES IF EXISTS LEVEL1_CUSTOMER;
CREATE TABLE LEVEL1_CUSTOMER
(CUST_ID CHAR(3) PRIMARY KEY,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(20),
BALANCE DECIMAL(7,2),
CREDIT_LIMIT DECIMAL(7,2),
REP_ID CHAR(2)
);
-- 50) Insert into the LEVEL1_CUSTOMER table teh customer ID, first name, last name, balance, credit limit, and rep ID
-- for customers with credit limits of $500.
INSERT INTO LEVEL1_CUSTOMER
(SELECT CUST_ID, FIRST_NAME, LAST_NAME, BALANCE, CREDIT_LIMIT, REP_ID
FROM CUSTOMER
WHERE CREDIT_LIMIT = 500);
-- 51) Change the last name of customer 616 in the LEVEL1_CUSTOMER table to "Martinez."
UPDATE LEVEL1_CUSTOMER
SET LAST_NAME = "Martinez"
WHERE CUST_ID = '616';
-- 52) For each customer in the LEVEL1_CUSTOMER table that is represented by sales rep 15 and has a balance over $150,
-- increase the customer's credit limit to $550.
UPDATE LEVEL1_CUSTOMER
SET CREDIT_LIMIT = 550
WHERE REP_ID = '15'
AND BALANCE > 150;
-- 53) Add customer ID 837 to the LEVEL1_CUSTOMER table. The first name is Debbie, the last name is Thomas, the balance is
-- zero, the credit limit is $500, and the sales rep ID is 15.
INSERT INTO LEVEL1_CUSTOMER VALUES ('837', 'Debbie', 'Thomas', 0, 50, '15');
SELECT * FROM LEVEL1_CUSTOMER;
-- 54) In the LEVEL1_CUSTOMER table, change the last name of customer 294 to "Jones," and then delete customer 795.
UPDATE LEVEL1_CUSTOMER
SET LAST_NAME = "Jones"
WHERE CUST_ID = '294';
DELETE FROM LEVEL1_CUSTOMER
WHERE CUST_ID = '795';
SELECT * FROM LEVEL1_CUSTOMER;
-- 55) Execute a rollback and then display the data in the LEVEL1_CUSTOMER table.
ROLLBACK;
SELECT * FROM LEVEL1_CUSTOMER;
COMMIT;
SELECT * FROM LEVEL1_CUSTOMER;
-- 56) Change the balance of customer 616 in the LEVEL1_CUSTOMER table to null.
UPDATE LEVEL1_CUSTOMER
SET BALANCE = null
WHERE CUST_ID = '616';
-- 57) KimTay Pet Supplies decides to maintain a customer type for each customer in the database. These tupes are R for
-- regular customers, D for distributors, and S for special customers. Add this information in a new column named
-- CUST_TYPE in the LEVEL1_CUSTOMER table. Issue DESCRIBE command to ensure above change. Then update LEVEL1_CUSTOMER
-- table to set
-- CUST_TYPE to "R" for all records.
ALTER TABLE LEVEL1_CUSTOMER
ADD CUST_TYPE CHAR(1);
DESCRIBE LEVEL1_CUSTOMER;
UPDATE LEVEL1_CUSTOMER
SET CUST_TYPE = 'R';
-- 58) Two customers in the LEVEL1_CUSTOMER table have a type other than R. Change the tupes for customers 227 and 492 to
-- S and D, respectively. Issue SELECT command to ensure above changes.
UPDATE LEVEL1_CUSTOMER
SET CUST_TYPE = 'S'
WHERE CUST_ID = 227;
UPDATE LEVEL1_CUSTOMER
SET CUST_TYPE = 'D'
WHERE CUST_ID = 492;
-- 59) The length of the LAST_NAME column in the LEVEL1_CUSTOMER table is too short. Increase its length to 30 characters.
-- In addition, change the CREDIT_LIMIT column so it cannot accept nulls. Issue DESCRIBE command to ensure above change.
DESCRIBE LEVEL1_CUSTOMER;
SELECT * FROM LEVEL1_CUSTOMER;
ALTER TABLE LEVEL1_CUSTOMER
MODIFY LAST_NAME VARCHAR(30);
ALTER TABLE LEVEL1_CUSTOMER
MODIFY CREDIT_LIMIT DECIMAL(7,2) NOT NULL;
-- 60) Delete the LEVEL1_CUSTOMER table because it is no longer needed in the KimTay Pet Supplies database.
DROP TABLE LEVEL1_CUSTOMER;
COMMIT;
-- 61) Create a view named DOGS that consists of the item ID, description, units on hand, and unit price of each
-- item in category DOG.
SELECT * FROM ITEM;
CREATE VIEW DOGS AS
(SELECT ITEM_ID, DESCRIPTION, ON_HAND, PRICE
FROM ITEM
WHERE CATEGORY = 'DOG');
SELECT * FROM DOGS;
-- 62) Create a view named DGS that consists of the item ID, description, units on hand, and unit price of
-- all items in category DOG. In this view, change the names of the ITEM_ID, DESCRIPTION, ON_HAND, and PRICE
-- columns ot ID, DSC, OH, and PRCE, respectively.
CREATE VIEW DGS (ID, DSC, OH, PRCE) AS
(SELECT ITEM_ID, DESCRIPTION, ON_HAND, PRICE
FROM ITEM
WHERE CATEGORY = 'DOG');
SELECT * FROM DGS;
-- 63) Create a view named REP_CUST consisting of the sales rep ID (named RID), sales rep first name (named RFIRST),
-- sales rep last name (named RLAST), customer ID (named CID), customer first name (named CFIRST), and customer last
-- name (named CLAST), for all sales reps and matching customers in the SALES_REP and CUSTOMER tables. Sort the
-- records by rep ID and customer ID.
CREATE VIEW REP_CUST (RID, RFIRST, RLAST, CID, CFIRST, CLAST) AS
(SELECT S.REP_ID, S.FIRST_NAME, S.LAST_NAME, C.CUST_ID, C.FIRST_NAME, C.LAST_NAME
FROM SALES_REP S INNER JOIN CUSTOMER C ON (S.REP_ID = C.REP_ID)
ORDER BY S.REP_ID, C.CUST_ID);
SELECT * FROM REP_CUST;
SELECT RFIRST, RLAST, CFIRST, CLAST
FROM REP_CUST;
-- 64) Create a view named CRED_CUST that consists of each credit limit (CREDIT_LIMIT) and the number of customers
-- having this credit limit (NUM_CUSTOMERS). Sort the credit limits in the ascending order.
CREATE OR REPLACE VIEW CRED_CUST(CREDIT_LIMIT, NUM_CUSTOMERS) AS
(SELECT CREDIT_LIMIT, COUNT(*)
FROM CUSTOMER
GROUP BY CREDIT_LIMIT
ORDER BY CREDIT_LIMIT DESC);
SELECT * FROM CRED_CUST;
-- 65) The DGS view is no longer necessary, so delete it.
Drop view DGS;
-- 66) Create an index named BALIND on the BALANCE column in the CUSTOMER table.
CREATE INDEX BALIND ON CUSTOMER (BALANCE);
-- 67) Create an index named REP_NAME on the combination of the LAST_NAME andFIRST_NAME columns in the
-- SALES_REP table.
CREATE INDEX REP_NAME ON SALES_REP(LAST_NAME, FIRST_NAME);
-- 68) Create an index named CRED_LASTNAME on the combination of the CREDIT_LIMITand LAST_NAME columns in
-- the CUSTOMER table, with the credit limit listed in descending order.
CREATE INDEX CRED_LASTNAME ON CUSTOMER (CREDIT_LIMIT DESC, LAST_NAME);
-- 69) Drop index CRED_LASTNAME as it is not needed anymore.
DROP INDEX CRED_LASTNAME ON CUSTOMER;
Staywell Database
CREATE DATABASE IF NOT EXISTS STAYWELL;
USE STAYWELL;
/* CREATE TABLE OFFICE_TABLE*/
DROP TABLE IF EXISTS OFFICE;
CREATE TABLE OFFICE
(
OFFICE_NUM CHAR(2) PRIMARY KEY,
OFFICE_NAME VARCHAR(50),
ADDRESS VARCHAR(20),
AREA VARCHAR(20),
CITY VARCHAR(15),
STATE CHAR(2),
ZIP_CODE CHAR(5)
);
insert into OFFICE values('1','StayWell-Columbia City','1135 N. Wells Avenue','Columbia City','Seattle','WA','98118');
insert into OFFICE values('2','StayWell-Georgetown','986 S. Madison Rd','Georgetown','Seattle','WA','98108');
/* CREATE TABLE OWNER*/
DROP TABLE IF EXISTS OWNER;
CREATE TABLE OWNER
(
OWNER_NUM CHAR(5) PRIMARY KEY,
LAST_NAME VARCHAR(20),
FIRST_NAME VARCHAR(20),
ADDRESS VARCHAR(25),
CITY VARCHAR(15),
STATE CHAR(2),
ZIP_CODE CHAR(5)
);
insert into OWNER values('MO100','Moore','Elle-May','8006 W. Newport Ave.','Reno','NV','89508');
insert into OWNER values('PA101','Patel','Makesh','7337 Sheffield St.','Seattle','WA','98119');
insert into OWNER values('AK102','Aksoy','Ceyda','411 Griffin Rd.','Seattle','WA','98131');
insert into OWNER values('CO103','Cole','Meerab','9486 Circle Ave.','Olympia','WA','98506');
insert into OWNER values('KO104','Kowalczyk','Jakub','7431 S. Bishop St.','Bellingham','WA','98226');
insert into OWNER values('SI105','Sims','Haydon','527 Primrose Rd.','Portland','OR','97203');
insert into OWNER values('BU106','Burke','Ernest','613 Old Plaeant St.','Twin Falls','ID','83303');
insert into OWNER values('RE107','Redman','Seth','7681 Fordham St.','Seattle','WA','98119');
insert into OWNER values('LO108','Lopez','Janie','9856 Pumpkin Hill Ln.','Everett','WA','98213');
insert into OWNER values('BI109','Bianchi','Nicole','7990 Willow Dr.','New York','NY','10005');
insert into OWNER values('JO110','Jones','Ammarah','730 Military Ave.','Seattle','WA','98126');
/* CREATE TABLE PROPERTY*/
DROP TABLE IF EXISTS PROPERTY;
CREATE TABLE PROPERTY
(
PROPERTY_ID CHAR(3) PRIMARY KEY,
OFFICE_NUM CHAR(2),
ADDRESS VARCHAR(30),
SQR_FT NUMERIC,
BDRMS VARCHAR(2),
FLOORS CHAR(1),
MONTHLY_RENT NUMERIC,
OWNER_NUM CHAR(5)
);
insert into PROPERTY values('1','1','30 West Thomas Rd.',1600,'3','1',1400,'BU106');
insert into PROPERTY values('2','1','782 Queen Ln.',2100,'4','2',1900,'AK102');
insert into PROPERTY values('3','1','9800 Sunbeam Ave.',1005,'2','1',1200,'BI109');
insert into PROPERTY values('4','1','105 North Illinois Rd.',1750,'3','1',1650,'KO104');
insert into PROPERTY values('5','1','887 Vine Rd.',1125,'2','1',1160,'SI105');
insert into PROPERTY values('6','1','8 Laurel Dr.',2125,'4','2',2050,'MO100');
insert into PROPERTY values('7','2','447 Goldfield St.',1675,'3','2',1700,'CO103');
insert into PROPERTY values('8','2','594 Leatherwood Dr.',2700,'5','2',2750,'KO104');
insert into PROPERTY values('9','2','504 Windsor Ave.',700,'2','1',1050,'PA101');
insert into PROPERTY values('10','2','891 Alton Dr.',1300,'3','1',1600,'LO108');
insert into PROPERTY values('11','2','9531 Sherwood Rd.',1075,'2','1',1100,'JO110');
insert into PROPERTY values('12','2','2 Bow Ridge Ave.',1400,'3','2',1700,'RE107');
/* CREATE TABLE RESIDENTS_TABLE*/
DROP TABLE IF EXISTS RESIDENTS;
CREATE TABLE RESIDENTS
(
RESIDENT_ID CHAR(3) PRIMARY KEY,
FIRST_NAME VARCHAR(20),
SURNAME VARCHAR(20),
PROPERTY_ID CHAR(3)
);
insert into RESIDENTS values('1','Albie','O’Ryan','1');
insert into RESIDENTS values('2','Tariq','Khan','1');
insert into RESIDENTS values('3','Ismail','Salib','1');
insert into RESIDENTS values('4','Callen','Beck','2');
insert into RESIDENTS values('5','Milosz','Polansky','2');
insert into RESIDENTS values('6','Ashanti','Lucas','2');
insert into RESIDENTS values('7','Randy','Woodrue','2');
insert into RESIDENTS values('8','Aislinn','Lawrence','3');
insert into RESIDENTS values('9','Monique','French','3');
insert into RESIDENTS values('10','Amara','Dejsuwan','4');
insert into RESIDENTS values('12','Rosalie','Blackmore','4');
insert into RESIDENTS values('13','Carina','Britton','4');
insert into RESIDENTS values('14','Valentino','Ortega','5');
insert into RESIDENTS values('15','Kaylem','Kent','5');
insert into RESIDENTS values('16','Alessia','Wagner','6');
insert into RESIDENTS values('17','Tyrone','Galvan','6');
insert into RESIDENTS values('18','Constance','Fleming','6');
insert into RESIDENTS values('19','Eamonn','Bain','6');
insert into RESIDENTS values('20','Misbah','Yacob','7');
insert into RESIDENTS values('21','Gianluca','Esposito','7');
insert into RESIDENTS values('22','Elinor','Lake','7');
insert into RESIDENTS values('23','Ray','Rosas','8');
insert into RESIDENTS values('24','Damon','Caldwell','8');
insert into RESIDENTS values('25','Dawood','Busby','8');
insert into RESIDENTS values('26','Dora','Harris','8');
insert into RESIDENTS values('27','Leroy','Stokes','8');
insert into RESIDENTS values('28','Tamia','Hess','9');
insert into RESIDENTS values('29','Amelia','Sanders','9');
insert into RESIDENTS values('30','Zarah','Byers','10');
insert into RESIDENTS values('31','Sara','Farrow','10');
insert into RESIDENTS values('32','Delilah','Roy','10');
insert into RESIDENTS values('33','Dougie','McDaniel','11');
insert into RESIDENTS values('34','Tahir','Halabi','11');
insert into RESIDENTS values('35','Mila','Zhikin','12');
insert into RESIDENTS values('36','Glenn','Donovan','12');
insert into RESIDENTS values('37','Zayn','Fowler','12');
/* CREATE TABLE RESIDENTS_TABLE*/
DROP TABLE IF EXISTS SERVICE_CATEGORY;
CREATE TABLE SERVICE_CATEGORY
(
CATEGORY_NUM CHAR(3) PRIMARY KEY,
CATEGORY_DESCRIPTION VARCHAR(25)
);
insert into SERVICE_CATEGORY values('1','Plumbing');
insert into SERVICE_CATEGORY values('2','Heating');
insert into SERVICE_CATEGORY values('3','Painting');
insert into SERVICE_CATEGORY values('4','Electrical systems');
insert into SERVICE_CATEGORY values('5','Carpentry');
insert into SERVICE_CATEGORY values('6','Furniture replacement');
/* CREATE TABLE RESIDENTS_TABLE*/
DROP TABLE IF EXISTS SERVICE_REQUEST;
CREATE TABLE SERVICE_REQUEST
(
SERVICE_ID CHAR(3) PRIMARY KEY,
PROPERTY_ID CHAR(3),
CATEGORY_NUM CHAR(3),
OFFICE_NUM CHAR(2),
DESCRIPTION VARCHAR(200),
STATUS VARCHAR(100),
EST_HOURS CHAR(2),
SPENT_HOURS CHAR(2),
NEXT_SERVICE_DATE DATE DEFAULT NULL
);
insert into SERVICE_REQUEST values('1','11','2','2',"The second bedroom upstairs is not heating up at night.","Problem has been confirmed. Central heating engineer has been scheduled.",'2','1',11/01/2019);
insert into SERVICE_REQUEST values('2','1','4','1','A new strip light is needed for the kitchen.','Scheduled','1','0',10/02/2019);
insert into SERVICE_REQUEST values('3','6','5','1','The bathroom door does not close properly.','Service rep has confirmed issue. Scheduled to be refitted.','3','1',11/09/2019);
insert into SERVICE_REQUEST values('4','2','4','1','New outlet has been requested for the first upstairs bedroom. (There is currently no outlet).','Scheduled','1','0',10/02/2019);
insert into SERVICE_REQUEST values('5','8','3','2','New paint job requested for the common area (lounge).','Open','10','0',NULL);
insert into SERVICE_REQUEST values('6','4','1','1','Shower is dripping when not in use. ','Problem confirmed. Plumber has been scheduled.','4','2',10/07/2019);
insert into SERVICE_REQUEST values('7','2','2','1','Heating unit in the entrance smells like it’s burning.','Service rep confirmed the issue to be dust in the heating unit. To be cleaned.','1','0',10/09/2019);
insert into SERVICE_REQUEST values('8','9','1','2','Kitchen sink does not drain properly.','Problem confirmed. Plumber scheduled. ','6','2',11/12/2019);
insert into SERVICE_REQUEST values('9','12','6','2','New sofa requested. ','Open','2','0',NULL);
COMMIT;
-- 1. List the owner number, last name, and first name of every property owner.
SELECT OWNER_NUM, LAST_NAME, FIRST_NAME
FROM OWNER;
-- 2. List the complete PROPERTY table (all rows and all columns).
SELECT *
FROM PROPERTY;
-- 3.List the last name and first name of every owner who lives in Seattle.
SELECT LAST_NAME, FIRST_NAME
FROM OWNER
WHERE CITY = 'SEATTLE';
-- 4. List the last name and first name of every owner who does not live in Seattle.
SELECT LAST_NAME, FIRST_NAME
FROM OWNER
WHERE NOT CITY = 'SEATTLE';
-- 5. List the property ID and office number for every property whose square footage is equal to or less than 1,400
-- square feet.
SELECT PROPERTY_ID, OFFICE_NUM
FROM PROPERTY
WHERE SQR_FT <= 1400;
-- 6. List the office number and address for every property with three bedrooms.
SELECT OFFICE_NUM, ADDRESS
FROM PROPERTY
WHERE BDRMS = 3;
-- 7. List the property ID for every property with two bedrooms that is managed by StayWell-Georgetown.
SELECT PROPERTY_ID
FROM PROPERTY
WHERE BDRMS = 2
AND OFFICE_NUM = 2;
-- 8. List the property ID for every property with a monthly rent that is between $1,350 and $1,750.
SELECT PROPERTY_ID
FROM PROPERTY
WHERE MONTHLY_RENT > 1350
AND MONTHLY_RENT < 1750;
-- 9. List the property ID for every property managed by StayWell-Columbia City whose monthly rent is less than $1,500.
SELECT PROPERTY_ID
FROM PROPERTY
WHERE OFFICE_NUM = 1
AND MONTHLY_RENT < 1500;
-- 10. Labor is billed at the rate of $35 per hour. List the property ID, category number, estimated hours, and
-- estimated labor cost for every service request. To obtain the estimated labor cost, multiply the estimated hours
-- by 35. Use the column name ESTIMATED_COST for the estimated labor cost.
SELECT PROPERTY_ID, CATEGORY_NUM, EST_HOURS, (EST_HOURS * 35) AS ESTIMATED_COST
FROM SERVICE_REQUEST;
-- 11. List the owner number and last name for all owners who live in Nevada (NV), Oregon (OR), or Idaho (ID).
SELECT OWNER_NUM, LAST_NAME
FROM OWNER
WHERE STATE IN ('NV', 'OR', 'ID');
-- 12. List the office number, property ID, square footage, and monthly rent for all properties. Sort the results
-- by monthly rent within the square footage.
SELECT PROPERTY_ID, SQR_FT, MONTHLY_RENT
FROM PROPERTY
ORDER BY SQR_FT, MONTHLY_RENT;
-- 13. How many three-bedroom properties are managed by each office?
SELECT OFFICE_NUM, COUNT(*)
FROM PROPERTY
WHERE BDRMS = 3
GROUP BY OFFICE_NUM;
-- 14.Calculate the total value of monthly rents for all properties
SELECT SUM(MONTHLY_RENT)
FROM PROPERTY;
-- 15. For every property, list the management office number, address, monthly rent, owner num-ber, owner’s first name,
-- and owner’s last name.
SELECT P.OFFICE_NUM, P.ADDRESS, P.MONTHLY_RENT, P.OWNER_NUM, O.FIRST_NAME, O.LAST_NAME
FROM PROPERTY P, OWNER O
WHERE P.OWNER_NUM = O.OWNER_NUM;
-- 16. For every completed or open service request, list the property ID, description, and status.
SELECT PROPERTY_ID, DESCRIPTION, STATUS
FROM SERVICE_REQUEST;
-- 17. For every service request for furniture replacement, list the property ID, management office number, address,
-- estimated hours, spent hours, owner number, and owner’s last name.
SELECT R.PROPERTY_ID, R.OFFICE_NUM, P.ADDRESS, R.EST_HOURS, R.SPENT_HOURS, P.OWNER_NUM, O.LAST_NAME
FROM SERVICE_REQUEST R, PROPERTY P, OWNER O
WHERE R.PROPERTY_ID = P.PROPERTY_ID
AND P.OWNER_NUM = O.OWNER_NUM
AND CATEGORY_NUM = 6;
-- 18. List the first and last names of all owners who own a two-bedroom property. Use the IN operator in your query.
SELECT FIRST_NAME, LAST_NAME
FROM OWNER
WHERE OWNER_NUM IN (SELECT OWNER_NUM
FROM PROPERTY
WHERE BDRMS = 2);
-- 19. Repeat Exercise 4, but this time use the EXISTS operator in your query.
SELECT O.FIRST_NAME, O.LAST_NAME
FROM OWNER O
WHERE Exists (SELECT *
FROM PROPERTY P
WHERE P.OWNER_NUM = O.OWNER_NUM
AND P.BDRMS = 2);
-- 20. List the property IDs of any pair of properties that have the same number of bedrooms. For example,
-- one pair would be property ID 2 and property ID 6, because they both have four bedrooms. The first property
-- ID listed should be the major sort key and the second prop-erty ID should be the minor sort key.
SELECT P.PROPERTY_ID, B.PROPERTY_ID
FROM PROPERTY P, PROPERTY B
WHERE P.BDRMS = B.BDRMS
AND P.PROPERTY_ID < B.PROPERTY_ID
ORDER BY P.BDRMS;
-- 21. List the square footage, owner number, owner last name, and owner first name for each property managed
-- by the Columbia City office.
SELECT P.SQR_FT, P.OWNER_NUM, O.LAST_NAME, O.FIRST_NAME
FROM PROPERTY P, OWNER O
WHERE P.OWNER_NUM = O.OWNER_NUM
AND P.OFFICE_NUM = 1;
-- 22. Repeat Exercise 7, but this time include only those properties with three bedrooms.
SELECT P.BDRMS, P.SQR_FT, P.OWNER_NUM, O.LAST_NAME, O.FIRST_NAME
FROM PROPERTY P, OWNER O
WHERE P.OWNER_NUM = O.OWNER_NUM
AND P.OFFICE_NUM = 1
AND P.BDRMS = 3;
-- 23. List the office number, address, and monthly rent for properties whose owners live in Washington state
-- and own a two-bedroom property.
SELECT P.OFFICE_NUM, P.ADDRESS, P.MONTHLY_RENT
FROM PROPERTY P, OWNER O
WHERE P.OWNER_NUM = O.OWNER_NUM
AND O.STATE = "WA"
AND P.BDRMS = 2;
-- Variation
SELECT OFFICE_NUM, ADDRESS, MONTHLY_RENT
FROM PROPERTY
WHERE BDRMS = 2
AND OWNER_NUM IN (SELECT OWNER_NUM
FROM OWNER
WHERE STATE = "WA");
-- 24. List the office number, address, and monthly rent for properties whose owners live in Washington state
-- but do not own two-bedroom properties.
SELECT P.OFFICE_NUM, P.ADDRESS, P.MONTHLY_RENT
FROM PROPERTY P, OWNER O
WHERE P.OWNER_NUM = O.OWNER_NUM
AND O.STATE = "WA"
AND NOT P.BDRMS = 2;
-- 25. Find the service ID and property ID for each service request whose estimated hours are greater than the
-- number of estimated hours of at least one service request on which the category number is 5.
SELECT SERVICE_ID, PROPERTY_ID
FROM SERVICE_REQUEST
WHERE EST_HOURS > ANY (SELECT EST_HOURS
FROM SERVICE_REQUEST
WHERE CATEGORY_NUM = 5);
-- Variation
SELECT SERVICE_ID, PROPERTY_ID
FROM SERVICE_REQUEST
WHERE EST_HOURS > (SELECT MIN(EST_HOURS)
FROM SERVICE_REQUEST
WHERE CATEGORY_NUM = 5);
-- 26. Find the service ID and property ID for each service request whose estimated hours are greater than the
-- number of estimated hours on every service request on which the cat-egory number is 5.
SELECT SERVICE_ID, PROPERTY_ID
FROM SERVICE_REQUEST
WHERE EST_HOURS > (SELECT SUM(EST_HOURS)
FROM SERVICE_REQUEST
WHERE CATEGORY_NUM = 5);
-- 27. List the address, square footage, owner number, service ID, number of estimated hours, and number of spent
-- hours for each service request on which the category number is 4.
SELECT P.ADDRESS, P.SQR_FT, P.OWNER_NUM, S.SERVICE_ID, S.EST_HOURS, S.SPENT_HOURS
FROM SERVICE_REQUEST S, PROPERTY P
WHERE S.PROPERTY_ID = P.PROPERTY_ID
AND S.CATEGORY_NUM = 4;
-- 28. Repeat Exercise 14, but this time be sure each property is included regardless of whether the property
-- currently has any service requests for category 4.
SELECT P.ADDRESS, P.SQR_FT, P.OWNER_NUM, S.SERVICE_ID, S.EST_HOURS, S.SPENT_HOURS
FROM SERVICE_REQUEST S, PROPERTY P
WHERE S.PROPERTY_ID = P.PROPERTY_ID;
-- 29. Repeat Exercise 15 using a different SQL command to obtain the same result.
-- What is the difference between the two commands?
SELECT P.ADDRESS, P.SQR_FT, P.OWNER_NUM, S.SERVICE_ID, S.EST_HOURS, S.SPENT_HOURS
FROM SERVICE_REQUEST S INNER JOIN PROPERTY P ON (S.PROPERTY_ID = P.PROPERTY_ID);
-- 30. Create a LARGE_PROPERTY table.
DROP TABLE IF EXISTS LARGE_PROPERTY;
CREATE TABLE LARGE_PROPERTY
(
OFFICE_NUM DECIMAL(2,0) NOT NULL,
ADDRESS CHAR(25) NOT NULL,
BDRMS DECIMAL (2,0),
FLOORS DECIMAL(2,0),
MONTHLY_RENT DECIMAL(6,2),
OWNER_NUM CHAR(5)
);
-- 31. Insert into the LARGE_PROPERTY table the office number, address, bedrooms, baths, monthly rent,
-- and owner number for those properties whose square footage is greater than 1,500 square feet.
INSERT INTO LARGE_PROPERTY
(SELECT OFFICE_NUM, ADDRESS, BDRMS, FLOORS, MONTHLY_RENT, OWNER_NUM
FROM PROPERTY
WHERE SQR_FT > '1500');
-- 32. StayWell has increased the monthly rent of each large property by $150. Update the monthly
-- rents in the LARGE_PROPERTY table accordingly.
UPDATE LARGE_PROPERTY
SET MONTHLY_RENT = MONTHLY_RENT + 150;
-- 33. After increasing the monthly rent of each large property by $150 (Exercise 3), StayWell decides
-- to decrease the monthly rent of any property whose monthly fee is more than $1750 by 1 percent. Update
-- the monthly rents in the LARGE_PROPERTY table accordingly.
UPDATE LARGE_PROPERTY
SET MONTHLY_RENT = MONTHLY_RENT - (MONTHLY_RENT*.01)
WHERE MONTHLY_RENT > 1750;
-- 34. Insert a row into the LARGE_PROPERTY table for a new property. The office number is 1, the address
-- is 2643 Lugsi Dr, the number of bedrooms is 3, the number of floors is 2, the monthly rent is $775, and
-- the owner number is MA111.
INSERT INTO LARGE_PROPERTY VALUES ('1', '2643 Lugsi Dr', '3', '2', 775, 'MA111');
-- 35. Delete all properties in the LARGE_PROPERTY table for which the owner number is BI109.
DELETE FROM LARGE_PROPERTY
WHERE OWNER_NUM = 'BI109';
COMMIT;
-- 36. The property in managed by Columbia City with the address 105 North Illinois Rd is in the
-- process of being remodeled and the number of bedrooms is unknown. Change the bedrooms value in
-- the LARGE_PROPERTY table to null.
UPDATE LARGE_PROPERTY
SET BDRMS = null
WHERE ADDRESS LIKE '%NORTH%'
AND OFFICE_NUM = 1;
-- 37. Add to the LARGE_PROPERTY table a new character column named OCCUPIED that is one character
-- in length. (This column indicates whether the property is currently occupied.) Set the value for
-- the OCCUPIED column on all rows to Y.
ALTER TABLE LARGE_PROPERTY
ADD OCCUPIED CHAR(1);
DESCRIBE LARGE_PROPERTY;
UPDATE LARGE_PROPERTY
SET OCCUPIED = 'Y';
SELECT * FROM LARGE_PROPERTY;
COMMIT;
-- 38. Change the MONTHLY_RENT column in the LARGE_PROPERTY table to reject nulls.
ALTER TABLE LARGE_PROPERTY
MODIFY MONTHLY_RENT DECIMAL (6,2) NOT NULL;
DESCRIBE LARGE_PROPERTY;
-- 39. Delete the LARGE_PROPERTY table from the database.
DROP TABLE LARGE_PROPERTY;
COMMIT;
Quinton Carter Portfolio 