04.AdvancedSQL-AllTasks

USE [TelerikAcademy]
-- 01.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.

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

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

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary <=
    (SELECT MIN(Salary) FROM Employees) * 1.1
ORDER BY Salary DESC

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

SELECT FirstName, LastName, Salary, DepartmentID
FROM Employees e
WHERE Salary =
        (
            SELECT MIN(Salary)
            FROM Employees
            WHERE DepartmentID = e.DepartmentID
        )

-- 04. Write a SQL query to find the average salary in the department #1.
SELECT AVG(Salary) AS AverageSalary
FROM Employees
WHERE DepartmentID = 1

-- 05. Write a SQL query to find the average salary  in the "Sales" department.
SELECT AVG(e.Salary) AS AverageSalary
FROM Employees e
    JOIN Departments d
    ON e.DepartmentID = d.DepartmentID
WHERE d.Name = 'Engineering'

-- 06. Write a SQL query to find the number of employees in the "Sales" department.
SELECT COUNT(*) AS EmployeesCount, d.Name AS Department-- it could be COUNT(e.FirstName)
FROM Employees e
JOIN Departments d
    ON e.DepartmentID = d.DepartmentID
WHERE d.Name = 'Sales'
GROUP BY d.Name

-- 07. Write a SQL query to find the number of all employees that have manager.
SELECT COUNT(*) AS EmployeesCount
FROM Employees
WHERE ManagerID IS NOT NULL

-- 08. Write a SQL query to find the number of all employees that have no manager.
SELECT COUNT(*) AS EmployeesCount
FROM Employees
WHERE ManagerID IS NULL

-- 09.Write a SQL query to find all departments and the average salary for each of them.
SELECT DepartmentID, AVG(Salary)
FROM Employees
GROUP BY DepartmentID

-- 10. Write a SQL query to find the count of all employees in each department and for
-- each town.
SELECT  DepartmentID, COUNT(*) AS EmployeesCount, t.Name
FROM Employees e
    JOIN Addresses a
    ON a.AddressID = e.AddressID
    JOIN Towns t
    ON a.TownID = t.TownID
GROUP BY e.DepartmentID, t.Name
ORDER BY e.DepartmentID

-- 11. Write a SQL query to find all managers that have exactly 5 employees. Display their
-- first name and last name.
SELECT e.FirstName, e.LastName, e.EmployeeID
FROM Employees e
WHERE 5 =
        (
            SELECT COUNT(*)
            FROM Employees
            WHERE ManagerID = e.EmployeeID
        )

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

SELECT e.FirstName, e.LastName,  ISNULL(m.FirstName+' '+m.LastName, 'no manager') AS ManagerFullName
FROM Employees m
    RIGHT JOIN Employees e
    ON e.ManagerID = m.EmployeeID

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

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

-- 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
SELECT  CONVERT(VARCHAR(25), GETDATE(), 131) AS [DATE]
SELECT  CONVERT(VARCHAR(25), GETDATE(), 121) AS [DATE]

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

-- username, password, fullname, lastlogin
CREATE TABLE Users
    (
        UserID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
        Username nvarchar(20) UNIQUE NOT NULL,
        [Password] nvarchar(20) CHECK(LEN([Password]) > 4) NOT NULL,
        Fullname nvarchar(100) NOT NULL,
        LastLogin datetime
    )

-- 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 RecentUsers
AS
    SELECT Username, DAY(GETDATE() - LastLogin) AS DayDifference
    FROM Users
    WHERE DAY(GETDATE() - LastLogin) = 1

-- 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 PRIMARY KEY IDENTITY(1,1),
            Name nvarchar(20) UNIQUE
        )

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

ALTER TABLE Users
    ADD CONSTRAINT FK_UsersGroup FOREIGN KEY(GroupID) REFERENCES Groups(GroupID)

-- 19. Write SQL statements to insert several records in the Users and
-- Groups tables.
INSERT INTO Groups
VALUES('Students')

INSERT INTO Groups
VALUES('Proffessors')

INSERT INTO Groups
VALUES('Administrators')

INSERT INTO Users
VALUES
    ('student1', 'student1pass', 'Studentov-1', GETDATE(), 2),
    ('student2', 'student2pass', 'Studentov-2', GETDATE(), 1),
    ('student3', 'student3pass', 'Studentov-3', GETDATE(), 1)

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

UPDATE Users
SET Username = 'studentche2', Password = 'studentche2pass'
FROM Users
WHERE Username = 'student1'

UPDATE Users
SET Username = 'student1', Password = 'student1pass'
FROM Users
WHERE Username = 'studentche2'

UPDATE Groups
SET Name = 'Uchenici'
FROM Groups
WHERE GroupID = 1

UPDATE Groups
SET Name = 'Students'
FROM Groups
WHERE GroupID = 1

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

DELETE FROM Users
WHERE Username = 'student2'

DELETE FROM Users
WHERE UserID = 8

-- all users from connected with the group should be deleted
DELETE FROM Groups
WHERE GroupID = 3

ROLLBACK TRAN

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

-- there are duplicate if we get the only the first letter from the FirstName
-- so I make the username with the first 3 letters
INSERT INTO Users(Username, [Password], Fullname, GroupID)
SELECT  LOWER(LEFT(FirstName,3)+LastName),
        LOWER(LEFT(FirstName,3)+LastName),
        FirstName+' ' + LastName,
        1
FROM Employees

-- 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 [Password] = NULL
FROM Users
WHERE   LastLogin < CONVERT(datetime, '10-03-2010')
        AND [Password] IS NOT NULL

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

BEGIN TRAN
DELETE FROM Users
WHERE [Password] IS NULL

ROLLBACK TRAN

-- 25. Write a SQL query to display the average employee salary by
-- department and job title.

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

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

SELECT e.FirstName+' '+e.LastName AS FullName, e.Salary, e.JobTitle, d.Name
FROM Employees e
    JOIN Departments d
    ON e.DepartmentID = d.DepartmentID
GROUP BY e.JobTitle, d.Name, e.Salary,  e.FirstName+' '+e.LastName, e.DepartmentID
HAVING e.Salary =
    (
        SELECT MIN(Salary)
        FROM Employees
        -- should check MIN(Salary) by JobTitle AND DepartmentID
        WHERE JobTitle = e.JobTitle AND DepartmentID = e.DepartmentID
    )
ORDER BY e.Salary DESC


-- 27. Write a SQL query to display the town where
-- maximal number of employees work.

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

-- 28. Write a SQL query to display the number of
-- managers from each town.

SELECT COUNT(DISTINCT e.ManagerID), t.Name
FROM Employees e
    JOIN Employees m
    ON e.ManagerID = m.EmployeeID
    JOIN Addresses a
    ON a.AddressID = m.AddressID
    JOIN Towns t
    ON a.TownID = t.TownID
GROUP BY t.Name

-- 29. Write a SQL to create table WorkHours to store work
-- reports for each employee (employee id, date, task, hours,
-- comments). Don't forget to define  identity, primary key and
-- appropriate foreign key.
-- Issue few SQL statements to insert, update and delete of
-- some data in the table.
-- 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 Tasks
        (
            TaskID INT IDENTITY(1,1) PRIMARY KEY,
            NAME nvarchar(50) NOT NULL
        )

CREATE TABLE WorkHours
        (
            WorkHoursID INT IDENTITY(1,1) PRIMARY KEY,
            EmployeeID INT FOREIGN KEY(EmployeeID) REFERENCES Employees(EmployeeID) NOT NULL,
            [DATE] datetime NOT NULL,
            TaskID INT FOREIGN KEY(TaskID) REFERENCES Tasks(TaskID) NOT NULL,
            [Hours] INT NULL,
            Comments nvarchar(250) NULL,
        )

INSERT INTO WorkHours
    VALUES(5, '2013-07-12', 4, NULL, NULL)

CREATE TABLE WorkHoursLog
        (
            LogID INT IDENTITY(1,1) PRIMARY KEY,
            ExecutedCommand nvarchar(20) NULL,
            WorkHoursID INT NULL,
            OldEmployeeID INT FOREIGN KEY(OldEmployeeID) REFERENCES Employees(EmployeeID) NULL,
            [OldDate] datetime NULL,
            OldTaskID INT FOREIGN KEY(OldTaskID) REFERENCES Tasks(TaskID) NULL,
            [OldHours] INT NULL,
            OldComments nvarchar(250) NULL,
            NewEmployeeID INT FOREIGN KEY(NewEmployeeID) REFERENCES Employees(EmployeeID) NULL,
            [NewDate] datetime NULL,
            NewTaskID INT FOREIGN KEY(NewTaskID) REFERENCES Tasks(TaskID) NULL,
            [NewHours] INT NULL,
            NewComments nvarchar(250) NULL
        )

ALTER TRIGGER TR_WorkHoursDelete
ON WorkHours
FOR DELETE
AS
    INSERT INTO WorkHoursLog
    SELECT 'DELETE', *, NULL, NULL, NULL, NULL, NULL
    FROM deleted

GO

ALTER TRIGGER TR_WorkHoursInsert
ON WorkHours
FOR INSERT
AS
    INSERT INTO WorkHoursLog
    SELECT 'INSERT', WorkHoursID,NULL, NULL, NULL, NULL, NULL,
        EmployeeID, [DATE], TaskID, [Hours], Comments
    FROM inserted

GO

ALTER TRIGGER TR_WorkHoursUpdate
ON WorkHours
FOR UPDATE
AS
    INSERT INTO WorkHoursLog
    SELECT 'UPDATE', d.WorkHoursID, d.EmployeeID, d.[DATE], d.TaskID, d.[Hours], d.Comments,
    i.EmployeeID, i.[DATE], i.TaskID, i.[Hours], i.Comments
    FROM inserted i, deleted d
GO


-- TESTING TRIGGERS --
DELETE FROM WorkHours WHERE WorkHoursID = 3

INSERT INTO WorkHours
    VALUES(5, '2013-07-12', 4, NULL, NULL)

UPDATE WorkHours
SET Hours = 123
FROM WorkHours
WHERE WorkHoursID = 8;
-- END TESTING --