Outline Overview of The SQL Query Language Data Definition Basic Query Structure Additional Basic Operations Set Operations Null Values Aggregate Functions Nested Subqueries Modification of the Database Database System Concepts-6th Edition 3.2 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 3.2 ©Silberschatz, Korth and Sudarshan th Edition Outline Overview of The SQL Query Language Data Definition Basic Query Structure 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-6th Edition 3.3 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 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
Data Definition Language The SQL data-definition language(DDL)allows the specification of information about relations,including: The schema for each relation. The domain of values associated with each attribute. Integrity constraints And as we will see later,also other information such as The set of indices to be maintained for each relations. Security and authorization information for each relation. The physical storage structure of each relation on disk. Database System Concepts-6th Edition 3.4 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 3.4 ©Silberschatz, Korth and Sudarshan th Edition Data Definition Language The schema for each relation. The domain of values associated with each attribute. Integrity constraints And as we will see later, also other information such as The set of indices to be maintained for each relations. 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-6th Edition 3.5 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 6 3.5 ©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
Create Table Construct An SQL relation is defined using the create table command: create table r(A D1,A2 D2,...An Dn (integrity-constraint ) (integrity-constraint)) r is the name of the relation each A;is an attribute name in the schema of relation r D;is the data type of values in the domain of attribute A, Example: create table instructor D char(5). name varchar(20), dept_name varchar(20), salary numeric(8,2)) Database System Concepts-6th Edition 3.6 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 3.6 ©Silberschatz, Korth and Sudarshan th Edition Create Table Construct An SQL relation is defined using the create table command: create table r (A1 D1 , A2 D2 , ..., An Dn , (integrity-constraint1 ), ..., (integrity-constraintk )) r is the name of the relation each Ai is an attribute name in the schema of relation r Di is the data type of values in the domain of attribute Ai Example: create table instructor ( ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2))