TAILIEUCHUNG - Lecture Database system concepts (5/e): Chapter 4 - Silberschatz, Korth, Sudarshan 

In this chapter, we provided detailed coverage of the basic structure of SQL. In this chapter, we cover some of the more advanced features of SQL. We address the issue of how to access SQL from a general-purpose programming language, which is very important for building applications that use a database to store and retrieve data. | Chapter 4: Advanced SQL 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:’ timestamp: date plus time of day Example: timestamp ‘2005-7-27 09:00:’ 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 ) Can cast string types to date/time/timestamp Example: cast as date Example: cast 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. 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 as Pounds) (Should also multiply by the dollar-to-pound conversion-rate) Large-Object Types Large objects (photos, videos, CAD files, etc.) are stored as a large object: blob: binary large object -- object is a large collection of .

• Cơ sở dữ liệu
• Database system
• Lecture Database system
• Relational databases
• Database design
• Advanced SQL
• SQL data types
• Database system concepts
• Lecture Database system concepts
• Database systems
• Database languages
• Database system architectures
• Relational database design
• Database system architecture
• File based approach
• Data management systems framework
• Recovery system
• Lecture Database systems
• Database architecture
• Database approach
• Database design process
• Query optimization
• Transaction state
• Concurrency control
• Data analysis
• Information retrieval
• Parallel databases
• Distributed databases
• Advanced application development
• Advanced data types
• Advanced transaction processing
• Relational model
• Basic query structure
• Dynamic SQL
• Tuple relational calculus
• Entity relationship model
• Application design
• Object­ based databases