Advertisement
g-stoyanov

Advanced SQL HW

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
  139.   JOIN Addresses a
  140.     ON e.AddressID = a.AddressID
  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.
  208. -- Users should have username, password, full name and last login time.
  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,
  216.   UserName NVARCHAR(10) NOT NULL,
  217.   UserPassword NVARCHAR(30) NOT NULL,
  218.   FullName NVARCHAR(50) NOT NULL,
  219.   LastLogin DATETIME,
  220.   CONSTRAINT PK_Users PRIMARY KEY(UserID),
  221.   CONSTRAINT UNQ_Users UNIQUE(UserName),
  222.   CONSTRAINT CHK_Password CHECK (LEN(UserPassword) >= 5)
  223. );
  224.  
  225. INSERT INTO [dbo].[Users]
  226.            ([UserName]
  227.            ,[UserPassword]
  228.            ,[FullName]
  229.            ,[LastLogin])
  230.      VALUES
  231.            ('Nakata',
  232.             'passwd',
  233.             'Svetlin Nakov',
  234.             GETDATE());
  235.  
  236. INSERT INTO [dbo].[Users]
  237.            ([UserName]
  238.            ,[UserPassword]
  239.            ,[FullName]
  240.            ,[LastLogin])
  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
  256. WHERE DAY(LastLogin) = DAY(GETDATE());
  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],
  323.        NULL AS [LastLogin],
  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
  392.   JOIN Addresses a
  393.     ON e.AddressID = a.AddressID
  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
  412.   JOIN Addresses a
  413.     ON m.AddressID = a.AddressID
  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,
  427.   Task NVARCHAR(100) 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]
  439.            ,[Task]
  440.            ,[WorkHours]
  441.            ,[Comment])
  442.      VALUES
  443.            (GETDATE(),
  444.             2,
  445.            'task 1',
  446.            3,
  447.            'task 1 comment');
  448.  
  449. INSERT INTO [dbo].[WorkHours]
  450.            ([WorkDate]
  451.            ,[EmployeeID]
  452.            ,[Task]
  453.            ,[WorkHours]
  454.            ,[Comment])
  455.      VALUES
  456.            (GETDATE(),
  457.             4,
  458.            'task 2',
  459.            3,
  460.            'task 2 comment');
  461.  
  462. INSERT INTO [dbo].[WorkHours]
  463.            ([WorkDate]
  464.            ,[EmployeeID]
  465.            ,[Task]
  466.            ,[WorkHours]
  467.            ,[Comment])
  468.      VALUES
  469.            (GETDATE(),
  470.             2,
  471.            'task 1',
  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,
  491.   OldTask NVARCHAR(100) 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,
  497.   NewTask NVARCHAR(100) 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,
  520.            d.Task,
  521.            d.WorkHours,
  522.            d.Comment,
  523.            i.WorkHourID,
  524.            i.WorkDate,
  525.            i.EmployeeID,
  526.            i.Task,
  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. -----------------------------------------------------------------------------------------------------------
Advertisement
Advertisement
Advertisement
RAW Paste Data Copied
Advertisement