create table dept
(deptno int primary key,
dname varchar(10),
loc varchar(10))
/* inserting records in dept */
insert into dept values (10,'sales','khi')
insert into dept (deptno,dname,loc) values (20,'accounts','lhr')
insert into dept (dname,deptno,loc) values ('production',30,'isl')
/* creating child table emp */
create table emp
(empno int primary key,
ename varchar(10),
sal float,
deptno int references dept(deptno))
insert into emp values (1,'faisal',1000,10)
insert into emp values (2,'amir',1200,10)
insert into emp values (3,'javed',1300,10)
insert into emp values (4,'khan',1200,20)
insert into emp values (5,'nasir',1250,20)
insert into emp values (6,'rehan',1100,20)
insert into emp values (7,'jamal',1300,30)
insert into emp values (8,'kamran',1400,30)
insert into emp values (9,'bilal',1450,30)
select *
from emp
/* employees of dept 10*/
select *
from emp
where deptno=10
/* emp of dept 10 or 20 */
select *
from emp
where deptno=10 or deptno=20
--or
select *
from emp
where deptno in (10,20)
/* emp who are not working in dept 10 */
select *
from emp
where deptno<>10
--or
select *
from emp
where not deptno=10
/* emp who are not working in dept 10 or 20 */
select *
from emp
where not (deptno=10 or deptno=20)
--or
select *
from emp
where not deptno in (10,20)
/* emp whose sal is in the range 1300 to 1400*/
select *
from emp
where sal>=1300 and sal<=1400
--or
select *
from emp
where sal between 1300 and 1400
/* emp whose sal is in the range 1300 to 1400 but working in
dept 10 or 20 */
select *
from emp
where (sal>=1300 and sal<=1400) and (deptno=10 or deptno=20)
/* emp of sales dept */
select *
from emp
where deptno=(select deptno
from dept
where dname='sales')
/* emp of sales and accounts */
select *
from emp
where deptno in (select deptno
from dept
where dname='sales' or dname='accounts')
/* all emp along with dept details */
select empno,ename,sal,dept.deptno,dname,loc
from dept,emp
where (emp.deptno=dept.deptno)
/* joins */
/* emp and dept table required */
/* Cross joins */
select empno,ename,sal,emp.deptno,dname
from emp cross join dept
/* or */
select empno,ename,sal,emp.deptno,dname
from emp,dept
/* Inner joins */
select empno,ename,sal,emp.deptno,dname
from emp inner join dept
on emp.deptno=dept.deptno
--
select empno,ename,sal,emp.deptno,dname
from emp inner join dept
on emp.deptno=dept.deptno
where sal>=1300 and sal<=1400
/* or */
select empno,ename,sal,emp.deptno,dname
from emp,dept
where emp.deptno=dept.deptno
/* Outer joins */
/* create following tables */
create table acholder
(accountno varchar(10) primary key,
title varchar(15),
bal float)
create table crholder
(crno varchar(19) primary key,
title varchar(15),
accountno varchar(10),
cramount float,
crremain float)
insert into acholder values ('071-109987','Faisal',10000)
insert into acholder values ('071-109986','Javed',11000)
insert into acholder values ('071-109654','Amir',2300)
insert into acholder values ('071-109788','kamran',45000)
insert into acholder values ('071-109982','Asim',4500)
insert into acholder values ('071-109675','Hira',5600)
insert into crholder(crno,title,cramount,crremain)
values ('1099-1234-4566-9100','Haris',35000,10000)
insert into crholder(crno,title,accountno,cramount,crremain)
values ('1099-1234-4566-9101','Faisal','071-109987',35000,15000)
insert into crholder(crno,title,cramount,crremain)
values ('1099-1234-4566-9105','Khurram',50000,25000)
insert into crholder(crno,title,accountno,cramount,crremain)
values ('1099-1234-4566-9110','Kamran','071-109788',50000,26000)
insert into crholder(crno,title,accountno,cramount,crremain)
values ('1099-1234-4566-9120','Javed','071-109986',35000,25000)
insert into crholder(crno,title,accountno,cramount,crremain)
values ('1099-1234-4566-9132','Hira','071-109675',35000,20000)
select * from acholder
select * from crholder
select acholder.accountno,acholder.title,acholder.bal,
crholder.crno,crholder.title,crholder.cramount,crholder.crremain
from acholder inner join crholder
on acholder.accountno=crholder.accountno
/* display all acct holder with/without credit details*/
select acholder.accountno,acholder.title,acholder.bal,
crholder.crno,crholder.title,crholder.cramount,crholder.crremain
from acholder left outer join crholder
on acholder.accountno=crholder.accountno
/* display all card holders with/without acct details*/
select acholder.accountno,acholder.title,acholder.bal,
crholder.crno,crholder.title,crholder.cramount,crholder.crremain
from acholder right outer join crholder
on acholder.accountno=crholder.accountno
select acholder.accountno,acholder.title,acholder.bal,
crholder.crno,crholder.title,crholder.cramount,crholder.crremain
from acholder full outer join crholder
on acholder.accountno=crholder.accountno
/* scenario for exists operartor */
create table discipline
(discid numeric(2) primary key,
discname varchar(15))
insert into discipline values (1,'commerce')
insert into discipline values (2,'engineering')
insert into discipline values (3,'medical')
create table admission
(stid numeric(6),
yr numeric(4),
stname varchar(10),
discid numeric(2) references discipline(discid),
constraint admission_pk primary key(stid,yr))
insert into admission values (101,2004,'faisal',1)
insert into admission values (102,2004,'javed',2)
insert into admission values (103,2004,'imran',3)
insert into admission values (101,2005,'kamran',1)
insert into admission values (102,2005,'iname',2)
insert into admission values (103,2005,'sohail',3)
insert into admission values (104,2005,'misha',1)
insert into admission values (105,2005,'sonia',2)
insert into admission values (106,2005,'rubab',3)
create table enroll
(stid numeric(6),
yr numeric(4),
examyr numeric(4),
semester numeric(2),
constraint enroll_pk primary key(stid,yr,examyr),
constraint enroll_fk foreign key(stid,yr) references admission(stid,yr))
insert into enroll values (101,2004,2005,2)
insert into enroll values (103,2004,2005,2)
insert into enroll values (101,2005,2005,1)
insert into enroll values (102,2005,2005,1)
insert into enroll values (103,2005,2005,1)
insert into enroll values (104,2005,2005,1)
select * from admission
select * from enroll
/* Q. List all students from admission table who are enrolled for exams 2005 */
select * from admission
where exists
(select * from enroll
where (admission.stid=enroll.stid and
admission.yr=enroll.yr) and enroll.examyr=2005)
order by yr,stid
/* Q. List all students from admission table who are not enrolled for exams 2005*/
select * from admission
where not exists (select * from enroll
where (admission.stid=enroll.stid and
admission.yr=enroll.yr) and enroll.examyr=2005)
order by yr,stid
select * from dept
select * from emp
/* count emp dept wise */
select deptno, count(deptno) [Employee Count]
from emp
group by deptno
/* display sum, avg sal dept wise */
select deptno,sum(sal) Total,avg(sal) Average
from emp
group by deptno
--or
select deptno,sum(sal) Total,avg(sal) Average,
round(avg(sal),2) Average
from emp
group by deptno
/* display sum, avg sal of dept 10,20 */
select deptno,sum(sal) Total,avg(sal) Average,
round(avg(sal),2) Average
from emp
where deptno=10 or deptno=20
group by deptno
/* display sum ,avg sal of dept 10,20 and sum sal > 3500 */
select deptno,sum(sal) Total,avg(sal) Average,
round(avg(sal),2) Average
from emp
where deptno=10 or deptno=20
group by deptno
having sum(sal)>3500
--or
select deptno,sum(sal) Total,avg(sal) Average,
round(avg(sal),2) Average,
str(avg(sal),10,2) Average
from emp
where deptno=10 or deptno=20
group by deptno
having sum(sal)>=3500
order by deptno desc
/* display max sal */
select max(sal),min(sal)
from emp
/* display max, min sal dept wise */
select max(sal),min(sal),deptno
from emp
group by deptno
/* using upper() */
select ename,upper(ename) uename from emp
/* using substring() */
select ename,substring(ename,2,3) from emp
/* using len() */
select ename,len(ename) en_length from emp
/* create a person table */
create table person
(pid int primary key,
pname varchar(10),
dob datetime)
insert into person values (1,'fahad','1980-09-23')
insert into person values (2,'hamid','1981-04-15')
insert into person values (3,'hamid','1982-07-25 02:30:50.190')
/* checking */
select * from person
/* using datediff(),getdate() */
select getdate() now
select dob,datediff(day,dob,getdate()) age from person
select dob,datediff(month,dob,getdate()) age from person
select dob,datediff(year,dob,getdate()) age from person
select dob,datediff(second,dob,getdate()) age from person
/* using dateadd() */
select dob,dateadd(day,10,dob) added from person
select dob,dateadd(month,10,dob) added from person
select dob,dateadd(year,10,dob) added from person
/* datepart */
select dob,datepart(mm,dob) from person
/* using convert(),cast() */
select dob,cast(dob as varchar(19)) from person
select dob,cast(dob as varchar(11)) from person
select dob,substring(cast(dob as varchar(19)),1,11) from person
select dob,substring(convert(varchar(19),dob),1,11) from person
/* case...when...then...end */
select empno,ename,sal,deptno,(case deptno
when 10 then 'sales'
when 20 then 'accounts'
when 30 then 'production'
end) dname
from emp
/* system tables */
select a.id objectid,b.id tableid,a.name,c.name,d.name
from sysobjects a,sysconstraints b,sysobjects c,syscolumns d
where a.id=b.constid and c.id=b.id and b.id=d.id
select * from sysconstraints where id=(select id from sysobjects where name='emp')
select * from sysconstraints where id=(select id from sysobjects where name='dept')
select * from syscolumns where id=(select id from sysobjects where name='emp')
0 comments:
Post a Comment