Querying with Transact-SQL_F2

1. Get the order ID and unit price for each order by joining the Orders table and the Order Details table.


2. Get the order ID and first name of the associated employee by joining the Orders and Employees tables.


3. Get the employee ID and related territory description for each territory an employee is in, by joining the Employees, EmployeeTerritories and Territories tables.


4. Select all the different countries from the Customers table and the Suppliers table using UNION.


5. Select all the countries, including duplicates, from the Customers table and the Suppliers table using UNION ALL.


6. Using the Products table, get the unit price of each product, rounded to the nearest dollar.


7. Using the Products table, get the total number of units in stock across all products.

8. Using the Orders table, get the order ID and year of the order by using YEAR(). Alias the year as OrderYear.


9. Using the Orders table, get the order ID and month of the order by using DATENAME(). Alias the month as OrderMonth.


10. Use LEFT() to get the first two letters of each region description from the Region table.

	--1. Get the order ID and unit price for each order by joining the Orders table and the Order Details table.
	SELECT o.OrderID, od.UnitPrice
	FROM dbo.Orders AS o
	JOIN dbo.[Order Details] AS od
	ON o.OrderID = od.OrderID;
	--2. Get the order ID and first name of the associated employee by joining the Orders and Employees tables.
	SELECT o.OrderID, e.FirstName
	FROM dbo.Orders AS o
	JOIN dbo.Employees AS e
	ON o.EmployeeID = e.EmployeeID;
	--3. Get the employee ID and related territory description for each territory an employee is in, by joining the Employees, EmployeeTerritories and Territories tables.
	SELECT e.EmployeeID, t.TerritoryDescription
	FROM dbo.Employees AS e
	JOIN dbo.EmployeeTerritories AS et
	ON e.EmployeeID = et.EmployeeID
	JOIN dbo.Territories AS t
	ON t.TerritoryID = et.TerritoryID
	ORDER BY e.EmployeeID, t.TerritoryDescription;
	--4. Select all the different countries from the Customers table and the Suppliers table using UNION.
	SELECT Country
	FROM dbo.Suppliers
	UNION
	SELECT Country
	FROM dbo.Customers
	ORDER BY Country;
	--5. Select all the countries, including duplicates, from the Customers table and the Suppliers table using UNION ALL.
	SELECT Country
	FROM dbo.Suppliers
	UNION ALL
	Select Country
	FROM dbo.Customers
	Order by Country;
	--6. Using the Products table, get the unit price of each product, rounded to the nearest dollar.
	SELECT ROUND(UnitPrice, 0)
	FROM dbo.Products;
	--7. Using the Products table, get the total number of units in stock across all products.
	SELECT SUM(UnitsInStock)
	FROM dbo.Products;
	--8. Using the Orders table, get the order ID and year of the order by using YEAR(). Alias the year as OrderYear.
	SELECT OrderID, YEAR(OrderDate) AS OrderYear
	FROM dbo.Orders;
	--9. Using the Orders table, get the order ID and month of the order by using DATENAME(). Alias the month as OrderMonth.
	SELECT OrderID, DATENAME(month, OrderDate) AS OrderMonth
	FROM dbo.Orders;
	--10. Use LEFT() to get the first two letters of each region description from the Region table.
	SELECT LEFT(RegionDescription, 2) AS RD
	FROM dbo.Region;

view raw final2.sql hosted with ❤ by GitHub

Weather Observation Station 3

Problem:

Query a list of CITY names from STATION with even ID numbers only. You may print the results in any order, but must exclude duplicates from your answer.

Input Format

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

	--The SQL DISTINCT command along with the SQL MOD() function is used to retrieve only unique records depending on the specified column or expression.
	SELECT DISTINCT City FROM Station WHERE MOD(ID,2)=0

view raw WeatherOStation3.sql hosted with ❤ by GitHub

Querying with Transact-SQL_F1 Raw

Q1. Select the quantity per unit for all products in the Products table.


Q2.Select the unique category IDs from the Products table.

Q3. Select the names of products from the Products table which have more than 20 units left in stock.

Q4. Select the product ID, product name, and unit price of the 10 most expensive products from the

Q5. Select the product ID, product name, and quantity per unit for all products in the Products table.

Q6. Select the product ID, product name, and unit price of all products in the Products table. Sort your results by number of units in stock, from greatest to least.
Skip the first 10 results and get the next 5 after that

7. Use STR and CONVERT where appropriate to display the first name, employee ID and birthdate (in ISO 8601 format) for each employee in the Employees table.
Each result should be a single string in the following format, where each <<value>> is replaced by the appropriately converted value:
<<FirstName>> has an EmployeeID of <<EmployeeID>> and was born <<BirthDate>>

Q8. Select from the Orders table.
The first column of your result should be a single string in exactly the following format:
<<ShipName>> is from <<ShipCity or ShipRegion or ShipCountry>>
If there is no ShipCity, then you should select ShipRegion, and if there is no ShipRegion you should select ShipCountry.

Q9. Select the ship name and ship postal code from the Orders table. If the postal code is missing, display 'unknown'.

Q10. Using the Suppliers table, select the company name, and use a simple CASE expression to display 'outdated' if the company has a fax number, or 'modern' if it doesn't. Alias the result of the CASE expression to Status.

	--Q1. Select the quantity per unit for all products in the Products table.
	SELECT QuantityPerUnit FROM Products
	--Q2.Select the unique category IDs from the Products table.
	SELECT Distinct CategoryID FROM Products
	--Q3. Select the names of products from the Products table which have more than 20 units left in stock.
	SELECT ProductName FROM Products
	WHERE UnitsInStock >20
	--Q4. Select the product ID, product name, and unit price of the 10 most expensive products from the Products table.
	SELECT TOP (10)
	ProductID, ProductName, UnitPrice
	FROM Products
	ORDER BY UnitPrice DESC
	--Q5. Select the product ID, product name, and quantity per unit for all products in the Products table. Sort your results alphabetically by product name.
	SELECT ProductID, ProductName, QuantityPerUnit
	FROM Products
	ORDER BY ProductName ASC
	--Q6. Select the product ID, product name, and unit price of all products in the Products table. Sort your results by number of units in stock, from greatest to least.
	--Skip the first 10 results and get the next 5 after that
	SELECT ProductID, ProductName, UnitPrice FROM Products
	ORDER BY UnitsInStock DESC
	OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
	--Q7. Use STR and CONVERT where appropriate to display the first name, employee ID and birthdate (in ISO 8601 format) for each employee in the Employees table.
	--Each result should be a single string in the following format, where each <<value>> is replaced by the appropriately converted value:
	--<<FirstName>> has an EmployeeID of <<EmployeeID>> and was born <<BirthDate>>
	SELECT FirstName +' has an EmployeeID of ' +CAST(EmployeeID AS VarChar(5)) +' and was born '+CONVERT(NVARCHAR(30), BirthDate, 126) AS Employees FROM Employees
	--Q8. Select from the Orders table.
	--The first column of your result should be a single string in exactly the following format:
	--<<ShipName>> is from <<ShipCity or ShipRegion or ShipCountry>>
	--If there is no ShipCity, then you should select ShipRegion, and if there is no ShipRegion you should select ShipCountry.
	SELECT
	ShipName + ' is from ' + COALESCE(ShipCity, ShipRegion,ShipCountry) AS destination
	FROM Orders
	--Q9. Select the ship name and ship postal code from the Orders table. If the postal code is missing, display 'unknown'.
	SELECT
	ShipName, ISNULL(ShipPostalCode, 'unknown')
	FROM Orders
	--Q10. Using the Suppliers table, select the company name, and use a simple CASE expression to display 'outdated' if the company has a fax number, or 'modern' if it doesn't. Alias the result of the CASE expression to Status.
	SELECT CompanyName,
	CASE
	WHEN Fax IS NULL THEN 'modern'
	ELSE 'outdated'
	END AS Status
	FROM Suppliers;

view raw final1.sql hosted with ❤ by GitHub

Weather Observation Station 10 HackerRank

Query the list of CITY names from STATION that do not end with vowels. Your result cannot contain duplicates.

Input Format

The STATION table is described as follows:

Works on MS SQL SERVER

SELECT DISTINCT city FROM station WHERE city NOT LIKE '%[aeiou]';

view raw WeatherOStation10-HR hosted with ❤ by GitHub

Congratulations, you passed the sample test case.

Click the Submit Code button to run you code against all the test cases.

Your Output (stdout)

Addison 
Agency 
Alanson 
Albany 
Albion 
Algonac 
Allerton 
Alton 
Andover 

Learning Transact-SQL exercise 1

Introduction to Transact-SQL

AdventureWorks Cycles is a company that sells directly to retailers, who then sell products to consumers. Each retailer that is an AdventureWorks customer has provided a named contact for all communication from AdventureWorks.

The sales manager at AdventureWorks has asked you to generate some reports containing details of the company's customers to support a direct sales campaign. Let's start with some basic exploration.

Instructions

Familiarize yourself with the Customer table by writing a Transact-SQL query that retrieves all columns for all customers.

Solution:👀

	-- select all columns
	SELECT * FROM SalesLT.Customer;
	-- from the SalesLT.Customer table

view raw Select*.sql hosted with ❤ by GitHub

Create List of Customer Contacts

As a next step, it would be good to have a structured view of the names of your customer contacts.

Create a table that lists all customer contact names. The table should include

the Title,FirstName,MiddleName,LastNamandSuffixof all customers.

Solution:👀

	-- select the Title, FirstName, MiddleName, LastName and Suffix columns
	-- from the Customer table
	SELECT Title, firstname, middlename, lastname, suffix
	FROM SalesLT.Customer;

view raw CreateCustomer.sql hosted with ❤ by GitHub

Title	firstname	middlename	lastname	suffix
Mr.	Orlando	N.	Gee	null
Mr.	Keith	null	Harris	null
Ms.	Donna	F.	Carreras	null
Ms.	Janet	M.	Gates	null

Create List of Customer Contacts (2)

Each customer has an assigned salesperson. Can you finish the query to include the salesperson and a nicely structured display of the customers' names?

• Complete the query to list the following elements for all customers:The salesperson
• A column named CustomerName that displays how the customer contact should be greeted (e.g. "Mr Smith").
• The customer's phone number (Phone)
• Don't forget to space out the contents of your CustomerName column with + ' ' + and use the alias provided.

Solution:👀

	-- finish the query
	SELECT Salesperson, Title + ' ' + LastName AS CustomerName, Phone
	FROM SalesLT.Customer;

view raw CreateCustomer2.sql hosted with ❤ by GitHub

Salesperson	CustomerName	Phone
adventure-works\pamela0	Mr. Gee	245-555-0173
adventure-works\david8	Mr. Harris	170-555-0127
adventure-works\jillian0	Ms. Carreras	279-555-0130
adventure-works\jillian0	Ms. Gates	710-555-0173
adventure-works\shu0	Mr. Harrington	828-555-0186
adventure-works\linda3	Ms. Carroll	244-555-0112

Retrieving Customer and Sales Data

if you want to build a string with an integer (e.g. id) you can use:

CAST(id AS VARCHAR)

Provide a list of all customer companies in the format <Customer ID>: <Company Name> (e.g. 78: Preferred Bikes). You'll need to use both CAST() and VARCHAR in your solution. Don't forget to use the alias provided.

Solution:👀

	-- cast the CustomerID column to a VARCHAR and concatenate with the CompanyName column
	SELECT CAST(CustomerID AS varchar) + ': ' + CompanyName AS CustomerCompany
	FROM SalesLT.Customer;

view raw RetrievingCSdata.sql hosted with ❤ by GitHub

CustomerCompany
1: A Bike Store
2: Progressive Sports
3: Advanced Bike Components
4: Modular Cycle Systems
5: Metropolitan Sports Supply

Retrieving Customer and Sales Data (2)

The SalesLT.SalesOrderHeader table contains records of sales orders. You have been asked to retrieve data for a report that shows:

• The sales order number and revision number in the format <Order Number> (<Revision>) (e.g. SO71774 (2)).
• The order date converted to ANSI standard format yyyy.mm.dd (e.g. 2015.01.31).

Solution:👀

	-- finish the query
	SELECT SalesOrderNumber + ' (' + STR(RevisionNumber, 1) + ')' AS OrderRevision,
	CONVERT(nvarchar(30), OrderDate, 102) AS OrderDate
	FROM SalesLT.SalesOrderHeader;

view raw RetrievingCSdata2.sql hosted with ❤ by GitHub

OrderRevision	OrderDate
SO71774 (1)	2004.06.01
SO71776 (1)	2004.06.01
SO71780 (1)	2004.06.01
SO71782 (1)	2004.06.01
SO71783 (1)	2004.06.01

Retrieving Customer Contact Names

In this exercise, you'll write a query that returns a list of customer names.

The list should consist of a single field in the format:

• <first name> <last name> (e.g. Keith Harris) if the middle name is unknown,
• <first name> <middle name> <last name> (e.g. Jane M. Gates) if a middle name is stored in the database.

Solution:👀

	-- use ISNULL to check for middle names and concatenate with FirstName and LastName
	SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName
	AS CustomerName
	FROM SalesLT.Customer;

view raw RetrievingCName.sql hosted with ❤ by GitHub

CustomerName
Orlando N. Gee
Keith Harris
Donna F. Carreras
Janet M. Gates
Lucy Harrington

Retrieving Primary Contact Details

Customers may provide AdventureWorks with an email address, a phone number, or both. 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.

Here, you will write a query that returns a list of customer IDs in one column, and a second column named PrimaryContact that contains the email address if known, and otherwise the phone number.

Note: In the sample data provided in AdventureWorksLT, there are no customer records without an email address. Therefore, to verify that your query works as expected, we have run the following UPDATE statement to remove some existing email addresses before you write your query. (Don't worry, you'll learn about UPDATE statements later in the course!)

UPDATE SalesLT.Customer
SET EmailAddress = NULL
WHERE CustomerID % 7 = 1;

Write a query that returns a list of customer IDs in one column, and a second column called PrimaryContact that contains the email address if known, and otherwise the phone number.

Solution:👀

	-- select the CustomerID, and use COALESCE with EmailAddress and Phone columns
	SELECT CustomerID, COALESCE(EmailAddress, Phone) AS PrimaryContact
	FROM SalesLT.Customer;

view raw RetrievingPrimaryContact hosted with ❤ by GitHub

CustomerID	PrimaryContact
1	245-555-0173
2	keith0@adventure-works.com
3	donna0@adventure-works.com
4	janet1@adventure-works.com
5	lucy0@adventure-works.com

Retrieving Shipping Status

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.

Note: In the sample data provided in AdventureWorksLT, there are no SalesOrderHeader records without a ship date. Therefore, to verify that your query works as expected, we have run the following UPDATE statement to remove some existing ship dates before you write your query. (Again, don't worry, you'll learn about UPDATEstatements later in the course!)

UPDATE SalesLT.SalesOrderHeader
SET ShipDate = NULL
WHERE SalesOrderID > 71899;

Write a query to list 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

Solution:👀

	SELECT SalesOrderID, OrderDate,
	CASE
	WHEN ShipDate IS NULL THEN 'Awaiting Shipment'
	ELSE 'Shipped'
	END AS ShippingStatus
	FROM SalesLT.SalesOrderHeader;

view raw RetrievingSStatus hosted with ❤ by GitHub

SalesOrderID	OrderDate	ShippingStatus
71774	2004-06-01 00:00:00	Shipped
71776	2004-06-01 00:00:00	Shipped
71780	2004-06-01 00:00:00	Shipped
71782	2004-06-01 00:00:00	Shipped
71783	2004-06-01 00:00:00	Shipped