Oracle 11g DATABASE DEVELOPMENT

LAB2

Chapter- 2 These commands, which

could be issued from SQL*Plus or SQL Developer,

will make it possible to log on as users HR and OE using the passwords HR and OE:

alter user hr account unlock identified by hr;

alter user oe account unlock identified by oe;.

Now login to sqlplus using the user name and password

Hr Hr

Chapter- 2 Arithmetic expression to calculate number of

days worked.

Chapter- 2 .

Use of the concatenation and arithmetic operators

Chapter- 2

.

Use of column and expression aliases

Chapter- 2 .

Use of the AS keyword to specify column aliases

Chapter- 2 .

Null values in the Commission_Pct column

Chapter- 2 .

Null arithmetic always returns a null value.

Chapter- 2 . Question 1: It was demonstrated earlier how the number of days for which

staff were employed in a job could be calculated. For how many years were staff employed while fulfilling these job roles and what were their EMPLOYEE_ID, JOB_ID, START_DATE, and END_DATE values? Alias the expression column in your query with the alias Years Employed. Assume that a year consists of 365.25 days.

select employee_id, job_id, start_date, end_date, ((end_date-start_date) + 1)/365.25 "Years Employed" from job_history;

Chapter- 2 .

Chapter- 2 .

select 'The Job Id for the '||job_title||'''s job is: '||job_id AS "Job Description“ from jobs;

Chapter- 2 .

Character column-based WHERE clause

Chapter- 2 .Equivalence of conditional

expressions

Chapter- 2 Statement 1: select employee_id from job_history where start_date = end_date; Statement 2: select employee_id from job_history where start_date = '01-JAN-2001'; Statement 3: select employee_id from job_history where start_date = '01-JAN-01'; Statement 4: select employee_id from job_history where start_date = '01-JAN-99';.

Chapter- 2 .

Using theWHERE clausewith numericexpressions

Chapter- 2 .Conditions basedon the equalityoperator

Chapter- 2 .Conditions basedon the inequalityoperators

Chapter- 2select last_name from employees

where salary between 3400 and 4000;

select last_name from employees

where salary >= 3400

and salary <= 4000;

select first_name, hire_date from employees

where hire_date between '24-JUL-1994' and '07-JUN-1996';

select first_name, hire_date from employees

where '24-JUL-1994' between hire_date+30 and '07-JUN-1996';.

Chapter- 2select last_name from employees

where salary in (1000,4000,6000);

select last_name from employees

where salary = 1000

OR salary = 4000

OR salary = 6000;

select last_name from employees

where last_name in ('King','Garbharran','Ramklass');

select last_name from employees

where hire_date in ('01-JAN-1998','01-DEC-1999');

.

Chapter- 2select first_name from employees

where first_name like 'A;'%where first_name like;'%'

where last_name like 'King;'where last_name = 'King;'

.

Pattern Comparison with the LIKE Operator

Chapter- 2 .