SQL vol. 1Prezentare generalCrearea tabelelorDeclararea restriciilorActualizarea tabelelor

SQL Structured Query LanguageEste cel mai rspndit limbaj de lucru cu bazele de dateBazat pe algebra relaionalFolosit de:Administratorii BD: crearea tabelelor, declararearea restriciilorDezvoltatorii de aplicaii (script-uri, proceduri, funcii etc.)Neinformaticieni extragerea de informaii ad-hoc, din bazele de date

Organisme de standardizare SQLISO International Organisation for Standardization, ANSI American National Standard Institute, AFNOR Association Francaise de Normalisation, OMG Operational Management Committee, DBSSG Database Systems Study Group, PRIS-TG Predictable Real-time Information Management Task Group

SQL

ODP

OIM

IRDS

Grupuri pe activiti

Comitete tehnice

i grupuri de studiu

Comitetul pentru Managementul Operaional

Sisteme de prelucrare a informaiilor

(Frana)

Comitetul de

stardarde acreditate

Naional

Internaional

EMBED OrgPlusWOPX.4

_1044538275.bin

Standarde SQLSQL1 sau SQL89

SQL2 sau SQL92 600 pagini- Entry intrare, de baz (SQL-89 corectat)- Intermediate intermediar- Full deplin.

SQL:1999 2000 pagini

SQL:2003

Orientri SQL:1999, SQL:2003Definirea i gestionarea obiectelor complexe i persistente:- generalizare i specializare- moteniri multiple- polimorfism- ncapsulare- tipuri de date definite de utilizator- expresii privind interogri recursive i instrumente adecvate de administrare a datelor.

SQL furnizeaza suport pentru:Definirea datelorConsultarea BDManipularea datelor din bazControlul accesuluiPartajarea bazei ntre mai muli utilizatori ai acesteiaMeninerea intergritii BD

Atuuri ale SQLIndependena de productorPortabilitate Este standardizatBazat pe modelul relaional Limbaj de nivel naltRspunsuri la nterogri simple, ad-hocSuport programatic pentru accesul la BDPermite multiple imagini asupra datelor bazeiPermite definirea dinamic a datelorSuport pentru arhitecturi client-server

Comenzi SQL

Cteva tipuri de date SQLSMALLINT: (4 poziii, reprezentate pe 16 bii),INTEGER sau INT: ntregi (9 poziii, 32 bii),NUMERIC(p,s) sau DECIMAL(p,s)FLOAT: virgul mobil (20 poziii ptr. mantis),CHAR(n) sau CHARACTER(n): (max. 240),VARCHAR(n) sau CHAR VARYING(n): ir de caractere de lungime variabil (max. 254),DATE: dat calendaristic,TIME: ora etc.,TIMESTAMP: an, lun, zi, or, minutul, secund, plus o fraciune de secund

Crearea tabelelor i declararea atributelorCREATE TABLE facturi (NrFact NUMERIC(8),DataFact DATE DEFAULT CURRENT_DATE,CodCl NUMERIC(6) DEFAULT 1001,Obs VARCHAR(50) )

Restricii - valori nenuleCREATE TABLE facturi (NrFact NUMERIC(8) NOT NULL,DataFact DATE DEFAULT CURRENT_DATE NOT NULL,CodCl NUMERIC (6) DEFAULT 1001 NOT NULL,Obs VARCHAR(50) )

Cheie primar/unicitate (1)CREATE TABLE facturi (NrFact NUMERIC(8) NOT NULL PRIMARY KEY,DataFact DATE DEFAULT CURRENT_DATE NOT NULL,CodCl NUMERIC(6) DEFAULT 1001 NOT NULL,Obs VARCHAR(50) ) ;CREATE TABLE judete (Jud CHAR(2) PRIMARY KEY,Judet VARCHAR(25) NOT NULL UNIQUE, Regiune VARCHAR (15) ) ;

Cheie primar/unicitate (2)CREATE TABLE liniifact (NrFact NUMERIC(8) NOT NULL,Linie SMALLINT NOT NULL, CodPr NUMERIC(6) NOT NULL,Cantitate NUMERIC(10) NOT NULL,PretUnit NUMBER (12), PRIMARY KEY (NrFact, Linie), UNIQUE (NrFact, CodPr) )

Restricii refereniale (1)CREATE TABLE liniifact (NrFact NUMERIC(8) NOT NULL,Linie SMALLINT NOT NULL, CodPr NUMERIC(6) NOT NULL,Cantitate NUMERIC(10) NOT NULL,PretUnit NUMBER (12), PRIMARY KEY (NrFact, Linie), UNIQUE (NrFact, CodPr),FOREIGN KEY NrFact REFERENCES facturi (NrFact),FOREIGN KEY CodPr REFERENCES produse (CodPr) )

Restricii refereniale (2)CREATE TABLE liniifact (NrFact NUMERIC(8) NOT NULL REFERENCES facturi (NrFact),Linie SMALLINT NOT NULL, CodPr NUMERIC(6) NOT NULL REFERENCE produse (CodPr),Cantitate NUMERIC(10) NOT NULL,PretUnit NUMBER (12), PRIMARY KEY (NrFact, Linie), UNIQUE (NrFact, CodPr) )

Restricii refereniale (3)CREATE TABLE liniifact (NrFact NUMERIC(8) NOT NULL,Linie SMALLINT NOT NULL, CodPr NUMERIC(6) NOT NULL,Cantitate NUMERIC(10) NOT NULL,PretUnit NUMBER (12), PRIMARY KEY (NrFact, Linie), UNIQUE (NrFact, CodPr),FOREIGN KEY NrFact REFERENCES facturi (NrFact)ON DELETE RESTRICT ON UPDATE CASCADE,FOREIGN KEY CodPr REFERENCES produse (CodPr)ON DELETE RESTRICT ON UPDATE CASCADE )

Restricii utilizator (1)CREATE TABLE facturi (NrFact NUMERIC(8) NOT NULL PRIMARY KEY,DataFact DATE DEFAULT CURRENT_DATE NOT NULLCHECK (DataFact >= DATE '2005-08-01' AND DataFact

Restricii utilizator (2)CREATE TABLE LINIIFACT (NrFact NUMERIC(8) NOT NULL,Linie SMALLINT NOT NULL CHECK (Linie > 0), CodPr NUMERIC(6) NOT NULL,Cantitate NUMERIC(10) NOT NULL,PretUnit NUMBER (12), PRIMARY KEY (NrFact, Linie), UNIQUE (NrFact, CodPr),FOREIGN KEY NrFact REFERENCES facturi (NrFact)ON DELETE RESTRICT ON UPDATE CASCADE,FOREIGN KEY CodPr REFERENCES produse (CodPr)ON DELETE RESTRICT ON UPDATE CASCADE )

Script PostgreSQL- creare tabele (1)DROP TABLE incasfact ;DROP TABLE incasari ;DROP TABLE liniifact ;DROP TABLE facturi ;DROP TABLE produse ;DROP TABLE persclienti ;DROP TABLE persoane ;DROP TABLE clienti ;DROP TABLE coduri_postale ;DROP TABLE judete ;

Script PostgreSQL- creare tabele (2)CREATE TABLE judete ( jud CHAR(2) CONSTRAINT pk_judete PRIMARY KEY CONSTRAINT ck_judCHECK (jud=LTRIM(UPPER(jud))), judet VARCHAR(25) CONSTRAINT un_judet UNIQUE CONSTRAINT nn_judet NOT NULL CONSTRAINT ck_judet CHECK (judet=LTRIM(INITCAP(judet))), regiune VARCHAR(15) DEFAULT 'Moldova' CONSTRAINT ck_regiune CHECK (regiune IN ('Banat', 'Transilvania', 'Dobrogea', 'Oltenia', 'Muntenia', 'Moldova')) ) ;

Script PostgreSQL- creare tabele (3)CREATE TABLE coduri_postale ( codpost CHAR(6) CONSTRAINT pk_cp PRIMARY KEY CONSTRAINT ck_codpostCHECK (codpost=LTRIM(codpost)), loc VARCHAR(25) CONSTRAINT nn_loc NOT NULL CONSTRAINT ck_loc CHECK (loc=LTRIM(INITCAP(loc))), jud CHAR(2) DEFAULT 'IS' CONSTRAINT fk_cp_jud REFERENCES judete(jud) ) ;

Script PostgreSQL- creare tabele (4)CREATE TABLE clienti ( codcl NUMBER(6) CONSTRAINT pk_clienti PRIMARY KEY CONSTRAINT ck_codcl CHECK (codcl > 1000), dencl VARCHAR (30) CONSTRAINT ck_dencl CHECK (SUBSTR(dencl,1,1) = UPPER(SUBSTR(dencl,1,1))), codfiscal CHAR(9) CONSTRAINT ck_codfiscal CHECK (SUBSTR(codfiscal,1,1) = UPPER(SUBSTR(codfiscal,1,1))), adresa VARCHAR(40) CONSTRAINT ck_adresa_clienti CHECK (SUBSTR(adresa,1,1) = UPPER(SUBSTR(adresa,1,1))), codpost CHAR(6) CONSTRAINT fk_clienti_cpREFERENCES coduri_postale(codpost), telefon VARCHAR2(10) ) ;

Script PostgreSQL- creare tabele (5)CREATE TABLE produse ( codpr NUMBER(6) CONSTRAINT pk_produse PRIMARY KEY CONSTRAINT ck_codpr CHECK (codpr > 0), denpr VARCHAR(30) CONSTRAINT ck_denpr CHECK (SUBSTR(denpr,1,1) = UPPER(SUBSTR(denpr,1,1))), um VARCHAR(10), grupa VARCHAR(15) CHECK (SUBSTR(grupa,1,1) = UPPER(SUBSTR(grupa,1,1))), procTVA NUMBER(2,2) DEFAULT .19 ) ;

Script PostgreSQL- creare tabele (6)CREATE TABLE facturi ( nrfact NUMBER(8) CONSTRAINT pk_facturi PRIMARY KEY, datafact DATE DEFAULT SYSDATE CONSTRAINT ck_datafact CHECK (datafact >= TO_DATE('01/08/2005','DD/MM/YYYY') AND datafact

Script PostgreSQL- creare tabele (7)CREATE TABLE liniifact ( nrfact NUMBER(8) CONSTRAINT fk_liniifact_facturi REFERENCES facturi(nrfact), linie NUMBER(2) CONSTRAINT ck_linie CHECK (linie > 0), codpr NUMBER(6) CONSTRAINT fk_liniifact_produse REFERENCES produse(codpr), cantitate NUMBER(10), pretunit NUMBER (12), CONSTRAINT pk_liniifact PRIMARY KEY (nrfact,linie) ) ;

Modificarea structurii (1)Adugarea unui nou atributALTER TABLE PERSOANE ADD DataNast DATE

Modificarea tipului/lungimii unui atributALTER TABLE PERSOANE MODIFY (Nume VARCHAR2(21))

Adugarea/modificarea valorii impliciteALTER TABLE PERSOANE MODIFY (Sex DEFAULT 'F')ALTER TABLE PERSOANE MODIFY (Sex DEFAULT NULL)

Adugarea/anularea restriciilor ALTER TABLE PERSOANE DROP PRIMARY KEYALTER TABLE PERSOANE ADD PRIMARY KEY (CNP)

tergerea tabelelorDROP < comportament la tergere >unde:< comportament la tergere > : : = = RESTRICT | CASCADE

Actualizarea tabelelorAdugarea unei liniiINSERT INTO tabel [(atribut1, atribut2, .)] VALUES (valoare_atribut1, valoare_atribut2, .)

tergerea liniilorDELETE FROM nume-tabelWHERE predicatDELETE FROM FACTURI WHERE NrFact = 1122

Modificarea valorilor unor atributeUPDATE tabel SET atribut1 = expresie1 [, atribut2= expresie2 .] WHERE predicatUPDATE CLIENTI SET Telefon = 032-313131 WHERE CodCl = 1001UPDATE PRODUSE SET ProcTVA = .22