tailieunhanh - Lecture Database system concepts - Chapter 4: Advanced SQL

Chapter 4 focuses on the most influential of the user-oriented relational languages: SQL. The main contents of this chapter include all of the following: SQL data types and schemas, integrity constraints, authorization, embedded SQL, dynamic SQL, functions and procedural constructs, recursive queries, advanced SQL features. | Chapter 4: Advanced SQL Database System Concepts, 5th Ed. ©Silberschatz, Korth and Sudarshan See ­ for conditions on re­use Chapter 4: Advanced SQL s SQL Data Types and Schemas s Integrity Constraints s Authorization s Embedded SQL s Dynamic SQL s Functions and Procedural Constructs** s Recursive Queries** s Advanced SQL Features** Database System Concepts, 5th Ed., July 2005 4. ©Silberschatz, Korth and Sudarshan Built­in Data Types in SQL s date: Dates, containing a (4 digit) year, month and date q Example: date ‘2005­7­27’ s time: Time of day, in hours, minutes and seconds. q Example: time ‘09:00:30’ time ‘09:00:’ s timestamp: date plus time of day q Example: timestamp ‘2005­7­27 09:00:’ s interval: period of time q q q Example: interval ‘1’ day Subtracting a date/time/timestamp value from another gives an interval value Interval values can be added to date/time/timestamp values Database System Concepts, 5th Ed., July 2005 4. ©Silberschatz, Korth and Sudarshan Build­in Data Types in SQL (Cont.) s Can extract values of individual fields from date/time/timestamp q Example: extract (year from ) s Can cast string types to date/time/timestamp q Example: cast as date q Example: cast as time Database System Concepts, 5th Ed., July 2005 4. ©Silberschatz, Korth and Sudarshan User­Defined Types s create type construct in SQL creates user­defined type create type Dollars as numeric (12,2) final s create domain construct in SQL­92 creates user­defined domain types create domain person_name char(20) not null s .