>>>>>>>>>>>>>BASI

C SQL ASSIGNMENT
>>>>>>
1) WHAT IS SEQUEL AND EXPLAIN HISTORY OF
SEQUEL ? 2) EXPLAIN DATA WITH EXAMPLE
3) WHAT IS DATABASE AND EXPLAIN IT ?
4) WHAT DOES DBMS AND RDBMS EXPLAIN WITH DIFFERENCES ?
5) EXPLAIN THE RULES OF AN E.F CODD ?
6) WHAT IS DATATYPE AND EXPLAIN ALL THE DATATYPES WITH SYNTAX ?
7) DIFFERENCES BETWEEN CHAR AND VARCHAR ?
8) EXPLAIN CONSTRAINTS WITH SYNTAX FOR EACH CONSTRAINTS ?9) DIFFERENCES
BETWEEN PRIMARY KEY AND FOREIGN KEY 10) DIFFERENCES BETWEEN UNIQUE AND
DISTINCT ?

>>>>>>> QUERIES ON
PROJECTION
>>>>>>>>>>
11) WAQTD EMPLOYEES NAME FROM EMPLOYEES TABLE ?
Answer: select ename from employee;

12) WAQTD SAL AND EMPLOYEES NAME FROM EMPLOYEE TABLE ?

Answer: select salary ,ename from employee;
13) WAQTD NAME AND DEPTNO AND JOBS THAT ARE PRESENT IN EMP TABLE ?
Answer: select ename,deptno,job from employee;
14) WAQTD HIREDATE AND DEPTNO FOR ALL THE EMPLOYEES ?
Answer: select hiredate ,deptno from employee;
15) WAQTD MGR AND EMPNO FROM EMPLOYEE TABLE ?
Answer: select job,id from employee where job="manager"
16) WAQTD ALL THE DETAILS OF ALL THE EMPLOYEES ?
Answer: select * from employee
17) WAQTD DEPARTMENT NAME FROM DEPT TABLE ?
18) WAQTD LOC THAT ARE PRESENT IN DEPT TABLE
19) WAQTD DEPTNO AND LOC PRESENT IIN DEPT TABLE ?
20) WAQTD ALL THE DETAILS OF DEPT TABLE ?

21) WAQTD DIFFERENT JOBS PRESENT IN EMPLOYEE TABLE ?

Answer: select job from employee;
22) WAQTD ENAME AND SAL PRESENT IN EMP TABLE BUT THERE
SHOULD NOT BE ANY REPEATED RECORDS ?
Answer: SELECT DISTINCT ename,salary
FROM employee;
23) WAQTD HIREDATE AND SAL OF AN EMPLOYEES BUT THERE
SHOULD NOT BE ANY REPEATED RECORDS ?
Answer:SELECT DISTINCT hiredate,salary FROM employee;
24) WAQTD JOB SAL AND DEPTNO OF AN EMPLOYEES BUT
THERE SHOULD NOT BE ANY REPEATED RECORDS ?
Answer:select distinct job,salary,deptno from employee;

25) WAQTD ALL THE DETAILS OF AN EMPLOYEES BUT

THERE SHOULD NOT BE ANY REPEATED RECORDS ?
Answer:select distinct * from employee
26) WAQTD ANNUAL SALARY FOR ALL THE EMPLOYEES ?
Answer: SELECT *, salary * 12 AS annual_salary
FROM employee;
27) WAQTD HALF TERM SALARY FOR ALL THE EMPLOYEES ?
Answer:select *,salary*6 as halfterm
from employee
28) WAQTD ENAME AND ANNUAL SALARY FOR ALL THE EMPLOYEES ?
Answer: select ename,salary,salary*12 as annualsal
from employee
29) WAQTD DEPTNO JOB AND SAL WITH 10% HIKE FOR ALL THE EMPLOYEE ?
Answer: select deptno,job,salary,salary+salary*0.10 as hikesal
from employee
30) WAQTD ALL THE DETAILS OF AN EMPLOYEES ALONG WITH ANNUAL SALARY
?
answer: select *,salary*12 as anualsal
from employee
31) WAQTDALL THE DETAILS OF AN EMPLOYEES ALONG WITH 10% HIKE IN
ANNUAL SALARY FOR ALL THE EMPLOYEE ?
Answer: select*,(salary*12)*1.10 as hike
from employee
32) WAQTD SAL ANNAUL SALARY HALF_TERM SALARY AND QUERTER TERM
SALARY FOR ALL THE EMPLOYEES ?
Answer:select salary,salary*12 as asalary,salary*6 as hsalary,salary*3 as qsalary
from employee
33) WAQTD ANNUAL SALARY WITH 20% DEDUCTION FOR ALL THE EMPLOYEES
?
Answer: select ename,salary*12 as annual_sal,(salary*12)-(salary*12)*0.20 as
annual_de
From employee;
34) WAQTD ALL THE DETAILS ALONG WITH 100 RS PENALTY FOR ALL THE
EMPLOYEES ?
Select *,salary+100 as salpenalty
From employee
35) WAQTD TOTAL SALARY NEEDED TO PAY FOR EACH AND EVERY EMPLOYEE ?
Answer: select ename,salary from employee
36) WAQTD ANNAUL SALARY AND HALF_TERM SALARY WITH ALIAS NAME FOR
ALL THE EMPLOYEES ?
Answew: select ename,salary*12 as Annual_sal,salary*6 as half_sal from employee
37) WAQTD ALL THE DETAILS OF AN EMPLOYEES ALONG WITH 12% HIKE
IN ANNUAL SALARY WITH ALIAS NAME FOR ALL THE
EMPLOYEES
Answer: select ename,salary*12 as annual_sal,(salary*12)*0.12 as
hike_annual
from employee
38) WAQTD JOB AS DESIGNATION HIREDATE AS JOINING DATE MGR AS
REPORTING MANAGER NUMBER FOR ALL THE EMPLOYEES ?
Answer: select job as designation,hiredate as
joining_data,salary as income
from employee
39) WAQTD JOB WITH DEPTNO FOR ALL THE EMPLOYEES USING ALIAS AS
DESIGNATION FOR THE JOB AND DNO FOR THE DEPTNO ?
Answer: select *,job as designation,deptno as dno from employee
40) WAQTD ALL THE DETAILS OF AN EMPLOYEES ALONG WITH
50 RS HIKE IN SAL USE ALIAS AS BONUS FOR ALL THE EMPLOYEE ?
Answer: select *,salary+50 as hike from employee

>>>>>>>>>>> QUERIES
ON SELECTION
>>>>>>>>>>

41 ) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHOSE SALARY IS MORE THEN 1000
?
Answer: select * from employee where salary>30000;
42) WAQTD ENAME AND SALARY FOR THE EMPLOYEES WHOSE
DEPTNO IS 20 ?
Answer: select ename,salary from employee where deptno=30;
43) WAQTD JOB AND DEPTNO OF AN EMPLOYEES WHO ARE WORKING AS MANAGER ?
Answer:select job,deptno from employee where job=’manager’;
44) WAQTD ALL THE DETAILS OF ALL THE EMPLOYEES WHOSE
NAME IS KING ?
Answer:select * from employee where ename=’king’;
45) WAQTD NAME AND HIREDATE OF ALL THE EMPLOYEES WHO HIRED
ON ‘01-JAN-81 ?
Answer:select ename,hiredate from employee where hiredate=’2023-01-10’
46) WAQTD HIREDATE AND DEPTNO OF AN EMPLOYEES WHO
HIRED AFTER THE 1981 ?
Answer: select ename,hiredate from employee where hiredate>’01-jan-
1981’
47) WAQTD ALL THE DETAILS OF AN EMPLOYEES WHO HIRED
BEFORE THE 1987 ?
Answer:select * from employee where hiredate<’01-jan-1987’
48) WAQTD ALL THE DETAILS ALONG WITH ANNUAL SALARY FOR ALL THE
EMPLOYEES WHOSE SAL IS LESS THEN 5000 ?
Answer:select *,salary*12 as annual_sal from employee where salary<5000;
49) WAQTD ALL THE DETAILS OF AN EMPLOYEES WHOSE JOB IS MANAGER ?
Ansewer:select * from employee where job=’manager’

50) WAQTD ENAME AND JOB AND DEPTNO OF ALL THE EMPLOYEES WHO ARE WORKING
IN DEPTNO 30 ?
Answer:select ename,job,deptno from employee where deptno=30;
51) WAQTD ALL THE DETAILS OF AN EMPLOYEES WHOSE JOB IS NOT
SALESMAN ?
Answer:select * from employee where job<>salesman;
52) WAQTD ENAME AND JOB OF ALL THE EMPLOYEES EXCEPT THE
EMPLOYEES OF DEPTNO 20 ?
Answer:select * from employee where deptno!=20;
53) WAQTD JOB EPTNO AND SAL OF AN ALL THE EMPLOYEES EXCEPT
THE EMPLOYEES WHO ARE WORKING AS SALESMAN ?
Answer:select ename,id,salary from employee where job=’salesman’
54) WAQTD DESIGNATION OF ALL THE EMPLOYEES EXCEPT KING ?
Answer:select job from employee where ename<>’king’;
55) WAQTD ALL THE DETAILS OF THE EMPLOYEES ALONG WITH THAT
20% HIKE OF IN THEIR SALARY FOR THE EMPLOYEES WHO HIRED
AFTER 85 ?
Answer: select *,salary+(salary*0.20) from employee where hiredate>’01-jan-
1985’
56) WAQTD ALL THE DETAILS OF THE EMPLOYEES ALONG WITH THAT
12% DEDUCTION OF IN THEIR SALARY FOR THE EMPLOYEES WHO
HIRED BEFORE 84 ?
Answer:select *,salary-(salary*0.12) as decsal from employee where
hiredate<’01-jan-1984’
57) WAQTD ALL THE DETAILS OF THE EMPLOYEES ALONG WITH THAT
2000 BONUS OF IN THEIR SALARY FOR THE EMPLOYEES
WHOSE EMPNO IS 7369 ?
Answer:select *,salary+2000 as salb from employee where
empno=7369;
58) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHO ARE NOT
WORKING
IN DEPTNO 10 ?
Answer:select * from employee where deptno<>10;
59) WAQTD ALL THE DETAILS OF THE EMPLOYEES ALONG WITH
HALFTERM SALARY FOR ALL THE EMPLOYEE WHOSE ANNUAL
SALARY IS MORE THEN 14000 ?
Answer:select *,salary*6 as halfsal from employee where salary*12>14000;
60) WAQTD ALL THE DETAILS OF THE EMPLOYEES ALONG WITH THAT
20% HIKE OF IN THEIR HALF TERM SALARY FOR ALL THE
EMPLOYEES WHOSE HALF TERM SALARY IS MORE THAN 7000 ?
Answer:select *,salary+(salary*0.20) as hikesal from employee where
salary*6>7000;

>>>>>>>>>>>
ASSIGNMENT ON
LOGICAL OPERATOR
>>>>>>>>>>
61) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHO ARE WORKING IN DEPTNO
10 AND 20?
Answer:select * from employee where deptno=10 or deptno=20;
62) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHO ARE WORKING AS
MANAGER AND SALESMAN ?
Answer:select * from employee where job=’manager’ or job=’salesman’;

63) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHOSE SAL IS MORE THEN
1000 AND LESS THEN 5000 ?
Answer:select * from employee where salary<1000 or salary>5000;
64) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHOSE DEPTNO IS 20 AND
WORKING AS MANAGER ?
Answer:select * from employee where deptno=20 and job’manager’
65) WAQTD ALL THE EMPLOYEE NAME AND SALARY WHO HIRED AFTER 81 INTO
DEPTNO 10 ?
Answer:select ename,salary from employee where hiredate>31-dec-9181 and deptno=10
66) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHOSE NAME IS KING AND
SCOTT ?
Answer:select * from employee where ename=’king’ or ename=’scott’
67) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHOSE SAL IS 3000 AND 5000 ?
 Answer:select * from employee where salary=3000 or salary=5000;
68) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHO ARE WORKING IN DEPTNO
10 AS MANAGER AND HIRED ON ‘17-DEC-82 ?
Answer:select * from employee where deptno=10 and job=’manager’and hiredate=’17-dec-82’
69) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHO ARE WORKING AS
PRESIDENT IN DEPTNO 10 AND HAVING SALARY OF 5000 ?
Answer:select * from employee where job=’president’ and deptno=10 and salary=5000;
70) WAQTD ALL THE DETAILS OF THE EMPLOYEES WORKING IN DEPTNO 10,20 AS
MANAGER ?
Answer: select * from employee where( deptno=10 or deptno=20 )and job =’manager’
71) WAQTD ALL THE EMPLOYEES NAME AND JOB FOR THE EMPLOYEES WHO
HIRED AFTER 81 AND BEFORE 87 ?
Answer:select ename,job from employee where hiredate>01-jan-1981 and hiredate<01-jan-9187
72) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHOSE SALARY ARE MORE THEN
1000 AND LESS THEN 5000 IN DEPTNO 20 ?
Answer:select * from employee where salary>1000 and salary<5000 and deptno=20
73) WAQTD ALL THE DETAILS OF THE OF THE EMPLOYEES WHO ARE WORKING AS
MANAGER IN DEPTNO 20 AND 10AND EARNING COMMISIONS 400,1600 ?
Answer:select * from employee where job=’manager’ and deptno=20 or deptno=10 and
comm=400 or comm=1600;
74) WAQTD ENAME AND ANNAUL SALARY WITH ALISAS NAME FOR THE EMPLOYEES
WHOSE ANNUAL SALARY IS MORE THEN 800 IN DEPTNO 30 WORKING AS
SALESMAN AND HIRED BEFORE THE 88 ?
Answer:select ename,salary*12 as annual_salary from employee salary*12>800 and deptno=30 and
job=’salesman’and hiredate=’01-jan-1988’
75) WAQTD ENAME AS A DEPTNO AS B AND JOB AS C FOR THE EMPLOYEES WHOSE
COMM IS MORETHE 100 IN DEPTNO 10,20,30 ?
Answer:select ename as A,deptno as B,job as C from employee where comm>100 and
(deptno=10 or deptno=20 or deptno=30)
76) WAQTD ALL THE DETAILS FOR THE EMPLOYEEES WHOSE HIREDATE ARE ‘03-
DEC-81’ and ‘17DEC81’ INTO DEPTNO 30 ?
Answer:select * from employee where hiredate=’03-dec-81’or hiredate=’17-dec-81’ and
deptno=30

77) WAQTD JOB AND SALARY FOR THE EMPLOYEES WHOSE COMM IS LESS THEN 2000
ANDSALARIES ARE MORE THEN 1000 IN DEPTNO 10, 20?
Answer:select job,salary from employee where comm<2000 and salary>1000 and (deptno=10
or deptno=20)
78) WAQTD ALL THE DEATILS FOR THE EMPLOYEES WHOSE NAMES ARE KING AND
SMITH ANDALLEN ?
Answer:select * from employee where ename=’king’or ename=’smith’or ename=’allen’
79) WAQTD ANNUAL SALARY HALF TERM SALARY WITH 25% DEDUCTION IN THE
SALARY FOR THEEMPLOYEES WHOSE ANNUAL SALARY IS MORE THEN 6000 ?
Answer:select (salary*12)-salary*0.25 as annual_salary,(salary*6)-salary*025 as halfsal from
employee where salary*12>6000
80) ALL THE DETAILS AND HALF TERM SALARY FOR SALESMAN AND MANAGER IN
DEPTNO 10,30 ?
Answer:select *,salary*6 as hts from employee where job=’salesman’or job=’manager’ and
deptno=10 or deptno=30

>>>>>>>>>>>
ASSIGNMENT ON
SPECIAL OPERATOR
>>>>>>>>>>>
81) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHO ARE WORKING IN
DEPTNO
10 AND 20?
Answer:select * from employee where deptno in (10,20)
82) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHO ARE WORKING AS
MANAGER AND SALESMAN ?
Answer:select * from employee job in(‘manager’,salesman)
83) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHOSE SAL IS MORE
THEN 1000 AND LESS THEN 5000 ?
Answer:select * from employee where salary between 100 and 5000
84) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHOSE DEPTNO IS 20
AND WORKING AS MANAGER ?
Answer:select * from employee deptno=20 and job=’manager’
85) WAQTD ALL THE EMPLOYEE NAME AND SALARY WHO HIRED AFTER 81
INTO DEPTNO 10 ?
Answer:select ename,salary from employee where hiredate=’31-dec-1981’ and
deptno=10
86) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHOSE NAME IS KING
AND SCOTT ?
Answer:select * from employee where ename in(‘king’,’scott’)
87) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHOSE SAL IS 3000 AND 5000 ?
Answer:select * from employee where salary in(3000,5000)
88) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHO ARE WORKING IN
DEPTNO 10 AS MANAGER AND HIRED ON ‘17-DEC-82 ?
Answer:select * from employee deptno=10 and job=’manager’ and hiredate=’17-dec-
82’
89) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHO ARE WORKING AS
PRESIDENT IN DEPTNO 10 AND HAVING SALARY OF 5000 ?
Answer:select * from employee where job=’president’ and deptno=10 and salary=5000
90) WAQTD ALL THE DETAILS OF THE EMPLOYEES WORKING IN DEPTNO
10,20 AS MANAGER ?
Answer:select * from employee deptno in(10,20) and job=’manager’
91) WAQTD ALL THE EMPLOYEES NAME AND JOB FRO THE EMPLOYEES
WHO HIRED AFTER 81 AND BEFORE 87 ?
Answer:select ename,job from employee where hiredate >’31-dec-81’ and hiredate<’01-
jan-87’
92) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHOSE SALARY ARE MORE
THEN 1000 ANDLESS THEN 5000 IN DEPTNO 20 ?
Answer:select * from employee where salary between 1000 and 5000 and deptno=20
93) WAQTD ALL THE DETAILS OF THE OF THE EMPLOYEES WHO ARE
WORKING AS MANAGER IN DEPTNO 20 AND 10AND EARNING
COMMISIONS 400,1600 ?
Answer:select * from employee job=’manager’ and deptno in(10,20) and comm
in(400,1600)
94) WAQTD ENAME AND ANNAUL SALARY WITH ALISAS NAME FOR THE
EMPLOYEES WHOSE ANNUAL SALARY IS MORE THEN 800 IN DEPTNO 30
WORKING AS SALESMAN AND HIRED BEFORE THE 88 ?
Answer:select ename,salary*12 as annual_sal from employee where salary*12>800 and deptno=30 and
job=’salesman’and hiredate<’01-jan-88’
95) WAQTD ENAME AS A DEPTNO AS B AND JOB AS C FOR THE EMPLOYEES WHOSE COMM
IS MORETHE 100 IN DEPTNO 10,20,30 ?
Answer:select ename as A,deptno as B,job as c from employee where comm >100 and deptno in(10,20,30)
96) WAQTD ALL THE DETAILS FOR THE EMPLOYEEES WHOSE HIREDATE ARE ‘03-DEC-81’
AND ‘17DEC81’ INTO DEPTNO 30 ?
Answer:select * from employee where hiredate in(’03-dec-81’,’17-dec-81’) and deptno=30
97) WAQTD JOB AND SALARY FOR THE EMPLOYEES WHOSE COMM IS LESS THEN 2000
ANDSALARIES ARE MORE THEN 1000 IN DEPTNO 10, 20?
Answer:select job,salary from employee where comm < 2000 and salary>1000 and deptnno in (10,20)
98) WAQTD ENAME FOR THE EMPLOYEES WHOSE NAMES ARE KING AND SMITH AND
ALLEN ?
Answer:select ename from employee where ename in (‘king’,’smith’,’allen’)
99) WAQTD ANNUAL SALARY HALF TERM SALARY WITH 25% DEDUCTION IN THE SALARY
FOR THE EMPLOYEES WHOSE ANNUAL SALARY IS MORE THEN 6000 ?
Answer:select salary*12 as annualsal,salary*6 as halfsala,salary*12-(salary*12*0.25)as decsala from
employee where salary*12>6000;
100) ALL THE DETAILS AND HALF TERM SALARY FOR SALESMAN AND MANAGER IN DEPTNO
10,30 ?
Answer:select * ,salary*6 as halfsal from employee where job in (‘salesman’,’manager’) and deptno
in(10,30)
101) ) WAQTD ALL THE DETAILS OF THE EMPLOYEES WHOSE HIRED BETWEEN 81 TO 89 INTO
DEPTNO 20 ?
Answer:select * from employee where hiredate between ‘01-jan-81’ and ‘31-dec-89’ and deptno=20
102) WAQTD DETAILS FOR THE EMPLOYEES WHOSE SALARY IN THE RANGE OF 1000 TO 5000
INDEPTNO 10,20 ?
Answer:select * from employee where salary between 1000 and 5000 and deptno in (10,20)
103) WAQTD ENAME AND JOB FOR THE EMPLOYEES WHO ARE WORKING AS ANAGER AND
EARNINGCOMMISION IN THE RANGE OF 100 TO 1000 ?
Answer:select ename,job from employee job=’manager’ and comm between 100 and 1000
104) WAQTD ALL THE DEATILS ALONG WITH 3 MONTHS SALARY FOR THE EMPLOYEES
WHOSESALARY IS NOT NULL AND DOESNT HAVING ANY REPORTING MANAGER ?
Answer:select *,salary*3 as monthsal from employee where salary is not null and reportingmanager is
null
105) WAQTD ENAME AND SALARY AND JIB FOR THE EMPLOYEES WORKING AS SALESMAN
INDEPTNO 30 AND EARNING SALARY BUT NOT COMMISION AND HIRED BETWEEN 82 TO
88 ?
Answer:select ename,salary,job from employee job=’salesman’ and deptno=30 and salary is not null and
comm is null and hiredate between 01-jan-82 and 31-dec-88
106) WAQTD ALL THE DETAILS OF THE EMPLOYEE WHO ARE EARNING SALARY ?
Anwer:select * from employee where salary is not null;
107) WAQTD ANNAUL SALARY OF THE EMPLOYEE WHO ARE NOT EARNING COMMISSION ?
Answer:select salary*12 as annualsal from employee where comm is null
108) WAQTD ALL THE DETAILS WITH ANNAUL COMMISSION FOR THE EMPLOYEE WHO ARE
WORKINGIN DEPT 10 AND DONT HAVE ANY REPORTING MANAGER ?
Answer:select *,comm*12 as Acomm from employee where deptno=10 and mga is null
 109) WAQTD ALL THE DETAILS OF THE EMPLOYEE EXCEPT DEPTNO 10 AND WHO WHO
HAVEREPORTING MAMAGER ?
Answer:select * from employee where deptno<>10 and mgr is not null
110) WAQTD ENAME AND SALARY. AND COMMISSION OF THE EMPLOYEE WHO EARNING
COMMISSIONIN DEPT NO 30 AND WORKING AS SALESMAN ?
Answer:select ename ,salary ,comm from employee where comm is not null and deptno=30 and
job=’salesman’
111) WAQTD ENAME OF THE EMPLOYEE WHOSE NAME START WITH S ?
Answer:select ename from employee where ename like’s%’
112 ) WAQTD ALL THE DETAILS FOR THE EMPLOYEE WHOSE NAME ENDS WITH R ?
Answer:select * from employee where ename like ‘%r’
113 ) WAQTD SALARY AND DEEPTNO AND NAME FOR THE
EMPLOYEE WHOSE NAME HAVING A CHARACTER ?
Answer:select salary,deptno,ename from employee where ename
like’%a%’
114 ) WAQTD ALL THE DETAILS OF THE EMPLOYEE WHOSE HAVING
2 A IN THEIR NAME ?
Answer:select * from employee where ename like ‘%aa%’
115 ) WAQTD ALL THE DETAILS OF THE EMPLOYEE WHOSE NAME
HAVING
CONJUGATIVE A IN THEIR NAMES ?
Answer:select * from employee where ename like’%aa%’
116) WAQTD ALL THE DETAILS OF THE EMPLOYEE WHOSE NAME
HAVING2ND CHARACTER AS M ?
Answer:select * from employee where ename like ‘_m%’
117) WAQTD ALL THE DETAILS OF THE EMPLOYEE WHOSE NAME HAVING
LAST 2ND CHAPTER AS E ?
Answer:select * from employee where ename like ‘%e_’
118) WAQTD ALL THE DETAILS OF THE EMPLOYEE WHOSE NAME START
WITH M HAVING 4TH CHARACTER L AND ENDS WITH R ?
Answer:select * from employee ename like’m__L%R’
119) WAQTD ALL THE DETAILS OF AN ALL THE EMPLOYEE WHO ARE EARNING 4 DOGIT OF
SALARY AND NAMES STARTS WITH VOWELS ?
Answer:select * from employee salary like ‘____’and ename
regexp’^[AEIOU]’
120 ) WAQTD ALL THE DETAILS OF THE EMPLOYEE WHOSE
NAME STARTS WITH CONSONANTS. ?
Answer:select * from employee where ename not regexp ^[AEIOU]
121) WAQTD ENAME AND HIREDATE OF ALL THE
EMPLOYEE WHOHIRED IN THE MONTH OF DEC ?
Answer:select ename ,hiredate from employee hiredate like’%-dec-
%’
122) WAQTD ALL THE DETAILS FOR THE EMPLOYEE WHO
HIREDDURING 1981 ?
Answer:select * from employee where hiredate like’__-__-1981’ OR
select * from employee where hiredate between ’01-jan-9181’ and
‘31-dec-9181’
123) WAQTD ALL THE DETAILS OF THE EMPLOYEE
EXCEPTWHOSE NAME HAVING A ?
Answer:select * from employee ename not like’%a%’
124) WAQTD SALARY AND NAMES FOR THE EMPLOYEE
WHOSENAME START WITH S EXCEPT THE EMPLOYEE WHOSE
SALARY. STARTS WITH 1 ?
Answer:select salary,ename from employee ename like’s%’ and
salary not like’1%’
125) WAQTD ALL THE DETAILS OF THE EMPLOYEE
WHOSENAME HAVING 2 TIME CONSUGATIVE. A ?
Answer:select * from employee where ename like’%aa%’
126) WAQTD ALL THE DETAILS OF THE EMPLOYEE WHOSE
NAME STARTWITH % ?
Answer:select * from employee where ename like ‘/%%’ escape ‘/’
127) WAQTD ALL THE DETAILS OF THE EMPLOYEE WHOSE
NAME ENDSWITH % ?
Answer:select * from employee where ename like ‘%/%’ escape ‘/’
128) WAQTD ALL THE DETAILS OF THE EMPLOYEE WHOSE
NAME STARTWITH % ENDS WITH _ ?
Answer:select * from employee where ename like ‘/%%/__’ escape
‘/’
129) WAQTD ALL THE DETAILS OF THE EMPLOYEE WHOSE
NAME HAVING % ?
Answer:select * from employee where like ‘%%/%’ escape’/’
130) WAQTD ALL THE DETAILS OF THE EMPLOYEE WHOSE
NAME START WITH % HAVING 4TH CHARACTER _ AND ENDS
WITH % ?
Answer:select * from employee ename like ‘/%%__/_%/%’
w\escape ‘/’
>>>>>>>>>>>>>>ASSINGMENT ON MRF >>>>>>>>>>>>>>>>>>
131) WAQTD MAX SALARY FROM THE EMPLOYEE TABLE ?
Answer:select max(salary) from employee
132) WAQTD MAX AND MIN COMM FROM EMPLOYEES TABLE ?
Answer:select max(comm),min(comm) from employee
133) WAQTD TOTAL SALARY NEEDED TO PAY FOR THE SALESMAN ?
Answer:select sum(salary) from employee
134) WAQTD NUMBER OF EMPLOYEES WORKING IN DEPTNO 30 ?
Answer:select count(id) from employee where deptno=30
135) WAQTD NUMBER OF EMPLOYEES WORKING AS MANAGER IN DEPTNO 20?
Answer:select count(id) from employee where job=’manager’ and deptno=20
136) WAQTD MAX SALARY NEEDED TO PAY FOR THE EMPLOYEES WHOSE NAME STARTS WITH
A?
Answer:select max(salary) from employee where ename like ‘a%’
137) WAQTD AVG SALARY NEEDED TO PAY FOR THE EMPLOYEES OF DEPTNO 10 ?
Answer:select avg(salary) from employee where deptno=10
138) WAQTD TOTAL SALARY AND AVG SALARY NEEDED TO PAY FOR THE EMPLOYEES WHO
AREHIRED IN THE RANGE OF 81 TO 87 ?
Answer:select sum(salary),avg(salary) from employee hiredate between ’01-jan-81’ and ’31-dec-87’
139) WAQTD NUMBER OF EMPLOYEES WORKING IN DEPTNO 40 ?
Answer:select count(id) from employee where deptno=40
140) WAQTD TOTAL SALARY NEEDED TO PAY FOR THE EMPLOYEES WHO ARE REPORTING TO
7839 ?
Answer: select sum(salary) from employee where mgr=7839
141) 141) WAQTD HOW MANY EMPLOYEES ARE WORKIN AS PRESIDENT IN DEPTNO 10 AND
EARNING SALARY MORE THEN 4000 ?
Answer:select count(id) from employee job=’president’ and deptno=10 and salary>4000
142) WAQTD HOW MANY EMPLOYEES ARE WORKIN AS SALESMAN IN DEPTNO 30
AND EARNINGSALARY LESS THEN 5000 ?
Answer:select count(id) from employee where job=’salesman’ and deptno=30 and salary<5000
143) WAQTD HOW MANAY EMPLOYEES ARE HIRED ON 03-DEC- 81 iN DEPTNO 10 ,
20
Answer:select count(id) from employee where hiredate=’03-dec-81’ and deptno in(10,20)
144) 144) WAQTD HOW MUCH TOTAL SALARY NEEDE TO PAY FOR THE EMPLOYEE
WHOSE MAME HAVING A CHARACTER ?
Answer:select sum(salary) from employee where ename like ‘%a%’
145) WAQTD AVG SALARY NEEDED TO PAY FOR THE SALESMAN IN DEPTNO 30 ?
Answer:select avg(salary) from employee job=’salesman’ and deptno=30
>>>>>>>>> ASSIGNMENT ON GROUP BY CLAUSE >>>>>>>>>>>>>
146).WAQTD NUMBER OF EMPLOYEES WORKING IN EACH DEPARTEMENT EXCEPT PRESIDENT.
?
Answer:select count(id) as countepm ,deptno from employee where job<>president group by deptno
147)WAQTD TOTAL SALARY NEEDED TO PAY ALL THE EMPLOYEES IN EACH JOB. ?
Answer:select sum(salary) from employee group by job
148)WAQTD NUMBER OF EMPLOYEEES WORKING AS MANAGER IN EACH DEPARTMENT. ?
Answer:select count(id) from employee where job=’manager’ group by deptno
149).WAQTD AVG SALARY NEEDED TO PAY ALL THE EMPLOYEES IN EACH DEPARTMENT
EXCLUDING THE EMPLOYEES OF DEPTNO 20 . ?
Answer:select avg(salary),deptno from employee where deptno<>20 group by deptno
150).WAQTD NUMBER OF EMPLOYEES HAVING CHARACTER 'A' IN THEIR NAMES IN EACH JOB.
?
Answer:select count(id),job from employee where ename like ‘%a%’ group by job
151).WAQTD NUMBER OF EMPLOYEES AND AVG SALARY NEEDED TO PAY THE EMPLOYEES
WHO SALARY IN GREATER THAN 2000 IN EACH DEPT. ?
Answer:select count(id),avg(salary),deptno from employee salary>2000 group by deptno
152)WAQT D TOTAL SALARY NEEDED TO PAY AND NUMBER OF SALESMANS IN EACH DEPT. ?
Answer:select sum(salary) ,dept as total_sal from employee where job=’salesman’ group by dept
153)WAQTD NUMBER OF EMPLOYEES WITH THEIR MAXIMUM SALARIES IN
EACH JOB. ?
Answer:select count(id),max(salary) from employee group by job
154) WAQTD MAXIMUM SALARIES GIVEN TO AN EMPLOYEE WORKING IN
EACH DEPT. ?
Answer:select max(salary) ,dept from employee group by deptno
155)WAQTD NUMBER OF TIMES THE SALARIES PRESENT IN EMPLOYEE TABLE. ?
Answer:select salary,count(*) as count_salary from
employee group by salary