Pergunta de entrevista da empresa Crowe LLP

Table 1: Analysis.dimProducts Column DataType ProductID INT(PK) Identity Name Varchar(100) Cost money Table 2: Analysis.dimCustomers Column DataType CustomerID Int (PK) IDENTITY FirstName Varchar(100) LastName Varchar(100) Table 3: Analysis.dimSales Column DataType SalesID INT (PK) IDENTITY ProductID INT (FK) CustomerID INT (FK) Quantity INT Questions 1. Write a query that would return the cost associated with a product of the name of “Shampoo” 2. Write a query that would return the first and last name (with one column as the return set instead of two) of all customers 3. Write a query that would return all sales associated with a customer with the first name of “Bob” and the last name of “Smith” 4. Write a query that would return the total number of products purchased by a customer with the first name of “Bob” and the last name of “Smith”. 5. Alter the query in question #4 to return the total number of products purchased by a customer with the first name of “Bob” and the last name of “Smith”, but still returning a value of zero if that name had no sales 6. Write a query that would return the amount of money made in revenue total based on the sales made of products. The result should be just one column. 7. Insert a new customer with the name of “Phil” and the last name of “Frank” 8. Delete a product with the name of “Shampoo” (make sure to incorporate all tables that would need to be validated before you do this delete) 9. Using joins, update the amount of the product “Shampoo” ordered by a customer with the name “Bob Smith” to 5 (assume there’s only one SaleID that represents this customer)