SQL PRIMARY KEY Constraint

 The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.
SQL PRIMARY KEY Constraint on CREATE TABLE:
The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is created:
SQL Server / Oracle / MS Access:                                                                                   CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
MySQL / SQL Server / Oracle / MS Access:                                                                  CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
MySQL:                                                                                                                              CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
SQL PRIMARY KEY Constraint on ALTER TABLE
MySQL / SQL Server / Oracle / MS Access:                                                                      ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)
To DROP a PRIMARY KEY Constraint
MySQL / SQL Server / Oracle / MS Access:                                                                            ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.
The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. The following SQL creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is created:
SQL Server / Oracle / MS Access:                                                                                  CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
MySQL / SQL Server / Oracle / MS Access:                                                                      CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)
MySQL                                                                                                                              CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)
SQL UNIQUE Constraint on ALTER TABLE
For single Col-    ALTER TABLE Persons ADD UNIQUE (P_Id)
For multi Col- ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
For Drop:
  MySQL- ALTER TABLE Persons DROP INDEX uc_PersonID
  SQL Server / Oracle / MS Access- ALTER TABLE Persons DROP CONSTRAINT uc_PersonID

SQL NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values.
The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.
The following SQL enforces the "P_Id" column and the "LastName" column to not accept NULL values:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

The SQL Constraints

Constraints are used to limit the type of data that can go into a table.
Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).
We will focus on the following constraints:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT

The ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
Syntax: 
To add-  ALTER TABLE <table_name> ADD <column_name>  datatype
To delete a col-  ALTER TABLE table_name DROP COLUMN column_name
To chng datatyp of col-  ALTER TABLE table_name ALTER COLUMN column_name datatype

The CREATE TABLE Statement

The CREATE TABLE statement is used to create a table in a database.
Syntax:
CREATE TABLE <table_name>
(
<column_name1> <data_type>,
<column_name2> <data_type>,
<column_name3> <data_type>,
....
)
Example:
 Now we want to create a table called "Persons" that contains five columns: P_Id, LastName, FirstName, Address, and City.
Query:
CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
Output:
The empty "Persons" table will now look like this:

P_Id    LastName    FirstName    Address    City      
                   

The CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a database.
Syntax:           CREATE DATABASE <database_name>
Example:         CREATE DATABASE my_db

Parts ofSQL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).
The query and update commands form the DML part of SQL:
SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
The DDL part of SQL permits database tables to be created or deleted. It also defines indexes (keys), specifies links between tables, and imposes constraints between tables. The most important DDL statements in SQL are:

CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index

can i put Semicolon after SQL Statements?

Some database systems require a semicolon at the end of each SQL statement.
Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.
We are using MS Access and SQL Server 2000 and we do not have to put a semicolon after each SQL statement, but like MySQL database programs force you to use it.

What Can SQL do?

SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
SQL can create stored procedures in a database
SQL can create views in a database
SQL can set permissions on tables, procedures, and views
NOTE:   SQL is not case sensitive

What is SQL?

SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL is an ANSI (American National Standards Institute) standard