TSQL examples for Avanade

Module2.sql

-- USE Northwind
-- comments out the query so it's not run
SELECT * FROM Northwind.dbo.Orders
SELECT * FROM dbo.Orders
SELECT * FROM Orders


SELECT CustomerID, OrderDate FROM Orders

SELECT OrderDate, CustomerID FROM Orders

-- 10 % 4 -> 2

-- camelCase, PascalCase, SNAKE_CASE

SELECT Country + ', ' + City + ', ' +    Address 
				AS 
			FullContactDetails FROM Customers
SELECT ProductID, UnitPrice, Quantity, UnitPrice * Quantity AS TotalPriceOfOrder FROM [Order Details]

Module3.sql

USE Northwind

SELECT * FROM Orders
SELECT * FROM Orders WHERE CustomerID = 'TOMSP'
SELECT * FROM Orders WHERE CustomerID = 'TOMSP' OR CustomerID = 'VINET'
SELECT * FROM Orders WHERE CustomerID IN ('TOMSP', 'VINET', 'HANAR')
SELECT * FROM Orders WHERE CustomerID IN ('TOMSP', 'VINET', 'HANAR')  AND OrderDate > '1996-08-01'

SELECT * FROM Orders 
WHERE CustomerID IN ('TOMSP', 'VINET', 'HANAR')  AND OrderDate >= '1996-07-04' AND OrderDate < '1996-07-06'

SELECT * FROM Employees WHERE LastName = 'Fuller' -- IS 'Fuller'
SELECT * FROM Employees WHERE LastName != 'Fuller' -- IS NOT 'Fuller
SELECT * FROM Employees WHERE LastName LIKE 'D%' -- STARTS WITH 'D'
SELECT * FROM Employees WHERE LastName LIKE '%N' -- ENDS WITH 'N'
SELECT * FROM Employees WHERE FirstName LIKE 'A__e' -- 'A something something e'
SELECT * FROM Employees WHERE FirstName LIKE 'An[d,n]%' -- STARTS WITH (A, N, d or n)
SELECT * FROM Employees WHERE FirstName LIKE 'An[^d]%' -- STARTS WITH (A, N, NOT d)

-- SELECT * FROM ORDERS WHERE OrderDate = 1996-08-01 Interpreted as a number

SELECT ProductID, UnitPrice, Quantity, UnitPrice * Quantity AS TotalPriceOfOrder FROM [Order Details]
WHERE UnitPrice * Quantity > 100

Module4.sql

USE Northwind

SELECT CustomerID, OrderDate FROM Orders 
WHERE CustomerID IN ('TOMSP', 'VINET', 'HANAR')  AND OrderDate >= '1996-07-04'
ORDER BY OrderDate DESC

SELECT CategoryID, UnitPrice, ProductName FROM Products 
ORDER BY CategoryID ASC, UnitPrice DESC, ProductName ASC

SELECT ProductName, UnitsInStock, UnitsOnOrder, UnitsInStock + UnitsOnOrder AS FutrureStock
FROM dbo.Products
WHERE UnitsInStock + UnitsOnOrder > 100
ORDER BY FutrureStock DESC

SELECT DISTINCT Country FROM Suppliers ORDER BY Country ASC

SELECT TOP 15 WITH TIES * FROM Products ORDER BY UnitPrice DESC

SELECT TOP 20 PERCENT * FROM Products ORDER BY UnitPrice DESC

Module5.sql

-- 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

-- COUNT(*) -> 10
-- SUM() -> 55
-- AVG() -> 5.5
-- MIN() -> 1
-- MAX() -> 10

SELECT COUNT(UnitPrice) AS NumProducts FROM Products
SELECT AVG(UnitPrice) AS AveragePrice FROM Products
SELECT MIN(UnitPrice) AS CheapestProduct FROM Products
SELECT MAX(UnitPrice) AS MostExpensive FROM Products

SELECT SUM(UnitPrice * Quantity) AS TotalRevenue FROM [Order Details]

SELECT MIN(OrderDate) AS EarliestOrder FROM Orders
SELECT MAX(OrderDate) AS NewestOrder FROM Orders

SELECT COUNT(Fax) FROM Customers
SELECT COUNT(*) AS CustomersWithoutFaxMachines FROM Customers WHERE Fax IS NULL

SELECT CategoryID, SupplierID FROM Products ORDER BY CategoryID ASC
SELECT 
	CategoryID,
	COUNT(CategoryID) AS NumberOfProducts,
	SUM(UnitPrice) AS TotalPrice,
	MAX(UnitPrice) AS MostExpensive,
	MIN(UnitPrice) AS Cheapest,
	AVG(UnitPrice) AS AveragePrice
FROM Products 
GROUP BY CategoryID
ORDER BY CategoryID

Module6.sql

use northwind

select * from products

select * from categories

--WHAT I WOULD LIKE TO SEE...
--Each ProductName and the CategoryName of the category it belongs to

--THE 4 STEPS TO WRITING A JOIN
--1.  SELECT the columns that you want to see, forgetting for a moment that they may be in different tables
--2.  FROM one of the tables, and it doesn't matter which
--3.  JOIN the other table (note - if you like typing you can say INNER JOIN, but JOIN is fine)
--4.  ON column name = column name

select 
	products.productname,
	categories.categoryname
from products
join categories
on products.categoryid = categories.categoryid

select * from orders
select * from customers

select 
	orders.orderid,
	orders.orderdate,
	customers.companyname
from orders
join customers
on orders.customerid = customers.customerid

select 
	products.productname,
	categories.categoryname
from products
join categories
on products.categoryid = categories.categoryid

select 
	p.productname,
	c.categoryname
from products as p
join categories as c
on p.categoryid = c.categoryid

select * from products
select * from categories
select * from suppliers

--WHAT I WANT TO SEE...
--	The name of each product
--	The companyname of its supplier
--	The categoryname it belongs to

select 
	p.productname,
	s.companyname,
	c.categoryname
from products as p
join categories as c
	on p.CategoryID = c.CategoryID
join suppliers as s
	on s.supplierid = p.SupplierID

select * from orders
select * from customers
select * from products
select * from [Order Details]


--WHAT I WOULD LIKE TO SEE...
--	Each OrderID
--	The OrderDate
--	The companyname of the customer
--	The products that the order was for
--  BUT only for products which are seafood

select 
	o.orderid,
	o.orderdate,
	c.companyname,
	p.productname
from orders as o
join customers as c
	on o.customerid = c.CustomerID
join [Order Details] as od
	on o.orderid = od.OrderID
join Products as p
	on od.ProductID = p.ProductID
join categories as ca
	on p.CategoryID = ca.CategoryID
where ca.CategoryName = 'seafood'

--WHAT I WOULD LIKE TO SEE...
--	A list of the cities & countries where we have both customers and staff

select distinct
	e.city,
	e.country
from Employees as e
join customers as c
on 
	e.city = c.city and
	e.country = c.country --Composite join criteria
	
select 
	c.categoryname as Category,
	count(*) as 'Number of products',
	sum(unitprice) as 'Total Price',
	avg(unitprice) as 'Average Price',
	min(unitprice) as 'Lowest Price',
	max(unitprice) as 'Highest Price'
from products as p
join Categories as c
	on p.CategoryID = c.CategoryID
group by c.categoryname

--OUTER JOINS

select * from orders
select * from customers

--WHAT I WOULD LIKE TO SEE...
--	Each OrderID
--	The companyname of the customer who placed it
--	BUT I also want to see customers in the list who have not placed any orders (they will have an orderid of NULL)

select 
	o.orderid,
	c.companyname
from customers as c
left join orders as o
on o.customerid = c.customerid

--WHAT I WANT TO SEE
--	Company names of customers who have placed no orders

select 
	c.companyname
from customers as c
left join orders as o
on o.customerid = c.customerid
where orderid is null

--WHAT I WANT TO SEE...
	--The companyname of each customer
	--The number of orders they have placed
	--Sorted in descending order of number of orders

select 
	c.companyname,
	count(o.orderid) as NumberOfOrders --NOT COUNT(*)
from customers as c
left join orders as o
on o.customerid = c.customerid
group by c.CompanyName
order by NumberOfOrders desc

--CROSS JOIN

--WHAT I WANT TO SEE...
	--Every possible combination of employee lastname and categoryname
	--Basically a list that says "this person could sell this category"
	--Also known as a cartesian product

select 
	e.lastname,
	c.categoryname
from Employees as e
cross join Categories as c

--SELF JOIN (NOT A TYPE OF JOIN!  JOINING A TABLE TO ITSELF)

select 
	employeeid,
	firstname,
	lastname,
	reportsto
from Employees

--WHAT I WOULD LIKE TO SEE...
	--The name of each employee
	--The name of their manager

select 
	e.firstname + ' ' + e.lastname as 'Employee',
	m.firstname + ' ' + m.lastname as 'Manager'
from employees as e
left join Employees as m
on e.reportsto = m.EmployeeID

--SET OPERATORS

select country from customers
	union --Combine queries and get rid of duplicates
select country from employees

select country from customers
	union all --Combine queries but keep duplicates
select country from employees

select country from customers
	intersect --Overlap (common entities) between queries
select country from employees

select country from customers
	except --Difference between queries (countries with customers but not staff)
select country from employees

select 
	companyname,
	contactname,
	city,
	country,
	phone,
	'Customer' as 'Type'
from customers
	union
select 
	'Northwind',
	firstname + ' ' + lastname,
	city,
	country,
	homephone,
	'Employee'
from Employees
	union
select
	companyname,
	contactname,
	city,
	country,
	phone,
	'Supplier'
from suppliers

Module7.sql

CREATE VIEW dbo.ReportsTo AS (
	SELECT 
	m.FirstName + ' ' + m.LastName AS ManagerName, e.FirstName + ' ' + e.LastName AS EmployeeName
	FROM
	Employees AS e
	JOIN
	Employees AS m
	ON
	e.ReportsTo = m.EmployeeID
)
GO -- ENDS BLOCK (Batch)

SELECT * from sys.views

SELECT * FROM dbo.ReportsTo ORDER BY ManagerName ASC


EXEC dbo.[Sales by Year] @ending_date='1998', @beginning_date='1996'
EXEC dbo.[Sales by Year] '1998', '1996'

Module8.sql

SELECT 
LastName, 
LEN(LastName) AS Length, 
UPPER(LastName) AS CapsName, 
LOWER(LastName) AS LowerCase 
FROM Employees


SELECT FirstName + ' ' + LastName AS FullName,
LEFT(FirstName, 1) + LEFT(LastName, 1) AS Initials
FROM Employees

SELECT SUBSTRING(ContactName, CHARINDEX(' ', ContactName, 1), 100) AS LastName  FROM Customers

SELECT GETDATE() AS CurrentDate, SYSDATETIME() as SysDateTimeExample, SYSDATETIMEOFFSET() AS sdto

SELECT 
GETDATE() AS CurrentDate,
Year(GETDATE()) AS CurrentYear,
Month(GETDATE()) AS CurrentMonth, 
Day(GETDATE()) AS CurrentDay

select 
	getdate() as 'Now',
	datepart(year, getdate()) as 'Year',
	datepart(week, getdate()) as 'Week number',
	datepart(quarter, getdate()) as 'Quarter',
	datepart(hour, getdate()) as 'Hour',
	datepart(minute, getdate()) as 'Minute',
	datepart(second, getdate()) as 'Seconds',
	datepart(weekday, getdate()) as 'Day of week',
	datename(month, getdate()) as 'Name of month',
	datename(weekday, getdate()) as 'Name of weekday'


select 
	getdate() as 'Now',
	dateadd(day, 14, getdate()) as '14 days from now',
	dateadd(day, -14, getdate()) as '14 days ago'

SELECT CompanyName, ISNULL(Fax, 'No fax machine') AS Fax FROM Customers ORDER BY Fax ASC
SELECT CompanyName, COALESCE(Fax, 'No fax machine') AS Fax FROM Customers ORDER BY Fax ASC

SELECT ProductName + ': £' + CAST(UnitPrice AS VARCHAR) AS DisplayPrice FROM Products
SELECT ProductName + ': £' + CONVERT(VARCHAR, UnitPrice) AS DisplayPrice FROM Products

SELECT CAST('1998' AS datetime) AS CastExample, 
CONVERT(varchar(30), getdate(), 101) AS ConvertExample,
PARSE('Monday, 28 November 2022' AS Date) AS ParseExample

-- CAST converts a value to a different type - often the most efficient
-- CONVERT is CAST but you specify a style
-- PARSE - newest, slowest but can read stuff the others can't