SHARE
TWEET

Basic CRUD

joro_thexfiles May 24th, 2020 (edited) 1,417 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. --2.Find All Information About Departments
  3. --CREATE VIEW v_2_Find_All_Information_About_Departments AS
  4. SELECT DepartmentID, [Name], ManagerID FROM Departments
  5.  
  6. --3.Find all Department Names
  7. SELECT [Name] FROM Departments
  8.  
  9. --4.Find Salary of Each Employee
  10. SELECT FirstName, LastName, Salary FROM Employees
  11.  
  12. --5.Find Full Name of Each Employee
  13. SELECT FirstName, MiddleName, LastName FROM Employees
  14.  
  15. --6.Find Email Address of Each Employee
  16. SELECT FirstName + '.' + LastName + '@softuni.bg' AS [Full Email Address] FROM Employees
  17.  
  18. --7.Find All Different Employee’s Salaries
  19. SELECT Salary
  20. FROM Employees
  21. GROUP BY Salary
  22.  
  23. SELECT DISTINCT Salary FROM Employees
  24.  
  25. --8.Find all Information About Employees
  26. SELECT * FROM Employees
  27. WHERE JobTitle = 'Sales representative'
  28.  
  29. --SELECT * FROM Employees
  30.  
  31. --9.Find Names of All Employees by Salary in Range
  32. SELECT FirstName, LastName , JobTitle FROM Employees
  33. Where Salary >= 20000 AND Salary <= 30000
  34.  
  35. --10.Find Names of All Employees
  36. SELECT FirstName + ' ' + MiddleName + ' ' + LAstName AS [Full Name] FROM Employees
  37. Where Salary IN (25000, 14000, 12500, 23600)
  38.  
  39. --11.Find All Employees Without Manager
  40. SELECT FirstName, LastName FROM Employees
  41. Where ManagerID IS NULL
  42.  
  43. --12.Find All Employees with Salary More Than 50000
  44. SELECT FirstName, LastName, Salary FROM Employees
  45. Where Salary > 50000
  46. ORDER BY Salary DESC
  47.  
  48. --13.Find 5 Best Paid Employees.
  49. SELECT TOP(5) FirstName, Lastname FROM Employees
  50. ORDER BY Salary DESC
  51.  
  52. --14.Find All Employees Except Marketing
  53. SELECT FirstName, LastName FROM Employees
  54. WHERE DepartmentID !=4
  55.  
  56. --15. Sort Employees Table
  57. SELECT * FROM Employees
  58. ORDER BY Salary DESC, FirstName ASC, LastName DESC, MiddleName ASC
  59.  
  60. --16.Create View Employees with Salaries
  61. CREATE VIEW v_EmployeesSalaries AS
  62. SELECT FirstName, LastName, Salary FROM Employees
  63.  
  64. SELECT * FROM v_EmployeesSalaries
  65. ORDER BY FirstName ASC, Salary DESC
  66.  
  67. SELECT FirstName, COUNT(*)
  68. FROM Employees
  69. GROUP BY FirstName
  70.  
  71. --17.Create View Employees with Job Titles
  72. CREATE VIEW V_EmployeeNameJobTitle AS
  73. SELECT FirstName + ' ' + ISNULL(MiddleName, '') + ' ' + LastName AS [FULL Name], JobTitle FROM Employees
  74.  
  75. --18.Distinct Job Titles
  76. SELECT DISTINCT JobTitle FROM Employees
  77.  
  78. --19.Find First 10 Started Projects
  79. SELECT TOP(10) * FROM Projects
  80. ORDER BY StartDate ASC, [Name] ASC
  81.  
  82. --20.Last 7 Hired Employees
  83. SELECT TOP(7) FirstName, LastName, HireDate FROM Employees
  84. ORDER BY HireDate DESC
  85.  
  86. --21.Increase Salaries
  87.  UPDATE Employees
  88.  SET Salary = Salary*1.12
  89.  WHERE DepartmentID = 1 OR DepartmentID = 2 OR DepartmentID = 4 OR DepartmentID = 11
  90.  SELECT Salary FROM Employees
  91.  
  92.  UPDATE Employees
  93.  SET Salary = Salary*1.12
  94.  WHERE DepartmentID IN(1,2,4,11)
  95.  SELECT Salary FROM Employees
  96.  
  97. --22.All Mountain Peaks
  98. SELECT PeakName FROM Peaks
  99. ORDER BY PeakName ASC
  100.  
  101. --23.Biggest Countries by Population
  102. SELECT TOP (30) [CountryName], [Population] FROM Countries
  103.  WHERE ContinentCode = 'EU'
  104.  ORDER BY [Population] DESC, CountryName ASC
  105.  
  106.  --24.*Countries and Currency (Euro / Not Euro)
  107.   SELECT CountryName, CountryCode,
  108.  IIF( CurrencyCode = 'EUR', 'Euro', 'Not Euro') AS Currency FROM Countries
  109.  ORDER BY CountryName ASC
  110.  
  111.  --25.All Diablo Characters
  112.  SELECT [Name] FROM Characters
  113.  ORDER BY [Name] ASC
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