SQL QUERIES -Part 1
1)Display
the details of all employees
SQL>Select * from emp;
2)
Display the depart information from department table
SQL>select * from dept;
3)
Display the name and job for all the employees
SQL>select ename, job from emp;
4)
Display the name and salary for all the
employees
SQL>select ename, sal from emp;
5)
Display the employee no and totalsalary
for all the employees
SQL>select empno,ename,sal,comm,
sal+nvl(comm,0) as"total
salary" from
emp
6)
Display the employee name and annual salary for all employees.
SQL>select ename, 12*(sal+nvl(comm,0))
as "annual Sal" from emp
7)
Display the names of all the employees who are working in depart number 10.
SQL>select emame from emp where deptno=10;
8)
Display the names of all the employees who are working as clerks and
drawing a salary more than 3000.
SQL>select ename from emp where
job='CLERK' and sal>3000;
9)
Display the employee number and name who
are earning comm.
SQL>select
empno,ename from emp where comm is not null;
10)
Display the employee number and name who
do not earn any comm.
SQL>select
empno,ename from emp where comm is null;
11)
Display the names of employees who are working as clerks, salesman or
analyst
and drawing a salary more than 3000.
SQL>select
ename from emp where job='CLERK' OR
JOB='SALESMAN'
OR JOB='ANALYST' AND SAL>3000;
12)
Display the names of the employees who are working in the company for
the
past 5 years;
SQL>select
ename from emp where
to_char(sysdate,'YYYY')-to_char(hiredate,'YYYY')>=5;
13)
Display the list of employees who have joined the company before
30-JUN-90
or after 31-DEC-90.
a)
select ename from emp where hiredate < '30-JUN-1990' or hiredate >
'31-DEC-90';
14)
Display current Date.
SQL>select sysdate from dual;
15)
Display the list of all users in your database (use catalog table).
SQL>select
* from all_users;
16)
Display the names of all tables from current user;
SQL>select
* from tab;
17)
Display the name of the current user.
SQL>show
user
18)
Display the names of employees working in depart number 10 or 20 or 40
or
employees working as CLERKS,SALESMAN or ANALYST.
SQL>select
ename from emp where deptno in(10,20,40) or job
in('CLERKS','SALESMAN','ANALYST');
19)
Display the names of employees whose name starts with alaphabet S.
SQL>select
ename from emp where ename like 'S%';
20)
Display the Employee names for employees whose name ends with alaphabet S.
SQL>select ename from emp where ename like '%S';
No comments:
Post a Comment