CREATE TABLE EMP(
empno number(4) primary key,
ename varchar(10),
job varchar(10),
hiredate date,
sal number(4,0));
desc EMP;
INSERT INTO EMP VALUES(7369,'SMITH','CLERK','17-DEC-80',800);
INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN','17-FEB-81',1600);
INSERT INTO EMP VALUES(7521,'WARD','SALESMAN','20-FEB-81',1250);
INSERT INTO EMP VALUES(7566,'JONES','MANAGER','22-APR-81',2975);
INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN','02-APR-81',1250);
INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER','28-SEP-81',2850);
INSERT INTO EMP VALUES(7782,'CLARK','MANAGER','01-MAY-81',2450);
INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST','19-JUN-81',3000);
INSERT INTO EMP VALUES(7839,'KING','PRESIDENT','17-APR-87',5000);
INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN','08-NOV-81',1500);
INSERT INTO EMP VALUES(7876,'ADAMS','CLERK','23-SEP-87',1100);
INSERT INTO EMP VALUES(7900,'JAMES','CLERK','03-MAY-81',950);
INSERT INTO EMP VALUES(7902,'FORD','ANALYST','03-DEC-81',3000);
INSERT INTO EMP VALUES(7934,'MILLER','CLERK','23-DEC-82',1300);
SELECT * FROM EMP
create table DEPT(
DEPTNO NUMBER(2) primary key,
DNAME VARCHAR(10),
LOC VARCHAR(10));
desc DEPT;
insert into DEPT values(10,'ACCOUNTING','NEW YORK');
insert into DEPT values(20,'RESEARCH','DALLAS');
insert into DEPT values(30,'SALES','CHICAGO');
insert into DEPT values(40,'OPERATIONS','BOSTON');
select * from DEPT;
create table DEPT(
DEPTNO NUMBER(2) primary key,
DNAME VARCHAR(10),
LOC VARCHAR(10));
desc DEPT;
insert into DEPT values(10,'ACCOUNTING','NEW YORK');
insert into DEPT values(20,'RESEARCH','DALLAS');
insert into DEPT values(30,'SALES','CHICAGO');
insert into DEPT values(40,'OPERATIONS','BOSTON');
select * from DEPT;
select * from EMP;
desc EMP;
alter table EMP modify ENAME VARCHAR(15);
alter table EMP add Ph_no NUMBER(10);
update EMP SET Ph_no=9959581871 where EMPNO=7369;
select * from EMP;
update EMP SET Ph_no=9959581878 where EMPNO=7521;
update EMP SET Ph_no=9959581878 where EMPNO=7566;
update EMP SET Ph_no=9959581878 where EMPNO=7654;
update EMP SET Ph_no=9959581878 where EMPNO=7698;
update EMP SET Ph_no=9959581878 where EMPNO=7782;
update EMP SET Ph_no=9959581878 where EMPNO=7788;
update EMP SET Ph_no=9959581878 where EMPNO=7839;
update EMP SET Ph_no=9959581878 where EMPNO=7844;
update EMP SET Ph_no=9959581878 where EMPNO=7876;
update EMP SET Ph_no=9959581878 where EMPNO=7900;
update EMP SET Ph_no=9959581878 where EMPNO=7902;
update EMP SET Ph_no=9959581878 where EMPNO=7934;
select * from EMP;
select * from PROJECT;
update PROJECT SET BUDGET=90000 where PNO=65;
alter table EMP add COMM NUMBER(4);
update EMP SET COMM=300 where EMPNO=7499;
update EMP SET COMM=500 where EMPNO=7521;
update EMP SET COMM=1400 where EMPNO=7654;
update EMP SET COMM=0 where EMPNO=7844;
select * from EMP;
delete from EMP PH_NO;
create table CLIENT_MASTER(
ClientNo varchar(6) primary key,
Name varchar(20),
City varchar(15),
Pincode number(8),
State varchar(15),
BalDue number(10,2));
desc CLIENT_MASTER;
insert into CLIENT_MASTER values('C00001','Ivan Bayross','Mumbai',400054,'Maharashtra',15000);
insert into CLIENT_MASTER values('C00002','Mamta Muzumdar','Madras',780001,'Tamil Nadu',0);
insert into CLIENT_MASTER values('C00003','Chhaya Bankar','Mumbai',400057,'Maharashtra',5000);
insert into CLIENT_MASTER values('C00004','Ashwini Joshi','Bangalore',560001,'Karnataka',0);
insert into CLIENT_MASTER values('C00005','Hansel Colaco','Mumbai',400060,'Maharashtra',2000);
insert into CLIENT_MASTER values('C00006','Deepak Sharma','Mangalore',560050,'Karnataka',0);
insert into CLIENT_MASTER values('C00007','Manish Jain','Mangalore',560050,'Karnataka',7000);
insert into CLIENT_MASTER values('C00008','Kavita Negi','Madras',780001,'Tamil Nadu',6500);
SELECT * FROM CLIENT_MASTER;
desc EMP;
desc DEPT;
desc PROJECT;
create table Client_Master(
ClientNo varchar(6) primary key,
Name varchar(20),
City varchar(15),
BalDue number(10,2));
insert into Client_Master values('C00001','Ivan Bayross','Mumbai',15000);
insert into CLIENT_MASTER values('C00002','Mamta Muzumdar','Madras',0);
insert into CLIENT_MASTER values('C00003','Chhaya Bankar','Mumbai',5000);
insert into CLIENT_MASTER values('C00004','Ashwini Joshi','Bangalore',0);
insert into CLIENT_MASTER values('C00005','Hansel Colaco','Mumbai',2000);
alter table Client_Master drop PRIMARY KEY;
create table Student(
Roll_No NUMBER(4) primary key,
Name VARCHAR(20) unique,
City VARCHAR(15),
Marks NUMBER(5,2) not null);
desc Student;
insert into Student values(2001,'Aditya Narang','Delhi',75.67);
insert into Student values(2002,'Mamta Kohli','Delhi',80);
insert into Student values(2003,'Chavi Mehta','Delhi',50.45);
insert into Student values(2004,'Ashwini Jindal','Delhi',90.25);
insert into Student values(2005,'Harshit Jain','Delhi',66.20);
select * from Student;
select * from EMP;
select * from EMP where SAL>2000;
select * from EMP where SAL between 1500 AND 3000;
select EMPNO, ENAME, JOB from EMP where JOB='SALESMAN';
select * FROM EMP where hiredate >= TO_DATE('1981-06-09', 'YYYY-MM-DD');
select EMPNO, ENAME, JOB from EMP where JOB in ('SALESMAN', 'MANAGER', 'ANALYST');
select EMPNO, ENAME, JOB from EMP where JOB = 'SALESMAN' and EMPNO > 7500;
select * from DEPT;
select DEPTNO, DNAME from DEPT where DEPTNO IN (10, 30);
select DEPTNO, DNAME from DEPT where DNAME IN ('RESEARCH', 'SALES');
select EMPNO, ENAME from EMP where ENAME LIKE 'M%';
select EMPNO, ENAME from EMP where ENAME LIKE '%N';
select EMPNO, ENAME from EMP where ENAME LIKE 'M%' or ENAME LIKE '%N';
select EMPNO, ENAME from EMP where ENAME LIKE '%A%' and JOB = 'SALESMAN';
select * from DEPT where DNAME LIKE '%O%O%';
select * from EMP;
alter table EMP add MGR varchar(4);
alter table EMP add DEPTNO number(2);
update EMP set MGR=7902, DEPTNO=20 where EMPNO=7369;
update EMP set MGR=7698, DEPTNO=30 where EMPNO=7499;
update EMP set MGR=7698, DEPTNO=30 where EMPNO=7521;
update EMP set MGR=7839, DEPTNO=20 where EMPNO=7566;
update EMP set MGR=7698, DEPTNO=30 where EMPNO=7654;
update EMP set MGR=7839, DEPTNO=30 where EMPNO=7698;
update EMP set MGR=7839, DEPTNO=10 where EMPNO=7782;
update EMP set MGR=7566, DEPTNO=20 where EMPNO=7788;
update EMP set MGR=NULL, DEPTNO=10 where EMPNO=7839;
update EMP set MGR=7698, DEPTNO=30 where EMPNO=7844;
update EMP set MGR=7788, DEPTNO=20 where EMPNO=7876;
update EMP set MGR=7698, DEPTNO=30 where EMPNO=7900;
update EMP set MGR=7566, DEPTNO=20 where EMPNO=7902;
update EMP set MGR=7782, DEPTNO=10 where EMPNO=7934;
alter table EMP add EDNO number(5);
update EMP set EDNO=10 where EMPNO in(7369,7499,7521,7566);
update EMP set EDNO=20 where EMPNO in(7654,7698,7782,7788,7839);
update EMP set EDNO=30 where EMPNO in(7844,7876,7900,7902,7934);
alter table EMP add constraint FK_EDNO FOREIGN KEY(EDNO) references DEPT(DEPTNO);
create table WORK_FOR(
ENO number(4) primary key,
PNO number(2),
WEEK_HRS number(2));
insert into WORK_FOR values(7369,10,20);
insert into WORK_FOR values(7499,20,50);
insert into WORK_FOR values(7876,10,20);
insert into WORK_FOR values(7902,50,45);
insert into WORK_FOR values(7900,30,40);
insert into WORK_FOR values(7521,60,40);
insert into WORK_FOR values(7934,80,20);
insert into WORK_FOR values(7566,45,34);
insert into WORK_FOR values(7782,60,25);
insert into WORK_FOR values(7901,75,10);
insert into WORK_FOR values(7698,30,30);
insert into WORK_FOR values(7844,20,20);
insert into WORK_FOR values(7903,70,50);
select * from WORK_FOR;
create table COURSE(
Course_ID number(3) primary key,
Cname varchar(30),
Fees number(10,2) check(Fees>=60000),
Percentage number(3),
Floor number(2),
HOD varchar(20));
desc COURSE;
insert into COURSE values(101,'BBA',75000,75,3,'Dr.Vijay');
insert into COURSE values(102,'B.Com(H)',80000,80,8,'Dr.Majnu');
insert into COURSE values(103,'Eco(H)',85000,85,9,'Dr.Sanjay');
insert into COURSE values(104,'BJMC',65000,65,4,'Dr.Vinita');
insert into COURSE values(105,'BBA-LLB',70000,70,7,'Dr.Praveen');
select * from COURSE;
create table Faculty (
FID NUMBER(3) PRIMARY KEY,
Fname VARCHAR2(30) NOT NULL,
Co_ID NUMBER(3),
Specializa on VARCHAR2(30) CHECK (Specializa on IN ('IT', 'Finance', 'HR', 'Marke ng')),
CONSTRAINT _Course FOREIGN KEY (Co_ID) REFERENCES COURSE(COURSE_ID));
desc Faculty;
insert into Faculty values(111, 'Dr.Sarita', 101, 'IT');
insert into Faculty values(112, 'Dr.Deepak', 101, 'IT');
insert into Faculty values(113, 'Dr.Sangeeta', 101, 'IT');
insert into Faculty values(114, 'Dr.Kamna', 102, 'IT');
insert into Faculty values(115, 'Dr.Ria', 103, 'Marke ng');
insert into Faculty values(116, 'Dr.Diksha', 104, 'HR');
insert into Faculty values(117, 'Dr.Parul', 105, 'Finance');
select * from Faculty;
create table STUDENTS(
Enrol_No NUMBER(4) PRIMARY KEY,
SName VARCHAR2(30) NOT NULL,
DOB DATE CHECK (DOB >= TO_DATE('2000-01-01', 'YYYY-MM-DD')),
City VARCHAR(30),
CID NUMBER(3),
Mobile NUMBER(10) UNIQUE,
Class_coordinator NUMBER(3),
CONSTRAINT _Course_Student FOREIGN KEY (CID) REFERENCES Course(Course_ID),
CONSTRAINT _Faculty_Student FOREIGN KEY (Class_coordinator) REFERENCES Faculty(FID));
desc STUDENTS;
insert into STUDENTS values(1, 'Rajasv', TO_DATE('2001-02-15', 'YYYY-MM-DD'), 'Delhi', 101,
1234567890, 111);
insert into STUDENTS values(2, 'Daksh', TO_DATE('2001-02-15', 'YYYY-MM-DD'), 'Delhi', 101,
4578412257, 111);
insert into STUDENTS values(3, 'Sneha', TO_DATE('2001-02-15', 'YYYY-MM-DD'), 'Mumbai', 102,
4578412278, 112);
insert into STUDENTS values(4, 'Meenal', TO_DATE('2001-02-15', 'YYYY-MM-DD'), 'Mumbai', 102,
4578445278, 112);
insert into STUDENTS values(5, 'Goyam', TO_DATE('2001-02-15', 'YYYY-MM-DD'), 'Chennai', 103,
4788412278, 113);
insert into STUDENTS values(6, 'Yash', TO_DATE('2001-02-15', 'YYYY-MM-DD'), 'Chennai', 103,
4574812278, 113);
insert into STUDENTS values(7, 'Vikas', TO_DATE('2001-02-15', 'YYYY-MM-DD'), 'Kolkata', 104,
4578413378, 114);
insert into STUDENTS values(8, 'Saad', TO_DATE('2001-02-15', 'YYYY-MM-DD'), 'Kolkata', 104,
3547412278, 115);
insert into STUDENTS values(9, 'Shubhi', TO_DATE('2001-02-15', 'YYYY-MM-DD'), 'Hyderabad', 105,
4578941278, 116);
insert into STUDENTS values(10, 'Aman', TO_DATE('2001-02-15', 'YYYY-MM-DD'), 'Hyderabad', 105,
8287958745, 117);
select * from STUDENTS;
select ENAME,JOB,SAL from EMP;
select ENAME,JOB,SAL,DEPTNO from EMP where JOB='MANAGER';
select * from EMP where JOB!='CLERK';
select * from EMP where SAL>2500;
select LOC from DEPT where DNAME='OPERATIONS';
select * from PROJECT where PTYPE='REAL_ESTATE';
select * from EMP where COMM='0';
select ENAME as "Name of Employee", HIREDATE as "DOJ" from EMP;
select * from EMP where COMM > SAL;
select * from PROJECT where BUDGET > 200000;
select DISTINCT PTYPE from PROJECT;
select DISTINCT JOB from EMP;
select * from EMP where JOB='PRESIDENT';
select * from PROJECT;
select LOWER(PTYPE) as project_type_lower from PROJECT;
select UPPER(ename) as upper_case_name, UPPER(job) as upper_case_job from EMP;
select * from EMP where UPPER(job) like '%MAN%';
select INITCAP(LOC) as capitalized_loca on from DEPT;
select INITCAP(ename) as capitalized_full_name from EMP;
select SUBSTR(PTYPE, 1, 3) as first_three_characters from PROJECT;
select DISTINCT SUBSTR(PTYPE, 1, 3) as first_three_characters from PROJECT;
select SUBSTR(ename, INSTR(ename, '',-1)+1) as last_name from EMP;
select PTYPE, LENGTH(PTYPE) as length_of_ptype from PROJECT;
select ename, SUBSTR(ename, -3) as last_three_characters from EMP;
select CONCAT(ename, ' is working as ') || job from EMP;
select CONCAT(DNAME, ' is located at ') || LOC from DEPT;
select REGEXP_REPLACE(job, '(^|\s)MAN(\w*)', '\1\2') as modified_job from EMP;
select REGEXP_REPLACE(job, '(\w*)MAN(\s|$)', '\1\2') as modified_job from EMP;
select ename as employee_name, LPAD('Rs ' || TO_CHAR(SAL), 6, ' ') as padded_salary from EMP;
select ename as employee_name, RPAD(TO_CHAR(SAL) || ' $', 5, ' ') as padded_salary from EMP;
select ename, REVERSE(ename) as reversed_name from EMP;
select REPLACE('Today is Sunny Day', 'Sunny', 'Cloudy') as modified_sentence from dual;
select ename as employee_name, CASE WHEN job = 'ANALYST' THEN 'PROGRAMMER' ELSE job END as
modified_job from EMP;
select ename as employee_name, '(' || TO_CHAR(SAL, '$999,999.00') || ')' as forma ed_salary from
EMP;
select SYSDATE from DUAL;
select TO_CHAR(SYSDATE, 'HH24:MI:SS') as current_ me from DUAL;
select TO_CHAR(SYSDATE, 'DAY') as current_day from DUAL;
select TO_CHAR(SYSDATE, 'FMDAY, DDth MONTH, YYYY') as forma ed_date from DUAL;
select TO_CHAR(HIREDATE, 'FMDAY, DDth MONTH, YYYY') as forma ed_hire_date from EMP;
select TO_CHAR(TO_DATE('15-AUG-1947', 'DD-MON-YYYY'), 'FMDAY') as independence_day from DUAL;
select TRUNC(SYSDATE - TO_DATE('15-AUG-1947', 'DD-MON-YYYY')) as days_since_independence from
DUAL;
select SYSDATE + 45 as date_a er_45_days from DUAL;
select EMPNO, ENAME, HIREDATE, TRUNC(SYSDATE - HIREDATE) as days_since_hire from EMP;
select EMPNO, ENAME, HIREDATE, TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) as
months_since_join from EMP;
select LAST_DAY(SYSDATE) as last_day_of_month from DUAL;
select EMPNO, ENAME, HIREDATE, EXTRACT(YEAR FROM HIREDATE) as hire_year from EMP;
create table STUDENT3(
ROLLNO varchar(5) primary key,
NAME varchar(20) not null,
GENDER varchar(1) not null,
DOB date not null,
SECTION varchar(20),
QUOTA varchar(5));
desc STUDENT3;
insert into STUDENT3 values(001,'RAVI MITTAL','M','12-SEP-2002','A','GEN');
insert into STUDENT3 values(002,'SANDEEO GOYAL','M','14-FEB-2002','A','MGMT');
insert into STUDENT3 values(003,'VIRAJ SINGHAL','M','12-JAN-2003','A','DEF');
insert into STUDENT3 values(004,'GITA KAPOOR','F','12-APR-2002','A','DEF');
insert into STUDENT3 values(005,'RICHA MITTAL','F','12-SEP-2002','B','OBC');
insert into STUDENT3 values(006,'ANSHUL GUPTA','M','14-FEB-2002','C','OBC');
insert into STUDENT3 values(007,'NEHA AGG.','F','12-JAN-2003','B','SC');
insert into STUDENT3 values(008,'AMIT GUPTA','M','12-APR-2002','C','GEN');
insert into STUDENT3 values(009,'SANYA','F','12-JAN-2003','C','GEN');
insert into STUDENT3 values(010,'RESHMA','M','12-APR-2002','C','GEN');
insert into STUDENT3 values(011,'AYAAN','M','12-SEP-2002','B','DEF');
insert into STUDENT3 values(012,'VIAAN','F','14-FEB-2002','A','DEF');
insert into STUDENT3 values(013,'SACHIN','M','12-JAN-2003','A','MGMT');
insert into STUDENT3 values(014,'MUKESH','M','12-APR-2002','B','MGMT');
insert into STUDENT3 values(015,'SIMRAN','F','12-SEP-2002','C','GEN');
insert into STUDENT3 values(016,'BHAVESH','M','14-FEB-2002','B','GEN');
insert into STUDENT3 values(017,'RUCHIKA','F','12-JAN-2003','B','DEF');
insert into STUDENT3 values(018,'SAMEER','M','12-APR-2002','A','DEF');
insert into STUDENT3 values(019,'ANIKA','F','12-JAN-2003','A','MGMT');
insert into STUDENT3 values(020,'NEHA','F','12-APR-2002','A','GEN');
insert into STUDENT3 values(021,'SANGEETA','F','14-FEB-2002','B','GEN');
insert into STUDENT3 values(022,'DEEPAK','M','12-JAN-2003','B','GEN');
insert into STUDENT3 values(023,'RUCHIKA','F','12-APR-2002','C','MGMT');
insert into STUDENT3 values(024,'NAVYA','F','12-SEP-2002','C','DEF');
insert into STUDENT3 values(025,'DEEPTI','F','12-APR-2002','A','DEF');
insert into STUDENT3 values(026,'SANGEETA','F','12-JAN-2003','A','MGMT');
insert into STUDENT3 values(027,'SARIKA','F','12-APR-2002','A','OBC');
insert into STUDENT3 values(028,'SAUMYA','F','14-FEB-2002','B','OBC');
insert into STUDENT3 values(029,'VATSAL','M','12-JAN-2003','B','OBC');
insert into STUDENT3 values(030,'PRAVEEN','M','12-APR-2002','A','MGMT');
create view SECTION_A_STUDENTS as select * from STUDENT3 where sec on='A';
create view SECTION_B_STUDENTS as select * from STUDENT3 where sec on='B';
create view SECTION_C_STUDENTS as select * from STUDENT3 where sec on='C';
create view MGMT_QUOTA_STUDENTS as select * from STUDENT3 where quota='MGMT';
create view GEN_QUOTA_STUDENTS as select * from STUDENT3 where quota='GEN';
create view DEF_QUOTA_STUDENTS as select * from STUDENT3 where quota='DEF';
select * from SECTION_A_STUDENTS;
select * from SECTION_B_STUDENTS;
select * from SECTION_C_STUDENTS;
select * from MGMT_QUOTA_STUDENTS;
select * from GEN_QUOTA_STUDENTS;
select * from DEF_QUOTA_STUDENTS;
select AVG(SAL) as AVERAGE_SALARY from EMP;
select ROUND(AVG(SAL), 2) as AVERAGE_SALARY from EMP;
select TRUNC(AVG(SAL), 0) as TRUNCATED_AVERAGE_SALARY from EMP;
select MIN(SAL) as MINIMUM_SALARY from EMP;
select MAX(SAL) as MAXIMUM_SALARY from EMP;
select COUNT(*) as NUMBER_OF_RECORDS from EMP;
select COUNT(*) as NO_OF_EMPLOYEES_WITH_NULL_COMM from EMP where COMM is NULL;
select COUNT(*) as NO_OF_EMPLOYEES_WITH_COMM from EMP where COMM is NOT NULL;
select COUNT(*) as NUMBER_OF_RECORDS from WORK_FOR;
select COUNT(*) as NO_OF_EMPLOYEES_ON_PROJECT_10 from WORK_FOR where PNO = 10;
select SUM(WEEK_HRS) as TOTAL_WEEK_HRS_ON_PROJECT_10 from WORK_FOR where PNO = 10;
select SUM(BUDGET) as TOTAL_BGT_FOR_PAPER_PROJECTS from PROJECT where PTYPE = 'PAPER';
select * from DEPT ORDER BY DEPTNO ASC;
select * from EMP ORDER BY DEPTNO ASC, JOB DESC;
select * from WORK_FOR ORDER BY PNO ASC;
select * from WORK_FOR ORDER BY ENO ASC;
select DEPTNO, COUNT(*) as EMPLOYEE_COUNT from EMP GROUP BY DEPTNO;
select JOB, COUNT(*) as EMPLOYEE_COUNT from EMP GROUP BY JOB;
select PTYPE, SUM(BUDGET) as TOTAL_BUDGET from PROJECT GROUP BY PTYPE;
select JOB, MAX(SAL) as MAX_SALARY, MIN(SAL) as MIN_SALARY from EMP GROUP BY JOB;
select DEPTNO, MAX(SAL) as MAX_SALARY, MIN(SAL) as MIN_SALARY from EMP GROUP BY DEPTNO;
select PNO, COUNT(ENO) as TOTAL_EMPLOYEES, SUM(WEEK_HRS) AS TOTAL_WEEK_HOURS from
WORK_FOR GROUP BY PNO;
select MAX(SAL) as MAX_MANAGER_SALARY, MIN(SAL) as MIN_MANAGER_SALARY from EMP where
JOB = 'MANAGER';
select MAX(SAL) as MAX_SALARY_DEPT_10, MIN(SAL) as MIN_SALARY_DEPT_10 from EMP where
DEPTNO = 10;
select COUNT(ENO) as TOTAL_EMPLOYEES, SUM(WEEK_HRS) as TOTAL_WEEK_HOURS from WORK_FOR
where PNO = 10;
select DEPTNO, AVG(SAL) as AVERAGE_SALARY from EMP GROUP BY DEPTNO HAVING COUNT(EMPNO)
> 4;
select EMP.* from EMP JOIN DEPT on EMP.DEPTNO = DEPT.DEPTNO where DEPT.DNAME = 'SALES';
select EMP.* from EMP JOIN WORK_FOR on EMP.EMPNO = WORK_FOR.ENO where WORK_FOR.PNO in
(10, 20);
select DISTINCT MGR_MANAGER.* from EMP EMP_SALESMAN JOIN EMP MGR_MANAGER on
EMP_SALESMAN.MGR = MGR_MANAGER.EMPNO where EMP_SALESMAN.JOB = 'SALESMAN';
select COUNT(*) as num_employees from EMP where EMP.EMPNO in (select WORK_FOR.ENO from
WORK_FOR JOIN PROJECT on WORK_FOR.PNO = PROJECT.PNO where PROJECT.PTYPE =
'ELECTRONICS');
select EMP.EMPNO,
EMP.ENAME,
SUM(WORK_FOR.WEEK_HRS) as TOTAL_HOURS_WORKED
from EMP
JOIN WORK_FOR on EMP.EMPNO = WORK_FOR.ENO
JOIN PROJECT on WORK_FOR.PNO = PROJECT.PNO
where PROJECT.PTYPE = 'ELECTRONICS'
GROUP BY EMP.EMPNO, EMP.ENAME;
select EMP.ENAME,
DEPT.DNAME
from EMP
JOIN DEPT on EMP.DEPTNO = DEPT.DEPTNO
where EMP.JOB = 'PRESIDENT';
select EMP.ENAME,
DEPT.DNAME,
DEPT.LOC
from EMP
JOIN DEPT on EMP.DEPTNO = DEPT.DEPTNO
where EMP.JOB = 'PRESIDENT';
select EMP.ENAME,
EMP.JOB,
DEPT.DNAME,
DEPT.LOC
from EMP
JOIN DEPT on EMP.DEPTNO = DEPT.DEPTNO
where DEPT.LOC = 'CHICAGO';
select EMP.ENAME,
EMP.JOB,
DEPT.DNAME,
DEPT.LOC
from EMP
JOIN DEPT on EMP.DEPTNO = DEPT.DEPTNO
where DEPT.LOC <> 'CHICAGO' OR DEPT.DEPTNO is NULL;
select MAX(EMP.SAL) AS MAX_SALARY
from EMP
JOIN DEPT on EMP.DEPTNO = DEPT.DEPTNO
where DEPT.DNAME = 'ACCOUNTING';
select EMP.ENAME,
EMP.JOB,
MAX(EMP.SAL) AS MAX_SALARY
from EMP
JOIN DEPT on EMP.DEPTNO = DEPT.DEPTNO
where DEPT.DNAME = 'ACCOUNTING'
GROUP BY EMP.ENAME, EMP.JOB;
select EMP.ENAME,
EMP.JOB,
PROJECT.PNAME
from EMP
JOIN WORK_FOR on EMP.EMPNO = WORK_FOR.ENO
JOIN PROJECT on WORK_FOR.PNO = PROJECT.PNO
where PROJECT.PTYPE = 'PAPER';
select DISTINCT DEPT.DNAME
from DEPT
JOIN EMP on DEPT.DEPTNO = EMP.DEPTNO
JOIN WORK_FOR on EMP.EMPNO = WORK_FOR.ENO
JOIN PROJECT on WORK_FOR.PNO = PROJECT.PNO
where PROJECT.PNO IN (60, 70, 80);
select DEPT.DNAME
from DEPT
JOIN EMP on DEPT.DEPTNO = EMP.DEPTNO
JOIN WORK_FOR on EMP.EMPNO = WORK_FOR.ENO
where WORK_FOR.PNO = 60;
select ENAME
from EMP
where MGR = 7698;
select EMPNO, ENAME, DEPTNO, EDNO from EMP where JOB = 'MANAGER';
select * from EMP where SAL > (select SAL from EMP where ENAME = 'JONES');
select * from EMP where JOB = (select JOB from EMP where ENAME = 'SMITH');
select E.*
from EMP E
JOIN DEPT D1 on E.DEPTNO = D1.DEPTNO
where E.DEPTNO = 20
and E.JOB IN (
select DISTINCT E2.JOB
from EMP E2
JOIN DEPT D2 on E2.DEPTNO = D2.DEPTNO
where D2.DEPTNO = 10);
select * from EMP
where SAL IN (
select SAL
from EMP
where ENAME IN ('WARD', 'FORD'))
ORDER BY SAL DESC;
select * from EMP
where JOB = (select JOB from EMP where ENAME = 'JAMES')
and SAL > (select SAL from EMP where ENAME = 'MARTIN');
select * from EMP where SAL > (select SUM(SAL) from EMP where JOB = 'SALESMAN');
select E.* from EMP E
JOIN DEPT D on E.DEPTNO = D.DEPTNO
where E.HIREDATE > (select HIREDATE from EMP where ENAME = 'BLAKE')
and (D.LOC = 'NEW YORK' or D.LOC = 'CHICAGO');
select E.* from EMP E where E.SAL = (select MAX(SAL) from EMP
where DEPTNO = (select DEPTNO from DEPT where DNAME = 'RESEARCH'));
select DISTINCT JOB from EMP
where DEPTNO = 10
and JOB NOT IN (select DISTINCT JOB from EMP where DEPTNO = 20);
