sql

Understand the Basic structure of select statement (Revision) \& Understand the Where Clause *- Comparison conditions ( =,=, Between, In, Like, Null condition) - Logical conditions (And, OR, Not) Sorting rows using order by clause Lab Exercise: 1) List the employees in ascending order by their Salaries? 2) List the employee number, Employee name of all employees working for Manager 7698. 3) List the employees who joined before 1981 . 4) List all employees in the following form: "Employee" name "hired in" hiredate "as" job name the column header "Employee information" 5) List the emps who joined in January. 6) List the emps who joined on 1-MAY-81, 3-DEC-81, 17-DEC-81, 19-JAN-80, in ascending order of seniority. 7) List every employee where the second character of his name is " A " 8) List the employees whose Sal is four-digit number ending with Zero. 9) List the employees of Deptno 30 or 10 those who joined in 80 's 10) List the emps who joined in any year but not belongs to the month of March. 11) List the details of the employees in ascending order by the Department number and descending by Jobs 12) Display the name, job, and salary for all employees who job is CLERK or ANALYST and their salary are not equal to $1000,$3000,$5000 13) List all the employees except managers in ascending order of Salaries. deptna number (2,0), dname varchar2(14). loc varchar2(13), constraint dept_pk primary key(deptno)); Create Table Eapl empn number (4,0) primary key, empnane varchar2(10), job varchar2(9), mgr varchar2(4), hifedate date, sal number (7,2), Comm number (7,2), deptno number(2,0), Constraint emp_fk. foreign key(deptno) References Dept(deptno))] insert into Dept values (10, 'ACCOUNTING', 'NEW YORK' ); insert into Dept values (20, 'RESEARCH', 'DALLAS'); insert into Dept values ( 3, 'SALES', 'CHICAGO'); insert into Dept values(49, 'OPERATIONS', 'BOSTON'); Insert into Emp values(7839, 'KING', 'PRESIDENT', null, to date ('17-11-1961', 'dd-min-yyyy' ), 5e90, nu11, 10); insert into Emp values(7698, 'BLAKE', 'MANAGER', 7839, to date ('1-5-1981', 'dd-mit-yyyy'), 2850, nu11, 30); insert into Emp values(7782, 'CLARK', 'MANAGER', 7839, to date('9-6-1981', 'dd-mun-yyyy'), 2450, null, 10); insert into Eep values (7566, "JONES', 'MAMAGER', 7839, to date ('2-4-1981', 'dd-mm-yyyyy' ),2975, nu11, 20); Insert into Emp values(7788, 'SCOT1', 'ANALYST', 7566, to date('13-JUL-87', 'dd-mm-rr') - 85, 3060, nu11, 20); insert into Emp values(7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981", 'dd-mm-yyyy' ), 3600, nuI1, 20); insert into Emp values(7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980',' dd-mm-yyyy '), 860, nu11, 20); insert into Eep values(7499, 'ALLEN', 'SALESMAN', 7698, to_date ('20-2-1981', 'dd-mm-yyyy'', 1660 , 300, 30) ; insert into Emp values(7521, 'WARD', 'SALESMAN', 7698, to date('22-2-1981', 'dd-mim-yyyy'), 1250, 500, 30); insert into Emp values(7654, 'MARTIN', 'SALESHAN', 7698, to_date('28-9-1981', 'dd-mm-yyyy'), 1250, 1400, 30); insert into Emp volues(7844, 'TURNER', 'SALESHAN', 7698, to date ('8-9-1981', 'dd-m-ymyyy' ),1500,0,30); Insert Into Emp values(7876, "ADAY5', 'CLERK', 7788, to date( '13-Jut-87', 'dd-mm-rr') - 51, 1100, nu11, 20); Insert into Emp values(7960, "JAME5', 'CLERK', 7698, to date('3-12-1981', 'dd-mm-yyyy'), 950, nu11, 30); insert into Enp values(7934, 'MILLER', 'CLERK', 7782, to_date('23-1-1982', "dd-mm-yyyy' ),1300, nu11, 18) ; Understand the Basic structure of select statement (Revision) \& Understand the Where Clause *- Comparison conditions ( =,=, Between, In, Like, Null condition) - Logical conditions (And, OR, Not) Sorting rows using order by clause Lab Exercise: 1) List the employees in ascending order by their Salaries? 2) List the employee number, Employee name of all employees working for Manager 7698. 3) List the employees who joined before 1981 . 4) List all employees in the following form: "Employee" name "hired in" hiredate "as" job name the column header "Employee information" 5) List the emps who joined in January. 6) List the emps who joined on 1-MAY-81, 3-DEC-81, 17-DEC-81, 19-JAN-80, in ascending order of seniority. 7) List every employee where the second character of his name is " A " 8) List the employees whose Sal is four-digit number ending with Zero. 9) List the employees of Deptno 30 or 10 those who joined in 80 's 10) List the emps who joined in any year but not belongs to the month of March. 11) List the details of the employees in ascending order by the Department number and descending by Jobs 12) Display the name, job, and salary for all employees who job is CLERK or ANALYST and their salary are not equal to $1000,$3000,$5000 13) List all the employees except managers in ascending order of Salaries. deptna number (2,0), dname varchar2(14). loc varchar2(13), constraint dept_pk primary key(deptno)); Create Table Eapl empn number (4,0) primary key, empnane varchar2(10), job varchar2(9), mgr varchar2(4), hifedate date, sal number (7,2), Comm number (7,2), deptno number(2,0), Constraint emp_fk. foreign key(deptno) References Dept(deptno))] insert into Dept values (10, 'ACCOUNTING', 'NEW YORK' ); insert into Dept values (20, 'RESEARCH', 'DALLAS'); insert into Dept values ( 3, 'SALES', 'CHICAGO'); insert into Dept values(49, 'OPERATIONS', 'BOSTON'); Insert into Emp values(7839, 'KING', 'PRESIDENT', null, to date ('17-11-1961', 'dd-min-yyyy' ), 5e90, nu11, 10); insert into Emp values(7698, 'BLAKE', 'MANAGER', 7839, to date ('1-5-1981', 'dd-mit-yyyy'), 2850, nu11, 30); insert into Emp values(7782, 'CLARK', 'MANAGER', 7839, to date('9-6-1981', 'dd-mun-yyyy'), 2450, null, 10); insert into Eep values (7566, "JONES', 'MAMAGER', 7839, to date ('2-4-1981', 'dd-mm-yyyyy' ),2975, nu11, 20); Insert into Emp values(7788, 'SCOT1', 'ANALYST', 7566, to date('13-JUL-87', 'dd-mm-rr') - 85, 3060, nu11, 20); insert into Emp values(7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981", 'dd-mm-yyyy' ), 3600, nuI1, 20); insert into Emp values(7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980',' dd-mm-yyyy '), 860, nu11, 20); insert into Eep values(7499, 'ALLEN', 'SALESMAN', 7698, to_date ('20-2-1981', 'dd-mm-yyyy'', 1660 , 300, 30) ; insert into Emp values(7521, 'WARD', 'SALESMAN', 7698, to date('22-2-1981', 'dd-mim-yyyy'), 1250, 500, 30); insert into Emp values(7654, 'MARTIN', 'SALESHAN', 7698, to_date('28-9-1981', 'dd-mm-yyyy'), 1250, 1400, 30); insert into Emp volues(7844, 'TURNER', 'SALESHAN', 7698, to date ('8-9-1981', 'dd-m-ymyyy' ),1500,0,30); Insert Into Emp values(7876, "ADAY5', 'CLERK', 7788, to date( '13-Jut-87', 'dd-mm-rr') - 51, 1100, nu11, 20); Insert into Emp values(7960, "JAME5', 'CLERK', 7698, to date('3-12-1981', 'dd-mm-yyyy'), 950, nu11, 30); insert into Enp values(7934, 'MILLER', 'CLERK', 7782, to_date('23-1-1982', "dd-mm-yyyy' ),1300, nu11, 18)