Saturday, January 19, 2008

Important SQL Quieries

create table command:-------
create table

(example:-----
create table branch_master
("branch_no" varchar(10),"name" varchar(25));

inserting data into table:-------
insert into (,)
values(,);

example:-----
insert into branch_mstr(branch_no,name)
values('b1','vile parle(ho)');

viewing data in the tableL--------

all rows and all columns:
select tofrom tablename;

example:-----
select * from branch_mstr;
selected columns and all rows:-------
select,from table name;

example:-----
select fname,lname from emp_mstr;
selected rows and all columns:------
select * from
where ;
example:----
select * from brach_mstr where name='vile parle(ho)';

selected columns and selected rows:-------
select ,
from where ;

example:-----
select acct_no,branch_no from acct_mstr where type='sb';

eliminating duplicate rows :--------
select distinct ,
from ;

example:-----
select distinct * from branch_mstr;

sorting data in a table:---------
select * from tablename order by,
,<[sort order]>;

example:-----
select * from branch_mstr order by name;

creating a table from a table:--------
create table
(,
)as select ,
from
;

example:------
create table acct_dtls(acct_no,brach_no,balance)as select acct_no,branch_no,curbal from acct_mstr;

creating table structure only:--------
create table acct_dtls(acct_no,brach_no,balance)
as select acct_no,branch_no,curbal from acct_mstr
where 1=2(condition cannot be fullfill);

inserting data into a talbe from another table:------
insert intoselect ,
from ;

example:------
insert into acct_dtls select acct_no,
branch_no,curbal from acct_mstr;

insertion of data setinto a table from another table:------
insert into select,
from
where ;

example:-----
insert into acct_dtls select acct_no,branch_no,curbal from acct_mstr where acct_no like 'sb%';

delete oprations:
--------
all rows:----
delete from ;
specific rows:------
delete from where ;

removal of specific rows based on the
date held by the other table:
---------
delete from addr_dtls where exists
(select fname from cust_mstr
where cust_mstr.cust_no=addr_dtls.code_no
and cust_mstr.fname='ivan');

updating the contents of table:--------
updating all rows:--------
update set=,=;

example:
----
update addr_dtls set city='bombay';

updating records conditionally:
----------
updateset=,= where ;

example:
------
update branch_mstr set name='head office' where name='vile parle(ho)';
modifying the structure of the table:---------
adding new columns:---------
alter table add(<
(),<()..);

example:-------
alter table branch_mstr add(city varchar(25));

droping a column from a table:--------
alter table drop column;

example:--------
alter table branch_mstr drop column city;

modifying exiting columns:--------
alter talbemodify(
());

example:------
alter table branch_mstr modify (name varchar(30));

renaming tablename:--------
rename to ;

example:---------
rename branch_mstr to branches;

destroying table:--------
drop table ;
displaying ther table structure:-------
describe ;

data constraints:------
primary key constraints:--------
at column level:--------
()primary key;
example:-------
create table cust_mstr("cust_no" varchar(10)primary key);
at talbe level:---------
primary key(,);
example:-------
create table fd_mstr("fd_ser_no" varchar(10),
"sf_no" varchar(10),"branch_no" varchar(10),
primary key(fd_ser_no,corp_cust_no));
foreign key (self reference) constraint:-----------
foreing key constraint defined at column level:--------
()references
[()][on delete cascade];
example:--------
create talbe emp_mstr("emp_no" varchar(10)
primary key,"branch_no" varchar(10) references branch_mstr,
"fname" varchar(25));
the foreign key defintion is specified as:--------
"branch_no" varchar(10) references branch_mstr;
foreing key constraint at the table level:---------
foriegn key(,)
references(,);
on delte casecade:--------
create talbe fd_dtls"fd_ser_no" varchar(10),
"fd_no" varchar(10),foreign key(fd_ser_no)references
fd_mstr(fd_ser_no) on delete cascade);
on delte set null:--------
create talbe fd_dtls"fd_ser_no" varchar(10),"fd_no"
varchar(10),foreign key(fd_ser_no)references
fd_mstr(fd_ser_no) on delete set null);
unique key constraint:-------
at column level:------
()unique key;
table level:---------
unique key(,);
example:-------
create table fd_mstr("fd_ser_no" varchar(10),
"sf_no" varchar(10),"branch_no" varchar(10),
unique key(fd_ser_no,corp_cust_no));
not null constraint:---------
()not null;
example:------
create table cust_mstr("cust_no" varchar(10)not null);
logical operators:--------
the and operator:--------
select * from trans_mstr where amt>=500 and amt<=5000 and to_char(dt,'dd/mm/yyyy')=to_char (sysdate,'dd/mm/yyyy'); the or operator:-------- select cust_no,fname,mname,lname"customers" from cust_mstr,addr_dtls where cust_mstr.cust_no= addr_dtls.code_no and(occup='information technology' or occup='self employee')and cust_no like 'c%'; the not operator:-----
select acct_no,type,opr_mode,opndt,from
acct_mstr where not(opr_mode='si' or opr_mode='jo');
range searchig:------
select * from trans_mstr where to_char(dt,'mm')
between 01 and 03;
select distinct from trans_mstr where to_char(dt,'mm')
not between 01 and 04;
pattern matching:-----
the use of like predicate:------
select fname,lname,dob_inc"birthdate",occup from
cust_mstr where fname like 'ch%';
the IN and NOT IN predicates:------
select fname,lname,dob_inc"birthday",occup from
cust_mstr where fname IN('hansel','mamta','namita',
'aruna');
select fname,lname,dob_inc"birthday",occup from
cust_mstr where fname NOT IN('hansel','mamta','namita',
'aruna');

grouping data from tables in sql:-------

group by clause:------
select ,,
aggregate_function () from tablename
where Group by,
,;

example:-------
select branch_no"branch no",count(emp_no)
"no. of employees" from emp_mstr group by branch_no;
having clause:-------
example:------
select cust_no,count(acct_fd_no)"no.of a/cs"
from acct_fd_cust_dtls group by cust_no having
count(acct_fd_no)>1;

subqueries:---------
example:-----------
select fname,laname from cust_mstr where
cust_no in(select code_no from addr_dtls
where code_no like
'c%' and pincode not in(select pincode from
addr_dtls where code_no like 'b%'));

correlated supqueries:-------
example:-------
select acct_no,curbal,branch_no from acct_mstr
A where curbal>(select avg(curbal) from acct_mstr
where branch_no=A.branch_no);
using multicolumn query:---------
example:-----------
select fname,laname from cust_mstr where
(fname,lname)in(select fname,lname from emp_mstr);
joining multiple tables:--------
select ,,
from ,
where .=
. and order by,,;

union clause:-------
multiple queries can be put together and their output can be combined using the union clause.
union clause merges the output of two or more queries into a single set of rows and columns.

example:
-------
select cust_no,fname,lname from cust_mstr,addr_dtls
where cust_mstr.cust_no=addr_dtls.code_no and
addr_dtls.city='mumbai' and addr_dtls.code_no like'e%'
UNION select emp_no,fname,lname from emp_mstr,
addr_dtls where cust_mstr.cust_no=addr_dtls.code_no and addr_dtls.city='mumbai' and addr_dtls.code_no like'c%';

sql functions:---------
avg():--------returns an average vlaue of n,ignoring
null vlaues in a column.
example:------
select avg(curbal)"average balance" from acct_mstr;
MIN():-------returns minimum value of expr.
example:------
select min(curbal)"minimum balance" from acct_mstr;
count(expr):-------returns the number of rows
where expr is not null.
example:-------
select count(acct_no)"no.of accounts" from acct_mstr;
MAX():--------returns the maximum value of expr.
example:-------
select max(curbal)"maximum balance" from acct_mstr;
SUM()---------returns the sum of the values of n;
example:-------
select sum(curbal)"total balance" from acct_mstr;
ABS(n):---------returns the absolute value of 'n'.
select abs(-15) from dual;
output:15
power(m,n):---------returns m raised to the n
power.n must be an integer ,else an error is returned.
example:-------
select power(3,2) from dual;
output:9
round(n,m):----------returns n,rounded to m
place to the right of a decimal point.if m
is omitted,n is rounded to 0 places.
select round(15.19,1) from dual;
output:15.2
sqrt(n):------returns square root of n.
example:--------
select sqrt(25) from dual;
output:5
greatest(expr1,expr2,...):--------returns the greatest value.
least(expr1,expr2,...):--------returns the least value.
MOD(m,n):------returns the remainder of a
first number divided by second number.
example:---
select mod(15,7),mod(15.7,7) from dual;
output:1,1.7.
FLOOR(n):------returns the largest integer
value that is equal to or less than a number.
example:----
select floor(24.8),floor(13.15) from dual;
output:24,13
CEIL(n):------select ceil(24.8),ceil(13.15) from dual;
output:25,14








No comments:

Post a Comment