• API
• FAQ
• Tools
• Archive
SHARE
TWEET

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
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,
99.         FullName nvarchar(60) NULL,
100.         LastLoggedIn datetime NOT NULL,
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
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
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
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,
204.         Hours SMALLINT NOT 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,
227.         OldHours SMALLINT NULL,
231.         NewHours SMALLINT 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'
255.                 FROM deleted d
256.         IF @operation = 'Insert'
259.                 FROM inserted i
260.         IF @operation = 'Update'
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)
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.

Top