Introduction to Structured Query Language

Introduction to Structured Query Language

SQL Constraints

SQL constraints are used to specify rules for the data in a table.

If there is any violation between the constraint and the data action, the action is aborted by the constraint.

Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).

SQL CREATE TABLE + CONSTRAINT Syntax

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

In SQL, we have the following constraints:

  • NOT NULL – Indicates that a column cannot store NULL value
  • UNIQUE – Ensures that each row for a column must have a unique value
  • PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly
  • FOREIGN KEY – Ensure the referential integrity of the data in one table to match values in another table
  • CHECK – Ensures that the value in a column meets a specific condition
  • DEFAULT – Specifies a default value when specified none for this column

Creating the Orders Table

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
Customer_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (Customer_Id)
REFERENCES Customer(Customer_Id)
)

Basics of the SELECT Statement

In a relational database, data is stored in tables. An example table would relate Social Security Number, Name, and Address

EmployeeAddressTable

SSNFirstNameLastNameAddressCityState
512687458JoeSmith83 First StreetHowardOhio
758420012MaryScott842 Vine Ave.LosantivilleOhio
102254896SamJones33 Elm St.ParisNew York
876512563SarahAckerman440 U.S. 110UptonMichigan

Now, let’s say you want to see the address of each employee. Use the SELECT statement, like so:

SELECT FirstName, LastName, Address, City, State 
FROM EmployeeAddressTable;

The following is the results of your query of the database:

First NameLast NameAddressCityState
JoeSmith83 First StreetHowardOhio
MaryScott842 Vine Ave.LosantivilleOhio
SamJones33 Elm St.ParisNew York
SarahAckerman440 U.S. 110UptonMichigan

To explain what you just did, you asked for the all of data in the EmployeeAddressTable, and specifically, you asked for the columns called FirstName, LastName, Address, City, and State. Note that column names and table names do not have spaces…they must be typed as one word; and that the statement ends with a semicolon (;). The general form for a SELECT statement, retrieving all of the rows in the table is:

SELECT ColumnName, ColumnName, ... 
FROM TableName;

To get all columns of a table without typing all column names, use:

SELECT * FROM TableName;
Walid BaniHani

 

College of Applied Sciences - Al Rustaq

Leave a Reply

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