Chapter 4:Advanced SQL SQL Data Types and Schemas Integrity Constraints Authorization Embedded SQL Dynamic SQL Functions and Procedural Constructs** Recursive Queries** Advanced SQL Features** Database System Concepts,5th Edition,Oct 5.2006 4.2 @Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.2 ©Silberschatz, Korth and Sudarshan Chapter 4: Advanced SQL SQL Data Types and Schemas Integrity Constraints Authorization Embedded SQL Dynamic SQL Functions and Procedural Constructs** Recursive Queries** Advanced SQL Features**
Built-in Data Types in SQL date:Dates,containing a(4 digit)year,month and date Example:date 2005-7-27' time:Time of day,in hours,minutes and seconds. Example:time '09:00:30' time‘09:00:30.75 timestamp:date plus time of day Example:timestamp 2005-7-27 09:00:30.75' interval:period of time 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 Edition,Oct 5.2006 4.3 @Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.3 ©Silberschatz, Korth and Sudarshan Built-in Data Types in SQL date: Dates, containing a (4 digit) year, month and date Example: date ‘2005-7-27’ time: Time of day, in hours, minutes and seconds. Example: time ‘09:00:30’ time ‘09:00:30.75’ timestamp: date plus time of day Example: timestamp ‘2005-7-27 09:00:30.75’ interval: period of time 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
Build-in Data Types in SQL (Cont.) Can extract values of individual fields from date/time/timestamp Example:extract (year from r.starttime) Can cast string types to date/time/timestamp Example:cast <string-valued-expression>as date Example:cast <string-valued-expression>as time Database System Concepts,5th Edition,Oct 5.2006 4.4 Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.4 ©Silberschatz, Korth and Sudarshan Build-in Data Types in SQL (Cont.) Can extract values of individual fields from date/time/timestamp Example: extract (year from r.starttime) Can cast string types to date/time/timestamp Example: cast <string-valued-expression> as date Example: cast <string-valued-expression> as time
User-Defined Types create type construct in SQL creates user-defined type create type Dollars as numeric(12,2)final create domain construct in SQL-92 creates user-defined domain types create domain person_name char(20)not null Types and domains are similar.Domains can have constraints,such as not null,specified on them. Database System Concepts,5th Edition,Oct 5.2006 4.5 @Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.5 ©Silberschatz, Korth and Sudarshan User-Defined Types create type construct in SQL creates user-defined type create type Dollars as numeric (12,2) final create domain construct in SQL-92 creates user-defined domain types create domain person_name char(20) not null Types and domains are similar. Domains can have constraints, such as not null, specified on them
Domain Constraints Domain constraints are the most elementary form of integrity constraint.They test values inserted in the database,and test queries to ensure that the comparisons make sense. New domains can be created from existing data types Example:create domain Dollars numeric(12,2) create domain Pounds numeric(12,2) We cannot assign or compare a value of type Dollars to a value of type Pounds. However,we can convert type as below (cast r.A as Pounds) (Should also multiply by the dollar-to-pound conversion-rate) Database System Concepts,5th Edition,Oct 5.2006 4.6 Silberschatz,Korth and Sudarshan
Database System Concepts, 5th Edition, Oct 5. 2006 4.6 ©Silberschatz, Korth and Sudarshan Domain Constraints Domain constraints are the most elementary form of integrity constraint. They test values inserted in the database, and test queries to ensure that the comparisons make sense. New domains can be created from existing data types Example: create domain Dollars numeric(12, 2) create domain Pounds numeric(12,2) We cannot assign or compare a value of type Dollars to a value of type Pounds. However, we can convert type as below (cast r.A as Pounds) (Should also multiply by the dollar-to-pound conversion-rate)