PART 1
1. Display first name ,last name and salary from s_emp where salary should display in the format $99,999;
2. Write query to display first name and last name as full name, salary *12+ commission_pct *salary as Total Salary from s_emp.If commission_pct *salary is null then consider it as zero use nvl function;
3. select to_char(sysdate,'YYYY') C1,to_char(sysdate,'YEAR') C2,to_char(sysdate,'MM') C3,to_char(sysdate,'DY') C4,to_char(sysdate,'DAY') C5 from dual;
4. write query to display employee name ,job, salary and revised salary from s_emp
If job is ANALYST then revised salary = salary*1.03
If job is MANAGER then revised salary = salary*1.1
If job is PRESIDENT then revised salary = salary*1.01
Else display revised salary = salary
(Use decode function)
5. display first name , last name as full name ,title , salary and revised salary from scott.s_emp table
If title is 'Sales Representative' then make revised salary = salary*1.03
If 'VP, Administration' then make revised salary = salary*1.02
If 'Warehouse Manager' then make revised salary = salary*1.1
If 'Stock Clerk' then make revised salary = salary*1.01
Else display revised salary = salary*1.05
(Use case function)
6. Display word "gOwoLFpack" in 3 ways by using Initcap , upper and lower functions;
7. select substr('ABCDEFG',0) S1,substr('ABCDEFG',1) S2,substr('ABCDEFG',4) S3,substr('ABCDEFG',4,3) S4,substr('ABCDEFG',-5) S5,substr('ABCDEFG',-5,4) S6,substr('ABCDEFG',-5,10) S7,substr('ABCDEFG',-5,-1) S8 from dual;
8. Display salary and if salary is null display commission and if both are null display 0 using coalesce function from s_emp ;
PART 2
1. Display Firstname , Lastname together with Alias as Fullname from student table.
2. Display employee name , commission and salary+10*commission as Total from scott.emp
3. Select unique deptno with job from scott.emp.
4. Select all the columns from scott.emp where sal is greater than equal to 1500
5. select only employee name from scott.emp whose employee number is less than 7782;
6. Select employee name from scott.emp where employee number is not equal to 7782;
7. Select employee name from scott.emp whose hiredate is 17th dec 1980.
8.select ename,sal,comm from scott.emp where 1=2;
9. select ename,sal,comm from scott.emp where 2=2;
10. select employee name , salary , commission from scott.emp where employee number is greater than 0
11. select ename,sal,comm from scott.emp where comm=comm;
12. Select employee name , salary and commission from scott.emp whose salary is less than equal to commission
13. By using IN operator in the where condition , display employee name , salary and job from scott.emp where jobs are in 'CLERK','SALESMAN','ANALYST'
14. By using between in where condition display employee name , salary from scott.emp where salary is between 1000 and 1500
15. Display employeenumber , employee name from scott.emp where employee name starts with letter M
16. Display employee name , salary , job from scott.emp where job is SALESMAN or PRESIDENT and salary is greater than 1500
Attachment:- New WinRAR archive.rar