[Solved] 1. 3 marks) List all the cities in which

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 09, 2024

1. 3 marks) List all the cities in which we have offices. In the report be sure to list New York as N.Y. and Los

image text in transcribed

1. 3 marks) List all the cities in which we have offices. In the report be sure to list New York as "N.Y." and Los Angeles as "L.A. 2, (3 marks) we need to make user names for our sales reps. A user name is made from the first initial of the first name plus the last name. (Ex: Bill Adams- badams) 73.(3 marks) An incident occured around: 1988-05-011 Unfortunately we are not sure if this date is accurate! (OH NO We definitely know it was within 2 months (plus or minus) from that date. Find all the SalesReps who were hired during this period, so we can continue our investigation. You CANNOT use the key words: BETWEEN, AND, OR Instead, you must use single-line functions to solve the problem. Display the sales rep name and their hire date. 4. (4 marks) Display everyone in the table sales reps with sales over a thousand. Order by argest Sales first. Show us their name, title and sales. We don't want to see any numbers below the thousands columns. We also want to see the whole word: "Representative instead of " Rep". Note: we are not rounding sales rep. Example: 3783 becomes 3000 5012 becomes 5000 Example solution row: Bill Adams Sales Representative 3000 5. (4 marks) Display all orders with an amount greater than 10 after rounding the nearest 10 spot (Ex: 4.24 Rounds down to 0. 5.5 Rounds up to 10). Show the Order Number, Customer number, Date and Amount. Ifa customer number is not available for the order, show the value -1 instead. The Amount should be formatted to use a dollar sign, we do not need to print the any decimal (Examples: 315, S37, $6). Format the date based on the example solution row below (ensure you use the same 113045 2112 Friday February 6, 1990 $450 Worksheet Query Builder ROP TABLE CustomerS; DROP TABLE Salesreps; DROP TABLE Offices; DROP TABLE Orders; DROP TABLE ProductS; CREATE TABLE Customers (CustNum CompanyY CustRe CreditLimit NUMBER (6), CONSTRAINT CustomersPK NUMBER (10) VARCHAR2 (20 NUMBER (10), NOT NULL, NOT p PRIMARY KEY (CustNum)) CREATE TABLE SalesReps (SalesRep NUMBER (10) NOT NULL VARCHAR2(15 NOT NUMBER (3), Name Age RepOffice NUMBER (10), Title HireDate DATE Manager Quota Sales CONSTRAINT SalesrepsPK VARCHAR2 (15), NOT NULL r NUMBER (10), NUMBER (10), NUMBER (12,2) NOT NULL, PRIMARY KEY (Salesrep)); CREATE TABLE Offices NUMBER NULL NU NU NU NU NU NU 50 ,2 3 0 iyi Price QtyOnHand NUMBER (5), CONSTRAINT ProductsPK NUMBER (9,2) NOT NULL PRIMARY KEY (Mfr, Product)); INSERT INTO Customers (CustNum, Company, CustRep, CreditLimit) VALUES (2111, 'JCP Inc' INSERT INTO Customers (CustNum, Company, CustRep, CreditLimit) VALUES (2102, 'first corp' INSERT INTO Customers (CustNum, Company, CustRep, CreditLimit) VALUES (2103, Acme Mfg INSERT INTO Customers (CustNum, Company, CustRep, CreditLimit) VALUES (2123, 'Carter and Sons' INSERT INTO Customers (CustNum, Company, CustRep, CreditLimit) VALUES (2107, 'Ace International' ,110, 350.00 INSERT INTO Customers (CustNum, Company, CustRep, CreditLimit) VALUES (2115, Smithson Corp.' INSERT INTO Customers (CustNum, Company, CustRep, CreditLimit) VALUES (2101, 'Jones Mfg. INSERT INTO Customers (CustNum, Company, CustRep, CreditLimit) VALUES (2112, 'ZetaCorp" INSERT INTO Customers (CustNum, Company, CustRep, CreditLimit) VALUES (2121, 'OMA Assoc.' INSERT INTO Customers (CustNum, Company, CustRep, CreditLimit) VALUES (2114, 'Orion Corp' INSERT INTO Customers (CustNum, Company, CustRep, CreditLimit) VALUES (2124, Peter Brothers' INSERT INTO Customers (CustNum, Company, CustRep, CreditLimit) VALUES (2108, 'Holm and Landis' ,103, 500.00) , 101, 650.00 ,105, 500.00) ,102, 400.00) , 101, 200.00 ,106, 650.00) ,108, 500.00) ,103, 450.00) ,102, 200.00 ,107, 400.00) ,109, 550.00) st an stRe Worksheet Query Builder VALUES (2113, Ian and Schmidt' INSERT INTO Customers (CustNum, Company, CustRep, CreditLimit) VALUES (2109, 'Chen Associates',103, 250.00) INSERT INTO Customers (CustNum, Company, CustRep, CreditLimit) VALUES (2105, 'AAA Investments , 104, 200.00 ) ,null, 450.00; INSERT INTO Salesreps (SalesRep, Name, Age, Repoffice, Title, HireDate, Manager, Quota, Sales) VALUES (105, Bi11 Adams INSERT INTO Salesreps (SalesRep, Name, Age, Repoffice, Title, HireDate, Manager, Quota, Sales) VALUES(037, Jim Anderson' INSERT INTO Salesreps (SalesRep, Name, Age, Repoffice, Title, HireDate, Manager, Quota, Sales) VALUES (109, 'Mary Jones INSERT INTO Salesreps (SalesRep, Name, Age, RepOffice, Title, HireDate, Manager, duota, Sales) VALUES (102, 'Sue Smith' INSERT INTO Salesreps (SalesRep, Name, Age, RepOffice, Title, HireDate, Manager, duota, Sales) VALUES (106, 'Sam Clark' INSERT INTO Salesreps (SalesRep, Name, Age, RepOffice, Title, HireDate, Manager, duota, Sales) VALUES (104, 'Bob Smith' INSERT INTO Salesreps (SalesRep, Name, Age, RepOffice, Title, HireDate, Manager, duota, Sales) VALUES (101, 'Dan Roberts'.45, 12, Sales Rep',1986-10-20, 104, 1250.00, 3057.63 INSERT INTO Salesreps (SalesRep, Name, Age, RepOffice, Title, HireDate, Manager, Quota, Sales) VALUES(110, 'Iom Snyder' INSERT INTO Salesreps (SalesRep, Name, Age, RepOffice, Title, HireDate, Manager, Quota, Sales) VALUES(108, 'Larry Finch' 62, 21, "Sales Mgr','1989-10-12, 106, 3500.00, 3618.65 INSERT INTO Salesreps (SalesRep, Name, Age, RepOffice, Title, HireDate, Manager, Quota, Sales) VALUES (103, 'Paul Cruz' INSERT INTO Salesreps (SalesRep, Name, Age, RepOffice, Title, HireDate, Manager, Quota, Sales) VALUES (107, 'Sally Angelli' , 49, 22, 'Sales Rep' 1988-11-14', 108, 3000.00, 1860.42 , 37, 13, Sales Rep', '1988-02-12', 104, 3500.00, 3679.11) ,NULL, NULL, NULL'1988-02-12", NULL, NULL, 0 ) , 31, 1, 'Sales Rep' 1989-10-12', 106, 3000.00, 3927.25 ,4, 2, Sales Rep' "1986-12-29', 108, 3500.00, 4740.50 ,52, 1l, 'V? Sales'"1988-06-14, NULL, 2250.00, 2999.12) , 33, 12, 'Sales Mg' "1987-05-19', 106, 2000.00, 1425.94: ,41, NULL'Sales Rep' , '1990-01-13', 101 NULL 759.85 , 29, 12, 'Sales Rep','1987-03-01', 104, 2750.00, 2867.75); SalesRe ame Mana ta, Sales SQL Worksheet History Worksheet Query Builder VALUES (21, 'Los Angeles''Hestern'.108, 7250.00, 9957.62) INSERT INTO Offices (office, City, Region, Mgr, Target, Sales) VALUES (30, 'Dallas INSERT INTO Offices (office, City, Region, Mgr, Target, Sales) VALUES (43, 'Miami , Southern', 107, 9500.00, .10 , Southern' , 107, 7500.00, 5123.22 INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (112961, "2000-04-20', 2117, 106, 'REI, '2A44L, 07, 315.00: INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (113012, "2000-02-28', 2111, 105, 'ACI, '41003, 35, 37.45): INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (112989, '1990-01-03', 2101, 106, EEA,'L14", 06, 14.58 INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (113051, '1990-02-10', 2118, 108, 'QSA, 'XK47', 04, 14.20: INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (112968, "2000-02-29', 2102, 101, 'ACI, '41004, 34, 39.78: INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (11303, 1990-01-30, 2107, 110, 'ACI,41002", 09, 225.00 INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (113045, "1990-02-02', 2112, 108, 'REI, '2A44R, 10, 450.00: INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Hfr, Product, Qty, Amount) VALUES (112963, "1989-12-17', 2103, 105, 'ACI, '41004, 28, 32.76: INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (113013, '1990-01-14, 2118, 108, BIC,41003, 01, 6.52 INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (113058, "1990-02-23', 2108, 109, 'EEA, 'L12" , 10, 14.80: INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (112997, "1990-01-08', 2124, 107, 'BIC, '41003, 01, 6.52: te un I Line 1 Column 1InsertI 1 Windows: QL Worksheet History Worksheet Query Builder INSERT INTO Orders (Orderum, OrderDate, Cust, Rep, MEr, Product, Qty, Amount) VALUES (112997, '1990-01-08. 2124, 107, 'BIC", "41003", 01, 6.52 ); INSERT INTO Orders (Orderm, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (112983, '1989-12-27', 2103, 105, 'ACI'41004', 06, 7.02 INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Oty, Amount) VALUES (113024, '1990-01-20, 2114, 108, 'QSA, 'XK47', 20, 71.00 INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (113062, 1990-02-24", 2124, 107, 'FEA, 'L14' , INSERT INTO Orders (Orderum, OrderDate, Cust, Rep, Mfr, Product, QtY, Amount) VALUES (112979, '1999-02-28', 2114, 102, 'ACI,4100Z', 06, 150.00 INSERT INTO Orders (Orderum, OrderDate, Cust, Rep, Mr, Product, Qty, Amount) VALUES (113027, "1990-01-22,2103, 105, "ACI, 41002, 54, 41.04 INSERT INTO Orders (Orderum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (113007, '1990-01-08, 2112, 108, 'IMM, '773C, 03, 29.25 INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (113069, '1990-03-02", 2109, 107, 'IMM, '775C", 22, 313.50 INSERT INTO Orders (0rderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (113034, '1990-01-29', 2107, 110, 'REI, '2A45C', 0, 6.32 INSERT INTO Orders (Orderum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (112992, '1989-11-04', 2118, 108, 'ACI,41002', 10, 7.60) INSERT INTO Orders (Orderm, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (112975, "1989-10-12,211, 103, "REI, 2A44G', 06, 21.00 INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Oty, Amount) VALUES (113055, '1990-02-15, 2108, 101, 'ACI, '4100x, 06, 1.50 INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (113048, '1990-02-10, 2120, 102, 'IMM '779C, 02, 37.50 INSERT INTO Orders (Orderum, OrderDate, Cust, Rep, Mfr, Product, tY, Amount) VALUES (112993, 1989-01-04', 2106, 102, 'REI,'2A4SC', 24, 18.96 INSERT INTO Orders (Orderum, OrderDate, Cust, Rep, MEr, Product, Qty, Amount) 10, 24.30); rksheet Query Builder INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (113003, '1990-01-25, 2108, 109, IM'779C, 03, 56.25 INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (113049, '1990-02-10',2118, 108, 'QSA','XK47', 02, 7.76; INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (112987, '1989-12-31', 2103, 105, 'ACI, 4100Y, 11, 275.00 INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (113057, 1990-02-18', 2111, 103, ACI,4100x', 24, 6.00) INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (113042, '1990-02-02', 2113, 101, 'REI, '2A44R, 05, 225.00 INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (113090, 2004-01-23", 2999, 101, "ACI, 41001', 10, 5.50 INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (113091, '2004-01-23, NULL, 101, IM887p,100, 250.00) INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (113192, '2004-01-23',2108, 109, 'BIC, 4103',300, 15.00 INSERT INTO Orders (OrderNum, OrderDate, Cust, Rep, Mfr, Product, Qty, Amount) VALUES (113195, '2004-01-23. 2102, 101, "BIC", "4103" ,500, 25.00 ); INSERT INTO Orders (OrderNu, OrderDate, Cust, Rep, Mfr, Product,Qty, Amount) VALUES (113196, '2005-01-21', NULL, 101, REI '2A44R',500, 25.00) INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES ('REI, 2A45C, "Ratchet Link' INSERT INTO Products (Mfr, Product, Description, Price, etyOnHand) VALUES('ACI, 4100Y", 'Widget Remover' INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES QSA', "XK47', "Reducer' INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) ..79, 210): , 27.50,25) ,3.55,38 rksheet Query Builder ,3.55,38) ,1.80 ,18.75, 9 , 1.07, 207) ,1.17, 139) VALUES QSA,XK47'"Reducer' INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES ('BIC',41672", "Plate' INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES(IMM,779C,900-1b Brace INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES 'ACI, 41003', "Size 3 widget' INSERT INTO Products (MEr, Product, Description, Price, OtyOnHand) VALUES ('ACI,"41004', "Size3 4 widget' INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES ('BIC',41003 Handle INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES (IMM, "887P', "Brace Pin' INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES QSA' 'XK48' 'Reducer INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES('REI, 2A44L,'Left Hinge' INSERT INTO Products (Mfr, Product, Description, Price, etyOnHand) VALUES FEA', 'L12' 'Housing INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES IMM,887H','Brace Holder INSERT INTO Products (Mfr, Product, Description, Price, etyOnHand) VALUES ('BIC',41089','Retainer' INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES 'ACI, 41001', "Size 1 widget' INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES (IMM, "775C'500 lb Brace' INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES 'ACI, 4100 'Widget Installer', 6.52, 3) ,2.50, 24) 1.34, 203) , 45.00, 12 ): , 1.48, 115 ): ..54, 223: ,2.25,78) .55, 277) , 14.25, 5 25.00, 28) Worksheet Query Builder INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES 'IMM', '775C' 500 lb Brace INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES'ACI, 41002' 'Widget Installer INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES C'QSA, 'XK48A', 'Reducer' INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES ('ACI, 41002', "Size 2 Widget' INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES'REI '2A44R 'Right Hinge' INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES (IMM, '773C'300 lb Brace' INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES ('ACI, 4100X', 'Widget Adjuster INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES 'FEA', 'Ll4'Motor Mount INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES ('IMM', '887X'Brace Retainer' INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES ('REI, 2A44G', 'Hinge Pin' INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES'FEA,'4103" 'Bar Clip' INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES ('EEA, 'XK48', 'Hose' INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES BIC, 'L14' Bowl' INSERT INTO Products (Mfr, Product, Description, Price, QtyOnHand) VALUES'RRC, 'L17' 'SQL Plus/Users Guide', 13.95, 42) COMMIT , 14.25,5: 25.00, 28 ,1.77,37 ,.76, 167 ,45.00, 12 ,9.75, 28 ,.25, 37 , 2.43,15 , 4.75, 32) , 3.50, 14 ): ,.05, 326): ,.17, 2 : , 3.95, 17)