C 1 SQL BASIC SQL Structured Query Language

C 1. SQL BASIC

SQL (Structured Query Language) is a special-purpose programming language designed from managing data in relational database management system SQL Instrunctions can be grouped into different classes: Ø Date defininition language (DDL): create, alter, drop Ø Data manipulation language (DML): • Insert – insert new data into a database • Update – updates data in a database • Delete – deletes data from a database • Select – extracts data from a database IN GOOD COMPANY 05 NOVEMBER 2020 2

DDL CREATE TABLE Studenti( nr. Matricol int NOT NULL, nume varchar(20) NULL, initiala. Tatalui varchar(3) NULL, prenume varchar(30) NULL, facultate varchar(10) NULL, specializare varchar(10) NULL, grupa int NULL ) IN GOOD COMPANY 05 NOVEMBER 2020 3

DDL (Cont) CONSTRAINTS • NOT NULL - specifies that the column does not accept NULL values • PRIMARY KEY - constraints identify the column or set of columns that have values that uniquely identify a row in a table • FOREIGN KEY - constraints identify and enforce the relationships between tables. You cannot insert a row with a foreign key value, except NULL, if there is no candidate key with that value. • The ON DELETE clause controls what actions are taken when you try to delete a row to which existing foreign keys point. The ON DELETE clause has the following options: NO ACTION specifies that the deletion fails with an error; CASCADE specifies that all the rows with foreign keys pointing to the deleted row are also deleted; SET NULL specifies that all rows with foreign keys pointing to the deleted row are set to NULL; SET DEFAULT specifies that all rows with foreign keys pointing to the deleted row are set to their default value • The ON UPDATE clause defines the actions that are taken if you try to update a candidate key value to which existing foreign keys point. This clause also supports the NO ACTION, CASCADE, SET NULL and SET DEFAULT options. • UNIQUE - constraints enforce the uniqueness of the values in a set of columns • CHECK (P), where P is a predicate - constraints enforce domain integrity by limiting the values that can be put in a column IN GOOD COMPANY 05 NOVEMBER 2020 4

DDL (Cont) ALTER TABLE Studenti ADD nationalitate varchar(30) ALTER TABLE Studenti ADD CONSTRAINT PK_nr. Matricol primary key (nr. Matricol) ALTER TABLE Studenti ALTER COLUMN initiala. Tatalui varchar(5) IN GOOD COMPANY 05 NOVEMBER 2020 5

DDL (Cont) Drop table_name Drop view schema_name. view_name Drop function schema_name. function_name Drop index_name on object_name Drop trigger_name on {database | all server} Drop database_name Drop schema_name Drop role_name Drop user_name IN GOOD COMPANY 05 NOVEMBER 2020 6

DML INSERT INTO Studenti ( nr. Matricol , nume , initiala. Tatalui , prenume , facultate , specializare , grupa , nationalitate) insert into Grupe select grupa, specializare from Studenti VALUES ( 12546 , 'Georgescu' , 'A' , 'Maria' , 'MI' , 'info-ro' , 122 , 'romana') IN GOOD COMPANY 05 NOVEMBER 2020 7

DML (Cont) update Studenti set nr. Matricol = nr. Matricol+1 update Studenti set nume = 'Ionnica' where nr. Matricol = 9546 IN GOOD COMPANY 05 NOVEMBER 2020 8

DML (Cont) DELETE FROM Studenti WHERE nrmatricol < 10000 DELETE FROM Studenti DELETE * FROM Studenti TRUNCATE TABLE Studenti The difference between truncate and delete • removes all rows from a table • the operation cannot be rolled back and no triggers will be fired • is faster and doesn't use as much undo space as a DELETE. • that the truncate resets auto increment column. IN GOOD COMPANY 05 NOVEMBER 2020 9

DML (Cont) SELECT nr. Matricol , nume , initiala. Tatalui , prenume , facultate , specializare , grupa , nationalitate FROM Studenti WHERE nrmatricol = 12486 WHERE grupa = 123 WHERE nationalitate is null WHERE prenume like 'V%' WHERE specializare like '%ro' IN GOOD COMPANY 05 NOVEMBER 2020 10

DML (Cont) IS NULL / IS NOT NULL – checks if the column is null or not BETWEEN – selects a range of data between two values. The values can be numbers, text, or dates [val 1, val 2) EXISTS / NOT EXISTS – returns the value true if the argument subquery is nonempty IN / NOT IN – operator allows you to specify multiple values in a WHERE clause. LIKE / NOT LIKE – operator is used in a WHERE clause to search for a specified pattern in a column; ‘%’ - matches any substring, ‘_’ - matches any character. IN GOOD COMPANY 05 NOVEMBER 2020 11

AGGREGATE FUNCTIONS Avg() – returns the average values Min() – returns the min value Max() – returns the max value Sum() – returns the average values Count() – returns the number of rows IN GOOD COMPANY 05 NOVEMBER 2020 12

COMMON TABLE EXPRESSIONS (CTE) WITH expression_name [ ( column_name [, . . . n] ) ] AS ( CTE_query_definition ) Recursive queries using CTE WITH cte_name ( column_name [, . . . n] ) AS ( CTE_query_definition –- Anchor member is defined. UNION ALL CTE_query_definition –- Recursive member is defined referencing cte_name. ) Statement using the CTE SELECT * FROM cte_name IN GOOD COMPANY 05 NOVEMBER 2020 13

CTE - EXAMPLE (Cont) USE Adventure. Works 2008; GO WITH Direct. Reports (Manager. ID, Employee. ID, Title, Dept. ID, Level) AS ( -- Anchor member definition SELECT e. Manager. ID, e. Employee. ID, e. Title, edh. Department. ID, 0 AS Level FROM Human. Resources. Employee AS e INNER JOIN Human. Resources. Employee. Department. History AS edh ON e. Employee. ID = edh. Employee. ID AND edh. End. Date IS NULL WHERE Manager. ID IS NULL UNION ALL -- Recursive member definition SELECT e. Manager. ID, e. Employee. ID, e. Title, edh. Department. ID, Level + 1 FROM Human. Resources. Employee AS e INNER JOIN Human. Resources. Employee. Department. History AS edh ON e. Employee. ID = edh. Employee. ID AND edh. End. Date IS NULL INNER JOIN Direct. Reports AS d ON e. Manager. ID = d. Employee. ID) - Statement that executes the CTE SELECT Manager. ID, Employee. ID, Title, Dept. ID, Level FROM Direct. Reports INNER JOIN Human. Resources. Department AS dp ON Direct. Reports. Dept. ID = dp. Department. ID WHERE dp. Group. Name = N'Sales and Marketing' OR Level = 0 IN GOOD COMPANY 05 NOVEMBER 2020 14

STORED PROCEDURE CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ] AS sql_statement [. . . n ] CREATE PROCEDURE au_info_all AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a. au_id = ta. au_id INNER JOIN titles t ON t. title_id = ta. title_id INNER JOIN publishers p ON t. pu EXEC au_info_allb_id = p. pub_id GO IN GOOD COMPANY 05 NOVEMBER 2020 15

CURSOR DECLARE cursor_name CURSOR FOR select_statement OPEN cursor_name FETCH NEXT FROM cursor_name INTO @variable_column_name WHILE @@FETCH_STATUS = 0 BEGIN … sql_statement FETCH NEXT FROM cursor_name INTO @variable_column_name END CLOSE cursor_name ; DEALLOCATE cursor_name; IN GOOD COMPANY 05 NOVEMBER 2020 16

Q&A IN GOOD COMPANY 05 NOVEMBER 2020 17