The Complete

SQL CREATE TABLE

2009-10-08T15:12:00.003+05:30

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

2009-10-08T14:44:00.004+05:30

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)

2009-10-08T14:16:00.003+05:30

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

2009-10-08T12:54:00.003+05:30

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

2009-10-08T12:47:00.003+05:30

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

2009-10-08T12:37:00.003+05:30

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.

INTRODUCTION TO SQL

2009-07-23T12:51:00.003+05:30

Structured Query Language
SQL is a database language used to create, manipulate and control the access to the Database objects. SQL is a non procedural language used to access relational databases. It is a flexible, efficient language with features designed to manipulate and examine relational data.
SQL is only used for definition and manipulation of database objects. It cannot be used for application development like form definitions, creation of procedures etc...For that you need to necessarily have some 3gl languages such as cobol or 4gl languages such as Dbase to provide front-end support to the database.

Key features of SQL are:
Non procedural language
Unified Language
Common language for all Relational databases. ( Syntax may change between different RDBMS )
SQL is made of Three sub-languages such as:
Data Definition language (DDL)
Data Manipulation language (DML)
Data control language (DCL)
Data Definition Language (DDL): allows you to define database objects at the conceptual level. It consists of commands to create objects and alter the structure of objects, such as tables, views, indexes etc.. Commonly used DDL statements are CREATE, DROP etc..
If you want to create a table Student,then use the following syntax
CREATE TABLE STUDENT ( STUDENT_ID INTEGER PRIMARY KEY,STUDENT_NM VARCHAR(30),COURSE_ID VARCHAR(15) , PHONE VARCHAR(10) , ADDRESS VARCHAR(50) );
To drop a table from the database
DROP TABLE STUDENT;
Data Manipulation language(DML): Allows you to retrieve or update data within a database. It is used for query, insertion, deletion and updating of information stored in databases. Eg: Select, Insert, Update, Delete.
STUDENT_ID STUDENT_NM COURSE_ id PHONE ADDRESS
1001 james Oracle 972-888-9018 888,NorthCentralExp,Dallas,TX- 75089
1002 JIM MSSql Server 972-678-8909 567, Preston Road, Dallas, TX - 75240
1003
BRUCE
Java
214-571-1567
1234, Elm Street, Dallas, TX - 75039
Select statement:Select statement in SQL language is used to display certain data from the table.For example:- if you want to know what course Jim is taking; Select statement fetches you the information you want,when you use the information you have. So,in the above scenario the information you have is student_nm as Jim and and the information you want is course_id, the intersection of those two columns in that table is what you are looking for.
SELECT (what you want)FROM (which tables)WHERE (what you have )
Now the select statement to know the course_id Jim looks like this:
SELECT COURSE_IDFROM STUDENTWHERE STUDENT_NM = 'JIM'
You will get the result as:
COURSE_ID
MSSql Server
If you want to see all the rows in the table then your select will be:
SELECT * FROM STUDENT;
If you would like to show student_nm and address who is attending Oracle course in the form of a report then your select will look like:
SELECT STUDENT_NM, ADDRESSFROM STUENTWHERE COURSE_ID = 'Oracle'
The result will be
STUDENT_NM
ADDRESS
JAMES
888, North Central Exp, Dallas, TX- 75089
Insert Statement
Insert statement is used to insert a new row into the table. For example:- If a new student DAVE is joining Java course then,use the INSERT SQL statement.
INSERT INTO STUDENT (STUDENT_ID, STUDENT_NM, COURSE_ID,PHONE, ADDRESS ) VALUES(1004, 'DAVE', 'Java','972-912-4008', '567, Washington Ave, Dallas - 75543' )
after executing the insert statement,your table should look like below when you issue a select from student table:
STUDENT_ID
STUDENT_NM
COURSE_ID
PHONE
ADDRESS
1001
JAMES
Oracle
972-888-9018
888, North Central Exp, Dallas, TX- 75089
1002
JIM
MSSql Server
972-678-8909
567, Preston Road, Dallas, TX - 75240
1003
BRUCE
Java
214-571-1567
1234, Elm Street, Dallas, TX - 75039
1004
DAVE
Java
972-912-4008
567, Washington Ave, Dallas - 75543
Update Statement
is used to change the existing information in the table.For example:-If DAVE moved to another address then we need to change the ADDRESS column for DAVE's record.If the new address is 146, Dallas Parkway, Dallas - 75240 then your update should be:
UPDATE STUDENT SET ADDRESS = '146, Dallas Parkway, Dallas - 75240'
WHERE STUDENT_NM = 'DAVE'
In order to make sure you updated the Address column for DAVE issue following SQL
SELECT * FROM STUDENT WHERE STUDENT_NM = 'DAVE'
then you should see the following result
STUDENT_ID
STUDENT_NM
COURSE_ID
PHONE
ADDRESS
1004
DAVE
Java
972-912-4008
146, Dallas Parkway, Dallas - 75240
Delete Statement
is used to delete a row from the table ie remove records from the table.For example:JAMES moved to different city, and he does not want to take the course.In order to remove JAMES's record from the table we use the DELETE statement
DELETE STUDENTWHERE STUDENT_NM = 'JAMES'
once you delete the record and you select all the information from the student table you should see the following information:
STUDENT_ID
STUDENT_NM
COURSE_ID
PHONE
ADDRESS
1002
JIM
MSSql Server
972-678-8909
567, Preston Road, Dallas, TX - 75240
1003
BRUCE
Java
214-571-1567
1234, Elm Street, Dallas, TX - 75039
1004
DAVE
Java
972-912-4008
567, Washington Ave, Dallas - 75543
If you dont include where clause in delete statment then it will remove all the rows from the table.
Data control language(DCL)In RDBMS one of the main advantages is the security for the data in the database. You can allow some user to do a specific operation or all operations on certain objects. Examples for DCL statements are GRANT, REVOKE statements.GRANT is used to Grant a permission to an user so that the user can do that operation.REVOKE is used to take back that permission from that user on that object.For example we have two users JAMES and DAVIDIf JAMES created a table called ITEMS then JAMES becomes the owner of that table.DAVID cannot access ITEMS table because he is not the owner of that table.DAVID can access ITEMS if JAMES gives the permission on his table.JAMES can give different types of access like Select, Update, Delete and Insert on ITEMS table to DAVID. For example:- If JAMES wants to provide only Select on ITEMS to DAVID then he can issue: GRANT SELECT ON ITEMS TO JAMESIf JAMES wants to provide only Select and Insert on ITEMS to DAVID then he can issue: GRANT SELECT, INSERT ON ITEMS TO JAMESIf JAMES wants to provide all the operations on ITEMS to DAVID then he can issue: GRANT ALL ON ITEMS TO JAMESOnce you provide all permissions on an object to an user then indirectly he becomes the owner and can do any manipulation to the table.

DISTRIBUTED VERSUS CENTRALIZED DATABASE

2009-07-09T15:19:00.006+05:30

DISTRIBUTED DATABASE VS CENTRALIZED DATABASE
I have extracted some key phrases on Why we use a distributed databaseover a centralized one:
"A big advantage of distributed DBMSs over centralized ones is that ofscalability. Growth can be sustained more gracefully in a distributed system."

"Local autonomy is another reason for a business to implement a DDBMS.Since the nature of today's applications usually require data ingeographic areas that are often decentralized, it often makes sense to implement a distributed system. In this way, data can physically reside nearest to where it is most often accessed, thus providing users with local control of data that they interact with."

"Another reason why one might want to consider a parallel architectureis to improve reliability and availability of the data in a scalablesystem. In a distributed system, with some careful tact, it is possible to access some, or possibly all of the data in a failure mode if there is sufficient data replication."

Major features of a DDB are:
* Data stored at a number of sites, each site logically singleprocessor
* Sites are interconnected by a network rather than a multiprocessor configuration
* DDB is logically a single database (although each site is adatabase site)
* DDBMS has full functionality of a DBMS
* To the user, the distributed database system should appearexactly like a non- Distributed database system.

Advantages of distributed database systems are:
* local autonomy (in enterprises that are distributed already)
* improved performance (since data is stored close to where neededand a query may be split over several sites and executed in parallel)
* improved reliability/availability (should one site go down)
* economics
* expandability
* shareability

Disadvantages of distributed database systems are:
* complexity (greater potential for bugs in software)
* cost (software development can be much more complex and therefore costlAlso,exchangeof messages and additional computations involve increased overheads)
* distribution of control (no single database administratorcontrols the DDB)
* security (since the system is distributed the chances ofsecurity lapses are greater)
* difficult to change (since all sites have control of their ownsites)
* lack of experience (enough experience is not available indeveloping distributed systems)

2009-06-04T15:14:00.002+05:30

Concepts of Relational database system
To understand the basic concepts of RDBMS,the following information is provided to you.
Table is the place where you can save all your data in the relational model(RDBMS). Data is organized in terms of Rows and columns in a table. For Example, see the information we have here in employee table.
EMPLOYEE TABLE : (With out Relational Model)
-------------------------------------------------------------------------------------------------
EMPNO ENAME DEPTNO DNAME LOC SAL COMM
-------- ---------------------------------------------------------------------------------------
7000 KING 10 MARKETING DALLAS 5000.00 300.00
7001 ALEX 20 DEVELOPMENT NY 3500 100
7002 CHRIS 10 MARKETING DALLAS 4000 100
7003 STEVE 20 DEVELOPMENT NY 4000 200
7004 KELLY 30 HR LA 2000 50
7005 JONES 30 HR LA 1700 40
In the above example,we have 6 employee records. 2 employees for each department.Take a look at it and see how many times we have the same information ie duplicate information.The departments are occuring twice and so more memory space is occupied. To calculate this,
MARKETING 9 BYTES
DEVELOPMENT 10 BYTES
HUMAN RESOURCES 9 BYTES
DALLAS 6 BYTES
NY 2 BYTES
LA 2 BYTES
TOTAL 28 BYTES
For 6 records we wasted 26 bytes, Imagine having 5000 employees working in thecompany! Then we are wasting 28 * 5000 bytes = 140000bytes,which is around 13 MB of memory.
Advantages of the new system(Relational model):
If you split the above shown data into two different tables then it becomes easy to maintain the data, For example:- If a particular department moved from Dallas to Atlanta,we need to change the city table for deptno 10 in the new system where as in the previous system we have to update all the employee records whose department is deptno 10.
removes the duplicate information (Data Redundancy) instead of duplicating the Department name and Location for every employee record as seen in the previous system,You can see Department name and Location only once in the Department table based on the common column in the new system .
Reduces the Memory space in the database
Employee Table
EMPNO ENAME DEPTNO SAL COMM
7000 KING 10 5000 300
7001 ALEX 20 3500 100
7002 CHRIS 10 4000 100
7003 STEVE 20 4000 200
7004 KELLY 30 2000 50
7005 JONES 30 1700 40
Department Table
DEPTNO DNAME LOC
10 MARKETING DALLAS
20 DEVELOPMENT NY
30 HUMAN RESOURCES LA
We have the same information now as before but the difference is we have two tables instead of one table.To relate the two tables, we need a common column between the tables. In the above example, there is a common column (DEPTNO).

2009-06-04T15:07:00.002+05:30

Introduction to RDBMS
DBMSConcepts of RDBMS:This part gives you details about the basic concepts like how the data is viewed in Relational Database management system and advantages of RDBMS over other systems.
Codd's rules: defines the relational completeness of a DBMS.
Primary keys: Any column or a set of columns that uniquely identify rows of a Table.
Entity integrity: The primary key of a table should not be partially or wholly NULL.
Referential integrity: No foreign key value may reference primary key that does not exist.
Normalization: It is a technique that logically groups the data over a number of tables, which are independent and contain no unnecessary data.
Structured Query language(SQL): It is a high level, set oriented, interactive database language for RDBMS. Used for definition and manipulation of database objects.

Introduction to DBMS

2009-06-02T15:47:00.001+05:30

Database Management System

is a software package where you maintain and control the information to run the business and to do the day to day operations in an organized and user friendly method. In the DBMS we can back up the data, implement the security so that certain information can be modified or viewed by certain people working in the organization. In this section we learn the main concepts which we must follow while working in DBMS.
In simple words, a Database Management System is a Software program which makes the task easier, the task being adding, deleting, modifying and maintain theinformation.
Here are some database terms and concepts.
Database Concepts
Data
Databases
Entity, Attribute and Data
Entity Relationships
Data Models
DataData is information about some thing. Data gets differed from business to business,FOR EXAMPLE:- For a bus transportation company BUS, DRIVERS, CITY, SEATS OCCUPIED are data, for a computer hardware company MONITOR, CPU, HARD DISK are data.
DatabasesDatabase is an area where you save the data needed to run the business. Database provides the following features like Sharing Data,enforcing Business rules,Data security which can be applied centrally
Entity, Attribute and DataAn Entity is an object which has some attributes, FOR EXAMPLE:- A employee in an organization has a name and a particular Job.Here EMPLOYEE is an ENTITY, NAME and JOB are ATTRIBUTES. Entity - EMPLOYEEAttribute - EMP_NAME and JOBData - Name of Employee like (Mike Modano) , his Job is like (Marketing).
Entity RelationshipsDatabase contains many Entities (Tables), we can relate an entity to another,FOR EXAMPLE:- In an organization we have many departments and many employees working , here we have two entities one is EMPLOYEE and the other is DEPARTMENT. So how do we relate these two entities?We can RELATE these two entities as:-In one department there can be many employees.
The relationship between two different entities can be classified as:
1.ONE to ONE example PERSON - PASSPORT ( one individual can have only one passport)
2.ONE to MANY example DEPARTMENT - EMPLOYEE ( In one department there can be many employees working)
3.MANY to MANY example DOCTOR - PATIENT ( A doctor can have many patients and a patient can have many doctors )