Assalam-o-Alaikum,
Dear Fellows, here I am sharing Some Queries to practice that we studied in 7th 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.