Laboratory Exercise 6
Data Types in SQL
CMP 420: Database Systems
Lehman College, City University of New York
20 March 2002
Data Types in SQL
Each implementation of SQL has slightly different types. The system
we're using in lab, Postgres, has a rich set of native data types
available to users. Most of these agree with the SQL standards presented
in the textbook. Today's lab focuses on using different types of data
in PostGreSQL.
Basic Types
Using the list of data types at
http://www.us.postgresql.org/users-lounge/docs/6.5/user/datatype.htm,
find the Postgres equivalent to the following standard types:
- integer
- boolean
- character(10)
Serial Types
It is often useful for every row of your table to have a unique number.
For example, the orders table, from the sample database used in
Lab 3 assigns to every new tuple a unique number
that serves as the order number. It would be tedious to assign the order
numbers manually. So, PostgreSQL has a built in type called serial
which assigns to the attribute the next available number, thus creating a
unique identifier for the row. What other tables in the sample database
use the serial datatype?
Types for Dates and Times
There are many ways to represent dates and times in PostgreSQL. A list of the available types can be found at
http://www.us.postgresql.org/users-lounge/docs/6.5/user/x774.htm.
There are two basic kinds provided by
Postgres: absolute clock times and relative time intervals. The first
is used for measuring the actual time or date when something occured. The
latter is used for measuring elapsed time.
Postgres supplies two primary user-oriented date and time types,
datetime and timespan, as well as the related SQL92 types
timestamp, interval, date and time.
Other date and time types are also available, mostly for
historical reasons.
There are built-in functions for working with dates: http://www.us.postgresql.org/users-lounge/docs/6.5/user/x2345.htm.
Note in this page, that 'now' gives the current date and time (according
to the system clock).
For example, say you wanted all orders placed in the first 6 months of the
year from the sample database from Lab 3. To
do this, you can use the date_part function to find the month
and use that in the conditional of the query:
SELECT *
FROM orders
WHERE date_part('hour',ord_placed) <= 6;
Similarly, you could also find all orders that were paid for in the same
year they were ordered:
SELECT *
FROM orders
WHERE date_part('year',ord_placed) = date_part('year',ord_paid);
Using the Different Types
Create a database for use in today's lab. Now, create a table to hold
information about library books. This table should have:
- a unique ID for each book (hint: use the serial data type),
- the title of the book,
- the year the book was published,
- the date the book was last checked out (if it was never checked
out, this field can be NULL),
- the date the book is due back to the library (if it was never
checked out, this field can be NULL), and
- the name of the borrower of the book (if it was never
checked out, this field can be NULL).
Add (by hand) several sample rows to your table. Write the following
queries to demonstrate your database:
- select all books published before 2000.
- select all books that have been checked out for more than 2 weeks.
- give the ID of all books that are due in less than 1 week (from
today or now).
Final Comments
Before leaving the lab, you need to log out and shut down your
computer. First, you should quit all applications. Then,
go to the K menu in the bottom left hand corner and
select Log Out. Choose the Shut Down option,
and shut off the monitor when you're done.
Remember to pick up any files you sent to the printer and to
take any personal belongings. It is difficult to get back
into the lab later (since it's locked when not in use), so,
it's worth making sure you haven't forgotten anything.