Answers for Assignments in Oracle Database For Beginners Course at Udemy.com

1. SQL - Structured Query Language
2. SQL and PL/SQL
3. YES
4. Views, Indexes, Transactions

1. True
2. Composite Primary Key
3. Child
4. Attribute
5. Primary Key

1. Cloud - Oracle Database 18c was fine-tuned to run in Cloud computing
2. Pluggable Database
3. HR account
4. SYSTEM

1. XE
2. YES. And also multiple SQL Worksheets for each connection.
3. localhost
4. 1521

1. Display employees who are working in department 60

select * 
from employees 
where department_id = 60

2. Display employees whose salary is more than 5000 and have commission

select * 
from employees
where commission_pct is not null and salary > 5000

3. Display employees whose email starts with letter "D"

select * 
from employees
where email like 'D%'

4. Display employees who are doing job as IT_PROG or SA_MAN or FI_MGR

select * 
from employees
where job_id in ('IT_PROG','SA_MAN','FI_MGR')

5.Display jobs where difference between min salary and max salary is less than 5000.

select * 
from jobs
where max_salary - min_salary < 5000

6.Display jobs whose job title contains letter "p" after third char

select * 
from jobs
where job_title like '___%p%'

7.Display employees who are drawing salary between 10000 and 15000 and do not have commission 

select * 
from employees
where salary between 10000 and 15000 and commission_pct is null 

8.Display employee full name (concatenate first_name and last_name), salary, job_id if salary is more than 8000 in the descending order of salary 

select first_name || ' ' || last_name fullname, salary, job_id 
from employees
where salary > 8000 
order by salary desc

9.Display first_name, salary + commission, bonus for all employees who have commission. Calculate bonus as 30% on salary 

select first_name, salary + (salary * commission_pct) total_salary, salary * 0.30 bonus 
from employees
where commission_pct is not null

1.Update salary of employees by 20% for employees whose salary is less than 10000

update employees
set salary = salary * 1.20
where salary < 10000

2. Insert a row in jobs table with job_id as JAVA_PROG, job_title as Java Programmer, min_salary as 8000 and max_salary as 15000

insert into jobs values('JAVA_PROG','Java Programmer',8000,15000)

3.Update jobs table by increasing min_salary to 10000 and max_salary to 24000 for IT Programmer.

update jobs 
set min_salary = 10000, max_salary = 24000
where job_id = 'IT_PROG'

4. Delete all job history details of employee 130 and later rollback them

delete from job_history where employee_id = 130;

rollback;

5.Hike salary of employee 120 by 10% and change commission_pct to 0.1 only if current salary is < 8000 

update employees 
set salary = salary  * 1.1, commission_pct = 0.1
where employee_id = 120 and salary < 8000

1. Display date after adding 100 days to sysdate

select sysdate + 100
from dual

2.Display first name, salary, hire date and first Friday since hire date

select first_name, salary, hire_date, next_day(hire_date,'Fri')
from employees

3. Display first name, job_id, email and completed years of experience for all employees

select first_name, job_id, email, floor(months_between(sysdate,hire_date) / 12) exp_years
from employees

4. Display job history where employee worked as Stock Clerk for more than a year

select *
from job_history
where job_id = 'ST_CLERK' and end_date - start_date > 365

5. Display employees whose experience is more than 10 years.

select *
from employees
where months_between(sysdate,hire_date) / 12 > 10

6. Display first name, salary, ending date of training period assuming training is for 3 months from hire date, in the order of hire date.

select first_name, salary, add_months(hire_date,3) end_training
from employees
order by hire_date

7. Display salary rounded to thousands for employees whose salary is greater than 10000.

select first_name, round(salary,-3) salary
from employees
where salary > 10000

8. Display employees who joined in the last 100 days

select * 
from employees
where  sysdate - hire_date <= 100

1.Display job titles where length is more than 10 and start with letter S

select job_title
from jobs
where length(job_title) > 10 and job_title like 'S%'

2.Display first word of job_title for all jobs

/* space is added to ensure we always find space even for job title that do no have spaces */
select substr(job_title,1,instr(job_title || ' ',' ')) first_word 
from jobs

3.Display employees whose first_name or last_name contains letter S in lower or upper case

select *
from employees
where  upper(first_name || last_name) like '%S%'

4.Display department names in uppercase and pad it on right with dots up to 15 chars

select rpad(department_name,15,'.')
from departments

5. Display second word onwards in job title

select substr(job_title, instr(job_title,' ') + 1) 
from jobs
where  job_title like '% %'

6. Display phone_number after removing all periods(.) 

/* We need to give some source and target chars for translation, so 1 to 1 and . to nothing to effectively
   remove all periods */
select first_name, phone_number, translate(phone_number,'1.','1') new_phone_number
from employees

1. Display employees who joined in year 2007 

select *
from employees
where to_char(hire_date,'yyyy') = 2007

2. Display city, street address, state province from locations. If state province is null, display as 'Not Available'. 

select city,street_address, nvl(state_province,'Not Available')
from locations

3. Display time of system in 24 hour format  

select to_char(sysdate,'hh24:mi:ss')
from dual

4. Display number of days between current date and '09-10-2018'. Round the result.

select round(sysdate - to_date('09-10-2018','dd-mm-yyyy'))
from dual

5. Display first_name, salary, bonus – 10% if employee is eligible for commission otherwise 20%.

select first_name, salary, nvl2(commission_pct,salary * 0.10, salary * 0.20) bonus
from employees

1. Create a table PAYMENTS with invoice number, admission number, pay date and amount paid. 
    Make admission number a foreign key referencing STUDENTS table. 
    Make sure amount is > 0.

create table payments
( 
   invno number(5) constraint payments_pk primary key,
   admno  number(5) constraint payments_admno_fk references students(admno),
   paidon date,
   amount number(5) constraint payments_amount_check check(amount > 0)
)

2. Create STUDENTS_LOG table with the following structure.
id        - number – primary key, 
admno     - number
cmd       - char(1) - must be i,u or d
exdate    - date

create table students_log
( 
   id  number(5) constraint students_log_pk primary key,
   admno  number(5),
   cmd  char(1) constraint students_log_cmd_check check(cmd in ('i','u','d')),
   exdate date
)

1. Add column REMARKS to PAYMENTS table.

alter table payments add(remarks varchar2(50))

2. Add a constraint to PAYMENTS table that ensures REMARKS is at least 5 characters.

alter table payments modify
     (remarks varchar2(50) constraint payments_remarks_check check (length(remarks) >= 5))

3. Drop STUDENTS_LOG table permanently.

drop table students_log purge

1. Display job_id and total salary for each job.

select job_id, sum(salary)
from employees
group by job_id

2. Display department_id and average salary of departments where total salary is greater than 20000.

select department_id, avg(salary)
from employees
group by department_id
having sum(salary) > 20000

3. Display number of employees with salary more than 8000 in each department 

select department_id, count(*)
from employees
where salary > 8000
group by department_id

4. Display region and number of countries in each region in the order of count.

select region_id, count(*)
from countries
group by region_id
order by 2

5. Display job_id and number of employees who did that job in the past for more than 100 days 

select job_id, count(*)
from job_history
where end_date - start_date > 100
group by job_id

1. Display name, job title, salary of employees whose salary is greater than 10000 

select first_name,job_title,salary
from employees natural join jobs
where salary > 10000

2.Display department name, average salary of employees when average salary is greater than 10000 

select department_name, avg(salary)
from employees join departments using(department_id)
group by department_name
having avg(salary) > 10000

3. Display department name, city and country name for each department

select department_name, city, country_name
from departments natural join locations natural join countries

4. Display details of employees who are living in city 'Oxford'

select first_name, salary, department_id, job_id
from employees join departments using(department_id)
join locations using(location_id)
where city = 'Oxford'

5. Display first_name, job_title, start_date and end_date

select first_name, job_title, start_date, end_date
from employees join job_history jh using(employee_id) 
join jobs j on (jh.job_id = j.job_id)

6. Display number of employees and average salary of employees in department 80

select count(*), avg(salary)
from employees
where department_id = 80

7. Display job_title and average number of days employees did that job in the past

select job_title, trunc(avg(end_date - start_date))
from jobs natural join job_history
group by job_title

1. Display details of employees who did a job in the past

select * 
from employees
where employee_id in
    (select employee_id from job_history)

2. Display name and salary of employees whose salary is more than average salary of their department

select first_name, salary 
from employees outer
where salary >
   (select avg(salary)
    from employees
    where department_id = outer.department_id)

3. Display details of departments that have no employees.

select * 
from departments outer
where not exists
   (select employee_id
    from employees
    where department_id = outer.department_id )

4. Display first_name and salary of employees whose salary is greater than the salary of employee whose id is 110

select first_name, salary
from employees
where salary >
   (select salary
    from employees
    where employee_id = 110)

5. Display employees whose salary is less than the salary of all employees in department 100

select *
from employees
where salary <
   (select min(salary)
    from employees
    where department_id = 100)

6. Display employees who are not working and never worked in department 80

select *
from employees
where employee_id not in
   (select employee_id 
    from employees
    where department_id = 80)
  and employee_id not in
   (select employee_id 
    from job_history
    where department_id = 80)

7. Display country in which employee 120 is working.

select country_name
from countries
where country_id in
   (select country_id
    from locations
    where location_id = 
      (select location_id 
       from departments
       where department_id = 
         (select department_id 
          from employees 
          where employee_id = 120)
      )
    )

8. Change salary of employee 120 to maximum salary of department 60.

update employees
set salary = 
      (select max(salary)
       from employees
       where department_id = 60)
where employee_id = 120

1. Create EMP_DETAILS view with employee name, salary, joining month, joining year, job title and department name.

create view emp_details as
  select first_name, salary, to_char(hire_date,'mm') join_month, to_char(hire_date,'yyyy') join_year,
    job_title, department_name
  from employees join jobs using(job_id) join departments using(department_id)

2. Create EXP_EMPLOYEES view to represent employees with experience more than 15 years

create view exp_employees as
  select *
  from employees
  where months_between(sysdate,hire_date) > 180

3. Create IT_JOB_HISTORY to get first_name, start_date, end_date for all IT_PROG jobs done in the past

create view IT_JOB_HISTORY as
  select first_name, start_date, end_date
  from employees join job_history using (employee_id)
  where job_history.job_id = 'IT_PROG'

4. Update EXP_EMPLOYEES view to increase salary of employees by 10 percent

update exp_employees
    set salary = salary * 1.1

5. Recreate EXP_EMPLOYEES using WITH CHECK OPTION and try to update EXP_EMPLOYEES in such a way that the change will violate condition.

CREATE OR REPLACE view exp_employees as
  select *
  from employees
  where months_between(sysdate,hire_date) > 180
  with check option;

update exp_employees set hire_date = sysdate 
where employee_id = 115;

6. Create a materialized view called JOB_SUMMARY with job title, total salary, number of employees and average salary in the order of job title

create materialized view job_summary
as
select job_title, sum(salary), count(*), avg(salary)
from jobs natural join employees 
group by job_title
order by 1

1. Index
2. YES
3. RECYCLE
4. FALSE
5. TRUE
6. TRUE

1. EXECUTE
2. TRUE
3. Privileges
4. FALSE
5. TRUE
6. TRUE

1. Display list of city names that do not start with vowels 

 select city
 from locations
 where regexp_like(city,'^[^AEIOU]')

2. Display details of employees whose name ends with two or more letters in the list a, b, c, d and e  

select *
from employees
where regexp_like (first_name, '[abcde]{2,}$')

3. Display details of locations where street address does not start with digits but contains a digit.

select *
from locations
where regexp_like(street_address, '^[^0-9]') and regexp_like(street_address, '[0-9]')

4. Display number of times a digit is present in column postal_code.

select postal_code, regexp_count(postal_code,'[[:digit:]]')
from locations

5. Display all digits up to first period (.) and all digits after last period (.)

select phone_number, regexp_substr(phone_number, '[0-9]+'),regexp_substr(phone_number, '[0-9]+$')
from employees

1. Which part of PL/SQL block is mandatory?

Execution Block

2. Which variable is used to enable server output?

SERVEROUTPUT

3. Write a PL/SQL program to display highest salary in departments 60 and 80.

set serveroutput on

declare
  v_max_60 employees.salary%type;
  v_max_80 employees.salary%type;
begin
  select max(salary) into v_max_60
  from employees
  where department_id = 60;
  
  select max(salary) into v_max_80
  from employees
  where department_id = 80;
 
  dbms_output.put_line('Max salary of department 60  :' || v_max_60);
  dbms_output.put_line('Max salary of department 80  :' || v_max_80);
end;

4. Display how many employees worked as Programmer and how many are currently working.

set serveroutput on

declare
  v_job_id  jobs.job_id%type;
  v_hist_count number(3);
  v_curr_count number(3);
begin
  -- Get job id for Programmer
  select job_id into v_job_id
  from jobs
  where job_title = 'Programmer';
  -- Get history count
  select count(*) into v_hist_count
  from job_history
  where job_id = v_job_id;
  -- Get current employees count 
  select count(*) into v_curr_count
  from employees
  where job_id = v_job_id;
 
  dbms_output.put_line('Number of employees worked as Programmer   :' || v_hist_count);
  dbms_output.put_line('Number of employees working as Programmer  :' || v_curr_count);
end;

1. Write a PL/SQL program to hike salary of employee 111 by 10% if experience is more than 15 years otherwise 5%.

set serveroutput on

declare
  v_exp  number(2);
begin
  -- Get experience in years   
  select trunc(months_between(sysdate,hire_date) / 12)  into v_exp
  from employees
  where employee_id = 111;
  -- Hike salary based on experience 
  if v_exp > 15 then
     update employees set salary = salary * 1.10
     where employee_id = 111;
  else
     update employees set salary = salary * 1.05
     where employee_id = 111;
  end if;
end;

2. Based on the salary of employee 125, display grade as follows:
Salary < 5000   : Grade A
Salary < 10000  : Grade B
Salary >= 10000 : Grade C

set serveroutput on
declare
  v_salary employees.salary%type;
begin
  -- Get salary for employee 125
  select salary into v_salary
  from employees
  where employee_id = 125;
  -- Display grade based on salary 
  if v_salary < 5000 then
      dbms_output.put_line('Grade A');
  elsif v_salary < 10000 then
      dbms_output.put_line('Grade B');
  else
      dbms_output.put_line('Grade C');
  end if;
end;

1. Display how many employees joined in years from 2001 to 2010

set serveroutput on

declare
  v_count number(3);
begin
  for year in 2001..2010
  loop
      select count(*) into v_count
      from employees
      where to_char(hire_date,'yyyy') = year;
      
      dbms_output.put_line( year ||  ' - ' || to_char(v_count,'99'));
  end loop;
end;

2. Display how many employees are there in each range of salaries. 
First range is 1 to 2000, second range is 2001 to 4000 and so on up to 26000.

set serveroutput on

declare
  v_count number(3);
  v_salary number(5);
begin
  v_salary  := 1;
  dbms_output.put_line('Count of Employees');
  
  while v_salary < 26000
  loop
      select count(*) into v_count
      from employees
      where salary between v_salary and v_salary + 1999;
      
      dbms_output.put_line(to_char(v_salary,'99999')  ||  ' - ' || to_char(v_salary + 1999,'99999') || ' : ' || to_char(v_count,'99'));
      v_salary := v_salary + 2000;
  end loop;
end;

1. Update job of employee 150 to 'IT_DBA'. Handle all possible exceptions.

set serveroutput on
begin
  update employees
  set job_id = 'IT_DBA'
  where employee_id = 150;
  commit;
exception
  when others then
    dbms_output.put_line('Error : ' || sqlerrm);
end;

2. Update salary of employee 120 to salary of employee 130. If employee 130 is not found then take average salary of all employees.

set serveroutput on
declare
  v_salary employees.salary%type;
begin
  select salary into v_salary
  from employees
  where employee_id = 130;
  
  update employees
  set salary = v_salary
  where employee_id = 120;
exception
  when no_data_found then
    select avg(salary) into v_salary
    from employees;
  
    update employees
    set salary = v_salary
    where employee_id = 120;
end;

1. Display names of employees for department 60 separated by comma(,).

set serveroutput on
declare
  cursor empcur is
   select first_name 
   from employees
   where department_id = 60;
   v_first boolean := true; 
begin
   for emprec in empcur
   loop
      if not v_first then  -- print comma(,) from second name onwards 
         dbms_output.put(',');  
      else
         v_first := false;
      end if;
      dbms_output.put(emprec.first_name);
   end loop;
   dbms_output.put_line(' ');
end;

2. Display top 3 most highly paid employees of each department

set serveroutput on
declare
  cursor deptcur is
   select department_id, department_name
   from departments outer
   where exists ( select department_id from employees where department_id = outer.department_id);
   
  cursor empcur (p_dept number) is
   select first_name, salary
   from employees
   where department_id = p_dept
   order by salary desc;
begin
  for deptrec in deptcur
  loop
   dbms_output.put_line(deptrec.department_name);
   for emprec in empcur(deptrec.department_id)
   loop
      dbms_output.put_line('  ' || rpad(emprec.first_name,15) || ' - ' || emprec.salary);
      exit when empcur%rowcount = 3;
   end loop;
  end loop;
end;

3. Hike salary of employees in department 30 by 20% if they have done more than 1 job in the past otherwise by 10%

set serveroutput on
declare
  cursor empcur is
   select employee_id
   from employees  
   where employees.department_id = 30;
   v_count number(2);
begin
   for emprec in empcur
   loop
      select count(*) into v_count
      from job_history
      where employee_id = emprec.employee_id;
      
      if v_count > 1 then
         update employees 
         set salary = salary * 1.2
         where employee_id = emprec.employee_id;
      else
         update employees 
         set salary = salary * 1.1
         where employee_id = emprec.employee_id;
      end if;
  end loop;
end;

1. Create a function that returns the manager's name for the given department.

create or replace function get_manager_name(p_deptid number)
return varchar2 is
  v_firstname employees.first_name%type;
begin
  select first_name into v_firstname
  from employees
  where employee_id = 
     (select manager_id 
      from departments
      where department_id = p_deptid);
      
  return v_firstname;
end;

2. Create a function that returns a string that contains names of employees, separated by comma(,) for the given JOB. 

create or replace function get_employees_by_job(p_jobid varchar)
return varchar2 is
   cursor empcur is
   select first_name 
   from employees
   where job_id = p_jobid;
   v_names varchar2(1000) := '';
begin
   for emprec in empcur
   loop
        v_names := v_names || emprec.first_name || ',';
   end loop;
   return rtrim(v_names,',');  -- send after removing comma at the end
end;

3. Create a procedure that deletes given job if there is no dependent records, otherwise raise application error 

create or replace procedure delete_job(p_jobid varchar) is
   v_emp_count number(5);
   v_jh_count  number(5);
begin
  select count(*) into v_emp_count
  from employees
  where job_id = p_jobid;
  
  select count(*) into v_jh_count
  from job_history
  where job_id = p_jobid;
  
  if v_emp_count = 0 and v_jh_count = 0 then
     delete from jobs
     where job_id = p_jobid;
  else
     raise_application_error(-20555,'Cannot delete job when it has dependents');
  end if;
end;

1. Create a procedure that takes employee id and returns current job and most recently done job (if any) by the employee through out parameters.

create or replace procedure get_emp_jobs(p_empid number, p_cur_job  out varchar2, p_prev_job out varchar2) is
begin
  p_cur_job := 'Unknown';
  p_prev_job := 'Unknown';
  
  select job_id into p_cur_job
  from employees
  where employee_id = p_empid;
  
  select job_id into p_prev_job
  from job_history
  where employee_id = p_empid
    and start_date = 
       (select max(start_date)
        from job_history
        where employee_id = p_empid);
exception
  when others then
     null;    -- ignore errors
end;

/* call above procedure as follows */

declare
  v_j1 varchar2(20);
  v_j2 varchar2(30);
begin
  get_emp_jobs(111,v_j1,v_j2);
  dbms_output.put_line( v_j1 || '  ' || v_j2);
end;

2. Create a package that has the following members related to job:
Function get_emp_count(jobid)
Function get_history_count(jobid)
Procedure change_min_salary(jobid, new_min_sal)

create or replace package jobs_pkg is
   function get_emp_count(p_jobid varchar) return number;
   function get_history_count(p_jobid varchar) return number;
   procedure change_min_salary(p_jobid varchar,p_minsal number);
end;

create or replace package body jobs_pkg is
   function get_emp_count(p_jobid varchar) return number is
     v_count number(5);
   begin
       select count(*) into v_count
       from employees
       where job_id = p_jobid;
       
       return v_count;
   end;
   
   function get_history_count(p_jobid varchar) return number is
     v_count number(5);
   begin
       select count(*) into v_count
       from job_history
       where job_id = p_jobid;
       
       return v_count;
   end;
   
   procedure change_min_salary(p_jobid varchar,p_minsal number) is
   begin
      update jobs 
        set min_salary = p_minsal
      where job_id = p_jobid;
   end;
end;

/* call members of package */

set serveroutput on
begin
  dbms_output.put_line( jobs_pkg.get_emp_count('IT_PROG'));
end;

1. Create a trigger to ensure manager_id is not same as employee_id in EMPLOYEES table 

create or replace trigger trg_manager_id_emp_id_check
before insert or update of employee_id, manager_id
on employees
for each row
begin
   if :new.employee_id =  :new.manager_id then
      raise_application_error(-20600,'Manager id and employee id cannot be same');
   end if;
end;

2. Create a trigger that prevents any insertion into JOB_HISTORY table for departments that do not have any employees.

create or replace trigger trg_job_history_insert_check
before insert 
on job_history
for each row
declare
  v_count number(3);
begin
   -- check whether department used in insert has any employees
   select count(*) into v_count
   from employees
   where department_id = :new.department_id;
   
   if v_count  = 0 then
     raise_application_error(-20222,'Department used in insertion has no employees!');
   end if;
end;

1. Create a trigger that writes all changes to SALARY of EMPLOYEES table into SALARY_LOG with employee_id, old salary, new salary and date of change. 

create table salary_log
( employee_id number(5),
  old_salary number(5),
  new_salary number(5),
  changedon  date
);

create or replace trigger trg_salary_log
before update of salary
on employees
for each row
begin
   insert into salary_log values (:new.employee_id, :old.salary, :new.salary, sysdate);
end;

/* update salary to fire the trigger */
update employees set salary = 6000 where employee_id = 140

2. Create a trigger on view EXP_EMPLOYEES to prevent any changes to base table through view. 
However log changes to EMPLOYEES_LOG. View EXP_EMPLOYEES was created earlier. 
Create table EMPLOYEES_LOG with employee_id, operation (u/i/d) and date.

/* Create EMPLOYEE_LOG table */
create table employees_log 
( employee_id number(5),
  operation  char(1),
  transdate  date
);

/* create trigger */

create or replace trigger trg_prevent_changes_to_exp_employees
instead of insert or update or delete
on exp_employees
for each row
begin
   if inserting then
       insert into employees_log values(:new.employee_id,'i',sysdate);
   elsif deleting then
       insert into employees_log values(:old.employee_id,'d',sysdate);
   else
       insert into employees_log values(:new.employee_id,'u',sysdate);
   end if;
end;

1. Create a procedure that takes table name and column name and drops that column.

create or replace procedure drop_column(table_name varchar2, column_name varchar2) is
begin
   execute immediate 'alter table '  ||  table_name || ' drop column ' || column_name;
end;

/* Calling drop_column procedure */
begin
   drop_column('salary_log','changedon');
end;

2. Create a function that returns list of values separated by colon(:) for the given table name and column name.

create or replace function get_column_values(table_name varchar2, column_name varchar2)
 return varchar2 is
 type values_cursor_type is ref cursor; -- define weak ref cursor    
 values_cursor values_cursor_type;      -- declare cursor variable
 v_value varchar2(100);
 v_values varchar2(1000) := '';
 begin 
  open values_cursor FOR 
    'select ' || column_name || ' from ' || table_name;
  loop
   fetch values_cursor into v_value;
   exit when values_cursor%notfound; -- exit at the end
   v_values := v_values  || v_value || ':';   -- concatenate value with colon(:)
  end loop; 
  close values_cursor;
  return rtrim(v_values,':');  -- remove last : 
end;

/* calling function */
begin
   dbms_output.put_line( get_column_values('jobs','job_title'));
end;