Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment