API tools faq
paste
Guest User

14/10

a guest
Oct 14th, 2016
35
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.13 KB | None | 0 0
raw download clone embed print report
  1. marina=# select * sailor;
  2. ERROR: syntax error at or near "sailor"
  3. LINE 1: select * sailor;
  4. ^
  5. marina=# select * from sailor;
  6. sid | sname | semail | dbirth | slevel
  7. -----+----------------+-------------------+------------+--------
  8. 10 | João da Silva | joao@gmail.com | 1990-10-10 | 20
  9. 20 | Maria da Silva | maria@gmail.com | 1987-08-10 | 10
  10. 30 | Pedro Antonio | pedro@gmail.com | 1977-11-20 | 30
  11. 40 | Alberto Dumont | alberto@gmail.com | 1969-01-05 | 40
  12. 50 | Roberto Silva | roberto@gmail.com | 1981-01-13 | 20
  13. 60 | Carla Carl | carla@gmail.com | 1988-09-22 | 20
  14. (6 rows)
  15.  
  16. marina=# select * from tplevel;
  17. lid | ldsc
  18. -----+---------
  19. 10 | Amateur
  20. 20 | Junior
  21. 30 | Master
  22. 40 | Senior
  23. (4 rows)
  24.  
  25. marina=# select count(*) from tplevel where lid = sid;
  26. ERROR: column "sid" does not exist
  27. LINE 1: select count(*) from tplevel where lid = sid;
  28. ^
  29. marina=# select count(*) from tplevel,sailor where lid = sid;
  30. count
  31. -------
  32. 4
  33. (1 row)
  34.  
  35. marina=# select ldsc,count(*) from tplevel,sailor where lid = sid;
  36. ERROR: column "tplevel.ldsc" must appear in the GROUP BY clause or be used in an aggregate function
  37. LINE 1: select ldsc,count(*) from tplevel,sailor where lid = sid;
  38. ^
  39. marina=#
  40.  
  41.  
  42.  
  43.  
  44.  
  45.  
  46.  
  47.  
  48.  
  49.  
  50.  
  51.  
  52.  
  53.  
  54.  
  55.  
  56.  
  57.  
  58.  
  59.  
  60.  
  61.  
  62.  
  63.  
  64. marina=# select ldsc,count(*) from tplevel,sailor where lid = sid;
  65. ERROR: column "tplevel.ldsc" must appear in the GROUP BY clause or be used in an aggregate function
  66. LINE 1: select ldsc,count(*) from tplevel,sailor where lid = sid;
  67. ^
  68. marina=# select ldsc,count(*) from tplevel,sailor group by tbid;
  69. ERROR: column "tbid" does not exist
  70. LINE 1: select ldsc,count(*) from tplevel,sailor group by tbid;
  71. ^
  72. marina=# select ldsc,count(*) from tplevel,sailor group by lid;
  73. ldsc | count
  74. ---------+-------
  75. Junior | 6
  76. Amateur | 6
  77. Senior | 6
  78. Master | 6
  79. (4 rows)
  80.  
  81. marina=# select * from sailor;
  82. sid | sname | semail | dbirth | slevel
  83. -----+----------------+-------------------+------------+--------
  84. 10 | João da Silva | joao@gmail.com | 1990-10-10 | 20
  85. 20 | Maria da Silva | maria@gmail.com | 1987-08-10 | 10
  86. 30 | Pedro Antonio | pedro@gmail.com | 1977-11-20 | 30
  87. 40 | Alberto Dumont | alberto@gmail.com | 1969-01-05 | 40
  88. 50 | Roberto Silva | roberto@gmail.com | 1981-01-13 | 20
  89. 60 | Carla Carl | carla@gmail.com | 1988-09-22 | 20
  90. (6 rows)
  91.  
  92. marina=# select ldsc,count(*) from tplevel,sailor group by lid;
  93. ldsc | count
  94. ---------+-------
  95. Junior | 6
  96. Amateur | 6
  97. Senior | 6
  98. Master | 6
  99. (4 rows)
  100.  
  101. marina=#
  102.  
  103.  
  104.  
  105.  
  106.  
  107.  
  108.  
  109.  
  110.  
  111.  
  112.  
  113.  
  114.  
  115.  
  116.  
  117.  
  118.  
  119.  
  120.  
  121.  
  122.  
  123.  
  124.  
  125.  
  126.  
  127. marina=# select ldsc,count(*) from tplevel,sailor group by lid;
  128. ldsc | count
  129. ---------+-------
  130. Junior | 6
  131. Amateur | 6
  132. Senior | 6
  133. Master | 6
  134. (4 rows)
  135.  
  136. marina=# select ldsc,count(*) from tplevel,sailor group by lid;
  137. ldsc | count
  138. ---------+-------
  139. Junior | 6
  140. Amateur | 6
  141. Senior | 6
  142. Master | 6
  143. (4 rows)
  144.  
  145. marina=# select * from sailor.
  146. marina-# ;
  147. ERROR: syntax error at or near ";"
  148. LINE 2: ;
  149. ^
  150. marina=# select * from sailor;
  151. sid | sname | semail | dbirth | slevel
  152. -----+----------------+-------------------+------------+--------
  153. 10 | João da Silva | joao@gmail.com | 1990-10-10 | 20
  154. 20 | Maria da Silva | maria@gmail.com | 1987-08-10 | 10
  155. 30 | Pedro Antonio | pedro@gmail.com | 1977-11-20 | 30
  156. 40 | Alberto Dumont | alberto@gmail.com | 1969-01-05 | 40
  157. 50 | Roberto Silva | roberto@gmail.com | 1981-01-13 | 20
  158. 60 | Carla Carl | carla@gmail.com | 1988-09-22 | 20
  159. (6 rows)
  160.  
  161. marina=# select ldsc,count(*) from tplevel,sailor group by sid;
  162. ERROR: column "tplevel.ldsc" must appear in the GROUP BY clause or be used in an aggregate function
  163. LINE 1: select ldsc,count(*) from tplevel,sailor group by sid;
  164. ^
  165. marina=# select ldsc,count(*) from tplevel,sailor group by slevel;
  166. ERROR: column "tplevel.ldsc" must appear in the GROUP BY clause or be used in an aggregate function
  167. LINE 1: select ldsc,count(*) from tplevel,sailor group by slevel;
  168. ^
  169. marina=# select ldsc,count(*) from tplevel,sailor group by slevel,ldsc;
  170. ldsc | count
  171. ---------+-------
  172. Amateur | 1
  173. Junior | 1
  174. Master | 1
  175. Senior | 1
  176. Amateur | 3
  177. Junior | 3
  178. Master | 3
  179. Senior | 3
  180. Amateur | 1
  181. Junior | 1
  182. Master | 1
  183. Senior | 1
  184. Amateur | 1
  185. Junior | 1
  186. Master | 1
  187. Senior | 1
  188. (16 rows)
  189.  
  190. marina=# select ldsc,count(*) from tplevel,sailor group by ldsc;
  191. ldsc | count
  192. ---------+-------
  193. Senior | 6
  194. Junior | 6
  195. Master | 6
  196. Amateur | 6
  197. (4 rows)
  198.  
  199. marina=# select ldsc,count(*) from tplevel,sailor group by sid,ldsc;
  200. ldsc | count
  201. ---------+-------
  202. Amateur | 1
  203. Junior | 1
  204. Master | 1
  205. Senior | 1
  206. Amateur | 1
  207. Junior | 1
  208. Master | 1
  209. Senior | 1
  210. Amateur | 1
  211. Junior | 1
  212. Master | 1
  213. Senior | 1
  214. Amateur | 1
  215. Junior | 1
  216. Master | 1
  217. Senior | 1
  218. Amateur | 1
  219. Junior | 1
  220. Master | 1
  221. Senior | 1
  222. Amateur | 1
  223. Junior | 1
  224. Master | 1
  225. Senior | 1
  226. (24 rows)
  227.  
  228. marina=# select ldsc,count(*) from tplevel,sailor group by slevel,ldsc;
  229. ldsc | count
  230. ---------+-------
  231. Amateur | 1
  232. Junior | 1
  233. Master | 1
  234. Senior | 1
  235. Amateur | 3
  236. Junior | 3
  237. Master | 3
  238. Senior | 3
  239. Amateur | 1
  240. Junior | 1
  241. Master | 1
  242. Senior | 1
  243. Amateur | 1
  244. Junior | 1
  245. Master | 1
  246. Senior | 1
  247. (16 rows)
  248.  
  249. marina=# select ldsc,count(slvel) from tplevel,sailor group by slevel,ldsc;
  250. ERROR: column "slvel" does not exist
  251. LINE 1: select ldsc,count(slvel) from tplevel,sailor group by slevel...
  252. ^
  253. marina=# select ldsc,count(slevel) from tplevel,sailor group by slevel,ldsc;
  254. ldsc | count
  255. ---------+-------
  256. Amateur | 1
  257. Junior | 1
  258. Master | 1
  259. Senior | 1
  260. Amateur | 3
  261. Junior | 3
  262. Master | 3
  263. Senior | 3
  264. Amateur | 1
  265. Junior | 1
  266. Master | 1
  267. Senior | 1
  268. Amateur | 1
  269. Junior | 1
  270. Master | 1
  271. Senior | 1
  272. (16 rows)
  273.  
  274. marina=# select ldsc,count(slevel) from tplevel,sailor group by ldsc;
  275. ldsc | count
  276. ---------+-------
  277. Senior | 6
  278. Junior | 6
  279. Master | 6
  280. Amateur | 6
  281. (4 rows)
  282.  
  283. marina=# select ldsc,count(sid]) from tplevel,sailor group by ldsc;
  284. ERROR: syntax error at or near "]"
  285. LINE 1: select ldsc,count(sid]) from tplevel,sailor group by ldsc;
  286. ^
  287. marina=# select ldsc,count(sid) from tplevel,sailor group by ldsc;
  288. ldsc | count
  289. ---------+-------
  290. Senior | 6
  291. Junior | 6
  292. Master | 6
  293. Amateur | 6
  294. (4 rows)
  295.  
  296. marina=# select ldsc,count(sid) from tplevel join sailor on lid = slevel group by ldsc;
  297. ldsc | count
  298. ---------+-------
  299. Senior | 1
  300. Junior | 3
  301. Master | 1
  302. Amateur | 1
  303. (4 rows)
  304.  
  305. marina=#
  306. marina=# select * from slevel;
  307. ERROR: relation "slevel" does not exist
  308. LINE 1: select * from slevel;
  309. ^
  310. marina=# select * from tplevel;
  311. lid | ldsc
  312. -----+---------
  313. 10 | Amateur
  314. 20 | Junior
  315. 30 | Master
  316. 40 | Senior
  317. (4 rows)
  318.  
  319. marina=# select * from reserve;
  320. sid | bid | dtimep | dtimer
  321. -----+-----+---------------------+---------------------
  322. 10 | 10 | 2015-09-10 08:00:00 | 2015-09-10 18:00:00
  323. 10 | 30 | 2015-07-14 08:00:00 | 2015-07-14 21:00:00
  324. 20 | 30 | 2014-12-11 12:00:00 | 2014-12-12 12:00:00
  325. 20 | 40 | 2015-02-06 08:00:00 | 2015-02-06 19:00:00
  326. 30 | 20 | 2011-08-03 08:00:00 | 2011-08-05 18:00:00
  327. 60 | 30 | 2016-10-05 08:00:00 |
  328. 50 | 20 | 2016-10-05 08:30:00 |
  329. (7 rows)
  330.  
  331. marina=# select sname,ldsc,count(sid) from sailor,tplevel,reserve;
  332. ERROR: column reference "sid" is ambiguous
  333. LINE 1: select sname,ldsc,count(sid) from sailor,tplevel,reserve;
  334. ^
  335. marina=# select ldsc,count(sid) from tplevel join sailor on lid = slevel group by ldsc;
  336. ldsc | count
  337. ---------+-------
  338. Senior | 1
  339. Junior | 3
  340. Master | 1
  341. Amateur | 1
  342. (4 rows)
  343.  
  344. marina=# select sname,ldsc,count(sid) join tplevel on lid = bid group by sid;
  345. ERROR: syntax error at or near "join"
  346. LINE 1: select sname,ldsc,count(sid) join tplevel on lid = bid group...
  347. ^
  348. marina=# select avg(bhorses) from boat natural join tpboat where tpdsc = 'Schooner';
  349. ERROR: column "tpdsc" does not exist
  350. LINE 1: ... avg(bhorses) from boat natural join tpboat where tpdsc = 'S...
  351. ^
  352. marina=# select avg(bhorses) from boat natural join tpboat where tdsc = 'Schooner';
  353. ERROR: column "tdsc" does not exist
  354. LINE 1: ... avg(bhorses) from boat natural join tpboat where tdsc = 'Sc...
  355. ^
  356. marina=# select avg(bhorses) from boat natural join tpboat where ldsc = 'Schooner';
  357. ERROR: column "ldsc" does not exist
  358. LINE 1: ... avg(bhorses) from boat natural join tpboat where ldsc = 'Sc...
  359. ^
  360. marina=# select * from boat;
  361. bid | bname | blen | bhorses | byear | bweight | bnsail | tbid
  362. -----+-------------+------+---------+-------+---------+--------+------
  363. 10 | Black Shark | 30 | 120 | 2010 | 80 | 3 | 10
  364. 20 | Arrow | 20 | 90 | 2012 | 60 | 1 | 20
  365. 30 | White Swan | 150 | 140 | 2008 | 120 | 6 | 40
  366. 40 | Small | 10 | 0 | 2014 | 20 | 1 | 20
  367. (4 rows)
  368.  
  369. marina=# select * from tpboat;
  370. tbid | tbdsc
  371. ------+----------
  372. 10 | Schooner
  373. 20 | Laser
  374. 30 | Catboat
  375. 40 | Scuna
  376. 50 | WindSurf
  377. 60 | Class 10
  378. (6 rows)
  379.  
  380. marina=# select avg(bhorses) from boat natural join tpboat where tbdsc = 'Schooner;
  381. marina'# ;
  382. marina'# select * from tpboat^C
  383. marina=# select avg(bhorses) from boat natural join tpboat where tbdsc = 'Schooner';
  384. avg
  385. ----------------------
  386. 120.0000000000000000
  387. (1 row)
  388.  
  389. marina=# sekect sname,count(*) from sailor natural join reserve group by sname;
  390. ERROR: syntax error at or near "sekect"
  391. LINE 1: sekect sname,count(*) from sailor natural join reserve group...
  392. ^
  393. marina=# select sname,count(*) from sailor natural join reserve group by sname;
  394. sname | count
  395. ----------------+-------
  396. Maria da Silva | 2
  397. Carla Carl | 1
  398. Pedro Antonio | 1
  399. Roberto Silva | 1
  400. João da Silva | 2
  401. (5 rows)
  402.  
  403. marina=# select sname from sailor natural join reserve;
  404. sname
  405. ----------------
  406. João da Silva
  407. João da Silva
  408. Maria da Silva
  409. Maria da Silva
  410. Pedro Antonio
  411. Carla Carl
  412. Roberto Silva
  413. (7 rows)
  414.  
  415. marina=# select sname from sailor natural join reserve group by sname having count(*) > 10;
  416. sname
  417. -------
  418. (0 rows)
  419.  
  420. marina=# select sname from sailor natural join reserve group by sname having count(*) < 10;
  421. sname
  422. ----------------
  423. Maria da Silva
  424. Carla Carl
  425. Pedro Antonio
  426. Roberto Silva
  427. João da Silva
  428. (5 rows)
  429.  
  430. marina=# select sname from sailor natural join reserve group by sname having count(*) < 10;
  431. sname
  432. ----------------
  433. Maria da Silva
  434. Carla Carl
  435. Pedro Antonio
  436. Roberto Silva
  437. João da Silva
  438. (5 rows)
  439.  
  440. marina=# select * from boat where bhorses > avg(bhorses);
  441. ERROR: aggregates not allowed in WHERE clause
  442. LINE 1: select * from boat where bhorses > avg(bhorses);
  443. ^
  444. marina=# select * from boat having bhorses > avg(bhorses);
  445. ERROR: column "boat.bid" must appear in the GROUP BY clause or be used in an aggregate function
  446. LINE 1: select * from boat having bhorses > avg(bhorses);
  447. ^
  448. marina=#
  449. marina=# select * from boat bhorses > (select avg(bhorses) from boat);
  450. ERROR: syntax error at or near ">"
  451. LINE 1: select * from boat bhorses > (select avg(bhorses) from boat...
  452. ^
  453. marina=# select * from boat bhorses = (select avg(bhorses) from boat);
  454. ERROR: syntax error at or near "="
  455. LINE 1: select * from boat bhorses = (select avg(bhorses) from boat...
  456. ^
  457. marina=# select * from boat where bhorses > (select avg(bhorses) from boat);
  458. bid | bname | blen | bhorses | byear | bweight | bnsail | tbid
  459. -----+-------------+------+---------+-------+---------+--------+------
  460. 10 | Black Shark | 30 | 120 | 2010 | 80 | 3 | 10
  461. 20 | Arrow | 20 | 90 | 2012 | 60 | 1 | 20
  462. 30 | White Swan | 150 | 140 | 2008 | 120 | 6 | 40
  463. (3 rows)
  464.  
  465. marina=# select avg(bhorses) from boat;
  466. avg
  467. ---------------------
  468. 87.5000000000000000
  469. (1 row)
  470.  
  471. marina=# ^C
  472. marina=#
Add Comment
Please, Sign In to add comment
Public Pastes
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!