SQL-Advanced-HW

ALTER FUNCTION dbo.GetEmployeeFullName (@empId INT)
RETURNS VARCHAR(50)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
    DECLARE @fullName VARCHAR(50)
        SELECT @fullName = REPLACE(FirstName + ' ' + ISNULL(MiddleName, '') + ' ' + LastName, '  ', ' ')
        FROM Employees
        WHERE EmployeeID = @empId
    RETURN @fullName
END
GO

-- Problem 1.   Write a SQL query to find the names and salaries of the employees that take the minimal salary in the company.
SELECT dbo.GetEmployeeFullName(EmployeeId) AS [FULL Name], 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.

SELECT dbo.GetEmployeeFullName(EmployeeId) AS [FULL Name], Salary FROM Employees
WHERE Salary <= (SELECT MIN(Salary) FROM Employees) + ((SELECT MIN(Salary) FROM Employees) * 0.10)

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

SELECT dbo.GetEmployeeFullName(EmployeeID) AS [FULL Name], Salary,
(SELECT Name FROM Departments WHERE DepartmentID = emp.DepartmentID) AS [Department Name]
FROM Employees emp
WHERE Salary = (SELECT MIN(Salary) FROM Employees WHERE DepartmentID = emp.DepartmentID)

-- Problem 4.   Write a SQL query to find the average salary in the department #1.

SELECT AVG(Salary) AS [Average Salary IN Dep#1] FROM Employees
WHERE DepartmentID = 1

-- Problem 5.   Write a SQL query to find the average salary in the "Sales" department.

SELECT AVG(emp.Salary) AS [Average Salary IN Sales Dep] FROM Employees emp
INNER JOIN Departments dep ON emp.DepartmentID = dep.DepartmentID
WHERE dep.Name = 'Sales'

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

SELECT COUNT(emp.EmployeeID) AS [Employee COUNT IN Sales Dep] FROM Employees emp
INNER JOIN Departments dep ON emp.DepartmentID = dep.DepartmentID
WHERE dep.Name = 'Sales'

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

SELECT COUNT(*) AS [Employees WITH Manager COUNT] FROM Employees
WHERE ManagerID IS NOT NULL

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

SELECT COUNT(*) AS [Employees WITH No Manager COUNT] FROM Employees
WHERE ManagerID IS NULL

-- Problem 9.   Write a SQL query to find all departments and the average salary for each of them.

SELECT dep.Name AS [Department], AVG(emp.Salary) AS [Average Salary] FROM Departments dep
INNER JOIN Employees emp ON dep.DepartmentID = emp.DepartmentID
GROUP BY dep.Name

-- Problem 10.  Write a SQL query to find the count of all employees in each department and for each town.

SELECT dep.Name AS [Department], t.Name AS [Town], COUNT(emp.EmployeeID) AS [Employee COUNT] FROM Departments dep
INNER JOIN Employees emp ON dep.DepartmentID = emp.DepartmentID
INNER JOIN Addresses ads ON ads.AddressID = emp.AddressID
INNER JOIN Towns t ON t.TownID = ads.TownID
GROUP BY dep.Name, t.Name
ORDER BY dep.Name

-- Problem 11.  Write a SQL query to find all managers that have exactly 5 employees.

SELECT man.FirstName + ' ' + man.LastName AS [FULL Name] FROM Employees man
WHERE (SELECT COUNT(*) FROM Employees emp WHERE emp.ManagerID = man.EmployeeID) = 5

-- Problem 12.  Write a SQL query to find all employees along with their managers.

SELECT dbo.GetEmployeeFullName(e2.EmployeeID) AS [Employee FULL Name],
ISNULL(dbo.GetEmployeeFullName(e1.EmployeeID), 'no manager') AS [Manager] FROM Employees e1
RIGHT OUTER JOIN Employees e2 ON e1.EmployeeID = e2.ManagerID

-- Problem 13.  Write a SQL query to find the names of all employees whose last name is exactly 5 characters long.

SELECT dbo.GetEmployeeFullName(EmployeeID) 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".

SELECT REPLACE(CONVERT(NVARCHAR, getdate(), 106), ' ', '.') + ' ' + REPLACE(CONVERT(NVARCHAR, getdate(), 14), ' ', '.')

-- Problem 15.  Write a SQL statement to create a table Users.

CREATE TABLE Users
(
    Id INT PRIMARY KEY IDENTITY NOT NULL,
    Username VARCHAR(30) NOT NULL UNIQUE,
    Password VARCHAR(20) NOT NULL,
    FullName nvarchar(60) NULL,
    LastLoggedIn datetime NOT NULL,
    CONSTRAINT chk_Password CHECK (LEN(Password) > 5)
)
GO
-- 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.

CREATE VIEW vUsersLoggedToday AS
SELECT * FROM Users
WHERE DAY(LastLoggedIn) = DAY(GETDATE())
GO

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

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

-- Problem 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 ('VIP')

INSERT INTO Users VALUES ('Pencho', '123344', 'Pencho Kenchev', GETDATE(), 1)

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

UPDATE Users SET Username = 'Pencho Dage'
WHERE Id = 2

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

DELETE FROM Users
WHERE Id = 2
GO

-- Problem 22.  Write SQL statements to insert in the Users table the names of all employees from the Employees table.

INSERT INTO Users
SELECT LEFT(FirstName, 1) + LOWER(LastName) + ISNULL(LEFT(MiddleName, 1), ''), LEFT(FirstName, 1) + LEFT(LOWER(LastName) + 'pass', 5),
FirstName + ' ' + LastName, GETDATE(), 1 FROM Employees
GO

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

ALTER TABLE Users
ALTER COLUMN Password VARCHAR(20) NULL

UPDATE Users SET Password = NULL
WHERE LastLoggedIn <= '2010-03-10'

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

DELETE FROM Users
WHERE Password IS NULL

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

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

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

SELECT dep.Name AS [Department Name], emp.JobTitle, MIN(emp.Salary) AS [Minmum Salary],
emp.FirstName + ' ' + emp.LastName
FROM Employees emp
INNER JOIN Departments dep ON emp.DepartmentID = dep.DepartmentID
GROUP BY dep.Name, emp.JobTitle, emp.FirstName, emp.LastName
ORDER BY dep.Name

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

SELECT TOP 1 * FROM (SELECT t.Name, COUNT(*) AS [EmployeeCount] FROM Employees emp
INNER JOIN Addresses ads ON ads.AddressID = emp.AddressID
INNER JOIN Towns t ON t.TownID = ads.TownID
GROUP BY t.Name) ec
ORDER BY ec.EmployeeCount DESC

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

SELECT t.Name, COUNT(*) FROM Employees emp
INNER JOIN Addresses ads ON ads.AddressID = emp.AddressID
INNER JOIN Towns t ON t.TownID = ads.TownID
WHERE emp.ManagerID IS NULL
GROUP BY t.Name

-- Problem 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,
    TaskDate datetime NULL,
    Task nvarchar(125) NOT NULL,
    Hours SMALLINT NOT NULL,
    Comments ntext NULL
)
GO

ALTER TABLE WorkHours
ALTER COLUMN Comments VARCHAR(MAX) NULL

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

INSERT INTO WorkHours VALUES (GETDATE(), 'Get all things done before midnight', 4, 'Don`t forget to wash the dishes too. Бързо Бързо')
INSERT INTO WorkHours VALUES (GETDATE(), 'Don`t do anything today', 4, 'Знам че ти се прави нещо но не го прави, не слагай и запетаи даже')
UPDATE WorkHours SET TaskDate = DATEADD(MONTH, 2, GETDATE()) WHERE Id = 2
-- DELETE FROM WorkHours WHERE Id = 1

-- Problem 31.  Define a table WorkHoursLogs to track all changes in the WorkHours table with triggers.

CREATE TABLE WorkHoursLogs
(
    Id INT PRIMARY KEY IDENTITY NOT NULL,
    ChangeDate datetime NOT NULL,
    OldTaskDate datetime NULL,
    OldTask nvarchar(125) NULL,
    OldHours SMALLINT NULL,
    OldComments VARCHAR(MAX) NULL,
    NewTaskDate datetime NULL,
    NewTask nvarchar(125) NULL,
    NewHours SMALLINT NULL,
    NewComments VARCHAR(MAX) NULL,
    Command CHAR(6) NOT NULL
)
GO

CREATE TRIGGER workhours_change
ON WorkHours
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @operation CHAR(6)
        SET @operation = CASE
            WHEN EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
                THEN 'Update'
            WHEN EXISTS(SELECT * FROM inserted)
                THEN 'Insert'
            WHEN EXISTS(SELECT * FROM deleted)
                THEN 'Delete'
            ELSE NULL
        END
    IF @operation = 'Delete'
        INSERT INTO WorkHoursLogs (ChangeDate, OldTaskDate, OldTask, OldHours, OldComments, Command)
        SELECT GETDATE(), d.TaskDate, d.Task, d.Hours, d.Comments, @operation
        FROM deleted d
    IF @operation = 'Insert'
        INSERT INTO WorkHoursLogs (ChangeDate, NewTaskDate, NewTask, NewHours, NewComments, Command)
        SELECT GETDATE(), i.TaskDate, i.Task, i.Hours, i.Comments, @operation
        FROM inserted i
    IF @operation = 'Update'
        INSERT INTO WorkHoursLogs (ChangeDate, OldTaskDate, OldTask, OldHours, OldComments,
            NewTaskDate, NewTask, NewHours, NewComments, Command)
                SELECT GETDATE(), d.TaskDate, d.Task, d.Hours, d.Comments, i.TaskDate, i.Task,
                    i.Hours, i.Comments, @operation
                FROM deleted d, inserted i
END
GO


UPDATE WorkHours SET TaskDate = DATEADD(MONTH, 3, GETDATE()) WHERE Id = 2
UPDATE WorkHours SET Comments = 'I`ve changed that thing and a log is in the WorkHOursLog' WHERE Id = 1
INSERT INTO WorkHours VALUES (GETDATE() + 1, 'Reporting For Duties', 8, 'You wanna piece of me boy?!')
-- select * from WorkHoursLogs

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

ALTER TABLE Employees
DROP CONSTRAINT FK_Employees_Employees
GO
ALTER TABLE Employees
ADD CONSTRAINT FK_Employees_Employees FOREIGN KEY (ManagerID)
REFERENCES Employees(EmployeeID)
ON DELETE CASCADE
ON UPDATE NO ACTION
GO

BEGIN TRY
    BEGIN TRANSACTION

    COMMIT
END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK
END CATCH

-- Problem 33.  Start a database transaction and drop the table EmployeesProjects.

BEGIN TRAN
--DROP TABLE EmployeesProjects
ROLLBACK
SELECT * FROM EmployeesProjects

-- Problem 34.  Find how to use temporary tables in SQL Server.

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

-- Should have it`s values restored
SELECT * FROM EmployeesProjects