Question

Using the Heather Sweeney SQL script, create and add values provided in Titanium in order to create the HSD Database. Write and save SQL statements using a text editor (e.g., Notepad) to answer the query requests below. Although you only submit the SQL text file, you may want to test and verify the SQL statements using MS SQL Server 2012. Use comment lines as descriptors for each different SQL statement.

The text file should contain the following SQL query requests

1.List the various product numbers only once in the line item table.

2.List the number of products ordered in line item by product number.

3.List the first name, last name, and number of contacts made for each customer who live in Dallas regardless of contact type .

4.List the first name, last name and phone number for customers who purchased the video companion titled ‘Kitchen Remodeling Basics’. Use a subquery.

5. List the number of invoices for each email address where the payment is made using Mastercard and the total invoice amount is less than $100.00.

6.Use the previous SQL statement to show all records regardless of payment type or amount with 2 or more invoices.

7.Verify that the total listed in line item is accurate and correct by creating a calculated field and displaying it next to the original total field.

8.Use previous SQL statement and modify it to show grand totals by invoice number.

9.List the total number of transactions by payment type greater than 8.

10. List the grand total for each customer making payments with their Visa cards.

Answer 1

Check the explanation

Step-by-step explanation:

1. SELECT DISTINCT ProductNumber FROM LINE_ITEM

2. SELECT ProductNumber,count(ProductNumber) FROM LINE_ITEM WHERE ProductNumber is not null

group by ProductNumber

3. SELECT LastName, FirstName, Phone FROM CUSTOMER WHERE City = 'Dallas'

5.

SELECT count(i.*) FROM Invoice i

Inner Join Line_Item li on li.invoiceNumber=i.invoicenumber

WHERE li.total<100 and i.paymenttype='Mastercard'