Outline Overview of The SQL Query Language ■SQL Data Definition Basic Query Structure of SQL Queries Additional Basic Operations ■Set Operations ▣Null Values Aggregate Functions ■Nested Subqueries Modification of the Database Database System Concepts-7th Edition 3.2 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 3.2 ©Silberschatz, Korth and Sudarshan th Edition Outline ▪ Overview of The SQL Query Language ▪ SQL Data Definition ▪ Basic Query Structure of SQL Queries ▪ Additional Basic Operations ▪ Set Operations ▪ Null Values ▪ Aggregate Functions ▪ Nested Subqueries ▪ Modification of the Database
History IBM Sequel language developed as part of System R project at the IBM San Jose Research Laboratory Renamed Structured Query Language (SQL) ANSI and ISO standard SQL: ·SQL-86 SQL-89 ·SQL-92 SQL:1999(language name became Y2K compliant!) ·SQL:2003 ■ Commercial systems offer most,if not all,SQL-92 features,plus varying feature sets from later standards and special proprietary features. Not all examples here may work on your particular system. Database System Concepts-7th Edition 3.3 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 3.3 ©Silberschatz, Korth and Sudarshan th Edition History ▪ IBM Sequel language developed as part of System R project at the IBM San Jose Research Laboratory ▪ Renamed Structured Query Language (SQL) ▪ ANSI and ISO standard SQL: • SQL-86 • SQL-89 • SQL-92 • SQL:1999 (language name became Y2K compliant!) • SQL:2003 ▪ Commercial systems offer most, if not all, SQL-92 features, plus varying feature sets from later standards and special proprietary features. • Not all examples here may work on your particular system
SQL Parts DML--provides the ability to query information from the database and to insert tuples into,delete tuples from,and modify tuples in the database. integrity-the DDL includes commands for specifying integrity constraints. View definition --The DDL includes commands for defining views. Transaction control-includes commands for specifying the beginning and ending of transactions. Embedded SQL and dynamic SQL--define how SQL statements can be embedded within general-purpose programming languages. Authorization-includes commands for specifying access rights to relations and views. Database System Concepts-7th Edition 3.4 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 3.4 ©Silberschatz, Korth and Sudarshan th Edition SQL Parts ▪ DML -- provides the ability to query information from the database and to insert tuples into, delete tuples from, and modify tuples in the database. ▪ integrity – the DDL includes commands for specifying integrity constraints. ▪ View definition -- The DDL includes commands for defining views. ▪ Transaction control –includes commands for specifying the beginning and ending of transactions. ▪ Embedded SQL and dynamic SQL -- define how SQL statements can be embedded within general-purpose programming languages. ▪ Authorization – includes commands for specifying access rights to relations and views
Data Definition Language The SQL data-definition language (DDL)allows the specification of information about relations,including: The schema for each relation. The type of values associated with each attribute. The Integrity constraints The set of indices to be maintained for each relation. Security and authorization information for each relation. The physical storage structure of each relation on disk. Database System Concepts-7th Edition 3.5 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 3.5 ©Silberschatz, Korth and Sudarshan th Edition Data Definition Language ▪ The schema for each relation. ▪ The type of values associated with each attribute. ▪ The Integrity constraints ▪ The set of indices to be maintained for each relation. ▪ Security and authorization information for each relation. ▪ The physical storage structure of each relation on disk. The SQL data-definition language (DDL) allows the specification of information about relations, including:
Domain Types in SQL char(n).Fixed length character string,with user-specified length n. varchar(n).Variable length character strings,with user-specified maximum length n. int.Integer (a finite subset of the integers that is machine-dependent). ■ smallint.Small integer (a machine-dependent subset of the integer domain type). numeric(p,d).Fixed point number,with user-specified precision of p digits,with d digits to the right of decimal point.(ex.,numeric(3,1),allows 44.5 to be stores exactly,but not 444.5 or 0.32) real,double precision.Floating point and double-precision floating point numbers,with machine-dependent precision. ■ float(n).Floating point number,with user-specified precision of at least n digits. More are covered in Chapter 4 Database System Concepts-7th Edition 3.6 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 3.6 ©Silberschatz, Korth and Sudarshan th Edition Domain Types in SQL ▪ char(n). Fixed length character string, with user-specified length n. ▪ varchar(n). Variable length character strings, with user-specified maximum length n. ▪ int. Integer (a finite subset of the integers that is machine-dependent). ▪ smallint. Small integer (a machine-dependent subset of the integer domain type). ▪ numeric(p,d). Fixed point number, with user-specified precision of p digits, with d digits to the right of decimal point. (ex., numeric(3,1), allows 44.5 to be stores exactly, but not 444.5 or 0.32) ▪ real, double precision. Floating point and double-precision floating point numbers, with machine-dependent precision. ▪ float(n). Floating point number, with user-specified precision of at least n digits. ▪ More are covered in Chapter 4