SHARE
TWEET

SQL Introduction HW

g-stoyanov Feb 10th, 2015 309 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Problem 3.   Start SQL Management Studio and connect to the database SoftUni.
  2. -- Examine the major tables in the "SoftUni" database.
  3. -- Your task is to examine the tables in the “SoftUni” database.
  4. -- You do not have to submit anything in your homework for this problem.
  5.  
  6. SELECT TABLE_NAME AS "Table name"
  7. FROM SoftUni.INFORMATION_SCHEMA.TABLES;
  8.  
  9. -----------------------------------------------------------------------------------------------------------
  10. -- Problem 4.   Write a SQL query to find all information about all departments (use "SoftUni" database).
  11. -- You should submit a SQL file as a part of your homework.
  12.  
  13. SELECT * FROM Departments;
  14.  
  15. -----------------------------------------------------------------------------------------------------------
  16. -- Problem 5.   Write a SQL query to find all department names.
  17. -- You should submit a SQL file as a part of your homework.
  18.  
  19. SELECT Name FROM Departments;
  20.  
  21. -----------------------------------------------------------------------------------------------------------
  22. -- Problem 6.   Write a SQL query to find the salary of each employee.
  23. -- You should submit a SQL file as a part of your homework.
  24.  
  25. SELECT FirstName + ' ' + LastName AS Name, Salary
  26. FROM Employees;
  27.  
  28. -----------------------------------------------------------------------------------------------------------
  29. -- Problem 7.   Write a SQL to find the full name of each employee.
  30. -- You should submit a SQL file as a part of your homework.
  31.  
  32. SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [FULL Name]
  33. FROM Employees;
  34.  
  35. -----------------------------------------------------------------------------------------------------------
  36. -- Problem 8.   Write a SQL query to find the email addresses of each employee.
  37. -- Write a SQL query to find the email addresses of each employee. (by his first and last name).
  38. -- Consider that the mail domain is softuni.bg. Emails should look like “John.Doe@softuni.bg".
  39. -- The produced column should be named "Full Email Addresses".
  40. -- You should submit a SQL file as a part of your homework.
  41.  
  42. SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [FULL Name],
  43.        FirstName + '.' + LastName + '@softuni.bg' AS [FULL Email Addresses]
  44. FROM Employees;
  45.  
  46. -----------------------------------------------------------------------------------------------------------
  47. -- Problem 9.   Write a SQL query to find all different employee salaries.
  48. -- You should submit a SQL file as a part of your homework.
  49.  
  50. SELECT DISTINCT Salary
  51. FROM Employees;
  52.  
  53. -----------------------------------------------------------------------------------------------------------
  54. -- Problem 10.  Write a SQL query to find all information about the employees whose job
  55. -- title is “Sales Representative“.
  56. -- You should submit a SQL file as a part of your homework.
  57.  
  58. SELECT * FROM Employees
  59. WHERE JobTitle = 'Sales Representative';
  60.  
  61. -----------------------------------------------------------------------------------------------------------
  62. -- Problem 11.  Write a SQL query to find the names of all employees whose first name starts with "SA".
  63. -- You should submit a SQL file as a part of your homework.
  64.  
  65. SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [FULL Name]
  66. FROM Employees
  67. WHERE FirstName LIKE 'SA%';
  68.  
  69. -----------------------------------------------------------------------------------------------------------
  70. -- Problem 12.  Write a SQL query to find the names of all employees whose last name contains "ei".
  71. -- You should submit a SQL file as a part of your homework.
  72.  
  73. SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [FULL Name]
  74. FROM Employees
  75. WHERE LastName LIKE '%ei%';
  76.  
  77. -----------------------------------------------------------------------------------------------------------
  78. -- Problem 13.  Write a SQL query to find the salary of all employees whose salary is
  79. -- in the range [20000…30000].
  80. -- You should submit a SQL file as a part of your homework.
  81.  
  82. SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [FULL Name],
  83.        Salary
  84. FROM Employees
  85. WHERE Salary BETWEEN 20000 AND 30000;
  86.  
  87. -----------------------------------------------------------------------------------------------------------
  88. -- Problem 14.  Write a SQL query to find the names of all employees whose salary
  89. -- is 25000, 14000, 12500 or 23600.
  90. -- You should submit a SQL file as a part of your homework.
  91.  
  92. SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [FULL Name],
  93.        Salary
  94. FROM Employees
  95. WHERE Salary IN(25000, 14000, 12500, 23600);
  96.  
  97. -----------------------------------------------------------------------------------------------------------
  98. -- Problem 15.  Write a SQL query to find all employees that do not have manager.
  99. -- You should submit a SQL file as a part of your homework.
  100.  
  101. SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [FULL Name]
  102. FROM Employees
  103. WHERE ManagerID IS NULL;
  104.  
  105. -----------------------------------------------------------------------------------------------------------
  106. -- Problem 16.  Write a SQL query to find all employees that have salary more than 50000.
  107. -- Order them in decreasing order by salary.
  108. -- You should submit a SQL file as a part of your homework.
  109.  
  110. SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [FULL Name],
  111.        Salary
  112. FROM Employees
  113. WHERE Salary > 50000
  114. ORDER BY Salary DESC;
  115.  
  116. -----------------------------------------------------------------------------------------------------------
  117. -- Problem 17.  Write a SQL query to find the top 5 best paid employees.
  118. -- You should submit a SQL file as a part of your homework.
  119.  
  120. SELECT TOP(5) FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName AS [FULL Name], Salary
  121. FROM Employees
  122. ORDER BY Salary DESC;
  123.  
  124. -----------------------------------------------------------------------------------------------------------
  125. -- Problem 18.  Write a SQL query to find all employees along with their address.
  126. -- Use inner join with ON clause.
  127. -- You should submit a SQL file as a part of your homework.
  128.  
  129. SELECT e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName AS [FULL Name],
  130.        t.Name AS Town,
  131.            a.AddressText AS [Address]
  132. FROM Employees e
  133.   JOIN Addresses a
  134.     ON e.AddressID = a.AddressID
  135.   JOIN Towns t
  136.     ON a.TownID = t.TownID;
  137.  
  138. -----------------------------------------------------------------------------------------------------------
  139. -- Problem 19.  Write a SQL query to find all employees and their address.
  140. -- Use equijoins (conditions in the WHERE clause).
  141. -- You should submit a SQL file as a part of your homework.
  142.  
  143. SELECT e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName AS [FULL Name],
  144.        t.Name AS Town,
  145.            a.AddressText AS [Address]
  146. FROM Employees e, Addresses a, Towns t
  147. WHERE e.AddressID = a.AddressID AND a.TownID = t.TownID;
  148.  
  149. -----------------------------------------------------------------------------------------------------------
  150. -- Problem 20.  Write a SQL query to find all employees along with their manager.
  151. -- You should submit a SQL file as a part of your homework.
  152.  
  153. SELECT e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName AS [Employee FULL Name],
  154.        m.FirstName + ' ' + ISNULL(m.MiddleName + ' ', '') + m.LastName AS [Manager FULL Name]
  155. FROM Employees e
  156.   JOIN Employees m
  157.     ON e.ManagerID = m.EmployeeID;
  158.  
  159. -----------------------------------------------------------------------------------------------------------
  160. -- Problem 21.  Write a SQL query to find all employees, along with their manager and their address.
  161. -- You should join the 3 tables: Employees e, Employees m and Addresses a.
  162. -- You should submit a SQL file as a part of your homework.
  163.  
  164. SELECT e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName AS [Employee FULL Name],
  165.        m.FirstName + ' ' + ISNULL(m.MiddleName + ' ', '') + m.LastName AS [Manager FULL Name],
  166.            t.Name AS Town,
  167.            a.AddressText AS [Address]
  168. FROM Employees e
  169.   JOIN Addresses a
  170.     ON e.AddressID = a.AddressID
  171.   JOIN Towns t
  172.     ON a.TownID = t.TownID
  173.   JOIN Employees m
  174.     ON e.ManagerID = m.EmployeeID;
  175.  
  176. -----------------------------------------------------------------------------------------------------------
  177. -- Problem 22.  Write a SQL query to find all departments and all town names as a single list.
  178. -- Use UNION.
  179. -- You should submit a SQL file as a part of your homework.
  180.  
  181. SELECT Name AS [Towns AND Departments List]
  182. FROM Departments
  183. UNION
  184. SELECT Name AS [Towns AND Departments List]
  185. FROM Towns;
  186.  
  187. -----------------------------------------------------------------------------------------------------------
  188. -- Problem 23.  Write a SQL query to find all the employees and the manager for each of them
  189. -- along with the employees that do not have manager.
  190. -- Use right outer join. Rewrite the query to use left outer join.
  191. -- You should submit a SQL file as a part of your homework.
  192.  
  193. -- LEFT OUTER JOIN
  194. SELECT e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName AS [Employee FULL Name],
  195.        ISNULL(m.FirstName + ' ' + ISNULL(m.MiddleName + ' ', '') + m.LastName, '') AS [Manager FULL Name]
  196. FROM Employees e LEFT OUTER JOIN Employees m
  197.   ON e.ManagerID = m.EmployeeID;
  198.  
  199. -- RIGHT OUTER JOIN
  200. SELECT e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName AS [Employee FULL Name],
  201.        ISNULL(m.FirstName + ' ' + ISNULL(m.MiddleName + ' ', '') + m.LastName, '') AS [Manager FULL Name]
  202. FROM Employees m RIGHT OUTER JOIN Employees e
  203.   ON e.ManagerID = m.EmployeeID;
  204.  
  205. -----------------------------------------------------------------------------------------------------------
  206. -- Problem 24.  Write a SQL query to find the names of all employees from the departments
  207. -- "Sales" and "Finance" whose hire year is between 1995 and 2005.
  208. -- You should submit a SQL file as a part of your homework.
  209.  
  210. SELECT e.FirstName + ' ' + ISNULL(e.MiddleName + ' ', '') + e.LastName AS [Employee FULL Name],
  211.        YEAR(e.HireDate) AS [Hire YEAR],
  212.            d.Name AS [Department Name]
  213. FROM Employees e
  214.   JOIN Departments d
  215.     ON e.DepartmentID = d.DepartmentID
  216. WHERE YEAR(HireDate) BETWEEN 1995 AND 2005 AND d.Name IN('Sales', 'Finance');
  217.  
  218. -----------------------------------------------------------------------------------------------------------
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