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
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 (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*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
(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'
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)
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')
No comments