SELECT Categories.CategoryID, CategoryName, AVG(UnitsInStock) AS AvgUnits
FROM Products JOIN Categories ON Categories.CategoryID = Products.CategoryID
WHERE (UnitsInStock > 0) AND (Discontinued = 0)
GROUP BY Categories.CategoryID, CategoryName
HAVING (AVG(UnitsInStock) > 30)
ORDER BY AvgUnits DESC

---------

SELECT dbo.Orders.ShipName, dbo.Orders.ShipAddress, dbo.Orders.ShipCity, dbo.Orders.ShipRegion, dbo.Orders.ShipPostalCode, 
 dbo.Orders.ShipCountry, dbo.Orders.CustomerID, dbo.Customers.CompanyName AS CustomerName, 
 dbo.Customers.Address, dbo.Customers.City, dbo.Customers.Region, dbo.Customers.PostalCode, dbo.Customers.Country, 
 dbo.Employees.FirstName + ' ' + dbo.Employees.LastName AS Salesperson, dbo.Orders.OrderID, dbo.Orders.OrderDate, 
 dbo.Orders.RequiredDate, dbo.Orders.ShippedDate, dbo.Shippers.CompanyName AS ShipperName, 
 dbo.[Order Details].ProductID, dbo.Products.ProductName, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity, 
 dbo.[Order Details].Discount, CONVERT(money, (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) 
 * (1 - dbo.[Order Details].Discount) / 100) * 100 AS ExtendedPrice, dbo.Orders.Freight
FROM dbo.Shippers INNER JOIN
 dbo.Products INNER JOIN
 dbo.Employees INNER JOIN
 dbo.Customers INNER JOIN
 dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID ON 
 dbo.Employees.EmployeeID = dbo.Orders.EmployeeID INNER JOIN
 dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID ON 
 dbo.Products.ProductID = dbo.[Order Details].ProductID ON dbo.Shippers.ShipperID = dbo.Orders.ShipVia

---------

SELECT Products.*, Categories.CategoryName
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)=0))

----------

SELECT Product_List.ProductID, Product_List.ProductName
FROM Products AS Product_List
WHERE (((Product_List.Discontinued)=0))

----------

SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, 
	Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City, 
	Customers.Region, Customers.PostalCode, Customers.Country, 
	(FirstName + ' ' + LastName) AS Salesperson, 
	Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName, 
	"Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity, 
	"Order Details".Discount, 
	(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight
FROM 	Shippers INNER JOIN 
		(Products INNER JOIN 
			(
				(Employees INNER JOIN 
					(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) 
				ON Employees.EmployeeID = Orders.EmployeeID) 
			INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID) 
		ON Products.ProductID = "Order Details".ProductID) 
	ON Shippers.ShipperID = Orders.ShipVia

----------

SELECT "Order Details".OrderID, "Order Details".ProductID, Products.ProductName, 
	"Order Details".UnitPrice, "Order Details".Quantity, "Order Details".Discount, 
	(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice
FROM Products INNER JOIN "Order Details" ON Products.ProductID = "Order Details".ProductID

----------

SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, 
	Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, 
	Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, 
	Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

-----------

SELECT Categories.CategoryName, Products.ProductName
FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) 
	INNER JOIN (Orders 
		INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID) 
	ON Products.ProductID = "Order Details".ProductID
WHERE (((Orders.ShippedDate) Between '1997/01/01' And '1997/12/31'))
GROUP BY Categories.CategoryName, Products.ProductName

-----------

SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE Products.Discontinued <> 1

-----------

SELECT DISTINCT Customers.CustomerID, Customers.CompanyName, Customers.City, Customers.Country
FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate BETWEEN '1997/01/01' And '1997/12/31'

-----------

Select person.personID, personAddress.addressID, personAddress.personID
from person INNER JOIN personaddress on person.personid = personaddress.personid

-----------

Select person.personID, personAddress.addressID, personAddress.PersonID
from person LEFT OUTER JOIN personaddress on person.personid = personaddress.personid

-----------

Select person.personID, personAddress.addressID, personAddress.PersonID
from person RIGHT OUTER JOIN personaddress on person.personid = personaddress.personid

-----------

Select person.personID, personAddress.addressID, personAddress.PersonID
from person FULL OUTER JOIN personaddress on person.personid = personaddress.personid
