· 7 years ago · Oct 29, 2018, 07:22 AM
1SELECT 5; -- intoarce o inreg cu val.
2SELECT 5 AS a1; --intoarcce o inreg cu un camp a1 cu val 5
3SELECT 5::text AS a1; -- intoarce sirul de caractere 5
4SELECT 5::int AS a1; -- 5 ca intreg
5SELECT 'UN text' AS a1; -- un sir de caractere
6SELECT 'Un text' AS "PRIma coloana"; -- un sir de caractere pentru un camp cu denumire oarecare
7SELECT 5+4; -- evalueaza expresia si intoarce rezultatul
8SELECT 8%3;
9SELECT '5+4='||(5+4); -- rezultatul de tip int este concatenat cu un sir de caractere
10SELECT 5.4*pow(5,200); -- evalueaza expresia
11SELECT 3^9;
12SELECT CURRENT_USER; -- utilizatorul curent
13SELECT CURRENT_DATE; -- data curenta
14SELECT CURRENT_DATE+2; -- adauga doua zile la data curenta
15SELECT CURRENT_DATE+240; -- adauga 240 de zile la data curenta
16SELECT CURRENT_DATE-300;-- scade 300 de zile
17SELECT '2014-12-10'::date+300;
18SELECT CURRENT_DATE-'2015-01-01'::date; --> nr zile
19SELECT CURRENT_TIME;
20SELECT CURRENT_TIME-'2:2:10'::time;-- operatii cu ore/min/sec
21SELECT CURRENT_DATE - 20+CURRENT_TIME;
22SELECT CURRENT_DATE - 20+CURRENT_TIME-'3:20:0'::time;
23 -- comentariu linie
24/*
25comentariu bloc */
26SELECT 3<<2 AS a1;
27CREATE TABLE c1.t1(a1 serial PRIMARY KEY, a2 int, a3 varchar(20));
28CREATE SCHEMA c1;
29seleCT * from c1.t1;
30INSERT INTO c1.t1(a2,a3) VALUES(12,'A11'),(42,'B41'),(32,'A21'),(10,'A13');
31SELECT * FROM c1.t1;
32SELECT * FROM c1.t1 WHERE a3 LIKE 'A2%'; -- % inlocuieste un sir de caractere ce incepe cu A2
33SELECT * FROM c1.t1 WHERE a3 LIKE '%A';
34SELECT * FROM c1.t1 WHERE a3 ILIKE 'a%';--insensitive like
35SELECT * FROM c1.t1 WHERE a3 ILIKE '%2%'; -- ce contine 2
36-- FUNCTII DE AGREGARE
37SELECT COUNT(*) FROM c1.t1; -- nr. de inregistrari
38SELECT SUM(a2) FROM c1.t1; -- suma totala pe a2
39SELECT MAX(a2) FROM c1.t1;
40SELECT AVG(a2) FROM c1.t1;
41SELECT string_agg(a3,';') FROM c1.t1;
42--EX2:
43CREATE TABLE c1.clase(clasa varchar(20), nume_elev varchar(50),data_n date,
44 CONSTRAINT k_clase PRIMARY KEY(clasa,nume_elev));
45SELECT * FROM c1.clase;
46-- cheia primara presupune ca valorile concatenate pe fiecare rand din cadrul cheii primare sunt unice la nivel de tabel - sunt interzise valorile NULL
47INSERT INTO c1.clase(clasa, nume_elev, data_n) VALUES
48('5A','Vasile','2001-01-31'), ('5A','Ilie','2002-01-31'),('5B','Ilie','2001-03-11'),
49('6A','Vlad','2000-01-30'), ('5B','Ion','2001-03-31'),('6A','Geo','2000-10-11'),
50('6A','Vasile','2000-1-1'), ('6A','Ilie','2002-01-31'),('6B','Stan','2000-05-11');
51-- afisarea numarului total de elevi
52SELECT COUNT(*) FROM c1.clase;
53--afisare varsta minima pe fiecare clasa
54SELECT clasa, MIN(data_n) FROM c1.clase GROUP BY clasa;
55--afisare varsta minima pe fiecare clasa ptr. elevii a caror nume incepe cu I
56SELECT clasa, MIN(data_n)
57 FROM c1.clase
58 WHERE nume_elev ILIKE 'i%'
59 GROUP BY clasa;
60 -- afiseaza clasele care au mai mult de doi elevi si a caror denumire de clasa se termina in A
61SELECT clasa, count(*) as nr
62 FROM c1.clase
63 WHERE clasa ILIKE '%a'
64 GROUP BY clasa
65 HAVING COUNT(*)>2;
66 --afiseaza numele celor mai mari elevi din fiecare clasa
67--CE APARE IN GROUP VA APARE SI IN SELECT
68-- SE EXECUTA mai intai filtrarea prin WHERE - la nivel de inregistrare si apoi
69-- filtrarea prin HAVING la nivel de GRUP
70
71 SELECT clase.clasa, clase.nume_elev,clase.data_n
72 FROM c1.clase,
73 (SELECT clasa, MIN(data_n) AS cls_dm
74 FROM c1.clase
75 GROUP BY clasa
76 )par
77 WHERE par.cls_dm=clase.data_n AND par.clasa=clase.clasa;
78 --inserare cu valori NULL
79-- inserare cu val NULL ptr. cheie primara nu este permis
80 INSERT INTO c1.clase(clasa, nume_elev, data_n) VALUES ('5A','Georgescu',NULL);
81 SELECT * FROM c1.clase;
82 SELECT * FROM c1.clase WHERE data_n IS NULL;
83 SELECT * FROM c1.clase WHERE data_n IS NOT NULL;
84 --eroare
85 INSERT INTO c1.clase(clasa, nume_elev, data_n) VALUES ('5A',NULL,'2000-01-01');
86 --permis
87 INSERT INTO c1.clase(clasa, nume_elev, data_n) VALUES ('5A','','2000-01-01');-
88 -- DIFERENTA INTRE NULL si EMPTY (doar ptr. date de tip varchar/text)
89DROP TABLE IF EXISTS c1.t2;CREATE TABLE c1.t2(a1 serial PRIMARY KEY, a2 varchar(10));
90--3 valori <>0, 2 empty, 1 NULL
91INSERT INTO c1.t2(a2) VALUES ('A11'),(''),(''),('A22'),(NULL),('A31');
92SELECT * FROM c1.t2;
93SELECT * FROM c1.t2 WHERE a2 IS NOT NULL;
94SELECT * FROM c1.t2 WHERE a2 IS NULL;
95SELECT * FROM c1.t2 WHERE a2='';
96SELECT COUNT(a2) FROM c1.t2; -- cele nule NU se contorizeaza
97
98-- NULL NU intra in functiile de agregare
99SELECT * from c1.clase;
100SELECT * from c1.clase ORDER BY data_n;
101SELECT * from c1.clase ORDER BY data_n DESC; -- sordtare - descrescator
102SELECT * from c1.clase ORDER BY data_n DESC, nume_elev ASC;