SQL Commands

Create Command:
------------------------
Create command is used to create a table.

Syntax:
---------------
Create table
< table_name> ( col_name1 datatype(size),
col_name2 datatype(size), ,, ,, ,
col_namen datatype(size) );

ex:
------

create table student ( sno number(3), sname varchar2(20), marks number(3));
Output:
---------
Table Created.

Insert command: 
--------------------
Insert command is used to insert the rows into the table.

Syntax:
----------
Insert into < table_name> values ( val1, val2, ....., valn );

Ex:
-----
insert into student values (101, 'ali', 80);
insert into student values (102, 'kiran', 85);
insert into student values (103, 'asif', 66);

For every insert command , the response we get is " 1 row created. "

Using null keyword: 
--------------------------
Null keyword is used , if we do not have value for a column.

Ex:
---
insert into student values (104,'ali', null);
insert into student values (105,null, null);

2nd syntax of insert command: 
----------------------------------------
insert into student( col1, col2, ..coln ) values ( val1, val2, ..., valn );

ex:
-----
insert into student ( sno, sname ) values (106,'haider');

The leftover column will have null value.

Selecting the rows from the table: 
--------------------------------------------

To retrieve the rows from the table select command is used.

Basic Syntax:
----------------
select * from < table_name>;

Ex:
-----

select * from student;

In the above command, * is a special character which is used to display all the information from the table.

To select specific columns: 
-----------------------------------
select col1, col2... , coln from the <table_name>;

ex:
-----
select sno, sname from student;
select sname from student;

Selecting specific rows:
-------------------------------

Where clause is used to filter the rows from the table.

Syntax:
---------

select * from < table_name> where < condition >;

ex:
------

select * from student where marks > 70;

Combination of selecting specific row and selecting specific columns:
------------------------------------------------------------------------------------------

select sno from student where marks > 70;

Using Arithmetic operations with select command: 
-----------------------------------------------------------------

select empno, ename , sal, sal* 12 , deptno from emp;

Using column Alias: 
-------------------------

Column alias is process of providing user definied column heading. select empno, ename , sal, sal* 12 annual_sal , deptno from emp; In the above query annual_sal is the column alias.

Using Distinct keyword: 
-------------------------------

Distinct keyword is used to get the distinct ( unique ) values. Duplicates will be supressed.

ex:
------

select distinct deptno from emp;

Update command: 
-----------------------

This command is used to change the data present in the table.

Syntax:
---------

Update < table_name> set <col_name> = < value> where < condition> ;

ex:
----

update student set marks =95 where sno =101;

Output:
---------

1 row updated.

Updating multiple columns:
-----------------------------------

update student set sname='nabil' , marks =48 where sno =102;

Note:
------

Update command without where clause , will update all the rows.

ex:
---

update student set marks=80;

Using Delete command: 
------------------------------

Delete command is used to delete the rows from the table.

Syntax:
---------

delete from < table_name> where < condition >;

ex:
-----

delete from student where sno=103; Output: 1 row deleted.

Note:
------

Delete command without where clause will delete all the rows.

ex:
----

delete from student;

( All the rows are deleted )

++++++++

Alter command: 
--------------------

Alter command is used to change the structure of the table.
We can perform following activities using Alter command

1) Adding a new column
2) Droping an existing column
3) Modifying a column
4) Renaming a column

Adding a new column:
----------------------------

Alter table < table_name> add ( col_name1 datatype(size), col_name2 datatype(size), ,, ,, ,, col_namen datatype(size) );

ex:
-----

Alter table student add ( city varchar2(10), state varchar2(10)); Table altered.

Note:
The new columns will have null values.

Droping a column:
-----------------------

Alter table < table_name > drop ( col_name1, col_name2,..., col_namen);

Ex:
----

Alter table student drop ( city, state);

Table Altered.

Modifying a column:
-------------------------

By using modifying we can increase and decrease the size of the column.

ex:
---

Alter table student modify ( sname varchar(20)); Table Altered.

Decreasing the size : 
--------------------------

Alter table student modify ( sname varchar(15)); Table altered.

Note:
-------

To decrease the size , the existing table data should fit into new size. By using Modify keyword , we can also change the datatype of the column.

Ex:
-----

Alter table student modify ( sname number(15));

Note:
-------

To change the datatype, column should be empty.

Renaming a column:
--------------------------
Syntax:
-----------

Alter table <table_name> rename column <old_name> to <new_name>;

Ex:
----

Alter table student rename column sno to roll_no; Table Altered.

Truncate command: 
-------------------------

This command is used to remove all the rows from the table.
Truncate table < table_name>;
Truncate table student;

Rename command:
------------------------

This command is used to change the table name.

Syntax:
--------

Rename <old_table_name > to < new_table_name>;

Syntax:
--------

Rename student to stu1; Table renamed. From now, we need to access the table by using the new name.

Drop command: 
--------------------

This command is used to remove the table from the database.

Syntax:
----------------

Drop table < table_name >;

ex:
----

Drop table stu1; Table dropped.