Advertisement
didito33

Tutorial Select Queries

Mar 16th, 2022
73
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Nested Queries
  2.  
  3. -- Find all clients who are handled by the branch
  4. -- that Michael Scott manages
  5. -- Assume you know Michael's ID
  6. SELECT client.client_name
  7. FROM client
  8. WHERE client.branch_id = (
  9.     SELECT branch.branch_id
  10.     FROM branch
  11.     WHERE branch.mgr_id = 102
  12.     LIMIT 1
  13. );
  14. -- Find names of all employees who have
  15. -- sold over 30000 to a single client
  16.  
  17. SELECT employee.first_name, employee.last_name
  18. FROM employee
  19. WHERE employee.emp_id IN (
  20.     SELECT works_with.emp_id
  21.     FROM works_with
  22.     WHERE works_with.total_sales > 30000
  23. );
  24.  
  25. -- Joins
  26.  
  27. -- Find all branches and the names of their managers
  28. SELECT employee.emp_id, employee.first_name, branch.branch_name
  29. FROM employee
  30. JOIN branch
  31. ON employee.emp_id = branch.mgr_id;
  32.  
  33. INSERT INTO branch VALUES (4,'Buffalo',NULL,NULL);
  34.  
  35. SELECT * FROM branch;
  36.  
  37. -- Unions
  38.  
  39. -- Find a list of all money spent or earned by the company
  40. SELECT salary AS Spent_Earned
  41. FROM employee
  42. UNION
  43. SELECT total_sales
  44. FROM works_with;
  45. -- Find a list of all clients & branch suppliers' names
  46. SELECT client_name, client.branch_id
  47. FROM client
  48. UNION
  49. SELECT supplier_name, branch_supplier.branch_id
  50. FROM branch_supplier;
  51.  
  52. -- Find a list of employee and branch names
  53. SELECT first_name AS Company_Names
  54. FROM employee
  55. UNION
  56. SELECT branch_name
  57. FROM branch;
  58.  
  59. -- -WildCards---
  60.  
  61. -- Find any clients who are schools
  62. SELECT * FROM client
  63. WHERE client_name LIKE '%school';
  64.  
  65. -- Find any employee born in october
  66. SELECT * FROM employee
  67. WHERE birth_day LIKE '____-10%';
  68.  
  69. -- Find any branch suppliers who are in the label business
  70. SELECT *
  71. FROM branch_supplier
  72. WHERE supplier_name LIKE '% Label%';
  73.  
  74. -- Find any clients who are an LLC
  75. SELECT *
  76. FROM client
  77. WHERE client_name LIKE '%LLC';
  78.  
  79. -- GROUP BY---
  80.  
  81. -- Find the total sales of each salesman
  82. SELECT SUM(total_sales), emp_id
  83. FROM works_with
  84. GROUP BY emp_id;
  85.  
  86. -- Find out how many males an females there are
  87. SELECT COUNT(sex), sex
  88. FROM employee
  89. GROUP BY sex;
  90.  
  91. -- WHERE---
  92.  
  93. -- Find the sum of all employee's salaries
  94. SELECT SUM(salary)
  95. FROM employee
  96. WHERE sex = 'M';
  97.  
  98. -- Find the average of all employee's salaries
  99. SELECT AVG(salary)
  100. FROM employee
  101. WHERE sex = 'M';
  102.  
  103. -- Find the number of female employees born after 1970
  104. SELECT COUNT(emp_id)
  105. FROM employee
  106. WHERE sex = 'F' AND birth_day > '1970-01.01';
  107. Select * from employee;
Advertisement
RAW Paste Data Copied
Advertisement