Homework #2 - SQL Points:200 Assignment Instructions: Given the data provided create a database in SQL Server. Then writ

[answerhappygod]

Homework 2 Sql Points 200 Assignment Instructions Given The Data Provided Create A Database In Sql Server Then Writ 1 (61.51 KiB) Viewed 32 times

Homework 2 Sql Points 200 Assignment Instructions Given The Data Provided Create A Database In Sql Server Then Writ 2 (45.76 KiB) Viewed 32 times

Homework #2 - SQL Points:200 Assignment Instructions: Given the data provided create a database in SQL Server. Then write the statements: 1. (20 pts) The 'create' statements used to create each of your database tables. Be sure to include the primary key and foreign key designations. 2. (20 pts) One 'insert' statement for each of your database tables. Use the first row of each table (You need to have three insert queries in total). 3. Answer the following questions (20 pts each). 1) Give me the name and phone number of customer # 1854. 2) Change employee Gilbert Smith's department to SHP013. Display all of the information for the employees after the change. 3) List the number of invoices and the total dollar amount for invoices billed on or before October 10, 2017. 4) Count the number of Customers who come from CA or NC, and name it as Count_Customer. 5) Give me the different billed date, the sum of the total invoice amount (named as 'SUM_AMOUNT) on each billed date (use 'group by' function). Display the output by SUM AMOUNT from highest to lowest (use 'order by' function). 6) List the number of customers (named as *CUST_COUNT") and the maximized total invoice amount (named as 'MAX_AMOUNT) in each billed date. Display the output by CUST_COUNT from highest to lowest (use 'order by' function). 4. SQL results with headers (40 pts). Steps are: (1) Run the code and get the result (on the bottom of SQL Server), (2) Right click the result and choose 'select all. (3) right click again and choose "copy with headers", (4) Open HW word document and paste the copied result into word.
Deliverables: o The SQL statement for each question in a text format. Do not use screenshots for the SQL queries. Copy and paste from SQL Server into a text editor such as Word. CUSTI D (PK) 1432 CUSTSTAT E CA 1456 NC 1543 NC 1854 TX 1987 CA EMPID (PK) 12784 13567 21291 21638 23496 23499 INVOICENU M (PK) 11278 11280 11270 1432 11271 1456 11273 1456 CUSTI D (FK) 1456 1987 11282 1854 11286 1987 Amount real CUSTOMERI ZIP COD E CUSTPHON CUSTNAME E 95123 408-629-0589 Santa Clara Valley Technology 27514 619-489-6792 Precision Products 23501 616-582-3906 Great Lakes HIREDAT E 16-Jan-72 ACC024 Meder 15-Jul-89 ACCO13 Hemesly 15-Feb-16 CSR010 Wachberger 15-Feb-16 SHP013 Purinton 19-Dec-16 CSR004 Smith 19-Dec-16. SHP002 Smith 78701 512-478-0788 93274 209-686-3953 EMPLOYEE DEPT INVOICE AMOUNT 6870.00 63836.00 4148.50 11825.00 7870.00 LASTNAME FIRSTNAME GENDE R 2148.50 13836.00 EMPI D Equipme Manufacturers Lone Star State Research Suppliers San Joaquin Scientific Supply (FK) 21291 21638 23496 23499 13567 21291 21638 Juan M Courtney M Marie F Prudence F Gilbert M Courtney F BILLEDO PAIDON N 5-Sep-17 5-Sep-17 6-01-17 6-Oct-17 10-0c-17 10-Oct-17 20-Nov-17 11-Oct-17 11-Nov- 17 20-01-17 25-0c-17 1 Data Notes: 1. Leave all of the fields as is. The primary key is noted as (PK) and the foreign key is noted as (FK) 2 Blank cell with --- indicates a NULL value I