CS 430Database Theory Winter 2005 Lecture 10: Introduction to SQL
SQL History • SQL = Structured Query Language • History • Initially defined by IBM Almaden Research Labs for System R (an experimental relational database) • Database language for IBM’s DB2 RDBMS • Standards • Standardized by ANSI (American National Standards Institute) and ISO (International Standards Organization) • Many Versions and Components
SQL1 or SQL-86 • SQL-86 was a subset of IBM’s SQL • Included basic Data Manipulation Language (DML) • Did not include any Data Definition Language (DDL) • Language Bindings: COBOL, FORTRAN, Pascal, PL/1 • “Least Common Denominator”
SQL-89 • Superset of SQL-86 • Added basic DDL • CREATE and GRANT • No DROP, ALTER, REVOKE • Views • Added Ada and C language bindings • Included Integrity Enhancements • NOT NULL, FOREIGN KEY, UNIQUE, … • Still “Least Common Denominator”
SQL2 or SQL-92 • Superset of SQL-89, some incompatibilities • No longer “Least Common Denominator” • Much bigger standard • SQL-89 -- 120 pages • SQL-92 -- 579 pages • Not all features available in all products (even today) • Defined levels of conformance • Entry, Intermediate, and Full Levels • Entry Level close to SQL-89 with Integrity Enhancements
SQL-92 Highlights • More data types with operators • Date and Time • Varying length Character Strings • Natural Join and Outer Joins • Dynamic SQL • SQL constructed dynamically by programs • Cascades for Referential Integrity • More DML, including ALTER, DROP, REVOKE
More SQL-92 Highlights • Standard Catalogs • Transactions • Domains as Macros for constraint checking • Not same as Relational Model domain notion • Multiple Character Sets • Temporary Tables • Automatically vanish at end of transaction or session
SQL3 or SQL-99 • Superset of SQL-92, Significantly larger • All the documents add up to over 2000 pages • Large Data Types • Binary Large Objects (BLOBs) and Character Large Objects (CLOBs) • Recursive Queries • Schemas • Can reference multiple schemas simultaneously
More SQL-99 • Triggers, Stored Procedures • Procedural Language (SQL/Persistent Stored Modules (PSM)) • Standard Programming Language API • SQL/Call Level Interface (CLI) • Standardized version of Microsoft’s ODBC • Object/Relational features added • User defined types and methods • Structured and composite types
More SQL-99 • Divided into Foundation and Packages • SQL-99 Foundation includes all of SQL-92 with extensions • Package highlights: • PSM • CLI • Basic Object Support • Enhanced Object Support • Multi-Media • Full-text, Spatial, Images
Some Basic SQL - Data Types • Numeric • Integers of various sizes • Float, Double Precision • DECIMAL(i, j) – i digits, j after decimal point • Character String • Fixed length (CHAR) • Varying length (VARCHAR) • Bit Strings – both Fixed and Varying • Boolean • Date and Time • Basic Date and Time types • Timestamp • Interval – Time interval
Some Basic SQL - Expressions • Basic operations • Arithmetic (+, *, …) • Comparison (=, <, …) • Numeric Functions, e.g. SIN, … • Control Flow Functions - Case, IF, IFNULL • String Functions, e.g. CONCAT, TRIM, … • Date/Time manipulation • Full Text Search, Pattern Matching • Aggregation – SUM, AVERAGE, … • And more …
Some Basic SQL - Data Definition • create table EMPLOYEE ( ID Int NOT NULL, Name Varchar(30) NOT NULL, DNum Int, Salary Decimal(11, 2), primary key (ID), foreign key(Dnum) references Dept(DeptNo)); • alter table EMPLOYEE alter DNum set default 999;
Some Basic SQL - Data Manipulation • SELECT • Retrieve a table • Like Relational Calculus • INSERT • Add one or more rows to a table • UPDATE • Modify one or more rows in a table • DELETE • Remove one or more rows from a table
Some Basic SQL - SELECT • select Id, Name from EMPLOYEE where Salary > 1000000; • select DNum, DeptName, average(Salary) from EMPLOYEE, DEPT where EMPLOYEE.Dnum = DEPT.DeptNo group by DNum;
Some Basic SQL - INSERT • insert into EMPLOYEE values(12345, ‘John Smith’, 99, 55000); • insert into Dept_Info( DNum, Name, ASal) select DNum, DeptName, average(Salary) from EMPLOYEE, DEPT where EMPLOYEE.Dnum = DEPT.DeptNo group by DNum;
Some Basic SQL – UPDATE • update EMPLOYEE set Salary = 100000 where ID = 123; • update EMPLOYEE set Salary = Salary * 0.05;
Some Basic SQL – DELETE • delete from EMPLOYEE where ID = 123; • delete from EMPLOYEE;
