SQL QUERIES

1. Display all sale orders that were paid with split payments of a card and a check

2. display highest servc price and the amount of sales containing it

3. display client first and last name and sale cost for the highest sale order

4, SELECT SALE ORDERS CONSISTING OF BOTH PRODUCTS AND SERVICES

CREATE DATABASE EM

USE EM

CREATE TABLE STORE (

STORE_ID INTEGER PRIMARY KEY,

STORE_ADDRESS VARCHAR(35) NOT NULL,

STORE_CITY VARCHAR(35) NOT NULL,

STORE_STATE CHAR(2) NOT NULL,

STORE_ZIP CHAR(5) NOT NULL,

STORE_PHONE CHAR(10) NOT NULL);

--INSERT DATA INTO STORE TABLE

INSERT INTO STORE VALUES(101,'34590 CLEVELAND DRIVE','OAKLAND','MI','48306','2488872340');

INSERT INTO STORE VALUES(102,'23456 MAIN STREET','OAKLAND','MI','48084','2487123099');

INSERT INTO STORE VALUES(103,'124 INDUSTRIAL DRIVE','TROY', 'MI','48083','2487661230');

INSERT INTO STORE VALUES(104,'34190 VAN DYKE','UTICA', 'MI','48044','5867132255');

INSERT INTO STORE VALUES(105,'555 KILMER DRIVE','OAKLAND','MI','48306','2480091221');

INSERT INTO STORE VALUES(106,'48910 HILL DRIVE','TROY','MI','48083','2488546711');

INSERT INTO STORE VALUES(107,'90 NICHOLAS ROAD','MACOMB','MI','48044','5869001200');

INSERT INTO STORE VALUES(108,'7500 WILLIAM DRIVE','MACOMB','MI','48044','5868760123');

INSERT INTO STORE VALUES(109,'8085 FREE DRIVE','DETROIT','MI','48183','3132226790');

INSERT INTO STORE VALUES(110,'7845 PARK AVENUE','BLOOMFIELD HILLS','MI','48127','2480114488');

CREATE TABLE PRODUCT (

PRODUCT_ID INTEGER PRIMARY KEY,

PRODUCT_NAME VARCHAR(35) NOT NULL,

PRODUCT_TYPE VARCHAR(35) NOT NULL,

PRODUCT_COST VARCHAR(10) NOT NULL);

--INSERT DATA INTO PRODUCT

INSERT INTO PRODUCT VALUES(196,'SHAMPOO','HAIR CARE','13.00');

INSERT INTO PRODUCT VALUES(842,'NAIL POLISH','NAILS','7.00');

INSERT INTO PRODUCT VALUES(091,'GUCCI COLOGNE','MENS FRAGRANCE','80.00');

INSERT INTO PRODUCT VALUES(856,'SHAMPOO + CONDITIONER','HAIR CARE','17.00');

INSERT INTO PRODUCT VALUES(344,'BODY LOTION','SKIN CARE','12.00');

CREATE TABLE BEAUTICIAN (

BEAUTICIAN_ID INTEGER PRIMARY KEY,

STORE_ID INTEGER NOT NULL,

BEAUTICIAN_FNAME VARCHAR(35) NOT NULL,

BEAUTUCIAN_LNAME VARCHAR(35) NOT NULL,

BEAUTICIAN_PHONE CHAR(10) NOT NULL,

BEAUTICIAN_ADDRESS VARCHAR(35) NOT NULL,

BEAUTICIAN_CITY VARCHAR(35) NOT NULL,

BEAUTICIAN_STATE CHAR(2) NOT NULL,

BEAUTICIAN_ZIP CHAR(5) NOT NULL,

BEAUTICIAN_HIRE_DATE DATE NOT NULL,

FOREIGN KEY (STORE_ID) REFERENCES STORE);

--INSERT DATA INTO BEAUTICIAN

INSERT INTO BEAUTICIAN VALUES(345,101,'BETH','MARKS','2489009001','500 LILLY DRIVE','OAKLAND','MI','48306','10-OCT-2015');

INSERT INTO BEAUTICIAN VALUES(791,105,'MARTHA','YORK','2483211934','900 HAVERFORD DRIVE','OAKLAND','MI','48306','25-JUN-2017');

INSERT INTO BEAUTICIAN VALUES(993,103,'NATALIE','JONES','2489567123','1278 WATTLES','TROY','MI','48083','09-NOV-2012');

INSERT INTO BEAUTICIAN VALUES(779,104,'MARK','FRITZ','5860983321','731 MAIN','UTICA','MI','48044','15-JAN-2014');

INSERT INTO BEAUTICIAN VALUES(297,109,'BILL','MATZ','3135568431','900 WOODWARD','DETROIT','MI','48183','30-MAR-2017');

INSERT INTO BEAUTICIAN VALUES(588,102,'PAM','EDDY','2487890123','670 MAIN','OAKLAND','MI','48306','12-JUN-2016');

INSERT INTO BEAUTICIAN VALUES(199,104,'MICKEY','FRED','5868677770','1245 KITMER DRIVE','UTICA','MI','48044','17-JUL-2016');

INSERT INTO BEAUTICIAN VALUES(389,106,'JIM','POLLARD','2483457721','2360 CROOKS','TROY','MI','48083','14-SEP-2014');

INSERT INTO BEAUTICIAN VALUES(885,107,'KATIE','BOZA','5866661400','5690 FAIRWAY DRIVE','UTICA','MI','48044','01-MAR-2014');

INSERT INTO BEAUTICIAN VALUES(590,108,'MICHAEL','BROWN','5866855831','1039 HAYES ROAD','WEST BLOOMFIELD','MI','48323','24-APR-2017');

INSERT INTO BEAUTICIAN VALUES(945,110,'ASHTON','COOPER','2487651072','1340 LANGLEY DRIVE','DETROIT','MI','48127','19-AUG-2015');

CREATE TABLE CLIENT (

CLIENT_ID INTEGER PRIMARY KEY,

CLIENT_FNAME VARCHAR(15) NOT NULL,

CLIENT_LNAME VARCHAR(15) NOT NULL,

CLIENT_PHONE CHAR(10) NOT NULL,

CLIENT_ADDRESS VARCHAR(35) NOT NULL,

CLIENT_CITY VARCHAR(20) NOT NULL,

CLIENT_STATE CHAR(2) NOT NULL,

CLIENT_ZIP CHAR(5) NOT NULL,

CLIENT_TYPE VARCHAR(1) NOT NULL);

--INSERT DATA INTO CLIENT TABLE

INSERT INTO CLIENT VALUES(5425,'Mark','Cuban','2488216615','5425 Pontiac Trl','West Bloomfield','MI','48323','M');

INSERT INTO CLIENT VALUES(5323,'Michelle','Martinez','2483422997','4429 Amenia Dr','Bloomfield Hills','MI','48303','M');

INSERT INTO CLIENT VALUES(8849,'Larry','Green','3132722555','8849 Marion','Redford','MI','48239','M');

INSERT INTO CLIENT VALUES(9120,'Kimberly','Young','2486327743','536 Knight','Troy','MI','48007','M');

INSERT INTO CLIENT VALUES(7443,'Pamela','Scott','3138023712','52342 Stratford','Macomb','MI','48042','M');

INSERT INTO CLIENT VALUES(1947,'Elizabeth','Jones','3133849937','1101 N Lafeyette','Oakland','MI','48306','N');

INSERT INTO CLIENT VALUES(1017,'Dawn','So','2483529934','732 Pioneer','Walled Lake','MI','48390','N');

INSERT INTO CLIENT VALUES(0932,'Kevin','Robinson','3134685821','16044 Trinity St','Detroit','MI','48127','N');

INSERT INTO CLIENT VALUES(7888,'Larry','Crawford','3138779002','410 Duck Ln','Walled Lake','MI','48392','N');

INSERT INTO CLIENT VALUES(3543,'Jake','Evans','2482218940','643 Union Lake Rd','Commerce','MI','48382','N');

CREATE TABLE APPOINTMENT (

APPOINTMENT_ID INTEGER PRIMARY KEY,

BEAUTICIAN_ID INTEGER NOT NULL,

CLIENT_ID INTEGER NOT NULL,

APPOINTMENT_DATE DATETIME NOT NULL,

APPOINTMENT_TIME TIME NOT NULL,

FOREIGN KEY (BEAUTICIAN_ID) REFERENCES BEAUTICIAN,

FOREIGN KEY (CLIENT_ID) REFERENCES CLIENT);

--INSERT DATA INTO APPOINTMENT TABLE

INSERT INTO APPOINTMENT VALUES(10,345,5425,'11-JAN-2018','10:00AM');

INSERT INTO APPOINTMENT VALUES(20,791,5323,'23-JAN-2018','2:15PM');

INSERT INTO APPOINTMENT VALUES(30,791,7443,'23-JAN-2018','3:30PM');

INSERT INTO APPOINTMENT VALUES(40,779,0932,'14-FEB-2018','11:45AM');

INSERT INTO APPOINTMENT VALUES(50,297,7888,'29-MAR-2018','1:15PM');

CREATE TABLE MEMBER (

CLIENT_ID INTEGER PRIMARY KEY FOREIGN KEY REFERENCES CLIENT NOT NULL,

MEMBERSHIP_ID INTEGER,

PAID_DATE DATE NOT NULL,

RENEW_DATE DATE NOT NULL);

--INSERT CLIENTS INTO MEMBER TABLE

INSERT INTO MEMBER VALUES(5323,223,'02-17-18','02-17-19');

INSERT INTO MEMBER VALUES(5425,674,'06-25-18','06-25-19');

INSERT INTO MEMBER VALUES(8849,905,'03-23-18','02-23-19');

INSERT INTO MEMBER VALUES(9120,774,'03-02-18','03-02-19');

INSERT INTO MEMBER VALUES(7443,619,'01-23-18','01-23-19');

CREATE TABLE NON_MEMBER (

CLIENT_ID INTEGER PRIMARY KEY FOREIGN KEY REFERENCES CLIENT NOT NULL);

--INSERT CLIENTS INTO NON_MEMBER TABLE

INSERT INTO NON_MEMBER VALUES(1947);

INSERT INTO NON_MEMBER VALUES(1017);

INSERT INTO NON_MEMBER VALUES(0932);

INSERT INTO NON_MEMBER VALUES(7888);

INSERT INTO NON_MEMBER VALUES(3543);

CREATE TABLE SALE (

SALE_ID INTEGER PRIMARY KEY NOT NULL,

CLIENT_ID INTEGER NOT NULL,

PRODUCT_ID INTEGER,

SERVICE_ID INTEGER,

MEMBER_DISCOUNT CHAR(1) NOT NULL,

SALE_DATE DATE NOT NULL,

SALE_TOTAL CHAR(5)

FOREIGN KEY (CLIENT_ID) REFERENCES CLIENT);

--INSERT DATA INTO SALE TABLE

INSERT INTO SALE VALUES(1001,5425,196,162,'Y','02/12/17','43');

INSERT INTO SALE VALUES(1004,3543,NULL,162,'N','01/23/17','30');

INSERT INTO SALE VALUES(1032,1017,856,160,'N','03/04/17','62');

INSERT INTO SALE VALUES(1230,9120,091,NULL,'Y','01/26/17','80');

INSERT INTO SALE VALUES(1008,5323,344,164,'N','05/19/17','27');

CREATE TABLE PROMOTION (

PROMO_ID INTEGER PRIMARY KEY,

CLIENT_ID INTEGER

FOREIGN KEY (CLIENT_ID) REFERENCES CLIENT);

--INSERT DATA INTO PROMOTION TABLE

INSERT INTO PROMOTION VALUES(9848,5323);

INSERT INTO PROMOTION VALUES(1263,7443);

INSERT INTO PROMOTION VALUES(9923,9120);

INSERT INTO PROMOTION VALUES(4352,8849);

INSERT INTO PROMOTION VALUES(7803,5425);

CREATE TABLE CC_PAYMENT(

CC_NUMBER CHAR(16) PRIMARY KEY NOT NULL,

NAME VARCHAR (35) NOT NULL,

CCV_NUMBER NUMERIC(3) NOT NULL,

EXP_DATE CHAR(4) NOT NULL);

--INSERT DATA INTO CC PAYMENT TABLE

INSERT INTO CC_PAYMENT VALUES('3432883299283444','Kevin Robinson','343','0720');

INSERT INTO CC_PAYMENT VALUES('7834734862483247','Michelle Martinez','126','0720');

INSERT INTO CC_PAYMENT VALUES('6448853654565874','Jake Evans','420','0720');

INSERT INTO CC_PAYMENT VALUES('7555466924568951','Dawn So','699','0720');

INSERT INTO CC_PAYMENT VALUES('8394337834444347','Larry Green','732','0720');

CREATE TABLE CHK_PAYMENT(

CHK_NUMBER CHAR(4) PRIMARY KEY NOT NULL,

ACCOUNT_NUMBER CHAR(17) NOT NULL,

ROUTING_NUMBER CHAR(9) NOT NULL,

CHK_DATE DATE NOT NULL,

CHK_PAYEE_NAME VARCHAR(35) NOT NULL);

--INSERT DATA INTO CHECK PAYMENT TABLE

INSERT INTO CHK_PAYMENT VALUES('2436','32424324434','534667234','07-23-17','Larry Crawford');

INSERT INTO CHK_PAYMENT VALUES('1734','54634534324','943843723','06-17-17','Elizabeth Jones');

INSERT INTO CHK_PAYMENT VALUES('4366','74478535656','993424334','05-19-17','Kimberly Young');

INSERT INTO CHK_PAYMENT VALUES('9942','65437349234','876433234','02-23-17','Mark Cuban');

INSERT INTO CHK_PAYMENT VALUES('3022','13249387849','424332342','04-12-17','Pamela Scott');

CREATE TABLE PAYMENT (

SALE_ID INTEGER PRIMARY KEY NOT NULL,

CC_NUMBER CHAR(16),

CHK_NUMBER CHAR(4),

FOREIGN KEY (CC_NUMBER) REFERENCES CC_PAYMENT,

FOREIGN KEY (CHK_NUMBER) REFERENCES CHK_PAYMENT);

--INSERT DATA INTO PAYMENT TABLE

INSERT INTO PAYMENT VALUES(43544,'6448853654565874',NULL);

INSERT INTO PAYMENT VALUES(34325,'8394337834444347',NULL);

INSERT INTO PAYMENT VALUES(76544,NULL,'1734');

INSERT INTO PAYMENT VALUES(94353,'7555466924568951','2436');

INSERT INTO PAYMENT VALUES(53233,'7834734862483247',NULL);

CREATE TABLE SERVC (

SERVICE_ID INTEGER PRIMARY KEY NOT NULL,

SERVICE_TYPE VARCHAR(35) NOT NULL,

SERVICE_COST VARCHAR(35) NOT NULL);

--INSERT DATA INTO SERVICE TABLE

INSERT INTO SERVC VALUES(160,'Hair color change','45.00');

INSERT INTO SERVC VALUES(161,'Hair blowout','15.00');

INSERT INTO SERVC VALUES(162,'Haircut','30.00');

INSERT INTO SERVC VALUES(163,'Trim','20.00');

INSERT INTO SERVC VALUES(164,'Deep clean','15.00');