ADVANCED_SQL_8

USE TelerikAcademy

--1. names and salaries of the employees that take the minimal salary
--use nested SELECT
SELECT FirstName, Salary FROM Employees
GROUP BY FirstName, Salary
HAVING Salary = (SELECT MIN(Salary) FROM Employees);

--2. 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.
DECLARE @minSalary INT = (SELECT MIN(Salary) FROM Employees);
SELECT FirstName, Salary FROM Employees
GROUP BY FirstName, Salary
HAVING Salary BETWEEN @minSalary AND @minSalary + @minSalary * 0.1
ORDER BY Salary DESC;

--3. find the full name, salary and department of the employees that take the minimal salary in their department.
SELECT e.FirstName, e.Salary, d.Name
FROM Employees e JOIN Departments d 
ON e.DepartmentID = d.DepartmentID
WHERE e.Salary IN 
(SELECT MIN(Salary) FROM Employees WHERE DepartmentID = e.DepartmentID)

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

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

--6 Write a SQL query to find the number of employees in the "Sales" department.
SELECT COUNT(*) FROM Employees e JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.Name = 'Sales'
--the same as
SELECT COUNT(*) FROM Employees
WHERE DepartmentID IN
(SELECT DepartmentID FROM Departments
WHERE Name = 'Sales')

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

--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 Departments d JOIN Employees e ON d.DepartmentID = e.DepartmentID
GROUP BY d.Name;

SELECT DISTINCT e.DepartmentID, (SELECT AVG(Salary) FROM Employees WHERE EmployeeID = e.DepartmentID) FROM 
Employees AS e

-- 10 Write a SQL query to find the count of all employees in each department and for each town.*/
BEGIN
	--Total employees in departmentId
	SELECT COUNT(*) FROM Employees WHERE DepartmentID = 1/*ID*/
	--Total employees in town
	SELECT COUNT(*) FROM 
		Employees e JOIN Addresses a ON e.AddressID=a.AddressID 
	WHERE a.TownID = 1--townId

	--Total employees for each town
	SELECT Name as [Town Name],
	(SELECT COUNT(*) FROM Employees e JOIN Addresses a ON e.AddressID=a.AddressID WHERE a.TownID = t.TownID) AS [TotalEmployees]
	FROM Towns t
	--Total employees for each department
	SELECT Name as [Department Name],
	(SELECT COUNT(*) FROM Employees WHERE DepartmentID = d.DepartmentID) AS [TotalEmployees] 
	FROM Departments d
END

-- 11 Write a SQL query to find all managers that have exactly 5 employees. Display their first name and last name.
BEGIN
	--Find how many times ManagerId=5
	SELECT COUNT(*) FROM Employees WHERE ManagerID = 64/*input*/

	--test solution
	SELECT m.FirstName, m.LastName FROM Employees e RIGHT OUTER JOIN Employees m ON e.ManagerID=m.EmployeeID WHERE
	(SELECT COUNT(*) FROM Employees WHERE ManagerID = e.ManagerID) = 5/*current ManagerId COUNT = 5*/
	GROUP BY m.FirstName, m.LastName
END

-- 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)".
BEGIN
	SELECT e.FirstName AS [Employee], ISNULL(m.FirstName, 'No Manager') AS [Manager]
		FROM Employees e LEFT JOIN Employees m 
			ON e.ManagerID = m.EmployeeID 
END

-- 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.
BEGIN
	SELECT LastName FROM Employees WHERE LEN(LastName) = 5
END
-- 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.
BEGIN
	select format(getdate(), 'dd.MM.yyyy HH:mm:ss:fff')
END
/*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.
*/
BEGIN
	CREATE TABLE Users
	(
		id INT NOT NULL IDENTITY(1,1),
		[user_name] NVARCHAR(50) NOT NULL UNIQUE,
		[password] NVARCHAR(50)/*VARBINARY(50)*/ NOT NULL,
		[full_name] NVARCHAR(100),
		[last_login] DATETIME,
		CONSTRAINT pk_users_id PRIMARY KEY(id),
		CONSTRAINT uc_users_username UNIQUE(user_name),
		CONSTRAINT chk_pass_length CHECK(LEN(password) >= 5)
	);
END

/* ..that inserts data into the Users table*/
BEGIN
	INSERT INTO Users (user_name, password, full_name, last_login)
	VALUES('user1','pass1','Ventsislav Marinov', GETDATE())

	INSERT INTO Users (user_name, password, full_name, last_login)
	VALUES('user2','pass1','Gabriel Marinov', CAST('2017-11-18' as datetime))

	INSERT INTO Users (user_name, password, full_name, last_login)
	VALUES('user3','pass1','Gabriel Ivanov', CAST('2017-11-17' as datetime))

	INSERT INTO Users (user_name, password, full_name, last_login)
	VALUES('user4','pass1','Gabriel Marinov', CAST('2017-11-18' as datetime));
END;

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.
Test if the view works correctly.
*/
CREATE VIEW [v_users_logged_2017_11_19] AS 
	SELECT user_name
	FROM Users
	WHERE CAST(last_login as DATE) = CAST(GETDATE() as DATE);
GO

/*17 Write a SQL statement to create a table Groups. Groups should have unique name (use unique constraint).
Define primary key and identity column.*/
BEGIN
	CREATE TABLE [groups]
	(
		[id] INT NOT NULL IDENTITY(1,1),
		[name] NVARCHAR(50) NOT NULL,
		CONSTRAINT [pk_groups_id] PRIMARY KEY(id),
		CONSTRAINT [uq_groups_name] UNIQUE(name)
	)
END

/*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.
*/
BEGIN
	ALTER TABLE Users
		ADD GroupId INT		

	ALTER TABLE Users
		ADD CONSTRAINT FK_GroupId_Groups FOREIGN KEY (GroupId) REFERENCES Groups(id);

	SELECT * FROM USERS
	SELECT * FROM GROUPS
	INSERT INTO Groups VALUES('Group1'),('Group2'),('Group3')
	UPDATE Users
	SET GroupId = 2
	WHERE id = 3

END

/*19 Write SQL statements to insert several records in the Users and Groups tables.*/
--DONE ABOVE

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

/*21 Write SQL statements to delete some of the records from the Users and Groups tables.*/
BEGIN
	DELETE FROM Users
		WHERE id = 15
END

/*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.*/
BEGIN
	SELECT * FROM USERS ORDER BY user_name
	DELETE FROM USERS
	
	--NOT FINISHED. ERROR: Cannot insert duplicate key in object 'dbo.Users'
	INSERT INTO Users (user_name, password, full_name, last_login)
	SELECT LOWER(LEFT(FirstName, 1) + LastName),
		   LOWER(LEFT(FirstName, 1) + LastName + 'filling'),
		   LOWER(FirstName + LastName),
		   NULL
	FROM Employees
	--WHERE LOWER(LEFT(FirstName, 1) + LastName) NOT IN (SELECT user_name FROM Users)	
	--Strange why the above check isn't solving

	SELECT *
	FROM Employees WHERE LOWER(LEFT(FirstName, 1) + LastName) = 'ahill'
END

/*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.*/
BEGIN
	UPDATE Users
	SET password = 'nullchec'--use nulchec because I made the password NOT NULL
	WHERE last_login <= CONVERT(DATETIME, '10.03.2010' )

	--test code 
	SELECT * FROM Users
	SELECT CONVERT(DATETIME, '10.03.2010' )
	DELETE FROM Users

	INSERT INTO Users (user_name, password, full_name, last_login)
	VALUES('user2','pass1','Gabriel Marinov', CAST('2017-11-18' as datetime))
	INSERT INTO Users (user_name, password, full_name, last_login)
	VALUES('user3','pass1','Gabriel Marinov', CAST('2007-11-18' as datetime))
END

/*24 Write a SQL statement that deletes all users without passwords (NULL password).*/
BEGIN
	DELETE FROM Users
		WHERE password = 'nullchec';--nullchec because my password is NOT NULL

		SELECT * FROM USERS
END

/*25 Write a SQL query to display the average employee salary by department and job title.*/
BEGIN
	--avg employee salary by job title
	SELECT JobTitle, AVG(Salary) as [Avg Salary] FROM Employees GROUP BY JobTitle
	--avg employee salary by department title
	SELECT d.Name, AVG(Salary) as [Avg Salary] FROM Employees e
		JOIN Departments d ON e.DepartmentID = d.DepartmentID
	 GROUP BY d.Name
END

/*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.*/
BEGIN
	 --Select employee where salary = min for department
	 SELECT * FROM Employees WHERE FirstName = 'Barbara'

     SELECT e.JobTitle, MIN(e.Salary) as [Min Salary], 
			(SELECT TOP 1 FirstName FROM Employees WHERE JobTitle = e.JobTitle AND 
				Salary = (SELECT MIN(Salary) FROM Employees WHERE JobTitle = e.JobTitle)) AS [Employee]
	 FROM Employees e
	 GROUP BY e.JobTitle

	 --select first person with lowest salary for the department/job
	 SELECT TOP 1 FirstName FROM Employees WHERE EmployeeID = ? AND 
	 Salary = (SELECT MIN(Salary) FROM Employees WHERE EmployeeID = ?)

	 --select min salary by job title
	 SELECT MIN(Salary) FROM Employees WHERE JobTitle = 'PARENT'

	 SELECT d.Name, MIN(Salary) as [Min Salary],
		(SELECT TOP 1 FirstName FROM Employees WHERE DepartmentID = d.DepartmentID AND 
			Salary = (SELECT MIN(Salary) FROM Employees WHERE DepartmentID = d.DepartmentID)) AS [Employee]
	 FROM Employees e
		JOIN Departments d ON e.DepartmentID = d.DepartmentID
	 GROUP BY d.Name, d.DepartmentID
END

/*27Write a SQL query to display the town where maximal number of employees work.*/
BEGIN 
	--select #employees from ?town
	SELECT TOP 1 Name as [Town Name],
	(
		SELECT COUNT(*) FROM Employees e JOIN Addresses a ON e.AddressID=a.AddressID WHERE a.TownID = t.TownID
	) AS [TotalEmployees]
	FROM Towns t ORDER BY TotalEmployees DESC
END

/*28 Write a SQL query to display the number of managers from each town.*/
BEGIN
	--all managers
	(SELECT m.* FROM Employees e JOIN Employees m ON e.ManagerID=m.EmployeeID) AS managers

	--all managers count from town
	SELECT COUNT(*) FROM (SELECT m.* FROM Employees e JOIN Employees m ON e.ManagerID=m.EmployeeID) AS managers
	JOIN Addresses a ON managers.AddressID=a.AddressID
	WHERE a.TownID = GIMME

	SELECT t.Name, (SELECT COUNT(*) FROM (SELECT m.* FROM Employees e JOIN Employees m ON e.ManagerID=m.EmployeeID) AS managers
	JOIN Addresses a ON managers.AddressID=a.AddressID
	WHERE a.TownID = t.TownID) AS [Managers Count] FROM Towns t
END

/*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 WorkHours
(
	[Id] INT IDENTITY(1,1),
	[EmployeeId] INT NOT NULL,
	[Date] DATE,
	[Task] NVARCHAR(50),
	[Comments] NVARCHAR(200),
	CONSTRAINT PK_WorkHours PRIMARY KEY(Id),
	CONSTRAINT FK_WorkHours FOREIGN KEY (EmployeeId) REFERENCES Employees(EmployeeId)
)

CREATE TABLE WorkHoursLogs
(
	[EmployeeId] INT NOT NULL,
	[Date] DATE,
	[Task] NVARCHAR(50),
	[Comments] NVARCHAR(200),
	[Command] NVARCHAR(50)
)

GO
CREATE TRIGGER WorkHours_trigger_afterDelete ON WorkHours
AFTER DELETE
AS
	DECLARE @empId INT;
	DECLARE @date DATE;
	DECLARE @task NVARCHAR(50);
	DECLARE @comments NVARCHAR(200);
	DECLARE @command NVARCHAR(50);

	SELECT @empId = [EmployeeId] FROM deleted d;
	SELECT @date = [Date] FROM deleted d;
	SELECT @task = [Task] FROM deleted d;
	SELECT @comments = [Comments] FROM deleted d;
	SELECT @command = 'After Delete trigger';

	INSERT INTO WorkHoursLogs VALUES
	(
		@empId, @date, @task, @comments, @command
	)

	GO

CREATE TRIGGER WorkHours_trigger_afterInsert ON WorkHours
	AFTER INSERT
	AS
	DECLARE @empId INT;
	DECLARE @date DATE;
	DECLARE @task NVARCHAR(50);
	DECLARE @comments NVARCHAR(200);
	DECLARE @command NVARCHAR(50);

	SELECT @empId = [EmployeeId] FROM inserted i;
	SELECT @date = [Date] FROM inserted i;
	SELECT @task = [Task] FROM inserted i;
	SELECT @comments = [Comments] FROM inserted i;
	SELECT @command = 'After Insert trigger';

	INSERT INTO WorkHoursLogs VALUES
	(
		@empId, @date, @task, @comments, @command
	)GO

CREATE TRIGGER WorkHours_trigger_afterUpdate ON WorkHours
	AFTER UPDATE
	AS
	DECLARE @empId INT;
	DECLARE @date DATE;
	DECLARE @task NVARCHAR(50);
	DECLARE @comments NVARCHAR(200);
	DECLARE @command NVARCHAR(50);

	SELECT @empId = [EmployeeId] FROM inserted i;
	SELECT @date = [Date] FROM inserted i;
	SELECT @task = [Task] FROM inserted i;
	SELECT @comments = [Comments] FROM inserted i;
	SELECT @command = 'After Update trigger';

	INSERT INTO WorkHoursLogs VALUES
	(
		@empId, @date, @task, @comments, @command
	)

	GO

SELECT * FROM WorkHours		
SELECT * FROM WorkHoursLogs

INSERT INTO WorkHours VALUES
(
	1, GETDATE(), 'Debugging', 'Comments on debugging'
)

UPDATE WorkHours
SET Task = 'Coding', Comments = 'Comments on coding'
WHERE EmployeeId = 1

DELETE FROM WorkHours
WHERE EmployeeId = 1

/*30 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_Addresses,
		 CONSTRAINT FK_Employees_Departments,
		 CONSTRAINT FK_Employees_Employees

ALTER TABLE Employees
		ADD CONSTRAINT FK_Employees_Addresses_Cascade FOREIGN KEY(AddressID) REFERENCES Addresses(AddressID) ON DELETE CASCADE,
			CONSTRAINT FK_Employees_Departments_Cascade FOREIGN KEY(DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE CASCADE,
			CONSTRAINT FK_Employees_Employees_Cascade FOREIGN KEY(ManagerID) REFERENCES Employees(EmployeeID);

ALTER TABLE Departments
	DROP CONSTRAINT FK_Departments_Employees

ALTER TABLE Departments
	ADD CONSTRAINT FK_Departments_Employees_Cascade FOREIGN KEY(DepartmentID) REFERENCES Employees(DepartmentID)




BEGIN TRANSACTION [Trans30]

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

	COMMIT TRANSACTION [Trans30]
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;  
ROLLBACK TRANSACTION [Trans30]
END CATCH

ROLLBACK TRANSACTION [Trans30]-- I Commited the transaction in the end of the try, therefore cannot ROLLBACK
--Solution? Restore DB from backup file and copy the Employees data into the current employees table

GO

SELECT * FROM Employees e JOIN Departments d ON e.DepartmentID = d.DepartmentID WHERE d.Name = 'Sales'

/*31 Start a database transaction and drop the table EmployeesProjects.
Now how you could restore back the lost table data?*/
--SImple answer-ROLLBACK
BEGIN TRANSACTION [Trans31]
BEGIN TRY
	DROP TABLE EmployeesProjects
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;  
ROLLBACK TRANSACTION [Trans30]
END CATCH

ROLLBACK 

GO

/*32 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.*/
 BEGIN
	SELECT * INTO #EmployeesProjectsTemp
	FROM EmployeesProjects

	SELECT * FROM #EmployeesProjectsTemp

	-- drop and then just copy the results from #temp
 END