SQL Assessment Questions:
The data model below shows relationships between tables. You will need to understand these tables to develop SQL queries that answer the questions. All columns except Primary Key (PK) columns should be assumed to be nullable.
Sample data for each of the tables (Sales, Product, SalesReturnReason) is shown below. A complete set of data is included in the attached Excel Spreadsheet. This spreadsheet data may be imported into your database of choice.

Question 1
Write a SQL Query that will return the total amount of sales by product category, for the month of December 2013.
• Your query should return 2 columns: ProductCategory, SalesAmount.
• Your query should produce a 2-row result set, including the following row and 1 additional row that is not shown:

Question 2
Write a SQL Query that returns the total amount of returns for reasons that were our fault (OurFault = “Y”) regardless of whether the items were physically returned (Returned = “Y”)
• Your query should return one column: Sales.
• Your query should produce a 1-row result set looking like the following:

Question 3
Write a SQL Query that will show total sales for the top 4 product subcategories, including ties (see note below) based on 2013 sales.
• A NULL ProductSubCategory should be listed as “Unknown”.
• In the event of a tie within the top 4, both records should be included.
• Your query should return 3 columns: ProductSubCategory, Sales and SalesRank.
• Your query should produce a 4-row result set including the following 2 records and 2 additional rows that are not shown:

Question 4
Write a SQL Query that will show all transactions where a customer purchased a Hydration Pack (ProductSubCategory = " Hydration Packs") and their previous purchase -- based on TransactionTimestamp -- was a Water Bottle or Cage (ProductSubCategory = "Bottles and Cages").
• Your query should return 6 columns: TransactionID, CustomerName, ProductSubCategory, PriorProductSubCategory, TransactionTimestamp, and SalesAmount.
• Your query should produce a 3 row result set that includes these 2-rows and 1 additional row that is not shown:

Question 5
Write a SQL Query that will return total sales by product category and year.
• Write the SQL query without using a CASE statement
• A NULL ProductCategory should be listed as “Unknown”.
• Your query should return 4 columns: ProductCategory, 2012, 2013, and 2014. Each of the year columns will show the total sales amount for that year.
• Your query should produce a 4-row result set including the 2 rows below, and 2 additional rows that are not shown:

Question

SQL Assessment Questions:
The data model below shows relationships between tables.  You will need to understand these tables to develop SQL queries that answer the questions.  All columns except Primary Key (PK) columns should be assumed to be nullable.    
Sample data for each of the tables (Sales, Product, SalesReturnReason) is shown below.  A complete set of data is included in the attached Excel Spreadsheet.   This spreadsheet data may be imported into your database of choice.

Question 1  
Write a SQL Query that will return the total amount of sales by product category, for the month of December 2013.  
•	Your query should return 2 columns: ProductCategory, SalesAmount. 
•	Your query should produce a 2-row result set, including the following row and 1 additional row that is not shown:

Question 2 
Write a SQL Query that returns the total amount of returns for reasons that were our fault (OurFault = “Y”) regardless of whether the items were physically returned (Returned = “Y”)
•	Your query should return one column: Sales. 
•	Your query should produce a 1-row result set looking like the following:

Question 3
Write a SQL Query that will show total sales for the top 4 product subcategories, including ties (see note below) based on 2013 sales.  
•	A NULL ProductSubCategory should be listed as “Unknown”.
•	In the event of a tie within the top 4, both records should be included.
•	Your query should return 3 columns: ProductSubCategory, Sales and SalesRank. 
•	Your query should produce a 4-row result set including the following 2 records and 2 additional rows that are not shown:

Question 4
Write a SQL Query that will show all transactions where a customer purchased a Hydration Pack (ProductSubCategory = " Hydration Packs") and their previous purchase -- based on TransactionTimestamp -- was a Water Bottle or Cage (ProductSubCategory = "Bottles and Cages").  
•	Your query should return 6 columns:  TransactionID, CustomerName, ProductSubCategory, PriorProductSubCategory, TransactionTimestamp, and SalesAmount.
•	Your query should produce a 3 row result set that includes these 2-rows and 1 additional row that is not shown:
 
Question 5
Write a SQL Query that will return total sales by product category and year. 
•	Write the SQL query without using a CASE statement
•	A NULL ProductCategory should be listed as “Unknown”.
•	Your query should return 4 columns: ProductCategory, 2012, 2013, and 2014.   Each of the year columns will show the total sales amount for that year.
•	Your query should produce a 4-row result set including the 2 rows below, and 2 additional rows that are not shown:

Anoniem · Accepted Answer

Answer 1
SELECT Product.ProductCategory,
    SUM(Sales.SalesAmount) AS SalesAmount
  FROM Sales LEFT OUTER JOIN
    Product ON Sales.ProductID = Product.ProductID
  WHERE Product.ProductCategory IS NOT NULL AND 
    (MONTH(Sales.TransactionTimestamp) = 12) AND (YEAR(Sales.TransactionTimestamp) = 2013)
  GROUP BY Product.ProductCategory

Answer 2
SELECT SUM(Sales.SalesAmount) AS Sales
  FROM Sales
  WHERE Sales.TransactionID IN
    (SELECT DISTINCT SalesReturnReason.TransactionID 
      FROM SalesReturnReason
      WHERE SalesReturnReason.OurFault = 'Y')

Answer 3
SELECT TOP 4 CASE WHEN Product .ProductSubCategory IS NULL THEN 'Unknown' ELSE Product .ProductSubCategory END AS ProductSubCategory, 
    SUM(dbo.Sales.SalesAmount) AS Sales,
    RANK() OVER (ORDER BY SUM(Sales.SalesAmount) DESC) AS Rank
  FROM Product LEFT OUTER JOIN
    Sales ON Product.ProductID = Sales.ProductID
  WHERE (YEAR(Sales.TransactionTimestamp) = 2013) 
  GROUP BY Product.ProductSubCategory
  ORDER BY SUM(Sales.SalesAmount) DESC

Answer 4
SELECT Sales.TransactionID, 
    Customer.CustomerName, 
	Product.ProductSubCategory,
	(SELECT TOP (1) PriorProduct.ProductSubCategory AS PriorProductSubCategory
	  FROM Sales AS PriorSales LEFT OUTER JOIN
	    Product AS PriorProduct ON PriorSales.ProductID = PriorProduct.ProductID
      WHERE PriorSales.CustomerID = Sales.CustomerID AND PriorSales.TransactionTimestamp < Sales.TransactionTimestamp
      ORDER BY PriorSales.TransactionTimestamp DESC) AS PriorProductSubCategory, 
	Sales.TransactionTimestamp, Sales.SalesAmount
  FROM Sales LEFT OUTER JOIN
    Product ON Sales.ProductID = dbo.Product.ProductID LEFT OUTER JOIN
	Customer ON Sales.CustomerID = Customer.CustomerID
  WHERE Product.ProductSubCategory = 'Hydration Packs' AND
    (SELECT TOP (1) PriorProduct.ProductSubCategory AS PriorProductSubCategory
	  FROM Sales AS PriorSales LEFT OUTER JOIN
	    Product AS PriorProduct ON PriorSales.ProductID = PriorProduct.ProductID
	  WHERE PriorSales.CustomerID = Sales.CustomerID AND PriorSales.TransactionTimestamp < Sales.TransactionTimestamp
      ORDER BY PriorSales.TransactionTimestamp DESC) = 'Bottles and Cages'
  ORDER BY Customer.CustomerID ASC

Answer 5
SELECT COALESCE (ProductCategory, 'Unknown') AS ProductCategory, 
    COALESCE ((SELECT SUM(Sales2012.SalesAmount) AS '2012'
	  FROM Sales AS Sales2012 LEFT OUTER JOIN
        Product AS Product2012 ON Sales2012.ProductID = Product2012.ProductID
	  WHERE YEAR(Sales2012.TransactionTimestamp) = 2012 AND 
	    COALESCE (Product2012.ProductCategory, 'Unknown') = COALESCE (dbo.Product.ProductCategory, 'Unknown')), 0) AS '2012',
    COALESCE ((SELECT SUM(Sales2013.SalesAmount) AS '2013'
	  FROM Sales AS Sales2013 LEFT OUTER JOIN
        Product AS Product2013 ON Sales2013.ProductID = Product2013.ProductID
	  WHERE YEAR(Sales2013.TransactionTimestamp) = 2013 AND 
	    COALESCE (Product2013.ProductCategory, 'Unknown') = COALESCE (dbo.Product.ProductCategory, 'Unknown')), 0) AS '2013',
    COALESCE ((SELECT SUM(Sales2014.SalesAmount) AS '2014'
	  FROM Sales AS Sales2014 LEFT OUTER JOIN
        Product AS Product2014 ON Sales2014.ProductID = Product2014.ProductID
	  WHERE YEAR(Sales2014.TransactionTimestamp) = 2014 AND 
	    COALESCE (Product2014.ProductCategory, 'Unknown') = COALESCE (dbo.Product.ProductCategory, 'Unknown')), 0) AS '2014'
  FROM Product
  GROUP BY ProductCategory
  ORDER BY ProductCategory

Centric Consulting

Sollicitatievraag bij Centric Consulting

Antwoord op sollicitatievraag

Gevolgde bedrijven

Vacatures