Some sql database queries examples

April 24, 2013

Some sql database queries examples


         Show records of  all employees with hire date in the format as dd-mm-yyyy.
  
s  select ename,empno,job,mgr,sal,comm,depno, convert(varchar(10),hiredate,105)'dd-mm-yyyy' from emp




1.	Show records of  all employees with hiredate in the format as dd-mm-yyyy
 select * from emp
where sal=any(select sal from emp where ename='smith' or ename='ford') order by  sal desc

select distinct job from emp
where deptno=10 and job!=all(select job from emp where deptno=20)



data base queries with waqeeh

select * from emp
where ename<all(select ename from emp where job='president')



data base queries with waqeeh
 insert into emp1 values (7114,'Sara','analyst',7902,17/12/1999,9000,null,20)
insert into emp1 values (7112,'Jim','clerk',7788,18/12/2000,2000,null,10)
select * from emp1


data base queries with waqeeh

update emp
set sal=sal+500
where job='clerk'
select * from emp


data base queries with waqeeh
 update emp set sal=sal+((sal/100)*20where ename like '%s%' select * from emp


data base queries with waqeeh

update emp
set sal=sal+(((sal*12)/100)*10)
where sal<(select avg(sal) from emp)
select * from emp


data base queries with waqeeh


delete from emp
where sal between 600 and 1500
select * from emp



update emp
set comm=comm+500
where job='salesman'
select * from emp


data base queries with waqeeh

delete from emp
where sal>(select sal from emp where ename='allen')
select * from emp


           delete from emp where hiredate<(select hiredate from emp where ename='ford')
select * from emp


data base queries with waqeeh

       Show the current system date and time
select getdate()as datetime


        Show the systems date
select convert(varchar(10),getdate(),105)



data base queries with waqeeh

create table dept1( deptno numeric(4) constraint pkdeptno primary key,
dname varchar(10),
location varchar(10));
select * from dept1



data base queries with waqeeh
 selectename,empno,job,mgr,sal,comm,depno, convert(varchar(10),hiredate,105)'mm-dd-yyyy' from emp


data base queries with waqeeh


 create table emp2
(empno numeric(4) constraint pk1ename primary key,
ename varchar(30) constraint nn1ename not null,
job varchar(30) constraint nn1job not null,
mgr numeric(4),
hiredate smalldatetime,
sal numeric(10,2),
gender char(1) constraint chkgender check (gender in ('m','f','M','F')));
select * from emp2



 select ename,empno,job,mgr,sal,comm,depno,
convert(varchar(50),hiredate,105)'dd-mm-yyyy'
from emp where hiredate between '1983-1-1' and '1983-12-31'



 select * from emp
where sal>(select sal from emp where ename='ALLEN')

select * from emp
where sal>(select sal from emp where ename='JONES')




select * from emp
where deptno=(select deptno from emp where ename='smith')

select * from emp
where HIREDATE>(select HIREDATE from emp where ename='FORD')



 select * from emp where SAL>(select max(sal) from emp where deptno=30)

 select * from emp
where SAL<all(select SAL from emp where deptno=10)

select * from emp
where SAL>all(select SAL from emp where job='clerk')

data base queries with waqeeh

select * from emp
where job=all(select job from emp where job='manager')

select * from emp
where sal>all(select sal from emp where ename='blake')



data base queries with waqeeh

You Might Also Like

0 comments

Popular Posts

Like us on Facebook

Flickr Images