Feb 12th, 2015
313
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
1. -- Problem 1.   Write a SQL query to find the names and salaries of the employees
2. -- that take the minimal salary in the company.
3. -- Use a nested SELECT statement.
4. --
5. -- FirstName    LastName    Salary
6. -- Susan        Eaton       9000.00
7. -- Kim          Ralls       9000.00
8. -- Jimmy        Bischoff    9000.00
9.
10. SELECT FirstName, LastName, Salary
11. FROM Employees
12. WHERE Salary = (SELECT MIN(Salary) FROM Employees);
13.
14. -----------------------------------------------------------------------------------------------------------
15. -- Problem 2.   Write a SQL query to find the names and salaries of the employees that have a salary
16. -- that is up to 10% higher than the minimal salary for the company.
17. --
18. -- FirstName    LastName    Salary
19. -- Rostislav    Shabalin    9500.00
20. -- Russell      King        9500.00
21. -- Jimmy        Bischoff    9000.00
22. -- Michael      Vanderhyde  9300.00
23. -- John         Frum        9300.00
24. -- Merav        Netz        9100.00
25. -- Jan          Miksovsky   9100.00
26. -- …          …         …
27.
28. SELECT FirstName, LastName, Salary
29. FROM Employees
30. WHERE Salary < (SELECT MIN(Salary) * 1.1 FROM Employees);
31.
32. -----------------------------------------------------------------------------------------------------------
33. -- Problem 3.   Write a SQL query to find the full name, salary and department of the employees that
34. -- take the minimal salary in their department.
35. -- Use a nested SELECT statement.
36. --
37. -- FirstName        LastName    Salary
38. -- Gail Erickson    32700.00    Engineering
39. -- Jossef Goldberg  32700.00    Engineering
40. -- Sharon Salavaria 32700.00    Engineering
41. -- Gancho D'Hers    25000.00    Tool Design
42. -- …              …         …
43.
44. SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [Employee FULL Name],
45.        Salary,
46.        d.Name AS [Department Name]
47. FROM Employees e
48.   JOIN Departments d
49.     ON e.DepartmentID = d.DepartmentID
50. WHERE Salary =
51.   (SELECT MIN(Salary) FROM Employees
52.    WHERE DepartmentID = e.DepartmentID)
53. ORDER BY e.DepartmentID;
54.
55. -----------------------------------------------------------------------------------------------------------
56. -- Problem 4.   Write a SQL query to find the average salary in the department #1.
57. --
58. -- FirstName        Average Salary
59. -- Gail Erickson    32700.00
60.
61. SELECT AVG(Salary) AS [Average Salary FOR Department #1]
62. FROM Employees e
63. WHERE e.DepartmentID = 1;
64.
65. -----------------------------------------------------------------------------------------------------------
66. -- Problem 5.   Write a SQL query to find the average salary in the "Sales" department.
67. --
68. -- Average Salary for Sales Department
69. -- 18403.7671
70.
71. SELECT AVG(Salary) AS [Average Salary FOR Sales Department]
72. FROM Employees e
73.   JOIN Departments d
74.     ON e.DepartmentID = d.DepartmentID
75. WHERE d.Name = 'Sales';
76.
77. -----------------------------------------------------------------------------------------------------------
78. -- Problem 6.   Write a SQL query to find the number of employees in the "Sales" department.
79. --
80. -- Sales Employees Count
81. -- 292
82.
83. SELECT COUNT(*) AS [Sales Employees COUNT]
84. FROM Employees e
85.   JOIN Departments d
86.     ON e.DepartmentID = d.DepartmentID
87. WHERE d.Name = 'Sales';
88.
89. -----------------------------------------------------------------------------------------------------------
90. -- Problem 7.   Write a SQL query to find the number of all employees that have manager.
91. --
92. -- Employees with manager
93. -- 289
94.
95. SELECT COUNT(ManagerID) AS [Employees WITH manager]
96. FROM Employees;
97.
98. -----------------------------------------------------------------------------------------------------------
99. -- Problem 8.   Write a SQL query to find the number of all employees that have no manager.
100. --
101. -- Employees without manager
102. -- 3
103.
104. SELECT COUNT(*) AS [Employees WITHOUT manager]
105. FROM Employees
106. WHERE ManagerID IS NULL;
107.
108. -----------------------------------------------------------------------------------------------------------
109. -- Problem 9.   Write a SQL query to find all departments and the average salary for each of them.
110. --
111. -- Department                   Average Salary
112. -- Document Control             14400.00
113. -- Engineering                  40166.6666
114. -- Executive                    92800.00
115. -- Facilities and Maintenance   13057.1428
116. -- …                          …
117.
118. SELECT d.Name AS [Department], AVG(Salary) AS [Average Salary]
119. FROM Employees e
120.   JOIN Departments d
121.     ON e.DepartmentID = d.DepartmentID
122. GROUP BY d.Name
123. ORDER BY [Department] ASC;
124.
125. -----------------------------------------------------------------------------------------------------------
126. -- Problem 10.  Write a SQL query to find the count of all employees in each department and for each town.
127. --
128. -- Town     Department          Employees count
129. -- Index    Document Control    1
130. -- Issaquah Document Control    4
131. -- Redmond  Engineering         1
132. -- Renton   Engineering         4
133. -- …      …                 …
134.
135. SELECT t.Name AS [Town], d.Name AS [Department], COUNT(d.Name) AS [Employees COUNT]
136. FROM Employees e
137.   JOIN Departments d
138.     ON e.DepartmentID = d.DepartmentID
141.   JOIN Towns t
142.     ON a.TownID = t.TownID
143. GROUP BY t.Name, d.Name
144. ORDER BY [Department] ASC;
145.
146. -----------------------------------------------------------------------------------------------------------
147. -- Problem 11.  Write a SQL query to find all managers that have exactly 5 employees.
148. -- Display their first name and last name.
149. --
150. -- FirstName    LastName            Employees count
151. -- Pilar        Ackerman            5
152. -- Paula        Barreto de Mattos   5
153. -- Jeff         Hay                 5
154. -- Lori         Kane                5
155. -- …          …                 …
156.
157. SELECT m.FirstName, m.LastName, COUNT(e.EmployeeID) AS [Employees COUNT]
158. FROM Employees e
159.   JOIN Employees m
160.     ON e.ManagerID = m.EmployeeID
161. GROUP BY m.FirstName, m.LastName
162. HAVING COUNT(e.EmployeeID) = 5;
163.
164. -----------------------------------------------------------------------------------------------------------
165. -- Problem 12.  Write a SQL query to find all employees along with their managers.
166. -- For employees that do not have manager display the value "(no manager)".
167. --
168. -- FirstName         Manager
169. -- Martin Kulov      No manager
170. -- George Denchev    No manager
171. -- Ovidiu Cracium    Roberto Tamburello
172. -- Michael Sullivan  Roberto Tamburello
173. -- …               …
174.
175. SELECT e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName AS [Employee FULL Name],
176.        ISNULL(m.FirstName + ' ' + ISNULL(m.MiddleName + ' ', '') + m.LastName, 'No manager') AS [Manager FULL Name]
177. FROM Employees e
178.   LEFT JOIN Employees m
179.     ON e.ManagerID = m.EmployeeID;
180.
181. -----------------------------------------------------------------------------------------------------------
182. -- Problem 13.  Write a SQL query to find the names of all employees whose last name
183. -- is exactly 5 characters long. Use the built-in LEN(str) function.
184. --
185. -- FirstName    Manager
186. -- Kevin        Brown
187. -- Terri        Duffy
188. -- Jo           Brown
189. -- Diane        Glimp
190. -- …          …
191.
192. SELECT FirstName, LastName
193. FROM Employees
194. WHERE LEN(LastName) = 5;
195.
196. -----------------------------------------------------------------------------------------------------------
197. -- Problem 14.  Write a SQL query to display the current date and time in the following format
198. -- "day.month.year hour:minutes:seconds:milliseconds".
199. -- Search in Google to find how to format dates in SQL Server.
200. --
201. -- DateTIme
202. -- 11.02.2015 18:50:02:960
203.
204. SELECT CONVERT(VARCHAR(10), GETDATE(), 104) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114) AS [DATE AND TIME];
205.
206. -----------------------------------------------------------------------------------------------------------
207. -- Problem 15.  Write a SQL statement to create a table Users.
209. -- Choose appropriate data types for the table fields. Define a primary key column with
210. -- a primary key constraint. Define the primary key column as identity to facilitate inserting records.
211. -- Define unique constraint to avoid repeating usernames. Define a check constraint to ensure the password
212. -- is at least 5 characters long.
213.
214. CREATE TABLE Users (
215.   UserID INT IDENTITY,
218.   FullName NVARCHAR(50) NOT NULL,
220.   CONSTRAINT PK_Users PRIMARY KEY(UserID),
223. );
224.
225. INSERT INTO [dbo].[Users]
228.            ,[FullName]
230.      VALUES
231.            ('Nakata',
232.             'passwd',
233.             'Svetlin Nakov',
234.             GETDATE());
235.
236. INSERT INTO [dbo].[Users]
239.            ,[FullName]
241.      VALUES
242.            ('Nakata2',
243.             'passwd',
244.             'Svetlin Nakov 2',
245.             '2015-02-11');
246.
247.
248. -----------------------------------------------------------------------------------------------------------
249. -- Problem 16.  Write a SQL statement to create a view that displays the users from the Users table
250. -- that have been in the system today.
251. -- Test if the view works correctly.
252.
253. CREATE VIEW TodayVisitors AS
254. SELECT *
255. FROM Users
257.
258. SELECT * FROM TodayVisitors;
259.
260. -----------------------------------------------------------------------------------------------------------
261. -- Problem 17.  Write a SQL statement to create a table Groups.
262. -- Groups should have unique name (use unique constraint). Define primary key and identity column.
263.
264. CREATE TABLE Groups (
265.   GroupID INT IDENTITY,
266.   GroupName NVARCHAR(10) NOT NULL,
267.   CONSTRAINT PK_Groups PRIMARY KEY(GroupID),
268.   CONSTRAINT UNQ_Groups UNIQUE(GroupName),
269. );
270.
271. -----------------------------------------------------------------------------------------------------------
272. -- Problem 18.  Write a SQL statement to add a column GroupID to the table Users.
273. -- Fill some data in this new column and as well in the Groups table. Write a SQL statement to add a
274. -- foreign key constraint between tables Users and Groups tables.
275.
276. ALTER TABLE Users
277. ADD GroupID INT FOREIGN KEY REFERENCES Groups(GroupID);
278. INSERT Groups VALUES ('Green');
279. INSERT Groups VALUES ('Blue');
280. INSERT Groups VALUES ('Red');
281. UPDATE Users SET GroupID = 1;
282. INSERT Users VALUES ('usr1', '12345', 'Alf',  GETDATE(), 2);
283. INSERT Users VALUES ('usr2', '123456', 'Jerry', GETDATE(), 2);
284. INSERT Users VALUES ('usr3', '1234567', 'Tom', GETDATE(), 3);
285. INSERT Users VALUES ('usr4', '12345678', 'Baba Qga', GETDATE(), 3);
286.
287. -----------------------------------------------------------------------------------------------------------
288. -- Problem 19.  Write SQL statements to insert several records in the Users and Groups tables.
289.
290. INSERT Groups VALUES ('Black');
291. INSERT Groups VALUES ('Brown');
292. INSERT Groups VALUES ('White');
293. INSERT Users VALUES ('usr5', '12345', 'Bendji',  GETDATE(), 4);
294. INSERT Users VALUES ('usr6', '123456', 'Zaxx', GETDATE(), 1);
295. INSERT Users VALUES ('usr7', '1234567', 'Star Prince', GETDATE(),5);
296. INSERT Users VALUES ('usr8', '12345678', 'Ohliu', GETDATE(), 6);
297.
298. -----------------------------------------------------------------------------------------------------------
299. -- Problem 20.  Write SQL statements to update some of the records in the Users and Groups tables.
300.
301. UPDATE Users SET GroupID = 1
302. WHERE GroupID = 2;
303.
304. UPDATE Groups SET GroupName = 'Pink'
305. WHERE GroupName = 'Black';
306.
307. -----------------------------------------------------------------------------------------------------------
308. -- Problem 21.  Write SQL statements to delete some of the records from the Users and Groups tables.
309.
310. DELETE FROM Users WHERE FullName LIKE 'Ohliu';
311. DELETE FROM Groups WHERE GroupID = 6;
312.
313. -----------------------------------------------------------------------------------------------------------
314. -- Problem 22.  Write SQL statements to insert in the Users table the names of all employees
315. -- from the Employees table.Combine the first and last names as a full name. For username use the first
316. -- letter of the first name + the last name (in lowercase). Use the same for the password,
317. -- and NULL for last login time.
318.
319. INSERT INTO Users
320. SELECT LEFT(LOWER(LEFT(FirstName, 1) + LEFT(ISNULL(MiddleName, '_'), 1) + LastName), 10) AS [UserName],
321.        LOWER(LEFT(FirstName, 1) + LastName + 'pwd') AS [UserPassword],
322.        FirstName + ' ' + LastName AS [FullName],
324.        2 AS [GroupID]
325. FROM Employees
326. ORDER BY LOWER(LEFT(FirstName, 1) + LastName);
327.
328. -----------------------------------------------------------------------------------------------------------
329. -- Problem 23.  Write a SQL statement that changes the password to NULL for all users that have not been
330. -- in the system since 10.03.2010.
331.
332. UPDATE Users SET UserPassword = NULL
333. WHERE LastLogin <= CAST('2013-10-03' AS DATETIME);
334.
335. -----------------------------------------------------------------------------------------------------------
336. -- Problem 24.  Write a SQL statement that deletes all users without passwords (NULL password).
337.
338. DELETE FROM Users WHERE UserPassword IS NULL;
339.
340. -----------------------------------------------------------------------------------------------------------
341. -- Problem 25.  Write a SQL query to display the average employee salary by department and job title.
342. --
343. -- Department   Job Title                       Average Salary
344. -- Finance      Accountant                      26400.00
345. -- Finance      Accounts Manager                34700.00
346. -- Finance      Accounts Payable Specialist     19000.00
347. -- Finance      Accounts Receivable Specialist  19000.00
348. -- …          …                             …
349.
350. SELECT d.Name AS [Department], e.JobTitle, AVG(Salary) AS [Average Salary]
351. FROM Employees e
352.   JOIN Departments d
353.   ON e.DepartmentID = d.DepartmentID
354. GROUP BY d.Name, e.JobTitle
355. ORDER BY [JobTitle];
356.
357. -----------------------------------------------------------------------------------------------------------
358. -- Problem 26.  Write a SQL query to display the minimal employee salary by department and job title along
359. -- with the name of some of the employees that take it.
360. --
361. -- Department   Job Title                       First Name  Min Salary
362. -- Engineering  Engineering Manager             Roberto     43300.00
363. -- Engineering  Senior Design Engineer          Michael     36100.00
364. -- Engineering  Vice President of Engineering   Terri       63500.00
365. -- Executive    Chief Executive Officer         Ken         125500.00
366. -- …          …                             …           …
367.
368. SELECT d.Name AS [Department],
369.        e.JobTitle,
370.        e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName AS [Employee FULL Name],
371.        e.Salary AS [Minimal Salary]
372. FROM Employees e
373.   JOIN Departments d
374.   ON e.DepartmentID = d.DepartmentID
375. GROUP BY d.Name,
376.          e.JobTitle,
377.          e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName,
378.          e.Salary,
379.          e.DepartmentID
380. HAVING e.Salary = (SELECT MIN(Salary) FROM Employees
381.                    WHERE JobTitle = e.JobTitle AND DepartmentID = e.DepartmentID)
382. ORDER BY [Department];
383.
384. -----------------------------------------------------------------------------------------------------------
385. -- Problem 27.  Write a SQL query to display the town where maximal number of employees work.
386. --
387. -- Name      Number of employees
388. -- Seattle  44
389.
390. SELECT TOP(1) t.Name AS [Name], COUNT(e.EmployeeID) AS [NUMBER OF Employees]
391. FROM Employees e
394.   JOIN Towns t
395.     ON a.TownID = t.TownID
396. GROUP BY t.Name
397. ORDER BY [NUMBER OF Employees] DESC;
398.
399. -----------------------------------------------------------------------------------------------------------
400. -- Problem 28.  Write a SQL query to display the number of managers from each town.
401. --
402. -- Town             Number of managers
403. -- Issaquah         3
404. -- Kenmore          5
405. -- Monroe           2
406. -- Newport Hills    1
407.
408. SELECT t.Name AS [Town], COUNT(DISTINCT e.ManagerID) AS [NUMBER OF Managers]
409. FROM Employees e
410.   JOIN Employees m
411.     ON e.ManagerID = m.EmployeeID
414.   JOIN Towns t
415.     ON a.TownID = t.TownID
416. GROUP BY t.Name;
417.
418. -----------------------------------------------------------------------------------------------------------
419. -- Problem 29.  Write a SQL to create table WorkHours to store work reports for each employee.
420. -- Each employee should have id, date, task, hours and comments. Don't forget to define identity,
421. -- primary key and appropriate foreign key.
422.
423. CREATE TABLE WorkHours (
424.   WorkHourID INT IDENTITY,
425.   WorkDate DATETIME,
426.   EmployeeID INT FOREIGN KEY REFERENCES Employees(EmployeeID) NOT NULL,
428.   WorkHours INT NOT NULL,
429.   Comment NVARCHAR(300) NULL,
430.   CONSTRAINT PK_WorkHours PRIMARY KEY(WorkHourID)
431. );
432.
433. -----------------------------------------------------------------------------------------------------------
434. -- Problem 30.  Issue few SQL statements to insert, update and delete of some data in the table.
435.
436. INSERT INTO [dbo].[WorkHours]
437.            ([WorkDate]
438.            ,[EmployeeID]
440.            ,[WorkHours]
441.            ,[Comment])
442.      VALUES
443.            (GETDATE(),
444.             2,
446.            3,
448.
449. INSERT INTO [dbo].[WorkHours]
450.            ([WorkDate]
451.            ,[EmployeeID]
453.            ,[WorkHours]
454.            ,[Comment])
455.      VALUES
456.            (GETDATE(),
457.             4,
459.            3,
461.
462. INSERT INTO [dbo].[WorkHours]
463.            ([WorkDate]
464.            ,[EmployeeID]
466.            ,[WorkHours]
467.            ,[Comment])
468.      VALUES
469.            (GETDATE(),
470.             2,
472.            3,
473.            NULL);
474.
475. UPDATE WorkHours SET Comment = 'No comment'
476. WHERE Comment IS NULL;
477.
478. DELETE FROM WorkHours WHERE EmployeeID = 4;
479.
480. -----------------------------------------------------------------------------------------------------------
481. -- Problem 31.  Define a table WorkHoursLogs to track all changes in the WorkHours table with triggers.
482. -- For each change keep the old record data, the new record data and the command (insert / update / delete).
483.
484.
485. CREATE TABLE WorkHoursLogs (
486.   WorkHoursLogID INT IDENTITY,
487.   LogCommand NVARCHAR(6) NOT NULL,
488.   OldWorkHourID INT NULL,
489.   OldWorkDate DATETIME NULL,
490.   OldEmployeeID INT FOREIGN KEY REFERENCES Employees(EmployeeID) NULL,
492.   OldWorkHours INT NULL,
493.   OldComment NVARCHAR(300) NULL,
494.   NewWorkHourID INT NULL,
495.   NewWorkDate DATETIME NULL,
496.   NewEmployeeID INT FOREIGN KEY REFERENCES Employees(EmployeeID) NULL,
498.   NewWorkHours INT NULL,
499.   NewComment NVARCHAR(300) NULL,
500.   CONSTRAINT PK_WorkHoursLogs PRIMARY KEY(WorkHoursLogID)
501. );
502.
503. CREATE TRIGGER tr_WorkHoursDelete ON WorkHours FOR DELETE
504. AS
505. BEGIN
506.     INSERT INTO WorkHoursLogs
507.     SELECT 'DELETE', * , NULL, NULL, NULL, NULL, NULL, NULL
508.     FROM DELETED
509. END;
510.
511. CREATE TRIGGER tr_WorkHoursUpdate ON WorkHours FOR UPDATE
512. AS
513. BEGIN
514.     SET NOCOUNT ON;
515.     INSERT INTO WorkHoursLogs
516.     SELECT 'UPDATE',
517.            d.WorkHourID,
518.            d.WorkDate,
519.            d.EmployeeID,
521.            d.WorkHours,
522.            d.Comment,
523.            i.WorkHourID,
524.            i.WorkDate,
525.            i.EmployeeID,
527.            i.WorkHours,
528.            i.Comment
529.     FROM INSERTED i, DELETED d
530. END;
531.
532. CREATE TRIGGER tr_WorkHoursInsert ON WorkHours FOR INSERT
533. AS
534. BEGIN
535.     INSERT INTO WorkHoursLogs
536.     SELECT 'INSERT', NULL, NULL, NULL, NULL, NULL, NULL, *
537.     FROM INSERTED
538. END;
539.
540. -----------------------------------------------------------------------------------------------------------
541. -- Problem 32.  Start a database transaction, delete all employees from the 'Sales' department along
542. -- with all dependent records from the pother tables. At the end rollback the transaction.
543.
544. BEGIN TRAN;
545.
546. DELETE Employees FROM Employees e
547.  JOIN Departments d
548.  ON e.EmployeeID = d.DepartmentID
549. WHERE d.Name = 'Sales';
550.
551. ROLLBACK TRAN;
552.
553. -----------------------------------------------------------------------------------------------------------
554. -- Problem 33.  Start a database transaction and drop the table EmployeesProjects.
555. -- Then how you could restore back the lost table data?
556.
557. BEGIN TRAN;
558.
559. DROP TABLE EmployeesProjects;
560.
561. ROLLBACK TRAN;
562.
563. -----------------------------------------------------------------------------------------------------------
564. -- Problem 34.  Find how to use temporary tables in SQL Server.
565. -- Using temporary tables backup all records from EmployeesProjects and restore them back after
566. -- dropping and re-creating the table.
567.
568. SELECT * INTO #TempTableProjects
569. FROM EmployeesProjects;
570.
571.  DROP TABLE EmployeesProjects;
572.
573.  CREATE TABLE EmployeesProjects
574.   (
575.    EmployeeID INT FOREIGN KEY REFERENCES Employees(EmployeeID) NOT NULL,
576.    ProjectID INT FOREIGN KEY REFERENCES Projects(ProjectID) NOT NULL,
577.   );
578.
579.  INSERT INTO EmployeesProjects
580.  SELECT * FROM  #TempTableProjects;
581.
582.  DROP TABLE tempdb.#TempTableProjects;
583.
584. -----------------------------------------------------------------------------------------------------------