Archive for May 2024

create table emp01(ename varchar2(20), sal varchar2(20),eno varchar2(20));

insert into emp01 values('SB',1000,'e1');

insert into emp01 values('SA',2000,'e2');

insert into emp01 values('SC',3000,'e3');

insert into emp01 values('SD',4000,'e4');

insert into emp01 values('SE',5000,'e5');

SELECT * FROM EMP01;

SELECT ENAME

FROM EMP01

WHERE SAL>

(SELECT SAL FROM EMP01 WHERE ENO='e4');

create table emp02(ename varchar2(20), sal varchar2(20),dno varchar2(20),deg varchar2(20));

insert into emp02 values('SB',1000,'d1','IT');

insert into emp02 values('SA',2000,'d2','HR');

insert into emp02 values('SC',3000,'d3','Fin');

insert into emp02 values('SD',4000,'d4','Mark');

insert into emp02 values('SE',5000,'d5','Opp');

insert into emp02 values('SF',6000,'d6','Mark');

SELECT * FROM EMP02;

SELECT ename,sal,dno,deg

FROM emp02

WHERE deg=

(SELECT deg FROM emp02 WHERE dno='d4');

create table emp02(ename varchar2(20), sal varchar2(20),dno varchar2(20),deg varchar2(20));

insert into emp02 values('SB',1000,'d1','IT');

insert into emp02 values('SA',2000,'d2','HR');

insert into emp02 values('SC',3000,'d3','Fin');

insert into emp02 values('SD',4000,'d4','Mark');

insert into emp02 values('SE',5000,'d5','Opp');

insert into emp02 values('SF',6000,'d6','Mark');

insert into emp02 values('Sg',7000,'d6','Mark');

insert into emp02 values('Sh',8000,'d2','Mark');

insert into emp02 values('Si',9000,'d4','Mark');

SELECT * FROM EMP02;

SELECT ename,sal,dno

FROM emp02

WHERE sal IN

(SELECT MIN(sal) FROM emp02 GROUP BY dno);

SELECT ename,sal,dno

FROM emp02

WHERE sal IN

(SELECT MAX(sal) FROM emp02 GROUP BY dno);

create table emp03(ename varchar2(20), sal varchar2(20),eno varchar2(20),deg varchar2(20));

insert into emp03 values('SB',1000,'e1','IT');

insert into emp03 values('SA',2000,'e2','HR');

insert into emp03 values('SC',3000,'e3','Fin');

insert into emp03 values('SD',4000,'e4','Mark');

insert into emp03 values('SE',5000,'e5','Opp');

insert into emp03 values('SF',6000,'e6','Mark');

insert into emp03 values('Sg',7000,'e6','Mark');

insert into emp03 values('Sh',8000,'e2','Mark');

insert into emp03 values('Si',9000,'e4','Mark');

SELECT * FROM EMP03;

SELECT ename,deg,eno,sal

FROM emp03

WHERE sal < ANY

(SELECT sal FROM emp03 where deg='Opp');

*** Not Equal comparison operator (<>) to compare two expressions

create table emp04(ename varchar2(20), sal varchar2(20),dno varchar2(20),deg varchar2(20),doj varchar2(20));

insert into emp04 values('SB',1000,'d1','IT','11may');

insert into emp04 values('SA',2000,'d2','HR','10may');

insert into emp04 values('SC',3000,'d3','Fin','12may');

insert into emp04 values('SD',4000,'d4','Mark','13may');

insert into emp04 values('SE',5000,'d5','Opp','14may');

insert into emp04 values('SF',6000,'d6','Mark','15may');

insert into emp04 values('Sg',7000,'d6','Mark','11may');

insert into emp04 values('Sh',8000,'d2','Mark','12may');

insert into emp04 values('Si',9000,'d4','Mark','10may');

insert into emp04 values('SJ',10000,'d4','Mark','10may');

insert into emp04 values('SK',11000,'d4','Mark','10may');

SELECT * FROM EMP04;

SELECT ename,doj

FROM emp04

WHERE dno=

(SELECT dno FROM emp04 where ename='SK')

and ename<> 'SK';

*****************************************************************

create table emp04(ename varchar2(20), sal varchar2(20),dno varchar2(20),deg varchar2(20),doj varchar2(20));

insert into emp04 values('SB',1000,'d1','IT','11may');

insert into emp04 values('SA',2000,'d2','HR','10may');

insert into emp04 values('SC',3000,'d3','Fin','12may');

insert into emp04 values('SD',4000,'d4','Mark','13may');

insert into emp04 values('SE',5000,'d5','Opp','14may');

insert into emp04 values('SF',6000,'d6','Mark','15may');

insert into emp04 values('Sg',7000,'d6','Mark','11may');

insert into emp04 values('Sh',8000,'d2','Mark','12may');

insert into emp04 values('Si',9000,'d4','Mark','10may');

insert into emp04 values('SJ',10000,'d4','Mark','10may');

insert into emp04 values('SK',11000,'d4','Mark','10may');

SELECT * FROM EMP04;

SELECT ename,doj

FROM emp04

WHERE dno=

(SELECT dno FROM emp04 where ename='SK')

and ename<> 'SK';

SELECT dno,ename

from emp04

where sal >

(select avg(sal) from emp04

) and (select * from emp04

ORDER BY sal DESC

);

select dno from emp04

ORDER BY sal DESC;

SELECT dno,ename

from emp04

ORDER BY sal DESC

(select avg(sal) from emp04);

create table emp05(ename varchar2(20), eno varchar2(20),dno varchar2(20));

insert into emp05 values('SBTM',10,'d1');

insert into emp05 values('SARM',20,'d2');

insert into emp05 values('SCTK',30,'d3');

insert into emp05 values('SDPT',40,'d4');

insert into emp05 values('SENL',50,'d5');

SELECT * FROM EMP05;

SELECT eno,ename

FROM emp05

WHERE dno IN

(SELECT dno from emp05 WHERE ename LIKE '%T%');

ENAME	ENO	DNO
SBTM	10	d1
SARM	20	d2
SCTK	30	d3
SDPT	40	d4
SENL	50	d5

ENO	ENAME
10	SBTM
30	SCTK
40	SDPT

create table emp06(ename varchar2(20), eno varchar2(20),dno varchar2(20),desig varchar2(20));

insert into emp06 values('SBTM',10,'d1','Hr');

insert into emp06 values('SARM',20,'d2','mkt');

insert into emp06 values('SCTK',30,'d3','Fi');

insert into emp06 values('SDPT',40,'d4','IT');

insert into emp06 values('SENL',50,'d5','HR');

SELECT * FROM EMP06;

create table dept1(loc varchar2(20),dno varchar2(20));

insert into dept1 values('SB','d1');

insert into dept1 values('SA','d2');

insert into dept1 values('SC','d3');

insert into dept1 values('SD','d4');

insert into dept1 values('SE','d5');

insert into dept1 values('SF','d6');

select ename, eno, desig

from emp06

where dno=

(select dno

from dept1

where loc='SC');

create table emp06(ename varchar2(20), eno varchar2(20),dno varchar2(20),desig varchar2(20));

insert into emp06 values('SBTM',10,'d1','Hr');

insert into emp06 values('SARM',20,'d2','mkt');

insert into emp06 values('SCTK',30,'d3','Fi');

insert into emp06 values('SDPT',40,'d4','IT');

insert into emp06 values('SENL',50,'d5','HR');

insert into emp06 values('SEN',60,'d6','HRM');

insert into emp06 values('SNL',70,'d7','CEM');

SELECT * FROM EMP06;

create table dept1(loc varchar2(20),dno varchar2(20));

insert into dept1 values('SB','d1');

insert into dept1 values('SA','d2');

insert into dept1 values('SC','d3');

insert into dept1 values('SD','d4');

insert into dept1 values('SD','d6');

insert into dept1 values('SE','d5');

insert into dept1 values('SD','d7');

select ename, eno, desig

from emp06 e, dept1 d

where e.dno=d.dno AND d.loc= 'SD';

create table emp07(ename varchar2(20), eno varchar2(20),dno varchar2(20),desig varchar2(20),sal varchar2(20));

insert into emp07 values('SBTM',10,'d1','Hr',1000);

insert into emp07 values('SARM',20,'d2','mkt',500);

insert into emp07 values('SCTK',30,'d3','Fi',900);

insert into emp07 values('SDPT',40,'d4','IT',9000);

insert into emp07 values('SENL',50,'d5','HR',5000);

insert into emp07 values('SEN',60,'d6','HRM',1000);

insert into emp07 values('SNL',70,'d7','CEM',2000);

select ename,dno,sal

from emp07

where exists

(select sal from emp where sal>1000);

create table coursee(cid varchar2(20), semester varchar2(20),year varchar2(20),cname varchar2(20));

insert into coursee values('c1','summer',2024,'Hr');

insert into coursee values('c2','summer',2024,'IT');

insert into coursee values('c3','summer',2024,'MKT');

insert into coursee values('c4','summer',2024,'FI');

insert into coursee values('c5','summer',2024,'OP');

insert into coursee values('c6','summer',2024,'MIS');

insert into coursee values('c7','Winter',2024,'Hr');

insert into coursee values('c7','summer',2024,'HrM');

insert into coursee values('c8','Winter',2024,'IT');

insert into coursee values('c9','Winter',2024,'MKT');

insert into coursee values('c10','Winter',2024,'FI');

insert into coursee values('c11','Winter',2024,'OP');

insert into coursee values('c12','Winter',2024,'MIS');

insert into coursee values('c13','Winter',2024,'MIS');

insert into coursee values('c13','summer',2024,'travel');

insert into coursee values('c13','summer',2024,'DM');

select * from coursee;

select distinct cid from coursee where semester='summer'

and year=2024 and cid in

(select cid from coursee where semester='Winter' and Year=2024);

CID

c13

select distinct cid from coursee where semester='summer'

and year=2024 and cid not in

(select cid from coursee where semester='Winter' and Year=2024);

CID

select cid from coursee where semester= 'summer'

and year=2024 and cid IN

(select cid from coursee where semester='Winter' and Year=2024);

CID

c13

create table course1(cid varchar2(20),dname varchar2(20),cname varchar2(20));

insert into course1 values('c1','Business Admin','Hr');

insert into course1 values('c2','Business Admin','IT');

insert into course1 values('c3','Business Admin','M');

insert into course1 values('c4','Business Admin','F');

create table student1(RNO varchar2(20),Name varchar2(20));

insert into student1 values(01,'Avi');

insert into student1 values(02,'Anirban');

insert into student1 values(03,'Jyoti');

insert into student1 values(04,'Neha');

insert into student1 values(05,'Pritam');

create table takes(cid varchar2(20),RNO varchar2(20));

insert into takes values('c1',05);

insert into takes values('c2',01);

insert into takes values('c2',03);

insert into takes values('c3',04);

insert into takes values('c4',02);

select * from takes;

select distinct S.RNO, S.name

from student1 S

where S.RNO NOT IN (

select T.cid from takes T

where T.RNO = S.RNO

and exists (select cid from course1 where dname='Business Admin' and cid = T.cid)

);

Operators	input	output
+	SELECT 30 + 20;	50
-	SELECT 30 - 20;	10
*	SELECT 30 * 20;	600
/	SELECT 30 / 10;	3
%	SELECT 17 % 5;	2
=	SELECT * FROM Products WHERE Price = 18;
	SELECT * FROM Products WHERE Price > 30;
	SELECT * FROM Products WHERE Price < 30;
	SELECT * FROM Products WHERE Price >= 30;
	SELECT * FROM Products WHERE Price <= 30;
	SELECT * FROM Products WHERE Price <> 18;
	SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
	SELECT * FROM Customers WHERE City = "London" AND Country = "UK";
	SELECT * FROM Products WHERE Price > ANY (SELECT Price FROM Products WHERE Price > 50); SELECT * FROM Products WHERE Price BETWEEN 50 AND 60;
	SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);
	SELECT * FROM Customers WHERE City IN ('Paris','London');
	SELECT * FROM Customers WHERE City LIKE 's%';
	SELECT * FROM Customers WHERE City NOT LIKE 's%';
	SELECT * FROM Customers WHERE City = "London" OR Country = "UK";
	SELECT * FROM Products WHERE Price > SOME (SELECT Price FROM Products WHERE Price > 20);