AlessioMaddaluno

Untitled

Feb 25th, 2021
2,413
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. # 1
  2.  
  3. SELECT *
  4. FROM deliverers;
  5.  
  6. #2
  7.  
  8. SELECT companyid
  9. FROM companies;
  10.  
  11. #3
  12.  
  13. SELECT d.name, d.delivererid
  14. FROM deliverers d
  15. WHERE d.name LIKE 'B%'
  16.  
  17. #4
  18.  
  19. SELECT d.name, d.sex,d.delivererid
  20. FROM deliverers d
  21. WHERE d.phoneno <> '8467' OR d.phoneno IS NULL;
  22.  
  23. #5
  24.  
  25. SELECT DISTINCT d.name, d.town
  26. FROM deliverers d JOIN penalties pe
  27. ON d.delivererid = pe.delivererid
  28. ORDER BY d.name;
  29.  
  30. #6
  31.  
  32. SELECT DISTINCT comref.name, comref.initials
  33. FROM (SELECT d.delivererid, d.name, d.initials
  34.       FROM deliverers d JOIN companies com ON com.delivererid = d.delivererid) comref
  35. JOIN penalties pen ON comref.delivererid = pen.delivererid
  36. WHERE pen.DATA > TO_DATE('31/12/1980','DD/MM/YYY');
  37.  
  38. #7
  39.  
  40. SELECT DISTINCT cd.companyid,d.delivererid
  41. FROM (SELECT delivererid FROM deliverers WHERE deliverers.town = 'Stratford') d
  42. JOIN companydel cd ON cd.delivererid = d.delivererid
  43. WHERE cd.numcollections > 2 AND cd.numdeliveries > 1;
  44.  
  45. #8
  46.  
  47. SELECT DISTINCT cd.delivererid
  48. FROM companydel cd
  49. NATURAL JOIN (SELECT d.delivererid FROM deliverers d WHERE d.year_of_birth > 1962) de
  50. NATURAL JOIN (SELECT com.companyid FROM companies com WHERE com.mandate = 'first') comp;
  51.  
  52. ### non esiste un deliverid = 67 ma solo uno = 57
  53.  
  54. #9
  55.  
  56. SELECT del.name
  57. FROM deliverers del
  58. WHERE del.delivererId IN (SELECT del.delivererid
  59. FROM companydel com
  60. JOIN (SELECT d.delivererid FROM deliverers d WHERE d.town = 'Inglewood' OR d.town = 'Stratford') del
  61. ON del.delivererid = com.delivererid
  62. GROUP BY del.delivererid
  63. HAVING COUNT(*) >= 2)
  64.  
  65. #10
  66.  
  67. SELECT del.delivererId,SUM(pen.amount)
  68. FROM penalties pen
  69. NATURAL JOIN (SELECT d.delivererId FROM deliverers d WHERE d.town = 'Inglewood') del
  70. GROUP BY del.delivererId
  71. HAVING COUNT(*) >= 2
  72.  
  73. #11
  74.  
  75. SELECT del.name,MIN(pen.amount)
  76. FROM penalties pen
  77. NATURAL JOIN deliverers del
  78. GROUP BY del.delivererId
  79. HAVING COUNT(*) >= 2 AND COUNT(*) < 4
  80.  
  81. #12
  82.  
  83. SELECT SUM(x.numdeliveries), SUM(x.numcollections)
  84. FROM (SELECT com.numdeliveries, com.numcollections
  85. FROM companydel com
  86. NATURAL JOIN (SELECT * FROM deliverers d WHERE d.town <> 'Stratford' AND d.name LIKE 'B%') del
  87. GROUP BY com.delivererId,com.companyId) x
RAW Paste Data

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×