The following was previously given as an answer to part 3 of the question provided in the attached photos. I am needing
Posted: Thu Jul 14, 2022 2:11 pm
The following was previously given as an answer to part 3 of the question provided in the attached photos.
I am needing steps 1, 2, and 4 answered in order to have a completed question.
Please also format the below text correctly as instructed in the directions.
Option 1
SELECT CUSTNAME,CUSTPHONE FROM CUSTOMER WHERE CUSTID=1854;
Option 2
UPDATE EMPLOYEE SET DEPT="SHP013" WHERE FIRSTNAME="Gilbert" AND LASTNAME="Smith";
SELECT * FROM EMPLOYEE;
Option 3
SELECT COUNT(INVOICENUM),SUM(AMOUNT) FROM INVOICE WHERE BILLEDON<"10-Oct-17";
Option 4
SELECT COUNT(CUSTID) AS "Count_Customer" FROM CUSTOMER WHERE CUSTSTATE="CA" OR CUSTSTATE="NIC";
Option 5
SELECT DISTINCT(BILLEDON) ,SUM(AMOUNT) AS "SUM_AMOUNT" FROM INVOICE GROUP BY BILLEDON ORDER BY SUM_AMOUNT;
Option 6
SELECT COUNT(CUSTID) AS "CUST_COUNT" MAX(AMOUNT) AS ",MAX_AMOUNT" FROM CUSTOMER,INVOICE ORDER BY CUST_COUNT;
Homework #2 - SQL oints: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 ( 40pts). Steps are: (I) 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: - 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. CUSTOMER1 EMPLOYEE 1 INVOICE Amount real I Data Notes: . 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 am needing steps 1, 2, and 4 answered in order to have a completed question.
Please also format the below text correctly as instructed in the directions.
Option 1
SELECT CUSTNAME,CUSTPHONE FROM CUSTOMER WHERE CUSTID=1854;
Option 2
UPDATE EMPLOYEE SET DEPT="SHP013" WHERE FIRSTNAME="Gilbert" AND LASTNAME="Smith";
SELECT * FROM EMPLOYEE;
Option 3
SELECT COUNT(INVOICENUM),SUM(AMOUNT) FROM INVOICE WHERE BILLEDON<"10-Oct-17";
Option 4
SELECT COUNT(CUSTID) AS "Count_Customer" FROM CUSTOMER WHERE CUSTSTATE="CA" OR CUSTSTATE="NIC";
Option 5
SELECT DISTINCT(BILLEDON) ,SUM(AMOUNT) AS "SUM_AMOUNT" FROM INVOICE GROUP BY BILLEDON ORDER BY SUM_AMOUNT;
Option 6
SELECT COUNT(CUSTID) AS "CUST_COUNT" MAX(AMOUNT) AS ",MAX_AMOUNT" FROM CUSTOMER,INVOICE ORDER BY CUST_COUNT;
Homework #2 - SQL oints: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 ( 40pts). Steps are: (I) 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: - 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. CUSTOMER1 EMPLOYEE 1 INVOICE Amount real I Data Notes: . 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