Urgent help needed SQL Queries! thanx :) Queries • (10 points) Write a query tha

Question

Urgent help needed SQL Queries! thanx :)

Queries

• (10 points) Write a query that returns the total sales in 2015 for the Stock Item “USB food flash drive – donut”. Leverage the extended price column for the calculation and the invoice date to determine what year the product was sold (Invoice tables).

• You should get $74,372.80

• (15 points) Write a query that returns how many customers we have in each state. Customers can be found in the Sales.Customers database, city and state information in the Application.StateProvinces and Application.Cities tables. Use the DeliveryCityID column in Customers to make your joins. Your query should return all states – 53 records and should be ordered from the most to the least number of customers. Return the full state name, not the two-letter code

• Delaware has 0 customers, Texas has 46

• (15 points) Write a query to return a list of people with duplicated email addresses in the Application.People table. Your query needs to return the person id, first name and last name (in separate columns based on the full name column), email address and phone number. Sort the list by email address and last name.

• 158 records should be returned

• Hint – you need to first identify the duplicate email addresses, then join these results back into the People table (derived table).

• One of the duplicate emails - teresa@wingtiptoys.com

Explanation / Answer

Query 1 : Total sales in 2015

Assuming the table name is "Invoice" table. and there is a column for stock item, and other column names mentioned above :-

Select Sum(inv.extended_price) as totalSale, inv.stock_item from sales.Invoice inv where inv.Invoice_Date in between('01/01/2015','12/31/2015') group by inv.stock_item having inv.stock_item ='USB food flash drive – donut'

Query 2 ::> The column names in the bracket [ ] are assumed

Select Count(cus.customer) as CustomerCount, st.[State_Long_Name] from Sales.Customers cus inner join Application.Cities cit on cus.DeliveryCityID = cit.[CityID] inner join Application.StateProvinces st on cit.[foreignkeyof State table] = st.[Primary key of state table] Group by st.[State_Long_Name] order by Count(cus.customer) Desc

Query 3 :>  

First create a temporary table Suppose #Temp. Then store all the duplicate email IDs in the temporary table.

Select Email_ID, Count(Email_ID) as email_count into #Temp from  Application.People group by Email_ID having Count(Email_ID) >1

Now all the duplicate email addresses are stored in #Temp So, join the #Temp to Application.People to extract other informations.

Select pp.Person_ID, pp.First_Name, pp.Last_Name, pp.Email_ID, pp.Phone_Number from  Application.People pp Inner Join #Temp t on pp.Email_ID = t.Email_ID order by pp.Email_ID, pp.Last_Name

Hope these help, Incase of any queries ... Let me know.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.