---------------------
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

-------
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

-------
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

-------
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

-------
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

-------
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

-------
SELECT	count(*) from dept_location

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

10.

SELECT	count(*) from Products
where	supplierID = 24

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

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

11.

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

-------

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

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

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

-------
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))

-------

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))

-------

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'))

-------

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))

--------

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))

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

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))

--------

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)))

-------

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));