sql playground
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

ZAGI.sql 9.4KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522
  1. -- create tables
  2. CREATE TABLE vendor (
  3. vendorid CHAR(2) NOT NULL,
  4. vendorname VARCHAR(25) NOT NULL,
  5. PRIMARY KEY (vendorid)
  6. );
  7. CREATE TABLE category (
  8. categoryid CHAR(2) NOT NULL,
  9. categoryname VARCHAR(25) NOT NULL,
  10. PRIMARY KEY (categoryid)
  11. );
  12. CREATE TABLE product (
  13. productid CHAR(3) NOT NULL,
  14. productname VARCHAR(25) NOT NULL,
  15. productprice NUMERIC (7,2) NOT NULL,
  16. vendorid CHAR(2) NOT NULL,
  17. categoryid CHAR(2) NOT NULL,
  18. PRIMARY KEY (productid),
  19. FOREIGN KEY (vendorid) REFERENCES vendor(vendorid),
  20. FOREIGN KEY (categoryid) REFERENCES
  21. category(categoryid)
  22. );
  23. CREATE TABLE region (
  24. regionid CHAR NOT NULL,
  25. regionname VARCHAR(25) NOT NULL,
  26. PRIMARY KEY (regionid)
  27. );
  28. CREATE TABLE store (
  29. storeid VARCHAR(3) NOT NULL,
  30. storezip CHAR(5) NOT NULL,
  31. regionid CHAR NOT NULL,
  32. PRIMARY KEY (storeid),
  33. FOREIGN KEY (regionid) REFERENCES region(regionid)
  34. );
  35. CREATE TABLE customer (
  36. customerid CHAR(7) NOT NULL,
  37. customername
  38. VARCHAR(15) NOT NULL,
  39. customerzip CHAR(5) NOT NULL,
  40. PRIMARY KEY (customerid)
  41. );
  42. CREATE TABLE salestransaction (
  43. tid VARCHAR(8) NOT NULL,
  44. customerid CHAR(7) NOT NULL,
  45. storeid VARCHAR(3) NOT NULL,
  46. tdate DATE NOT NULL,
  47. PRIMARY KEY (tid),
  48. FOREIGN KEY (customerid) REFERENCES
  49. customer(customerid),
  50. FOREIGN KEY (storeid) REFERENCES store(storeid)
  51. );
  52. CREATE TABLE soldvia (
  53. productid CHAR(3) NOT NULL,
  54. tid VARCHAR(8) NOT NULL,
  55. noofitems INT NOT NULL,
  56. PRIMARY KEY (productid, tid),
  57. FOREIGN KEY (productid) REFERENCES product(productid),
  58. FOREIGN KEY (tid) REFERENCES salestransaction(tid)
  59. );
  60. -- insert data into tables
  61. INSERT INTO vendor VALUES ('PG','Pacifica Gear');
  62. INSERT INTO vendor VALUES ('MK','Mountain King');
  63. INSERT INTO category VALUES ('CP','Camping');
  64. INSERT INTO category VALUES ('FW','Footwear');
  65. INSERT INTO product VALUES ('1X1','Zzz Bag',100,'PG','CP');
  66. INSERT INTO product VALUES ('2X2','Easy Boot',70,'MK','FW');
  67. INSERT INTO product VALUES ('3X3','Cosy Sock',15,'MK','FW');
  68. INSERT INTO product VALUES ('4X4','Dura Boot',90,'PG','FW');
  69. INSERT INTO product VALUES ('5X5','Tiny Tent',150,'MK','CP');
  70. INSERT INTO product VALUES ('6X6','Biggy Tent',250,'MK','CP');
  71. INSERT INTO region VALUES ('C','Chicagoland');
  72. INSERT INTO region VALUES ('T','Tristate');
  73. INSERT INTO store VALUES ('S1','60600','C');
  74. INSERT INTO store VALUES ('S2','60605','C');
  75. INSERT INTO store VALUES ('S3','35400','T');
  76. INSERT INTO customer VALUES ('1-2-333','Tina','60137');
  77. INSERT INTO customer VALUES ('2-3-444','Tony','60611');
  78. INSERT INTO customer VALUES ('3-4-555','Pam ','35401');
  79. INSERT INTO salestransaction VALUES (
  80. 'T111','1-2-333','S1','2013-01-01'
  81. );
  82. INSERT INTO salestransaction VALUES (
  83. 'T222','2-3-444','S2','2013-01-01'
  84. );
  85. INSERT INTO salestransaction VALUES (
  86. 'T333','1-2-333','S3','2013-01-02'
  87. );
  88. INSERT INTO salestransaction VALUES (
  89. 'T444','3-4-555','S3','2013-01-02'
  90. );
  91. INSERT INTO salestransaction VALUES (
  92. 'T555','2-3-444','S3','2013-01-02'
  93. );
  94. INSERT INTO soldvia VALUES ('1X1','T111',1);
  95. INSERT INTO soldvia VALUES ('2X2','T222',1);
  96. INSERT INTO soldvia VALUES ('3X3','T333',5);
  97. INSERT INTO soldvia VALUES ('1X1','T333',1);
  98. INSERT INTO soldvia VALUES ('4X4','T444',1);
  99. INSERT INTO soldvia VALUES ('2X2','T444',2);
  100. INSERT INTO soldvia VALUES ('4X4','T555',4);
  101. INSERT INTO soldvia VALUES ('5X5','T555',2);
  102. INSERT INTO soldvia VALUES ('6X6','T555',1);
  103. -- delete the tables
  104. DROP TABLE soldvia;
  105. DROP TABLE salestransaction;
  106. DROP TABLE store;
  107. DROP TABLE product;
  108. DROP TABLE vendor;
  109. DROP TABLE region;
  110. DROP TABLE category;
  111. DROP TABLE customer;
  112. -- queries
  113. -- 1
  114. SELECT productid, productname, productprice, vendorid, categoryid
  115. FROM product;
  116. -- 1a
  117. SELECT * FROM product;
  118. -- 2
  119. SELECT productname, productid, vendorid, categoryid, productprice
  120. FROM product;
  121. -- 3
  122. SELECT productid,productprice
  123. FROM product;
  124. -- 3a
  125. SELECT productid, productprice, productprice * 1.1
  126. FROM product;
  127. -- 4
  128. -- ERROR EXECUTING
  129. SELECT productid, productname, vendorid, productprice
  130. FROM product;
  131. WHERE productprice > 100;
  132. -- 5
  133. SELECT productid, productname, vendorid, productprice
  134. FROM product;
  135. WHERE productprice <= 100 AND categoryid = 'FW';
  136. -- 6
  137. SELECT vendorid
  138. FROM product;
  139. -- 7
  140. SELECT DISTINCT vendorid
  141. FROM product;
  142. -- 8
  143. SELECT productid, productname, categoryid, productprice
  144. FROM product;
  145. WHERE categoryid = 'FW'
  146. ORDER BY productprice;
  147. -- 9
  148. SELECT productid, productname, categoryid, productprice
  149. FROM product;
  150. WHERE categoryid = 'FW'
  151. ORDER BY productprice DESC;
  152. -- 10
  153. SELECT productid, productname, categoryid, productprice
  154. FROM product;
  155. ORDER BY categoryid, price;
  156. -- 11
  157. SELECT *
  158. FROM product;
  159. WHERE productname LIKE '%Boot%';
  160. -- 12
  161. SELECT AVG(productprice)
  162. FROM product;
  163. -- 13
  164. SELECT COUNT(*)
  165. FROM product;
  166. -- 14
  167. SELECT COUNT(DISTINCT vendorid)
  168. FROM product;
  169. -- 15
  170. SELECT COUNT(*), AVG(productprice), MIN(productprice), MAX(productprice)
  171. FROM product
  172. WHERE categoryid = 'CP';
  173. -- 16
  174. SELECT vendorid, COUNT(*), AVG(productprice)
  175. FROM product
  176. GROUP BY vendorid;
  177. -- 17
  178. SELECT COUNT(*), AVG(productprice)
  179. FROM product
  180. GROUP BY vendorid;
  181. -- 18
  182. SELECT vendorid, COUNT(*)
  183. FROM product
  184. WHERE productprice >= 100
  185. GROUP BY vendorid;
  186. -- 19
  187. SELECT vendorid, categoryid, COUNT(*), AVG(productprice)
  188. FROM product
  189. GROUP BY vendorid, categoryid;
  190. -- 20
  191. SELECT productd, SUM(noofitems)
  192. FROM soldvia
  193. GROUP BY productid;
  194. -- 21
  195. SELECT productid, COUNT(tid)
  196. FROM soldvia
  197. GROUP BY productid;
  198. -- 22
  199. SELECT vendorid, categoryid, COUNT(*), AVG(productprice)
  200. FROM product
  201. GROUP BY vendorid, categoryid
  202. HAVING COUNT(*) > 1;
  203. -- 23
  204. SELECT vendorid, categoryid, COUNT(*), AVG(productprice)
  205. FROM product
  206. WHERE productprice >= 50
  207. GROUP BY vendorid, categoryid
  208. HAVING COUNT(*) > 1;
  209. -- 24
  210. SELECT productid, SUM(noofitems)
  211. FROM soldvia
  212. GROUP BY productid;
  213. HAVING SUM(noofitems) > 3;
  214. -- 25
  215. SELECT productid, COUNT(tid)
  216. FROM soldvia
  217. GROUP BY productid
  218. HAVING COUNT(tid) > 1;
  219. -- 26
  220. SELECT productid
  221. FROM soldvia
  222. GROUP BY productid
  223. HAVING SUM(noofitems) > 3;
  224. -- 27
  225. SELECT productid
  226. FROM soldvia
  227. GROUP BY productid
  228. HAVING COUNT(tid) > 1;
  229. -- 28
  230. SELECT productid, productname, productprice
  231. FROM product
  232. WHERE productprice < (
  233. SELECT AVG(productprice)
  234. FROM product
  235. );
  236. -- 29
  237. SELECT productid, productname, productprice
  238. FROM product
  239. WHERE productid IN (
  240. SELECT productid
  241. FROM soldvia
  242. GROUP BY productid
  243. HAVING SUM(noofitems) > 3
  244. );
  245. -- 30
  246. SELECT productid, productname, productprice
  247. FROM productd
  248. WHERE productid IN (
  249. SELECT productid
  250. FROM soldvia
  251. GROUP BY productid
  252. HAVING COUNT(tid) > 1
  253. );
  254. -- 31
  255. SELECT productid, productname, vendorname, productprice
  256. FROM product, vendor
  257. WHERE product.vendorid = vendorid;
  258. -- 31a
  259. SELECT p.productid, p.productname, v.vendorname, p.productprice
  260. FROM product p, vendor v
  261. WHERE p.vendorid = v.vendorid
  262. -- 31b
  263. SELECT p.productid pid, p.productname pname, v.vendorname vname, p.productprice pprice
  264. FROM product p, vendor v
  265. WHERE p.vendorid = v.vendorid;
  266. -- 31c
  267. SELECT p.productid AS pid, p.productname AS pname, v.vendorname AS vname, p.productprice AS pprice
  268. FROM product p, vendor v
  269. WHERE p.vendorid = v.vendorid;
  270. -- 32
  271. SELECT productid, productname, vendorname, productprice
  272. FROM product, vendor
  273. -- 33
  274. SELECT *
  275. FROM product, vendor;
  276. -- 34
  277. SELECT *
  278. FROM product, vendor
  279. WHERE product.vendorid = vendor.vendorid;
  280. -- 35
  281. SELECT t.tid, t.tdate, p.productname, sv.noofitems AS quantity, (sv.noofitems * p.productprice) AS amount
  282. FROM product p, salestransaction t, soldvia sv
  283. WHERE sv.productid = p.productid AND sv.tid = t.tid
  284. ORDER BY t.tid;
  285. -- Alter Statement 1
  286. ALTER TABLE vendor ADD (
  287. vendorphonenumber CHAR(11)
  288. );
  289. -- Alter Statement 2
  290. ALTER TABLE vendor DROP (
  291. vendorphonenumber
  292. );
  293. -- Insert Statement 1
  294. INSERT INTO product VALUES (
  295. '7X7', 'Airy Sock', '1000', 'MK', 'CP'
  296. );
  297. -- Update Statement 1
  298. UPDATE product
  299. SET productprice = 10
  300. WHERE productid = '7X7';
  301. -- Alter Statement 3
  302. ALTER TABLE product ADD (
  303. discount CHAR(11)
  304. );
  305. -- Update Statement 2
  306. UPDATE product
  307. SET discount = 0.2;
  308. -- Update Statement 3
  309. UPDATE product
  310. SET discount = 0.3
  311. WHERE vendorid = 'MK';
  312. -- Alter Statement 4
  313. ALTER TABLE product DROP (
  314. discount
  315. );
  316. -- Delete Statement 1
  317. DELETE FROM product
  318. WHERE productid = '7X7';
  319. -- Create View Statement 1
  320. -- first line may be broken
  321. CREATE VIEW products_more_than_3_sold AS
  322. SELECT productid, productname, productprice
  323. FROM product
  324. WHERE productid IN (
  325. SELECT productid
  326. FROM soldvia
  327. GROUP BY productid
  328. HAVING SUM(noofitems) > 3
  329. );
  330. -- Query 29a
  331. SELECT *
  332. FROM products_more_than_3_sold;
  333. -- Create View Statement 2
  334. -- first line may be broken
  335. CREATE VIEW products_in_multiple_trnsc AS
  336. SELECT productid, productname, productprice
  337. FROM product
  338. WHERE productid in (
  339. SELECT productid
  340. FROM soldvia
  341. GROUP BY productid
  342. HAVING COUNT(tid) > 1
  343. );
  344. -- Query 30a
  345. SELECT *
  346. FROM products_in_multiple_trnsc;
  347. -- Drop View Statement 1
  348. DROP VIEW products_more_than_3_sold;
  349. -- Drop View Statement 2
  350. DROP VIEW products_in_multiple_trnsc;
  351. -- Query 36
  352. SELECT *
  353. FROM products_more_than_3_sold
  354. UNION
  355. SELECT *
  356. FROM products_in_multiple_trnsc
  357. -- Query 37
  358. SELECT *
  359. FROM products_more_than_3_sold
  360. INNER JOIN products_in_multiple_trnsc
  361. USING (
  362. productid, productname, productprice
  363. );
  364. -- Query 38
  365. SELECT DISTINCT *
  366. FROM products_more_than_3_sold
  367. WHERE (
  368. productid, productname, productprice
  369. ) NOT IN (
  370. SELECT *
  371. FROM products_in_multiple_trnsc
  372. );
  373. -- Alternative Query 38
  374. SELECT DISTINCT *
  375. FROM products_more_than_3_sold
  376. LEFT JOIN products_in_multiple_trnsc
  377. USING (
  378. productid, productname, productprice
  379. )
  380. WHERE products_in_multiple_trnsc.productid IS NULL;