SQL CREATE TABLE

CREATE TABLE
Tables are the basic structure where data is stored in the database. Given that in most cases, there is no way for the database vendor to know ahead of time what your data storage needs are, chances are that you will need to create tables in the database yourself. Many database tools allow you to create tables without writing SQL, but given that tables are the container of all the data, it is important to include the CREATE TABLE syntax in this tutorial.

Before we dive into the SQL syntax for CREATE TABLE, it is a good idea to understand what goes into a table. Tables are divided into rows and columns. Each row represents one piece of data, and each column can be thought of as representing a component of that piece of data. So, for example, if we have a table for recording customer information, then the columns may include information such as First Name, Last Name, Address, City, Country, Birth Date, and so on. As a result, when we specify a table, we include the column headers and the data types for that particular column.

So what are data types? Typically, data comes in a variety of forms. It could be an integer (such as 1), a real number (such as 0.55), a string (such as 'sql'), a date/time expression (such as '2000-JAN-25 03:22:22'), or even in binary format. When we specify a table, we need to specify the data type associated with each column (i.e., we will specify that 'First Name' is of type char(50) - meaning it is a string with 50 characters). One thing to note is that different relational databases allow for different data types, so it is wise to consult with a database-specific reference first.

The SQL syntax for CREATE TABLE is
CREATE TABLE "table_name"("column 1" "data_type_for_column_1","column 2" "data_type_for_column_2",... )
So, if we are to create the customer table specified as above, we would type in
CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date) ;

Sometimes, we want to provide a default value for each column. A default value is used when you do not specify a column's value when inserting data into the table. To specify a default value, add "Default [value]" after the data type declaration. In the above example, if we want to default column "Address" to "Unknown" and City to "Mumbai", we would type in
CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50) default 'Unknown',
City char(50) default 'Mumbai',
Country char(25),
Birth_Date date) ;
You can also limit the type of information a table / a column can hold. This is done through the CONSTRAINT keyword. The constraint concept is posted seperately in the blog

SQL PRIMARY KEY

PRIMARY KEY

A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself , or it can be an artificial field (one that has nothing to do with the actual record). A primary key can consist of one or more fields on a table. When multiple fields are used as a primary key, they are called a composite key.
Primary keys can be specified either when the table is created (using CREATE TABLE) or by changing the existing table structure (using ALTER TABLE).

The following is the syntax to implement a primary key constraint on a column while creating tables:

CREATE TABLE Customer (SID integer PRIMARY KEY, Last_Name varchar(30), First_Name varchar(30));

The syntax to add a primary key by a altering table is given below

ALTER TABLE Customer ADD PRIMARY KEY (SID);

Note: Before using the ALTER TABLE command to add a primary key, you'll need to make sure that the field is defined as 'NOT NULL' -- in other words, NULL cannot be an accepted value for that field.

SQL CONSTRAINTS(NOT NULL,CHECK,UNIQUE)

CONSTRAINTS
When you bind a business rule to a column in the table then those rules are called the Constraints. Constraints are defined while creating the table. Say for example, you cannot have an employee who does not have a name, then employee name column in employee table should be a NOT NULL column. The NOT NULL is a constraint.
The constraint types and short descriptions are given below:
Constraint Types And Description
NOT NULL:-
you must provide the value in that column. you cannot leave that column blank
PRIMARY KEY:-
No duplicate values allowed, for example Empno in Employee table should be unique
CHECK:-
checks the value and controls the inserting and updating values.
DEFAULT:-
Assigns a default value if no value is given.
-------------------------------------------------------------------------------------------------
NOT NULL:-
If we have a business rule saying that all customers should have a name, we cannot have any customer with out a name. So to implement that business rule we can create customer table and specify customer name column as NOT NULL (constraint)
Example
CREATE TABLE EMPLOYEE (EMPNO NUMBER(4) PRIMARY KEY, ENAME VARCHAR(4) NOT NULL);
-----------------------------------------------------------------------------------------------
CHECK
Check constraint is used where we define a condition on a column. Check constraint consists of the keyword
col_name datatype CHECK (col_name in(value1, value2))
Example
If you have a business rule saying that all employees in the organization should get atleast $500 then we can use CHECK constraint while creating table.
CREATE TABLE EMPLOYEE ( EMPNO NUMBER(4) PRIMARY KEY, ENAME VARCHAR(4) NOT NULL, SALARY NUMBER(7,2) CHECK (SALARY > 500) );
-----------------------------------------------------------------------------------------------------------------
DEFAULT
While inserting a row into a table without giving values for every column, SQL must insert a default value to fill in the excluded columns, or the command will be rejected. The most common default value is NULL. This can be used with columns not defined with a NOT NULL.
Default value assigned to a column while creating the table using
CREATE TABLE operation.
Example
CREATE TABLE ITEM (ITEM_ID NUMBER(4) PRIMARY KEY, ITEM_NAME VARCHAR(15),ITEM_DESC VARCHAR(100), QOH NUMBER(4) DEFAULT 100);
Assigning a default value 0 for numeric columns makes the computation.
-------------------------------------------------------------------------------------------------
UNIQUE:-
The values entered into a column are unique ie no duplicate values exists.This constraint ensures business that there is no duplicates allowed.

SQL WHERE

Next, we might want to conditionally select the data from a table. For example, we may want to only retrieve stores with sales above $1,000. To do this, we use the WHERE keyword. The syntax is as follows:
SELECT "column_name"FROM "table_name"WHERE "condition"
For example, to select all stores with sales above $1,000 in Table Store_Information,
Table Store_Information:
-----------------------------------------------------------
store_name Sales Date
------------------------------------------------------------
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
------------------------------------------------------------
we key in,
SELECT store_nameFROM Store_InformationWHERE Sales > 1000;
Result:
store_name
Los Angeles

SQL DISTINCT

The SELECT keyword allows us to grab all information from a column (or columns) on a table. This, of course, necessarily mean that there will be redundancies. What if we only want to select each DISTINCT element? This is easy to accomplish in SQL. All we need to do is to add DISTINCT after SELECT. The syntax is as follows:
SELECT DISTINCT "column_name"FROM "table_name"
For example, to select all distinct stores in Table Store_Information,
Table Store_Information
----------------------------------------------------------------
store_name Sales Date
----------------------------------------------------------------
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
-----------------------------------------------------------------
we key in,
SELECT DISTINCT store_name FROM Store_Information;
Result:
store_name(column name)
Los Angeles
San Diego
Boston

SQL SELECT

What do we use SQL commands for? A common use is to select data from the tables located in a database. Immediately, we see two keywords: we need to SELECT information FROM a table. (Note that a table is a container that resides in the database where the data is stored). Hence we have the most basic SQL structure:
SELECT "column_name" FROM "table_name"
To illustrate the above example, assume that we have the following table:
Table Store_Information(TABLE NAME)
----------------------------------------------------------
store_name Sales Date
----------------------------------------------------------
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
------------------------------------------------------------
We shall use this table as an example throughout the tutorial (this table will appear in all sections). To select all the stores in this table, we key in,
SELECT store_name FROM Store_Information
Result:
store_name(column Name)
Los Angeles
San Diego
Los Angeles
Boston
Multiple column names can be selected, as well as multiple table names.