ORACLE LAB 2
Using Arithmetic Operators
Syntax:
SQL> SELECT <Col-names,expression>
FROM <table name>
Example:
SQL> SELECT ename, sal, sal + 300
FROM emp;
Operator Precedence
Example:
SQL> SELECT ename, sal, 12 * sal + 300
FROM employees;
Use parenthesis in the above query
SELECT ename, sal, 12 * (sal + 300)
FROM employees;
Column Aliases using AS
select email AS mail, salary,12 * (salary + 300) AS
"Incremented Salary" from employees
Concatenation Operator ||
SELECT ename | | job AS “Employee”
FROM emp;
- Concatenates the two columns ename
and job
Literal Character String
• A literal is a character, a number,
or a date included in the SELECT
list.
• Date and character literal values
must be enclosed within single
quotation marks.
• Each character string is output
once for each row returned.
Using Literal Character String
SELECT ename || ‘ is a ’ || job
AS “Employee Details"
FROM emp;
Duplicate Rows
Example:
SQL> SELECT deptno
FROM emp;
DUPLICATE
Sample Output: VALUES
DEPTNO
------------------
???
10
30
10
20
30
.......
14 rows selected.
Eliminating Duplicate Rows
Using ‘distinct’ keyword with select
SELECT DISTINCT deptno FROM emp;
Matching Character Pattern
select * from employees where email like '%KING'
Sorting the rows – ORDER BY
• Sort rows with ORDER BY clause
• ASC : ascending order
• DESC : descending order
• The ORDER BY clause comes last in the
SELECT Statement.
Syntax for ORDER BY
Syntax:
SELECT <column name>
FROM <table name>
ORDER BY <column name>
Default sorting is in the Ascending order
Sorting in Descending order
SELECT ename, job, hiredate
FROM emp
ORDER BY hiredate DESC;
Sorting using alias name
SELECT empno, ename, sal*12 AS
annsal FROM emp ORDER BY annsal
Assignment
1. Create a query to display unique job code from employees table.
2. Name the column heading,emp#,employee,job and hire instead of
emp_id,last_name,job_id,hire_date.
3. Display column heading EMPLOYEE instead of emp_id without
usinng double quotes(");
4. Name the column heading one i,e,employee instead of display two
column heading i,e last_name and job_id1
5. Combine two column heading i,e last_name and job_id with a literal
character heading employee details.
6. How to display the output which is given below.
monthly
king: 1 month salary =24000
where king is last name,2400 is salary
MONTHLY is the column heading name and (:1 month salary+) is a
literal character string
7. Create aquery to display the last name and salary of
employees earning more than 12000.
8. Create a query to display the employees last name and
department id for emp_id 176
9. Display te last _name and salary for all employees
whose salary is not in the range of 5000,12000
10. Display the employees last_name ,job_id and hire date
of employees hired between FEB20,1998 and MAY 1,1998.
11. Display the last name and dept_no off all employees in
department 20 and 50
12. List the last_name and salary of emlployees whose
monthly salary between 5000 and 12000 and are in
department 20 or 50 .Label the column employee and monthly
instead of last name and salary
13. Display the last name and hired date of every employee
who was hired in 1994 in descending order of hired date
14. Display the last name and job title of all employees who
do not have a manager
Assignment 3
1. The commission that an employee can avail is
calculated by multiplying the commission
percentage to the salary. The report
requirement is to fetch the full name and the
commission amount of all the employees
whose first_name contain the substring ‘avi’.
If the commission amount is null, then that
record needs to be omitted. Please name the
calculated commission column header as
“commission amt”.
2. The programmer wants to
