Download
cs 430 database theory n.
Skip this Video
Loading SlideShow in 5 Seconds..
CS 430 Database Theory PowerPoint Presentation
Download Presentation
CS 430 Database Theory

CS 430 Database Theory

402 Vues Download Presentation
Télécharger la présentation

CS 430 Database Theory

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