What is the ANSI SQL standard

SQL: overview. SQL (Structured Query Language) is ANSI and ISO Standard 1975 SEQUEL = Structured English Query Language for System R

Transcript

1 SQL: Overview SQL (Structured Query Language) is ANSI and ISO Standard 1975 SEQUEL = Structured English Query Language for System R (IBM) 1979 Oracle V2 (Relational Software Inc.) first SQL product 1986 ANSI Standard Standard SQL International Organization for Standardization (ISO ) Standard SQL SQL1 (Version 2) standardized 1992 SQL2 or SQL 92 adopted by the ISO All current DBMS essentially adhere to it 1999 SQL: 1999 (ISO / IEC 9075: 1999, also called SQL3) Not yet implemented in all DBMS, this also applies to the the following: 2003 SQL: SQL: 2008 1

2 SQL: Data definition Data definition in SQL using CREATE, DROP and ALTER TABLE CREATE TABLE Defines a new (basic) table with name and attribute specification (name and data type from INTEGER; FLOAT, DECIMAL (i, j), CHAR (n), VARCHAR ( n)) A constraint NOT NULL can be defined for attributes Example CREATE TABLE department (ABT_NAME VARCHAR (10) NOT NULL, ABT_ID INTEGER NOT NULL, ABT_Leiter CHAR (9), ABT_Leiter_seit CHAR (9)); 2

3 CREATE TABLE (continued) SQL: Data definition In SQL2, primary keys, foreign keys and constraints for referential integrity can also be specified Primary keys using the keywords PRIMARY KEY and UNIQUE Example CREATE TABLE department (ABT_NAME VARCHAR (10) NOT NULL, ABT_ID INTEGER NOT NULL , ABT_Leiter CHAR (9), ABT_ Leiter_ since CHAR (9)); PRIMARY KEY (ABT_ID), UNIQUE (ABT_NAME), FOREIGN KEY (ABT_Leiter) REFERENCES staff); 3

4 DROP TABLE: SQL: Data definition Deletes a table and its definition. The Tbll table can never be used again! Example: DROP TABLE Personal; ALTER TABLE: Adds a further attribute to a relation. After the statement has been executed, all tuples have the value NULL for this attribute t Thus, the NOT NULL constraint is not allowed here. Example: ALTER TABLE Personal ADD Job description VARCHAR (12); The user must explicitly enter a value for each tuple 4

5 SQL: Data definition Further DDL statements in SQL2 and SQL: 1999 CREATE SCHEMA A DB schema Sh is explicitly declared via a name Support of the referential integrity RESTRICT, CASCADE, SET NULL, SET DEFAULT Example: CREATE TABLE department (ABT_NAME VARCHAR (10) NOT NULL, ABT_ID INTEGER NOT NULL, ABT_ Head CHAR (9), ABT_Leiter_seit CHAR (9)); PRIMARY KEY (ABT_ID), UNIQUE (ABT_ NAME), FOREIGN KEY (ABT_Leiter) REFERENCES Personal ON DELETE SET DEFAULT ON UPDATE CASCADE); 5

6 Referential integrity (continued) SQL: Data definition CREATE TABLE Personal (Name VARCHAR (30) NOT NULL, P_ ID CHAR (9), NOT NULL Geb_Dat DATE, ABT_ID INTEGER DEFAULT 1, superior CHAR (9), PRIMARY KEY (P_ID) , FOREIGN KEY (ABT_ID) REFERENCES DEPT ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (superior) REFERENCES Personal ON DELETE SET NULL ON UPDATE CASCADE); 6th

7 SQL: Data definition Further data types in SQL2 or SQL: 1999 DATE: Dates with different formats, standard YYYY MM DD TIME: Ziti time in hours, minutes and Sk seconds, standard d hh: mm: ss TIME (i): time with fractions of a second (i digits) hh: mm: ss: ii ... i TIMESTAMP: consists of DATE and TIME INTERVAL: Relative specification of time compared to an absolute specification (plus 3 WEEKS) CLOB: text without internal structure BLOB: digital object 7

8 SQL: Queries SQL has a statement for requests: f the SELECT statement S Important difference between the formal relational model and the SQL data model: SQL works with multi-sets (bags), i.e. identical tuples can exist in a table. Example: {A, B, C, A} is a bag; {A, B, C} is both bag and quantity The basic form of a SQLSELECT statement SELECT is a mapping, also called SFW clause (SELECT FROM WHERE block) SELECT FROM

WHERE is the Set of attribute names whose values ​​form the result of the query
specifies all tables (relations) for answering the query is a (Boolean) condition, g, which specifies every tuple that is in the response set. 8th

9 Example DB schema: SQL: Requests (Queries) Personnel V_Name N_NameName P_ID Geb_Dat Adr M_W Salary Vorg Abt_Nr Department Abt_Name Abt_ID Abt_Leiter Abt_Leiter_Seit Location Abt_ID Location Project P_Name Proj_ID Location Dept. From Work_for t P_ID Proj_ID Hours Related P_Id Geb_Dat M_W

10 Simple queries: SQL: Queries Correspond to the expressions selection, projection and join of the RA query on a table: Date of birth and address of employee Bernd Müller Q0: SELECT Geb_Dat, Adr FROM Personal WHERE VName = Bernd 'V_Name AND NName = Müller N_Name Corresponds to a selection (WHERE clause) and projection (SELECT clause) Result may contain duplicates Request for two tables: Name and address of all employees in the research department Q1: SELECT V_Name, N_Name, Adr FROM Personnel, Department WHERE Dept_Name = Name = Research ' ANDAbt Abt_ID = Abt_Nr Corresponds to a combination of selection, projection and join of RA 10

11 SQL: Queries Simple queries (cont.) Give me the project ID, the associated department ID and surname, address and date of birth of the responsible department manager for each project in Freiberg Q2: SELECT Proj_ID, Abt_Id, NN N_Name, Adr, GbDt Geb_Dat FROM project, department, personnel WHERE dept = dept_ID AND dept_leaders = P_ID AND location = 'Freiberg'; In Q2 there are two joins aliases: Attribute names must be unique in SQL statements (with regard to relations) If attributes with the same name from different tables are required in a statement, they must be qualified: Table names as prefix Examples: Personal.Geb_Dat, Project. Location In some queries you need a table more than once; in this case (and aliases are assigned for abbreviated spellings: Example Q8: Give me the name and name of the direct superior of an employee Q8: SELECT P.V_Name, P.N_Name, V.V_Name, V.N_Name FROM Personal (AS) PV WHERE P.Vorg = V.P_ID In Q8 the table names P and V are aliases or tuple variables for the staff table; P and V represent different copies P the subordinates, V the superiors, AS is optional 11

12 Simple queries (cont.) Empty WHERE clause: SQL: Queries If no condition (WHERE clause) l) exists, all tuples are qualified for the result Corresponds to WHERE TRUE Example: Enter with the P_IDs of all employees Q9: SELECT P_ ID FROM staff; If several tables are specified in the FROM clause (without the WHERE clause), the Cartesian product is output. Example: Q10: SLC SELECT P_ID, Dept_Name FROM Personnel, Department; Problem: Very large number of answers possible! 12th

13 Simple queries (cont.) SQL: Queries *: In order to output all attributes of the specified tables, * is used: Examples: Q1C: SELECT * FROM Personal WHERE Abt_Nr = 5; Q1D: SELECT * FROM Personnel, Department WHERE Dept_Name = Research 'AND Dept_Nr = Dept_ID; DISTINCT: SQL does not treat tables as sets; Duplicates can occur To avoid duplicates in the answer, DISTINCT is used: Examples with / without duplicates: Q11: SELECT Salary FROM Personnel Q11A: SELECT DISTINCT Salary FROM Personnel 13

14 Set operations SQL: Queries SQL has built in set operations UNION (and some i versions of SQL MINUS and INTERSECT) Results are always sets, duplicates are eliminated Set operations only on tables compatible with union: here identical attributes and these specified in the same order! Example: Search all project numbers of the projects in which an employee with the last name Müller works or which belong to a department that is managed by an employee Müller Q4: (SELECT P_Name FROM Project, Department, Personnel WHERE Dept. = Dept._ID AND Dept._Leaders = P_ID AND N_Name = Müller) UNION (SELECT P_Name FROM Project P, works for A, Personal Pers WHERE PProj P.Proj_ID = A.Proj_ID IDAND P.P_ID PP ID = Pers.P_IDP AND N_Name = Müller) 14

15 SQL: Queries Nested h queries: A SELECT statement can be used as a nested subquery in a WHERE clause of the so-called outer query Sh Nesting htl is itoften an alternative ti to queries; Example form Q1: SELECT V_Name, N_Name, Adr FROM Personal WHERE Abt_Nr IN (SELECT Abt_ID FROM Department WHERE Abt_Name = Research ') IN corresponds to the element Operator extended to multi-set requests can be nested as deeply as possible Attributes are set as early as possible (furthest inside) assigned Correlated (sub) inquiries are defined in that attributes are used across inquiry boundaries Example: Name of employees with relatives with identical first names Q12: SELECT P.V_Name, P.N_Name FROM Personnel las P WHERE P.P_ID IN (SELECT P_ID FROM Relatives WHERE P_ID = P.P_ID AND P.V_Name = An_Name) _ 15

16 Nested queries (cont.): SQL: Queries Every nested query that only uses IN or = operators in the nesting can be expressed by a simple (not nested) SWF clause Example (see above): Q12A: SELECT P.V_Name, P.N_Name FROM Personnel P, Relatives A WHERE P.P_ID ID = A.P_ID ANDP.V P.V_Name = A.An_Name; EXISTS checks whether the result of a correlated subquery is empty or not Example (see above): Q12B: SELECT P.V_Name, P.N_Name FROM Personal P WHERE EXISTS (SELECT * FROM Relatives WHERE P_ID = P.P_ID PP ANDPV P.V_Name = An_Name) N 16

17 Nested queries (cont.): SQL: Queries names of employees without relatives Q6: SELECT VN V_Name, NN N_Name FROM Personnel P WHERE NOT EXISTS (SELECT * FROM Relatives WHERE P_ID = P.P_ID) EXISTS is necessary for expressiveness from SQL! Explicit sets (lists) Instead of nesting, explicit lists (sets) can also be used in a WHERE clause: Q13: SELECT DISTINCT P_ID FROM Working_for WHERE Proj_ID IN (1, 2, 3) 17

18 Queries and NULL SQL: Queries In SQL queries, a NULL value can be checked Here itis or IS NOT to be used as an operator (NULL values ​​are different, = does not work!) Example: All employees without a superior Q14: SELECT V_Name, N_Name FROM Personal WHERE Vorg IS NULL Warning: NULL values ​​are not taken into account for join conditions! 18th

19 SQL: Queries Joins A JOIN can be defined in the FROM clause Looks like any other table Different joins can be specified ("theta" JOIN, NATURAL JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS JOIN, etc) Examples: Q8: SELECT PV P.V_Name, PN P.N_Name, N VV V.V_Name, VN V.N_NameN FROM Personal PV WHERE P.Vorg = V.P_ID can be written as Q8: SELECT P.V_Name, P.N_Name , V.V_Name, V.N_Name FROM (Personal P LEFT OUTER JOIN Personal V ON P.Vorg = V.P_ID) Q1 (see above) o) Q1: SELECT FROM WHERE or as Q1: SELECT FROM WHERE V_Name, N_Name, Adr ( Personal JOIN department ON Abt_Nr = Abt_ID) Abt_Name = Research V_Name, N_Name, Adr (Personal NATURAL JOIN Department AS Department (Name, Abt_Nr, AL, ALS) Name = Research 19

20 SQL: Data definition aggregations COUNT, SUM, MAX, MIN, and AVG Maximum, minimum and average salary of employees Q15: SELECT MAX (SALARY), MIN (SALARY), AVG (SALARY) FROM Personal In DBMS do only one aggregation function after SELECT allowed! Number of all employees and number of employees in the research department: Q17: SELECT COUNT (*) FROM Personnel Q18: SELECT COUNT (*) FROM Personnel, department WHERE Dept_Nr = Dept_ID AND Dept_Name = Research 20

21 SQL grouping: data definition Often one would like to use the aggregation function on subgroups of the tuple set Jd Each subgroup is iddfii defined via identical values ​​with regard to one or more grouping attributes Grouping goes via GROUP BY to attributes that also have to be selected Example: Give me number, number of employees and their average salary for each department Q20: SELECT Abt_Nr, COUNT (*), AVG (salary) FROM Personal GROUP BY Abt_Nr Groups are formed on the basis of identical Abt_Nr Example: Give me the number and name of each project and the number of employees in the project Q21: SELECT P.Proj_ID, P_Name, COUNT (*) FROM Project P, Working_for A WHERE P.Proj_ID = A.Proj_ID GROUP BY P.Proj_ID, P_Name 21

22 Group by having SQL: Data definition In addition, groups that are to be aggregated can be selected using conditions The Having clause selects groups (instead of individual tuples) Example: For each project with more than two employees, give me its number, name and number Employees in project Q21: SELECT PP P.Proj_ID, PN P_Name, COUNT (*) FROM Project P, Working_for A WHERE P.Proj_ID = A.Proj_ID GROUP BY P.Proj_ID, P_Name HAVING COUNT (*)> 2 22