SHARE
TWEET

SQL-Advanced-HW

a guest Feb 12th, 2015 736 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ALTER FUNCTION dbo.GetEmployeeFullName (@empId INT)
  2. RETURNS VARCHAR(50)
  3. WITH RETURNS NULL ON NULL INPUT
  4. AS
  5. BEGIN
  6.         DECLARE @fullName VARCHAR(50)
  7.                 SELECT @fullName = REPLACE(FirstName + ' ' + ISNULL(MiddleName, '') + ' ' + LastName, '  ', ' ')
  8.                 FROM Employees
  9.                 WHERE EmployeeID = @empId
  10.         RETURN @fullName
  11. END
  12. GO
  13.  
  14. -- Problem 1.   Write a SQL query to find the names and salaries of the employees that take the minimal salary in the company.
  15. SELECT dbo.GetEmployeeFullName(EmployeeId) AS [FULL Name], Salary FROM Employees
  16. WHERE Salary = (SELECT MIN(Salary) FROM Employees)
  17.  
  18. -- 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.
  19.  
  20. SELECT dbo.GetEmployeeFullName(EmployeeId) AS [FULL Name], Salary FROM Employees
  21. WHERE Salary <= (SELECT MIN(Salary) FROM Employees) + ((SELECT MIN(Salary) FROM Employees) * 0.10)
  22.  
  23. -- 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.
  24.  
  25. SELECT dbo.GetEmployeeFullName(EmployeeID) AS [FULL Name], Salary,
  26. (SELECT Name FROM Departments WHERE DepartmentID = emp.DepartmentID) AS [Department Name]
  27. FROM Employees emp
  28. WHERE Salary = (SELECT MIN(Salary) FROM Employees WHERE DepartmentID = emp.DepartmentID)
  29.  
  30. -- Problem 4.   Write a SQL query to find the average salary in the department #1.
  31.  
  32. SELECT AVG(Salary) AS [Average Salary IN Dep#1] FROM Employees
  33. WHERE DepartmentID = 1
  34.  
  35. -- Problem 5.   Write a SQL query to find the average salary in the "Sales" department.
  36.  
  37. SELECT AVG(emp.Salary) AS [Average Salary IN Sales Dep] FROM Employees emp
  38. INNER JOIN Departments dep ON emp.DepartmentID = dep.DepartmentID
  39. WHERE dep.Name = 'Sales'
  40.  
  41. -- Problem 6.   Write a SQL query to find the number of employees in the "Sales" department.
  42.  
  43. SELECT COUNT(emp.EmployeeID) AS [Employee COUNT IN Sales Dep] FROM Employees emp
  44. INNER JOIN Departments dep ON emp.DepartmentID = dep.DepartmentID
  45. WHERE dep.Name = 'Sales'
  46.  
  47. -- Problem 7.   Write a SQL query to find the number of all employees that have manager.
  48.  
  49. SELECT COUNT(*) AS [Employees WITH Manager COUNT] FROM Employees
  50. WHERE ManagerID IS NOT NULL
  51.  
  52. -- Problem 8.   Write a SQL query to find the number of all employees that have no manager.
  53.  
  54. SELECT COUNT(*) AS [Employees WITH No Manager COUNT] FROM Employees
  55. WHERE ManagerID IS NULL
  56.  
  57. -- Problem 9.   Write a SQL query to find all departments and the average salary for each of them.
  58.  
  59. SELECT dep.Name AS [Department], AVG(emp.Salary) AS [Average Salary] FROM Departments dep
  60. INNER JOIN Employees emp ON dep.DepartmentID = emp.DepartmentID
  61. GROUP BY dep.Name
  62.  
  63. -- Problem 10.  Write a SQL query to find the count of all employees in each department and for each town.
  64.  
  65. SELECT dep.Name AS [Department], t.Name AS [Town], COUNT(emp.EmployeeID) AS [Employee COUNT] FROM Departments dep
  66. INNER JOIN Employees emp ON dep.DepartmentID = emp.DepartmentID
  67. INNER JOIN Addresses ads ON ads.AddressID = emp.AddressID
  68. INNER JOIN Towns t ON t.TownID = ads.TownID
  69. GROUP BY dep.Name, t.Name
  70. ORDER BY dep.Name
  71.  
  72. -- Problem 11.  Write a SQL query to find all managers that have exactly 5 employees.
  73.  
  74. SELECT man.FirstName + ' ' + man.LastName AS [FULL Name] FROM Employees man
  75. WHERE (SELECT COUNT(*) FROM Employees emp WHERE emp.ManagerID = man.EmployeeID) = 5
  76.  
  77. -- Problem 12.  Write a SQL query to find all employees along with their managers.
  78.  
  79. SELECT dbo.GetEmployeeFullName(e2.EmployeeID) AS [Employee FULL Name],
  80. ISNULL(dbo.GetEmployeeFullName(e1.EmployeeID), 'no manager') AS [Manager] FROM Employees e1
  81. RIGHT OUTER JOIN Employees e2 ON e1.EmployeeID = e2.ManagerID
  82.  
  83. -- Problem 13.  Write a SQL query to find the names of all employees whose last name is exactly 5 characters long.
  84.  
  85. SELECT dbo.GetEmployeeFullName(EmployeeID) FROM Employees
  86. WHERE LEN(LastName) = 5
  87.  
  88. --Problem 14.   Write a SQL query to display the current date and time in the following format "day.month.year hour:minutes:seconds:milliseconds".
  89.  
  90. SELECT REPLACE(CONVERT(NVARCHAR, getdate(), 106), ' ', '.') + ' ' + REPLACE(CONVERT(NVARCHAR, getdate(), 14), ' ', '.')
  91.  
  92. -- Problem 15.  Write a SQL statement to create a table Users.
  93.  
  94. CREATE TABLE Users
  95. (
  96.         Id INT PRIMARY KEY IDENTITY NOT NULL,
  97.         Username VARCHAR(30) NOT NULL UNIQUE,
  98.         Password VARCHAR(20) NOT NULL,
  99.         FullName nvarchar(60) NULL,
  100.         LastLoggedIn datetime NOT NULL,
  101.         CONSTRAINT chk_Password CHECK (LEN(Password) > 5)
  102. )
  103. GO
  104. -- 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.
  105.  
  106. CREATE VIEW vUsersLoggedToday AS
  107. SELECT * FROM Users
  108. WHERE DAY(LastLoggedIn) = DAY(GETDATE())
  109. GO
  110.  
  111. -- Problem 17.  Write a SQL statement to create a table Groups.
  112.  
  113. CREATE TABLE Groups
  114. (
  115.         Id INT PRIMARY KEY IDENTITY NOT NULL,
  116.         Name VARCHAR(50) UNIQUE NULL
  117. )
  118. GO
  119.  
  120. -- Problem 18.  Write a SQL statement to add a column GroupID to the table Users.
  121.  
  122. ALTER TABLE Users
  123. ADD GroupId INT NOT NULL,
  124. FOREIGN KEY(GroupId) REFERENCES Groups(Id)
  125.  
  126. -- Problem 19.  Write SQL statements to insert several records in the Users and Groups tables.
  127.  
  128. INSERT INTO Groups VALUES ('Admin')
  129. INSERT INTO Groups VALUES ('Normal User')
  130. INSERT INTO Groups VALUES ('VIP')
  131.  
  132. INSERT INTO Users VALUES ('Pencho', '123344', 'Pencho Kenchev', GETDATE(), 1)
  133.  
  134. -- Problem 20.  Write SQL statements to update some of the records in the Users and Groups tables.
  135.  
  136. UPDATE Users SET Username = 'Pencho Dage'
  137. WHERE Id = 2
  138.  
  139. -- Problem 21.  Write SQL statements to delete some of the records from the Users and Groups tables.
  140.  
  141. DELETE FROM Users
  142. WHERE Id = 2
  143. GO
  144.  
  145. -- Problem 22.  Write SQL statements to insert in the Users table the names of all employees from the Employees table.
  146.  
  147. INSERT INTO Users
  148. SELECT LEFT(FirstName, 1) + LOWER(LastName) + ISNULL(LEFT(MiddleName, 1), ''), LEFT(FirstName, 1) + LEFT(LOWER(LastName) + 'pass', 5),
  149. FirstName + ' ' + LastName, GETDATE(), 1 FROM Employees
  150. GO
  151.  
  152. -- 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.
  153.  
  154. ALTER TABLE Users
  155. ALTER COLUMN Password VARCHAR(20) NULL
  156.  
  157. UPDATE Users SET Password = NULL
  158. WHERE LastLoggedIn <= '2010-03-10'
  159.  
  160. -- Problem 24.  Write a SQL statement that deletes all users without passwords (NULL password).
  161.  
  162. DELETE FROM Users
  163. WHERE Password IS NULL
  164.  
  165. -- Problem 25.  Write a SQL query to display the average employee salary by department and job title.
  166.  
  167. SELECT dep.Name AS [Department Name], emp.JobTitle, AVG(emp.Salary) AS [Average Salary] FROM Employees emp
  168. INNER JOIN Departments dep ON emp.DepartmentID = dep.DepartmentID
  169. GROUP BY dep.Name, emp.JobTitle
  170. ORDER BY dep.Name
  171.  
  172. -- 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.
  173.  
  174. SELECT dep.Name AS [Department Name], emp.JobTitle, MIN(emp.Salary) AS [Minmum Salary],
  175. emp.FirstName + ' ' + emp.LastName
  176. FROM Employees emp
  177. INNER JOIN Departments dep ON emp.DepartmentID = dep.DepartmentID
  178. GROUP BY dep.Name, emp.JobTitle, emp.FirstName, emp.LastName
  179. ORDER BY dep.Name
  180.  
  181. -- Problem 27.  Write a SQL query to display the town where maximal number of employees work.
  182.  
  183. SELECT TOP 1 * FROM (SELECT t.Name, COUNT(*) AS [EmployeeCount] FROM Employees emp
  184. INNER JOIN Addresses ads ON ads.AddressID = emp.AddressID
  185. INNER JOIN Towns t ON t.TownID = ads.TownID
  186. GROUP BY t.Name) ec
  187. ORDER BY ec.EmployeeCount DESC
  188.  
  189. -- Problem 28.  Write a SQL query to display the number of managers from each town.
  190.  
  191. SELECT t.Name, COUNT(*) FROM Employees emp
  192. INNER JOIN Addresses ads ON ads.AddressID = emp.AddressID
  193. INNER JOIN Towns t ON t.TownID = ads.TownID
  194. WHERE emp.ManagerID IS NULL
  195. GROUP BY t.Name
  196.  
  197. -- Problem 29.  Write a SQL to create table WorkHours to store work reports for each employee.
  198.  
  199. CREATE TABLE WorkHours
  200. (
  201.         Id INT PRIMARY KEY IDENTITY NOT NULL,
  202.         TaskDate datetime NULL,
  203.         Task nvarchar(125) NOT NULL,
  204.         Hours SMALLINT NOT NULL,
  205.         Comments ntext NULL
  206. )
  207. GO
  208.  
  209. ALTER TABLE WorkHours
  210. ALTER COLUMN Comments VARCHAR(MAX) NULL
  211.  
  212. -- Problem 30.  Issue few SQL statements to insert, update and delete of some data in the table.
  213.  
  214. INSERT INTO WorkHours VALUES (GETDATE(), 'Get all things done before midnight', 4, 'Don`t forget to wash the dishes too. Бързо Бързо')
  215. INSERT INTO WorkHours VALUES (GETDATE(), 'Don`t do anything today', 4, 'Знам че ти се прави нещо но не го прави, не слагай и запетаи даже')
  216. UPDATE WorkHours SET TaskDate = DATEADD(MONTH, 2, GETDATE()) WHERE Id = 2
  217. -- DELETE FROM WorkHours WHERE Id = 1
  218.  
  219. -- Problem 31.  Define a table WorkHoursLogs to track all changes in the WorkHours table with triggers.
  220.  
  221. CREATE TABLE WorkHoursLogs
  222. (
  223.         Id INT PRIMARY KEY IDENTITY NOT NULL,
  224.         ChangeDate datetime NOT NULL,
  225.         OldTaskDate datetime NULL,
  226.         OldTask nvarchar(125) NULL,
  227.         OldHours SMALLINT NULL,
  228.         OldComments VARCHAR(MAX) NULL,
  229.         NewTaskDate datetime NULL,
  230.         NewTask nvarchar(125) NULL,
  231.         NewHours SMALLINT NULL,
  232.         NewComments VARCHAR(MAX) NULL,
  233.         Command CHAR(6) NOT NULL
  234. )
  235. GO
  236.  
  237. CREATE TRIGGER workhours_change
  238. ON WorkHours
  239. AFTER INSERT, UPDATE, DELETE
  240. AS
  241. BEGIN
  242.         DECLARE @operation CHAR(6)
  243.                 SET @operation = CASE
  244.                         WHEN EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
  245.                                 THEN 'Update'
  246.                         WHEN EXISTS(SELECT * FROM inserted)
  247.                                 THEN 'Insert'
  248.                         WHEN EXISTS(SELECT * FROM deleted)
  249.                                 THEN 'Delete'
  250.                         ELSE NULL
  251.                 END
  252.         IF @operation = 'Delete'
  253.                 INSERT INTO WorkHoursLogs (ChangeDate, OldTaskDate, OldTask, OldHours, OldComments, Command)
  254.                 SELECT GETDATE(), d.TaskDate, d.Task, d.Hours, d.Comments, @operation
  255.                 FROM deleted d
  256.         IF @operation = 'Insert'
  257.                 INSERT INTO WorkHoursLogs (ChangeDate, NewTaskDate, NewTask, NewHours, NewComments, Command)
  258.                 SELECT GETDATE(), i.TaskDate, i.Task, i.Hours, i.Comments, @operation
  259.                 FROM inserted i
  260.         IF @operation = 'Update'
  261.                 INSERT INTO WorkHoursLogs (ChangeDate, OldTaskDate, OldTask, OldHours, OldComments,
  262.                         NewTaskDate, NewTask, NewHours, NewComments, Command)
  263.                                 SELECT GETDATE(), d.TaskDate, d.Task, d.Hours, d.Comments, i.TaskDate, i.Task,
  264.                                         i.Hours, i.Comments, @operation
  265.                                 FROM deleted d, inserted i
  266. END
  267. GO
  268.                
  269.  
  270. UPDATE WorkHours SET TaskDate = DATEADD(MONTH, 3, GETDATE()) WHERE Id = 2
  271. UPDATE WorkHours SET Comments = 'I`ve changed that thing and a log is in the WorkHOursLog' WHERE Id = 1
  272. INSERT INTO WorkHours VALUES (GETDATE() + 1, 'Reporting For Duties', 8, 'You wanna piece of me boy?!')
  273. -- select * from WorkHoursLogs
  274.  
  275. -- 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.
  276.  
  277. ALTER TABLE Employees
  278. DROP CONSTRAINT FK_Employees_Employees
  279. GO
  280. ALTER TABLE Employees
  281. ADD CONSTRAINT FK_Employees_Employees FOREIGN KEY (ManagerID)
  282. REFERENCES Employees(EmployeeID)
  283. ON DELETE CASCADE
  284. ON UPDATE NO ACTION
  285. GO
  286.  
  287. BEGIN TRY
  288.     BEGIN TRANSACTION
  289.        
  290.     COMMIT
  291. END TRY
  292. BEGIN CATCH
  293.  
  294.     IF @@TRANCOUNT > 0
  295.         ROLLBACK
  296. END CATCH
  297.  
  298. -- Problem 33.  Start a database transaction and drop the table EmployeesProjects.
  299.  
  300. BEGIN TRAN
  301. --DROP TABLE EmployeesProjects
  302. ROLLBACK
  303. SELECT * FROM EmployeesProjects
  304.  
  305. -- Problem 34.  Find how to use temporary tables in SQL Server.
  306.  
  307. DECLARE @tempEmplProjTable TABLE
  308. (
  309.         EmployeeID INT NOT NULL,
  310.         ProjectID INT NOT NULL
  311. )
  312. INSERT INTO @tempEmplProjTable
  313.         SELECT EmployeeID, ProjectID FROM EmployeesProjects
  314. DROP TABLE EmployeesProjects
  315. CREATE TABLE EmployeesProjects
  316. (
  317.         EmployeeID INT NOT NULL,
  318.         ProjectID INT NOT NULL
  319. )
  320. INSERT INTO EmployeesProjects
  321.         SELECT * FROM @tempEmplProjTable
  322. GO
  323.  
  324. -- Should have it`s values restored
  325. SELECT * FROM EmployeesProjects
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top