Lecture Database system concepts - Chapter 3: SQL

Chapter 3 focuses on the relational data model, covering the relevant relational algebra and relational calculus. 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 Database System Concepts, 5th Ed. ©Silberschatz, Korth and Sudarshan See ­ for conditions on re­use Chapter 3: SQL s Data Definition s Basic Query Structure s Set Operations s Aggregate Functions s Null Values s Nested Subqueries s Complex Queries s Views s Modification of the Database s Joined Relations** Database System Concepts, 5th Ed., June 2006 3. ©Silberschatz, Korth and Sudarshan History s IBM Sequel language developed as part of System R project at the IBM San Jose Research Laboratory s Renamed Structured Query Language (SQL) s ANSI and ISO standard SQL: q SQL­86 q SQL­89 q SQL­92 q SQL:1999 (language name became Y2K compliant!) q SQL:2003 s Commercial systems offer most, if not all, SQL­92 features, plus varying feature sets from later standards and special proprietary features. q Not all examples here may work on your particular system. Database System Concepts, 5th Ed., June 2006 3. ©Silberschatz, Korth and Sudarshan Data Definition Language Allows the specification of not only a set of relations but also information about each relation, including: s The schema for each relation. s The domain of values associated with each attribute. s Integrity constraints s The set of indices to be maintained for each relations. s Security and authorization information for each relation. s The physical storage structure of each relation on disk. Database System Concepts, 5th Ed., June 2006 3. ©Silberschatz, Korth and Sudarshan Domain Types in SQL s char(n). Fixed length character string, with user­specified length n. s varchar(n). Variable length character strings, with user­specified maximum length n. s int. Integer (a finite subset of the integers that is machine­dependent). s smallint. Small integer (a machine­dependent subset of the integer domain type). s .