Scribd
Upload
6 views4 pages

SQL Query Examples for Employee Data Retrieval

This document provides examples of SQL SELECT queries for retrieving data from database tables. It demonstrates how to select specific columns, filter rows, use logical operators, sort results, and handle null values. Examples include selecting the department name and location, employees whose salary exceeds 3000, employees whose job is salesman or manager, and more. The order of result rows depends on the database server configuration but can be explicitly set using ORDER BY.

Uploaded by

Priyanshi Srivastava
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
6 views4 pages

SQL Query Examples for Employee Data Retrieval

This document provides examples of SQL SELECT queries for retrieving data from database tables. It demonstrates how to select specific columns, filter rows, use logical operators, sort results, and handle null values. Examples include selecting the department name and location, employees whose salary exceeds 3000, employees whose job is salesman or manager, and more. The order of result rows depends on the database server configuration but can be explicitly set using ORDER BY.

Uploaded by

Priyanshi Srivastava
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

SELECT QUERy######################

For retrieving all data- SELECT *

From retrieving particular data use below commands.

1)Display the depname and their corresponding location from dept table?

SELECT dname,loc FROM DEPT38;

2)changing the column name if we are not aware what dname and loc is?

SELECT dname AS "DEPARTMENTN",Loc AS LOCATION FROM DEPT38;

3)GIVE Ename,no and designation of all those employee whose salary is greater than 3000.

SELECT EMPNO,ENAME,JOB FROM EMP112 WHERE SAL>=3000;

4)display all employees name whose job designation is either sslaesman or manager

EACH time we are comparing column name should come twice.

SELECT ENAME FROM EMP112 WHERE JOB='Salesman' OR JOB='Manager';

When data is lakhs then we cant type everytime JOB=’Salesman’ or


JOB=’MANAGER’,JOB=’hshsh’…………….

For that format is::::

SELECT ENAME FROM EMP112 WHERE JOB IN('Salesman','Manager','Clerk');

5)give employee no and their manager id of all those employees who are clerk and earn salary greater
than 500

SELECT EMPNO,MGR FROM EMP112 WHERE JOB='Clerk' AND SAL>=500;

6)ename nd sal of all emp whose salary is beeteween 3000 nd 5000 all inclusivr

SELECT ENAME,SAL FROM EMP112 WHERE SAL>=3000 AND SAL<=5000;


SELECT ENAME,SAL FROM EMP112 WHERE SAL BETWEEN 3000 AND 5000;

7)list of all ename who doesn’t have manager

SELECT ENAME FROM EMP112 WHERE MGR IS NULL;

8)GIVE rthe job of all employees

SELECT JOB FROM EMP112;

9)select distinct job from table

SELECT DISTINCT JOB FROM EMP112;

10)Select distinct job, deptno from employee table

Will retrieve distinch data of both combination….nd it shoul;d be written only once in SELECT [Link]
default it will combine the other columns/

SELECT DISTINCT JOB,DEPARTMENTNO FROM EMP112;

10)list of all those employee who do not work in dept no 40.

SELECT ENAME FROM EMP112 WHERE DEPARTMENTNO !=40;

SELECT ENAME FROM EMP112 WHERE DEPARTMENTNO NOT IN 40;

SELECT ENAME FROM EMP112 WHERE DEPARTMENTNO NOT IN(40,30);

SELECT ENAME FROM EMP112 WHERE DEPARTMENTNO !=40 AND DEPARTMENTNO!=30

11)LIST OF ALL those employees whose nname starts wotth an A;

SELECT ENAME FROM EMP112 WHERE ENAME LIKE 'A%';


SELECT ENAME FROM EMP112 WHERE ENAME LIKE '%A%';

Same output for both above ALLEN ND ADAM

SELECT ENAME FROM EMP112 WHERE ENAME LIKE '%a%';

Output is all those in which letter a is there/

SELECT ENAME FROM EMP112 WHERE ENAME LIKE '_a%';

OUTPUT IS all name who have one character before “a”

SELECT ENAME FROM EMP112 WHERE ENAME LIKE '%ke';

Output last letter ke

SELECT ENAME FROM EMP112 WHERE ENAME LIKE 'Cl%';

Output last letter CL

SELECT ENAME FROM EMP112 WHERE ENAME LIKE '%t_';

Output second last letter “t”

All above commands will be used only with VARCHAR2

###############While using SELECT command,what is the order of the output??

Totally depends on server.

12)SELECT ENAME FROM EMP112 ORDER BY ENAME;


By default showed ascending order of empnames.

13)Select ename from emp order by ename,salary?

Same Result showed as ascending order of employees/

SELECT ENAME FROM EMP112 ORDER BY ENAME DESC;(IF in descending order)

14) SELECT ENAME,SAL FROM EMP112 ORDER BY 1,2;

SELECT ENAME FROM EMP112 ORDER BY 2; (NOT VALID QUERY)

SELECT ENAME FROM EMP112 ORDER BY SAL;(VALID QUERY)

You might also like