2. Display employee last name and manager’s last name for all employees. Use app
ID: 3736894 • Letter: 2
Question
2. Display employee last name and manager’s last name for all employees. Use appropriate column headings in your result such as “EMPLOYEE” and “MANAGER”. (Manager’s EMP_NUM is available in EMP_MGR column of EMP table). 3. Display customer last name, invoice number and product code of products ordered sorted by customer last name in ascending order and invoice number in descending order. 4. Display last names, first names, and employee count for all managers sorted in descending order by employees count. This query will list all employees who are managers. (Hint: You should join the Employee table to it 5. Who are the customers with no invoices placed. Display last name, first name, customer balance for these customers /* ADVSQLDBINIT.SQL */ /* Advanced SQL */ /* Script file for MS SQL Server DBMS */ /* This script file creates the following tables: */ /* V - default vendor table data */ /* P - default product table data */ /* VENDOR, PRODUCT, CUSTOMER, INVOICE, LINE */ /* EMPLOYEE, EMP */ /* and loads the default data rows */ CREATE DATABASE ABCTools; USE ABCTools; BEGIN TRANSACTION; DROP TABLE LINE; DROP TABLE INVOICE; DROP TABLE CUSTOMER; DROP TABLE CUSTOMER_2; DROP TABLE PRODUCT; DROP TABLE VENDOR; DROP TABLE EMPLOYEE; DROP TABLE EMP; DROP TABLE P; DROP TABLE V; CREATE TABLE V ( V_CODE INTEGER PRIMARY KEY, V_NAME VARCHAR(35) NOT NULL, V_CONTACT VARCHAR(15) NOT NULL, V_AREACODE CHAR(3) NOT NULL, V_PHONE CHAR(8) NOT NULL, V_STATE CHAR(2) NOT NULL, V_ORDER CHAR(1) NOT NULL); CREATE TABLE P ( P_CODE VARCHAR(10) PRIMARY KEY, P_DESCRIPT VARCHAR(35) NOT NULL, P_INDATE DATETIME NOT NULL, P_QOH INTEGER NOT NULL, P_MIN INTEGER NOT NULL, P_PRICE NUMERIC(8,2) NOT NULL, P_DISCOUNT NUMERIC(4,2) NOT NULL, V_CODE INTEGER, P_MIN_ORDER INTEGER, P_REORDER INTEGER); CREATE TABLE VENDOR ( V_CODE INTEGER, V_NAME VARCHAR(35) NOT NULL, V_CONTACT VARCHAR(15) NOT NULL, V_AREACODE CHAR(3) NOT NULL, V_PHONE CHAR(8) NOT NULL, V_STATE CHAR(2) NOT NULL, V_ORDER CHAR(1) NOT NULL, PRIMARY KEY (V_CODE)); CREATE TABLE PRODUCT ( P_CODE VARCHAR(10) CONSTRAINT PRODUCT_P_CODE_PK PRIMARY KEY, P_DESCRIPT VARCHAR(35) NOT NULL, P_INDATE DATETIME NOT NULL, P_QOH INTEGER NOT NULL, P_MIN INTEGER NOT NULL, P_PRICE NUMERIC(8,2) NOT NULL, P_DISCOUNT NUMERIC(4,2) NOT NULL, V_CODE INTEGER, P_MIN_ORDER INTEGER, P_REORDER INTEGER, CONSTRAINT PRODUCT_V_CODE_FK FOREIGN KEY (V_CODE) REFERENCES VENDOR); CREATE TABLE CUSTOMER ( CUS_CODE INTEGER PRIMARY KEY, CUS_LNAME VARCHAR(15) NOT NULL, CUS_FNAME VARCHAR(15) NOT NULL, CUS_INITIAL CHAR(1), CUS_AREACODE CHAR(3) DEFAULT '615' NOT NULL CHECK(CUS_AREACODE IN ('615','713','931')), CUS_PHONE CHAR(8) NOT NULL, CUS_BALANCE NUMERIC(9,2) DEFAULT 0.00, CONSTRAINT CUS_UI1 UNIQUE(CUS_LNAME,CUS_FNAME)); CREATE TABLE CUSTOMER_2 ( CUS_CODE INTEGER PRIMARY KEY, CUS_LNAME VARCHAR(15) NOT NULL, CUS_FNAME VARCHAR(15) NOT NULL, CUS_INITIAL CHAR(1), CUS_AREACODE CHAR(3), CUS_PHONE CHAR(8)); CREATE TABLE INVOICE ( INV_NUMBER INTEGER PRIMARY KEY, CUS_CODE INTEGER NOT NULL REFERENCES CUSTOMER(CUS_CODE), INV_DATE DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, CONSTRAINT INV_CK1 CHECK (INV_DATE > '01-JAN-2010')); CREATE TABLE LINE ( INV_NUMBER INTEGER NOT NULL, LINE_NUMBER NUMERIC(2,0) NOT NULL, P_CODE VARCHAR(10) NOT NULL, LINE_UNITS NUMERIC(9,2) DEFAULT 0.00 NOT NULL, LINE_PRICE NUMERIC(9,2) DEFAULT 0.00 NOT NULL, PRIMARY KEY (INV_NUMBER,LINE_NUMBER), FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE ON DELETE CASCADE, FOREIGN KEY (P_CODE) REFERENCES PRODUCT(P_CODE), CONSTRAINT LINE_UI1 UNIQUE(INV_NUMBER, P_CODE)); CREATE TABLE EMPLOYEE ( EMP_NUM INTEGER PRIMARY KEY, EMP_TITLE CHAR(10), EMP_LNAME VARCHAR(15) NOT NULL, EMP_FNAME VARCHAR(15) NOT NULL, EMP_INITIAL CHAR(1), EMP_DOB DATETIME, EMP_HIRE_DATE DATETIME, EMP_YEARS INTEGER, EMP_AREACODE CHAR(3), EMP_PHONE CHAR(8)); CREATE TABLE EMP ( EMP_NUM INTEGER PRIMARY KEY, EMP_TITLE CHAR(10), EMP_LNAME VARCHAR(15) NOT NULL, EMP_FNAME VARCHAR(15) NOT NULL, EMP_INITIAL CHAR(1), EMP_DOB DATETIME, EMP_HIRE_DATE DATETIME, EMP_AREACODE CHAR(3), EMP_PHONE CHAR(8), EMP_MGR INTEGER); /* V rows */ INSERT INTO V VALUES(21225,'Bryson, Inc.' ,'Smithson','615','223-3234','TN','Y'); INSERT INTO V VALUES(21226,'SuperLoo, Inc.' ,'Flushing','904','215-8995','FL','N'); INSERT INTO V VALUES(21231,'D&E Supply' ,'Singh' ,'615','228-3245','TN','Y'); INSERT INTO V VALUES(21344,'Gomez Bros.' ,'Ortega' ,'615','889-2546','KY','N'); INSERT INTO V VALUES(22567,'Dome Supply' ,'Smith' ,'901','678-1419','GA','N'); INSERT INTO V VALUES(23119,'Randsets Ltd.' ,'Anderson','901','678-3998','GA','Y'); INSERT INTO V VALUES(24004,'Brackman Bros.' ,'Browning','615','228-1410','TN','N'); INSERT INTO V VALUES(24288,'ORDVA, Inc.' ,'Hakford' ,'615','898-1234','TN','Y'); INSERT INTO V VALUES(25443,'B&K, Inc.' ,'Smith' ,'904','227-0093','FL','N'); INSERT INTO V VALUES(25501,'Damal Supplies' ,'Smythe' ,'615','890-3529','TN','N'); INSERT INTO V VALUES(25595,'Rubicon Systems' ,'Orton' ,'904','456-0092','FL','Y'); /* P rows */ INSERT INTO P VALUES('11QER/31','Power painter, 15 psi., 3-nozzle' ,'03-NOV-2009', 8, 5,109.99,0.00,25595, 25,0); INSERT INTO P VALUES('13-Q2/P2','7.25-in. pwr. saw blade' ,'13-DEC-2009', 32, 15, 14.99,0.05,21344, 50,0); INSERT INTO P VALUES('14-Q1/L3','9.00-in. pwr. saw blade' ,'13-NOV-2009', 18, 12, 17.49,0.00,21344, 50,0); INSERT INTO P VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50' ,'15-JAN-2010', 15, 8, 39.95,0.00,23119, 35,0); INSERT INTO P VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50' ,'15-JAN-2010', 23, 5, 43.99,0.00,23119, 25,0); INSERT INTO P VALUES('2232/QTY','B&D jigsaw, 12-in. blade' ,'30-DEC-2009', 8, 5,109.92,0.05,24288, 15,0); INSERT INTO P VALUES('2232/QWE','B&D jigsaw, 8-in. blade' ,'24-DEC-2009', 6, 5, 99.87,0.05,24288, 15,0); INSERT INTO P VALUES('2238/QPD','B&D cordless drill, 1/2-in.' ,'20-JAN-2010', 12, 5, 38.95,0.05,25595, 12,0); INSERT INTO P VALUES('23109-HB','Claw hammer' ,'20-JAN-2010', 23, 10, 9.95,0.10,21225, 25,0); INSERT INTO P VALUES('23114-AA','Sledge hammer, 12 lb.' ,'02-JAN-2010', 8, 5, 14.40,0.05,NULL , 12,0); INSERT INTO P VALUES('54778-2T','Rat-tail file, 1/8-in. fine' ,'15-DEC-2009', 43, 20, 4.99,0.00,21344, 25,0); INSERT INTO P VALUES('89-WRE-Q','Hicut chain saw, 16 in.' ,'07-FEB-2010', 11, 5,256.99,0.05,24288, 10,0); INSERT INTO P VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft' ,'20-FEB-2010',188, 75, 5.87,0.00,NULL , 50,0); INSERT INTO P VALUES('SM-18277','1.25-in. metal screw, 25' ,'01-MAR-2010',172, 75, 6.99,0.00,21225, 50,0); INSERT INTO P VALUES('SW-23116','2.5-in. wd. screw, 50' ,'24-FEB-2010',237,100, 8.45,0.00,21231,100,0); INSERT INTO P VALUES('WR3/TT3' ,'Steel matting, 4''x8''x1/6", .5" mesh','17-JAN-2010', 18, 5,119.95,0.10,25595, 10,0); /* VENDOR rows */ INSERT INTO VENDOR SELECT * FROM V; /* PRODUCT rows */ INSERT INTO PRODUCT SELECT * FROM P; /* CUSTOMER rows */ INSERT INTO CUSTOMER VALUES(10010,'Ramas' ,'Alfred','A' ,'615','844-2573',0); INSERT INTO CUSTOMER VALUES(10011,'Dunne' ,'Leona' ,'K' ,'713','894-1238',0); INSERT INTO CUSTOMER VALUES(10012,'Smith' ,'Kathy' ,'W' ,'615','894-2285',345.86); INSERT INTO CUSTOMER VALUES(10013,'Olowski' ,'Paul' ,'F' ,'615','894-2180',536.75); INSERT INTO CUSTOMER VALUES(10014,'Orlando' ,'Myron' ,NULL,'615','222-1672',0); INSERT INTO CUSTOMER VALUES(10015,'O''Brian','Amy' ,'B' ,'713','442-3381',0); INSERT INTO CUSTOMER VALUES(10016,'Brown' ,'James' ,'G' ,'615','297-1228',221.19); INSERT INTO CUSTOMER VALUES(10017,'Williams','George',NULL,'615','290-2556',768.93); INSERT INTO CUSTOMER VALUES(10018,'Farriss' ,'Anne' ,'G' ,'713','382-7185',216.55); INSERT INTO CUSTOMER VALUES(10019,'Smith' ,'Olette','K' ,'615','297-3809',0); /* CUSTOMER_2 rows */ INSERT INTO CUSTOMER_2 VALUES(345,'Terrell','Justine','H','615','322-9870'); INSERT INTO CUSTOMER_2 VALUES(347,'Olowski','Paul','F',615,'894-2180'); INSERT INTO CUSTOMER_2 VALUES(351,'Hernandez','Carlos','J','723','123-7654'); INSERT INTO CUSTOMER_2 VALUES(352,'McDowell','George',NULL,'723','123-7768'); INSERT INTO CUSTOMER_2 VALUES(365,'Tirpin','Khaleed','G','723','123-9876'); INSERT INTO CUSTOMER_2 VALUES(368,'Lewis','Marie','J','734','332-1789'); INSERT INTO CUSTOMER_2 VALUES(369,'Dunne','Leona','K','713','894-1238'); /* INVOICE rows */ INSERT INTO INVOICE VALUES(1001,10014,'16-JAN-2010'); INSERT INTO INVOICE VALUES(1002,10011,'16-JAN-2010'); INSERT INTO INVOICE VALUES(1003,10012,'16-JAN-2010'); INSERT INTO INVOICE VALUES(1004,10011,'17-JAN-2010'); INSERT INTO INVOICE VALUES(1005,10018,'17-JAN-2010'); INSERT INTO INVOICE VALUES(1006,10014,'17-JAN-2010'); INSERT INTO INVOICE VALUES(1007,10015,'17-JAN-2010'); INSERT INTO INVOICE VALUES(1008,10011,'17-JAN-2010'); /* LINE rows */ INSERT INTO LINE VALUES(1001,1,'13-Q2/P2',1,14.99); INSERT INTO LINE VALUES(1001,2,'23109-HB',1,9.95); INSERT INTO LINE VALUES(1002,1,'54778-2T',2,4.99); INSERT INTO LINE VALUES(1003,1,'2238/QPD',1,38.95); INSERT INTO LINE VALUES(1003,2,'1546-QQ2',1,39.95); INSERT INTO LINE VALUES(1003,3,'13-Q2/P2',5,14.99); INSERT INTO LINE VALUES(1004,1,'54778-2T',3,4.99); INSERT INTO LINE VALUES(1004,2,'23109-HB',2,9.95); INSERT INTO LINE VALUES(1005,1,'PVC23DRT',12,5.87); INSERT INTO LINE VALUES(1006,1,'SM-18277',3,6.99); INSERT INTO LINE VALUES(1006,2,'2232/QTY',1,109.92); INSERT INTO LINE VALUES(1006,3,'23109-HB',1,9.95); INSERT INTO LINE VALUES(1006,4,'89-WRE-Q',1,256.99); INSERT INTO LINE VALUES(1007,1,'13-Q2/P2',2,14.99); INSERT INTO LINE VALUES(1007,2,'54778-2T',1,4.99); INSERT INTO LINE VALUES(1008,1,'PVC23DRT',5,5.87); INSERT INTO LINE VALUES(1008,2,'WR3/TT3',3,119.95); INSERT INTO LINE VALUES(1008,3,'23109-HB',1,9.95); /* EMP rows */ INSERT INTO EMP VALUES(100,'Mr.' ,'Kolmycz' ,'George' ,'D' ,'15-JUN-1942','15-MAR-1985','615','324-5456',NULL); INSERT INTO EMP VALUES(101,'Ms.' ,'Lewis' ,'Rhonda' ,'G' ,'19-MAR-1965','25-APR-1986','615','324-4472',100); INSERT INTO EMP VALUES(102,'Mr.' ,'Vandam' ,'Rhett' ,NULL,'14-NOV-1958','20-DEC-1990','901','675-8993',100); INSERT INTO EMP VALUES(103,'Ms.' ,'Jones' ,'Anne' ,'M' ,'16-OCT-1974','28-AUG-1994','615','898-3456',100); INSERT INTO EMP VALUES(104,'Mr.' ,'Lange' ,'John' ,'P' ,'08-NOV-1971','20-OCT-1994','901','504-4430',105); INSERT INTO EMP VALUES(105,'Mr.' ,'Williams' ,'Robert' ,'D' ,'14-MAR-1975','08-NOV-1998','615','890-3220',NULL); INSERT INTO EMP VALUES(106,'Mrs.','Smith' ,'Jeanine','K' ,'12-FEB-1968','05-JAN-1989','615','324-7883',105); INSERT INTO EMP VALUES(107,'Mr.' ,'Diante' ,'Jorge' ,'D' ,'21-AUG-1974','02-JUL-1994','615','890-4567',105); INSERT INTO EMP VALUES(108,'Mr.' ,'Wiesenbach','Paul' ,'R' ,'14-FEB-1966','18-NOV-1992','615','897-4358',NULL); INSERT INTO EMP VALUES(109,'Mr.' ,'Smith' ,'George' ,'K' ,'18-JUN-1961','14-APR-1989','901','504-3339',108); INSERT INTO EMP VALUES(110,'Mrs.','Genkazi' ,'Leighla','W' ,'19-MAY-1970','01-DEC-1990','901','569-0093',108); INSERT INTO EMP VALUES(111,'Mr.' ,'Washington','Rupert' ,'E' ,'03-JAN-1966','21-JUN-1993','615','890-4925',105); INSERT INTO EMP VALUES(112,'Mr.' ,'Johnson' ,'Edward' ,'E' ,'14-MAY-1961','01-DEC-1983','615','898-4387',100); INSERT INTO EMP VALUES(113,'Ms.' ,'Smythe' ,'Melanie','P' ,'15-SEP-1970','11-MAY-1999','615','324-9006',105); INSERT INTO EMP VALUES(114,'Ms.' ,'Brandon' ,'Marie' ,'G' ,'02-NOV-1956','15-NOV-1979','901','882-0845',108); INSERT INTO EMP VALUES(115,'Mrs.','Saranda' ,'Hermine','R' ,'25-JUL-1972','23-APR-1993','615','324-5505',105); INSERT INTO EMP VALUES(116,'Mr.' ,'Smith' ,'George' ,'A' ,'08-NOV-1965','10-DEC-1988','615','890-2984',108); /* EMPLOYEE rows */ INSERT INTO EMPLOYEE VALUES(100,'Mr.' ,'Kolmycz' ,'George' ,'D' ,'15-JUN-1942','15-MAR-1985',18,'615','324-5456'); INSERT INTO EMPLOYEE VALUES(101,'Ms.' ,'Lewis' ,'Rhonda' ,'G' ,'19-MAR-1965','25-APR-1986',16,'615','324-4472'); INSERT INTO EMPLOYEE VALUES(102,'Mr.' ,'Vandam' ,'Rhett' ,NULL,'14-NOV-1958','20-DEC-1990',12,'901','675-8993'); INSERT INTO EMPLOYEE VALUES(103,'Ms.' ,'Jones' ,'Anne' ,'M' ,'16-OCT-1974','28-AUG-1994', 8,'615','898-3456'); INSERT INTO EMPLOYEE VALUES(104,'Mr.' ,'Lange' ,'John' ,'P' ,'08-NOV-1971','20-OCT-1994', 8,'901','504-4430'); INSERT INTO EMPLOYEE VALUES(105,'Mr.' ,'Williams' ,'Robert' ,'D' ,'14-MAR-1975','08-NOV-1998', 4,'615','890-3220'); INSERT INTO EMPLOYEE VALUES(106,'Mrs.','Smith' ,'Jeanine','K' ,'12-FEB-1968','05-JAN-1989',14,'615','324-7883'); INSERT INTO EMPLOYEE VALUES(107,'Mr.' ,'Diante' ,'Jorge' ,'D' ,'21-AUG-1974','02-JUL-1994', 8,'615','890-4567'); INSERT INTO EMPLOYEE VALUES(108,'Mr.' ,'Wiesenbach','Paul' ,'R' ,'14-FEB-1966','18-NOV-1992',10,'615','897-4358'); INSERT INTO EMPLOYEE VALUES(109,'Mr.' ,'Smith' ,'George' ,'K' ,'18-JUN-1961','14-APR-1989',13,'901','504-3339'); INSERT INTO EMPLOYEE VALUES(110,'Mrs.','Genkazi' ,'Leighla','W' ,'19-MAY-1970','01-DEC-1990',12,'901','569-0093'); INSERT INTO EMPLOYEE VALUES(111,'Mr.' ,'Washington','Rupert' ,'E' ,'03-JAN-1966','21-JUN-1993', 9,'615','890-4925'); INSERT INTO EMPLOYEE VALUES(112,'Mr.' ,'Johnson' ,'Edward' ,'E' ,'14-MAY-1961','01-DEC-1983',19,'615','898-4387'); INSERT INTO EMPLOYEE VALUES(113,'Ms.' ,'Smythe' ,'Melanie','P' ,'15-SEP-1970','11-MAY-1999', 3,'615','324-9006'); INSERT INTO EMPLOYEE VALUES(114,'Ms.' ,'Brandon' ,'Marie' ,'G' ,'02-NOV-1956','15-NOV-1979',23,'901','882-0845'); INSERT INTO EMPLOYEE VALUES(115,'Mrs.','Saranda' ,'Hermine','R' ,'25-JUL-1972','23-APR-1993', 9,'615','324-5505'); INSERT INTO EMPLOYEE VALUES(116,'Mr.' ,'Smith' ,'George' ,'A' ,'08-NOV-1965','10-DEC-1988',14,'615','890-2984'); COMMIT; GRANT SELECT ON V TO PUBLIC; GRANT SELECT ON P TO PUBLIC;
Explanation / Answer
GIve me comment, If you get any error, because I don't on which platform(ORACLE, SQL, etc...)
2. Display employee last name and manager’s last name for all employees. Use appropriate column headings in your result such as “EMPLOYEE” and “MANAGER”. (Manager’s EMP_NUM is available in EMP_MGR column of EMP table).
SELECT E.EMP_LNAME as EMPLOYEE, M.EMP_LNAME as MANAGER
FROM EMP E JOIN EMP M ON E.MGR = M.EMP_NUM;
3. Display customer last name, invoice number and product code of products ordered sorted by customer last name in ascending order and invoice number in descending order.
SELECT C.CUS_LNAME, I.INV_NUMBER, L.P_CODE
FROM (CUSTOMER C JOIN INVOICE I ON C.CUS_CODE = I.CUS_CODE) JOIN LINE L ON I.INV_NUMBER = L.INV_NUMBER
ORDER BY C.CUS_NAME ASC, I.INV_NUMBER DESC;
4. Display last names, first names, and employee count for all managers sorted in descending order by employees count. This query will list all employees who are managers. (Hint: You should join the Employee table to it
SELECT EMP_LNAME, EMP_FNAME, COUNT
FROM (SELECT M.EMP_LNAME AS EMP_LNAME, M.EMP_FNAME AS EMP_FNAME, COUNT(*) as COUNT
FROM EMP M JOIN EMP E ON M.EMP_MGR = E.EMP_NUM
GROUP BY M.EMP_MGR, M.EMP_LNAME, M.EMP_FNAME)
ORDER BY COUNT DESC;
5. Who are the customers with no invoices placed. Display last name, first name, customer balance for these customers
SELECT CUST_LNAME, CUST_FNAME, CUS_BALANCE
FROM CUSTOMER
WHERE CUS_CODE NOT IN(
SELECT CUS_CODE
FROM INVOICE
);
Related Questions
Hire Me For All Your Tutoring Needs
Integrity-first tutoring: clear explanations, guidance, and feedback.
Drop an Email at
drjack9650@gmail.com
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.