Display employee number and total salary for each employee.

select empno, sal+comm from emp;

Display employee name and annual salary for all employees.

select empno, empname, 12*sal+nvl(comm,0) annualsal from emp;

Display the names of all employees who are working in department number 10.

select ename from emp where deptno=10;

Display the names of all employees working as clerks and drawing a salary more than 3000.

select ename from emp where job=?CLERK? and sal>3000;

Display employee number and names for employees who earn commission.

select empno, ename from emp where comm is not null and comm>0;

Display names of employees who do not earn any commission.

Select empno, ename from emp where comm is null and comm=0;

Display the names of employees who are working as clerk, salesman or analyst and drawing a salary more than 3000.

select ename from emp where (job=?CLERK? or job=?SALESMAN? or job=?ANALYST?) and sal>3000; (or) select ename from emp where job in(?CLERK?,?SALESMAN?,?ANALYST?) and sal>3000;

Display the names of employees who are working in the company for the past 5 years.

select ename from emp where sysdate-hiredate>5*365;

Display the list of employees who have joined the company before 30th June 90 or after 31st dec 90.

select * from emp where hiredate between ?30-jun-1990? and ?31-dec-1990?;

Display the names of employees working in department number 10 or 20 or 40 or employees working as clerks, salesman or analyst.

select ename from emp where deptno in (10,20,40) or job in (?CLERK?,?SALESMAN?,?ANALYST?);

Display the names of employees whose name starts with alphabet S

select ename from emp where ename like ?S%?;

Display employee names for employees whose name ends with alphabet.

select ename from emp where ename like ?%S?;

Help

Options

focusNode

Didn't know it?
click below

Knew it?
click below

Don't Know

Remaining cards (0)

Know

retry

shuffle

restart

0:00

Embed Code - If you would like this activity on your web page, copy the script below and paste it into your web page.

Normal Size Small Size show me how

SQL

Question	Answer
Display the dept information from department table.	select * from dept;
Display the details of all employees.	select * from emp;
Display the name and job for all employees.	select ename, job from emp;
Display name and salary for all employees.	select ename, sal from emp;
Display employee number and total salary for each employee.	select empno, sal+comm from emp;
Display employee name and annual salary for all employees.	select empno, empname, 12*sal+nvl(comm,0) annualsal from emp;
Display the names of all employees who are working in department number 10.	select ename from emp where deptno=10;
Display the names of all employees working as clerks and drawing a salary more than 3000.	select ename from emp where job=?CLERK? and sal>3000;
Display employee number and names for employees who earn commission.	select empno, ename from emp where comm is not null and comm>0;
Display names of employees who do not earn any commission.	Select empno, ename from emp where comm is null and comm=0;
Display the names of employees who are working as clerk, salesman or analyst and drawing a salary more than 3000.	select ename from emp where (job=?CLERK? or job=?SALESMAN? or job=?ANALYST?) and sal>3000; (or) select ename from emp where job in(?CLERK?,?SALESMAN?,?ANALYST?) and sal>3000;
Display the names of employees who are working in the company for the past 5 years.	select ename from emp where sysdate-hiredate>5*365;
Display the list of employees who have joined the company before 30th June 90 or after 31st dec 90.	select * from emp where hiredate between ?30-jun-1990? and ?31-dec-1990?;
Display current date.	select sysdate from dual;
Display the list of users in your database (using log table).	select * from dba_users;
Display the names of all tables from the current user.	select * from tab;
Display the name of the current user.	show user;
Display the names of employees working in department number 10 or 20 or 40 or employees working as clerks, salesman or analyst.	select ename from emp where deptno in (10,20,40) or job in (?CLERK?,?SALESMAN?,?ANALYST?);
Display the names of employees whose name starts with alphabet S	select ename from emp where ename like ?S%?;
Display employee names for employees whose name ends with alphabet.	select ename from emp where ename like ?%S?;

Created by: abcz123

"Know" box contains:
Time elapsed:
Retries: