---------------------
1.


SELECT     Salary
FROM       Employees

----------------------
2.

SELECT     Salary
FROM         dbo.Employees
WHERE     (Salary > 30000)

----------------------
3.

SELECT DISTINCT Salary
FROM   dbo.Employees

----------------------
4.


SELECT     Categories.CategoryID, Categories.CategoryName, Products.ProductName
FROM       Categories INNER JOIN
           Products ON Categories.CategoryID = Products.CategoryID

SELECT     Categories.CategoryID, Categories.CategoryName, Products.ProductName
FROM       Categories,Products
where	   Categories.CategoryID = Products.CategoryID

-------
The following one was discussed in the classroom and is listed here for comparison. It is not
executable in the database since the tables involved in the query are not defined.

SELECT d.dnumber, dname, dlocation
FROM dpartment d, dept_location l
WHERE d.dnumber=l.dnumber

----------------------
5.



SELECT     e.EmployeeID, e.LastName, r.LastName Supervised
FROM       Employees e INNER JOIN
           Employees r ON e.EmployeeID = r.Supervisor

-------
This one is for comparison:

SELECT s.ssn, s.lname, r.lname
FROM employee s, employee r
WHERE s.ssn=r.superssn

-----------------------
6.

SELECT     e.EmployeeID, e.LastName, r.LastName AS supervised
FROM       Employees e INNER JOIN
           Employees r ON e.EmployeeID = r.Supervisor
ORDER BY e.LastName, r.LastName

-------
This one is for comparison:

SELECT s.ssn, s.lname, r.lname
FROM employee s, employee r
WHERE s.ssn=r.superssn
Order by s.lname, r.lname


------------------------
7.

SELECT     e.EmployeeID, e.LastName, COUNT(r.LastName) AS supervised
FROM       Employees e INNER JOIN
           Employees r ON e.EmployeeID = r.Supervisor
GROUP BY e.EmployeeID, e.LastName

-------
This one is for comparison:

SELECT s.ssn, s.lname, count(r.lname)
FROM employee s, employee r
WHERE s.ssn=r.superssn
Group By s.ssn, s.lname

------------------------
8.

SELECT     e.EmployeeID, e.LastName, COUNT(r.LastName) AS supervised
FROM       dbo.Employees e INNER JOIN
           dbo.Employees r ON e.EmployeeID = r.Supervisor
GROUP BY e.EmployeeID, e.LastName
having count(r.LastName) < 2

-------
This one is for comparison:

SELECT	s.ssn, s.lName, count(r.lName)
FROM	Employees s, Employees r
WHERE	s.ssn = r.superssn
group by s.ssn, s.lname
having count(r.lname) < 2

------------------------

9.

SELECT	count(*) from Products

-------
This one is for comparison:

SELECT	count(*) from dept_location

------------------------

10.

SELECT	count(*) from Products
where	supplierID = 24

-------
This one is for comparison:

select count(*) from dept_location
where dlocation='Houston'

------------------------

11.

SELECT     *
FROM         dbo.Employees
WHERE     (LastName LIKE '%e%')

-------
This one is for comparison:

select * from dept_locations
where  dlocation like '%o%'

------------------------
12.

SELECT     FirstName, Salary
FROM         dbo.Employees
WHERE     (Salary BETWEEN 30000 AND 60000)

-------
This one is for comparison:

selcet fname, salary from Employees
where salary between 30000 and 50000

------------------------

13.

SELECT     EmployeeID, FirstName
FROM       dbo.Employees
WHERE     (EmployeeID IN
              (SELECT     employeeID
                          FROM dbo.EmployeeTerritories))

-------
This one is for comparison:


select ssn, fname from employee
where ssn in
	(select essn from dependent)

-------------------------

14.	

SELECT     EmployeeID, FirstName
FROM       dbo.Employees
WHERE     (EmployeeID = ANY
              (SELECT     employeeID
                          FROM employeeTerritories))

-------
This one is for comparison:

select ssn, fname from employee
where ssn = any
	(select essn from dependent)

-------------------------

15.

SELECT     EmployeeID, LastName
FROM       dbo.Employees
WHERE     (Salary > ALL
                (SELECT salary
                        FROM   employees
                        WHERE  Title = 'Inside Sales Coordinator'))

-------
This one is for comparison:

select ssn, fname from employee
where salary >
	all (select salary from employee where dno=4)

--------------------------

16.

SELECT     EmployeeID, LastName
FROM         dbo.Employees x
WHERE     (Salary >= ALL
                  (SELECT salary
                          FROM    employees y
                          WHERE   x.Title = y.Title))

--------
This one is for comparison:

select dno, ssn, fname from employee e
where salary
	>=
	(select salary from  employee x where x.dno=e.dno)

--------------------------

17.

SELECT     EmployeeID, LastName
FROM         dbo.Employees x
WHERE     (Salary =
               	  (SELECT MAX(salary)
                          FROM  employees y
                          WHERE x.Title = y.Title))

--------------------------
This one is for comparison:

select dno, ssn, fname from employee e where salary
= (select max(salary) from employee x where x.dno=e.dno)

------------------------
18.

Find all the products for which the shipping region is not
the same as suppliers' region.


SELECT	Products.ProductID, Products.ProductName, Orders.ShipRegion
FROM	Products INNER JOIN
	[Order Details] ON Products.ProductID = [Order Details].ProductID INNER JOIN
	Orders ON [Order Details].OrderID = Orders.OrderID
WHERE	(Orders.ShipRegion NOT IN
	(SELECT  Region
                 FROM	Suppliers
                 WHERE	Suppliers.SupplierID = Products.SupplierID))

--------
This one is for comparison:

find out who is working on a project that is not located where their department is located. 


SELECT employee.ssn, employee.fname, employee.lname, project.pnumber, project.plocation
FROM employee, project, works_on
WHERE 
employee.ssn = works_on.essn and 
project.pnumber = works_on.pno and
plocation NOT IN 
(SELECT dlocation FROM dept_locations WHERE dnumber=employee.dno)

-------------------------

19. 

SELECT	EmployeeID, LastName
FROM	Employees
WHERE	EXISTS
	(SELECT	*
	FROM	Orders
	WHERE	Orders.EmployeeID = Employees.EmployeeID)

-------------------------

20.

SELECT	EmployeeID, LastName
FROM	Employees
WHERE	Not EXISTS
	(SELECT	*
	FROM	Orders
	WHERE	Orders.EmployeeID = Employees.EmployeeID)

-------------------------

21.


Find all those customers whose orders are transferred by all the shippers.

SELECT	CustomerID, CompanyName
FROM	Customers x
WHERE	(NOT EXISTS
             (SELECT	*
              FROM	Shippers AS y
              WHERE	NOT EXISTS
              		(SELECT	*
                         FROM	Orders z
                         WHERE	z.CustomerID = x.CustomerID AND
				z.ShipVia = y.ShipperID)))

-------
This one is for comparion:

Who is working on every project?

SELECT e.ssn, e.fname, e.lname
FROM employee AS e
WHERE 
NOT EXISTS 
(SELECT * FROM project AS p WHERE 
NOT EXISTS
(SELECT * FROM works_on AS w WHERE w.essn=e.ssn AND w.pno=p.pnumber));