SQL: Basic concepts - University of Edinburgh

SQL: Basic concepts

? SQL operates with tables, so the first thing to do is create tables. ? Syntax:

CREATE TABLE ( type, ..., type>)

? For example:

CREATE TABLE Movies (title char(20), director char(10), actor char(10))

CREATE TABLE Schedule (theater char(10), title char(20))

Database Systems

1

L. Libkin

Types

? char(n) ? fixed length string of exactly n characters. Example: 'Polanski'

? varchar(n) ? variable length string of up to n characters. Example: 'Polanski'. What's the difference? We'll see soon. Note: varchar is actually an abbreviation for char varying.

? bit(n) ? fixed length bit string of exactly n bits. Example: B'0101', X'C1'

? bit varying(n) ? variable length bit string of up to n bits.

Database Systems

2

L. Libkin

Types cont'd

? int ? signed integer (4 bytes) ? smallint ? signed integer (2 bytes) ? real ? real numbers. ? In fact, there is a general float type float(s), and real is float(s)

where s is implementation defined. ? SQL has many more types, such as date, time, timestamp, character

sets in different alphabets, etc.

Database Systems

3

L. Libkin

Types cont'd: Dates and Times

? date type: keyword DATE followed by a date in an appropriate form, e.g. DATE '2001-12-14'

? time type: keyword TIME followed by a string representing time; SQL uses the 24-hour clock.

? timestamp type: combines date and time. For example, TIMESTAMP '2001-12-14 11:28:00' is 11:28am on December 14, 2001.

? Operations on these types: they can be compared for equality, and for order. If for two dates d1 and d2 we have d1 < d2, then d1 is earlier than d2.

Database Systems

4

L. Libkin

Populating tables

? General syntax:

INSERT INTO VALUES (...) ? Examples:

INSERT INTO Movies VALUES (`Chinatown', `Polanski', `Nicholson')

INSERT INTO Schedule VALUES (`Odeon', `Chinatown')

? More generally, one can use other queries for insertion:

INSERT INTO Name (SELECT ... FROM ... WHERE ...)

as long as the attributes in the result of the query as the same as those of Name.

Database Systems

5

L. Libkin

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download