Types of SQL :
- DDL ( Data Definition Language )
- DML ( Data Manipulation Language )
- DQL ( Data Query Language )
- DCL ( Data Contorl Language )
- DTCL
Syntax to create table :
create table <table_name> ( <column_name1> datatype1(size), <column_name2> datatype2(size), <column_name3> datatype3(size) );
eg : create table emp empno(5);
create table emp empno(7,2); // here size is 7 digits and limitation is 2 digits after decimal point.
Note : All Query Statements must end with a semi-colon ( ;) .
: These Queries are to be written in the sql command lines....
: SQL queries are not Case-sensitive but the table data are Case-sensitive .
: These Queries are to be written in the sql command lines....
: SQL queries are not Case-sensitive but the table data are Case-sensitive .
Creating table with constraints :
Constraints : A restriction applied to a table or accepting value
eg : primary key, foreign key, unique, not null, check.
Primary key : A Table can have only once primary key.
Unique : A column cannot contain duplicate value.
Not Null : A column cannot contain a null value ( no-value)
Check : The Column will only contain values with a range.
eg : create table emp (empno number(5), empname varchar (20), sal number (7,2), check (sal>0));
Primary key : This is a value of a column or columns present in a table to recognize each of its record uniquely.
A Table can have only one primary key
A Primary key can be column level or table level.
A primary key has to be unique and not null.
Creating table with column level primary key :
syntax :
create table table_name ( column_name datatype(size) primary key, ....... );
Creating table with table level primary key :
syntax :
create table table_name ( column_name datatype (size), column_name datatype (size), ....... , constraint key_name primary key (column_name(s)));
eg :
create table emp (empno number(5), ename varchar (20),sal number (7,3), constraint k123 primary key (empno,empname));
Foreign Key :
This is the value of a column or columns to refer value of primary key column present in another table.
This can be duplicate.
This cannot contain those value which are not present in primary key column.
A table containing primary key can be dropped only after dropping the table containing its foreign key.
This can be column level or Table level.
Creating table with foreign key at column level :
syntax:
create table table_name ( column_name datatype (size), column_name datatype (size) references primary key table_name (primary key column_name(s).....));
eg :
create table library (empno number(5) references emp (empno),bname varchar(20));
Creating table with Table level foreign key :
syntax :
create table table_name (column_name datatype(size), column_name datatype(size) ........, constarint key_name foriegn key ((column_name(s)) refrences primary key table_name (primary key column (s)));
To Remove a table :
drop table table_name;
ex :
drop table emp;
To Add a column in a Table :
alter table table_name add column_name datatype (size);
To Insert Record in a Table :
insert into table_name values (value1, value2, ....... );
To Insert Values for same columns of table or to insert values without using the order of column present in the table :
insert into table_name (column_name(s) values (value1, value2,...... ));
To insert for same column of table or to insert value without using the order of column present in the table
syntax :
insert into table_name (column_name(s) values (value1, value2,.....);
To Update a Record :
To change values of all Records
syntax :
update table_name set column_name =value ;
To change some record values :
update table name set column_name =value where [condition_provided] ;
To remove record
To rename all record
delete from table_name where [condition_provided] ;
Syntax to fetch records :
select * / column_name(s) / destination column_name [as_alias] form table_name [ where condition ] / group by column_name ] / order by column_name asc/des ] ;
Ex :
1. select * from table_name ;
2. To fetch some column of tables
select empno, ename, form emp ;
3. using 'as' to give new name in display
select empno as no, ename as name form emp;
4. to fetch unique values from a column/ destination
select distinc set from emp ;
Using condition to fetch records
Operator
<, >, <=, >=, !=, =
Using like :
This can be used to find an expression in text type of column
Symbol
' _ ' ( underscore ) It can replace one char
' % ' It can replace multiple character
Ex :
To fetch those records where the value of ename contains 'a' as the first character .
select * from emp where ename like ' A% ';
AND :
it can be used for multiple condition if all the condition match then the records get fetched
ex :
select * from emp where empno > 100 and sal > 1000 ;
OR :
It can be used for multiple condition if any of the conditions matched the records get fetched .
ex :
select * from emp where job = " manager " or sal >2000 ;
IN :
This can be used to compare one column with multiple values .
This is the replacement of multiple or the compare values of one column.
ex :
select * form emp where job IN ( 'clerk', 'peon' , 'analyst' );
Some other Formatting :
Single values functions :
sin()
cos()
tan()
round()
floor()
ceil()
Group rule functions :
max()
min()
arg()
count()
Using group by :
This can be used to form groups by using duplicate values of a column.
This is used to perform group value operating column.
ex :
To find arg salary of each job present in emp table
select job avg(sal) as sal any form emp group by job ;
Using order by :
this can be used to arrange records based upon value of a column
Default order is ascending
ex :
select * form emp order by sal ;
NOTE :
This is not the end but just the basics.. all topics covered are not explained completely .. its just an overview of sql queries ....
drop table table_name;
ex :
drop table emp;
To Add a column in a Table :
alter table table_name add column_name datatype (size);
To Insert Record in a Table :
insert into table_name values (value1, value2, ....... );
To Insert Values for same columns of table or to insert values without using the order of column present in the table :
insert into table_name (column_name(s) values (value1, value2,...... ));
To insert for same column of table or to insert value without using the order of column present in the table
syntax :
insert into table_name (column_name(s) values (value1, value2,.....);
To Update a Record :
To change values of all Records
syntax :
update table_name set column_name =value ;
To change some record values :
update table name set column_name =value where [condition_provided] ;
To remove record
To rename all record
delete from table_name where [condition_provided] ;
Syntax to fetch records :
select * / column_name(s) / destination column_name [as_alias] form table_name [ where condition ] / group by column_name ] / order by column_name asc/des ] ;
Ex :
1. select * from table_name ;
2. To fetch some column of tables
select empno, ename, form emp ;
3. using 'as' to give new name in display
select empno as no, ename as name form emp;
4. to fetch unique values from a column/ destination
select distinc set from emp ;
Using condition to fetch records
Operator
<, >, <=, >=, !=, =
Using like :
This can be used to find an expression in text type of column
Symbol
' _ ' ( underscore ) It can replace one char
' % ' It can replace multiple character
Ex :
To fetch those records where the value of ename contains 'a' as the first character .
select * from emp where ename like ' A% ';
AND :
it can be used for multiple condition if all the condition match then the records get fetched
ex :
select * from emp where empno > 100 and sal > 1000 ;
OR :
It can be used for multiple condition if any of the conditions matched the records get fetched .
ex :
select * from emp where job = " manager " or sal >2000 ;
IN :
This can be used to compare one column with multiple values .
This is the replacement of multiple or the compare values of one column.
ex :
select * form emp where job IN ( 'clerk', 'peon' , 'analyst' );
Some other Formatting :
Single values functions :
sin()
cos()
tan()
round()
floor()
ceil()
Group rule functions :
max()
min()
arg()
count()
Using group by :
This can be used to form groups by using duplicate values of a column.
This is used to perform group value operating column.
ex :
To find arg salary of each job present in emp table
select job avg(sal) as sal any form emp group by job ;
Using order by :
this can be used to arrange records based upon value of a column
Default order is ascending
ex :
select * form emp order by sal ;
NOTE :
This is not the end but just the basics.. all topics covered are not explained completely .. its just an overview of sql queries ....
µtorrent(peer-2-peer)
Ultra surf (proxy )
CC cleaner
Auslogics Disk defrag (defragmentor)
