SQL Tutorial:
Welcome to SQL toturial. In this toturial you will learn about different SQL commands.There are many database (MS-Access, MS-Server, Oracle, MYSQL) that use the SQL. Each database has its own syntax for sql commands, but if you are familiear with SQL commands on one database you can use them on other database's. For this you must need to remember the SQL command syntax for a particular database. All commands given here with repect to Oracle server.

______________________________
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Control Language (DCL)
______________________________

->Data Definition Language

These commands mainly used by two types of person. One is Database Developer and other is DBA. Database Developer uses these commands to develop the new database or to update existing database. DBA also use these commands quite often. DBA can create views and/or sequences in oracle by using these commands. Views are created for privacy reasons where as sequences are created to improve the database performance. We will discuss these topics later in this tutorial.

->Data manipulation Language

All commands that are used to extract information update information and add new record comes under this category. Oracle clients mostly used these commands to perform day to day operations.

->Data Control Language

All commands come under this category only used by DBA. Normally no other user can use these commands in standard organizations. These commands help DBA to manage the user rights. For example who can view the information, who can update the information and who can add new records into the system.

*****In this tutorial we are not going to deal with DCL commands.*****

Create Table Command:

__________________________________________________________________________________________
Create table tablename(col1 datatype(size),col2 datatype(size),......coln datatype(size));
__________________________________________________________________________________________

Create table is key word that is used to create new table.
tablename is name of table that you want to create.
col1 up to coln are attribute of the relation(table) that you want to create for that particular table.
Datatype is type of attribute.

If we want to create a relation named customer that store the information about customers we can use following query.

__________________________________________________________________________________
Create table Customer(customer_id number(10), first_name varchar(2), last_name varchar(2),
house_number varchar(2), street varchar2(30), city varchar2(20), state varchar2(5),
phone_no1 number(10), phone_no2 number(10));
__________________________________________________________________________________

Create Primary Key Command:

_____________________________________________________________________________________
create table tablename(col1 datatype(size) primary key,col2 datatype(size)......coln datatype
(size));
_____________________________________________________________________________________

Primary key is used to differ each record from others. In oracle there is a key word 'primary key' to create primary key. Whenever you apply primary key on a particular attribute you need to keep in mind followings:

----Primary key attribute cannot contain duplicate values
----Primary key attribute can not have null values

________________________________________________________________________________
Create table Customer(customer_id number(10) primary key, first_name varchar(2), last_name
varchar(2), house_number varchar(2), street varchar2(30), city varchar2(20), state varchar2(5),
phone_no1 number(10), phone_no2 number(10));
________________________________________________________________________________

In the above example we used Primary key keyword after the size of customer_id attribute. Now this field
can't have values that are identical to each other.
Create Foreign Key Command:
___________________________________________________________________________________
Create table tablename(col1 datatype(size),col2 datatype(size) references tablename,...
coln datatype(size));
___________________________________________________________________________________

Foreign key refers to a primary key of another table. In the above command you can see "References" keyword after the second column. In Oracle references key word is used to create foreign key. Column2 can hold only those value that exists under the primary key of specified table. Value may be duplicate under the foreign key column that mean same value can be appear more then once under foreign key.

______________________________________________________________________________________
Create table Orders(Order_no number(10), Order_Date date, customer_id number(10)
references customer, order_status varchar2(10));
______________________________________________________________________________________

In the above example we created orders table. This table has four attribute. Third attribute is customer_id, refering to customer_id in the customer table. So this attribute can hold only those value that appears under the customer_id in the customer table.

Unique Constraint
Some time there may be situation when we want that particular field only contains unique values, but it may also contain null values. In such situation we use unique key constraints rather applying primary key to that field.

_______________________________________________________________________________________
create table tablename(col1 datatype(size), col2 datatype(size) unique,.............coln datatype(size));
_______________________________________________________________________________________

In oracle we use "Unique" keywork to apply unique constraint. Once this constraint is applied to the column, no duplicate value can be added into that field.

________________________________________________________________________________________
Create table customer(customer_id datatype(10) primary key, first_name varchar2(10), last_name
varchar2(10),Address varchar2(50),city varchar2(10), state varchar2(4), phone_no number(10) unique);
________________________________________________________________________________________

In the above example we applied unique constraint to phone_no field. The reason is phone number can never be same. For those customers who does not have any phone we can still enter null values into the system. More then one null value can be appear in the column. Unique constraint is never applied to null values.

Check Constraint

Check constraint is very important constraint used may times to apply different sort of constraints. Suppose you want to make sure that order_delievery_date is always grater then the order_date. For this use can use check constraints.

__________________________________________________________________________________
Create table Orders(order_no number(10), Order_date date, customer_id number(10)
references customer, product_no number(10) references product, order_delievery_date date,
Order_status varchar2(10), check(order_date < order_delievery_date);

__________________________________________________________________________________

The above command will make sure that order_delievery_date is always grater then the order date. There are many other sort of constraints that we can apply through Check constraints.

Not Null Constraint

Some time there may be situation that you never want a filed to be empty but it may have duplicate values. In such situations we Not Null constraints come into picture.

_________________________________________________________________________________________
create table customer(customer_id number(10)primary key, first_name varchar2(10),
last_name varchar2(10),address varchar2(50)not null, city varchar2(10),
state varchar2(4), phone_no number(10));

_________________________________________________________________________________________

In the above command we applied not null constraint to address field of the customer table. That means Address field can never have null values and it may contain same values for more then one customer.

Insert Command:

____________________________________________________________________________________________
insert into tablename(col1,col2...coln) values(value1, value2, ......valuen);
____________________________________________________________________________________________

To insert data into table oracle use "Insert into" keyword followed by the table name followed by columns of table into which you want to insert values, and finally values you want to insert.

__________________________________________________________________________________________
insert into customer(customer_id,first_name,last_name,address,city, state,phone_no) values(1001,'Mandeep', 'Singh',
'xyz street', 'Sydney', 'NSW',0294478743);
__________________________________________________________________________________________

The above command will insert new record in the customer table.

Select Command:

________________________
Select * from tablename;
________________________

Where * represent all columns in the specified table. Here are few examples.

_______________________
Select * from Customer;
Select * from Order;
_______________________

The above commands will extract all the records relating to customers and orders.
If you want to extract the information from only few columns then you can use the following syntax.

________________________________
Select col1,col2 from tablename;
________________________________
Where col1 and col2 are the columns from which you want to extract the information. For Example;
_____________________________________________
Select Customer_Name, Phone_no from Customer;
Select Order_Id, Order_Date from Order;
_____________________________________________

The first command will extract the name and phone number of all the customers and the second command will extract all order id’s and date when the order was placed.

Most of times when you want information from the database you needs only few required rows. For this Oracle has special keyword 'Where' to specify the condition. On the basis of condition Oracle engine returns the results to the users.

Suppose your manager wants the phone numbers and name of those customers who lives in sydney. For this we can use the following command.

________________________________________________________________________
Select first_name,last_name, phone_no from customer where city='sydney';
________________________________________________________________________

Oracle Joins:

Subquaries:

Update Command:

Oracle Arithmetic Operators:

Oracle Logical Operator:

Oracle Function:

Views:

Sequences:

Index: