- PRACTICE QUESTIONS ON ADVENTUREWORKS DATABASE UPDATE
- PRACTICE QUESTIONS ON ADVENTUREWORKS DATABASE SERIES
I adapted a question that a book I have presented. Does anyone know of a list of questions out there for this database?I am particularly interested in correlated subqueries. I have downloaded the AdventureWorks database, but I could do with some example questions, the kind I would be asked in a real world business. All ForumsĪdventureWorks practise questions - esp correlated We've got lots of great SQL ServerĮxperts to answer whatever question you can come up with.
PRACTICE QUESTIONS ON ADVENTUREWORKS DATABASE UPDATE
Therefore, to verify that your query works as expected, run the following UPDATE statement to remove some existing ship dates before creating your query IMPORTANT: In the sample data provided in AdventureWorksLT, there are no sales order header records without a ship date. SELECT CustomerID, COALESCE(EmailAddress, Phone) AS Primar圜ontact FROM SalesLT.Customer Ĩ.You have been asked to create a query that returns a list of sales order IDs and order dates with a column named ShippingStatus that contains the text “Shipped” for orders with a known ship date, and “Awaiting Shipment” for orders with no ship date. When EmailAddress is not null then emailaddressĮnd as 'primary contact' FROM SalesLT.Customer UPDATE SalesLT.Customer SET EmailAddress = NULL WHERE CustomerID % 7 = 1 Therefore, to verify that your query works as expected, run the following UPDATE statement to remove some existing email addresses before creating your query IMPORTANT: In the sample data provided in AdventureWorksLT, there are no customer records without an email address.
You must write a query that returns a list of customer IDs in one column, and a second column named Primar圜ontact that contains the email address if known, and otherwise the phone number. If an email address is available, then it should be used as the primary contact method if not, then the phone number should be used. SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '')+ LastName AS CustomerNameFROM SalesLT.Customer Ĭustomers may provide adventure Works with an email address, a phone number, or both. Gates) if a middle name is stored in the database. The list must consist of a single field in the format (for example Keith Harris) if the middle name is unknown, or (for example Jane M. Retrieve customer contact names with middle names if known You have been asked to write a query that returns a list of customer names. SELECT SalesOrderNumber + ' (' + STR(RevisionNumber, 1) + ')' AS OrderRevision, CONVERT(nvarchar(30), OrderDate, 102) AS OrderDateĦ. The order date converted to ANSI standard format (yyyy.mm.dd – for example 2015.01.31). The sales order number and revision number in the format () – for example SO71774 (2). You have been asked to retrieve data for a report that shows: SELECT CAST(CustomerID AS varchar) + ': ' + CompanyName AS CustomerCompany FROM SalesLT.Customer ĥ.Retrieve a list of sales order revisions The SalesLT.SalesOrderHeader table contains records of sales orders. You have been asked to provide a list of all customer companies in the format SELECT Salesperson, Title + ' ' + LastName AS CustomerName, Phone FROM SalesLT.Customer
(ii)A column named CustomerName that displays how the customer contact should be greeted (for example, “Mr Smith”) You must write a query to create a call sheet that lists: Retrieve customer names and phone numbers Each customer has an assigned salesperson. SELECT Title, FirstName, MiddleName, LastName, Suffix FROM SalesLT.Customer ģ. Retrieve customer name data Create a list of all customer contact names that includes the title, first name, middle name (if any), last name, and suffix (if any) of all customers. Retrieve customer details Familiarize yourself with the Customer table by writing a Transact-SQL query that retrieves all columns for all customers.Ģ. Note: These are available on the internet but not available at single place hence I made an effort to bring them all together.ġ. The main theme of this is to help the beginners to enhance their T-SQL capabilities on a phase by phase manner.
PRACTICE QUESTIONS ON ADVENTUREWORKS DATABASE SERIES
I am writing a series of posts on AdventureWorksLT database which can be downloaded from the link AdventureworksLT2012.