Some sql database queries examples

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

 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)

select * from emp

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

 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

update emp

set sal=sal+500

where job='clerk'

select * from emp

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

update emp

set sal=sal+(((sal*12)/100)*10)

where sal<(select avg(sal) from emp)

select * from emp

delete from emp

where sal between 600 and 1500

select * from emp

update emp

set comm=comm+500

where job='salesman'

select * from emp

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

       Show the current system date and time

select getdate()as datetime

        Show the systems date

select convert(varchar(10),getdate(),105)

create table dept1( deptno numeric(4) constraint pkdeptno primary key,

dname varchar(10),

location varchar(10));

select * from dept1

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

 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')

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')