1 / 18

CS 430 Database Theory

CS 430 Database 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

Leo
Download Presentation

CS 430 Database Theory

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CS 430Database Theory Winter 2005 Lecture 10: Introduction to SQL

  2. 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

  3. 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”

  4. 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”

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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 …

  13. 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;

  14. 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

  15. 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;

  16. 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;

  17. Some Basic SQL – UPDATE • update EMPLOYEE set Salary = 100000 where ID = 123; • update EMPLOYEE set Salary = Salary * 0.05;

  18. Some Basic SQL – DELETE • delete from EMPLOYEE where ID = 123; • delete from EMPLOYEE;

More Related