Please use sql to answer the following question an erd also provide as the picture shown COMPI22 W21 ERS 100ASGM1 ORDER ITEMSPF. ORDER IDP TEM IDF PRODUCT IDQUANTITY* UNIT PRICENUMBER (12)NUMBER (12)NUMBER (12)NUMBER (8,2)NUMBER (82)COMPI22 W21 ERS 100ASGM1 ORDERSP. ORDER IDF CUSTOMER_IDSTATUSCOMPI22 W21 ERS 100 ASGM1 EMPLOYEESP. EMPLOYEE ID* FIRST NAMELAST NAMEEMAIL• PHONE* HIRE DATEMANAGER IDJOB TITLENUMBERNUMBERVARCHAR2 (255 BYTE)VARCHAR2 (255 BYTE)VARCHAR2 (255 BYTE)VARCHAR2 (50 BYTE)DATENUMBER (12)VARCHAR2 (255 BYTE)NUMBER (6)VARCHAR2 (20 BYTE)NUMBER (6)DATESALESMAN ID* ORDER DATEPK ASGM1 ORDER_ITEMS (ORDER_ID, ITEM_ID)O FK ORDER_ITEMS_ORDERS (ORDER_ID)FK ORDER_ITEMS PRODUCTS (PRODUCT_ID)O PK ASGM1 ORDER_ITEMS (ORDER_ID, ITEM_ID)ASGM1 ORDERS PK (ORDER ID)O FK ORDERS_CUSTOMERS (CUSTOMER_ID)FK ORDERS EMPLOYEES (SALESMAN_D)ASGM1 EMPLOYEES PK (EMPLOYEE ID)Og FK EMPLOYEES MANAGER (MANAGER_ID)COMP122 W21 ERS 100.ASGM1_INVENTORIESPF PRODUCT_IDPF WAREHOUSE ID* QUANTITYNUMBER (12)NUMBER (12)NUMBER (8)PK ASGM1_INVENTORIES (PRODUCT_ID, WAREHOUSE ID)FK INVENTORIES PRODUCTS (PRODUCT_ID)FK_INVENTORIES_WAREHOUSES (WAREHOUSE ID)O PK ASGM1_INVENTORIES (PRODUCT_ID, WAREHOUSE ID)COMP122_W21_ERS_100 ASGM 1_CUSTOMERSP CUSTOMER_ID NUMBERNAMEVARCHAR2 (255 BYTE)VARCHAR2 (255 BYTE)VARCHAR2 (255 BYTE)NUMBER (8,2)COMP122 W21_ERS_100 ASGM1_PRODUCTSP PRODUCT_D* PRODUCT NAMEDESCRIPTIONADDRESSWEBSITECREDIT LIMITNUMBERVARCHAR2 (255 BYTE)VARCHAR2 (2000 BYTE)NUMBER (92)NUMBER (92)NUMBERASGM1 CUSTOMERS PK (CUSTOMER_ID)STANDARD COoSTLIST PRICE• CATEGORY IDASGM1_PRODUCTS PK (PRODUCT_ID)FK ASGM1 PRODUCTS CATEGORIES (CATEGORY ID)COMP122 W21 ERS 100 ASGM1 WAREHOUSESCOMP122 W21 ERS 100.ASGM1 CONTACTSP. WAREHOUSE IDWAREHOUSE NAMELOCATION_IDP CONTACT ID* FIRST_NAMELAST NAMEEMAILPHONECUSTOMER IDNUMBERVARCHAR2 (255 BYTE)VARCHAR2 (255 BYTE)VARCHAR2 (255 BYTE)VARCHAR2 (20 BYTE)NUMBERNUMBERVARCHAR2 (255 BYTE)NUMBER (12)IFa ASGM1_WAREHOUSES PK (WAREHOUSE ID)COMP122 W21_ERS 100 ASGM1_PRODUCT CATEGORIESP. CATEGORY ID• CATEGORY NAMEFK _WAREHOUSES_LOCATIONS (LOCATION_ID)NUMBERVARCHAR2 (255 BYTE)> ASGM1 CONTACTS_PK (CONTACT_ID)FK CONTACTS CUSTOMERS (CUSTOMER_ID)ASGM1_PRODUCT CATEGORIES_PK (CATEGORY_ID)COMP122 W21 ERS 100 ASGM1 LOCATIONSP. LOCATION IDADDRESSPOSTAL CODENUMBERVARCHAR2 (255 BYTE)VARCHAR2 (20 BYTE)VARCHAR2 (50 BYTE)VARCHAR2 (50 BYTE)CHAR (2 BYTE)COMP122 W21 ERS 100.ASGM1 COUNTRIESCHAR (2 BYTE)VARCHAR2 (40 BYTE)NUMBERCOMP122 W21 ERS 100 ASGM1 REGIONSP. REGION IDREGION NAMECOUNTRY IDCOUNTRY NAMECITYNUMBERSTATEVARCHAR2 (50 BYTE)REGION IDCOUNTRY_ID- ASGM1_COUNTRIES_PK (COUNTRY_ID)a ASGM1 REGIONS PK (REGION_ID)ASGM1 LOCATIONS PKLOCATION IDA 4. Display customer ID, customer name, and the order ID and the order date of allorders for customer whose ID is 44.a. Show also the total quantity and the total amount of each customer's order.b. Sort the result from the highest to lowest total order amount.Cutomer 1d NaneOrder 1d order DateTotal heme tot Aurt44 Jabil Cireuit$1,050,939.97$755, 093.92$620, 962.99$508, 588.59$398,636.259228-OCT-1569 17-MAR-1779058144 Jabil Circuit44 Jabil cireuit1024-JAN-172914-AUG-1788383144 Jabil Cireuit44 Jabil Cireuit8203-DEC-166875. Display customer Id, name, total number of orders, the total number of itemsordered, and the total order amount for customers who have more than 30 orders.Sort the result based on the total number of orders.Cuntomer 14) Name47 General Mills8 International Paper49 NextEra Energy9 Emerson Electric44 Jabil cireuitTotal Number of Orders Total kems Total Amount3116$3,725, 138.14$2, 642,238.04$2, 452, 508.95$2, 893, 564.97$3,334, 221. 7233352328133513301373745ב377

4) SELECT c.CUSTOMER_ID, c.NAME, o.ORDER_ID, o.ORDER_DATE, oi.QUANTITY AS "Total Items",…

4. Display customer ID, customer name, and the order ID and the order date of all orders for customer whose ID is 44. a. Show also the total quantity and the total amount of each customer's order. b. Sort the result from the highest to lowest total order amount. Cuntoner ) Nare l0 order ta ) order Date 9228-OCT-15 $1,050, 939.97 $755, 093.92 $620, 962.99 $508, 508.59 $390, 636.25 44 Jabil cireuit 44 Jabil cireuit 790 44 Jabil cireuit 44 Jabil Cireuit 69 17-MAR-17 1024-JAN-17 581 803 44 Jabil cireuit 29 14-AUG-17 8203-DEC-16 831 687 5. Display customer Id, name, total number of orders, the total number of items ordered, and the total order amount for customers who have more than 30 orders. Sort the result based on the total number of orders. Cuatoner le) Name 47 General Mills 8 International Paper 49 NextEra Energy 9 Emerson Electric Total Nanber of Orders) Total Rena ) Total Amourt 33 3116 3201 3351 3301 $3, 725, 130.14 $2, 642,238.04 $2, 452, 508.95 $2, 893, 564.97 $3,334, 221. 72 35 37 37 44 Jabil cireuit 45 3772

4. Display customer ID, customer name, and the order ID and the order date of all orders for customer whose ID is 44. a. Show also the total quantity and the total amount of each customer's order. b. Sort the result from the highest to lowest total order amount. Cuntoner ) Nare l0 order ta ) order Date 9228-OCT-15 $1,050, 939.97 $755, 093.92 $620, 962.99 $508, 508.59 $390, 636.25 44 Jabil cireuit 44 Jabil cireuit 790 44 Jabil cireuit 44 Jabil Cireuit 69 17-MAR-17 1024-JAN-17 581 803 44 Jabil cireuit 29 14-AUG-17 8203-DEC-16 831 687 5. Display customer Id, name, total number of orders, the total number of items ordered, and the total order amount for customers who have more than 30 orders. Sort the result based on the total number of orders. Cuatoner le) Name 47 General Mills 8 International Paper 49 NextEra Energy 9 Emerson Electric Total Nanber of Orders) Total Rena ) Total Amourt 33 3116 3201 3351 3301 $3, 725, 130.14 $2, 642,238.04 $2, 452, 508.95 $2, 893, 564.97 $3,334, 221. 72 35 37 37 44 Jabil cireuit 45 3772

Programming Logic & Design Comprehensive

9th Edition

ISBN:9781337669405

Author:FARRELL

Publisher:FARRELL

Chapter8: Advanced Data Handling Concepts

Section: Chapter Questions

Problem 17RQ

See similar textbooks

Similar questions

Computer Science The following script uses a derived table to return he date and invoice total of the earliest invoice issued by each vendor. Write a script that generates the same results set but uses a temporary table in place of the derived table. Make sure your script tests for the existence of any objects it creates. Use itse2333; SELECT VendorName, FirstInvoiceDate, InvoiceTotal FROM Invoices JOIN (SELECT VendorID, MIN(InvoiceDate) as FirstInvoiceDate FROM invoices GROUP BY VendorID) as FirstInvoices ON (Invoices.VendorID = FirstInvoice.FirstInvoice.VendorID and Invoices.InvoiceDate = First.Invoice.FirstInvoiceDate) JOIN Vendors ON Invoices.VendorID = Vendor.VendorID ORDER BY VendorName, FirstInvoiceDate);
create database GA2gouse GA2gocreate table salesman(salesman_id int primary key, name varchar(30), city varchar(20), commission varchar(30));insert into salesman (salesman_id, name, city, commission) values (5001, 'James Hoog', 'New York', 0.15); insert into salesman (salesman_id, name, city, commission) values (5002, 'Nail Knite', 'Paris', 0.13); insert into salesman (salesman_id, name, city, commission) values (5005, 'Pit Alex', 'London', 0.11); insert into salesman (salesman_id, name, city, commission) values (5006, 'Mc Lyon', 'Paris', 0.14); insert into salesman (salesman_id, name, city, commission) values (5007, 'Paul Adam', 'Rome', 0.13); insert into salesman (salesman_id, name, city, commission) values (5003, 'Lauson Hen', 'San Jose', 0.12); Create table Customer (customer_id int primary key, cust_name varchar(30), city varchar(10), grade varchar(30), salesman_id varchar(30)(select salesman_id from salesman); why does my sql server code not work, at the customer table?
create database GA2gouse GA2gocreate table salesman(salesman_id int primary key, name varchar(30), city varchar(20), commission varchar(30));insert into salesman (salesman_id, name, city, commission) values (5001, 'James Hoog', 'New York', 0.15); insert into salesman (salesman_id, name, city, commission) values (5002, 'Nail Knite', 'Paris', 0.13); insert into salesman (salesman_id, name, city, commission) values (5005, 'Pit Alex', 'London', 0.11); insert into salesman (salesman_id, name, city, commission) values (5006, 'Mc Lyon', 'Paris', 0.14); insert into salesman (salesman_id, name, city, commission) values (5007, 'Paul Adam', 'Rome', 0.13); insert into salesman (salesman_id, name, city, commission) values (5003, 'Lauson Hen', 'San Jose', 0.12); Create table Customer (customer_id int primary key, cust_name varchar(30), city varchar(10), grade varchar(30), salesman_id int,CONSTRAINT FK_salesman_id FOREIGN KEY (salesman_id) REFERENCES salesman(salesman_id));insert into customer…
create database GA2gouse GA2gocreate table salesman(salesman_id int primary key, name varchar(30), city varchar(20), commission varchar(30));insert into salesman (salesman_id, name, city, commission) values (5001, 'James Hoog', 'New York', 0.15); insert into salesman (salesman_id, name, city, commission) values (5002, 'Nail Knite', 'Paris', 0.13); insert into salesman (salesman_id, name, city, commission) values (5005, 'Pit Alex', 'London', 0.11); insert into salesman (salesman_id, name, city, commission) values (5006, 'Mc Lyon', 'Paris', 0.14); insert into salesman (salesman_id, name, city, commission) values (5007, 'Paul Adam', 'Rome', 0.13); insert into salesman (salesman_id, name, city, commission) values (5003, 'Lauson Hen', 'San Jose', 0.12); Create table Customer (customer_id int primary key, cust_name varchar(30), city varchar(10), grade varchar(30), salesman_id varchar(30),CONSTRAINT FK_salesman_id FOREIGN KEY (salesman_id) REFERENCES salesman(salesman_id)); i keep getting…
create database GA2gouse GA2gocreate table salesman(salesman_id int primary key, name varchar(30), city varchar(20), commission varchar(30));insert into salesman (salesman_id, name, city, commission) values (5001, 'James Hoog', 'New York', 0.15); insert into salesman (salesman_id, name, city, commission) values (5002, 'Nail Knite', 'Paris', 0.13); insert into salesman (salesman_id, name, city, commission) values (5005, 'Pit Alex', 'London', 0.11); insert into salesman (salesman_id, name, city, commission) values (5006, 'Mc Lyon', 'Paris', 0.14); insert into salesman (salesman_id, name, city, commission) values (5007, 'Paul Adam', 'Rome', 0.13); insert into salesman (salesman_id, name, city, commission) values (5003, 'Lauson Hen', 'San Jose', 0.12); Create table Customer (customer_id int primary key, cust_name varchar(30), city varchar(10), grade varchar(30), salesman_id int,CONSTRAINT FK_salesman_id FOREIGN KEY (salesman_id) REFERENCES salesman(salesman_id));insert into customer…
create database GA2gouse GA2gocreate table salesman(salesman_id int primary key, name varchar(30), city varchar(20), commission varchar(30));insert into salesman (salesman_id, name, city, commission) values (5001, 'James Hoog', 'New York', 0.15); insert into salesman (salesman_id, name, city, commission) values (5002, 'Nail Knite', 'Paris', 0.13); insert into salesman (salesman_id, name, city, commission) values (5005, 'Pit Alex', 'London', 0.11); insert into salesman (salesman_id, name, city, commission) values (5006, 'Mc Lyon', 'Paris', 0.14); insert into salesman (salesman_id, name, city, commission) values (5007, 'Paul Adam', 'Rome', 0.13); insert into salesman (salesman_id, name, city, commission) values (5003, 'Lauson Hen', 'San Jose', 0.12); Create table Customer (customer_id int primary key, cust_name varchar(30), city varchar(10), grade varchar(30), salesman_id varchar(30),CONSTRAINT FK_salesman_id FOREIGN KEY (salesman) REFERENCES salesman(salesman_id)); i keep getting this…
create database GA2gouse GA2gocreate table salesman(salesman_id int primary key, name varchar(30), city varchar(20), commission varchar(30));insert into salesman (salesman_id, name, city, commission) values (5001, 'James Hoog', 'New York', 0.15); insert into salesman (salesman_id, name, city, commission) values (5002, 'Nail Knite', 'Paris', 0.13); insert into salesman (salesman_id, name, city, commission) values (5005, 'Pit Alex', 'London', 0.11); insert into salesman (salesman_id, name, city, commission) values (5006, 'Mc Lyon', 'Paris', 0.14); insert into salesman (salesman_id, name, city, commission) values (5007, 'Paul Adam', 'Rome', 0.13); insert into salesman (salesman_id, name, city, commission) values (5003, 'Lauson Hen', 'San Jose', 0.12); Create table Customer (customer_id int primary key, cust_name varchar(30), city varchar(10), grade varchar(30), salesman_id int,CONSTRAINT FK_salesman_id FOREIGN KEY (salesman_id) REFERENCES salesman(salesman_id));insert into customer…
create database GA2gouse GA2gocreate table salesman(salesman_id int primary key, name varchar(30), city varchar(20), commission varchar(30));insert into salesman (salesman_id, name, city, commission) values (5001, 'James Hoog', 'New York', 0.15); insert into salesman (salesman_id, name, city, commission) values (5002, 'Nail Knite', 'Paris', 0.13); insert into salesman (salesman_id, name, city, commission) values (5005, 'Pit Alex', 'London', 0.11); insert into salesman (salesman_id, name, city, commission) values (5006, 'Mc Lyon', 'Paris', 0.14); insert into salesman (salesman_id, name, city, commission) values (5007, 'Paul Adam', 'Rome', 0.13); insert into salesman (salesman_id, name, city, commission) values (5003, 'Lauson Hen', 'San Jose', 0.12); Create table Customer (customer_id int primary key, cust_name varchar(30), city varchar(10), grade varchar(30), salesman_id int,CONSTRAINT FK_salesman_id FOREIGN KEY (salesman_id) REFERENCES salesman(salesman_id));insert into customer…
Creat a query for the following in miscrosoft access d. Produce the total payments received by each business on behalf of their employees. Name this query Q4 – TotalByBusiness. e. Create a query that prompts you (the user) for your first/last name and produces your client number, your first and last names, the salary deduction along with the total premium. Also include a calculated field named ‘Excess’ to subtract the total premium from your salary deduction.
Course: Database *(SQL)* Please excute the given SQL script (https://drive.google.com/file/d/1zxe_aOhERjVCL54_zbgSLkFTRHYQhOPW/view?usp=sharing) for accessing the data. The data is described in the following relation schemas: Airport (airportID, name, city) Passenger (ticketNo, name, nationality, flightNo, seatNo)FK: flightNo references Flight (flightNo)FK: seatNo references Seat (seatNo) Flight (flightNo, flightCompany, departAirport, arrivalAirport)FK: departAirport references Airport (airportID)FK: arrivalAirport references Airport (airportID) Seat (seatNo, flightNo, class)FK: flightNo references Flight (flightNo) #Construct the SQL statements based on following transactions: Transaction SQL Statement Retrieve the flight number, departure and arrival airports of all British Airways. Retrieve the name of every passenger together with their flight number and the associated company. Retrieve all flights departed from all airports in London. Retrieve the ticket numbers…
Course: Database *(SQL)* Please excute the given SQL script (https://drive.google.com/file/d/1zxe_aOhERjVCL54_zbgSLkFTRHYQhOPW/view?usp=sharing) for accessing the data. The data is described in the following relation schemas: Airport (airportID, name, city) Passenger (ticketNo, name, nationality, flightNo, seatNo)FK: flightNo references Flight (flightNo)FK: seatNo references Seat (seatNo) Flight (flightNo, flightCompany, departAirport, arrivalAirport)FK: departAirport references Airport (airportID)FK: arrivalAirport references Airport (airportID) Seat (seatNo, flightNo, class)FK: flightNo references Flight (flightNo) #Construct the SQL statements based on following transactions:1. Retrieve all rows in Airport table for all the airports in London city.2. Retrieve all British and German passengers.3. Retrieve all names of all the passengers.4. Retrieve the flight number, departure and arrival airports of all British Airways.5. Retrieve the name of every passenger together with their…
Course: Database *(SQL)* Please excute the given SQL script (https://drive.google.com/file/d/1zxe_aOhERjVCL54_zbgSLkFTRHYQhOPW/view?usp=sharing) for accessing the data. The data is described in the following relation schemas: Airport (airportID, name, city) Passenger (ticketNo, name, nationality, flightNo, seatNo)FK: flightNo references Flight (flightNo)FK: seatNo references Seat (seatNo) Flight (flightNo, flightCompany, departAirport, arrivalAirport)FK: departAirport references Airport (airportID)FK: arrivalAirport references Airport (airportID) Seat (seatNo, flightNo, class)FK: flightNo references Flight (flightNo) #Construct the SQL statements based on following transactions: Transaction SQL Statement Retrieve all rows in Airport table for all the airports in London city. Retrieve all British and German passengers. Retrieve all names of all the passengers. Retrieve the flight number, departure and arrival airports of all British Airways. Retrieve the name of…