CREATE Table

Creating a Table in SQL

Once a database has been created, the next step is to define the relations (tables) that will store the data. Each table is built by specifying its columns, the type of data each column can hold, and any restrictions (constraints) on the values in those columns. This is done using the CREATE TABLE statement.

The general syntax is:

CREATE TABLE tablename(
    attributename1 datatype constraint,
    attributename2 datatype constraint,
    ...
    attributenameN datatype constraint
);

Every CREATE TABLE statement ends with a semicolon (;). If you type an incomplete statement, the SQL shell shows a continuation prompt (->) and waits for you to finish.

Key Points About the CREATE TABLE Statement

• Degree of a relation: The number of columns (attributes) in a table is called the degree of that relation. It is denoted by N.
• Attribute name: This is simply the name you give to a column in the table.
• Datatype: This specifies what kind of data the attribute can store — for example, numbers, text, or dates.
• Constraint: This imposes restrictions on the values that can be entered into an attribute. By default, every attribute is allowed to hold NULL values (meaning no value is entered), except for the primary key.

Choosing Data Types and Constraints: An Example

To understand how to choose data types and constraints, consider the STUDENT table. The textbook walks through the reasoning for each attribute:

• RollNumber: Since a class has at most 100 students, and roll numbers run from 1 to 100, three digits are enough. So INT is the right data type. This attribute will be the primary key.
• SName: Student names vary in length. Assuming a maximum of 20 characters, VARCHAR(20) is used. It must not be left empty, so the NOT NULL constraint is applied.
• SDateofBirth: This stores a date, so the data type is DATE. It also cannot be null.
• GUID: This is the guardian's 12-digit Aadhaar number. Since it is a fixed-length number and no mathematical operations will be performed on it, CHAR(12) is chosen. It acts as a foreign key linking to the GUARDIAN table.

The textbook provides similar reasoning for the GUARDIAN and ATTENDANCE tables. Here is a summary of the chosen data types and constraints for all three relations:

Table: STUDENT

Table: GUARDIAN

Table: ATTENDANCE

A Simple Example: Creating the STUDENT Table …