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.
Adventureworks database practice problems series#
If yes, what is their phone number?Ī customer with a high-end store wants to know what the three most expensive wheels are for any bike.Ī customer, Gary Vargas from Exercise Center, wants to know what email we have on file for him.I am writing a series of posts on AdventureWorksLT database which can be downloaded from the link AdventureworksLT2012.
Adventureworks database practice problems code#
You will have to look at the data in the database and think about the query you need to code and result set you need to solve a problem.Ī customer comes in and asks if you carry medium sports shorts and what colors they come in.Ī customer asks if anybody at their company "Sharp Bikes" is a customer. The following questions will not specifically point out a table/columns that you need to work with. Sort the results by Color in descending order and then by Name in ascending order Select all of the Products that have a name containing "Mountain Frame" or "Road Frame" and have a Color of "Red". Using your solution from question 4, add another condition to narrow your previous results to only include Products with a weight over 9000. Select all of the Products that are Red and have a ListPrice between $500 and $1500 Select all the customers with a first name of "Mike" whose phone number starts with 454 Select all the customers who do not have a middle name. Sort the results by LastName, then FirstName, then CompanyName. Select the FirstName, LastName, and CompanyName of all Customers whose company name contains the word "Bike". Notice the schema name "SalesLT" in front of the table name? Questions
0 kommentar(er)
