Introduction to Structured Query Language

Introduction to Structured Query Language

This page is a introductory tutorial of the Structured Query Language (also known as SQL) and is a pioneering effort on the World Wide Web, as this is the first comprehensive SQL tutorial available on the Internet. SQL allows users to access data in relationalf database management systems, such as Oracle, Sybase, Informix, Microsoft SQL Server, Access, and others, by allowing users to describe the data the user wishes to see. SQL also allows users to define the data in a database, and manipulate that data. This page will describe how to use SQL, and give examples. The SQL used in this document is “ANSI”, or standard SQL, and no SQL features of specific database management systems will be discussed until the “Nonstandard SQL” section. It is recommended that you print this page, so that you can easily refer back to previous examples.

Table Components

Table-name and Column-name

Where table-name and column-name are the names used and offered by you for the table and its columns, type stands for the kind of data to be stored in each column or field and size gives the maximum size of each field in terms of characters wide.

There is a wide range of data types available for use with Relational Database Management System (RDBMS). A few of the most commonly used data types and those that you will learn to use in this unit follow.

NOT NULL

This is optional and can be omitted. A column can be defined as NOT NULL. Use of this option means that the data must include a value for that column. If a column is defined as NOT NULL, and no value is given when inserting data, then an error will occur. The row will not be stored. Primary key columns (see below) must be defined as NOT NULL. For further information, see the discussion on null values later in this section.

PRIMARY KEY

This part of the command specifies the column, or columns, that make up the primary key. In a relational database all tables must have one, and only one, primary key (PK) defined. Should a table have more than one column that makes up the primary key then a comma separates them.

For example: PRIMARY KEY (student_number, unit_number).

FOREIGN KEY

This part of the command specifies the column, or columns, that make up the foreign key (FK). In a relational database a foreign key is the primary key of the parent table that links or joins tables together and enforces referential integrity between tables. To illustrate how and why we define foreign keys in database we are going to use the department and employee tables; see below.

Foreign key
Database Foreign key

Foreign Key Constraint

The foreign key constraint:

  • will reject an insert or update of a value if a corresponding value does not currently exist in the master table
  • will reject a delete if it would invalidate a references constraint; for example, a department may not be removed from the department table if there are any employees from the employee table who belong to that department
  • must reference a primary key column or group of columns in the master table
  • requires that the data type of the foreign key column(s) and constraint column(s) match.

Using the Create Table Command

CREATE TABLE table_name
(
column_name1 data_type size,
column_name2 data_type size,
column_name3 data_type size,
....
);

The column_name parameters specify the names of the columns of the table.

The data_type parameter specifies what type of data the column can hold (e.g. varchar, integer, decimal, date, etc.).

The size parameter specifies the maximum length of the column of the table.

The column_name parameters specify the names of the columns of the table.

The data_type parameter specifies what type of data the column can hold (e.g. varchar, integer, decimal, date, etc.).

The size parameter specifies the maximum length of the column of the table.

Rules for table and column names:

  • Can include letters and numbers but must begin with a letter.
  • Cannot include spaces. However, if you wish to represent a space use an underscore (_). For example: employee_number.
  • Cannot be a reserved word used in SQL, e.g. TABLE.
  • Cannot be the name of an SQL command, e.g. CREATE.
Walid BaniHani

 

College of Applied Sciences - Al Rustaq

Leave a Reply

Your email address will not be published. Required fields are marked *