Friday, October 27, 2017

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

1 comment:

  1. Hi,

    Do you have Querying with Transact-SQL_Final 3 and 4 solutions? Thanks

    ReplyDelete