tailieunhanh - Lecture Database system concepts (5/e): Chapter 3 - Silberschatz, Korth, Sudarshan 

This chapter presents the following content: Data definition, basic query structure, set operations, aggregate functions, null values, nested subqueries, complex queries, views, modification of the database. | Chapter 3: SQL Chapter 3: SQL Data Definition Basic Query Structure Set Operations Aggregate Functions Null Values Nested Subqueries Complex Queries Views Modification of the Database Joined Relations** 1 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 schema for each relation, including attribute types. Integrity constraints Authorization information for each relation. Non-standard SQL extensions also allow specification of The set of indices to be maintained for each relations. The physical storage structure of each relation on disk. Allows the specification of: 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 attribute Ai Example: create table branch (branch_name char(15), branch_city char(30), assets integer) 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 n digits to the right of decimal point. real, double precision. Floating point and double-precision floating point numbers, with machine-dependent precision. float(n). Floating point . | Chapter 3: SQL Chapter 3: SQL Data Definition Basic Query Structure Set Operations Aggregate Functions Null Values Nested Subqueries Complex Queries Views Modification of the Database Joined Relations** 1 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 schema for each relation, including attribute types. Integrity constraints Authorization information for each relation. Non-standard SQL extensions also allow specification of The set of indices to be maintained for each relations. The physical storage structure of each relation on disk. Allows the specification .