Advanced-SQL

IF EXISTS (SELECT
    1
FROM Information_schema.Routines
WHERE Specific_schema = 'dbo'
AND specific_name = 'getFullNameOfEmployee'
AND Routine_Type = 'FUNCTION') DROP FUNCTION getFullNameOfEmployee
GO
CREATE FUNCTION dbo.getFullNameOfEmployee(@FirstName nvarchar(MAX), @MiddleName nvarchar(MAX), @LastName nvarchar(MAX))
RETURNS nvarchar(MAX)
AS
BEGIN
    DECLARE @FullName nvarchar(MAX) = @FirstName + ISNULL(' ' + @MiddleName, '') + ' ' + @LastName;
    RETURN @FullName
END;
GO

--1. Write a SQL query to find the names and salaries of the employees that take the minimal salary in the company.
SELECT
    FirstName,
    LastName,
    Salary
FROM Employees
WHERE Salary = (SELECT
    MIN(Salary)
FROM Employees)

--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.
SELECT
    FirstName,
    LastName,
    Salary
FROM Employees
WHERE Salary <= (SELECT
    MIN(Salary) * 1.10
FROM Employees)

--3. Write a SQL query to find the full name, salary and department
-- of the employees that take the minimal salary in their department.
SELECT
    dbo.getFullNameOfEmployee(e.FirstName, e.MiddleName, e.LastName) AS [FULL Name],
    Salary,
    d.Name AS Department
FROM Employees e
JOIN Departments d
    ON e.DepartmentID = d.DepartmentID
WHERE Salary = (SELECT
    MIN(Salary)
FROM Employees)

--4. Write a SQL query to find the average salary in the department #1.
SELECT
    AVG(Salary) AS [Average Salary],
    MIN(DepartmentID) AS [Department]
FROM Employees
WHERE DepartmentID = 1

--5. Write a SQL query to find the average salary in the "Sales" department.
SELECT
    AVG(e.Salary) AS [Average Salary],
    MIN(d.Name) AS [Department]
FROM Employees e
JOIN Departments d
    ON e.DepartmentID = d.DepartmentID
WHERE d.Name = 'Sales'

--6. Write a SQL query to find the number of employees in the "Sales" department.
SELECT
    COUNT(*) AS [NUMBER OF Employees],
    MIN(d.Name) AS [Department]
FROM Employees e
JOIN Departments d
    ON e.DepartmentID = d.DepartmentID
WHERE d.Name = 'Sales'

--7. Write a SQL query to find the number of all employees that have manager.
SELECT
    COUNT(ManagerID)
FROM Employees

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

--9. Write a SQL query to find all departments and the average salary for each of them.
SELECT
    d.Name,
    AVG(e.Salary)
FROM Employees e
JOIN Departments d
    ON e.DepartmentID = d.DepartmentID
GROUP BY    d.DepartmentID,
            d.Name
ORDER BY d.Name

--10. Write a SQL query to find the count of all employees in each department and for each town.
SELECT
    COUNT(*) AS [COUNT OF Employees],
    d.Name AS [Department Name],
    t.Name AS [Town Name]
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    d.DepartmentID,
            d.Name,
            t.TownID,
            t.Name
ORDER BY d.Name

--11. Write a SQL query to find all managers that have exactly 5 employees.
SELECT
    m.FirstName,
    m.LastName,
    COUNT(e.ManagerID) AS [Employees COUNT]
FROM Employees e
JOIN Employees m
    ON e.ManagerID = m.EmployeeID
GROUP BY    m.ManagerID,
            m.FirstName,
            m.LastName
HAVING COUNT(e.EmployeeID) = 5
ORDER BY m.FirstName

--12. Write a SQL query to find all employees along with their managers.
SELECT
    e.FirstName,
    ISNULL(m.FirstName + ' ' + m.LastName, '(no manager)') AS Manager
FROM Employees e
LEFT OUTER JOIN Employees m
    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.
SELECT
    dbo.getFullNameOfEmployee(FirstName, MiddleName, LastName) AS [FULL Name]
FROM Employees
WHERE LEN(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".
SELECT
    CONVERT(NVARCHAR, GETDATE(), 4) + ' '
    + CONVERT(NVARCHAR, GETDATE(), 114) AS DateTime

--15. Write a SQL statement to create a table Users.
CREATE TABLE Users
(
    Id INT PRIMARY KEY IDENTITY NOT NULL,
    Username VARCHAR(100) NOT NULL UNIQUE,
    Password VARCHAR(100),
    FullName nvarchar(100) NULL,
    LastLoggedIn datetime,
    CONSTRAINT check_Password CHECK (LEN(Password) > 5)
)
GO

--16. Write a SQL statement to create a view that displays the users from the Users table
--that have been in the system today.
CREATE VIEW vUsersLoggedToday AS
SELECT
    *
FROM Users
WHERE DAY(LastLoggedIn) = DAY(GETDATE())
GO

--17. Write a SQL statement to create a table Groups.
CREATE TABLE Groups
(
    Id INT PRIMARY KEY IDENTITY NOT NULL,
    Name VARCHAR(50) UNIQUE NULL
)
GO

--18. Write a SQL statement to add a column GroupID to the table Users.
ALTER TABLE Users
ADD GroupId INT NOT NULL,
FOREIGN KEY(GroupId) REFERENCES Groups(Id)
GO

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

INSERT INTO Users
    VALUES ('Goshko', '123456', 'Gosho Goshov', GETDATE(), 1)

--20. Write SQL statements to update some of the records in the Users and Groups tables.
UPDATE Users
SET Username = 'Goshko1'
WHERE Id = 3

--21. Write SQL statements to delete some of the records from the Users and Groups tables.
DELETE FROM Users
WHERE Id = 2
GO

--22. Write SQL statements to insert in the Users table the names of all employees from the Employees table.
INSERT INTO Users (UserName, Password, FullName, LastLoggedIn, GroupId)
    SELECT
        LOWER(LEFT(FirstName, 1) + LastName) + CAST(EmployeeID AS NVARCHAR(10)),
        LOWER(LEFT(FirstName, 1) + LastName) + '123',
        FirstName + ' ' + LastName,
        NULL,
        2
    FROM Employees
GO

--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
WHERE LastLoggedIn <= '2010-03-10'

--24. Write a SQL statement that deletes all users without passwords (NULL password).
DELETE FROM Users
WHERE Password IS NULL

--25. Write a SQL query to display the average employee salary by department and job title.
SELECT
    d.Name AS [Department Name],
    e.JobTitle AS [Job Title],
    AVG(Salary) AS [Average Salary]
FROM Employees e
JOIN Departments d
    ON e.DepartmentID = d.DepartmentID
GROUP BY    d.Name,
            e.JobTitle

--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
    d.Name AS [Department Name],
    e.JobTitle AS [Job Title],
    MIN(e.FirstName) AS [FIRST Name],
    MIN(Salary) AS [Minimum Salary]
FROM Employees e
JOIN Departments d
    ON e.DepartmentID = d.DepartmentID
GROUP BY    d.Name,
            e.JobTitle
ORDER BY d.Name

--27. Write a SQL query to display the town where maximal number of employees work.
SELECT TOP 1
    *
FROM (SELECT
    t.Name,
    COUNT(*) 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) TownsAndNumberOfEmployees
ORDER BY TownsAndNumberOfEmployees.[NUMBER OF Employees] DESC

--28. Write a SQL query to display the number of managers from each town.
SELECT
    em.Town,
    COUNT(*) AS [Employees COUNT]
FROM (SELECT DISTINCT
    e.EmployeeID,
    t.Name AS Town
FROM Employees e
JOIN Employees m
    ON m.ManagerID = e.EmployeeID
JOIN Addresses a
    ON e.AddressID = a.AddressID
JOIN Towns t
    ON a.TownID = t.TownID) em
GROUP BY em.Town

--29. Write a SQL to create table WorkHours to store work reports for each employee.
CREATE TABLE WorkHours
(
        Id INT PRIMARY KEY IDENTITY NOT NULL,
        EmployeeId INT FOREIGN KEY REFERENCES Employees(EmployeeId)  NOT NULL,
        DATE datetime NULL,
        Task nvarchar(150) NOT NULL,
        Hours INT NOT NULL,
        Comments ntext NULL

)
GO

--30. Issue few SQL statements to insert, update and delete of some data in the table.
--,31. Define a table WorkHoursLogs to track all changes in the WorkHours table with triggers.

--31
CREATE TABLE WorkHoursLogs
(
        Id INT PRIMARY KEY IDENTITY NOT NULL,
        Message nvarchar(150) NOT NULL,
        DateOfChange datetime NOT NULL
)

GO

CREATE TRIGGER  tr_WorkHoursInsert
ON WorkHours
 FOR INSERT
AS
INSERT INTO WorkHoursLogs (Message, DateOfChange)
    VALUES ('row added', GETDATE())
GO

CREATE TRIGGER  tr_WorkHoursDelete
ON WorkHours
 FOR DELETE
AS
INSERT INTO WorkHoursLogs (Message, DateOfChange)
    VALUES ('row deleted', GETDATE())
GO

CREATE TRIGGER  tr_WorkHoursUpdate
ON WorkHours
 FOR UPDATE
AS
INSERT INTO WorkHoursLogs (Message, DateOfChange)
    VALUES ('row updated', GETDATE())
GO

--30
INSERT INTO WorkHours (EmployeeId, DATE, Task, Hours)
    VALUES (10, GETDATE(), 'Random task', 11)

INSERT INTO WorkHours (EmployeeId, DATE, Task, Hours)
    VALUES (11, GETDATE(), 'yeah, yeah, blabla', 34)

DELETE WorkHours
WHERE EmployeeId = 11

UPDATE WorkHours
SET Task = 'updated task'
WHERE EmployeeId = 11

SELECT
    *
FROM WorkHoursLogs

--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
WHERE DepartmentID = (SELECT
        DepartmentID
    FROM Departments
    WHERE Name = 'Sales')
ROLLBACK TRAN

--33. Start a database transaction and drop the table EmployeesProjects.
BEGIN TRAN
DROP TABLE EmployeesProjects
ROLLBACK TRAN

--34. Find how to use temporary tables in SQL Server.
DECLARE @temporaryEmployeesProjTable TABLE
(
        EmployeeID INT NOT NULL,
        ProjectID INT NOT NULL
)

INSERT INTO @temporaryEmployeesProjTable
    SELECT
        EmployeeID,
        ProjectID
    FROM EmployeesProjects

DROP TABLE EmployeesProjects

CREATE TABLE EmployeesProjects
(
        EmployeeID INT NOT NULL,
        ProjectID INT NOT NULL
)
INSERT INTO EmployeesProjects
    SELECT
        *
    FROM @temporaryEmployeesProjTable
GO