{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

ch4 - Chapter4:AdvancedSQL ,5thEd...

Info icon This preview shows pages 1–7. Sign up to view the full content.

View Full Document Right Arrow Icon
Database System Concepts, 5th Ed . © Silberschatz, Korth and Sudarshan See  www.db-book.com  for conditions on re-use  Chapter 4: Advanced SQL Chapter 4: Advanced SQL
Image of page 1

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
© Silberschatz, Korth and Sudarshan 4.<number> Database System Concepts, 5 th  Ed., July  2005 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**
Image of page 2
© Silberschatz, Korth and Sudarshan 4.<number> Database System Concepts, 5 th  Ed., July  2005 Built-in Data Types in SQL  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
Image of page 3

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
© Silberschatz, Korth and Sudarshan 4.<number> Database System Concepts, 5 th  Ed., July  2005 Build-in Data Types in SQL (Cont.) 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
Image of page 4
© Silberschatz, Korth and Sudarshan 4.<number> Database System Concepts, 5 th  Ed., July  2005 User-Defined Types 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.
Image of page 5

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
© Silberschatz, Korth and Sudarshan 4.<number> Database System Concepts, 5 th  Ed., July  2005 Domain Constraints 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
Image of page 6
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern