《数据库系统概念 Database System Concepts》原书教学资源（第六版，PPT课件讲稿，英文版）Chapter 3 Introduction to SQL

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

Integrity Constraints in Create Table not null primary key (A1,...A) foreign key (Am,...An)references r Example: create table instructor ID char(5), name varchar(20)not null, dept name varchar(20), salary numeric(8,2), primary key (ID), foreign key(dept_name)references department), primary key declaration on an attribute automatically ensures not null Database System Concepts-6th Edition 3.7 @Silberschatz,Korth and Sudarshan

Database System Concepts - 6 3.7 ©Silberschatz, Korth and Sudarshan th Edition Integrity Constraints in Create Table not null primary key (A1 , ..., An ) foreign key (Am, ..., An ) references r Example: create table instructor ( ID char(5), name varchar(20) not null, dept_name varchar(20), salary numeric(8,2), primary key (ID), foreign key (dept_name) references department); primary key declaration on an attribute automatically ensures not null

And a Few More Relation Definitions create table student ID varchar(5), name varchar(20) not null. dept name varchar(20), tot cred numeric(3,0), primary key (ID), foreign key (dept_name)references department); create table takes ID varchar(5), course id varchar(8), sec id varchar(8), semester varchar(6), year numeric(4,0), grade varchar(2), primary key (ID,course id,sec id,semester,year), foreign key(ID)references student, foreign key (course_id,sec_id,semester,year)references section); Note:sec_id can be dropped from primary key above,to ensure a student cannot be registered for two sections of the same course in the same semester Database System Concepts-6th Edition 3.8 @Silberschatz,Korth and Sudarshan

Database System Concepts - 6 3.8 ©Silberschatz, Korth and Sudarshan th Edition And a Few More Relation Definitions create table student ( ID varchar(5), name varchar(20) not null, dept_name varchar(20), tot_cred numeric(3,0), primary key (ID), foreign key (dept_name) references department); create table takes ( ID varchar(5), course_id varchar(8), sec_id varchar(8), semester varchar(6), year numeric(4,0), grade varchar(2), primary key (ID, course_id, sec_id, semester, year) , foreign key (ID) references student, foreign key (course_id, sec_id, semester, year) references section); Note: sec_id can be dropped from primary key above, to ensure a student cannot be registered for two sections of the same course in the same semester

And more still create table course course id varchar(8), title varchar(50), dept name varchar(20), credits numeric(2,0) primary key (course_id), foreign key(dept name)references department); Database System Concepts-6th Edition 3.9 @Silberschatz,Korth and Sudarshan

Database System Concepts - 6 3.9 ©Silberschatz, Korth and Sudarshan th Edition And more still create table course ( course_id varchar(8), title varchar(50), dept_name varchar(20), credits numeric(2,0), primary key (course_id), foreign key (dept_name) references department);

Updates to tables Insert insert into instructor values ('10211','Smith','Biology',66000); Delete Remove all tuples from the student relation delete from student Drop Table drop table r Alter alter table r add A D where A is the name of the attribute to be added to relation r and D is the domain of A. All exiting tuples in the relation are assigned null as the value for the new attribute. alter table r drop A where A is the name of an attribute of relation r Dropping of attributes not supported by many databases. Database System Concepts-6th Edition 3.10 @Silberschatz,Korth and Sudarshan

Database System Concepts - 6 3.10 ©Silberschatz, Korth and Sudarshan th Edition Updates to tables Insert insert into instructor values (‘10211’, ’Smith’, ’Biology’, 66000); Delete Remove all tuples from the student relation  delete from student Drop Table drop table r Alter alter table r add A D  where A is the name of the attribute to be added to relation r and D is the domain of A.  All exiting tuples in the relation are assigned null as the value for the new attribute. alter table r drop A  where A is the name of an attribute of relation r  Dropping of attributes not supported by many databases

Basic Query Structure A typical SQL query has the form: select A1,A2,...,A from n,r2,...,Im where P A,represents an attribute R;represents a relation P is a predicate. The result of an SQL query is a relation. Database System Concepts-6th Edition 3.11 @Silberschatz,Korth and Sudarshan

Database System Concepts - 6 3.11 ©Silberschatz, Korth and Sudarshan th Edition Basic Query Structure A typical SQL query has the form: select A1 , A2 , ..., An from r1 , r2 , ..., rm where P Ai represents an attribute Ri represents a relation P is a predicate. The result of an SQL query is a relation