Creation of Base Tables

The statement for table creation is  CREATE TABLE:

  1. CREATE TABLE < name of the TABLE > (< list of COLUMN specifications AND constraints >);

A column specification includes the column name and the data type valid for the column. Furthermore, certain constraints can be defined not only via extra specifications, but within the column specification itself. Examples are the (non-compound) primary and foreign key constraints, as well as the NOT NULL constraint.

We will study the table-related aspects of the language using the educational databases.

The Product table of the Computer firm database consists of three columns maker, model, and type; all of them have the string data type VARCHAR(N). To create this table, we could use the following statement:

  1. CREATE TABLE Product (maker varchar(10), model varchar(50), type varchar(50));

N is the maximal number of characters the data in the corresponding column can contain. VARCHAR is a variable-length type; this means that, if we specify a value with less than N characters, the very number of characters specified will be stored in the system. Alternatively, the fixed-length string type CHAR(N) can be used, which is right-padded with spaces to the specified length when stored. In other words, there are always exactly N characters stored in the system for this data type.

If N is not specified, it is assumed to be 1 (single character) by default.

As soon as the table has been created, data can be written to it using the INSERT statement . Lets do that: 

  2. ('A', '1232', 'PC'),
  3. ('A', '1232', 'Printer'),

The data has been successfully added, but it seems to be not quite right somehow. First, its not clear whether the model 1232 is a printer or a PC. Then, we have another model, about which nothing is known at all.

Here we need to make a short digression to emphasize that by creating tables, we establish a relational model of the subject area. Our subject area is stock management of a computer company. To make sure the data model is adequate to the subject area, the tables have to be defined in such a way that things happening to objects in the real world can be simulated by the model, while things not possible in the subject area shouldnt occur in the model, either.

Thus, in reality a model cant be a printer and a PC at the same time, as it turned out to be in our case. This is where we approach the concept of data integrity and its enforcement by applying constraints.

Bookmark and Share
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 More tags
The book was updated
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100