Advanced SQL HW

-- Problem 1.   Write a SQL query to find the names and salaries of the employees
-- that take the minimal salary in the company.
-- Use a nested SELECT statement.
--
-- FirstName    LastName    Salary
-- Susan        Eaton       9000.00
-- Kim          Ralls       9000.00
-- Jimmy        Bischoff    9000.00

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary = (SELECT MIN(Salary) FROM Employees);

-----------------------------------------------------------------------------------------------------------
-- Problem 2.   Write a SQL query to find the names and salaries of the employees that have a salary
-- that is up to 10% higher than the minimal salary for the company.
--
-- FirstName    LastName    Salary
-- Rostislav    Shabalin    9500.00
-- Russell      King        9500.00
-- Jimmy        Bischoff    9000.00
-- Michael      Vanderhyde  9300.00
-- John         Frum        9300.00
-- Merav        Netz        9100.00
-- Jan          Miksovsky   9100.00
-- …          …         …

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary < (SELECT MIN(Salary) * 1.1 FROM Employees);

-----------------------------------------------------------------------------------------------------------
-- Problem 3.   Write a SQL query to find the full name, salary and department of the employees that
-- take the minimal salary in their department.
-- Use a nested SELECT statement.
--
-- FirstName        LastName    Salary
-- Gail Erickson    32700.00    Engineering
-- Jossef Goldberg  32700.00    Engineering
-- Sharon Salavaria 32700.00    Engineering
-- Gancho D'Hers    25000.00    Tool Design
-- …              …         …

SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [Employee FULL Name],
       Salary,
       d.Name AS [Department Name]
FROM Employees e
  JOIN Departments d
    ON e.DepartmentID = d.DepartmentID
WHERE Salary =
  (SELECT MIN(Salary) FROM Employees
   WHERE DepartmentID = e.DepartmentID)
ORDER BY e.DepartmentID;

-----------------------------------------------------------------------------------------------------------
-- Problem 4.   Write a SQL query to find the average salary in the department #1.
--
-- FirstName        Average Salary
-- Gail Erickson    32700.00

SELECT AVG(Salary) AS [Average Salary FOR Department #1]
FROM Employees e
WHERE e.DepartmentID = 1;

-----------------------------------------------------------------------------------------------------------
-- Problem 5.   Write a SQL query to find the average salary in the "Sales" department.
--
-- Average Salary for Sales Department
-- 18403.7671

SELECT AVG(Salary) AS [Average Salary FOR Sales Department]
FROM Employees e
  JOIN Departments d
    ON e.DepartmentID = d.DepartmentID
WHERE d.Name = 'Sales';

-----------------------------------------------------------------------------------------------------------
-- Problem 6.   Write a SQL query to find the number of employees in the "Sales" department.
--
-- Sales Employees Count
-- 292

SELECT COUNT(*) AS [Sales Employees COUNT]
FROM Employees e
  JOIN Departments d
    ON e.DepartmentID = d.DepartmentID
WHERE d.Name = 'Sales';

-----------------------------------------------------------------------------------------------------------
-- Problem 7.   Write a SQL query to find the number of all employees that have manager.
--
-- Employees with manager
-- 289

SELECT COUNT(ManagerID) AS [Employees WITH manager]
FROM Employees;

-----------------------------------------------------------------------------------------------------------
-- Problem 8.   Write a SQL query to find the number of all employees that have no manager.
--
-- Employees without manager
-- 3

SELECT COUNT(*) AS [Employees WITHOUT manager]
FROM Employees
WHERE ManagerID IS NULL;

-----------------------------------------------------------------------------------------------------------
-- Problem 9.   Write a SQL query to find all departments and the average salary for each of them.
--
-- Department                   Average Salary
-- Document Control             14400.00
-- Engineering                  40166.6666
-- Executive                    92800.00
-- Facilities and Maintenance   13057.1428
-- …                          …

SELECT d.Name AS [Department], AVG(Salary) AS [Average Salary]
FROM Employees e
  JOIN Departments d
    ON e.DepartmentID = d.DepartmentID
GROUP BY d.Name
ORDER BY [Department] ASC;

-----------------------------------------------------------------------------------------------------------
-- Problem 10.  Write a SQL query to find the count of all employees in each department and for each town.
--
-- Town     Department          Employees count
-- Index    Document Control    1
-- Issaquah Document Control    4
-- Redmond  Engineering         1
-- Renton   Engineering         4
-- …      …                 …

SELECT t.Name AS [Town], d.Name AS [Department], COUNT(d.Name) AS [Employees COUNT]
FROM Employees e
  JOIN Departments d
    ON e.DepartmentID = d.DepartmentID
  JOIN Addresses a
    ON e.AddressID = a.AddressID
  JOIN Towns t
    ON a.TownID = t.TownID
GROUP BY t.Name, d.Name
ORDER BY [Department] ASC;

-----------------------------------------------------------------------------------------------------------
-- Problem 11.  Write a SQL query to find all managers that have exactly 5 employees.
-- Display their first name and last name.
--
-- FirstName    LastName            Employees count
-- Pilar        Ackerman            5
-- Paula        Barreto de Mattos   5
-- Jeff         Hay                 5
-- Lori         Kane                5
-- …          …                 …

SELECT m.FirstName, m.LastName, COUNT(e.EmployeeID) AS [Employees COUNT]
FROM Employees e
  JOIN Employees m
    ON e.ManagerID = m.EmployeeID
GROUP BY m.FirstName, m.LastName
HAVING COUNT(e.EmployeeID) = 5;

-----------------------------------------------------------------------------------------------------------
-- Problem 12.  Write a SQL query to find all employees along with their managers.
-- For employees that do not have manager display the value "(no manager)".
--
-- FirstName         Manager
-- Martin Kulov      No manager
-- George Denchev    No manager
-- Ovidiu Cracium    Roberto Tamburello
-- Michael Sullivan  Roberto Tamburello
-- …               …

SELECT e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName AS [Employee FULL Name],
       ISNULL(m.FirstName + ' ' + ISNULL(m.MiddleName + ' ', '') + m.LastName, 'No manager') AS [Manager FULL Name]
FROM Employees e
  LEFT JOIN Employees m
    ON e.ManagerID = m.EmployeeID;

-----------------------------------------------------------------------------------------------------------
-- Problem 13.  Write a SQL query to find the names of all employees whose last name
-- is exactly 5 characters long. Use the built-in LEN(str) function.
--
-- FirstName    Manager
-- Kevin        Brown
-- Terri        Duffy
-- Jo           Brown
-- Diane        Glimp
-- …          …

SELECT FirstName, LastName
FROM Employees
WHERE LEN(LastName) = 5;

-----------------------------------------------------------------------------------------------------------
-- Problem 14.  Write a SQL query to display the current date and time in the following format
-- "day.month.year hour:minutes:seconds:milliseconds".
-- Search in Google to find how to format dates in SQL Server.
--
-- DateTIme
-- 11.02.2015 18:50:02:960

SELECT CONVERT(VARCHAR(10), GETDATE(), 104) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114) AS [DATE AND TIME];

-----------------------------------------------------------------------------------------------------------
-- Problem 15.  Write a SQL statement to create a table Users.
-- Users should have username, password, full name and last login time.
-- Choose appropriate data types for the table fields. Define a primary key column with
-- a primary key constraint. Define the primary key column as identity to facilitate inserting records.
-- Define unique constraint to avoid repeating usernames. Define a check constraint to ensure the password
-- is at least 5 characters long.

CREATE TABLE Users (
  UserID INT IDENTITY,
  UserName NVARCHAR(10) NOT NULL,
  UserPassword NVARCHAR(30) NOT NULL,
  FullName NVARCHAR(50) NOT NULL,
  LastLogin DATETIME,
  CONSTRAINT PK_Users PRIMARY KEY(UserID),
  CONSTRAINT UNQ_Users UNIQUE(UserName),
  CONSTRAINT CHK_Password CHECK (LEN(UserPassword) >= 5)
);

INSERT INTO [dbo].[Users]
           ([UserName]
           ,[UserPassword]
           ,[FullName]
           ,[LastLogin])
     VALUES
           ('Nakata',
            'passwd',
            'Svetlin Nakov',
            GETDATE());

INSERT INTO [dbo].[Users]
           ([UserName]
           ,[UserPassword]
           ,[FullName]
           ,[LastLogin])
     VALUES
           ('Nakata2',
            'passwd',
            'Svetlin Nakov 2',
            '2015-02-11');


-----------------------------------------------------------------------------------------------------------
-- Problem 16.  Write a SQL statement to create a view that displays the users from the Users table
-- that have been in the system today.
-- Test if the view works correctly.

CREATE VIEW TodayVisitors AS
SELECT *
FROM Users
WHERE DAY(LastLogin) = DAY(GETDATE());

SELECT * FROM TodayVisitors;

-----------------------------------------------------------------------------------------------------------
-- Problem 17.  Write a SQL statement to create a table Groups.
-- Groups should have unique name (use unique constraint). Define primary key and identity column.

CREATE TABLE Groups (
  GroupID INT IDENTITY,
  GroupName NVARCHAR(10) NOT NULL,
  CONSTRAINT PK_Groups PRIMARY KEY(GroupID),
  CONSTRAINT UNQ_Groups UNIQUE(GroupName),
);

-----------------------------------------------------------------------------------------------------------
-- Problem 18.  Write a SQL statement to add a column GroupID to the table Users.
-- Fill some data in this new column and as well in the Groups table. Write a SQL statement to add a
-- foreign key constraint between tables Users and Groups tables.

ALTER TABLE Users
ADD GroupID INT FOREIGN KEY REFERENCES Groups(GroupID);
INSERT Groups VALUES ('Green');
INSERT Groups VALUES ('Blue');
INSERT Groups VALUES ('Red');
UPDATE Users SET GroupID = 1;
INSERT Users VALUES ('usr1', '12345', 'Alf',  GETDATE(), 2);
INSERT Users VALUES ('usr2', '123456', 'Jerry', GETDATE(), 2);
INSERT Users VALUES ('usr3', '1234567', 'Tom', GETDATE(), 3);
INSERT Users VALUES ('usr4', '12345678', 'Baba Qga', GETDATE(), 3);

-----------------------------------------------------------------------------------------------------------
-- Problem 19.  Write SQL statements to insert several records in the Users and Groups tables.

INSERT Groups VALUES ('Black');
INSERT Groups VALUES ('Brown');
INSERT Groups VALUES ('White');
INSERT Users VALUES ('usr5', '12345', 'Bendji',  GETDATE(), 4);
INSERT Users VALUES ('usr6', '123456', 'Zaxx', GETDATE(), 1);
INSERT Users VALUES ('usr7', '1234567', 'Star Prince', GETDATE(),5);
INSERT Users VALUES ('usr8', '12345678', 'Ohliu', GETDATE(), 6);

-----------------------------------------------------------------------------------------------------------
-- Problem 20.  Write SQL statements to update some of the records in the Users and Groups tables.

UPDATE Users SET GroupID = 1
WHERE GroupID = 2;

UPDATE Groups SET GroupName = 'Pink'
WHERE GroupName = 'Black';

-----------------------------------------------------------------------------------------------------------
-- Problem 21.  Write SQL statements to delete some of the records from the Users and Groups tables.

DELETE FROM Users WHERE FullName LIKE 'Ohliu';
DELETE FROM Groups WHERE GroupID = 6;

-----------------------------------------------------------------------------------------------------------
-- Problem 22.  Write SQL statements to insert in the Users table the names of all employees
-- from the Employees table.Combine the first and last names as a full name. For username use the first
-- letter of the first name + the last name (in lowercase). Use the same for the password,
-- and NULL for last login time.

INSERT INTO Users
SELECT LEFT(LOWER(LEFT(FirstName, 1) + LEFT(ISNULL(MiddleName, '_'), 1) + LastName), 10) AS [UserName],
       LOWER(LEFT(FirstName, 1) + LastName + 'pwd') AS [UserPassword],
       FirstName + ' ' + LastName AS [FullName],
       NULL AS [LastLogin],
       2 AS [GroupID]
FROM Employees
ORDER BY LOWER(LEFT(FirstName, 1) + LastName);

-----------------------------------------------------------------------------------------------------------
-- Problem 23.  Write a SQL statement that changes the password to NULL for all users that have not been
-- in the system since 10.03.2010.

UPDATE Users SET UserPassword = NULL
WHERE LastLogin <= CAST('2013-10-03' AS DATETIME);

-----------------------------------------------------------------------------------------------------------
-- Problem 24.  Write a SQL statement that deletes all users without passwords (NULL password).

DELETE FROM Users WHERE UserPassword IS NULL;

-----------------------------------------------------------------------------------------------------------
-- Problem 25.  Write a SQL query to display the average employee salary by department and job title.
--
-- Department   Job Title                       Average Salary
-- Finance      Accountant                      26400.00
-- Finance      Accounts Manager                34700.00
-- Finance      Accounts Payable Specialist     19000.00
-- Finance      Accounts Receivable Specialist  19000.00
-- …          …                             …

SELECT d.Name AS [Department], e.JobTitle, AVG(Salary) AS [Average Salary]
FROM Employees e
  JOIN Departments d
  ON e.DepartmentID = d.DepartmentID
GROUP BY d.Name, e.JobTitle
ORDER BY [JobTitle];

-----------------------------------------------------------------------------------------------------------
-- Problem 26.  Write a SQL query to display the minimal employee salary by department and job title along
-- with the name of some of the employees that take it.
--
-- Department   Job Title                       First Name  Min Salary
-- Engineering  Engineering Manager             Roberto     43300.00
-- Engineering  Senior Design Engineer          Michael     36100.00
-- Engineering  Vice President of Engineering   Terri       63500.00
-- Executive    Chief Executive Officer         Ken         125500.00
-- …          …                             …           …

SELECT d.Name AS [Department],
       e.JobTitle,
       e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName AS [Employee FULL Name],
       e.Salary AS [Minimal Salary]
FROM Employees e
  JOIN Departments d
  ON e.DepartmentID = d.DepartmentID
GROUP BY d.Name,
         e.JobTitle,
         e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName,
         e.Salary,
         e.DepartmentID
HAVING e.Salary = (SELECT MIN(Salary) FROM Employees
                   WHERE JobTitle = e.JobTitle AND DepartmentID = e.DepartmentID)
ORDER BY [Department];

-----------------------------------------------------------------------------------------------------------
-- Problem 27.  Write a SQL query to display the town where maximal number of employees work.
--
-- Name      Number of employees
-- Seattle  44

SELECT TOP(1) t.Name AS [Name], COUNT(e.EmployeeID) AS [NUMBER OF Employees]
FROM Employees e
  JOIN Addresses a
    ON e.AddressID = a.AddressID
  JOIN Towns t
    ON a.TownID = t.TownID
GROUP BY t.Name
ORDER BY [NUMBER OF Employees] DESC;

-----------------------------------------------------------------------------------------------------------
-- Problem 28.  Write a SQL query to display the number of managers from each town.
--
-- Town             Number of managers
-- Issaquah         3
-- Kenmore          5
-- Monroe           2
-- Newport Hills    1

SELECT t.Name AS [Town], COUNT(DISTINCT e.ManagerID) AS [NUMBER OF Managers]
FROM Employees e
  JOIN Employees m
    ON e.ManagerID = m.EmployeeID
  JOIN Addresses a
    ON m.AddressID = a.AddressID
  JOIN Towns t
    ON a.TownID = t.TownID
GROUP BY t.Name;

-----------------------------------------------------------------------------------------------------------
-- Problem 29.  Write a SQL to create table WorkHours to store work reports for each employee.
-- Each employee should have id, date, task, hours and comments. Don't forget to define identity,
-- primary key and appropriate foreign key.

CREATE TABLE WorkHours (
  WorkHourID INT IDENTITY,
  WorkDate DATETIME,
  EmployeeID INT FOREIGN KEY REFERENCES Employees(EmployeeID) NOT NULL,
  Task NVARCHAR(100) NOT NULL,
  WorkHours INT NOT NULL,
  Comment NVARCHAR(300) NULL,
  CONSTRAINT PK_WorkHours PRIMARY KEY(WorkHourID)
);

-----------------------------------------------------------------------------------------------------------
-- Problem 30.  Issue few SQL statements to insert, update and delete of some data in the table.

INSERT INTO [dbo].[WorkHours]
           ([WorkDate]
           ,[EmployeeID]
           ,[Task]
           ,[WorkHours]
           ,[Comment])
     VALUES
           (GETDATE(),
            2,
           'task 1',
           3,
           'task 1 comment');

INSERT INTO [dbo].[WorkHours]
           ([WorkDate]
           ,[EmployeeID]
           ,[Task]
           ,[WorkHours]
           ,[Comment])
     VALUES
           (GETDATE(),
            4,
           'task 2',
           3,
           'task 2 comment');

INSERT INTO [dbo].[WorkHours]
           ([WorkDate]
           ,[EmployeeID]
           ,[Task]
           ,[WorkHours]
           ,[Comment])
     VALUES
           (GETDATE(),
            2,
           'task 1',
           3,
           NULL);

UPDATE WorkHours SET Comment = 'No comment'
WHERE Comment IS NULL;

DELETE FROM WorkHours WHERE EmployeeID = 4;

-----------------------------------------------------------------------------------------------------------
-- Problem 31.  Define a table WorkHoursLogs to track all changes in the WorkHours table with triggers.
-- For each change keep the old record data, the new record data and the command (insert / update / delete).


CREATE TABLE WorkHoursLogs (
  WorkHoursLogID INT IDENTITY,
  LogCommand NVARCHAR(6) NOT NULL,
  OldWorkHourID INT NULL,
  OldWorkDate DATETIME NULL,
  OldEmployeeID INT FOREIGN KEY REFERENCES Employees(EmployeeID) NULL,
  OldTask NVARCHAR(100) NULL,
  OldWorkHours INT NULL,
  OldComment NVARCHAR(300) NULL,
  NewWorkHourID INT NULL,
  NewWorkDate DATETIME NULL,
  NewEmployeeID INT FOREIGN KEY REFERENCES Employees(EmployeeID) NULL,
  NewTask NVARCHAR(100) NULL,
  NewWorkHours INT NULL,
  NewComment NVARCHAR(300) NULL,
  CONSTRAINT PK_WorkHoursLogs PRIMARY KEY(WorkHoursLogID)
);

CREATE TRIGGER tr_WorkHoursDelete ON WorkHours FOR DELETE
AS
BEGIN
    INSERT INTO WorkHoursLogs
    SELECT 'DELETE', * , NULL, NULL, NULL, NULL, NULL, NULL
    FROM DELETED
END;

CREATE TRIGGER tr_WorkHoursUpdate ON WorkHours FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO WorkHoursLogs
    SELECT 'UPDATE',
           d.WorkHourID,
           d.WorkDate,
           d.EmployeeID,
           d.Task,
           d.WorkHours,
           d.Comment,
           i.WorkHourID,
           i.WorkDate,
           i.EmployeeID,
           i.Task,
           i.WorkHours,
           i.Comment
    FROM INSERTED i, DELETED d
END;

CREATE TRIGGER tr_WorkHoursInsert ON WorkHours FOR INSERT
AS
BEGIN
    INSERT INTO WorkHoursLogs
    SELECT 'INSERT', NULL, NULL, NULL, NULL, NULL, NULL, *
    FROM INSERTED
END;

-----------------------------------------------------------------------------------------------------------
-- Problem 32.  Start a database transaction, delete all employees from the 'Sales' department along
-- with all dependent records from the pother tables. At the end rollback the transaction.

BEGIN TRAN;

DELETE Employees FROM Employees e
 JOIN Departments d
 ON e.EmployeeID = d.DepartmentID
WHERE d.Name = 'Sales';

ROLLBACK TRAN;

-----------------------------------------------------------------------------------------------------------
-- Problem 33.  Start a database transaction and drop the table EmployeesProjects.
-- Then how you could restore back the lost table data?

BEGIN TRAN;

DROP TABLE EmployeesProjects;

ROLLBACK TRAN;

-----------------------------------------------------------------------------------------------------------
-- Problem 34.  Find how to use temporary tables in SQL Server.
-- Using temporary tables backup all records from EmployeesProjects and restore them back after
-- dropping and re-creating the table.

SELECT * INTO #TempTableProjects
FROM EmployeesProjects;

 DROP TABLE EmployeesProjects;

 CREATE TABLE EmployeesProjects
  (
   EmployeeID INT FOREIGN KEY REFERENCES Employees(EmployeeID) NOT NULL,
   ProjectID INT FOREIGN KEY REFERENCES Projects(ProjectID) NOT NULL,
  );

 INSERT INTO EmployeesProjects
 SELECT * FROM  #TempTableProjects;

 DROP TABLE tempdb.#TempTableProjects;

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