SQL Command



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

c7



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

c5

c3

c1

c2

c4

c6



select cid from coursee where semester= 'summer'

and year=2024 and cid IN

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



CID

c7

c13

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);

0 Comments

Data Visualization Graphs