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
Create Table Construct An SQL relation is defined using the create table command: create table r (A1 D1:A2 D2:...An Dn (integrity-constraint ) (integrity-constraint)) ris 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-7th Edition 3.7 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 3.7 ©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 Types of integrity constraints 。primary key(A,,An) foreign key (Am,...An)references r 。not null ■ SQL prevents any update to the database that violates an integrity constraint. ■ 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), Database System Concepts-7th Edition 3.8 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 3.8 ©Silberschatz, Korth and Sudarshan th Edition Integrity Constraints in Create Table ▪ Types of integrity constraints • primary key (A1 , ..., An ) • foreign key (Am, ..., An ) references r • not null ▪ SQL prevents any update to the database that violates an integrity constraint. ▪ 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);
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); Database System Concepts-7th Edition 3.9 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 3.9 ©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);
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-7th Edition 3.10 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 3.10 ©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 radd 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-7th Edition 3.11 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 3.11 ©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