Scribd
Upload
34 views6 pages

Practice Question CHP 2

This document provides examples of SQL queries using various SQL clauses, functions, and operators for data extraction and manipulation. Some examples include queries using aggregate functions, joins, subqueries, set operators, string functions, date functions and more. The examples reference tables like EMP, DEPT, and DUAL to demonstrate concepts like filtering, sorting, grouping, aggregating data using SQL.

Uploaded by

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

Practice Question CHP 2

This document provides examples of SQL queries using various SQL clauses, functions, and operators for data extraction and manipulation. Some examples include queries using aggregate functions, joins, subqueries, set operators, string functions, date functions and more. The examples reference tables like EMP, DEPT, and DUAL to demonstrate concepts like filtering, sorting, grouping, aggregating data using SQL.

Uploaded by

op gamer
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 6

CH 2: Interactive SQL for data extraction

 Word comparison operators:

1. Display unique jobs from emp table.


 Select distinct (job) from emp;

2. List the employees whose salary is more than 3000 after 20% increment.
 Select ename , sal from emp
Where (sal+sal*0.2)>3000;

3. List the employees, deptno who does not belong to dept 20 or 30.
 Select ename, deptno from emp
Where deptno NOT IN(20,30);

4. List all employees and their no,jobs who joined before 1981.
 Select empno, ename, job from emp
Where hire_date<’01-JAN-1981’;

5. List all the ename, sal whose annual sal does not fall from 22000 to 45000.
 Select empno, ename, sal from emp
Where sal NOT BETWEEN 1600 and 50000;

6. List all empno, enames, job except president and mgr.


 Select empno, ename, job from emp
Where job NOT IN(‘president’,’mgr’);

7. Display names of all employees whose name is 5 character long.


 Select ename from emp
Where ename LIKE ’_ _ _ _ _’;

8. Display the name of employee who does not work under any mgr.
 Select ename from emp
Where mgr IS NULL;
Client

Client no Name City Pincode State Balance Due


C001 Vikash Jain Mumbai 400054 Maharashtra 15000
C002 V.Chandra Chennai 780001 Tamil Nadu 0
C003 Pramod Patil Mumbai 400057 Maharashtra 5000
C004 Jatin Sapru Mumbai 400057 Maharashtra 0
C005 Virat Kohli Delhi 100001 2000
C006 Mumbai 400050 Maharashtra 0

Based on above table, solve the following queries:

9. Find the names of all clients and their city.


 Select name, city from client;

10. Find all the clients having ‘I’ as second letter in their name.
 Select name from client
Where name LIKE ‘_ i%’;

11. Find all the clients who does not stay in Mumbai or Chennai.
 Select name, city from client
Where city NOT IN (‘mumbai’,’chennai’);

12. Select all clients having balance below 2000 and above 3000.
 Select name , balance from client
Where balance_due NOT BETWEEN 2000 and 3000;

13. List all names having name consist of five alphabets only.
 Select name from client
Where name LIKE ‘_ _ _ _ _’;

14. List all the information of client whose state is unknown.


 Select * from client
Where state IS NULL;

15. Find distinct cities of client.


 Select distinct (city) from client;
 String and numeric functions:
 Sample examples with emp and dual tables:
Write the queries based on emp table , Dept table or dual table.
Q1.Display the employees name by capitalize its initial letter whose number is
7369.
1. Select initcap(ename) from emp where empno=7369;

Q2. Concate and display all the employees name with their jobs with a space in between.
2. Select ename || ‘ ‘ || job from emp where empno=7369;

Q3. Display the employee name in lowercase whose number is 7369


3. Select lower(ename) from emp where empno=7369;
Q4. Convert the word “hello” into uppercase and display.
4. Select upper(‘hello’) from dual;

Q5. Select and display the part of all employees name starting at 1st position and ending at 3rd
position.
5. Select substr(ename,1,3) from emp where empno=7369;
Q6. Replace and display the word TICK with TACK.
6. Select replace(‘tick and tack’ , ‘t’, ‘c’) from dual;

Q7.Find out the length of the name of the department whose number is 20.
7. Select length(dname) from dept where deptno=20;

Q8. Extract the character from the number 65 and display.


8. Select chr(65) from dual;

Q9. Extract the number from the character ,’a’ and display.
9. Select ascii(‘a’) from dual;

Q!0. Display all the employees name by left padding with ‘*’ and Right padding with ‘#’.
10. Select lpad(ename , 10, ‘*’), rpad(ename , 10, ‘#’) from emp;
Q11. Concat all the employees name along with their jobs and with a string in
between”is a”.
11. Select concat (concat (ename, ‘is a’), job) from emp;

Numeric Functions

Write the queries based on emp table , Dept table or dual table.

Q1. Find and display the absolute value of -7.5.


1.Select abs(-7.5) from dual;

Q2. Find out the value of 2 power 10 and display.


2.Select power(2,10) from dual;

Q3.Find out the modulus of 11 divided by 3.


3.Select mod (11,3) from dual;

Q4. Display the salary of the employee no 7369 and round it upto 3 decimal places.
4. Select sal, round(sal, -3) from emp where empno=7369;

Q5. Display the salary of the employee no 7369 and truncate it upto 3 decimal places.
5. Select sal, trunc(sal, -3) from emp where empno=7369;

Q6. Find and display the ceil of 14.3 .


6. Select ceil(14.3) from dual;
Q7. Find and display the floor of 14.7
7. Select floor(14.7) from dual;

Q8.Write the function to convert the value 113 to 100 and 110.
8. Select trunc(113,-1) , trunc(113,-2) from dual;

 Date functions:
 Sample examples:
1. Select round (’02-01-2020’ , day) from dual;

2. Select round (’31-12-2019’ , day) from dual;

3. Select round (’02-01-2020’ , month) from dual;

4. Select round (’31-12-2019’ , month) from dual;

5. Select round (’08-08-2020’ , month) from dual;

6. Select round (’08-08-2020’ , year) from dual;

7. Select round (’31-12-2019’ , year) from dual;

8. Select trunc (’08-08-2020’ , day) from dual;

9. Select trunc (’08-08-2020’ , month) from dual;

10. Select trunc (’08-08-2020’ , year) from dual;



 Aggregate functions:

1. Find out the average salary of all employees in emp table.


 Select avg(sal) from emp;

2. Find out the min and max salary of emp table.


 Select min(sal) , max(sal) from emp;

 Clauses:

1. Display employee names and no in ascending order of salary of emp table.


 Select ename, empno from emp Orderby sal asc;

2. Display employee names and no department wise.


 Select ename,empno from emp Orderby deptno;

3. Display the average salary of all departments.


 Select avg(sal) from emp Groupby deptno;
4. Display the sum of salary from emp table where deptno>10.
 Select deptno, sum(sal) from emp Groupby deptno Having deptno>10;

 Multiple row subquery:

1. List the employees of deptno = 20 who’s jobs are same as deptno=10.


 Select ename from emp where deptno= 20 AND job IN (select job from emp where
deptno=10);

 Single row subquery:


Q1. List the employees whose department name is CO.
1. Select * from emp where deptno=(select deptno from dept where dname= ‘CO’);
 Set operators:

1. Find distinct jobs from emp and workers table.


 Select distinct (job) from emp UNION Select distinct (job) from worker;

2. Find common name of emp and worker


 Select ename from emp INTERSECT Select wname from worker;

Join:

1. Display the number of employees in each department who are working in different
departments along with their deptno respectively.
 Select dept.deptno, count (empno) from emp,dept Where dept.deptno = emp.deptno
Groupby dept.deptno;

You might also like