SQL LAB ASSIGNMENT

Assignment 1

The structure of two tables along with the Relation given below

Give the SQL queries for the question given below

1. Select the last name of all employees.

SELECT LastName FROM Employees;

2. Select the last name of all employees, without duplicates.

SELECT DISTINCT LastName FROM Employees;

3. Select all the data of employees whose last name is "Smith".

SELECT * FROM Employees WHERE LastName = 'Smith';

4. Select all the data of employees whose last name is "Smith" or "Doe".

/* With OR */

SELECT * FROM Employees WHERE LastName = 'Smith' OR LastName = 'Doe';

/* With IN */

SELECT * FROM Employees WHERE LastName IN ('Smith' , 'Doe');

5. Select all the data of employees that work in department 14.

SELECT * FROM Employees WHERE Department = 14;

6. Select all the data of employees that work in department 37 or department 77.

/* With OR */

SELECT * FROM Employees WHERE Department = 37 OR Department = 77;

SELECT * FROM Employees WHERE Department IN (37,77);

7. Select all the data of employees whose last name begins with an "S".

SELECT * FROM Employees WHERE LastName LIKE 'S%';

8. Select the sum of all the departments' budgets.

SELECT SUM(Budget) FROM Departments;

9. Select the number of employees in each department (you only need to show the department code and the number of employees).

SELECT Department, COUNT(*)  FROM Employees  GROUP BY Department;

10. Select all the data of employees, including each employee's department's data.

SELECT SSN, E.Name AS Name_E, LastName, D.Name AS Name_D, Department, Code, Budget FROM Employees E INNER JOIN Departments D ON E.Department = D.Code;

11. Select the name and last name of each employee, along with the name and budget of the employee's department.

/* Without labels */

SELECT Employees.Name, LastName, Departments.Name AS DepartmentsName, Budget

FROM Employees INNER JOIN Departments ON Employees.Department = Departments.Code;

/* With labels */

SELECT E.Name, LastName, D.Name AS DepartmentsName, Budget FROM Employees E INNER JOIN Departments D ON E.Department = D.Code;

12. Select the name and last name of employees working for departments with a budget greater than $60,000.

/* Without subquery */

SELECT Employees.Name, LastName FROM Employees INNER JOIN Departments

ON Employees.Department = Departments.Code AND Departments.Budget > 60000;

/* With subquery */

SELECT Name, LastName FROM Employees WHERE Department IN

  (SELECT Code FROM Departments WHERE Budget > 60000);

13. Select the departments with a budget larger than the average budget of all the departments.

SELECT * FROM Departments WHERE Budget>(SELECT AVG(Budget)FROM Departments);

14. Select the names of departments with more than two employees.

/* With subquery */

SELECT Name FROM Departments WHERE Code IN (SELECT Department FROM Employees

      GROUP BY Departmens HAVING COUNT(*) > 2);

/* With UNION. This assumes that no two departments have the same name */

SELECT Departments.Name FROM Employees INNER JOIN Departments ON Department = Code GROUP BY Departments.Name HAVING COUNT(*) > 2;

15. Display all the information of all enployees

Select * from Employees;
16. Add a new department called "Quality Assurance", with a budget of $40,000 and departmental code 11. Add an employee called "Mary Moore" in that department, with SSN 847-21-9811.

INSERT INTO Departments VALUES ( 11 , 'Quality Assurance' , 40000);

INSERT INTO Employees VALUES ( '847219811' , 'Mary' , 'Moore' , 11);

17. Reduce the budget of all departments by 10%.

UPDATE Departments SET Budget = Budget * 0.9;

18. Reassign all employees from the Research department (code 77) to the IT department (code 14).

UPDATE Employees SET Department = 14 WHERE Department = 77;

19. Delete from the table all employees in the IT department (code 14).

DELETE FROM Employees WHERE Department = 14;

20. Delete from the table all employees who work in departments with a budget greater than or equal to $60,000.

DELETE FROM Employees WHERE Department IN (SELECT Code FROM Departments WHERE Budget >= 60000);

21. Delete from the table all employees.

DELETE FROM Employees;

Assignment 2

Table: Sales

OrderID	OrderDate	OrderPrice	OrderQuantity	CustomerName
1	12/22/2005	160	2	Smith
2	8/10/2005	190	2	Johnson
3	7/13/2005	500	5	Baldwin
4	7/15/2005	420	2	Smith
5	12/22/2005	1000	4	Wood
6	10/2/2005	820	4	Smith
7	11/3/2005	2000	2	Baldwin

Give the SQL Queries for the following operations

1. Count how many orders have made a customer with CustomerName of Smith.

2. Find number of unique customers that have ordered from the store.

3. Find out total no. of items ordered by all the customers.

4. Find out average number of items per order.

5. Find out the average OrderQuantity for all orders with OrderPrice greater than 200

6. Find out what was the minimum price paid for any of the orders.

7. Find out the highest OrderPrice from the given sales table

8. List out unique customers‟ name only from the table.

9. List out name of the customers who have given order in the month of DECEMBER

10. Find out the total amount of money spent for each of the customers.

11. Select all unique customers, who have spent more than 1200 in the store.

12. Select all customers that have ordered more than 5 items in total from all their orders.

13. Select all customers who have spent more than 1000, after 10/01/2005.

14. Select orders in increasing order of order price.

15. Select orders in decreasing order of order price.