SQL Subqeuries Practice

Assalam-o-Alaikum,

Dear Fellows, here I am sharing Some Queries to practice that we studied in 7^th June09 Class. Faisal Sab also gave us some simple tasks to play with queries. Following is the script to generate required database and tables with insert queries;

--create database

create database company

--use company database and create tables then put data using insert queries.

use company

create table dept

(deptno numeric(2) primary key,

dname varchar(10))

create table designation

(desigId numeric(2) primary key,

desigDesc varchar(10))

create table emp

(empno numeric(4) primary key,

ename varchar(10),

sal numeric(6,2),

desigId numeric(2) references designation(desigId),

hiredate datetime,

deptno numeric(2) references dept(deptno))

insert into dept values (10,'sales')

insert into dept values (20,'accounts')

insert into dept values (30,'product')

insert into designation values (1,'Manager')

insert into designation values (2,'Clerk')

insert into emp values (1001,'faisal',1000.50,1,'2007-1-2',10)

insert into emp values (1002,'amir',900.50,2,'2008-2-28',10)

insert into emp values (1003,'javed',1500.50,1,'2009-3-14',20)

insert into emp values (1004,'kamran',1100.15,2,'2006-2-5',20)

insert into emp values (1005,'sohail',2000,1,'2005-1-12',30)

select * from dept

select * from designation

select * from emp

Now play with the queries to accomplish some of the simple tasks;

--Get all employees which are in ‘accounts’ department, using subquery

Select * from emp

Where deptno =

(select deptno from dept where dname='accounts')

--Get all managers, using subquery

Select * from emp

Where desigId =

(select desigId from designation where desigDesc='manager')

--Get manager of sales department using subqeury

Select * from emp

Where desigId =

(select desigId from designation where desigDesc='manager')

And

(select deptno from dept where dname='sales')

--Get employees which have more salary than the average salary of all employees

SELECT * FROM emp

WHERE sal > (SELECT AVG(sal) FROM emp)

--Get Average sal of employees working in sales dept

SELECT avg(sal) FROM emp

WHERE deptno = (SELECT deptno FROM dept WHERE dname='sales' )

--Get the junior most employee

SELECT * FROM emp

WHERE hiredate = (select min(hiredate) FROM emp)

--Get the senior most employee

SELECT * FROM emp

WHERE hiredate = (select max(hiredate) FROM emp)

--Get the who has complete more than one year

SELECT * FROM emp

WHERE datediff(year,hiredate,GETDATE()) > 1

I hope you understand all, in case any qeury feel free to put comments.