SQL: Structured Query Language

SQL: Structured Query Language Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu

SQL Language • Data Definition Language (DDL) • Create tables, specifying the columns and their types of columns, the primary keys, etc. • Drop tables, add/drop columns, add/drop constraints – primary key, unique, etc. • Data Manipulation Language (DML) • Update, Insert, Delete tuples • Query the data These are already covered Our focus today

Reminder About DDL • Create “Students” relation • Create “Courses” relation CREATE TABLE Students (sid: CHAR(20) Primary Key, name: CHAR(20) Not NULL, login: CHAR(10), age: INTEGER, gpa: REAL); CREATE TABLE Courses (cid: Varchar(20) Primary Key, name: string, maxCredits : integer, graduateFlag: boolean); • Create “Enrolled” relation CREATE TABLE Enrolled (sid: CHAR(20) Foreign Key References (Students.sid), cid: Varchar(20), enrollDate: date, grade: CHAR(2), Constraints fk_cidForeign Key cid References (Courses.cid)); Alter Table Enrolled Add Constraints fk_cidForeign Key cid References Courses(cid));

Reminder About:Insert, Update, Delete • This is performed using Data Manipulation Language of SQL (DML) • Insertion • Insert into Students values (‘1111’, …); • Deletion • Delete from Students; • Delete from Students Where sid = ‘1111’; • Update • Update Students Set GPA = GPA + 0.4; • Update Students Set GPA = GPA + 0.4 Where sid = ‘1111’;

SQL Query LanguageSELECT Statement

SELECT-FROM-WHERE SELECT <list of columns> FROM <relation name> WHERE<conditions>; π relation name σ

SELECT-FROM-WHERE * Means “project all attributes” SELECT * FROM Student WHEREsName= ‘Greg’ AND address=‘320FL’; Student  (sName=‘Greg’ AND address=‘320FL’) (Student)

SELECT-FROM-WHERE SELECT sNumber FROM Student WHEREsName=‘Greg’ AND address=‘320FL’; Student πsNumber((sName=‘Greg’ AND address=‘320FL’) (Student))

Select-From Query • Only SELECTand FROMclauses are mandatory • The WHEREclause is optional • If not exist, then all records will be returned (there are no selection predicates) SELECT <list of columns> FROM <relation name>;

Select-From Query SELECTsNumber, sName FROMStudent; Student  (sNumber, sName) (Student)

Extended Projection The select clause can have expressions and constants SELECT <list of columns or expressions> FROM <relation name> WHERE<conditions>; Can also rename the fields or expressions using “AS”

Extended Projection SELECT‘Name:’ || sName AS info, 0 AS gpa FROM Student WHEREaddress=‘320FL’; Student  (info  ‘Name:’||sName, gpa  0 ) ( (address=‘320FL’) (Student))

Mapping between SQL and Relational Algebra  L ( C (R)) SELECT L FROM R WHERE C

Renaming Relations and Tuple Variables SELECTS1.sNumber AS num FROMStudent S1 WHERES1.sNumber >= 1; Tuple variable Student  (num S1.sNumber) ( (S1.sNumber >= 1) (S1(Student)))

Where Clause • The comparison operator depends on the data type • For Numbers: • <, >, <=, >=, =, <> • What about Strings?? SELECTS1.sNumber AS num FROMStudent S1 WHERES1.sNumber >= 1;

String Operators • Comparison Operators based on lexicographic ordering: =, <, >, <>, >=, <= • Concatenation operator: || • Pattern match: sLIKE p • p denotes a pattern • Can use wild characters in p such as _, % • _  matches exactly any single character • %  matches zero or more characters SELECT‘Name:’ || sName FROM Student WHEREaddress=‘320FL’;

String Matching Example SELECTs1.sNumber AS num FROMStudent S1 WHEREs1.sName LIKE ‘Da%’ OrS1.professor LIKE ‘M_’;

Set Operators in SQL • Set Semantics • Union, Intersect, Except • Bag Semantics • Union All, Intersect All, Except All • The two relations R and S must have the same number of columns and data types (Union Compatible) • Oracle allows columns to have different names

Example Operators : UNION, INTERSECT, and EXCEPT (SELECT sName FROM Undergrad_Student) UNION (SELECT sName FROM Grad_Student) If two students have the same name, it will keep only one (SELECT sName FROM Undergrad_Student) UNION All (SELECT sName FROM Grad_Student) Will keep all names even if identical

Set Operations in SQL: Example

Cartesian Product in SQL • In Relation Algebra: R x S • In SQL, add R and S to FROM clause • No WHERE condition that links R and S SELECT* FROMStudent, Professor; SELECTsName, pNumber FROMStudent, Professor;

Cross Product - Example Student Professor SELECT* FROMStudent, Professor;

Theta Join in SQL • In Relation Algebra: R ⋈C S • In SQL, add R and S to FROM clause • WHERE condition that links R and S with the join condition C SELECT* FROMStudent, Professor WHEREStudent.pNum = Professor.Number; Join condition

Theta Join Example Student Professor SELECTsNumber, sName, pName FROMStudent, Professor WHEREprofNum = pNumber;  sNumber,sName,pName(Student ⋈(profNum=pNumber) Professor)

Natural Join Reminder: Join columns must have same names in both relations (R ⋈ S) SELECT * FROM Student , Professor WHERE Student.pnumber = Professor.pnumber ; Student ⋈ Professor SELECT * FROM Student NATURAL JOIN Professor; Explicitly add the equality join condition

Natural Join - Example Professor Student SELECT * FROM Student , Professor WHERE Student.pNumber = Professor.pNumber ; Student ⋈ Professor

Example Queries SELECT * FROMloan WHEREamount > 1200 ; SELECTloan_number FROMloan WHEREamount > 1200 ;

Example Queries SELECTcustomer_name FROMdepositor Union SELECTcustomer_name FROMborrower;

Example Queries DBMS is smart enough !!! (Select first, then joins) SELECT customer_name FROMborrower B, loan L WHERE B.loan_number = L.loan_number ANDL.branch_name = “Perryridge”;

Sorting: ORDER BY clause • New optionalclause that you can add to the SELECT statement called “ORDER BY” • Allows sorting the returned records according to one or more fields SELECT* FROMStudent WHEREsNumber >= 1 ORDER BY pNumber, sName; Default is ascending order SELECT* FROMStudent WHEREsNumber >= 1 ORDER BY pNumberASC, sNameDESC;

Sorting: ORDER BY clause Student SELECT* FROMStudent WHEREsNumber >= 1 ORDER BY pNumber, sName DESC;  (pNumber, sName DESC) ( (sNumber >= 1) (Student))

Duplicate Elimination in SQL • New optionalkeyword “DISTINCT” • Added in the SELECT clause SELECTDISTINCT… FROM… … Eliminate any duplicates from the answer

Duplicate Elimination: Example Student SELECTDISTINCTsName, address FROMStudent; • (sName,address(Student)) • ( (address) ( (sNumber > 1) (Student))) SELECTDISTINCTaddress FROMStudent WHEREsNumber > 1;

Always Remember…. • Only SELECT and FROM clauses are mandatory • All the others are optional • You can mix and match the optional ones • But if you add a clause, then keep it in its order SELECTDISTINCTaddress FROMStudent WHEREsNumber > 1; SELECTaddress FROMStudent ORDER BY sNumber; SELECTaddress FROMStudent WHEREsNumber > 1 ORDER BY sNumber; SELECTaddress FROMStudent ORDER BY sNumber WHEREsNumber > 1; X

Aggregation + GroupBy

Possible Aggregations in SQL SELECT COUNT(*) FROM Student; SELECT COUNT(sNumber) FROM Student; SELECT MIN(sNumber) FROM Student; SELECT MAX(sNumber) FROM Student; SELECT SUM(sNumber) FROM Student; SELECT AVG(sNumber) FROM Student;

Grouping & Aggregation in SQL • New optionalclause called “GROUP BY” • If the SELECT statement has “WHERE” • Then WHERE conditions are evaluated first, then records are grouped SELECTpNumber, COUNT(sName) FROMStudent GROUP BY pNumber; Form one group for each pNumber, and then count inside each group

GROUP BY: Example I Student cnt count(*) (Student) pNumber,cntcount(*) ( (sNumber > 1) (Student)) SELECTpNumber, count(*) AS CNT FROMStudent WHEREsNumber > 1 GROUP BYpNumber; SELECTcount(*) AS CNT FROMStudent;

GROUP BY: Example II Student pNumber,address, CNT  count(sName), SUM  sum(sNumber) ( (sNumber > 1) (Student)) SELECTpNumber,address, count(sName) AS CNT, sum(sNumber) ASSUM FROMStudent WHEREsNumber > 1 GROUP BYpNumber, address;

Restrictions of GROUP BY • If you group by A1, A2, …An, then any other column projected in SELECT clause must be inside an aggregation function SELECTpNumber, address, count(sName) AS CNT, sum(sNumber) ASSUM FROMStudent WHEREsNumber > 1 GROUP BYpNumber, address; SELECTpNumber, address, sName, sum(sNumber) ASSUM FROMStudent WHEREsNumber > 1 GROUP BYpNumber, address; X SELECTpNumber, count(sName) AS CNT, sum(sNumber) ASSUM FROMStudent WHEREsNumber > 1 GROUP BYpNumber, address;

HAVING Clause: Putting Condition on Groups • How to add conditions on each group? • Select only the groups where the COUNT > 5 • These conditions are after you build the groups (not before) • Remember: WHERE conditions are executed before the groups are formed • New optionalclause called “HAVING”, added after the GROUP BY clause SELECTpNumber, COUNT(sName) FROMStudent GROUP BY pNumber HAVING SUM(sNumber) > 2; Can reference aggregation inside HAVING

HAVING Clause: Example Student  (SUM> 3) (pNumber,address, CNT  count(sName), SUM  sum(sNumber) ( (sNumber > 1) (Student))) SELECTpNumber,address, count(sName) AS CNT, sum(sNumber) ASSUM FROMStudent WHEREsNumber > 1 GROUP BYpNumber, address HAVING SUM > 3;

SELECT Statement Clauses SELECT<projection list> FROM<relation names> WHERE <conditions> GROUP BY <grouping columns> HAVING <grouping conditions> ORDER BY <order columns>; • Optional clauses if added must be in the order above • Order of execution • FROM • WHERE • GROUP BY • HAVING • ORDER BY • SELECT optional

SQL: Structured Query Language