Advertisement
reathh

Advanced-SQL

Feb 15th, 2015
397
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. IF EXISTS (SELECT
  2.     1
  3. FROM Information_schema.Routines
  4. WHERE Specific_schema = 'dbo'
  5. AND specific_name = 'getFullNameOfEmployee'
  6. AND Routine_Type = 'FUNCTION') DROP FUNCTION getFullNameOfEmployee
  7. GO
  8. CREATE FUNCTION dbo.getFullNameOfEmployee(@FirstName nvarchar(MAX), @MiddleName nvarchar(MAX), @LastName nvarchar(MAX))
  9. RETURNS nvarchar(MAX)
  10. AS
  11. BEGIN
  12.     DECLARE @FullName nvarchar(MAX) = @FirstName + ISNULL(' ' + @MiddleName, '') + ' ' + @LastName;
  13.     RETURN @FullName
  14. END;
  15. GO
  16.  
  17. --1. Write a SQL query to find the names and salaries of the employees that take the minimal salary in the company.
  18. SELECT
  19.     FirstName,
  20.     LastName,
  21.     Salary
  22. FROM Employees
  23. WHERE Salary = (SELECT
  24.     MIN(Salary)
  25. FROM Employees)
  26.  
  27. --2.Write a SQL query to find the names and salaries of the employees
  28. --that have a salary that is up to 10% higher than the minimal salary for the company.
  29. SELECT
  30.     FirstName,
  31.     LastName,
  32.     Salary
  33. FROM Employees
  34. WHERE Salary <= (SELECT
  35.     MIN(Salary) * 1.10
  36. FROM Employees)
  37.  
  38. --3. Write a SQL query to find the full name, salary and department
  39. -- of the employees that take the minimal salary in their department.
  40. SELECT
  41.     dbo.getFullNameOfEmployee(e.FirstName, e.MiddleName, e.LastName) AS [FULL Name],
  42.     Salary,
  43.     d.Name AS Department
  44. FROM Employees e
  45. JOIN Departments d
  46.     ON e.DepartmentID = d.DepartmentID
  47. WHERE Salary = (SELECT
  48.     MIN(Salary)
  49. FROM Employees)
  50.  
  51. --4. Write a SQL query to find the average salary in the department #1.
  52. SELECT
  53.     AVG(Salary) AS [Average Salary],
  54.     MIN(DepartmentID) AS [Department]
  55. FROM Employees
  56. WHERE DepartmentID = 1
  57.  
  58. --5. Write a SQL query to find the average salary in the "Sales" department.
  59. SELECT
  60.     AVG(e.Salary) AS [Average Salary],
  61.     MIN(d.Name) AS [Department]
  62. FROM Employees e
  63. JOIN Departments d
  64.     ON e.DepartmentID = d.DepartmentID
  65. WHERE d.Name = 'Sales'
  66.  
  67. --6. Write a SQL query to find the number of employees in the "Sales" department.
  68. SELECT
  69.     COUNT(*) AS [NUMBER OF Employees],
  70.     MIN(d.Name) AS [Department]
  71. FROM Employees e
  72. JOIN Departments d
  73.     ON e.DepartmentID = d.DepartmentID
  74. WHERE d.Name = 'Sales'
  75.  
  76. --7. Write a SQL query to find the number of all employees that have manager.
  77. SELECT
  78.     COUNT(ManagerID)
  79. FROM Employees
  80.  
  81. --8. Write a SQL query to find the number of all employees that have no manager.
  82. SELECT
  83.     COUNT(*)
  84. FROM Employees
  85. WHERE ManagerID IS NULL
  86.  
  87. --9. Write a SQL query to find all departments and the average salary for each of them.
  88. SELECT
  89.     d.Name,
  90.     AVG(e.Salary)
  91. FROM Employees e
  92. JOIN Departments d
  93.     ON e.DepartmentID = d.DepartmentID
  94. GROUP BY    d.DepartmentID,
  95.             d.Name
  96. ORDER BY d.Name
  97.  
  98. --10. Write a SQL query to find the count of all employees in each department and for each town.
  99. SELECT
  100.     COUNT(*) AS [COUNT OF Employees],
  101.     d.Name AS [Department Name],
  102.     t.Name AS [Town Name]
  103. FROM Employees e
  104. JOIN Departments d
  105.     ON e.DepartmentID = d.DepartmentID
  106. JOIN Addresses a
  107.     ON e.AddressID = a.AddressID
  108. JOIN Towns t
  109.     ON a.TownID = t.TownID
  110. GROUP BY    d.DepartmentID,
  111.             d.Name,
  112.             t.TownID,
  113.             t.Name
  114. ORDER BY d.Name
  115.  
  116. --11. Write a SQL query to find all managers that have exactly 5 employees.
  117. SELECT
  118.     m.FirstName,
  119.     m.LastName,
  120.     COUNT(e.ManagerID) AS [Employees COUNT]
  121. FROM Employees e
  122. JOIN Employees m
  123.     ON e.ManagerID = m.EmployeeID
  124. GROUP BY    m.ManagerID,
  125.             m.FirstName,
  126.             m.LastName
  127. HAVING COUNT(e.EmployeeID) = 5
  128. ORDER BY m.FirstName
  129.  
  130. --12. Write a SQL query to find all employees along with their managers.
  131. SELECT
  132.     e.FirstName,
  133.     ISNULL(m.FirstName + ' ' + m.LastName, '(no manager)') AS Manager
  134. FROM Employees e
  135. LEFT OUTER JOIN Employees m
  136.     ON e.ManagerID = m.EmployeeID
  137.  
  138. --13. Write a SQL query to find the names of all employees whose last name is exactly 5 characters long.
  139. SELECT
  140.     dbo.getFullNameOfEmployee(FirstName, MiddleName, LastName) AS [FULL Name]
  141. FROM Employees
  142. WHERE LEN(LastName) = 5
  143.  
  144. --14. Write a SQL query to display the current date and time in the following format
  145. --"day.month.year hour:minutes:seconds:milliseconds".
  146. SELECT
  147.     CONVERT(NVARCHAR, GETDATE(), 4) + ' '
  148.     + CONVERT(NVARCHAR, GETDATE(), 114) AS DateTime
  149.  
  150. --15. Write a SQL statement to create a table Users.
  151. CREATE TABLE Users
  152. (
  153.     Id INT PRIMARY KEY IDENTITY NOT NULL,
  154.     Username VARCHAR(100) NOT NULL UNIQUE,
  155.     Password VARCHAR(100),
  156.     FullName nvarchar(100) NULL,
  157.     LastLoggedIn datetime,
  158.     CONSTRAINT check_Password CHECK (LEN(Password) > 5)
  159. )
  160. GO
  161.  
  162. --16. Write a SQL statement to create a view that displays the users from the Users table
  163. --that have been in the system today.
  164. CREATE VIEW vUsersLoggedToday AS
  165. SELECT
  166.     *
  167. FROM Users
  168. WHERE DAY(LastLoggedIn) = DAY(GETDATE())
  169. GO
  170.  
  171. --17. Write a SQL statement to create a table Groups.
  172. CREATE TABLE Groups
  173. (
  174.     Id INT PRIMARY KEY IDENTITY NOT NULL,
  175.     Name VARCHAR(50) UNIQUE NULL
  176. )
  177. GO
  178.  
  179. --18. Write a SQL statement to add a column GroupID to the table Users.
  180. ALTER TABLE Users
  181. ADD GroupId INT NOT NULL,
  182. FOREIGN KEY(GroupId) REFERENCES Groups(Id)
  183. GO
  184.  
  185. --19. Write SQL statements to insert several records in the Users and Groups tables.
  186. INSERT INTO Groups
  187.     VALUES ('Admin')
  188. INSERT INTO Groups
  189.     VALUES ('Normal User')
  190. INSERT INTO Groups
  191.     VALUES ('Super User')
  192.  
  193. INSERT INTO Users
  194.     VALUES ('Goshko', '123456', 'Gosho Goshov', GETDATE(), 1)
  195.  
  196. --20. Write SQL statements to update some of the records in the Users and Groups tables.
  197. UPDATE Users
  198. SET Username = 'Goshko1'
  199. WHERE Id = 3
  200.  
  201. --21. Write SQL statements to delete some of the records from the Users and Groups tables.
  202. DELETE FROM Users
  203. WHERE Id = 2
  204. GO
  205.  
  206. --22. Write SQL statements to insert in the Users table the names of all employees from the Employees table.
  207. INSERT INTO Users (UserName, Password, FullName, LastLoggedIn, GroupId)
  208.     SELECT
  209.         LOWER(LEFT(FirstName, 1) + LastName) + CAST(EmployeeID AS NVARCHAR(10)),
  210.         LOWER(LEFT(FirstName, 1) + LastName) + '123',
  211.         FirstName + ' ' + LastName,
  212.         NULL,
  213.         2
  214.     FROM Employees
  215. GO
  216.  
  217. --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.
  218. UPDATE Users
  219. SET Password = NULL
  220. WHERE LastLoggedIn <= '2010-03-10'
  221.  
  222. --24. Write a SQL statement that deletes all users without passwords (NULL password).
  223. DELETE FROM Users
  224. WHERE Password IS NULL
  225.  
  226. --25. Write a SQL query to display the average employee salary by department and job title.
  227. SELECT
  228.     d.Name AS [Department Name],
  229.     e.JobTitle AS [Job Title],
  230.     AVG(Salary) AS [Average Salary]
  231. FROM Employees e
  232. JOIN Departments d
  233.     ON e.DepartmentID = d.DepartmentID
  234. GROUP BY    d.Name,
  235.             e.JobTitle
  236.  
  237. --26. Write a SQL query to display the minimal employee salary by department
  238. --and job title along with the name of some of the employees that take it.
  239. SELECT
  240.     d.Name AS [Department Name],
  241.     e.JobTitle AS [Job Title],
  242.     MIN(e.FirstName) AS [FIRST Name],
  243.     MIN(Salary) AS [Minimum Salary]
  244. FROM Employees e
  245. JOIN Departments d
  246.     ON e.DepartmentID = d.DepartmentID
  247. GROUP BY    d.Name,
  248.             e.JobTitle
  249. ORDER BY d.Name
  250.  
  251. --27. Write a SQL query to display the town where maximal number of employees work.
  252. SELECT TOP 1
  253.     *
  254. FROM (SELECT
  255.     t.Name,
  256.     COUNT(*) AS [NUMBER OF Employees]
  257. FROM Employees e
  258. JOIN Addresses a
  259.     ON e.AddressID = a.AddressID
  260. JOIN Towns t
  261.     ON a.TownID = t.TownID
  262. GROUP BY t.Name) TownsAndNumberOfEmployees
  263. ORDER BY TownsAndNumberOfEmployees.[NUMBER OF Employees] DESC
  264.  
  265. --28. Write a SQL query to display the number of managers from each town.
  266. SELECT
  267.     em.Town,
  268.     COUNT(*) AS [Employees COUNT]
  269. FROM (SELECT DISTINCT
  270.     e.EmployeeID,
  271.     t.Name AS Town
  272. FROM Employees e
  273. JOIN Employees m
  274.     ON m.ManagerID = e.EmployeeID
  275. JOIN Addresses a
  276.     ON e.AddressID = a.AddressID
  277. JOIN Towns t
  278.     ON a.TownID = t.TownID) em
  279. GROUP BY em.Town
  280.  
  281. --29. Write a SQL to create table WorkHours to store work reports for each employee.
  282. CREATE TABLE WorkHours
  283. (
  284.         Id INT PRIMARY KEY IDENTITY NOT NULL,
  285.         EmployeeId INT FOREIGN KEY REFERENCES Employees(EmployeeId)  NOT NULL,
  286.         DATE datetime NULL,
  287.         Task nvarchar(150) NOT NULL,
  288.         Hours INT NOT NULL,
  289.         Comments ntext NULL
  290.  
  291. )
  292. GO
  293.  
  294. --30. Issue few SQL statements to insert, update and delete of some data in the table.
  295. --,31. Define a table WorkHoursLogs to track all changes in the WorkHours table with triggers.
  296.  
  297. --31
  298. CREATE TABLE WorkHoursLogs
  299. (
  300.         Id INT PRIMARY KEY IDENTITY NOT NULL,
  301.         Message nvarchar(150) NOT NULL,
  302.         DateOfChange datetime NOT NULL
  303. )
  304.  
  305. GO
  306.  
  307. CREATE TRIGGER  tr_WorkHoursInsert
  308. ON WorkHours
  309.  FOR INSERT
  310. AS
  311. INSERT INTO WorkHoursLogs (Message, DateOfChange)
  312.     VALUES ('row added', GETDATE())
  313. GO
  314.  
  315. CREATE TRIGGER  tr_WorkHoursDelete
  316. ON WorkHours
  317.  FOR DELETE
  318. AS
  319. INSERT INTO WorkHoursLogs (Message, DateOfChange)
  320.     VALUES ('row deleted', GETDATE())
  321. GO
  322.  
  323. CREATE TRIGGER  tr_WorkHoursUpdate
  324. ON WorkHours
  325.  FOR UPDATE
  326. AS
  327. INSERT INTO WorkHoursLogs (Message, DateOfChange)
  328.     VALUES ('row updated', GETDATE())
  329. GO
  330.  
  331. --30
  332. INSERT INTO WorkHours (EmployeeId, DATE, Task, Hours)
  333.     VALUES (10, GETDATE(), 'Random task', 11)
  334.  
  335. INSERT INTO WorkHours (EmployeeId, DATE, Task, Hours)
  336.     VALUES (11, GETDATE(), 'yeah, yeah, blabla', 34)
  337.  
  338. DELETE WorkHours
  339. WHERE EmployeeId = 11
  340.  
  341. UPDATE WorkHours
  342. SET Task = 'updated task'
  343. WHERE EmployeeId = 11
  344.  
  345. SELECT
  346.     *
  347. FROM WorkHoursLogs
  348.  
  349. --32. Start a database transaction, delete all employees from the 'Sales' department along
  350. --with all dependent records from the pother tables. At the end rollback the transaction.
  351. BEGIN TRAN
  352. DELETE Employees
  353. WHERE DepartmentID = (SELECT
  354.         DepartmentID
  355.     FROM Departments
  356.     WHERE Name = 'Sales')
  357. ROLLBACK TRAN
  358.  
  359. --33. Start a database transaction and drop the table EmployeesProjects.
  360. BEGIN TRAN
  361. DROP TABLE EmployeesProjects
  362. ROLLBACK TRAN
  363.  
  364. --34. Find how to use temporary tables in SQL Server.
  365. DECLARE @temporaryEmployeesProjTable TABLE
  366. (
  367.         EmployeeID INT NOT NULL,
  368.         ProjectID INT NOT NULL
  369. )
  370.  
  371. INSERT INTO @temporaryEmployeesProjTable
  372.     SELECT
  373.         EmployeeID,
  374.         ProjectID
  375.     FROM EmployeesProjects
  376.  
  377. DROP TABLE EmployeesProjects
  378.  
  379. CREATE TABLE EmployeesProjects
  380. (
  381.         EmployeeID INT NOT NULL,
  382.         ProjectID INT NOT NULL
  383. )
  384. INSERT INTO EmployeesProjects
  385.     SELECT
  386.         *
  387.     FROM @temporaryEmployeesProjTable
  388. GO
Advertisement
Advertisement
Advertisement
RAW Paste Data Copied
Advertisement