Extras din laborator
Tabela A
a1 a2 a3 a4 a5 a6
Tabela B
b1 b2 b3 b4 b5 a1
Tabela C
c1 c2 c3 c4 C5 a1
SELECT [domeniu: ALL/DISTINCT/DISTINCTROW] lista selectie câmpuri
FROM nume tabela(e)
[WHERE criteriu de selectie]
[ORDER BY lista câmpuri criterii de ordonare {ASC/DESC}];
Exemple:
SELECT DISTINCTROW a1,a2
FROM A
SELECT DISTINCT a5
FROM A
WHERE a5>1000;
SELECT DISTINCT a5
FROM A
WHERE a5>1000 AND a3=”text”;
SELECT DISTINCT a5
FROM A
WHERE a5 (NOT) BETWEEN 1000 AND 10000;
SELECT DISTINCT a5
FROM A
WHERE a3 LIKE ”*text”;
SELECT a2,a4,a5
FROM A
WHERE a2 IN(1500, 13000, 14000)
ORDER BY a2 ASC, a5 DESC;
SELECT DISTINCTROW Max(a5) AS [a5_maxim], Min(a5) AS [a5_minim], Avg(a5) AS [a5_medie]
FROM A
SELECT COUNT(*) AS [Numar de tupluri]
FROM A
SELECT a1, a2, a3, a5, a6, IIF(a5>=a6;a5-a6;a6-a5) AS [functie conditionala]
FROM A
WHERE a2 IS NOT NULL;
Interogari de grupare
SELECT [domeniu: ALL/DISTINCT/DISTINCTROW] lista selectie câmpuri
FROM nume tabela(e)
[WHERE criteriu de selectie]
[GROUP BY câmp(uri) de grupare]
[HAVING criteriul câmpului de grupare]
[ORDER BY lista câmpuri criterii de ordonare {ASC/DESC}];
SELECT b1, b2, b4, b5, b3*b4 AS [produs b3 si b4]
FROM B
WHERE b2=”ctiteriu text”
GROUP BY b4
HAVING Sum(b3)>300000;
Interogari asociative clasice
SELECT [domeniu] lista selectie câmpuri din tabele diferite
FROM nume tabele
[WHERE criteriu de compunere
[ORDER BY lista câmpuri criterii de ordonare {ASC/DESC}];
SELECT A.a1, A.a2, B.b1, C.c1, C.c3
FROM A, B, C
WHERE A.a1=B.a1 AND B.a1=C.a1
ORDER BY C.c3;
Compuneri interne
SELECT [domeniu] lista selectie câmpuri din tabele diferite
FROM nume tabela_1
{INNER/LEFT/RIGHT} JOIN nume tabela_2
ON criteriu de asociere
[WHERE criteriu de compunere
[ORDER BY lista câmpuri criterii de ordonare {ASC/DESC}];
SELECT A.a5, B.b3, (A.a5*B.B3) AS [Produs]
FROM A
INNER JOIN B ON A.A1=B.A1
ORDER BY A.a5;
SELECT A.a1, A.a2, A.a5, B.b1, C.c1
FROM A
INNER JOIN (B INNER JOIN C ON B.a1=C.a1) ON
(A.a1=B.a1) AND (B.a10C.a1);
Preview document
Conținut arhivă zip
- Comenzi SQL de Selectie.doc