1.
Display all the
employees whose name start with P
select * from emp where ename like 'P%';
2.
Display all the analyst
and clerks in department 10 and 20
SELECT * FROM EMP WHERE JOB
IN('ANALYST','CLERK') AND DEPTNO
IN(10,20);
3.
List all the department
names which are having blank space in their
location
and having Dname with at least two EE's
SELECT DNAME FROM DEPT WHERE DNAME LIKE
'%E%E%';
4.
Display all the
employees who are having at least two AA's in their job description and salary
in the range 1200 to 2800 in dept 10,20,30.
SELECT
* FROM EMP
WHERE JOB LIKE '%A%A%'
AND SAL BETWEEN 1200 AND 2800
AND DEPTNO IN(10,20,30);
5.
Display all the
employees who aren't having any reporting manager with number ending with '8'
and not working as 'MANAGER' or 'SALESMAN' in dept 30.
SELECT
* FROM EMP
WHERE MGR NOT LIKE '%8'
AND JOB NOT IN('MANAGER','SALESMAN') AND
DEPTNO=30;
6.
List all the employees
who are not getting any commission with their designation neither 'CLERK' nor
'MANAGER' and joined in the year 81 and getting salary more than 1500.
SELECT * FROM EMP
WHERE
COMM IS NULL
AND JOB NOT IN(‘CLERK’,’MANAGER’)
AND HIREDATE LIKE ‘%81’
AND SAL>1500;
7.
List all the employees
whose name is having at least FIVE characters and joined in the year 80 or 81
and having a reporting manager with salary in the range 800 to 2000 in dept 30
or 40.
SELECT * FROM EMP
WHERE
WNAME LIKE ‘_____%’
AND
(HIREDATE LIKE ‘%80’ OR HIREDATE LIKE ‘%81’)
AND
MGR IS NOT NULL
AND
SAL BETWEEN 800 AND 2000
AND
DEPTNO IN(30,40);
8.
Display all the
employees whose name is having exactly 5 characters and having vowel as middle
character
9.
Display all the products
whose name is having 2 consecutive underscores
10.
Display all the
employees whose name starts with s and does not end with vowel
11.
Display all the products
whose name is having ' (single quote) as character
12.
Display all the
employees who is working as manager for only one employee
13.
List all the employees
where reporting manager ID is ending with the number 8?
SELECT
* FROM EMP WHERE MGR LIKE '%8';
14.
List all the employees
who's job is salesman and the salary ranges between 1500 to 3000?
SELECT
* FROM EMP WHERE JOB='SALESMAN' AND SAL BETWEEN 1500 AND 3000;
15.
List all the employees
who are working as managers and salesman in dept 20 & 30 with a salary in
the range of 1000 to 3000?
SELECT * FROM EMP WHERE JOB
IN('MANAGER','SALESMAN') AND DEPTNO IN(20,30) AND SAL BETWEEN 1000 AND 3000;
16.
List all the employees
who's salary is in the range of 1000 to 2000 in dept
10,20,30 except all clerks?
SELECT * FROM EMP WHERE SAL BETWEEN 1000 AND
2000 AND
DEPTNO
IN(10,20,30) AND JOB!='CLERK';
17.
List all the employees
who's names starts with letter A and who's salary is greater than 1000?
SELECT
* FROM EMP WHERE ENAME LIKE 'A%' AND SAL>1000;
18.
List all the student
name whose percentage is above 60.
SELECT SNAME FROM STUDENT WHERE PERC>60;
19.
Display all the employee
whose name has consecutive m in it.
SELECT
* FROM EMP WHERE ENAME LIKE '%MM%';
20.
Display all the employee
whose job not belongs to clerk and whose department number is 10 and 20.
SELECT * FROM EMP WHERE JOB!='CLERK' AND DEPTNO IN(10,20);
21.
List all the employee
names who as always two P's and whose salary ranges between 1000 to 3000.
SELECT
ENAME FROM EMP WHERE ENAME LIKE '%P%P%' AND SAL BETWEEN 1000 AND 3000;
22.
Display all the employee
name who don't have commission and working in department 10,20.
SELECT
ENAME FROM EMP WHERE COMM IS NULL AND DEPTNO IN (10,20);
23.
list all the employees
who is not having reporting manager in dept 20 and 30.
SELECT
* FROM EMP WHERE MGR IS NULL AND DEPTNO IN(20,30);
24.
list all the employees
whose name does not start with A
SELECT
ENAME FROM EMP WHERE ENAME NOT LIKE 'A%';
OR
SELECT
* FROM EMP WHERE ENAME NOT LIKE 'A%';
25.
display all the
employees who have joined in the year 87.
SELECT
* FROM EMP WHERE HIREDATE BETWEEN '01-JAN-87' AND '31-DEC-87';
OR
SELECT *
FROM EMP
WHERE HIREDATE LIKE '%87';
26.
list the employees who
have 2 T's in there name.
SELECT * FROM EMP WHERE ENAME LIKE ‘%T%T%’;
27.
list all the employees
whose employee no is not 7654.
SELECT * FROM EMP WHERE EMPNO!=7654;
28.
Write a query to display
all the salesmen in Dept 30
SELECT * FROM EMP WHERE JOB='SALESMAN' AND
DEPTNO=30;
29.
Write a query to display
the employees whose salary is 3000 and 2000
SELECT * FROM EMP WHERE SAL IN(3000,2000);
30.
List all the employees
whose name has at least one R in it.
SELECT ENAME FROM EMP WHERE ENAME LIKE '%R%';
31.
List all the employees
whose salary is between the range 800 and 2500
SELECT * FROM EMP WHERE SAL BETWEEN 800 AND
2500;
32.
List all the employees
except who works for Dept 30
SELECT * FROM EMP WHERE DEPTNO!=30;
33.
Display all the
employees who's analyst salary greater than equal to 2000.
SELECT * FROM EMP WHERE JOB='ANALYST' AND
SAL>=2000;
34.
Display all the
employees who's name end with T and start with A .
SELECT * FROM EMP WHERE ENAME LIKE 'A%T';
35.
Display all the
employees who are managers and with their annual salary
SELECT EMP.*, SAL*12 ANNUAL_SALARY FROM EMP
WHERE JOB='MANAGER';
36.
Display all the
employees who are salesmen and with their annual bonus
SELECT EMP.*, COMM*12 ANNUAL_BONUS FROM EMP
WHERE JOB='SALESMAN';
37.
Display all the
employees who's job name end with second character E
SELECT * FROM EMP WHERE JOB LIKE ‘%E_’;
38.
Display all the employee
who don't have commission and salary greater than 1000.
SELECT * FROM EMP WHERE COMM IS NULL AND
SAL>1000;
39.
Display department name
which 'LL' in their Location.
SELECT DNAME FROM DEPT WHERE LOC LIKE '%LL%';
40.
List all the employee
whose department is not 20 and name don't have 'M' as first letter.
SELECT * FROM EMP WHERE DEPTNO!=20 AND ENAME NOT
LIKE 'M%';
41.
List all the employee
with employee number between 7000 to 8000.
SELECT * FROM EMP WHERE EMPNO BETWEEN 7000 AND
8000;
42.
Display all the employee
whose salary not in the range from 1500 to 3000.
SELECT * FROM EMP WHERE SAL NOT BETWEEN 1500 AND
3000;
43.
Display all the
employees having greater than 1000 who joined in the month of December
and who having reporting manager ?
SELECT * FROM EMP WHERE SAL>1000 AND HIREDATE
LIKE '%DEC%' AND MGR IS NOT NULL;
44.
Display all the
employees who have the at least minimum 4 characters in their jobs in the
department 20?
SELECT * FROM EMP WHERE JOB LIKE '%____%' AND
DEPTNO=20;
45.
List all the employee
who are in clerk name end with s except department number 10 ?
SELECT * FROM EMP WHERE JOB='CLERK' AND ENAME
LIKE '%S' AND DEPTNO!=10;
46.
Display the
employee name should be 5 character?
SELECT ENAME FROM EMP WHERE ENAME LIKE ‘_____’;
47.
List all the employee
who are getting salary more than highest commission?
48.
Display the
sentence with a combination of ename ,sal ,deptno from emp table
SELECT ENAME, SAL,DEPTNO FROM EMP;
49.
Display the employee
whose second letter from the beginning is 'A' and the last second letter
is 'R'
SELECT * FROM EMP WHERE ENAME LIKE ‘_A%R_’;
50.
Display the employee who
is earning salary more than 300/- but not more than 3000/-
SELECT * FROM EMP WHERE SAL BETWEEN 300 AND 3000;
51.
Display the details of
analyst
SELECT * FROM EMP WHERE JOB=’ANALYST’;
52.
Display the details of
employee who does not take commission
SELECT * FROM EMP WHERE COMM IS NULL;
53.
List all employee with
annual salary with 500 rs bonus and working as manager joined in the month of
April and may
SELECT EMP.* ,SAL*12+500 ANNUAL_SALARY FROM EMP WHERE JOB=’MANAGER’ AND HIREDATE LIKE ‘%APR%’ OR HIREDATE LIKE ‘%MAY%’;
54.
Write a query to display
employee whose emp no starts with 78 and 79 whose designation contains at least
one E in dept no 20 and 30
SELECT * FROM EMP WHERE EMPNO LIKE '78%' OR EMPNO LIKE '79%' AND
JOB LIKE '%E%' AND DEPTNO IN(20,30);
55.
List all the manager working
in dept 30 and 20 hired between 02-jan-81 to 9-june-81
SELECT ENAME FROM EMP WHERE JOB=’MANAGER’ AND DEPTNO
IN(30,20) AND HIREDATE BETWEEN 02-JAN-81 AND 09-JUN-81;
56.
List the employee job
with hiredate whose name starts between A and S working in dept 30 and 20
SELECT ENAME,JOB,HIREDATE FROM EMP
WHERE ENAME BETWEEN 'A' AND 'S'
AND DEPTNO IN(30,20);
57.
Write a query to display
Dname which contains at least two S and at least one of O or A in their
location
SELECT DNAME FROM DEPT
WHERE DNAME LIKE ‘%S%S%’
AND (LOC LIKE ‘%O%’ OR LOC LIKE ‘%A%’);
58.
List all the manager
working in department 20 &30 hired between 02-jan-81 & 09-june-81.
SELECT * FROM EMP WHERE JOB='MANAGER' AND DEPTNO
IN(20,30) AND HIREDATE BETWEEN '02-JAN-81' AND ’09-JUN-81;
59.
Write a query to display
employees whose employee id starts with 78 & 79 whose designation contains
atleast one E and working in department 20 & 30.
SELECT * FROM EMP WHERE EMPNO LIKE '78%' OR EMPNO LIKE '79%' AND
JOB LIKE '%E%' AND DEPTNO IN(20,30);
60.
List the employee job
with hiredate whose name starts between 'A' & 'S' working in department 20
& 30 also display commission amount.
SELECT ENAME,JOB,HIREDATE ,COMM FROM EMP
WHERE ENAME BETWEEN 'A' AND 'S'
AND DEPTNO IN(30,20);
61.
Write a query to display
name which contains atleast two S & atleast one 'O' or 'A' in their
location from department table.
SELECT * FROM DEPT
WHERE DNAME LIKE ‘%S%S%’
AND (LOC LIKE ‘%O%’ OR LOC LIKE ‘%A%’;
62.
List all the employee
with annual salary with 5000rs bonus working as manager joined in the month of
april& may.
SELECT EMP.* ,SAL*12+5000 ANNUAL_SALARY FROM EMP
WHERE JOB=’MANAGER’
AND (HIREDATE
LIKE ‘%APR%’ OR HIREDATE LIKE ‘%MAY%’);
63.
List all the manager
working in department 20 &30 hired between 02-jan-81 & 09-june-81.
SELECT * FROM EMP WHERE JOB='MANAGER' AND DEPTNO
IN(20,30) AND HIREDATE BETWEEN '02-JAN-81' AND ’09-JUN-81;
64.
Write a query to display
employees whose employee id starts with 78 & 79 whose designation contains
atleast one E and working in department 20 & 30.
SELECT * FROM EMP
WHERE (EMPNO LIKE '78%' OR
EMPNO LIKE '79%')
AND JOB LIKE '%E%' AND DEPTNO
IN(20,30);
65.
List the employee job
with hiredate whose name starts between 'A' & 'S' working in department 20
& 30 also display commission amount.
SELECT ENAME,JOB,HIREDATE ,COMM FROM EMP
WHERE ENAME BETWEEN 'A' AND 'S'
AND DEPTNO IN(30,20);
66.
Write a query to display
name which contains atleast two S & atleast one 'O' or 'A' in their
location from department table.
SELECT * FROM DEPT
WHERE DNAME LIKE ‘%S%S%’
AND (LOC LIKE ‘%O%’ OR
LOC LIKE ‘%A%’;
67.
List all the employee
with annual salary with 5000rs bonus working as manager joined in the month of
april& may.
SELECT EMP.*
,SAL*12+5000 ANNUAL_SALARY FROM EMP
WHERE JOB=’MANAGER’
AND (HIREDATE LIKE ‘%APR%’ OR HIREDATE LIKE ‘%MAY%’);
68.
List all the employees
in dept 20 who have salary less than 500
SELECT * FROM EMP WHERE DEPTNO=20 AND
SAL<500;
69.
List all the employees
who work in dept 10
SELECT * FROM EMP WHERE DEPTNO=20;
70.
List all the employees
who should report to manager in dept 30
SELECT * FROM EMP WHERE MGR IS NOT NULL AND
DEPTNO=30;
71.
List all the employees
whose salry is greater than 3000
SELECT * FROM EMP WHERE SAL>3000;
72.
List all the employees
whose job is analyst
SELECT * FROM EMP WHERE JOB=ANALYST’;
73.
List the employees whose
salary in the range of 2000 to 3000 except in dept 10 and 20 in all clerks?
SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 3000 AND DEPTNO NOT
IN(10,20) AND JOB=’CLERKS’;
74.
List the employees whose
name is having letter 'E' as last but two character?
SELECT * FROM EMP WHERE ENAME LIKE ‘%E__’;
75.
list all the employees
who is having comm in dept 30?
SELECT * FROM EMP WHERE COMM IS NOT NULL AND DEPTNO=30;
76.
Display all the employee
whose earning salary in the range 2500 and 5000 in dept 10 and 20?
SELECT * FROM EMP
WHERE SAL BETWEEN 2500 AND 5000
AND DEPTNO NOT IN(10,20);
77.
list all the employees
who are not having reporting manager in dept 20?
SELECT * FROM EMP WHERE MGR IS NULL AND DEPTNO=20;
78.
List the employees who
are '5' no in second position in empno as well as mgr
SELECT * FROM EMP WHERE
EMPNO LIKE ‘_5%’ AND MGR LIKE ‘_5%’;
79.
List all the salgrade
whose lowsalary between 1000 to 2000
SELECT * FROM SALGRADE
WHERE LOSAL BETWEEN 1000 AND 2000;
80.
List the employees name
who's are having 3rd letter should 'A' in ename in it
SELECT * FROM EMP WHERE ENAME LIKE ‘__A%’;
81.
List all the manager who
earn less than 3000
SELECT * FROM EMP WHERE JOB=’MANAGER’ AND SAL<3000;
82.
List the departments
name whos are having 3rd letter should 'n' in it
SELECT DNAME FROM DEPT WHERE DNAME LIKE '__N%';
83.
Display all employees
working as a Clerk and Sal not more than 1500 in Dept 10,20,30.
SELECT * FROM EMP
WHERE JOB IN(‘CLERK’,’SALESMAN’)
AND SAL>1500 AND DEPTNO IN(10,20,30);
84.
List the employees whose
job and Ename having letter 'T'.
SELECT * FROM EMP
WHERE ENAME LIKE ‘%T%’ AND JOB LIKE ‘%T%’;
85.
List all employees
except who are having Commission
SELECT * FROM EMP WHERE COMM IS
NULL;
86.
Write a query to display
Deptname and location having character 'o'.
SELECT * FROM DEPT
WHERE
DNAME LIKE ‘%O%’ AND LOC LIKE ‘%O%’;
87.
List the employees whose
EmpNo ends with '9' and don't having reporting Manager.
SELECT * FROM EMP
WHERE EMPNO LIKE ‘%9’
AND MGR IS NULL;
88.
Display all the
employees whose joined between Feb and may.
89.
Display all the enames who
is having a reporting manager 7698.
SELECT ENAME FROM EMP WHERE MGR=7698;
90.
Display all the
employees whose getting highest salary in department 20
and 30.
91.
List all the enames in
department 30 who is having 'A'is one of the character .
SELECT ENAME FROM EMP
WHERE DEPTNO=30 AND ENAME LIKE ‘%A%’;
92.
List all the department
names and location with atleast 2O's.
SELECT * FROM DEPT
WHERE DNAME LIKE ‘%O%O%’ AND LOC LIKE ‘%O%O%’;
93.
List all employee whose
name starts with k and ends k?
SELECT * FROM EMP WHERE
ENAME LIKE “K%K’;
94.
List the department
which are having k in their location as well as their department name?
SELECT DNAME FROM DEPT
WHERE
DNAME LIKE ‘%K%’ AND LOC LIKE ‘%K%’;
95.
List all the employee is
having letter P in the 3rd position?
SELECT * FROM EMP WHERE ENAME LIKE ‘__P%’;
96.
List all the emp whoes
MANAGER employee number between 7698,7839 and salary ends with number 5?
SELECT * FROM EMP
WHERE MGR BETWEEN 7698 AND 7839 AND SAL LIKE
‘%5’;
97.
List all the employee
whose employee 3rd letter should 2 and his manager annual salary
between 30000 to 35000?
98.
List the employee who
joined in any year but not belongs to the month of march.
SELECT * FROM EMP
WHERE HIREDATE NOT LIKE '%MAR%';
99.
list the employee of
department no 30 or 10 joined in the year 81.
SELECT * FROM EMP
WHERE DEPTNO IN(30,10) AND HIREDATE LIKE ‘%81’;
100. Display the names of employee working in
department no 10 or 20 or 30 employee working as clerk,salesman or analyst.
SELECT ENAME FROM EMP
WHERE DEPTNO IN(10,20,30) AND JOB
IN(‘CLERK’,’ANALYST’,’SALESMAN’);
101. List the employees whose names having a
character get 'll' together.
SELECT * FROM EMP WHERE ENAME LIKE ‘%LL%’;
102. Display the details of the employee whose comm
is more than their salary.
SELECT * FROM EMP WHERE COMM>SAL;
103. List all the emp whose name has atleast one 'a'
in it.
SELECT ENAME FROM EMP WHERE ENAME LIKE ‘%a%’;
104. List all the emp in dept 30 whose sal >=
1500.
SELECT * FROM EMP WHERE DEPTNO=30 AND SAL>=1500;
105. List all the emp who are hired in year 81.
SELECT ENAME FROM EMP WHERE HIREDATE LIKE '%81';
106. List all the analyst in all the department.
SELECT * FROM EMP WHERE JOB=’ANALYST’;
107. List all the emp who are receiving commission.
SELECT * FROM EMP WHERE COMM IS NOT NULL;
108. List the employees whose name does not start
with ‘A’
SELECT ENAME FROM EMP WHERE ENAME NOT LIKE ‘A%’;
109. Display all the employee who are joined in JAN.
SELECT * FROM EMP WHERE HIREDATE LIKE ‘%JAN%’;
110. Display all the employee who are ‘MANAGER’s
having E as the last character in ename but salary having exactly 4 character.
SELECT * FROM EMP WHERE JOB=’MANAGER’ AND ENAME LIKE ‘%E’ AND SAL
LIKE ‘____’;
111. List all the employees who are having reporting
managers in dept 20.
SELECT * FROM EMP WHERE DEPTNO=20 AND MGR IS NOT
NULL;
112. Display the employees who are having employee no
as 7902,7934.
SELECT * FROM EMP WHERE EMPNO IN(7902,7934);
113. Display all the employees name start with J?
SELECT ENAME FROM EMP WHERE ENAME LIKE ‘J%’;
114. List all the employees whose Commission greater
than 100?
SELECT * FROM EMP WHERE COMM>100;
115. List all the employees who have joined in the
month of April?
SELECT * FROM EMP WHERE HIREDATE LIKE ‘%APR%’;
116. List all the employees whose salary is less than
5000?
SELECT * FROM EMP WHERE SAL<5000;
117. List all the employees who have joined in the year
82?
SELECT * FROM EMP WHERE HIREDATE LIKE ‘%82’;
118. Display all the employees who's job has string
'na' in it.
SELECT * FROM EMP WHERE JOB LIKE ‘%NA%’;
119. Display all the employees who joined after Feb
81
SELECT * FROM EMP
WHERE HIREDATE>’28-FEB-82’;
120. Display all the employees except those who are
working in dept10&30.
SELECT * FROM EMP WHERE DEPTNO NOT IN(10,30);
121. Display all the employees who's commission is
not null.
SELECT * FROM EMP WHERE COMM IS NOT NULL;
122. List all the employees who are having reporting
manager in dept 10&20.
SELECT * FROM EMP WHERE MGR IS NOT NULL AND
DEPTNO IN(10,20);
123. Display all the employees whose job has string
‘ANA’ in it.
SELECT * FROM EMP WHERE JOB LIKE ‘%ANA%’;
124. Display all the presidents in department 10.
SELECT * FROM EMP WHERE JOB=’PRESIDENT’ AND
DEPTNO=10;
125. List the employees whose hire date is before feb
81.
SELECT * FROM EMP
WHERE
HIREDATE<’01-FEB-81’;
126. Display all the employees whose name starts with
alphabet ‘B’ and ends with a ‘E’ and salary more than 2200.
SELECT * FROM EMP
WHERE ENAME LIKE ‘B%E’ AND SAL>2200;
127. Display the students whose name is having both
first character as ’B’ and last character as a vowel and percentage more than
85 but less than 90.
128. Select all the employees whose name 4th
character starts with 'n' and 1st character start with 'a' or 's' who is
working in department 30
SELECT * FROM EMP
WHERE ENAME LIKE ‘___N%’
AND (ENAME LIKE ‘A%’ OR ENAME LIKE ‘S%’)
AND DEPTNO=30;
129. List all the employees who is working in
department 10 and salary in the rage of 1500 and 5000 and having commission
>=500
SELECT ENAME FROM EMP
WHERE DEPTNO=10
AND SAL
BETWEEN 1500 AND 5000
AND COMM>=500;
130. List the employee except those who are working
in dept 10 & 30 and joined after 03-Dec-81
SELECT * FROM EMP
WHERE DEPTNO NOT IN(10,30)
AND HIREDATE>’03-DEC-81’;
131. List all the employees whose name having
consecutive 'SS' and working in dept
10
whose salary in the range of 500 and 1500
SELECT * FROM EMP
WHERE ENAME LIKE ‘%SS%’
AND DEPTNO=10
AND SAL BETWEEN 500 AND 1500;
132. List those employees who are having reporting
manager in dept 20,10,30 and name end with 'S' with salary in the range
of 2000 and 3000 and joined before 19-June-87
SELECT * FROM EMP
WHERE MGR IS NOT NULL
AND DEPTNO IN(10,20,30)
AND SAL BETWEEN 2000 AND 3000
AND HIREDATE<’19-JUN-87’;
133. list all the employees whose names end with TT.
SELECT * FROM EMP WHERE ENAME LIKE ‘%TT’;
134. list all the employees who have commission of
500 or more.
SELECT * FROM EMP WHERE COMM>=500;
135. list all the employees whose salary is between
the range 500 and 2000 and belongs to dept 20 and dept 30
SELECT * FROM EMP WHERE SAL BETWEEN 500 AND 2000
AND DEPTNO IN(20,30);
136. list all the salesman and analyst who work in
dept 20 and dept 30
SELECT * FROM EMP WHERE JOB IN(‘SALESMAN’,’ANALYST’)
AND DEPTNO IN(20,30);
137. list all the employees whose salary is less than
1000 and doesnt belong to
dept 20
SELECT
* FROM EMP WHERE SAL<1000 AND DEPTNO!=20;
138. List all the analyst in department number 20 and
having salary greater
than
2000.
SELECT
* FROM EMP
WHERE JOB=’ANALYST’ AND DEPTNO=20 AND
SAL>2000;
139. List all the employees except clerk and analyst
and who are not earning salary in the range 1000,3000.
SELECT * FROM EMP
WHERE JOB NOT IN(‘CLERK’,’ANALYST’)
AND (SAL<1000 OR SAL>3000);
OR
SELECT * FROM EMP
WHERE JOB NOT IN('CLERK','ANALYST')
AND SAL NOT BETWEEN 1000 AND 3000;
140. Select all the salesman whose name second
character starts with 'A' and who have joined after 20-FEB-81.
SELECT * FROM EMP
WHERE JOB IN(‘SALESMAN’)
AND ENAME LIKE ‘_A%’
AND HIREDATE>’02-FEB-81’;
141. List all the employees who are getting 3000 and
excess salaries working in department 10 and 20.
SELECT * FROM EMP WHERE SAL>=3000 AND DEPTNO
IN(10,20);
142. Select all the salesman whose name has atleast 1
character 'R' and whose salary is less than 1500 and who are joined after
21-FEB-81.
SELECT * FROM EMP
WHERE JOB IN(‘SALESMAN’)
AND ENAME LIKE ‘%R%’
AND SAL<1500
AND HIREDATE>’21-FEB-81’;
143. Write a query to display all the employee
details who joined on the year 81 and who has A in his employee name and who
gets a commission in the deptno starting with 3.
SELECT * FROM EMP
WHERE HIREDATE LIKE ‘%81’
AND ENAME LIKE ‘%A%’ AND COMM IS NOT NULL
AND DEPTNO LIKE ‘3%’;
144. Write a query to list all the employees who has a
manager and has annual salary greater than 10000 and his empID is between 7850
to 7950 and who works as a analyst.
SELECT * FROM EMP
WHERE MGR IS NOT NULL AND (SAL*12)>10000
AND EMPID BETWEEN 7850 AND 7950
AND JOB=’ANALYST’;
145. Write a query to list all the department details
where department name has E or A and whose location has A and whose deptno is
30.
SELECT * FROM DEPT
WHERE (DNAME LIKE ‘%E%’ OR DNAME LIKE ‘%A%’)
AND LOC LIKE ‘%A%’
AND DEPTNO=30;
146. Write a query to display the employees who has
salary less than 2000 and more than 3000 and who joined on NOV or SEP and gets
a total monthly salary of less than 2000 including commision.
SELECT * FROM EMP
WHERE (SAL<2000 OR SAL>3000)
AND (HIREDATE LIKE '%NOV%' OR HIREDATE LIKE
'%SEP%')
AND (SAL+COMM)<2000;
147. Write a query to display the employee details
and quaterly salary of employees except SALESMAN whose salary i more than 500
but less than 2000 and name starts with S.
SELECT EMP.*, SAL/4 QUATERLY_SALARY FROM EMP
WHERE JOB!=’SALESMAN’
AND SAL BETWEEN 500 AND 2000
AND ENAME LIKE ‘S%’;
148. list all the names with 'E' present in their
last names and their salaries is between 6000 and 12000
SELECT * FROM EMPLOYEES
WHERE LAST_NAME LIKE
‘%E%’
AND SALARY BETWEEN 6000
AND 12000;
149. hint: login using HR
150. list all the names of employees who are not
working as 'IT Programmer or clerk' and whose sal is more than 3000.
SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES
WHERE JOB_ID NOT IN(‘IT_PROG’,’ST_CLERK’)
AND SALARY>3000;
151. List all the departments starting with location no
1700 and those who have managers.
SELECT * FROM
DEPARTMENTS WHERE LOCATION_ID=1700 AND MANAGER_IN IS NOT NULL;
152. hint:refer departments table in HR
153. list all the employee first and last name
followed by 'your job is ' and the job along with their salaries greater than
9000
SELECT FIRST_NAME||’ ‘||LAST_NAME||’ YOUR JOB IS
‘||JOB_ID,SALARY FROM EMPLOYEES
WHERE SALARY>9000;
154. list all the employees first name followed by '
your salary is ' salary and their phone number should contain atleast '1'
number somewhere and they must not be Sales representative and hr
representative.
SELECT FIRST_NAME||’ YOUR SALARY IS ‘||SALARY,
PHONE_NUMBER FROM EMPLOYEES
WHERE PHONE_NUMBER LIKE ‘%1%’
AND JOB_ID NOT IN(‘SA_MAN’,’ HR_REP’);
155. Display only the manager codes of the employees
whose salary is more than 1000 and hiring date is in the year 81
SELECT MGR FROM EMP
WHERE SAL>1000
AND HIREDATE LIKE ‘%81’;
156. Display all the employees who report to the
manager 7698 and are salesman whose names end with N
SELECT * FROM EMP WHERE MGR=7698 AND
JOB=’SALESMAN’ AND ENAME LIKE ‘%N’;
157. Display only the employee name along with the
joining date in one column from department 30 and the joining date is in the
year 81
SELECT
ENAME||’ ‘||HIREDATE FROM EMP
WHERE DEPTNO=30
AND HIREDATE LIKE ‘%81’;
158. List the employees who are Clerks and salary is
above 1000 and the hired date is after year 81
SELECT * FROM EMP
WHERE
JOB=’CLERK’
AND SAL>1000 AND HIREDATE>’31-DEC-81’;
159. List the employees whose salary is between 1000
to 3000 except clerks whose name third character starts with A
SELECT * FROM EMP
WHERE SAL BETWEEN 1000 AND 3000
AND JOB!=’CLERK’
AND ENAME LIKE ‘__A%’;
160. List all the employees except salesman and
manger in deptno 10
SELECT * FROM EMP
WHERE JOB NOT IN(‘SALESMAN’,’MANAGER’)
AND DEPTNO=10;
161. List all the employees who are nor earning
salary in the range 1500 and 3000
SELECT * FROM EMP
WHERE SAL<1500 OR SAL>3000;
162. Display all the employees who are 'MANAGER'
having J as first character in ename
SELECT * FROM EMP
WHERE JOB=’MANAGER’
AND ENAME LIKE ‘J%’;
163. List all the salesman in deptno 20 and having
greater than 2000
SELECT * FROM EMP
WHERE JOB=’SALESMAN’ AND DEPTNO=20 AND SAL>2000;
164. List all the employees whose name starts with
'J' or 'F'
SELECT * FROM EMP
WHERE (ENAME LIKE ‘J%’ OR ENAME LIKE ‘F%’);
165. Display the names who are not salesman and
analyst and whose salary is more than 1000 working in department 20
SELECT ENAME FROM EMP
WHERE JOB NOT IN(‘SALESMAN’,’ANALYST’)
AND SAL>1000 AND DEPTNO=20;
166. display the name whose last but not two is L
having some commission and does not belong department 10 and 20
SELECT
ENAME FROM EMP
WHERE ENAME LIKE ‘%L__’
AND COMM IS NOT NULL
AND DEPTNO NOT IN(10,20);
167. salesman whose salary is ending with 5
SELECT ENAME FROM EMP
WHERE JOB=’SALESMAN’ AND SAL LIKE ‘%5’;
168. employees joined between 80 and 81
SELECT * FROM EMP
WHERE HIREDATE BETWEEN ’01-JAN-80’ AND
’31-DEC-81’;
169. list all the salesman whose name consists of n
SELECT ENAME FROM EMP
WHERE JOB=’SALESMAN’ AND ENAME LIKE ‘%N%’;
170. List all employees who is analyst ,clerk and
salesman
SELECT * FROM EMP
WHERE JOB IN(‘ANALYST’,’SALESMAN’,’CLERK’);
171. List all the employees whose name start with a
and end with n
SELECT *FROM EMP WHERE ENAME LIKE ‘A%N’;
172. List all the employees Whose name doesn't start
with n
SELECT ENAME FROM EMP WHERE ENAME NOT LIKE 'N%';
173. List all the employees whose name having 5
characters and salary between 2000 and 20000
SELECT ENAME FROM EMP
WHERE ENAME LIKE ‘_____’
AND SAL BETWEEN 2000 AND 20000;
174. Display all employees whose is working as
analyst and salesman and working in dept 10 and 20 and salary less than 30000
SELECT * FROM EMP
WHERE JOB IN(‘ANALYST’,’SALESMAN’)
AND DEPTNO IN(10,20) AND SAL<30000;
175. List all the employees who joined in the year 82
and salary greater than 1000
SELECT * FROM EMP
WHERE HIREDATE LIKE '%82' AND SAL>1000;
176. List all the employees who are salesman and not
have any commission
SELECT * FROM EMP WHERE JOB=’SALESMAN’ AND COMM
IS NULL;
177. List all the employees whose ratio of commission
to salary is greater than 0.5
SELECT * FROM EMP
WHERE (COMM/SAL)>0.5;
178. Display the name and job of the employees whose
name second character is 'L' and designation is not 'SALESMAN'
SELECT ENAME,JOB FROM EMP WHERE ENAME LIKE ‘_L%’
AND JOB!=’SALESMAN’;
179. Display all the employees whose name 3rd
character is 'R' and getting some commission
SELECT * FROM EMP WHERE ENAME LIKE '__R%' AND
COMM IS NOT NULL;
180. select all the employees having
commision >1000 join the job on sep whose name having 'T' last but two
and dept no is not 10 or 20.
SELECT * FROM EMP
WHERE COMM>1000 AND HIREDATE LIKE ‘%SEP%’
AND ENAME LIKE ‘%T__’ AND DEPTNO NOT IN( 10,20);
181. select all the employees who join on date 17th
of any year there sal is equal to 800 or 5000 and there deptno not equal
to 30
SELECT * FROM EMP WHERE HIREDATE LIKE '17%'
AND (SAL=800 OR SAL=5000) AND DEPTNO!=30;
OR
SELECT * FROM EMP WHERE HIREDATE LIKE '17%' AND
SAL IN(800,5000) AND DEPTNO!=30;
182. select all the analyst whose name not having 2
consecutine 't's & sal is between 2000 and 3000 not belongs to deptno
10 or 20.
SELECT * FROM EMP
WHERE JOB='ANALYST' AND
ENAME NOT LIKE '%TT%'
AND SAL BETWEEN 2000 AND 3000
AND DEPTNO NOT IN(10,20);
183. select all the employee who's name having 2
consecutive 'l' they neither the salesman nor manager having sal <2000
& joining on 1st month of any year.
SELECT * FROM EMP
WHERE ENAME LIKE ‘%LL%’
AND JOB NOT IN(‘SALESMAN’,’MANAGER’)
AND SAL<2000 AND HIREDATE LIKE ‘%JAN%’;
184. list all the employes whose job is analyst and
name starts with the 3 rd character as S and the salary more than 3000.
SELECT * FROM
EMP
WHERE JOB=’ANALYST’ A
ND ENAME LIKE ‘__S%’ AND SAL>3000;
185. list all the employees whose job is salesman and
hiredate is between 17-dec-18 to 08-dec-81 and deptno as 30
SELECT * FROM EMP
WHERE JOB=’SALESMAN’
AND HIREDATE BETWEEN ’17-DEC-18’ AND ’08-DEC-81’
AND DEPTNO=30;
186. list all the employees whose name is king and
the salary is greater than 5000 in the dept 10 and 20
SELECT * FROM EMP
WHERE ENAME=UPPER(‘King’) AND SAL>5000
AND DEPTNO IN(10,20);
187. list all the employees whose salary is more then
4000 and commission is null and deptno 10
SELECT * FROM EMP
WHERE SAL>4000 AND COMM IS NULL AND
DEPTNO=10;
188. list all the employees whose reporting manager
is not null and the deptno is 10 and 30 and salary is more than 3000
SELECT * FROM EMP
WHERE MGR IS NOT NULL
AND DEPTNO IN(10,30) AND SAL>3000;
189. List all the employees in dept 20, earning more
than Rs 2500 and not having commission and whose name does not start with
‘S’.
SELECT * FROM EMP
WHERE DEPTNO=20 AND SAL>2500 AND
COMM IS NULL
AND ENAME NOT LIKE ‘S%’;
190. Display all the employee whose earning salary
not in the range 2500 and 5000 in dept 10 and 20 whose annual salary is ending
with zero.
SELECT EMP.*,SAL*12 ANNUAL_SALARY FROM EMP
WHERE SAL NOT BETWEEN 2500 AND 5000
AND
DEPTNO IN(10,20)
AND SAL*12 LIKE ';
191. Display all the employee who are joined after
year 81, not working as managers and clerks in dept 10 and 20 with a salary in
the range of 1000 to 3000.
SELECT * FROM EMP
WHERE HIREDATE>’31-DEC-81’
AND JOB NOT IN(‘MANAGERS’,’CLERK’)
AND DEPTNO IN(10,20) AND SAL BETWEEN 1000 AND
3000;
192. List the department names, Locations which are
having letter ‘O’ in their locations as well as their department names NOT IN
DEPT 20 AND 40.
SELECT DNAME,LOC FROM DEPT
WHERE DNAME LIKE '%O%' AND LOC LIKE '%O%'
AND DEPTNO NOT IN(20,40);
193. Select all the employees whose name start with s
or A in departmetment 20 and
30.
whose are joined before 01-may-81 and after 01-may-80.
SELECT
* FROM EMP
WHERE (ENAME LIKE 'S%' OR ENAME LIKE 'A%')
AND DEPTNO IN(20,30)
AND HIREDATE
BETWEEN '01-MAY-80' AND '01-MAY-81'
194. List all the employees whose salary is
between 1000 and 3000 who are salesman not working in department 10,20 with
commission greater than 1000.
SELECT * FROM EMP
WHERE SAL BETWEEN 1000 AND 3000
AND JOB=’SALESMAN’ AND DEPTNO NOT IN(10,20)
AND COMM>1000;
195. List all the department name where department
number is greater than 20 and location has atleast one character a.
SELECT DNAME FROM DEPT
WHERE DEPTNO>20 AND LOC LIKE ‘%A%’;
196. List all the clerk whose salary is less than
1000 who joined after year 75 and also display their annual salary.
SELECT EMP.* ,SAL*12 ANNUAL_SALARY FROM EMP
WHERE JOB=’CLERK’ AND SAL<1000
AND HIREDATE>’31-DEC-75’;
197. List all the managers,analyst and salesman with
annual commission in department 10 and 30 with salary less than 2000 and also
display their annual salary of 2 years.
SELECT ENAME, SAL,(SAL*12*2) YR ANNUAL_SALARY ,(COMM*12)
ANNUAL_COMM FROM EMP
WHERE JOB IN(‘MANAGER’,’ANALYST’,’SALESMAN’) AND
DEPTNO IN(10,30) AND SAL<2000 ;
198. List all the employees who joined afer year 80
and having salary is between
2500
and 4000 and also calculate their salary after six months.
SELECT EMP.*,SAL*6 SIXMONTHSALARY FROM EMP
WHERE HIREDATE>’31-DEC-80’
AND SAL BETWEEN 2500 AND 4000;
199. select all the manager whose salary is more than
2500 and department of
20
or 30.
SELECT
* FROM EMP
WHERE
JOB=’MANAGER’
AND
SAL>2500 AND DEPTNO IN(20,30);
200. select all the employee whose employee no second
digit is 8 and who do not have manager.
SELECT * FROM EMP
WHERE EMPNO LIKE ‘_8%’
AND MGR IS NULL;
201. select all the employee who work in department
10, 20, 30 and salary is more than 2000 and who are joined in between the year
85 to 87.
SELECT * FROM EMP
WHERE DEPTNO IN(10,20,30)
AND SAL>2000
AND HIREDATE BETWEEN ’01-JAN-85’ AND
’31-DEC-87’;
202. list all the department no which is having
concatenate L in location name.
SELECT
DEPTNO FROM DEPT
WHERE LOC LIKE ‘%L%’;
203. list all the employee name who earn more than
1500 salary with commission and working in department 20 and 30 and employee
name consists at least one A.
SELECT ENAME FROM EMP
WHERE (SAL+COMM)>1500
AND DEPTNO IN(20,30)
AND ENAME LIKE ‘%A%’;
204. Display all the employee whose job is clerk and
annual salary ends with double
zero?
SELECT
* FROM EMP
WHERE JOB=’CLERK’
AND (SAL*12) LIKE ‘’;
205. Display all the employee name whose name starts
with 'A' and salary is range from 1000 to 3000?
SELECT ENAME FROM EMP
WHERE ENAME LIKE ‘A%’
AND SAL BETWEEN 1000 AND 3000;
206. Display employee name who have exactly 5
characters in their name and who works in dept 30?
SELECT ENAME FROM EMP
WHERE ENAME LIKE '_____'
AND DEPTNO=30;
207. Display all the employee whose having reporting
manager from dept 20 and dept 30?
SELECT * FROM EMP
WHERE MGR IS NOT NULL
AND DEPTNO IN(20,30);
208. display employee number whose name doesn't
ends with 'S'?
SELECT EMPNO FROM EMP
WHERE ENAME NOT LIKE ‘%S’;
209. List all employees who is working as clerk in
dept 30 and getting salary above
2000
SELECT * FROM EMP
WHERE JOB=’CLERK’
AND DEPTNO=30
AND SAL>2000;
210. List all employees who are having R as third
character in their name, not working as clerk and joined after JUN-81
SELECT * FROM EMP
WHERE ENAME LIKE ‘__R%’
AND JOB!=’CLERK’
AND HIREDATE>’30-JUN-81’;
211. List all employees who are working under same
manager
212. List all departments which are having A as last
before character in their location whose deptno is not 10
SELECT * FROM DEPT
WHERE LOC LIKE ‘%A_’
AND
DEPTNO!=10;
213. List all the managers who don't get a commission
in deptno 20 and 30
SELECT * FROM EMP
WHERE JOB=’MANAGER’
AND COMM IS NULL
AND DEPTNO IN(20,30);
214. Display all employees with names containing
characters A and R together in it and are getting commission
SELECT * FROM EMP
WHERE ENAME LIKE ‘%AR%’
AND COMM IS NOT NULL;
215. Display employees with same salary
216. Display all employees except salary range
between 1000 and 5000 and are hired after year 1980
SELECT * FROM EMP
WHERE (SAL<1000 OR SAL>5000)
AND HIREDATE>'31-DEC-80';
217. Display all department names and location
containing string 'ON' in it
SELECT * FROM DEPT
WHERE DNAME LIKE ‘%ON%’
AND LOC LIKE ‘%ON%’;
218. Display all employees who were hired in year
1981 and doesn't have a reporting manager
SELECT * FROM EMP
WHERE HIREDATE LIKE ‘%81’
AND MGR IS NULL;
219. List all the employees whose getting salary less
than 1000 and whose name start with s and end with h and whose working in dept
20.
SELECT * FROM EMP
WHERE SAL<1000
AND LOWER(ENAME) LIKE 's%h' AND
DEPTNO=20;
220. Display all the employees who are joined after
17-dec-80 and getting salary greater than 2000 and their jobs are analyst. And
whose name start with S and end with T.
SELECT * FROM EMP
WHERE HIREDATE >’17-DEC-80’
AND SAL>2000 AND JOB=’ANALYST’
AND ENAME LIKE ‘S%T’;
221. Display all the employees whose name having
atleast one character O and his job is analyst and whose joined after
17-nov-81.
SELECT * FROM EMP
WHERE ENAME LIKE ‘%O%’
AND JOB=’ANALYST’
AND HIREDATE >’17-NOV-81’;
222. Display all the employees who are salesman,
analyst, president and manager and their salary is greater than 1000 along with
the employees whose getting commission.
SELECT * FROM EMP
WHERE JOB IN(‘SALESMAN’, ‘ANALYST’, ‘PRESIDENT’,’
MANAGER’)
AND SAL>1000 AND COMM IS NOT NULL;
223. Display all the employees who are not working as
manager and analyst in dept
20
and 30 and whose getting salary range between 1000 to 3000 and their name
having
atleast one O.
SELECT * FROM EMP
WHERE JOB NOT IN(‘MANAGER’,’ANALYST’)
AND DEPTNO IN(20,30)
AND SAL BETWEEN 1000 AND 3000
AND ENAME LIKE ‘%O%’;
224. list all the employees who are working as
salesmen and analyst in dept 20 and 30 with a salary in the range of 1000 and
2000?
SELECT * FROM EMP
WHERE JOB IN(‘SALESMAN’,’ANALYST’)
AND DEPTNO IN(20,30)
AND SAL BETWEEN 1000 AND 2000;
225. list all the employees whose salary is not in
the range of 2000 to 3000 in dept 10 except all clerks?
SELECT * FROM EMP
WHERE (SAL<2000 OR SAL>3000)
AND
DEPTNO=10
AND JOB<>’CLERK’;
226. display all the salesman whose quarterly salary
ends with 5?
SELECT * FROM EMP
WHERE (SAL/4) LIKE '%5';
227. list all the employees from the accounting
department and all the employees who are located at boston?
228. display all the employees whose names
start with either w or m?
SELECT * FROM EMP
WHERE ENAME LIKE ‘W%’ OR ENAME LIKE ‘M%’;
229. List all employee number whose employee number
ending with at least 4 or 7.
SELECT EMPNO FROM EMP
WHERE EMPNO LIKE '%4' OR EMPNO LIKE '%7';
230. List all employee whose manager is either blake
or king
231. List all employee whose date of joining is
before 82 and salary should be more then 2000.
SELECT * FROM EMP
WHERE HIREDATE<’01-JAN-82’
AND SAL>2000;
232. List all employee whose name start with A and
has A in between
SELECT * FROM EMP
WHERE ENAME LIKE ‘A%A%’;
233. List all employee whose manager is king and have
salary more then 1500.
234. Display all the employees in department 10 whose
salaries are lesser than 1000.
SELECT * FROM EMP
WHERE DEPTNO=10
AND SAL<1000;
235. Display all the employees who are getting some
commission with their designation is neither manager nor clerk.
SELECT * FROM EMP
WHERE COMM IS NOT NULL
AND JOB NOT IN(‘MANAGER’,’CLERK’);
236. Select * from emp where COMM IS not null And JOB
NOT IN ('MANAGER','CLERK');
237. Display all the employees who have joined in the
month of December.
SELECT * FROM EMP
WHERE HIREDATE LIKE ‘%DEC%’;
238. Display all the employees whose name is having
two consecutive L's .
SELECT * FROM EMP
WHERE ENAME LIKE ‘%LL%’;
239. Display all the employees whose salary is not in
the range 2000 and 5000 in department 10 and 20
SELECT * FROM EMP
WHERE (SAL<2000 OR
SAL>5000)
AND DEPTNO IN(10,20);
240. List all the employees whose job is neither
clerk nor salesman in dept 10
SELECT * FROM EMP
WHERE JOB NOT IN(‘SALESMAN’,’CLERK’)
AND DEPTNO=10;
241. List all the location name whose name has
atleast one o in department 30
and
40
SELECT
LOC FROM DEPT
WHERE LOC LIKE ‘%O%’
AND DEPTNO IN (30,40);
242. List all the employees whose job is not clerk
and annual salary is more than 1000
SELECT * FROM EMP
WHERE JOB!=’CLERK’
AND (SAL*12)>1000;
243. Select the employees whose names has 2
consecutive l's
SELECT ENAME FROM EMP
WHERE ENAME LIKE ‘%LL%’;
244. Select all the employees whose emp number end
with 9
SELECT * FROM EMP
WHERE EMPNO LIKE ‘%9’;
245. List all the employees who have joined except on
apr and nov an sal more than 2500
SELECT *
FROM EMP
WHERE (HIREDATE NOT LIKE ‘%APR%’ OR HIREDATE NOT
LIKE ‘%NOV%’) AND SAL>2500;
246. List all the employees whose annual salary is
equal to or more than 10000.
SELECT * FROM EMP
WHERE (SAL*12)>=10000;
247. Display only ename whose name has two
consecutive T's .
SELECT ENAME FROM EMP
WHERE ENAME LIKE ‘%TT%’;
248. List ename, job and deptno who does have
reporting manager.
SELECT ENAME,JOB,DEPTNO FROM EMP
WHERE MGR IS NULL;
249. Display all the employees whose salary is less
than 1000 in dept 10 and 30.
SELECT * FROM EMP
WHERE SAL<1000
AND DEPTNO IN(10,30);
250. list all the employees whose salary not in range
of 1000 and 2000 in dept 10,20 except manager
SELECT * FROM EMP
WHERE SAL NOT BETWEEN 1000 AND 2000
AND DEPTNO IN(10,20)
AND JOB <>'MANAGER';
251. list all the employees whose salary between 1000
and 1500 working in dept 20,30 except clerk and salesman
SELECT * FROM EMP
WHERE SAL BETWEEN 1000
AND 1500
AND DEPTNO IN(20,30)
AND JOB NOT
IN(’CLERK’,’SALESMAN’);
252. list the department names which are having
letter 'A' in their locations as well as department name
SELECT DNAME FROM DEPT
WHERE DNAME LIKE '%A%'
AND LOC LIKE '%A%';
253. Display all the employees who are 'manager'
having 's' in their last character in ename
SELECT * FROM EMP
WHERE JOB=’MANAGER’
AND ENAME LIKE ‘%S’;
254. Display all the employees who have joined after
02-feb-80 except salesman
SELECT * FROM EMP
WHERE HIREDATE>’02-FEB-80’
AND JOB!=’SALAEMAN’;
255. What is an Operator in SQL?
Operators are the symbols used to perform some
specific operation. The input required for operator are called operands.
256. What is the purpose of the condition operators
BETWEEN and IN?
257. display all the students who are ME or CIVIL
with percentage greater
than
75%.
SELECT
* FROM STUDENT
WHERE BRANCH IN(‘ME’,’CIVIL’)
AND PERCENTAGE>75;
258. what are the operators in SQL? and list
the presidense of operators?
259. list all employees except those who are not
working in dept 10,20 & 30.
SELECT * FROM EMP
WHERE DEPTNO NOT IN(10,20,30);
260. Display all the manager whose annual Salary is
ending with one ?
SELECT * FROM EMP
WHERE JOB=’MANAGER’
AND SAL*12 LIKE ‘%1’;
261. Select all the salesman whose salary not between
500 and 1000 in
department 20 ?
SELECT
* FROM EMP
WHERE JOB=’SALESMAN’
AND SAL NOT BETWEEN 500 AND 1000
AND DEPTNO=20;
262. Display all the employee who are getting some
commission with their designation is MANAGER and CLERK ?
SELECT * FROM EMP
WHERE COMM IS NOT NULL
AND JOB IN(‘MANAGER’,’CLERK’);
263. List all the salesmen in dept number 10 and
having salary greater than 2500 ?
SELECT * FROM EMP
WHERE JOB=’SALESMAN’
AND DEPTNO=10
AND SAL>2500;
264. List all the employees whose name starts with
‘k’ or ‘p’ ?
SELECT * FROM EMP
WHERE( ENAME LIKE ‘K%’ OR ENAME LIKE ‘P%’);
265. Display employees from whose name is having
letter ‘R’ in it?
SELECT * FROM EMP
WHERE ENAME LIKE ‘%R%’;
266. List all the employees whose name starts with
‘M’ or ‘K’ or 'N' or 'N'?
SELECT * FROM EMP
WHERE (ENAME LIKE ‘M%’ OR ENAME LIKE ‘K%’
OR ENAME
LIKE ‘N%’);
267. Display all the employee who are ‘SALESMAN’s
having R as the last character in ename but salary having exactly 4 character
SELECT * FROM EMP
WHERE JOB=’SALESMAN’
AND ENAME LIKE ‘%R’
AND SAL LIKE ‘____’;
268. Display all the employees whose job has string
‘LES’ in it.
SELECT * FROM EMP
WHERE JOB LIKE ‘%LES%’;
269. Select all the clerk whose name 4th character
start with 'L' and earning salary less than 2000
SELECT * FROM EMP
WHERE ENAME LIKE ‘___L%’
AND SAL<2000;
270. List the employees whose name doesn't have
letter S in it?
SELECT * FROM EMP
WHERE ENAME NOT LIKE '%S%';
271. List the employees whose salary is not in range
from 1000 to 2000 in dept 20 and 30 except all clerk and analyst?
SELECT * FROM EMP
WHERE SAL NOT BETWEEN 1000 AND 2000
AND DEPTNO IN(20,30)
AND JOB NOT IN(‘CLERK’,’ANALYST’);
272. List the employees whose name start with S and
ends with H
SELECT * FROM EMP
WHERE ENAME LIKE ‘S%H’;
273. List the employees whose salary is >1500 and
<2500
SELECT * FROM EMP
WHERE (SAL>1500 OR SAL<2500);
274. List the salesman whose ename 2nd char is M and
salary in range from 500 to 1500 in it?
SELECT * FROM EMP
WHERE ENAME LIKE ‘_M%’
AND SAL BETWEEN 500 AND 1500;
275. List all the employee's names,jobs,salary, who
has at least one 's' in their name, earn more than 2000 but not getting any
commission in dept 10 and 20.
SELECT ENAME,JOB,SAL FROM EMP
WHERE ENAME LIKE ‘%S%’
AND SAL>2000 AND COMM IS NULL
AND DEPTNO IN(10,20);
276. List out all the managers and clerks who joined
in year 81 and earns more than 2000 but not getting any commission.
SELECT * FROM EMP
WHERE JOB IN(’MANAGER’,’CLERK’)
AND HIREDATE LIKE ‘%81’
AND SAL>2000 AND COMM IS NULL;
277. list all the departments which has at least one
'o' in it's location,which has department number exactly of 2 digits and
department name doesn't have any '%' character
SELECT * FROM DEPT
WHERE LOC LIKE ‘%O%’
AND DEPTNO LIKE ‘__’ ;
278. list all the employees whose employee number
doesn't end with 2, name doesn't end with 'N'or 'k', hired before 81 and earns
more than 800 and salary contains at least one zero digit.
SELECT * FROM EMP
WHERE EMPNO NOT LIKE ‘%2’
AND (ENAME NOT LIKE ‘%N’ OR ENAME NOT LIKE ‘%K’)
AND HIREDATE<’31-DEC-81’
AND SAL>800 AND SAL LIKE ‘%0%’;
279. List all the employees who has reporting
manager, whose name doesn't have any R or A character, who earns between 2000
and 3000, who joined after 80 and before 83, works in department 10,20,30.
SELECT * FROM EMP
WHERE MGR IS NOT NULL
AND
(ENAME NOT LIKE ‘%R%’ OR ENAME NOT LIKE ‘%A%’)
AND SAL BETWEEN 2000 AND 3000
AND (HIREDATE>’31-DEC-80’ AND
HIREDATE<’01-JAN-83’)
AND DEPTNO IN(10,20,30);
280. Display all the employee who has a joining date
on JANUARY, NOVEMBER and APRIL in department 20,10 except 30, also having
salary more than 2500.
SELECT * FROM EMP
WHERE (HIREDATE LIKE ‘%JAN%’ OR HIREDATE LIKE
‘%NOV%’ OR HIREDATE LIKE ‘%APR%’
AND DEPTNO IN(10,20)
AND SAL>2500;
281. Display employee name who has their name
starting with letters A, J and K also having salary in the range 800 to 2500
with not getting any commission.
SELECT * FROM EMP
WHERE
(ENAME LIKE ‘A%’ OR ENAME LIKE ‘J%’ OR ENAME LIKE ‘K%’) AND SAL BETWEEN
800 AND 2500
AND COMM IS NULL;
282. Display the employee's name whose gross salary
is more than 1500.
SELECT * FROM EMP
WHERE (SAL+COMM)>1500;
283. Display employee name who has their employee
number between 7521 to 7782.
SELECT ENAME
FROM EMP
WHERE EMPNO BETWEEN 7521 AND 7782;
284. Display employee number who doesn't get any
commission in department 30 but getting salary exactly 1600.
SELECT EMPNO FROM EMP
WHERE COMM IS NULL
AND DEPTNO=30
AND SAL=1600;
285. List the manager in employee table with yearly
bonus of Rs5000 who salary lesser than Rs2500.show annual salary along with
employee table.
SELECT EMP.*,SAL*12 ANNUAL_SALARY ,SAL*12+5000
WITH_BONUS FROM EMP
WHERE SAL<2500;
286. List all the employees data whose name
having 2 L's and matching at least 1 L's in their job's whose
deportment number 10.
SELECT * FROM EMP
WHERE ENAME LIKE ‘%L%L%’
AND JOB LIKE ‘%L%’ AND DEPTNO=10;
287. List all the employees whose are all joining
from 30-DEC-81 to 30--DEC-87 and their deportment 20,30 whose Annual salary are
Rs 1000 bones of half-off the year . list their Annual salary along with
employee data .
288. List all the employees annual salary who earn
the monthly salary Rs 1000 to Rs 5000 .Their deportment number not in 20
. Also date of joining after 01-JAN-82 and before 01-JAN-88.
SELECT EMP.*,SAL*12 ANNUAL_SALARY FROM EMP
WHERE SAL BETWEEN 1000 AND 5000
AND DEPTNO!=20
AND (HIREDATE>’01-JAN-82’ AND
HIREDATE<’01-JAN-88’);
289. List all the employees where reporting manager
not having NULL Value and deportment number is either 20 or 30 whose employee
name start with S's .
SELECT * FROM EMP
WHERE MGR IS NOT NULL
AND DEPTNO IN(20,30)
AND ENAME LIKE ‘S%’;
290. LIST ALL DEPT WHICH DO NOT HAVE LETTER 'T' IN
LOCATION AS WELL AS DEPARTMENT NAME ?
SELECT * FROM DEPT
WHERE DNAME NOT LIKE ‘%T%’
AND LOC NOT LIKE ‘%T%’;
291. LIST ALL EMP HIRED BEFORE 83 EARNING SALARY IN
RANGE 500 TO 1500 AND GETTING NO COMM?
SELECT * FROM EMP
WHERE HIREDATE<’31-DEC-83’
AND SAL BETWEEN 500 AND 1500
AND COMM
IS NULL;
292. LIST ALL SALESMAN IN DEPT 30 HIRED IN THE YEAR
81 WHO GET SOME COMM?
SELECT * FROM EMP
WHERE JOB=’SALESMAN’
AND DEPTNO=30 AND HIREDATE LIKE ‘%81’
AND COMM IS NOT NULL;
293. LIST ALL EMP IN DEPT 20 AND 30 WHOSE EPMNO ENDS
WITH 8 OR 9 EARNING SAL BELOW 5000?
SELECT * FROM EMP
WHERE DEPTNO IN(20,30)
AND (EMPNO LIKE ‘%8’ OR EMPNO LIKE ‘%9’)
AND SAL<5000;
294. LIST ALL CLERKS AND SALESMAN IN DEPT 10,20,30
WHOSE NAME STARTS WITH LETTER A?
SELECT * FROM EMP
WHERE JOB IN(‘CLERK’,’SALESMAN’)
AND DEPTNO IN(10,20,30)
AND ENAME LIKE ‘A%’;
295. Display employee who work as manager, salesman
or analyst and getting sal 3000 or more.
SELECT * FROM EMP
WHERE JOB IN(‘MANAGER’,’SALESMAN’,’ANALYST’)
AND SAL>=3000;
296. Select * from emp where (job like 'MANAGER' or
job like 'SALESMAN' or job like 'ANALYST') and sal >=3000;
297. Display all Analyst whose name doesn't end with
'S'
SELECT * FROM EMP WHERE JOB LIKE 'ANALYST' AND
JOB NOT LIKE '%S';
298. Select * from emp where job like 'ANALYST' and
Job not like '%S';
299. Display all employees whose total salary is more
than 2000
SELECT * FROM EMP WHERE (SAL + COMM) > 2000;
300. select * from emp where (sal + comm) > 2000;
301. Display employees whose department between 10
,20 and salary within 1000 and 4000
SELECT * FROM EMP WHERE DEPTNO BETWEEN 10 AND 20
AND SAL BETWEEN 1000 AND 4000;
302. Select * from emp where deptno between 10 and 20
and sal between 1000
and 4000;
303. Display employees having salary of 4 digits and
start with 2 and end with 0
SELECT * FROM EMP WHERE SAL LIKE '2%0';
304. Select * from emp where sal like '2%0';
305. List all the employees who earn 3000 in
department no 20
SELECT *
FROM EMP
WHERE SAL=3000
AND DEPTNO=20;
306. List all the employees whose job does not
ends with T
SELECT * FROM EMP
WHERE JOB NOT LIKE ‘%T’;
307. List all the manager and analyst using IN
operator
SELECT * FROM EMP
WHERE JOB IN(‘MANAGER’,’ANALYST’);
308. Display all the employees who works salary
between 2500 and 5000 in department no 10 & 20
SELECT * FROM EMP
WHERE SAL BETWEEN 2500 AND 5000
AND DEPTNO IN(10,20);
309. List all employees except president and clerk
SELECT * FROM EMP WHERE JOB NOT
IN(‘PRECIDENT’,’CLERK’);
