________ designed relational model
Dr. E.F. CODD
Data models are _______, _______, _________ and ________.
Hierarchical, Network, Relational, Object-oriented
Composite primary key is ___________.
A primary key that is consisting of multiple columns
A row is otherwise known as ________.
Tuple
How many tables does SELECT operator take?
One
______ is an example for an RDBMS.
Oracle Database
SQL command used to create table belongs to ______ category of SQL Commands.
DDL
_______key is used to join a child table with parent table.
Foreign
______ is the standard language for RDBMS.
SQL
What is a domain?
A set of potential values
Oracle instance is a collection of _________ and ____________.
Memory structures and Processes
What is the use of Host String?
Host string is used to connect to server from clients like Sql*plus
SQL*PLUS commands must be terminated with semicolon (;) [TRUE/FALSE]
FALSE
What is the password of user SYSTEM?
Whatever is given at the time of installation
_____________ is where we enter SQL commands in SQL Developer.
Worksheet
______ is the operator used to compare a column with null value.
IS NULL
______ operator is used to compare one value with a set of values.
IN
The maximum number of characters that can be stored in CHAR type is ______.
2000
In LIKE operator, % stands for _____________.
Zero or more characters of any kind
______ is used to change the heading of a column.
Alias
______ command is used to display definition of a table.
DESCRIBE
Display list of courses where course code starts with letter ‘c’.
select * from courses where code like 'c%'
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
Select rows from COURSES where course fee is in the range 3000 to 5000.
select * from courses where fee between 3000 and 5000
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')
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
__________ constraint can be used to implement business rules.
CHECK
__________ option of REFERENCES constraint is used to delete all child rows when parent row is being deleted.
DELETE CASCADE
Data dictionary view used to get information about constraints is _____________.
USER_CONSTRAINTS
When a table has a composite primary key, where is the PRIMARY KEY constraint defined?
At table level using table constraint
What is the relationship between COURSES and COURSE_FACULTY table?
to Many
How do you get details of all CHECK constraints of all tables?
select * from user_constraints where constraint_type = 'C'
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
How do you add a check constraint to an existing column?
Using ALTER TABLE command with ADD option
How do you drop a constraint?
Using constraint name in ALTER TABLE command with DROP option
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.
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.
_________ command is used to mark a location in a transaction.
SAVEPOINT
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.
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.
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.
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.
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.
______ 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
The return value of ROUND (2323.343, 2) is _________.
2323.34
To get the remainder of a division_____ function is used.
MOD
In date arithmetic __________, _______________ and ___________ operations are allowed.
Adding a number, Subtracting a number, Subtracting a date
____________ is the result of LAST_DAY (SYSDATE) assuming SYSDATE is 24th August.
31-AUG
Which function can be used to set time portion of the DATE data type to 00:00:00, without effecting date portion?
TRUNC(date)
Display details of students who have joined in the last 4 months.
select * from students where months_between(sysdate, dj) < 4
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
Display the first Sunday since batch with code b2 started.
select next_day(stdate,'Sun') from batches where code = 'b2'
Display details of batches that started three or more months back.
select * from batches where months_between(sysdate,stdate) >= 3
Display the details of payments of last Monday.
select * from payments where trunc(dp) = trunc( next_day(sysdate - 8, 'Mon'))
__________ is the function to get number of years between two dates.
MONTHS_BETWEEN() / 12 will give number of years
______ function performs one to one character substitution.
TRANSLATE
______ format option is used to get complete year spelled out in TO_CHAR function.
select to_char(sysdate,'year') from dual
_____ symbol is used to concatenate strings.
||
What happens if ‘replace string’ is not given for REPLACE function?
It replaces source with nothing, effectively removing occurrences of source from string
Can a NUMBER be converted to DATE? [Yes/No] _____.
No
How do you change the name of each student to uppercase in STUDENTS table?
update students set name = upper(name)
Display the names of the students who have more than 15 characters in the name.
select name from students where length(name) > 15
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
Display the details of the students who have more than 10 characters in the firstname.
select * from students where instr(name, ' ') > 10
What is the result of AMOUNT – DISCOUNT if column DISCOUNT is null?
NULL. Any expression that contains a null returns null.
How do you get the position of 5th occurrence of letter ‘o’ in student’s name?
Use INSTR(name,'o',1,5)
What will be the result of select ‘10’ * ‘20’ from dual?
200 as Oracle automatically converts both strings to numbers
_______ clause is used to select groups based on condition.
HAVING
Select count(*) from students; Is it a valid query?
Yes. It returns number of rows in STUDENTS table
What is the correct order of GROUP BY, ORDER BY and WHERE clauses in SELECT?
WHERE, GROUP BY and ORDER BY
Display ADMNO of students who have paid more than twice.
select admno from payments group by admno having count(*) > 2
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'
Display faculty who can take more than 2 courses.
select faculty_code from course_faculty group by faculty_code having count(*) > 2
Display least course fee.
select min(fee) from courses
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'
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
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
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
What is required to join two tables?
A common column is required to join two tables
What is meant by self-join?
Self-join is where a table is joined to itself
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
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.
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
Display admno, student name, pay date and amount paid.
select admno, name, dp, amount from payments natural join students
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)
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
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
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
Display admno, student name, email, pay date and amount paid.
select admno, name, email, dp, amount from students join payments using(admno)
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)
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
A correlated subquery is executed for ______ number of times.
N number of times where N is number of rows we have in main query
Subquery nesting can be up to ______ levels.
16
What is the result of x > ANY (10,20), if x is 15? ________.
TRUE
Subquery always passes the result to the main-query [T/F] _____.
TRUE
Subquery can be used in VALUES clause of INSERT command [T/F] ____.
TRUE
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') )
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)
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)
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)
)
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'
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)
)
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')
)
What are the major applications of a view?
Controlling Access, Query Simplification, Data Independence, and Presenting data in different forms
A view can be used with ALTER TABLE command [T/F] ?_______ .
FALSE
The table on which a view is based is called as _____.
Base Table
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.
A view can be used to manipulate base table when it follows certain rules [T/F]? _____.
TRUECreate 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;
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
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
Which constraints automatically create index?
Primary Key and Unique
What does ONLINE option in CREATE INDEX command do?
ONLINE option allows index to be created without having to lock table
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)
_______ option in CREATE SEQUENCE is used to generate numbers in reverse order.
Negative value for INCREMENT BY option
_________ is the pseudo column used to get the next available number from a sequence.
NEXTVAL
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;
Change the decrement value of sequence REVERSE (created earlier) to 2.
Alter sequence reverse_seq increment by -2
What is the purpose of ROWID?
ROWID is used internally by Oracle to identify each row uniquely_________ command is used to change user password.
ALTER USER
Which object privilege allows user to create an index on the table?
INDEX
_______ option is used to grant a privilege along with permission to grant the privilege to other users.
WITH GRANT OPTION
A Role is ____________________________.
A collection of privileges
______ command is used to revoke a system privilege.
REVOKE
__________ data dictionary view may be used to list tables that a user can access.
ALL_TABLES
______ data dictionary view is used to know the list of tables owned by the current user.
USER_TABLES
____ is the synonym for USER_CATALOG data dictionary.
CAT
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
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;
Enable the role LEADER.
set role leader
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
_____ system variable is used to automatically commit changes made to database.
AUTOCOMMIT
Which command is used to change the heading of a column?
COLUMN
_____ is the numeric format to display number 12345 as 12,345.00.
99,999.00
_____ command is used to display the values of system variables.
SHOW
Display the title ‘Sales Report’ at the top of each printed page by aligning it to center.
TTITLE center 'Sales Report'
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'
________ variable is used to display the name of the user in the title in TTITLE command.
SQL.USER
________ and _________ variables are used to set the dimensions of the report page.
PAGESIZE and LINESIZE
What is the purpose of LABEL option of COMPUTE command?
to display text for computed value
How do you turn off top title?
TITILE OFF
_____________ function returns number of matches for the given regular expression.
REGEXP_COUNT
How many times MERGE scans source table?
Only once
What is the difference between INSERT ALL and INSERT FIRST?
INSERT FIRST stops after first condition is satisfied, INSERT ALL evaluates all conditions
What is meant by regular expression “[^0-9]$” ?
for non-digits at the end of string
Which clause of SELECT is used for flashback query?
AS OF TIMESTAMP
What does \2 represent in REGEXP_REPLACE function?
\2 represents second group in regular expression in source
In _______ part of PL/SQL block errors are handled.
Exception Handling
___, ____ and ____ are the valid values for BOOLEAN data type.
True, False, Null
The part of Oracle Server that executes SQL commands is called as _______.
SQL Statement Executor
______ is an example of Oracle tool that contains PL/SQL engine.
Oracle Forms
_____ is the operator for exponentiation.
**
_____ is used for commenting a single line.
--
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;
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;
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;
Write a PL/SQL block to insert a new row into PAYMENTS table with the following data:
INVNO is to be taken from INVNO_SEQ sequence.
ADMNO of student with the name George Michael.
Date of payment is previous Monday.
Amount is the balance amount to be paid by the student.
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;
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;
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;
_________ statement is used to re-raise an exception.
RAISE
_________ function is used to get error message of the most recent error.
SQLERRM
How do you associate an Oracle error with a user-defined error?
Using PRAGMA EXCEPTION_INIT procedure
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
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
Which attribute is used to find out how many rows were fetched from cursor so far?
ROWCOUNT
Can we use ISOPEN attribute with implicit cursor?
YES. But it always returns false
How can we know whether the most recent DML operation has affected any row?
Using SQL%FOUND
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;
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
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;
_____ command is used to display errors that occurred during compilation of a stored procedure.
SHOW ERRORS
_______ view provides information about stored procedures.
USER_SOURCE
_______ option is used to specify that a parameter is both input and output parameter.
INOUT
What is the command used to compile a procedure explicitly?
ALTER PROCEDURE with COMPILE option
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;
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;
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;
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;
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;
Which data dictionary view contains information about triggers?
USER_TRIGGERS
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.
Is it possible to create two or more triggers for the same event (BEFORE INSERT)?
YES
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
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;
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;
Which keyword is used to refer to a row in nested table?
VALUE
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)))
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 )
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') )
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;
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;
Insert a new row into APPLICANTS with the following details. NAME - ‘Bob’, RESUME - Empty, PHOTO - Empty.
insert into applicants values('Bob',empty_clob(), null);
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';
Display the length of RESUME column of applicant ‘Nike’.
select dbms_lob.getlength(resume) from applicants where name = 'Nike'
________ option is used to pass bind arguments to placeholders.
USING
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
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;
__________ parameter is used in EXPDP to copy only table data without definition.
CONTENT=DATA_ONLY
__________ parameter of IMPDP can be used to append data from source file to tables in database.
TABLE_EXISTS_ACTION=APPEND
__________ file contains records that could not be loaded by SQL*Loader.
BAD File
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"
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