The SQL*Plus

1) how to create a table with out constrain

create table EMPLOYEE
(
Sno number(5),
Name varchar(20),
Phone number(10),
branch varchar(5)
);

2) how to create a table with constrain

create table table_name
(
Sno number(5) check(Sno>0) primary key,
Name varchar(20) not null,
Phone number(10) not null unique check(length(Phone)=10),
branch varchar(5) not null
);

3) how to rename table name

RENAME Old_name to new_name;

4) how to delete table permanently from data base

truncate table EMPLOYEE2;

Truncate will empty the table (will not remove the table structure)

5) how to drop the table from database

drop table table_name;

6)

create table Like this

THIS QUREY FOR CREATING EMP TABLE

CREATE TABLE EMP (
    EMPNO NUMBER(4),
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2)
);

INSERTING DATA INTO EMP TABLE

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-80', 'DD-MON-YY'), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-81', 'DD-MON-YY'), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-81', 'DD-MON-YY'), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('02-APR-81', 'DD-MON-YY'), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('28-SEP-81', 'DD-MON-YY'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('01-MAY-81', 'DD-MON-YY'), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('09-JUN-81', 'DD-MON-YY'), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('19-APR-87', 'DD-MON-YY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('17-NOV-81', 'DD-MON-YY'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('08-SEP-81', 'DD-MON-YY'), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('23-MAY-87', 'DD-MON-YY'), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, TO_DATE('03-DEC-81', 'DD-MON-YY'), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('03-DEC-81', 'DD-MON-YY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, TO_DATE('23-JAN-82', 'DD-MON-YY'), 1300, NULL, 10);

THIS QUREY FOR CREATING DEPT TABLE

CREATE TABLE DEPT (
    DEPTNO NUMBER(2),
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13)
);

INSERTING DATA INTO DEPT TABLE

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

NOW RUN THIS COMMAND

SELECT * FROM EMP;
SELECT * FROM DEPT;

1) HOW TO DISPLAY NAME FROM EMP TABLE

SELECT ENAME
FROM EMP;

2) HOW TO DISPLAY ALL THE COLUMNS FROM EMP TABLE

SELECT *
FROM EMP;

3) HOW TO DISPLAY ANNUAL SALARY FOR EMPOYEE

SELECT SAL*12
FROM EMP;

4) HOW TO DISPLAY HALF TERM ANNUAL SALARY FOR EMPOYEE

SELECT SAL*6
FROM EMP;

5) HOW TO DISPLAY HALF TERM ANNUAL SALARY FOR EMPOYEE

SELECT SAL*6
FROM EMP;

6) HOW TO DISPLAY YEAR SALARY WITH 20% HIKE EMPOYEE

SELECT SAL*12+SAL*12 *20/100
FROM EMP;

7) HOW TO DISPLAY YEAR SALARY WITH 20% HIKE EMPOYEE

SELECT SAL*12-SAL*12 *9/100
FROM EMP;

ALIAS

* Alias is an alternative name which is assigned for a column name or an experssion in the result table
* we can assign alias name with or without using "As" Keywords
* Alias name must be a Single String Which is Seperate by an Unterscorce or enclosed within double quotes

1) HOW TO Change output column name

SELECT SAL*12 AS ANNUAL_SALARY
FROM EMP;

OR

SELECT SAL*12 AS "ANNUAL SALARY"
FROM EMP;

SELECT CLAUS

* Select is used to Retive the Data By Selecting Row and Columns

1) WAQTD names of the employees if they are working in department number 10

SELECT ENAME
FROM EMP
WHERE DEPTNO= 10;

2) WAQTD detail of the employees if they are not working in department number 10

SELECT *
FROM EMP
WHERE DEPTNO !=10;

3) WAQTD det ails of the employees if they are getting salary more than 1000

SELECT *
FROM EMP
WHERE SAL > 1000;

4) WAQTD Name Salary and Designation of the employees if they are getting salary less then 5000

SELECT ENAME,SAL,JOB
FROM EMP
WHERE SAL < 5000;

5) WAQTD Details of the Employee if they are working as manager

SELECT *
FROM EMP
WHERE JOB = 'MANAGER';

6) WAQTD Details of the employee if they hired after 81

SELECT *
FROM EMP
WHERE HIREDATE > '31-DEC-81';

7) WAQTD name and sala along with his Annual salary if the annual salary is more than 1200

SELECT ENAME, SAL, SAL*12 AS ANNUAL_SALARY
FROM EMP
WHERE SAL*12 < 1200;

8) WAQTD EMPNO OF THE EMPLOYEES WHO ARE WORKING IN DEPT 30

SELECT EMPNO , DEPTNO
FROM EMP
WHERE DEPTNO = 30;

9) WAQTD ENAME AND HIREDATE IF THEY ARE HIRED BEFORE 1981

SELECT ENAME, HIREDATE
FROM EMP
WHERE HIREDATE < TO_DATE('01-JAN-1981', 'DD-MON-YYYY');

10) WAQTD DETAILS OF THE EMPLOYEES WORKING AS MANAGER

SELECT *
FROM EMP
WHERE JOB = 'MANAGER';

11) WAQTD NAME AND SALARY GIVEN TO AN EMPLOYEE IF EMPLOYEE EARNS A COMMISSION OF RUPEES 1400

SELECT ENAME,SAL,COMM
FROM EMP
WHERE COMM = 1400;

12) WAQTD DETAILS OF THE EMPLOYEES HAVING COMMISSION MAORE THAN SALARY

SELECT *
FROM EMP
WHERE SAL < COMM;

SELECT CLAUS WITH DUAL CONDTION

HI

1) WAQTD DETAILS OF THE EMPLOYEES WORKING AS CLERK AND EARNING LESS THAN 1500

2) WAQTD NAME AND HIREDATE OF THE EMPLOYEES WORKING AS MANAGER IN DEPT 30

3) WAQTD DETAILS OF THE EMP ALONG WITH ANNUAL SALARY IF THEY ARE WORKING IN DEPT 30 AS SALESMAN AND THEIR ANNUAL SALARY HAS TO BE GREATER THAN 14000

4) WAQTD ALL THE DETAILS OF THE EMP WORKING IN DEPT 30 OR AS ANALYST

5) WAQTD NAMES OF THE EMPLOYEES WHOSE SALARY IS LESS THAN 1100 AND THEIR DESIGNATION IS CLERK

6) WAQTD NAME AND SAL, ANNUAL SAL AND DEPTNO IF DEPTNO IS 20 EARNING MORE THAN 1100 AND ANNUAL SALARY EXCEEDS 12000

7) WAQTD EMPNO AND NAMES OF THE EMPLOYEES WORKING AS MANAGER IN DEPT 20

8) WAQTD DETAILS OF EMPLOYEES WORKING IN DEPT 20 OR 30

9) WAQTD DETAILS OF EMPLOYEES WORKING AS ANALYST IN DEPT 10

10) WAQTD DETAILS OF EMPLOYEE WORKING AS PRESIDENT WITH SALARY OF RUPEES 4000

11) WAQTD NAMES AND DEPTNO, JOB OF EMPS WORKING AS CLERK IN DEPT 10 OR 20

12) WAQTD DETAILS OF EMPLOYEES WORKING AS CLERK OR MANAGER IN DEPT 10

13) WAQTD NAMES OF EMPLOYEES WORKING IN DEPT 10, 20, 30, 40

14) WAQTD DETAILS OF EMPLOYEES WITH EMPNO 7902, 7839

15)WAQTD DETAILS OF EMPLOYEES WORKING AS MANAGER OR SALESMAN OR CLERK

16)WAQTD NAMES OF EMPLOYEES HIRED AFTER 81 AND BEFORE 87

17) WAQTD DETAILS OF EMPLOYEES EARNING MORE THAN 1250 BUT LESS THAN 3000

18) WAQTD NAMES OF EMPLOYEES HIRED AFTER 81 INTO DEPT 10 OR 30

19) WAQTD NAMES OF EMPLOYEES ALONG WITH ANNUAL SALARY FOR THE EMPLOYEES WORKING AS MANAGER OR CLERK INTO DEPT 10 OR 30

20) WAQTD ALL THE DETAILS ALONG WITH ANNUAL SALARY IF SAL IS BETWEEN 1000 AND 4000 ANNUAL SALARY MORE THAN 15000

ASSIGNMENT ON EXPRESSION & ALIAS

HI

1)WAQTD NAME OF THE EMPLOYEE ALONG WITH THEIR ANNUAL SALARY.

2) WAQTD ENAME AND JOB FOR ALL THE EMPLOYEE WITH THEIR HALF TERM SALARY.

3) WAQTD ALL THE DETAILS OF THE EMPLOYEES ALONG WITH AN ANNUAL BONUS OF 2000.

4) WAQTD NAME SALARY AND SALARY WITH A HIKE OF 10%.

5) WAQTD NAME AND SALARY WITH DEDUCTION OF 25%.

6) WAQTD NAME AND SALARY WITH MONTHLY HIKE OF 50.

7) WAQTD NAME AND ANNUAL SALARY WITH DEDUCTION OF 10%.

8) WAQTD TOTAL SALARY GIVEN TO EACH EMPLOYEE (SAL + COMM).

9) WAQTD DETAILS OF ALL THE EMPLOYEES ALONG WITH ANNUAL SALARY.

10) WAQTD NAME AND DESIGNATION ALONG WITH 100 PENALTY IN SALARY.

ASSIGNMENT ON SPECIAL OPERATORS :

HI

1) LIST ALL THE EMPLOYEES WHOSE COMMISSION IS NULL

2) LIST ALL THE EMPLOYEES WHOSE COMMISSION IS NULL

3) LIST ALL THE SALESMEN IN DEPT 30

4) LIST ALL THE SALESMEN IN DEPT NUMBER 30 AND HAVING SALARY GREATER THAN 1500

5) LIST ALL THE EMPLOYEES WHOSE NAME STARTS WITH ‘S’ OR ‘A’

6)LIST ALL THE EMPLOYEES EXCEPT THOSE WHO ARE WORKING IN DEPT 10 & 20

7) LIST ALL THE EMPLOYEES EXCEPT THOSE WHO ARE WORKING IN DEPT 10 & 20

8) LIST ALL THE EMPLOYEES WHO ARE HAVING REPORTING MANAGERS IN DEPT 10

9) LIST ALL THE EMPLOYEES WHOSE COMMISSION IS NULL AND WORKING AS CLERK

10) LIST ALL THE EMPLOYEES WHO DON’T HAVE A REPORTING MANAGER IN DEPT NO 10 OR 30

11) LIST ALL THE SALESMEN IN DEPT 30 WITH SAL MORE THAN 2450

12) LIST ALL THE ANALYST IN DEPT NUMBER 20 AND HAVING SALARY GREATER THAN 2500

13) LIST ALL THE EMPLOYEES WHOSE NAME STARTS WITH ‘M’ OR ‘J’

14) LIST ALL THE EMPLOYEES WITH ANNUAL SALARY EXCEPT THOSE WHO ARE WORKING IN DEPT 30

15) LIST THE EMPLOYEES WHOSE NAME DOES NOT END WITH ‘ES’ OR ‘R’

16) LIST ALL THE EMPLOYEES WHO ARE HAVING REPORTING MANAGERS IN DEPT 10 ALONG WITH 10% HIKE IN SALARY

17) DISPLAY ALL THE EMPLOYEE WHO ARE ‘SALESMAN’ HAVING ‘E’ AS THE LAST BUT ONE CHARACTER IN ENAME BUT SALARY HAVING EXACTLY 4 CHARACTER

18) DISPLAY ALL THE EMPLOYEE WHO ARE JOINED AFTER YEAR 81

19) DISPLAY ALL THE EMPLOYEE WHO ARE JOINED IN FEB

20) LIST THE EMPLOYEES WHO ARE NOT WORKING AS MANAGERS AND CLERKS IN DEPT 10 AND 20 WITH A SALARY IN THE RANGE OF 1000 TO 3000.

ASSIGNMENT ON vowels :

HI

1) DISPLAY NAMES OF THE EMPLOYEES IF NAME STARTS WITH VOWELS

2) DISPLAY NAMES OF THE EMPLOYEES IF NAME ENDS WITH VOWELS

3) DISPLAY NAMES OF THE EMPLOYEES IF NAME DOES NOT START WITH VOWELS

4) DISPLAY NAMES OF THE EMPLOYEES IF NAME DOES NOT END WITH VOWELS

5) DISPLAY NAMES OF THE EMPLOYEES IF NAME STARTS WITH VOWELS BUT DOES NOT END WITH VOWELS

6) DISPLAY NAMES OF THE EMPLOYEES IF NAME DOES NOT START WITH VOWELS BUT ENDS WITH VOWELS

7) DISPLAY NAMES OF THE EMPLOYEES IF NAME DOES NOT START WITH VOWELS AND DOES NOT END WITH VOWELS

8) DISPLAY NAMES OF THE EMPLOYEES IF NAME STARTS WITH VOWELS AND ENDS WITH VOWELS VOWELS ----> A E I O U

ASSIGNMENT ON vowels :

HI

1) WAQTD NUMBER OF EMPLOYEES GETTING SALARY LESS THAN 2000 IN DEPTNO 10

2) WAQTD TOTAL SALARY NEEDED TO PAY EMPLOYEES WORKING AS CLERK

3) WAQTD AVERAGE SALARY NEEDED TO PAY ALL EMPLOYEES

4) WAQTD NUMBER OF EMPLOYEES HAVING 'A' AS THEIR FIRST CHARACTER

5) WAQTD NUMBER OF EMPLOYEES WORKING AS CLERK OR MANAGER

6) WAQTD TOTAL SALARY NEEDED TO PAY EMPLOYEES HIRED IN FEB

7) WAQTD NUMBER OF EMPLOYEES REPORTING TO 7839 (MGR)

8) WAQTD NUMBER OF EMPLOYEES GETTING COMMISSION IN DEPTNO 30

9) WAQTD AVG SAL, TOTAL SAL, NUMBER OF EMPS AND MAXIMUM SALARY GIVEN TO EMPLOYEES WORKING AS PRESIDENT

10) WAQTD NUMBER OF EMPLOYEES HAVING 'A' IN THEIR NAMES

11) WAQTD NUMBER OF EMPS AND TOTAL SALARY NEEDED TO PAY THE EMPLOYEES WHO HAVE 2 CONSECUTIVE L's IN THEIR NAMES

12) WAQTD NUMBER OF DEPARTMENTS PRESENT IN EMPLOYEE TABLE

13) WAQTD NUMBER OF EMPLOYEES HAVING CHARACTER 'Z' IN THEIR NAMES

14) WAQTD NUMBER OF EMPLOYEES HAVING '$' IN THEIR NAMES

15) WAQTD TOTAL SALARY GIVEN TO EMPLOYEES WORKING AS CLERK IN DEPT 30

16) WAQTD MAXIMUM SALARY GIVEN TO THE EMPLOYEES WORKING AS ANALYST

17) WAQTD NUMBER OF DISTINCT SALARIES PRESENT IN EMPLOYEE TABLE

18) WAQTD NUMBER OF DISTINCT SALARIES PRESENT IN EMPLOYEE TABLE

19) WAQTD AVG SALARY GIVEN TO THE CLERK

20) WAQTD MINIMUM SALARY GIVEN TO THE EMPLOYEES WHO WORK IN DEPT 10 AS MANAGER OR A CLERK

ASSIGNMENT QUESTION ON GROUP BY:

HI

1) WAQTD NUMBER OF EMPLOYEES WORKING IN EACH DEPARTMENT EXCEPT PRESIDENT.

2) WAQTD TOTAL SALARY NEEDED TO PAY ALL THE EMPLOYEES IN EACH JOB.

3) WAQTD NUMBER OF EMPLOYEES WORKING AS MANAGER IN EACH DEPARTMENT.

4) WAQTD AVG SALARY NEEDED TO PAY ALL THE EMPLOYEES IN EACH DEPARTMENT EXCLUDING THE EMPLOYEES OF DEPTNO 20.

5) WAQTD NUMBER OF EMPLOYEES HAVING CHARACTER 'A' IN THEIR NAMES IN EACH JOB.

6) WAQTD NUMBER OF EMPLOYEES AND AVG SALARY NEEDED TO PAY THE EMPLOYEES WHOSE SALARY IS GREATER THAN 2000 IN EACH DEPT.

7) WAQTD TOTAL SALARY NEEDED TO PAY AND NUMBER OF SALESMEN IN EACH DEPT.

8) WAQTD NUMBER OF EMPLOYEES WITH THEIR MAXIMUM SALARIES IN EACH JOB.

9) WAQTD MAXIMUM SALARIES GIVEN TO AN EMPLOYEE WORKING IN EACH DEPT.

10) WAQTD NUMBER OF TIMES THE SALARIES PRESENT IN EMPLOYEE TABLE.

ASSIGNMENT QUESTION ON HAVING CLAUSE:

HI

1) WAQTD DNO AND NUMBER OF EMP WORKING IN EACH DEPT IF THERE ARE ATLEAST 2 CLERKS IN EACH DEPT

2) WAQTD DNO AND TOTAL SAALARYNEEDED TO PAY ALL EMP IN EACH DEPT IF THERE ARE ATLEAST 4 EMP IN EACH DEPT

3) WAQTD NUMBER OF EMP EARNING SAL MORE THAN 1200 IN EACH JOB AND THE TOTAL SAL NEEDED TO PAY EMP OF EACH JOB MUST EXCEES 3800

4) WAQTD DEPTNO AND NUMBER OF EMP WORKING ONLY IF THERE ARE 2 EMP WORKING IN EACH DEPT AS MANAGER .

5) WAQTD JOB AND MAX SAL OF EMP IN EACH JOB IF THE MAX SAL EXCEEDS 2600

6) WAQTD THE SALARIES WHICH ARE REPEATED IN EMP TABLE

7) WAQTD THE HIREDATE WHICH ARE DUPLICATED IN EMP TABLE

8) WAQTD AVG SALARY OF EACH DEPT IF AVG SAL IS LESS THAN 3000

9) WAQTD DEPTNO IF THERE ARE ATLEAST 3 EMP IN EACH DEPT WHOS NAME HAS CHAR 'A' OR 'S'.

10) WAQTD MIN AND MAX SALARIES OF EACH JOB IF MIN SAL IS MORE THAN 1000 AND MAX SAL IS LESS THAN 5000 .

ASSIGNMENT QUESTION ON SUBQURERY OPERATOR :

HI

1) WAQTD NAME OF THE EMPLOYEES EARNING MORE THAN ADAMS

2) WAQTD NAME AND SALARY OF THE EMPLOYEES EARNING LESS THAN KING

3) WAQTD NAME AND DEPTNO OF THE EMPLOYEES IF THEY ARE WORKING IN THE SAME DEPT AS JONES

4) WAQTD NAME AND JOB OF ALL THE EMPLOYEES WORKING IN THE SAME DESIGNATION AS JAMES

5) WAQTD EMPNO AND ENAME ALONG WITH ANNUAL SALARY OF ALL THE EMPLOYEES IF THEIR ANNUAL SALARY IS GREATER THAN WARDS ANNUAL SALARY.

6) WAQTD NAME AND HIREDATE OF THE EMPLOYEES IF THEY ARE HIRED BEFORE SCOTT

7) WAQTD NAME AND HIREDATE OF THE EMPLOYEES IF THEY ARE HIRED AFTER THE PRESIDENT

8) WAQTD NAME AND SAL OF THE EMPLOYEE IF THEY ARE EARNING SAL LESS THAN THE EMPLOYEE WHOS EMPNO IS 7839

9) WAQTD ALL THE DETAILS OF THE EMPLOYEES IF THE EMPLOYEES ARE HIRED BEFORE MILLER

10) WAQTD ENAME AND EMPNO OF THE EMPLOYEES IF EMPLOYEES ARE EARNING MORE THAN ALLEN

11) WAQTD ENAME AND SALARY OF ALL THE EMPLOYEES WHO ARE EARNING MORE THAN MILLER BUT LESS THAN ALLEN .

12) WAQTD ALL THE DETAILS OF THE EMPLOYEES WORKING IN DEPT 20 AND WORKING IN THE SAME DESIGNATION AS SMITH

13) WAQTD ALL THE DETAILS OF THE EMPLOYEES WORKING AS MANAGER IN THE SAME DEPT AS TURNER

14) WAQTD NAME AND HIREDATE OF THE EMPLOYEES HIRED AFTER 1980 AND BEFORE KING

15) WAQTD NAME AND SAL ALONG WITH ANNUAL SAL FOR ALL EMPLOYEES WHOS SAL IS LESS THAN BLAKE AND MORE THAN 3500

16) WAQTD ALL THE DETAILS OF EMPLOYEES WHO EARN MORE THAN SCOTT BUT LESS THAN KING

17) WAQTD NAME OF THE EMPLOYEES WHOS NAME STARTS WITH 'A' AND WORKS IN THE SAME DEPT AS BLAKE

18) WAQTD NAME AND COMM IF EMPLOYEES EARN COMISSION AND WORK IN THE SAME DESIGNATION AS SMITH

19) WAQTD DETAILS OF ALL THE EMPLOYEES WORKING AS CLERK IN THE SAME DEPT AS TURNER .

20) WAQTD ENAME, SAL AND DESIGNATION OF THE EMPLOYEES WHOS ANNUAL SALARY IS MORE THAN SMITH AND LESS THAN KING.

ASSIGNMENT ON CASE 2:

21) WAQTD DNAME OF THE EMPLOYEES WHOS NAME IS SMITH

22) WAQTD DNAME AND LOC OF THE EMPLOYEE WHOS ENAME IS KING

23) WAQTD LOC OF THE EMP WHOS EMPLOYEE NUMBER IS 7902

24) WAQTD DNAME AND LOC ALONG WITH DEPTNO OF THE EMPLOYEE WHOS NAME ENDS WITH 'R'.

25 ) WAQTD DNAME OF THE EMPLOYEE WHOS DESIGNATION IS PRESIDENT

26) WAQTD NAMES OF THE EMPLOYEES WORKING IN ACCOUNTING DEPARTMENT

27) WAQTD ENAME AND SALARIES OF THE EMPLOYEES WHO ARE WORKING IN THE LOCATION CHICAGO

28) WAQTD ENAME AND SALARIES OF THE EMPLOYEES WHO ARE WORKING IN THE LOCATION CHICAGO

29) WAQTD DETAILS OF THE EMP ALONG WITH ANNUAL SALARY IF EMPLOYEES ARE WORKING IN NEW YORK

30) WAQTD NAMES OF EMPLOYEES WORKING IN OPERATIONS DEPARTMENT

ASSIGNMENT ON CASE 1&2 :

31) WAQTD NAMES OF THE EMPLOYEES EARNING MORE THAN SCOTT IN ACCOUNTING DEPT

32) WAQTD DETAILS OF THE EMPLOYEES WORKING AS MANAGER IN THE LOCATION CHICAGO

33) WAQTD NAME AND SAL OF THE EMPLOYEES EARNING MORE THAN KING IN THE DEPT ACCOUNTING

34) WAQTD DETAILS OF THE EMPLOYEES WORKING AS SALESMAN IN THE DEPARTEMENT SALES

35) WAQTD NAME, SAL, JOB, HIREDATE OF THE EMPLOYEES WORKING IN OPERATIONS DEPARTMENT AND HIRED BEFORE KING

36) DISPLAY ALL THE EMPLOYEES WHOSE DEPARTMET NAMES ENDING 'S'.

37) WAQTD DNAME OF THE EMPLOYEES WHOS NAMES HAS CHARACTER 'A' IN IT .

38) WAQTD DNAME AND LOC OF THE EMPLOYEES WHOS SALARY IS RUPEES 800 .

39) WAQTD DNAME OF THE EMPLOYEES WHO EARN COMISSION

40) WAQTD LOC OF THE EMPLOYEES IF THEY EARN COMISSION IN DEPT 40

ASSIGNMENT ON TYPES OF SUB QUERY.

51) WAQTD NAME OF THE EMPLOYEES EARNING SALARY MORE THAN THE SALESMAN

52) WAQTD DETAILS OF THE EMPLOYEES HIRED AFTER ALL THE CLERKS

53) WAQTD NAME AND SALARY FOR ALL THE EMPLOYEES IF THEY ARE EARNING LESS THAN ATLEST A MANAGER

54) WAQTD NAME AND HIREDATE OF EMPLOYEES HIRED BEFORE ALL THE MANAGERS

55) WAQTD NAMES OF THE EMPLOYEES HIRED AFTER ALL THE MANAGERS AND EARNING SALARY MORE THAN ALL THE CLERKS

56) WAQTD DETAILS OF THE EMPLOYEES WORKING AS CLERK AND HIRED BEFORE ATLEST A SALESMAN

57) WAQTD DETAILS OF EMPLOYEES WORKING IN ACCOUNTING OR SALES DEPT

58) WAQTD DEPARTMENT NAMES OF THE EMPLOYEES WITH NAME SMITH, KING AND MILLER

59) WAQTD DETAILS OF EMPLOYEES WORKING NEWYORK OR CHICAGO

60) WAQTD EMP NAMES IF EMPLOYEES ARE HIRED AFTER ALL THE EMPLOYEES OF DEPT 10

ASSIGNMENT ON MAX & MIN :

1 ) WAQTD NAME OF THE EMPLOYEE EARNING MAXIMUM SALARY

2) WAQTD NAME OF THE EMPLOYEE EARNING MINIMUM SALARY

3) WAQTD NAME AND HIREDATE OF THE EMPLOYEE HIRED BEFORE ALL THE EMPLOYEES (FIRST EMP)

4) WAQTD NAME AND HIREDATE OF THE EMPLOYEES HIRED AT THE LAST

5) WAQTD NAME, COMM OF THE EMPLOYEE WHO EARNS MIN COMISSION

6) WAQTD NAME, SAL AND COMM OF THE EMPLOYEE EARNING MAXIMUM COMISSION

7) WAQTD DETAILS OF THE EMPLOYEE WHO HAS GREATEST EMPNO

8) WAQTD DETAILS OF THE EMPLOYEES HAVING THE LEAST HIREDATE

9) WAQTD DETAILS OF THE EMPLOYEES EARNING LEAST ANNUAL SALARY

10) WAQTD NAME, ANNUAL SALARY OF THE EMPLOYEES IF THEIR ANNUAL SALARY IS MORE THAN ALL THE SALESMAN

ASSIGNMENT ON NESTED SUB QUERY :

1 ) WAQTD 2ND MINIMUM SALARY

2) WAQTD 5TH MAXIMUM SALARY

3) WAQTD NAME OF THE EMPLOYEE EARNING 3RD MAXIMUM SALARY

4) WAQTD EMPNO OF THE EMPLOYEE EARNING 2D MAXIMUM SALARY

5) WAQTD DEPARTMENT NAME OF AN EMPLOYEE GETTING 4TH MAX SAL

6) WAQTD DETAILS OF THE EMPLOYEE WHO WAS HIRED 2nd

7) WAQTD NAME OF THE EMPLOYEE HIRED BEFORE THE LAST EMPLOYEE

8) WAQTD LOC OF THE EMPLOYEE WHO WAS HIRED FIRST

9) WAQTD DETAILS OF THE EMPLOYEE EARNING 7TH MINIMUM SALARY

10) WAQTD DNAME OF EMPLOYEE GETTING 2ND MAXIMUM SALARY

ASSIGNMENT ON INNER JOIN :

1 ) NAME OF THE EMPLOYEE AND HIS LOCATION OF ALL THE EMPLOYEES .

2) WAQTD DNAME AND SALARY FOR ALL THE EMPLOYEE WORKING IN ACCOUNTING.

3) WAQTD DNAME AND ANNUAL SALARY FOR ALL EMPLOYEES WHOS SALARY IS MORE THAN 2340

4) WAQTD DNAME AND ANNUAL SALARY FOR ALL EMPLOYEES WHOS SALARY IS MORE THAN 2340

5) WAQTD DNAME AND ANNUAL SALARY FOR ALL EMPLOYEES WHOS SALARY IS MORE THAN 2340

6) WADTD DNAME AND JOB FOR ALL THE EMPLOYEES WHOS JOB AND DNAME STARTS WITH CHARACTER 'S'

7) WAQTD DNAME AND MGR NO FOR EMPLOYEES REPORTING TO 7839

8) WAQTD DNAME AND HIREDATE FOR EMPLOYEES HIRED AFTER 83 INTO ACCOUNTING OR RESEARCH DEPT

9) WAQTD ENAME AND DNAME OF THE EMPLOYEES WHO ARE GETTING COMM IN DEPT 10 OR 30

10) WAQTD DNAME AND EMPNO FOR ALL THE EMPLOYEES WHO'S EMPNO ARE (7839,7902) AND ARE WORKING IN LOC NEW YORK.

ASSIGNMENT ON EMP AND MANAGER RELATION.

1) WAQTD SMITHS REPORTING MANAGER'S NAME

2) WAQTD ADAMS MANAGER'S MANAGER NAME

3) WAQTD DNAME OF JONES MANAGER

4) WAQTD MILLER'S MANAGER'S SALARY

5) WAQTD LOC OF SMITH'S MANAGER'S MANAGER.

6) WAQTD NAME OF THE EMPLOYEES REPORTING TO BLAKE

7) WAQTD NUMBER OF EMPLPOYEES REPORTING TO KING

8) WAQTD DETAILS OF THE EMPLOYEES REPORTING TO JONES

9) WAQTD ENAMES OF THE EMPLOYEES REPORTING TO BLAKE'S MANAGER

10) WAQTD NUMBER OF EMPLOYEES REPORTING TO FORD'S MANAGER