Advertisement
Guest User

SQL 1 full

a guest
Oct 23rd, 2019
165
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 5.68 KB | None | 0 0
  1. --a
  2. create table Postoffices
  3. (
  4. postNbr char(5),
  5. postOffice varchar(20),
  6. constraint pk_Postoffices primary key(postNbr)
  7. );
  8.  
  9. create table Newspapers
  10. (
  11. newsPaper varchar(20),
  12. nType varchar(20),
  13. monthPrice int,
  14. constraint ck_monthPrice check (monthPrice between 5 and 100),
  15. constraint pk_Newspapers primary key(newsPaper)
  16. );
  17.  
  18. create table Customers
  19. (
  20. customerNbr int,
  21. lastname varchar(30),
  22. firstname varchar(20),
  23. street varchar(30),
  24. postNbr char(5),
  25. constraint pk_Customers primary key(customerNbr),
  26. constraint fk_customersPostNbr foreign key(postNbr) references Postoffices(postNbr)
  27. );
  28.  
  29. create table Subscriptions
  30. (
  31. customerNbr int,
  32. newsPaper varchar(20),
  33. months int,
  34. constraint ck_Subscriptions check(months between 1 and 12),
  35. constraint fk_customerNbr foreign key(customerNbr) references Customers(customerNbr),
  36. constraint fk_newsPaper foreign key(Newspaper) references Newspapers(newsPaper),
  37. constraint pk_Subscriptions primary key(customerNbr, newsPaper)
  38. );
  39.  
  40. --b
  41. insert into Customers(customerNbr, lastname, firstname, street, postNbr) values
  42. ('1', 'Anka', 'Kalle', 'Ankgränd 2', '66000'),
  43. ('2', 'Anka', 'Kajsa', 'Ankgränd 3', '66000'),
  44. ('3', 'Pan', 'Peter', 'Barngatan 11', '11000'),
  45. ('4', 'Puh', 'Nalle', 'Honungsgatan 1', '55000'),
  46. ('5', 'Åsna', 'Ior', 'Ekgränd 9', '550000'),
  47. ('6', 'Kanin', 'Kalle', 'Ekgatan 2', '55000'),
  48. ('7', 'Ling', 'Tinge', 'Barngatan 11', '11000'),
  49. ('8', 'Lång', 'Ben', 'Ankgatan 3', '66000');
  50.  
  51. insert into Postoffices values
  52. ('11000', 'INGENSTANS'),
  53. ('55000', 'SJUMILASKOGEN'),
  54. ('66000', 'ANKEBORG');
  55.  
  56. select * from Postoffices
  57. select * from customers
  58.  
  59. insert into Newspapers values
  60. ('Tietokone', 'fakta', '15'),
  61. ('Apu', 'familje', '10'),
  62. ('Seura', 'familje', '12'),
  63. ('Suosikki', 'ungdoms', '20'),
  64. ('MikroPC', 'fakta', '15'),
  65. ('Vasabladet', 'dags', '20'),
  66. ('Pohjalainen', 'dags', '22');
  67.  
  68. insert into Subscriptions values
  69. ('1', 'Suosikki', '3'),
  70. ('1', 'Apu', '12'),
  71. ('2', 'Seura', '4'),
  72. ('2', 'Tietokone', '12'),
  73. ('2', 'Vasabladet', '12'),
  74. ('4', 'Vasabladet', '3'),
  75. ('4', 'Pohjalainen', '6'),
  76. ('5', 'Suosikki', '3'),
  77. ('5', 'Pohjalainen', '10'),
  78. ('6', 'Tietokone', '12'),
  79. ('6', 'Vasabladet', '3'),
  80. ('6', 'Pohjalainen', '12'),
  81. ('7', 'Vasabladet', '12'),
  82. ('8', 'Apu', '12'),
  83. ('8', 'Seura', '3'),
  84. ('8', 'Suosikki', '9');
  85.  
  86. --c
  87. alter table dbo.Customers
  88. add phone char(15),
  89. constraint df_phone default null for phone;
  90.  
  91. --d
  92. create index newspaperindex on dbo.Newspapers(nType);
  93. create index subsIndex on dbo.Subscriptions(customerNbr, newsPaper);
  94.  
  95. --e
  96. update Customers set street = 'åsnestigen 5',
  97. postNbr = 11000 where customerNbr = 5;
  98. update Newspapers set monthPrice = monthPrice + 2 where nType in('familje', 'fakta');
  99. update subscriptions set months = 12 where customerNbr = 5 and newsPaper = 'Pohjalainen'
  100. --går inte att ändra till 'help'
  101. --f
  102. delete from Subscriptions where newsPaper = 'Apu';
  103. delete from Newspapers where newsPaper = 'Apu';
  104.  
  105. select newspaper, ntype, monthprice from Newspapers order by monthPrice desc, newsPaper asc;
  106. --g
  107. select distinct Newspapers as 'tidning', monthprice as 'pris',
  108. monthprice * 5.95 as 'markpris' from newspaper;
  109.  
  110. --h -->groupby och statistiska funktioner
  111. select newspaper, count(*) as 'antal'
  112. from Subscriptions
  113. group by newsPaper;
  114.  
  115. --antalet prenumerationer per tidning
  116. select Newspaper, sum(months) as 'månader'
  117. from Subscriptions
  118. group by newsPaper;
  119.  
  120. --totala antalet prenumerationer
  121. select count(*) as 'totalantal'
  122. from Subscriptions;
  123.  
  124. --medelmånadspriset för alla tidningar
  125. select avg(monthprice) as 'medelpris'
  126. from Newspapers;
  127.  
  128. --i -->selektion med where
  129. --namnet på de tidningar som hör till typen fakta eller familje
  130. select *
  131. from Newspapers
  132. where nType = 'fakta' or newsPaper = 'familje';
  133.  
  134. select *
  135. from Newspaper
  136. where ntype like 'f%';
  137.  
  138. select *
  139. from Newspapers
  140. where nType in ('fakta', 'familje');
  141.  
  142. --namnet på alla kunder som bor på en gata
  143. select firstname, lastname
  144. from Customers
  145. where street like '%gata%';
  146.  
  147. --namnet på alla Ankor och Kallar
  148. select lastname, firstname
  149. from Customers
  150. where firstname = 'Kalle' or lastname = 'Anka';
  151.  
  152. --antalet Ankor
  153. select count(*) as 'antalet ankor'
  154. from Customers
  155. where lastname = 'Anka';
  156.  
  157. --namnet på de tidningar som har minst 20 prenumerationsmånader
  158. select Newspaper, sum(months) as 'månader'
  159. from Subscriptions
  160. group by newsPaper
  161. having sum(months) > 20;
  162.  
  163. --j -->join
  164. --efternamn, förnamn, gatuaddress, postnummer och anstalt för alla kunder sorterade
  165. --i stigande sekvens enligt postanstalt, efternamn och förnamn
  166. select Customers.firstname, Customers.lastname, Customers.street, Customers.postNbr,
  167. Postoffices.postOffice
  168. from Customers inner join Postoffices
  169. on Customers.postNbr = Postoffices.postNbr
  170. order by Postoffices.postOffice asc,
  171. Customers.lastname asc,
  172. Customers.firstname;
  173.  
  174. --Antalet prenumerationer per tidning. Även tidningar som saknar prenumerationer bör tas med .
  175. SELECT Newspapers.newsPaper, COUNT(Subscriptions.newsPaper) AS 'antalet prenumerationer'
  176. FROM Newspapers LEFT OUTER JOIN Subscriptions
  177. ON Newspapers.newsPaper = Subscriptions.newsPaper
  178. GROUP BY Newspapers.newsPaper;
  179.  
  180. --Totalinkomsten för alla prenumerationer tillsammans.
  181. SELECT SUM(Subscriptions.months*Newspapers.monthprice) AS 'totalinkomst'
  182. FROM Newspapers INNER JOIN Subscriptions
  183. ON Newspapers.newsPaper= Subscriptions.newsPaper;
  184.  
  185. --k
  186. --Namnet på alla kunder som har samma postanstalt som Nalle Puh.
  187. SELECT firstname, lastname FROM Customers WHERE postnbr =
  188. (SELECT postnbr FROM Customers WHERE firstname='Nalle' AND lastname='Puh');
  189.  
  190. --Namnet på den tidning som har det högsta månadspriset.
  191. SELECT newspaper FROM Newspapers WHERE monthprice = (SELECT MAX(monthprice) FROM Newspapers);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement