Cap24. Mssql proceduri stocate built-in

1) Procedurile stocate sp_addtype si sp_droptype

2) Procedurile stocate sp_help si sp_helptext

3) Procedurile stocate sp_depends si sp_helpconstraint

4) Procedura stocata sp_key (PRIMARY KEY):

5) Procedura stocata sp_fkeys

6) Procedura sp_helpdb (afiseaza marimea si locatia fisierelor din bd) si procedura stocata sp_spaceused

7) Procedura stocata sp_helptrigger -afiseaza informatii despre triggeri

8) Procedura stocata sp_who si kill

9) Procedura stocata sp_lock (afiseaza informatii despre locks din bd)

10) Procedurile stocate sp_password si sp_addlogin:

sp_addlogin si alte proc.stocate:

Cap23. Interogari si sub-interogari avansate,tabele temporare,intersectia a 2 tabele, diferenta a 2 tabele

1) Self join (multy-query table that refers to itself):

2) Tabele temporare pt. inlaturarea datelor duplicate

Stergere inregistrari din tabela temporara:

3) Setare valori tabela 1 bazat pe valori din tabela 2 (clauza UPDATE):

4) Operatorii IN si EXISTS (IN verifica daca o valoare e intr-o lista de valori)

5) Operatorul ALL pt.a combina 2 query-uri intr-una singura

6) Operatorul EXISTS pt.a verifica daca exista inregistrari duplicat in tabela

7) Join la continutul tabelelor si rezultatele functiilor (chestionar msssql)

8) Folosire view-uri pt.a vedea ierarhia  nivelurilor de agregare

9) Predicatul EXISTS pt. a afisa intersectia a 2 tabele

10) Predicatul EXCEPT pt.a afisa diferenta dintre 2 tabele

11) Clauza TOP

12) SELECT cu RUNNING TOTALS

Cap.20 Proceduri stocate ,functii utlizator

1) Sintaxa generala proceduri stocate  (ex.proc.stocata si executie proc stocata)

2)  Cum se definesc variabile intr-o procedura stocata

3) Folosire proceduri stocate pt.a intoarce valori

4) Intoarcerea unei valori dintr-o functie  stocata folosind cuv.cheie RETURN:

5) Lucrul cu cursori in procedurile stocate

6) Declararea variabilelor intr-o procedura stocata si asignarea valorilor pt.aceste valori declarate (clauzele declare si set):

7) Crearea unei functii utilizator (CREATE FUNCTION-crearea functiilor scalare in mssql)

Explicatia parametrilor:

Exemple CREATE FUNCTION:

Ex1:

Ex2:

Ex3:

8) Stergerea unei proceduri stocate sau functii utilizator (functii scalare)

Cap19. Lucru cu information schema view

Totul despre tabele sistem:

Creare mdf si ldf fisiere:

Adaugare fisiere si grupuri fisiere la bd existente:

Arhitectura single respectiv multi pt.bd

In a single-database architecture (such as that used by a DB2 or ORACLE SQL Server), the DBMS supports (and manages) a single database. (The database may consist of multiple physical files, but all database objects exist in a single logical database file.)

Multi-server:

A multi-database architecture, as shown in Figure 498.2, lets you group tables (by owner) within multiple database files. Instead of keeping tables for multiple applications in the same database file (as is the case in a single-database architecture), each database will normally support a single (or set of related) applications. When you add a new, different application, you will typically create a new database to hold that tables used to support it.

Raport vanzari mssql:

1) Schema check constraints view

2) Folosire COLUMN_DOMAIN

3) Schema COLUMN_PRIVILEGES

4) Schema column views

5) Schema CONSTRAINT_COLUMN_USAGE

6) Schema CONSTRAINT_TABLE_USAGE

7) Schema DOMAIN_CONSTRAINTS

8) Schema DOMAIN

9) Schema KEY_COLUMN_USAGE

10) Schema PARAMETERS

11) Schema REFERENTIAL_CONSTRAINTS

12) Schema ROUTINES

13) Schema SCHEMATA

14) Schema Table_Constraints

15) Schema Table_Privileges

16) Schema tables

17) Schema VIEW_COLUMN_USAGE

18) Schema VIEW_TABLE_USAGE

19) Schema ROUTINE_COLUMNS

20) Schema VIEWS View

Cap18. DataBlobs and Text tipuri date, lucrul cu imagini in mssql

1) Teorie:

2) INSERT si UPDATE la tip data BLOB:

3) INSERTEXT si UPDATEXT:

4) Functiile pt.blob READTEXT,TEXTVALID, PATINDEX

Cap17. Triggers

1) Timp si data setata la executie

2) Triggers de insert

An INSERT trigger is a stored procedure you want the DBMS to execute either AFTER or INSTEAD OF executing an INSERT statement on a particular table or view .

3) Triggers de delete

A DELETE trigger is a stored procedure that the DBMS executes either after or instead of executing a DELETE statement on a specific table or view.

4) Triggers de update

An UPDATE trigger is a stored procedure that the DBMS executes either after or instead of executing an UPDATE statement on a specific table or view.

5) Trigger update pt.schimbare PK sau FK:

6) Cascade triggers

Because a trigger can execute INSERT, UPDATE, and DELETE statements, work performed by one trigger can cause the DBMS to activate another trigger, which may execute a statement that activates a third trigger, and so on. In short, a single triggering (INSERT, UPDATE, or DELETE) statement can cause the DBMS to activate a cascade of triggers, each trigger executing statements that activate other triggers.

7) Triggers referential integrity

8) Trigger pt. a trimite un mesaj e-mail

Cap16.Cursori (regasirea si manipularea datelor)

1) Declarare si creare cursor

2) Declarare cursor si clauza ORDER BY:

3) Declarare cursor FOR UPDATE:

4)

5)

6) Cursor cu update si delete:

7) Index pt.schimbarea ordinii dintr-un cursor:

8) Inchidere cursor, drop cursor (deallocate) si cursor insensitive

9) Variabila sistem @@ROW_CURSOR:

10) Nested cursors

Cap.14 Tranzactii nivele de izolare si procesare concurenta

1) INSERT INTO cu SELECT

DBA – admin-ul bd (cand sunt putini useri)

DBO – in momentul crearii tabelelor, view-urilor, query-urilor respectivul user devine database owner (dbo)

2) GRANT DELETE

3) GRANT USAGE si create domain – acces pt.useri la domenii create

4) REVOKE si GRANT – optiunea cascade

5) Folosire GRANT si REVOKE simultan

6) Cele 4 niveluri ale tranzactiilor:

7) Deadlock – situatii de inter-blocare, nivelul serializable

8) SET TRANSACTION: (setarea unui nivel de izolare al tranzactiei -unul din cele 4)

9) COMMIT pt.update permanent la bd:

10) Constrangeri multi-tabela:

Cap.13 Subquery-uri

1) Exemplu grouped subqueries

2)

3) Predicatele EXISTS si NOT EXIST la subqueries:

4) Relatia intre joins si subqueries

5) Cuvinte rezervate IN si NOT IN la subcereri

6) ALL ,SOME si ANY subquery ce intoarce valori multiple:

7) Folosire functie agregat intr-un subquery pt.a intoarce o singura valoare:

8) Rolul subquery-urilor intr-o clauza where:

9) Subquery-uri pt. a intoarce valoare de true sau false

10) Nested queries pt.a intoarce valoare de true sau false:

11) Queries corelate

12) Subqueries in clauza HAVING

13) Query-uri corelate cu operatori de comparatie

14) Folosire query corelat ca un filtru (clauza HAVING ):

15) Query-uri corelate cu clauza update

Cap.12 Join-uri in sql, interogari multi-tabela

1) Produs cartezian a 2 sau mai multe tabele (nu exista clauza where)

2) CROSS JOIN produs cartezian

3) Clauza where pt. join-ul a 2 tabele

Exemplul1:

Exemplul2:

Exemplul3:

Exemplul4:

continuare ex.4:

Exemplul5: Intersectie cu CORRESPONDING:

4) Join multiplu clauza where pt.a genera produsul cartezian

5) Alias-uri:

6) Natural Join

A NATURAL JOIN is a special type of equi-join with an implied WHERE clause that compares all columns in one table with corresponding columns that have the same name in another table for equality

7) Join conditional (clauza where), cross join:

8) Inner join exemplu:

9) Cuv.rezervat USING la INNER JOIN:

10) OUTER JOIN exemplu:

11) LEFT OUTER JOIN:

12) RIGHT OUTER JOIN si FULL OUTER JOIN:

Ex2:

13) JOIN la mai multe tabele intr-un singur query:

14) Non-equality inner join si outer join:

15) UNION JOIN:

16) Rolul unei clauze FROM intr-un join:

17) Operatorul * la clauza select

18) Single table join

19) Alias-uri:

20) Exemplu select cu procent

21) Exists – in loc de count(*) pt.a verifica daca o subcerere intoarce cel putin o inregistrare

22) Exemple joins cu clauza where si tratare valori NULL

22) Nested queries (subqueries) – lucreaza pe mai multe tabele dintr-o data