USE Library;

/*SELECT Products.ProductName, S.Address
FROM Products JOIN Suppliers S on Products.SupplierID = S.SupplierID
WHERE Products.ProductID BETWEEN 20 AND 30

SELECT Products.ProductName, Products.UnitsInStock
FROM Products JOIN Suppliers S on Products.SupplierID = S.SupplierID
WHERE S.CompanyName = 'Tokyo Traders'

SELECT COUNT(OrderID), Customers.Address
FROM Customers LEFT OUTER JOIN Orders O
    on Customers.CustomerID = O.CustomerID and year(OrderDate) = 1997
GROUP BY Customers.CustomerID, Address
HAVING COUNT(OrderID) = 0

SELECT S.CompanyName, S.Phone
FROM Products JOIN Suppliers S on Products.SupplierID = S.SupplierID
WHERE UnitsInStock = 0*/

/*SELECT member.firstname, member.lastname, juvenile.birth_date
from juvenile JOIN member ON member.member_no = juvenile.member_no

SELECT DISTINCT title.title
from loan JOIN title ON loan.title_no = title.title_no

SELECT loanhist.in_date, -DATEDIFF(day, loanhist.due_date, loanhist.in_date), loanhist.fine_paid
FROM loanhist JOIN title ON loanhist.title_no = title.title_no
WHERE title.title =  'Tao Teh King' AND fine_paid IS NOT NULL

SELECT reservation.isbn
 from reservation JOIN member ON member.member_no = reservation.member_no
WHERE member.lastname='Stephen A. Graff'
*/

USE Northwind;

select Products.ProductName, Address, CompanyName, Products.UnitPrice
FROM Products join Suppliers S on Products.SupplierID = S.SupplierID
join Categories on Products.CategoryID = Categories.CategoryID
where CategoryName = 'Meat/Poultry' AND UnitPrice between 20 and 30

select Products.ProductName, Address, CompanyName, Products.UnitPrice
FROM Products join Suppliers S on Products.SupplierID = S.SupplierID
join Categories on Products.CategoryID = Categories.CategoryID
where CategoryName = 'Confections'

select distinct Customers.CompanyName, Customers.Phone
from Customers join Orders O on Customers.CustomerID = O.CustomerID
join Shippers on O.ShipVia = Shippers.ShipperID
WHERE Shippers.CompanyName = 'United Package'

select distinct Customers.CompanyName, Customers.Phone
from Customers
join Orders O on Customers.CustomerID = O.CustomerID
join [Order Details] OD on OD.OrderID = O.OrderID
join Products P on OD.ProductID = P.ProductID
join Categories C on P.CategoryID = C.CategoryID
WHERE C.CategoryName = 'Confections'