Answers to Exercises in Oracle Database 12c for beginners
- Srikanth Pragada

Chapter 1: INTRODUCTION TO DBMS

  1. ________ designed relational model

    Dr. E.F. CODD

  2. Data models are _______, _______, _________ and ________.

    Hierarchical, Network, Relational, Object-oriented

  3. Composite primary key is ___________.

    A primary key that is consisting of multiple columns

  4. A row is otherwise known as ________.

    Tuple

  5. How many tables does SELECT operator take?

    One

  6. ______ is an example for an RDBMS.

    Oracle Database

  7. SQL command used to create table belongs to ______ category of SQL Commands.

    DDL

  8. _______key is used to join a child table with parent table.

    Foreign

  9. ______ is the standard language for RDBMS.

    SQL

  10. What is a domain?

    A set of potential values

Chapter 2: INTRODUCTION TO ORACLE

  1. Oracle instance is a collection of _________ and ____________.

    Memory structures and Processes

  2. What is the use of Host String?

    Host string is used to connect to server from clients like Sql*plus

  3. SQL*PLUS commands must be terminated with semicolon (;) [TRUE/FALSE]

    FALSE

  4. What is the password of user SYSTEM?

    Whatever is given at the time of installation

  5. _____________ is where we enter SQL commands in SQL Developer.

    Worksheet

Chapter 3: GETTING STARTED WITH ORACLE DATABASE 12C

  1. ______ is the operator used to compare a column with null value.

    IS NULL

  2. ______ operator is used to compare one value with a set of values.

    IN

  3. The maximum number of characters that can be stored in CHAR type is ______.

    2000

  4. In LIKE operator, % stands for _____________.

    Zero or more characters of any kind

  5. ______ is used to change the heading of a column.

    Alias

  6. ______ command is used to display definition of a table.

    DESCRIBE

  7. Display list of courses where course code starts with letter ‘c’.

    select * from courses where code like 'c%'
  8. Display rows of COURSES table in the ascending order of course fee and descending order of course code.

    select * from courses order by fee, code desc 
  9. Select rows from COURSES where course fee is in the range 3000 to 5000.

    select * from courses where fee between 3000 and 5000 
  10. Add a new row to COURSES table with the following data - course code - cpp, name – C++ Programming, duration – 30, fee – 3500, prereq – C programming.

    insert into courses values('cpp','C++ Programming',30,3500,'C Programming') 
  11. Display all the rows where course fee is not known but duration is known.

    select * from courses where fee is null and duration is not null 

Chapter 4: CREATING SAMPLE TABLES

  1. __________ constraint can be used to implement business rules.

    CHECK

  2. __________ option of REFERENCES constraint is used to delete all child rows when parent row is being deleted.

    DELETE CASCADE

  3. Data dictionary view used to get information about constraints is _____________.

    USER_CONSTRAINTS

  4. When a table has a composite primary key, where is the PRIMARY KEY constraint defined?

    At table level using table constraint

  5. What is the relationship between COURSES and COURSE_FACULTY table?

    to Many

  6. How do you get details of all CHECK constraints of all tables?

    select * from user_constraints where constraint_type = 'C'
  7. Is it possible to create a constraint to prevent a date that is less than the system date?

    No. SYSDATE cannot be used in CHECK constraint

Chapter 5: CHANGING STRUCTURE AND DATA

  1. How do you add a check constraint to an existing column?

    Using ALTER TABLE command with ADD option

  2. How do you drop a constraint?

    Using constraint name in ALTER TABLE command with DROP option

  3. Is it possible to know the name of the constraint? If yes, how?

    Yes. We can use USER_CONSTRAINTS table to get details of all constraints.

  4. How do you give primary key constraint if two or more columns are part of the primary key?

    We have to use PRIMARY KEY constraint at table level and mention columns that make up primary key.

  5. _________ command is used to mark a location in a transaction.

    SAVEPOINT

  6. What is ROLLBACK/UNDO segment and how is it used?

    ROLLBACK segment is used to store before image for changes. It is used to get back old data when changes are rolled back. It is also used to provide read consistency and enable flashback queries.

  7. When does a transaction begin and when does it end?

    A transaction begins when a session starts or when another transaction ends. It ends either with COMMMIT or ROLLBACK.

  8. If a row that is to be updated is already locked then what happens?

    The program which is trying to change a row that is locked will wait for row to be unlocked indefinitely.

  9. What happens if a row is updated and not committed or rolled back?

    Row might be committed if any event that causes auto commit takes place. It might also be rolled back, if any event causes Oracle to roll back changes.

  10. What is read consistency?

    Read consistency is where data provided by Oracle regarding a query is consistent to the point of starting query. That means, no changes made after query started will be provided.

Chapter 6: ARITHMETIC AND DATE FUNCTIONS

  1. ______ function can be used to subtract months from a date.

    ADD_MONTHS() with negative number as second parameter will subtract that many months from the given date

  2. The return value of ROUND (2323.343, 2) is _________.

    2323.34

  3. To get the remainder of a division_____ function is used.

    MOD

  4. In date arithmetic __________, _______________ and ___________ operations are allowed.

    Adding a number, Subtracting a number, Subtracting a date

  5. ____________ is the result of LAST_DAY (SYSDATE) assuming SYSDATE is 24th August.

    31-AUG

  6. Which function can be used to set time portion of the DATE data type to 00:00:00, without effecting date portion?

    TRUNC(date)

  7. Display details of students who have joined in the last 4 months.

    select * from students where months_between(sysdate, dj) < 4 
  8. Display ADMNO, NAME, DJ and number of days between current date and DJ for each student.

    select admno, name, dj, sysdate - dj nodays from students
  9. Display the first Sunday since batch with code b2 started.

    select next_day(stdate,'Sun') from batches where code = 'b2'
  10. Display details of batches that started three or more months back.

    select * from batches where months_between(sysdate,stdate) >= 3
  11. Display the details of payments of last Monday.

    select * from payments where trunc(dp) = trunc( next_day(sysdate - 8, 'Mon'))
  12. __________ is the function to get number of years between two dates.

    MONTHS_BETWEEN() / 12 will give number of years

Chapter 7: STRING, CONVERSION AND MISCELLANEOUS FUNCTIONS

  1. ______ function performs one to one character substitution.

    TRANSLATE

  2. ______ format option is used to get complete year spelled out in TO_CHAR function.

    select to_char(sysdate,'year') from dual 
  3. _____ symbol is used to concatenate strings.

    ||

  4. What happens if ‘replace string’ is not given for REPLACE function?

    It replaces source with nothing, effectively removing occurrences of source from string

  5. Can a NUMBER be converted to DATE? [Yes/No] _____.

    No

  6. How do you change the name of each student to uppercase in STUDENTS table?

    update students set name = upper(name)

  7. Display the names of the students who have more than 15 characters in the name.

    select name from students where length(name) > 15 
  8. Display students’ firstname second and lastname first. For example, Luis Figo should be displayed as Figo Luis.

    select name,substr(name, instr(name,' ')), substr(name,1, instr(name,' '))
                from students 
  9. Display the details of the students who have more than 10 characters in the firstname.

    select * from students where  instr(name, ' ') > 10 
  10. What is the result of AMOUNT – DISCOUNT if column DISCOUNT is null?

    NULL. Any expression that contains a null returns null.

  11. How do you get the position of 5th occurrence of letter ‘o’ in student’s name?

    Use INSTR(name,'o',1,5)

  12. What will be the result of select ‘10’ * ‘20’ from dual?

    200 as Oracle automatically converts both strings to numbers

Chapter 8: GROUPING DATA

  1. _______ clause is used to select groups based on condition.

    HAVING

  2. Select count(*) from students; Is it a valid query?

    Yes. It returns number of rows in STUDENTS table

  3. What is the correct order of GROUP BY, ORDER BY and WHERE clauses in SELECT?

    WHERE, GROUP BY and ORDER BY

  4. Display ADMNO of students who have paid more than twice.

    select admno from payments group by admno having count(*) > 2  
  5. Display average time (in days) taken to complete Oracle course.

    select avg(enddate - stdate) from batches where enddate is not null and course_code = 'ora' 
  6. Display faculty who can take more than 2 courses.

    select faculty_code from course_faculty group by faculty_code having count(*) > 2 
  7. Display least course fee.

    select min(fee) from courses 
  8. Display the number of months between first and last batches of course Java SE.

    select months_between( max(stdate), min(stdate)) from batches where course_code = 'jse' 
  9. Display year, faculty and number of batches taken by faculty.

    select  to_char(stdate,'yyyy'), faculty_code, count(*) from batches group by to_char(stdate,'yyyy'), faculty_code order by 1,2  
  10. Display the number of students joined in each month.

    select to_char(dj,'mm-yyyy'), count(*)
    from students
    group by to_char(dj,'mm-yyyy')
    order by 1
    
  11. Display the number of students joined in each month of the current year.

    select to_char(dj,'mm-yyyy'), count(*)
    from students
    where to_char(dj,'yyyy') = to_char(sysdate,'yyyy')
    group by to_char(dj,'mm-yyyy')
    order by 1
            

Chapter 9: JOINING TABLES

  1. What is required to join two tables?

    A common column is required to join two tables

  2. What is meant by self-join?

    Self-join is where a table is joined to itself

  3. How do you qualify a column that is existing in two or more tables that are being joined?

    We qualify a column by using tablename.columnname. Ex: students.admno

  4. What is table alias? Is it stored anywhere?

    A table alias is what we use to shorten reference to table in query. It is not stored anywhere.

  5. What happens when you join two tables without any condition?

    Each row in first table is joined with each row of second table thus resulting in product of tables also called as Cartesian product

  6. Display admno, student name, pay date and amount paid.

    select admno, name, dp, amount
    from payments natural join students
    
  7. Display admno, student name, batch code, stdate of batch and faculty name.

    select admno, s.name student, batch_code, stdate, f.name faculty
    from  students s join batches b on ( s.batch_code  = b.code)
    join faculty f on (f.code = b.faculty_code)
    
  8. Display admno, student name, course name, stdate of batch and faculty code.

    select admno, s.name student, c.name Course, stdate, faculty_code
    from  students s join batches b on ( s.batch_code  = b.code)
    join courses c on (c.code = b.course_code)
    order by 1 
  9. Display student name, course name, faculty code and enddate of all batches that were completed.

    select s.name student, c.name course, faculty_code, enddate
    from students s join batches b on (s.batch_code = b.code)
    join courses c on ( c.code = b.course_code)
    where enddate is not null 
  10. Display students who have more number of characters in name than the student with admno 10.

    select s1.*
    from students s1 join students s2
    on ( length(s1.name) > length(s2.name) )
    where s2.admno = 10 
  11. Display admno, student name, email, pay date and amount paid.

    select admno, name, email, dp, amount
    from students join payments using(admno) 
  12. In previous query include the details of students who haven’t paid anything so far.

    select admno, name, email, dp, amount
    from students left outer  join payments using(admno) 
  13. Display the details of students who haven’t paid any amount so far.

    select admno, name, batch_code, dj, phone, email
    from students s left outer  join payments using(admno)
    where payments.amount is null 

Chapter 10: SUBQUERIES

  1. A correlated subquery is executed for ______ number of times.

    N number of times where N is number of rows we have in main query

  2. Subquery nesting can be up to ______ levels.

    16

  3. What is the result of x > ANY (10,20), if x is 15? ________.

    TRUE

  4. Subquery always passes the result to the main-query [T/F] _____.

    TRUE

  5. Subquery can be used in VALUES clause of INSERT command [T/F] ____.

    TRUE

  6. Display details of courses taken by students who joined in the month of June 2015.

    select * from courses
    where code in
     (select course_code from batches  where code  in
      (select batch_code from students where to_char(dj,'mm-yyyy') = '06-2015')
     ) 
  7. Delete the details of students who haven’t paid anything so far.

    delete from students
    where not exists
     (select 1 from payments
      where admno = students.admno) 
  8. Display the details of courses for which there are more than 3 batches.

    select * from courses
    where code in
      (select course_code
       from batches
       group by course_code
       having count(*) > 3) 
  9. Display the details of course that has highest number of batches.

    select * from courses  where code in
     (select course_code
      from batches group by  course_code
      having count(*) =
        (select max(count(*)) from batches
         group by course_code)
     )
    
  10. Change the ENDDATE of batch B8 to the ENDDATE of most recently ended batch.

    update batches set enddate = (select max(enddate) from batches)
    where code = 'b8'  
  11. Display the details of students who haven’t paid total amount so far.

    select * from students
    where admno in
      (select admno
       from  payments
        group by admno
         having sum(amount) <
         (select fee from courses c join batches b on (b.course_code = c.code)
         and b.code  = students.batch_code)
      )
    
  12. Display the details of payments made by students of Oracle batch started on 5-May-2015.

    select * from payments
    where admno in
      (select admno
        from students where batch_code in
         (select code from batches
         where stdate = '15-may-2015' and course_code ='ora')
      )
    

Chapter 11: VIEWS

  1. What are the major applications of a view?

    Controlling Access, Query Simplification, Data Independence, and Presenting data in different forms

  2. A view can be used with ALTER TABLE command [T/F] ?_______ .

    FALSE

  3. The table on which a view is based is called as _____.

    Base Table

  4. When a table is dropped then all the views based on it will be dropped automatically [T/F]? ______.

    Views based on table being dropped are marked invalid, but not dropped.

  5. A view can be used to manipulate base table when it follows certain rules [T/F]? _____.

    TRUE
  6. Create a view, which contains the course name and number of students who have taken that course so far.

    create view course_students
    as
    select c.name, count(*) nostudents
    from students s join batches b on (b.code = s.batch_code) join courses c on (c.code = b.course_code)
    group by c.name; 
  7. Create a view to provide the following: batch code, course name, faculty name, stdate, enddate and no. of days between enddate and stdate for all completed batches.

    create view completed_batches
    as
    select  b.code batchcode, c.name CourseName, f.name FacultyName, stdate, enddate, enddate - stdate nodays
    from  batches b join courses c on (b.course_code = c.code) join faculty f on (f.code = b.faculty_code)
    where enddate is not null 
  8. Create a view to get batch code, course code, faculty code, timings, start date and end date for all completed batches. Also ensure the changes made to base table through view are retrievable through view.

    create or replace view completed_batches
    as
    select  code, course_code, faculty_code, timings, stdate, enddate
    from  batches
    where enddate is not null
    with check option 

Chapter 12: INDEXING, SEQUENCES AND PSEUDO COLUMNS

  1. Which constraints automatically create index?

    Primary Key and Unique

  2. What does ONLINE option in CREATE INDEX command do?

    ONLINE option allows index to be created without having to lock table

  3. How do you create an index on FACULTY_CODE and COURSE_CODE of BATCHES table?

    create index idx_batches_fcode_ccode on batches (faculty_code, course_code)

  4. _______ option in CREATE SEQUENCE is used to generate numbers in reverse order.

    Negative value for INCREMENT BY option

  5. _________ is the pseudo column used to get the next available number from a sequence.

    NEXTVAL

  6. Create a sequence called REVERSE to generate numbers in the descending order from 10000 to 1000 with a decrement of 5.

    Create sequence reverse_seq start with 10000 maxvalue 10000
    minvalue 1000 increment by -5; 
  7. Change the decrement value of sequence REVERSE (created earlier) to 2.

    Alter sequence reverse_seq increment by -2 
  8. What is the purpose of ROWID?

    ROWID is used internally by Oracle to identify each row uniquely

Chapter 13: SECURITY

  1. _________ command is used to change user password.

    ALTER USER

  2. Which object privilege allows user to create an index on the table?

    INDEX

  3. _______ option is used to grant a privilege along with permission to grant the privilege to other users.

    WITH GRANT OPTION

  4. A Role is ____________________________.

    A collection of privileges

  5. ______ command is used to revoke a system privilege.

    REVOKE

  6. __________ data dictionary view may be used to list tables that a user can access.

    ALL_TABLES

  7. ______ data dictionary view is used to know the list of tables owned by the current user.

    USER_TABLES

  8. ____ is the synonym for USER_CATALOG data dictionary.

    CAT

  9. Grant UPDATE privilege on STDATE column of BATCHES table to user PRANEETH with permission to grant the privilege to others.

    grant update(stdate) on batches to praneeth with grant option

  10. Create a role and assign a few privileges to that role. Assign the role to user PRANAV.

    create role programmer;
    grant all on students to programmer;
    grant select on courses to programmer;
    grant programmer to pranav;
  11. Enable the role LEADER.

    set role leader

  12. Display the table name, column name of all columns that you can access.

    select table_name, column_name
    from all_tab_columns
    order by 1,2 

Chapter 14: REPORT GENERATION USING SQL*PLUS COMMANDS

  1. _____ system variable is used to automatically commit changes made to database.

    AUTOCOMMIT

  2. Which command is used to change the heading of a column?

    COLUMN

  3. _____ is the numeric format to display number 12345 as 12,345.00.

    99,999.00

  4. _____ command is used to display the values of system variables.

    SHOW

  5. Display the title ‘Sales Report’ at the top of each printed page by aligning it to center.

    TTITLE center 'Sales Report'

  6. Define the following attributes for column NAME. Heading should be ‘Course Name’. Allow only first 20 characters to be displayed. Display ‘NONE’ if the value is null.

    COLUMN TITLE heading 'Book Title' format a20 null 'None'

  7. ________ variable is used to display the name of the user in the title in TTITLE command.

    SQL.USER

  8. ________ and _________ variables are used to set the dimensions of the report page.

    PAGESIZE and LINESIZE

  9. What is the purpose of LABEL option of COMPUTE command?

    to display text for computed value

  10. How do you turn off top title?

    TITILE OFF

Chapter 15: ADVANCED FEATURES

  1. _____________ function returns number of matches for the given regular expression.

    REGEXP_COUNT

  2. How many times MERGE scans source table?

    Only once

  3. What is the difference between INSERT ALL and INSERT FIRST?

    INSERT FIRST stops after first condition is satisfied, INSERT ALL evaluates all conditions

  4. What is meant by regular expression “[^0-9]$” ?

    for non-digits at the end of string

  5. Which clause of SELECT is used for flashback query?

    AS OF TIMESTAMP

  6. What does \2 represent in REGEXP_REPLACE function?

    \2 represents second group in regular expression in source

Chapter 16: INTRODUCTION TO PL/SQL

  1. In _______ part of PL/SQL block errors are handled.

    Exception Handling

  2. ___, ____ and ____ are the valid values for BOOLEAN data type.

    True, False, Null

  3. The part of Oracle Server that executes SQL commands is called as _______.

    SQL Statement Executor

  4. ______ is an example of Oracle tool that contains PL/SQL engine.

    Oracle Forms

  5. _____ is the operator for exponentiation.

    **

  6. _____ is used for commenting a single line.

    --

  7. Write PL/SQL block to change the DURATION of ORA course to the duration of JSE course.

    declare
      v_duration  courses.duration%type;
    begin
      select duration into v_duration
      from courses
      where code = 'jse';
      update courses set duration = v_duration
      where code = 'ora';
    end;
  8. Insert a new row into COURSE_FACULTY table with the following details: Course Name is Java SE, Faculty name is Craig Walls, and SINCEWHEN column is 2012.

    declare
      v_faculty  faculty.code%type;
      v_course   courses.code%type;
    begin
      select code  into v_course
      from courses
      where name  = 'Java SE';
      select code  into v_faculty
      from faculty
      where name  = 'Craig Walls';
      insert into course_faculty values( v_faculty,v_course,2012);
    end;
    

Chapter 17: CONTROL STRUCTURES

  1. Write a PL/SQL block to decrease the duration to 35 for course with code ora, if more than 2 batches have started in the last two months.

    declare
      v_count number(2);
    begin
      select count(*)   into v_count
      from batches
      where course_code = 'ora' and  months_between(sysdate,stdate) <= 2;
      if  v_count > 2 then
        update courses set duration = 35
        where code = 'ora';
      end if;
    end;
    
  2. Write a PL/SQL block to insert a new row into PAYMENTS table with the following data:

    declare
      v_admno students.admno%type;
      v_course_fee courses.fee%type;
      v_fee_paid  courses.fee%type;
    begin
      select admno into v_admno
      from students
      where name = 'George Michael';
      select sum(amount) into v_fee_paid
      from payments
      where admno = v_admno;
      select fee into v_course_fee
      from courses
      where code = (select course_code from batches where code = (select batch_code from students where admno = v_admno) );
      insert into payments values (invno_seq.nextval, v_admno, next_day(sysdate-8,'Mon'), v_course_fee - v_fee_paid);
    end;
    
  3. Display how many students have joined in each month in the current year.

    set serveroutput on
    declare
      v_count  number(3);
    begin
      for i in  1..12
      loop
        select count(*) into v_count
        from students
        where  to_char(dj,'yyyy') = to_char(sysdate,'yyyy') and to_char(dj,'mm') = i;
        dbms_output.put_line( i || ' - ' ||  v_count);
      end loop;
    end;
    

Chapter 18: EXCEPTION HANDLING

  1. Look for student number 1008. If it is not found then display a suitable error message on the screen otherwise display student name and total amount paid by student so far.

     declare
       v_total_paid  payments.amount%type;
       v_name        students.name%type;
    begin
       select  name into v_name
       from students
       where  admno = 1008;
       select nvl(sum(amount),0) into v_total_paid
       from  payments
       where admno = 1008;
       dbms_output.put_line('Total amount paid  by ' || v_name || ' is  ' || to_char(v_total_paid));
    exception
       when no_data_found  then
       dbms_output.put_line('Sorry! Student 1008 not found!');
    end; 
  2. _________ statement is used to re-raise an exception.

    RAISE

  3. _________ function is used to get error message of the most recent error.

    SQLERRM

  4. How do you associate an Oracle error with a user-defined error?

    Using PRAGMA EXCEPTION_INIT procedure

  5. When UPDATE command does not update any row then which of the following will happen?

    a. NO_DATA_FOUND exception occurs

    b. INVALID_UPDATE exception occurs

    c. No exception is raised

    C

  6. When an exception is not handled in the current block, which of the following happens?

    a. It results in error and terminates the block

    b. It is propagated to outer block

    c. It is ignored

    B

Chapter 19: CURSOR HANDLING

  1. Which attribute is used to find out how many rows were fetched from cursor so far?

    ROWCOUNT

  2. Can we use ISOPEN attribute with implicit cursor?

    YES. But it always returns false

  3. How can we know whether the most recent DML operation has affected any row?

    Using SQL%FOUND

  4. How do you declare an input argument for the cursor and how do you pass value to it?

    declare
      cursor course_batches (p_course varchar2) is
      select * from batches
      where course_code = p_course;
    begin
       -- pass ora as parameter to cursor
       open course_batches('ora');
       -- remaining process
    end;
    
  5. What is the use of CURRENT OF clause in DELETE and UPDATE commands?

    CURRENT OF clause in DELETE and UPDATE commands is used to affect row in the table that corresponds to current row in cursor

  6. Display 10th to 15th students in the order of joining date.

    declare
       cursor students_cursor   is
       select * from students
       order by dj;
    begin
       for student_rec in students_cursor
       loop
        if  students_cursor%rowcount >= 10 then
         dbms_output.put_line( student_rec.name);
         exit when  students_cursor%rowcount = 15;
        end if;
       end loop;
    end;
                

Chapter 20: PROCEDURES, FUNCTIONS AND PACKAGES

  1. _____ command is used to display errors that occurred during compilation of a stored procedure.

    SHOW ERRORS

  2. _______ view provides information about stored procedures.

    USER_SOURCE

  3. _______ option is used to specify that a parameter is both input and output parameter.

    INOUT

  4. What is the command used to compile a procedure explicitly?

    ALTER PROCEDURE with COMPILE option

  5. Create a function to take batch code and return the number of students in the batch.

    create or replace function students_count(p_batch varchar2)
    return number
    is
       v_count number(3);
    begin
      select count(*) into v_count
      from students
      where batch_code = p_batch;
      return v_count;
    end;
    
  6. Create a function to return the first missing admission number. If no admission number is missing then return the highest admission number + 1.

    create or replace function next_admno
    return number
    is
       v_count number(1);
       v_admno  students.admno%type;
    begin
      -- start with minimum admno + 1
      select min(admno) + 1 into v_admno
      from students;
      loop
           select count(*) into v_count
           from students
           where admno = v_admno;
           if  v_count = 0 then
            return  v_admno;
           end if;
           v_admno := v_admno + 1;
      end loop;
    end;
    
  7. Create a function to take faculty code and return the number of batches the faculty can handle.

    create or replace function faculty_course_count(p_faculty varchar2)
    return number
    is
       v_count number(3);
    begin
      select count(*) into v_count
      from course_faculty
      where faculty_code = p_faculty;
      return v_count;
    end;
    
  8. Create a procedure to take course code and return minimum and maximum duration of batches of that course.

    create or replace procedure course_batches_duration(p_course varchar2, p_min out number, p_max out number)
    is
    begin
      select min( trunc(enddate-stdate)), max( trunc(enddate-stdate)) into p_min, p_max
      from batches
      where course_code =  p_course and enddate is not null;
    end;
    The following code shows how to call the above procedure.
    declare
      v_min number(2);
      v_max number(2);
    begin
      course_batches_duration ('ora', v_min,v_max);
      dbms_output.put_line( v_min || '-' || v_max);
    end;
    
  9. Create a package to contain the following functions.

    a. Function GET_STATUS – takes batch code and returns S - if batch is yet to start, C – if batch is completed or R – if batch is currently running.

    b. Function GET_TOTAL_AMOUNT – returns the total amount collected from the given batch code.

    create or replace package batch_actions
    is
       function get_status(p_batch varchar2) return varchar2;
       function get_total_amount (p_batch varchar2) return number;
    end;
    create or replace package body batch_actions
    is
       function get_status(p_batch varchar2) return varchar2
       is
          v_stdate  date;
          v_enddate date;
       begin
          select stdate, enddate into v_stdate, v_enddate
          from batches
          where  code = p_batch;
          if v_stdate > sysdate then
            return 'S';
          elsif  v_enddate is null then
            return 'R';
          else
            return 'C';
          end if;
       end;
       function get_total_amount (p_batch varchar2) return number
       is
        v_total number(6);
       begin
         select nvl(sum(amount),0) into v_total
         from  payments
         where admno in ( select admno from students where batch_code = p_batch);
         return v_total;
       end;
    end;
    

Chapter 21: DATABASE TRIGGERS

  1. Which data dictionary view contains information about triggers?

    USER_TRIGGERS

  2. How many before triggers can we create?

    6 different before triggers can be created on a single table. One before insert, One before delete, and One before update for row-level and the same three for statement-level.

  3. Is it possible to create two or more triggers for the same event (BEFORE INSERT)?

    YES

  4. What is the default type of trigger? [Statement/Row]

    Statement. Unless FOR EACH ROW is given, a trigger is considered to be statement-level trigger

  5. Create a trigger to prevent any increase to FEE column of COURSES table if the increase is more than 50% of the existing course fee.

    create or replace trigger trg_fee_hike_check
    before update of fee
    on courses
    for each row
    begin
       if :new.fee - :old.fee >  :old.fee * 0.50 then
          raise_application_error(-20555,'Fee cannot be increased by more than 50% of existing fee');
       end if;
    end;
    
  6. Create a trigger to prevent all deletions from COURSES table between 9p.m to 9 a.m.

    create or replace trigger trg_prevent_courses_deletion
    before delete
    on courses
    begin
       if to_char(sysdate,'hh24') < 9 or to_char(sysdate,'hh24') >= 21 then
          raise_application_error(-20222,'No deletions can be made before 9AM and after 9PM');
       end if;
    end;
    

Chapter 22: RECORDS, COLLECTIONS AND LOBS

  1. Which keyword is used to refer to a row in nested table?

    VALUE

  2. Create a record type – DEPENDENT_TYPE, which contains two fields – dependent name and age. Create EMPLOYEES table with id, name, designation and dependents, which is a nested table of DEPENDENT_TYPE.

    create or replace type dependent_type as object
    (
      name  varchar2(50),
      age   number(3)
    );
    create or replace type dependents_table as table of dependent_type;
    create table employees
    ( id number(5),
      name varchar2(50),
      desg varchar2(50),
      dependents dependents_table
    )
    nested table dependents store as dependents_tab;
    
    insert into employees values (1, 'Stagner','Programmer', dependents_table ( dependent_type('Lucy',30))) 
  3. Create APPLICANTS table with the following columns.

    Column    Type

    Name      VARCHAR2(20)

    Resume    CLOB

    Photo     BFILE

    create table applicants
    ( name varchar2(20),
      resume clob,
      photo  bfile
    )
  4. Insert a row into APPLICANTS table with the following details.

    NAME - ‘Nike’, RESUME - ‘Subjects: Oracle, Java, jQuery’, and PHOTO - ‘nike.jpg’ which is directory referred by PHOTOS directory alias.

    insert into applicants values('Nike','Subjects : Oracle, Java, jQuery',bfilename('PHOTOS','nike.jpg') )
  5. Write a PL/SQL block to find out whether the pattern jQuery exists in the RESUME column of applicant ‘Nike’. If found display the starting position otherwise display error message using DBMS_OUTPUT package.

    set serveroutput on
    declare
     v_resume clob;
     pos      number(3);
    begin
      select resume into v_resume
      from applicants
      where name  = 'Nike';
      pos := dbms_lob.instr(v_resume,'jQuery',1,1);
      if  pos > 0 then
        dbms_output.put_line('Found at : ' || pos);
      else
        dbms_output.put_line('jQuery Not Found');
      end if;
    end;
  6. Check whether the physical file for applicant Nike is existing on the disk.

    declare
     v_photo bfile;
    begin
      select photo into v_photo
      from applicants
      where name  = 'Nike';
      if dbms_lob.fileexists(v_photo) = 1 then
        dbms_output.put_line('File is existing');
      else
        dbms_output.put_line('File is NOT existing');
      end if;
    end;
  7. Insert a new row into APPLICANTS with the following details. NAME - ‘Bob’, RESUME - Empty, PHOTO - Empty.

    insert into applicants values('Bob',empty_clob(), null);

  8. Change the value of PHOTO column of Bob to ‘bob2016.jpg’ in PHOTOS directory.

    create directory  photos as 'd:\photos';
    update applicants set photo = bfilename('PHOTOS','bob2016.jpg')
    where name  = 'Bob';  
  9. Display the length of RESUME column of applicant ‘Nike’.

    select dbms_lob.getlength(resume)
    from applicants
    where name = 'Nike'

Chapter 23: DYNAMIC SQL

  1. ________ option is used to pass bind arguments to placeholders.

    USING

  2. When there are 2 placeholders used three times in the command then how many bind arguments are to be passed?

    3 values must be passed

  3. Create a function that takes table name and a condition and returns the number of rows in the table that satisfy the given condition.

    -- on error returns null
    create or replace function get_row_count(tn varchar2, cond varchar2)
    return number
    is
      v_count number(5);
    begin
       execute immediate 'select count(*) from ' || tn || '  where ' || cond
         into v_count;
       return v_count;
    exception
       when others then
        return null;
    end;
    select get_row_count('students', 'admno > 5') from dual;
    

Chapter 24 UTILITIES

  1. __________ parameter is used in EXPDP to copy only table data without definition.

    CONTENT=DATA_ONLY

  2. __________ parameter of IMPDP can be used to append data from source file to tables in database.

    TABLE_EXISTS_ACTION=APPEND

  3. __________ file contains records that could not be loaded by SQL*Loader.

    BAD File

  4. How do you specify that you want to export from BATCHES table where ENDDATE is not null to EXPDP command?

    TABLES=batches
    QUERY=batches:"WHERE enddate is not null"
    
  5. What is the difference between loading data using SQL*Loader and creating external table?

    In case of external table, data is not loaded into Database. SQL*Loader loads data into Database physically