Skip to content

Instantly share code, notes, and snippets.

@sandeepmchouhan111293

sandeepmchouhan111293/sql dumps

Created January 7, 2017 11:18
Show Gist options
  • Save sandeepmchouhan111293/c1eb9a87f3cd263128921b549c51ee5a to your computer and use it in GitHub Desktop.
Save sandeepmchouhan111293/c1eb9a87f3cd263128921b549c51ee5a to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
Looking for Real Exam Questions for IT Certification Exams!
We Guarantee you can pass any IT certification exam at your first attempt with just 10-
12 hours study of our guides.
Our study guides contain actual exam questions, you will get word to word same on your
actual test; accurate answers with detailed explanation verified by experts and all
graphics and drag-n-drop exhibits shown just as on the real test
To test the quality of our guides, you can download the one-third portion of any guide
from http://www.certificationking.com absolutely free.
Besides, we also offer complete version of following exams absolutely free. You can start
your certification from these free guides and if you are satisfied you can buy the rest
♦ MCSE/MCSA: 70-215 & 70-290 ♦ MCAD/MCSA: 70-305 & 70-229
♦ CCNP: 642-801 ♦ CCSP: 642-501 ♦ CCIP: 642-611 ♦ OCP 9i: 1Z0-007
♦ CIW: 1D0-410 ♦ Novell: 50-632 & 50-650 ♦ Citrix: 1Y0-610 ♦ LOTUS: 190-510
For pricing and placing order, please visit http://certificationking.com/order.html
We accept all major credit cards through www.paypal.com
For other payment options and any further query, feel free to mail us at
info@certificationking.com
Exam : 1Z0-147
Title : Oracle 9i: Program with PL/SQL
Total Question 71
# QUESTION 1
Examine this function:
CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG
(V_ID in PLAYER_BAT_STAT.PLAYER_ID%TYPE)
RETURN NUMBER
IS
V_AVG NUMBER;
BEGIN
SELECT HITS / AT_BATS
INTO V_AVG
FROM PLAYER_BAT_STAT
WHERE PLAYER_ID = V_ID;
RETURN (V_AVG);
END;
Which statement will successfully invoke this function in SQL *Plus?
A. SELECT CALC_PLAYER_AVG(PLAYER_ID)
FROM PLAYER_BAT_STAT;
B. EXECUTE CALC_PLAYER_AVG(31);
C. CALC_PLAYER('RUTH');
D. CALC_PLAYER_AVG(31);
E. START CALC_PLAYER_AVG(31)
Answer: A
Incorrect Answers
B. You can't call a function in this way, in this way you can call a procedure, because function must return a
value, to call a function using EXECUTE command you should declare a bind variable using the
VARIABLE command then assign the value returned from the function to this variable, in the following
way:
SQL> VARIABLE v_get_value NUMBER
SQL> EXECUTE :v_get_value := CALC_PLAYER_AVG(31)
PL/SQL procedure successfully completed.
SQL> PRINT v_get_value
V_GET_VALUE
1
C. Again this way can't be use for calling a function in PL/SQL block because the function return a value
and this values must be assigned to PL/SQL variable or to bind variable. Like this
DECLARE
v_get_from_fn NUMBER;
BEGIN
v_get_from := CALC_PLAYER_AVG(31);
END;
/
D. Same as C.
E. START is use to execute a script.
# QUESTION 2
Which three are true statements about dependent objects? (Choose three)
A. Invalid objects cannot be described.
B. An object with status of invalid cannot be a referenced object.
C. The Oracle server automatically records dependencies among objects.
D. All schema objects have a status that is recorded in the data dictionary.
E. You can view whether an object is valid or invalid in the USER_STATUS data dictionary view.
F. You can view whether an object is valid or invalid in the USER_OBJECTS data dictionary view.
Answer: A,C,F
Incorrect answers: B, D, E
# QUESTION 3
You have created a stored procedure DELETE_TEMP_TABLE that uses dynamic SQL to remove a
table in your schema. You have granted the EXECUTE privilege to user A on this procedure.
When user A executes the DELETE_TEMP_TABLE procedure, under whose privileges are the
operations performed by default?
A. SYS privileges
B. Your privileges
C. Public privileges
D. User A's privileges
E. User A cannot execute your procedure that has dynamic SQL.
Answer: B
When you create a procedure, it will be executed under the privileges of the creator, unless the procedure
has the following statement AUTHID CURRENT_USER. If you specify AUTHID CURRENT_USER, the
privileges of the current user are checked at run time, and external references are resolved in the schema of
the current user. Like this example
SQL> CREATE OR REPLACE PROCEDURE delete_temp_table(v_table varchar2)
2 AUTHID CURRENT_USER
3 IS
4 BEGIN
5 EXECUTE IMMEDIATE 'DROP TABLE '||V_TABLE;
6 END;
7 /
Procedure created.
If the procedure is create in this way then the EXECUTE IMMEDIATE statement will be execute under the
privilege of the user who executes the procedure, but if we skip line 2 then the procedure will be executed
under the privilege of the owner of the procedure.
Incorrect Answers
A: SYS privilege has nothing with is.
C: What is the public privileges? There is nothing called public privileges.
D: This will be true if the procedure contains the AUTHID CURRENT_USER.
E: There is no problem in having a dynamic SQL statement in Procedure.
# QUESTION 4
Examine this code:
CREATE OR REPLACE PRODECURE add_dept
(p_dept_name VARCHAR2 DEFAULT 'placeholder',
p_location VARCHAR2 DEFAULT 'Boston')
IS
BEGIN
INSERT INTO departments
VALUES (dept_id_seq.NEXTVAL, p_dept_name, p_location);
END add_dept;
/Which three are valid calls to the add_dep procedure? (Choose three)
A. add_dept;
B. add_dept('Accounting');
C. add_dept(, 'New York');
D. add_dept(p_location=>'New York');
Answer: A,B,D
A is correct because both of the parameter have a default values.
B is correct because here we call the procedure using position notation, and the first parameter for the
procedure will have the value 'Accounting', and since the second parameter has a default value then we
can skip it, and in this case it will take the default value.
D is correct because here we are calling the procedure using naming notation, the value 'New York' will go
to the parameter p_location, and the parameter p_dept_name will have the default value.
The following table list the for passing parameters to a procedure:
Incorrect Answer
C: You can't use this way and assume that the PL/SQL will understand that he should assign the default
value for the first parameter. This is incorrect way for calling.
# QUESTION 5
Which two statements about packages are true? (Choose two)
A. Packages can be nested.
B. You can pass parameters to packages.
C. A package is loaded into memory each time it is invoked.
D. The contents of packages can be shared by many applications.
E. You can achieve information hiding by making package constructs private.
Answer: D,E
Actually theses are some of the advantages of the package, sharing the package among applications and
hide the logic of the procedures and function that are inside the package by declaring them in the package
header and write the code of these procedures and functions inside the package body.
Incorrect answers:
A: Packages can not be nested
B: Parameters can't be passed to a package; parameters can be passed to procedures and functions only.
C: By the first time you call a procedure, function, or reference a global variable within the package, the
whole package will be loaded into the memory and stay there, so when ever you need to reference any of the
package's constructs again you will find it in the memory.
# QUESTION 6
Which two programming constructs can be grouped within a package? (Choose two)
A. Cursor
B. Constant
C. Trigger
D. Sequence
E. View
Answer: A,B
Incorrect Answers
C: Triggers are objects that we create are created on the tables.
D: Sequences can't be grouped inside the packages, but we can reference then inside the package.
E: Views are created and they are database objects, and they can't be grouped inside the packages.
# QUESTION 7
Which two statements describe the state of a package variable after executing the package in which it
is declared? (Choose two)
A. It persists across transactions within a session.
B. It persists from session to session for the same user.
C. It does not persist across transaction within a session.
D. It persists from user to user when the package is invoked.
E. It does not persist from session to session for the same user.
Answer: A,E
You can keep track of the state of a package variable or cursor, which persists throughout the user session,
from the time the user first references the variable or cursor to the time the user disconnects.
1. Initialize the variable within its declaration or within an automatic, one-time-only procedure.
2. Change the value of the variable by means of package procedures.
3. The value of the variable is released when the user disconnects.
Incorrect Answers
B: Each session will have its own value for the variables
C: It persists across the transactions and through the user session.
D: Each user has his own values and results, because each user has his own users.
# QUESTION 8
Which code can you use to ensure that the salary is not increased by more than 10% at a time nor is it
ever decreased?
A. ALTER TABLE emp ADD
CONSTRAINT ck_sal CHECK (sal BETWEEN sal AND sal*1.1);
B. CREATE OR REPLACE TRIGGER check_sal
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
WHEN (new.sal < old.sal OR
new.sal > old.sal * 1.1)
BEGIN
RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease
salary not increase by more than 10%');
END;
C. CREATE OR REPLACE TRIGGER check_sal
BEFORE UPDATE OF sal ON emp
WHEN (new.sal < old.sal OR
new.sal > old.sal * 1.1)
BEGIN
RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease
salary not increase by more than 10%');
END;
D. CREATE OR REPLACE TRIGGER check_sal
AFTER UPDATE OR sal ON emp
WHEN (new.sal < old.sal OR
-new.sal > old.sal * 1.1)
BEGIN
RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease
salary not increase by more than 10%');
END;
Answer: B
Row triggers are the correct chose for solving the problem. A row trigger fires each time the table is affected
by the triggering event. If the triggering event affects no rows, a row trigger is not executed.
Row triggers are useful if the trigger action depends on data of rows that are affected or on data provided by
the triggering event itself. You can create a BEFORE row trigger in order to prevent the triggering operation
from succeeding if a certain condition is violated.
Within a ROW trigger, reference the value of a column before and after the data change by prefixing it with
the OLD and NEW qualifier.
Incorrect answers:
A: Check constaint can't do this job lets take a look:
SQL> ALTER TABLE emp ADD
2 CONSTRAINT ck_sal CHECK (sal BETWEEN sal AND sal*1.1)
3 /
Table altered.
SQL> select ename, sal
2 from emp
3 where ename = 'KING';
ENAME SAL
KING 5000
Now let's issue an update statement
SQL> update emp
2 set sal = 10
3 where ename = 'KING';
1 row updated.
As you can see the check constraint can't compare the old value with the new value.
D,C: You can use NEW and OLD qualifier with row level triggers, If in the CREATE TRIGGER statement
you didn't say FOR EACH ROW then the trigger will be statement level trigger
# QUESTION 9
Examine this code:
CREATE OR REPLACE PACKAGE bonus
IS
g_max_bonus NUMBER := .99;
FUNCTION calc_bonus (p_emp_id NUMBER)
RETURN NUMBER;
FUNCTION calc_salary (p_emp_id NUMBER)
RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY bonus
IS
v_salary employees.salary%TYPE;
v_bonus employees.commission_pct%TYPE;
FUNCTION calc_bonus (p_emp_id NUMBER)
RETURN NUMBER
IS
BEGIN
SELECT salary, commission_pct
INTO v_salary, v_bonus
FROM employees
WHERE employee_id = p_emp_id;
RETURN v_bonus * v_salary;
END calc_bonus
FUNCTION calc_salary (p_emp_id NUMBER)
RETURN NUMBER
IS
BEGIN
SELECT salary, commission_pct
INTO v_salary, v_bonus
FROM employees
WHERE employees
RETURN v_bonus * v_salary + v_salary;
END cacl_salary;
END bonus;
/
Which statement is true?
A. You can call the BONUS.CALC_SALARY packaged function from an INSERT command against the
EMPLOYEES table.
B. You can call the BONUS.CALC_SALARY packaged function from a SELECT command against the
EMPLOYEES table.
C. You can call the BONUS.CALC_SALARY packaged function form a DELETE command against the
EMPLOYEES table.
D. You can call the BONUS.CALC_SALARY packaged function from an UPDATE command against the
EMPLOYEES table.
Answer: B
For the Oracle server to execute a SQL statement that calls a stored function, it must know the purity level
of a stored functions, that is, whether the functions are free of side effects. Side effects are changes to
database tables or public packaged variables (those declared in a package specification). Side effects could
delay the execution of a query, yield order-dependent (therefore indeterminate) results, or require that the
package state variables be maintained across user sessions. Various side effects are not allowed when a
function is called from a SQL query or DML statement. Therefore, the following restrictions apply to stored
functions called from SQL expressions:
• A function called from a query or DML statement may not end the current transaction, create or roll
back to a savepoint, or alter the system or session
• A function called from a query statement or from a parallelized DML statement may not execute a
DML statement or otherwise modify the database
• A function called from a DML statement may not read or modify the particular table being modified
by that DML statement
# QUESTION 10
Which statement is valid when removing procedures?
A. Use a drop procedure statement to drop a standalone procedure.
B. Use a drop procedure statement to drop a procedure that is part of a package.
Then recompile the package specification.
C. Use a drop procedure statement to drop a procedure that is part of a package.
Then recompile the package body.
D. For faster removal and re-creation, do not use a drop procedure statement.
Instead, recompile the procedure using the alter procedure statement with the REUSE SETTINGS
clause.
Answer: A
The DROP DROCEDURE statement is used to drop a stand alone procedure
Incorrect answers:
B: You can't drop a procedure that's inside a package, you have to drop the package, and in this case the
whole procedures, functions,... that are inside the packages will be droped.
C: Same as B.
D: REUSE SETTINGS is used to to prevent Oracle from dropping and reacquiring compiler switch
settings.With this clause, Oracle preserves the existing settings and uses them for the recompilation.
# QUESTION 11
Examine this package:
CREATE OR REPLACE PACKAGE BB_PACK
IS
V_MAX_TEAM_SALARY NUMBER(12,2);
PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY
NUMBER);
END BB_PACK;
/
CREATE OR REPLACE PACKAGE BODY BB_PACK
IS
PROCEDURE UPD_PLAYER_STAT
(V_ID IN NUMBER, V_AB IN NUMBER DEFAULT 4, V_HITS IN NUMBER)
IS
BEGIN
UPDATE PLAYER_BAT_STAT
SET AT_BATS = AT_BATS + V_AB,
HITS = HITS + V_HITS
WHERE PLAYER_ID = V_ID;
COMMIT;
END UPD_PLAYER_STAT;
PROCEDURE ADD_PLAYER
(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBER)
IS
BEGIN
INSERT INTO PLAYER(ID,LAST_NAME,SALARY)
VALUES (V_ID, V_LAST_NAME, V_SALARY);
UPD_PLAYER_STAT(V_ID,0,0);
END ADD_PLAYER;
END BB_PACK;
You make a change to the body of the BB_PACK package. The BB_PACK body is recompiled.
What happens if the stand alone procedure VALIDATE_PLAYER_STAT references this package?
A. VALIDATE_PLAYER_STAT cannot recompile and must be recreated.
B. VALIDATE_PLAYER_STAT is not invalidated.
C. VALDIATE_PLAYER_STAT is invalidated.
D. VALIDATE_PLAYER_STAT and BB_PACK are invalidated.
Answer: B
You can greatly simplify dependency management with packages when referencing a package procedure or
function from a stand-alone procedure or function.
• If the package body changes and the package specification does not change, the stand-alone
procedure referencing a package construct remains valid.
• If the package specification changes, the outside procedure referencing a package construct is
invalidated, as is the package body.
# QUESTION 12
You need to create a trigger on the EMP table that monitors every row that is changed and places this
information into the AUDIT_TABLE.
What type of trigger do you create?
A. FOR EACH ROW trigger on the EMP table.
B. Statement-level trigger on the EMP table.
C. FOR EACH ROW trigger on the AUDIT_TABLE table.
D. Statement-level trigger on the AUDIT_TABLE table.
E. FOR EACH ROW statement-level trigger on the EMP table.
Answer: A
FOR EACH ROW trigger on the updated table(emp) should be create to record each update row in the
AUDIT_TABLE.
# QUESTION 13
Which statements are true? (Choose all that apply)
A. If errors occur during the compilation of a trigger, the trigger is still created.
B. If errors occur during the compilation of a trigger you can go into SQL *Plus and query the
USER_TRIGGERS data dictionary view to see the compilation errors.
C. If errors occur during the compilation of a trigger you can use the SHOW ERRORS command within
iSQL *Plus to see the compilation errors.
D. If errors occur during the compilation of a trigger you can go into SQL *Plus and query the
USER_ERRORS data dictionary view to see compilation errors.
Answer: A, C, D
# QUESTION 14
Which two dictionary views track dependencies? (Choose two)
A. USER_SOURCE
B. UTL_DEPTREE
C. USER_OBJECTS
D. DEPTREE_TEMPTAB
E. USER_DEPENDENCIES
F. DBA_DEPENDENT_OBJECTS
Answer: D, E
# QUESTION 15
Given a function CALCTAX:
CREATE OR REPLACE FUNCTION calctax (sal NUMBER) RETURN NUMBER
IS
BEGIN
RETURN (sal * 0.05);
END;
If you want to run the above function from the SQL *Plus prompt, which statement is true?
A. You need to execute the command CALCTAX(1000);.
B. You need to execute the command EXECUTE FUNCTION calctax;.
C. You need to create a SQL *Plus environment variable X and issue the command
:X := CALCTAX(1000);.
D. You need to create a SQL *Plus environment variable X and issue the command
EXECUTE :X := CALCTAX;
E. You need to create a SQL *Plus environment variable X and issue the command
EXECUTE :X := CALCTAX(1000);
Answer: E
When you call a function from SQL*PLUS you need to assign the returned value a bind variable, and you
need the EXECUTE command to execute the function.
# QUESTION 16
What happens during the execute phase with dynamic SQL for INSERT, UPDATE, and DELETE
operations?
A. The rows are selected and ordered.
B. The validity of the SQL statement is established.
C. An area of memory is established to process the SQL statement.
D. The SQL statement is run and the number of rows processed is returned.
E. The area of memory established to process the SQL statement is released.
Answer: D
All SQL statements have to go through various stages. Some stages may be skipped.
1. Parse
Every SQL statement must be parsed. Parsing the statement includes checking the statement's syntax and
validating the statement, ensuring that all references to objects are correct, and ensuring that the relevant
privileges to those objects exist.
2. Bind
After parsing, the Oracle server knows the meaning of the Oracle statement but still may not have enough
information to execute the statement. The Oracle server may need values for any bind variable in the
statement. The process of obtaining these values is called binding variables.
3. Execute
At this point, the Oracle server has all necessary information and resources, and the statement is executed.
4. Fetch
In the fetch stage, rows are selected and ordered (if requested by the query), and each successive fetch
retrieves another row of the result, until the last row has been fetched. You can fetch queries, but not the
DML statements.
# QUESTION 17
What part of a database trigger determines the number of times the trigger body executes?
A. Trigger type
B. Trigger body
C. Trigger event
D. Trigger timing
Answer: A
Part Description Possible
rigger timing When the trigger fires in relation to the BEFORE
triggering event AFTER
INSTEAD OF
Triggering event Which data manipulation operation on the INSERT
table or view causes the trigger to fire UPDATE
DELETE
Trigger type How many times the trigger body executes Statement
Row
Trigger body What action the trigger performs Complete PL/SQL block
# QUESTION 18
Examine this code:
CREATE OR REPLACE FUNCTION gen_email_name
(p_first_name VARCHAR2, p_last_name VARCHAR2, p_id NUMBER)
RETURN VARCHAR2
is
v_email_name VARCHAR2(19);
BEGIN
v_email_home := SUBSTR(p_first_name, 1, 1) ||
SUBSTR(p_last_name, 1, 7) ||
'@Oracle.com';
UPDATE employees
SET email = v_email_name
WHERE employee_id = p_id;
RETURN v_email_name;
END;
You run this SELECT statement:
SELECT first_name, last_name
gen_email_name(first_name, last_name, 108) EMAIL
FROM employees;
What occurs?
A. Employee 108 has his email name updated based on the return result of the function.
B. The statement fails because functions called from SQL expressions cannot perform DML.
C. The statement fails because the functions does not contain code to end the transaction.
D. The SQL statement executes successfully, because UPDATE and DELETE statements are ignoring in
stored functions called from SQL expressions.
E. The SQL statement executes successfully and control is passed to the calling environment.
Part Description Possible Values
Trigger timing When the trigger fires in relation to the
triggering event
BEFORE
AFTER
INSTEAD OF
Triggering event Which data manipulation operation on the
table or view causes the trigger to fire
INSERT
UPDATE
DELETE
Trigger type How many times the trigger body executes Statement
Row
Trigger body What action the trigger performs Complete PL/SQL block
Answer: B
• When called from a SELECT statement or a parallelized UPDATE or DELETE statement, the function
cannot modify any database tables
• When called from an UPDATE, or DELETE statement, the function cannot query or modify any
database tables modified by that statement.
• When called from a SELECT, INSERT, UPDATE, or DELETE statement, the function cannot execute
SQL transaction control statements (such as COMMIT), session control statements (such as SET
ROLE), or system control statements (such as ALTER SYSTEM). Also, it cannot execute DDL
statements (such as CREATE) because they are followed by an automatic commit.
• The function cannot call another subprogram that breaks one of the above restrictions.
# QUESTION 19
Which table should you query to determine when your procedure was last compiled?
A. USER_PROCEDURES
B. USER_PROCS
C. USER_OBJECTS
D. USER_PLSQL_UNITS
Answer: C
In the USER_OBJECTS there is
Incorrect Answers
A. USER_PROCEDURES lists all functions and procedures, along with associated properties. For example,
ALL_PROCEDURES indicates whether or not a function is pipelined, parallel enabled or an aggregate
function. If a function is pipelined or an aggregate function, the associated implementation type (if any) is
also identified. It doesn't have when the object was last complied.
B. There is nothing called USER_PROCS.
D. There is nothing called USER_PLSQL_UNITS
# QUESTION 20
Examine this code:
CREATE OR REPLACE TRIGGER secure_emp
BEFORE LOGON ON employees
BEGIN
IF (TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN')) OR
(TO_CHAR(SYSDATE, 'HH24:MI')
NOT BETWEEN '08:00' AND '18:00')
THEN RAISE_APPLICATION_ERROR (-20500, 'You may
insert into the EMPLOYEES table only during
business hours.');
END IF;
END;
/
What type of trigger is it?
A. DML trigger
B. INSTEAD OF trigger
C. Application trigger
D. System event trigger
E. This is an invalid trigger.
Answer: E
As you can see there is nothing called BEFORE LOGON
# QUESTION 21
Examine this package:
CREATE OR REPLACE PACKAGE discounts
IS
g_id NUMBER := 7829;
discount_rate NUMBER := 0.00;
PROCEDURE display_price (p_price NUMBER);
END discounts;
/
CREATE OR REPLACE PACKAGE BODY discounts
IS
PROCEDURE display_price (p_price NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Discounted '||
TO_CHAR(p_price*NVL(discount_rate, 1)));
END display_price;
BEGIN
discount_rate := 0.10;
END discounts;
/
Which statement is true?
A. The value of DISCOUNT_RATE always remains 0.00 in a session.
B. The value of DISCOUNT_RATE is set to 0.10 each time the package is invoked in a session.
C. The value of DISCOUNT_RATE is set to 1.00 each time the procedure DISPLAY_PRICE is
invoked.
D. The value of DISCOUNT_RATE is set to 0.10 when the package is invoked for the first time in a
session.
Answer: D
A one-time-only procedure is executed only once, when the package is first invoked within the user session
# QUESTION 22
Examine this code:
CREATE OR REPLACE TRIGGER update_emp
AFTER UPDATE ON emp
BEGIN
INSERT INTO audit_table (who, dated)
VALUES (USER, SYSDATE);
END;
You issue an UPDATE command in the EMP table that results in changing 10 rows.
How many rows are inserted into the AUDIT_TABLE?
A. 1
B. 10
C. None
D. A value equal to the number of rows in the EMP table.
Answer: A
# QUESTION 23
Examine this package:
CREATE OR REPLACE PACKAGE BB_PACK
IS
V_MAX_TEAM_SALARY NUMBER(12,2);
PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME VARCHAR2,
V_SALARY_NUMBER;
END BB_PACK;
/
CREATE OR REPLACE PACKAGE BODY BB_PACK
IS
PROCEDURE UPD_PLAYER_STAT
(V_ID IN NUMBER, V_AB IN NUMBER DEFAULT 4, V_HITS IN NUMBER)
IS
BEGIN
UPDATE PLAYER_BAT_STAT
SET AT_BATS = AT_BATS + V_AB,
HITS = HITS + V_HITS
WHERE PLAYER_ID = V_ID)
COMMIT;
END UPD_PLAYER_STAT;
PROCEDURE ADD_PLAYER
(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBER)
IS
BEGIN
INSERT INTO PLAYER(ID,LAST_NAME,SALARY)
VALUES (V_ID, V_LAST_NAME, V_SALARY);
UPD_PLAYER_STAT(V_ID,0.0);
END ADD_PLAYER;
END BB_PACK;
Which statement will successfully assign $75,000,000 to the V_MAX_TEAM_SALARY variable from
within a stand-alone procedure?
A. V_MAX_TEAM_SALARY := 7500000;
B. BB_PACK.ADD_PLAYER.V_MAX_TEAM_SALARY := 75000000;
C. BB_PACK.V_MAX_TEAM_SALARY := 75000000;
D. This variable cannot be assigned a value from outside the package.
Answer: C
To assign a value for a public variable which is declared in the package header, all what you have to
do is do user the following syntax
package_name.var_name:=value;
# QUESTION 24
There is a CUSTOMER table in a schema that has a public synonym CUSTOMER and you are granted all
object privileges on it. You have a procedure PROCESS_CUSTOMER that processes customer
information that is in the public synonym CUSTOMER table. You have just created a new table called
CUSTOMER within your schema.
Which statement is true?
A. Creating the table has no effect and procedure PROCESS_CUSTOMER still accesses data from public
synonym CUSTOMER table.
B. If the structure of your CUSTOMER table is the same as the public synonym CUSTOMER table then
the procedure PROCESS_CUSTOMER is invalidated and gives compilation errors.
C. If the structure of your CUSTOMER table is entirely different from the public synonym CUSTOMER
table then the procedure PROCESS_CUSTOMER successfully recompiles and accesses your
CUSTOMER table.
D. If the structure of your CUSTOMER table is the same as the public synonym CUSTOMER table then
the procedure PROCESS_CUSTOMER successfully recompiles when invoked and accesses your
CUSTOMER table.
Answer: D
The procedure will first look in the owner of the procedure schema before looking for the public synonym.
Incorrect answers:
A, B, C
# QUESTION 25
Which two statements about packages are true? (Choose two)
A. Both the specification and body are required components of a package.
B. The package specification is optional, but the package body is required.
C. The package specification is required, but the package body is optional.
D. The specification and body of the package are stored together in the database.
E. The specification and body of the package are stored separately in the database.
Answer: C,E
Raw
gistfile2.txt
=======================================================================================================
//Program to calculate number of days and number of month in a year.
BEGIN
DECLARE month, day,totaldays AS INTEGERS
INITIALIZE totaldays TO 365
CALCULATE month = totaldays / 30
CALCULATE day = totaldays % 30
PRINT "No.of months " + month
PRINT "No.of days + day
END
========================================================================================================
//Program to print required power of given number
BEGIN
DECLARE pow,num,result,count AS INTEGER
INITIALIZE num TO 1
INITIALIZE count TO 1
PROMPT "Enter the number and its power" AND STORE IN num, pow
DO
calculate result*=num
count++;
UNTIL (count <= pow)
PRINT "Number "+ num + "to power "+pow+ "is= "+result
END
=========================================================================================================
//Program to find the sum of first n natural numbers where n is entered by user.
BEGIN
DECLARE num,count,sum AS INTEGER
PROMPT "Enter the value of n" AND STORE IN num
FOR COUNT = 1 TO num
sum+=count;
END FOR
PRINT "Sum is " + sum
END
============================================================================================================
//Program to find whether a given number is zero or positive or negative
BEGIN
DECLARE value AS INTEGER
IF ( value == 0 )THEN
PRINT "The value you entered was zero."
ELSE IF ( value < 0 ) THEN
PRINT "The value is negative."
ELSE
PRINT "The value is positive."
END IF
END
============================================================================================================
// Program to find largest number
BEGIN
DECLARE num1,num2, num3 AS INTEGER
PROMPT "Enter 3 numbers" AND STORE IN num1,num2,num3;
IF(num1>=num2 && num1>=num3) THEN
PRINT "Largest Number "+num1
ELSEIF(num2>=num1 && num2>=num3)
PRINT "Largest Number "+num2
ELSE
PRINT "Largest Number "+num3
END IF
END
============================================================================================================
//Program to compare two numbers
BEGIN
DECLARE number1, number2 AS INTEGER
PROMPT "Enter number" AND STORE IN number1
number2 = (number1>5 ? 3 : 4)
PRINT number2
END
===========================================================================================================
//Program to accept a number and find whether the given number is odd or even repeatedly
BEGIN
WHILE(TRUE)
PRINT "Enter a NUMBER"
  ACCEPT num
   IF (REMAINDER of num/2 = 0) THEN
     PRINT "Number is EVEN"
  ELSE
   PRINT "Number is ODD“
END IF
PRINT "Do you want to continue?[Y/N]"
   ACCEPT choice
    IF(choice='Y') THEN
cycle
ELSE
exit
  END WHILE
END
=========================================================================================================
/*********************************************************************
* File : ModulerApproachDemo.txt
* Author Name : Capgemini
* Desc : Program to check whether the number is prime
* Version : 1.0
* Last Modified Date : 01-May-2016
* Change Description : Description about the changes implemented
*********************************************************************/
BEGIN
DECLARE num AS INTEGER
ACCEPT num
IF num > 2 THEN
IF checkPrime(num)THEN
PRINT num, " is prime"
ELSE
PRINT num, " is not prime"
END IF
ELSE IF num > 1 THEN
PRINT num, " is prime"
ELSE
PRINT num, " is not prime"
END IF
END
/*********************************************************************
* Module Name : checkPrime()
* Input Parameters : number
* Return Type : BOOLEAN
* Author : Capgemini
* Creation Date : 01-May-2016
* Description : Checking for prime number
*********************************************************************/
SUB checkPrime(number)
DECLARE index AS INTEGER
FOR index = 2 TO (number/2)
IF number % index == 0 THEN
RETURN false
END IF
END FOR
RETURN true
END SUB
=====================================================================
/*********************************************************************
* File : ModulerApproachDemo.txt
* Author Name : Capgemini
* Desc : Program to sort the element using Bubble sort algorithm
* Version : 1.0
* Last Modified Date : 01-May-2016
* Change Description : Description about the changes implemented
*********************************************************************/
BEGIN
DECLARE arr[10] AS INETEGER ARRAY AND INITIALISE AS [66,46,20,89,44,22,11,33,45,22]
DECLARE index AS INETEGER INITIALIZE AS 0
bubbleSort(arr,10)
END
/*********************************************************************
* Module Name : bubbleSort()
* Input Parameters : array
* Return Type : void
* Author : Capgemini
* Creation Date : 01-May-2016
* Description : sorting the array by bubble sort algorithm
*********************************************************************/
SUB bubbleSort(arr,n)
FOR index = 0 TO n-1
FOR index1 = 0 TO n-1-index
IF arr[index1] > arr[index1+1] THEN
//swap(arr[index1],arr[index1+1])
temp = arr[index1]
arr[index1]=arr[index1+1]
arr[index1+1] = temp
END IF
END FOR
END SUB
Dry run
---------
66, 46, 20, 89, 44, 22, 11, 33, 45, 22
Iteration 1
1
66, 46, 20, 89, 44, 22, 11, 33, 45, 22
2
46, 66, 20, 89, 44, 22, 11, 33, 45, 22
3
46, 20, 66, 89, 44, 22, 11, 33, 45, 22
4
46, 20, 66, 89, 44, 22, 11, 33, 45, 22
5
46, 20, 66, 44, 89, 22, 11, 33, 45, 22
6
46, 20, 66, 44, 22, 89, 11, 33, 45, 22
7
46, 20, 66, 44, 22, 11, 89, 33, 45, 22
8
46, 20, 66, 44, 22, 11, 33, 89, 45, 22
9
46, 20, 66, 44, 22, 11, 33, 45, 89, 22
10
46, 20, 66, 44, 22, 11, 33, 45, 22, 89
Iteration 2
1
46, 20, 66, 44, 22, 11, 33, 45, 22, 89
2
20, 46, 66, 44, 22, 11, 33, 45, 22, 89
3
20, 46, 66, 44, 22, 11, 33, 45, 22, 89
4
20, 46, 44, 66, 22, 11, 33, 45, 22, 89
5
20, 46, 44, 22, 66, 11, 33, 45, 22, 89
6
20, 46, 44, 22, 11, 66, 33, 45, 22, 89
7
20, 46, 44, 22, 11, 33, 66, 45, 22, 89
8
20, 46, 44, 22, 11, 33, 45, 66, 22, 89
9
20, 46, 44, 22, 11, 33, 45, 22, 66, 89
=========================================================================================================
/*********************************************************************
* File : LinearSearchDemo.txt
* Author Name : Capgemini
* Desc : Program to search an element using linear search algorithm
* Version : 1.0
* Last Modified Date : 01-May-2016
* Change Description : Description about the changes implemented
*********************************************************************/
BEGIN
DECLARE arr[10] AS INETEGER ARRAY AND INITIALISE AS [11,44,22,55,44,22,66,33,88,22]
DECLARE index AS INETEGER INITIALIZE AS 0
DECLARE sum AS INETEGER INITIALIZE AS 0
DECLARE element AS INETEGER
PRINT "Enter searching element :"
ACCEPT element
result = linearSearch(arr,10,element)
IF result== -1 THEN
PRINT "Element not found"
ELSE
PRINT "Element found at ", result
END IF
END
/*********************************************************************
* Module Name : linearSearch()
* Input Parameters : array,int,int
* Return Type : int
* Author : Capgemini
* Creation Date : 01-May-2016
* Description : searching the element by linear search algorithm
*********************************************************************/
SUB linearSearch(arr,n,ele)
FOR index = 0 TO n-1
IF ele == arr[index] THEN
RETURN index
END IF
END FOR
RETURN -1
END SUB
================================================================================
/*********************************************************************
* File : DefensiveProgramming.txt
* Author Name : Capgemini
* Desc : Program to apply discount on productprice
* Version : 1.0
* Last Modified Date : 28-Feb-2015
* Change Description : Description about the changes implemented
*********************************************************************/
BEGIN
DECLARE productId AS INTEGER
DECLARE discount AS REAL
PROMPT "Enter productId" AND STORE IN productId
IF(isValid(productId)) THEN
PROMPT "Enter discount" AND STORE IN discount
IF(isValid(discount)) THEN
applyDiscount(productId,discount);
ELSE
PRINT"Discount value should contain numbers"
END IF
ELSE
PRINT"Product Id should contain numbers"
END IF
END
/*********************************************************************
* Module Name : applyDiscount()
* Input Parameters : productId, discount
* Return Type : INTEGER
* Author : Capgemini
* Creation Date : 28-Feb-2015
* Description : Applying discount on the product price
*********************************************************************/
SUB applyDiscount(productId,discount)
DECLARE result AS INTEGER
result=getProductPrice(productId)*discount
IF(result==-1) THEN
PRINT "Product doesn’t exist with this id"+ productId
ELSE
PRINT "Product Price" + result;
END IF
END SUB
/*********************************************************************
* Module Name : getProductPrice()
* Input Parameters : productId
* Return Type : INTEGER
* Author : Capgemini
* Creation Date : 28-Feb-2015
* Description : Based on productId, fetching product price if productId exists, else errorcode -1 will be returned *********************************************************************/
SUB getProductPrice(productId)
DECLARE errorcode AS INTEGER AND STORE 0
IF(elementfound(productId)) THEN
RETURN productPrice
ELSE
errorcode = -1
RETURN errocode;
END IF
END SUB
/*********************************************************************
* Module Name : isValid()
* Input Parameters : data
* Return Type : BOOLEAN
* Author : Capgemini
* Creation Date : 28-Feb-2015
* Description : To validate data for accepting only digits
*********************************************************************/
SUB isValid(data)
IF(isDigits(data)) THEN
RETURN true
ELSE
RETURN false
END IF
END SUB
===/*********************************************************************
* File : DefensiveProgramming.txt
* Author Name : Capgemini
* Desc : Program to apply discount on productprice
* Version : 1.0
* Last Modified Date : 28-Feb-2015
* Change Description : Description about the changes implemented
*********************************************************************/
BEGIN
DECLARE productId AS INTEGER
DECLARE discount AS REAL
PROMPT "Enter productId" AND STORE IN productId
IF(isValid(productId)) THEN
PROMPT "Enter discount" AND STORE IN discount
IF(isValid(discount)) THEN
applyDiscount(productId,discount);
ELSE
PRINT "Discount value should contain numbers"
END IF
ELSE
PRINT "Product Id should contain numbers"
END IF
END
/*********************************************************************
* Module Name : applyDiscount()
* Input Parameters : productId, discount
* Return Type : INTEGER
* Author : Capgemini
* Creation Date : 28-Feb-2015
* Description : Applying discount on the product price
*********************************************************************/
SUB applyDiscount(productId,discount)
DECLARE result AS INTEGER
DECLARE price AS REAL
price=getProductPrice(productId)
result=price - (price*discount)/100
PRINT "Product Price" + result;
EXCEPTION
WHEN NoSuchElement THEN
PRINT errormessage //Errormessage returned from exception
END SUB
/*********************************************************************
* Module Name : getProductPrice()
* Input Parameters : productId
* Return Type : INTEGER
* Author : Capgemini
* Creation Date : 28-Feb-2015
* Description : Based on productId, fetching product price if productId exists, else exception will be raised *********************************************************************/
SUB getProductPrice(productId)
DECLARE errorcode AS INTEGER AND STORE 0
IF(elementfound(productId)) THEN
RETURN productPrice
ELSE
RAISE NoSuchElement("Product doesn’t exist with this id"+ productId)
END IF
END SUB
/*********************************************************************
* Module Name : isValid()
* Input Parameters : data
* Return Type : BOOLEAN
* Author : Capgemini
* Creation Date : 28-Feb-2015
* Description : To validate data for accepting only digits
*********************************************************************/
SUB isValid(data)
IF(isDigits(data)) THEN
RETURN true
ELSE
RETURN false
END IF
END SUB
==================================================================
--Emp
DROP TABLE EMP;
CREATE TABLE EMP(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902,
TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698,
TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839,
TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839,
TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839,
TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566,
TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL,
TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788,
TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782,
TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
--Designation Masters
DROP TABLE DESIGNATION_MASTER CASCADE CONSTRAINTS;
DROP TABLE DESIGNATION_MASTERs CASCADE CONSTRAINTS;
CREATE TABLE Designation_Master(
Design_Code NUMBER(3) PRIMARY KEY,
Design_Name VARCHAR2(50) UNIQUE);
INSERT INTO designation_master VALUES(101,'HOD');
INSERT INTO designation_master VALUES(102,'Professor');
INSERT INTO designation_master VALUES(103,'Reader');
INSERT INTO designation_master VALUES(104,'Sr.Lecturer');
INSERT INTO designation_master VALUES(105,'Lecturer');
INSERT INTO designation_master VALUES(106,'Director');
--Department Masters
DROP TABLE DEPARTMENT_MASTER CASCADE CONSTRAINTS;
DROP TABLE DEPARTMENT_MASTERs CASCADE CONSTRAINTS;
CREATE TABLE Department_Master(
Dept_code NUMBER(2) PRIMARY KEY,
Dept_Name VARCHAR2(50) UNIQUE);
INSERT INTO department_master VALUES(10,'Computer Science');
INSERT INTO department_master VALUES(20,'Electricals');
INSERT INTO department_master VALUES(30,'Electronics');
INSERT INTO department_master VALUES(40,'Mechanics');
INSERT INTO department_master VALUES(50,'Robotics');
--Student Masters
DROP TABLE STUDENT_MASTER CASCADE CONSTRAINTS;
DROP TABLE STUDENT_MASTERs CASCADE CONSTRAINTS;
CREATE TABLE Student_Master(
Student_Code NUMBER(6) PRIMARY KEY,
Student_Name VARCHAR2(50) NOT NULL,
Dept_Code NUMBER(2) REFERENCES Department_Master(dept_code),
Student_Dob DATE,
Student_Address VARCHAR2(240));
INSERT INTO student_master VALUES(1001,'Amit',10,'11-Jan-80','chennai');
INSERT INTO student_master VALUES(1002,'Ravi',10,'1-Nov-81','New Delhi');
INSERT INTO student_master VALUES(1003,'Ajay',20,'13-Jan-82',null);
INSERT INTO student_master VALUES(1004,'Raj',30,'14-Jan-79','Mumbai');
INSERT INTO student_master VALUES(1005,'Arvind',40,'15-Jan-83','Bangalore');
INSERT INTO student_master VALUES(1006,'Rahul',50,'16-Jan-81','Delhi');
INSERT INTO student_master VALUES(1007,'Mehul',20,'17-Jan-82','Chennai');
INSERT INTO student_master VALUES(1008,'Dev',10,'11-Mar-81','Bangalore');
INSERT INTO student_master VALUES(1009,'Vijay',30,'19-Jan-80','Bangalore');
INSERT INTO student_master VALUES(1010,'Rajat',40,'20-Jan-80','Bangalore');
INSERT INTO student_master VALUES(1011,'Sunder',50,'21-Jan-80','Chennai');
INSERT INTO student_master VALUES(1012,'Rajesh', 30,'22-Jan-80',null);
INSERT INTO student_master VALUES(1013,'Anil',20,'23-Jan-80','Chennai');
INSERT INTO student_master VALUES(1014,'Sunil',10,'15-Feb-85', null);
INSERT INTO student_master VALUES(1015,'Kapil',40,'18-Mar-81','Mumbai');
INSERT INTO student_master VALUES(1016,'Ashok',40,'26-Nov-80',null);
INSERT INTO student_master VALUES(1017,'Ramesh',30,'27-Dec-80',null);
INSERT INTO student_master VALUES(1018,'Amit Raj',50,'28-Sep-80','New Delhi');
INSERT INTO student_master VALUES(1019,'Ravi Raj',50,'29-May-81','New Delhi');
INSERT INTO student_master VALUES(1020,'Amrit',10,'11-Nov-80',null);
INSERT INTO student_master VALUES(1021,'Sumit',20,'1-Jan-80','Chennai');
--Student Marks
DROP TABLE STUDENT_MARKS CASCADE CONSTRAINTS;
CREATE TABLE Student_Marks(
Student_Code NUMBER (6) REFERENCES student_Master(student_code),
Student_Year NUMBER not null,
Subject1 NUMBER (3),
Subject2 NUMBER (3),
Subject3 NUMBER (3));
INSERT INTO student_marks VALUES(1001, 2010, 55,45,78);
INSERT INTO student_marks VALUES(1002, 2010, 66,74,88);
INSERT INTO student_marks VALUES(1003, 2010, 87,54,65);
INSERT INTO student_marks VALUES(1004, 2010, 65,64,90);
INSERT INTO student_marks VALUES(1005, 2010, 78,88,65);
INSERT INTO student_marks VALUES(1006, 2010, 65,86,54);
INSERT INTO student_marks VALUES(1007, 2010, 67,79,49);
INSERT INTO student_marks VALUES(1008, 2010, 72,55,55);
INSERT INTO student_marks VALUES(1009, 2010, 71,59,58);
INSERT INTO student_marks VALUES(1010, 2010, 68,44,92);
INSERT INTO student_marks VALUES(1011, 2010, 89,96,78);
INSERT INTO student_marks VALUES(1012, 2010, 78,56,55);
INSERT INTO student_marks VALUES(1013, 2010, 75,58,65);
INSERT INTO student_marks VALUES(1014, 2010, 73,74,65);
INSERT INTO student_marks VALUES(1015, 2010, 66,45,74);
INSERT INTO student_marks VALUES(1016, 2010, 68,78,74);
INSERT INTO student_marks VALUES(1017, 2010, 69,44,52);
INSERT INTO student_marks VALUES(1018, 2010, 65,78,56);
INSERT INTO student_marks VALUES(1019, 2010, 78,58,74);
INSERT INTO student_marks VALUES(1020, 2010, 45,55,65);
INSERT INTO student_marks VALUES(1021, 2010, 78,79,78);
INSERT INTO student_marks VALUES(1001, 2011, 68,44,92);
INSERT INTO student_marks VALUES(1002, 2011, 89,96,78);
INSERT INTO student_marks VALUES(1003, 2011, 78,56,55);
INSERT INTO student_marks VALUES(1004, 2011, 75,58,65);
INSERT INTO student_marks VALUES(1005, 2011, 73,74,65);
INSERT INTO student_marks VALUES(1006, 2011, 66,45,74);
INSERT INTO student_marks VALUES(1007, 2011, 68,78,74);
INSERT INTO student_marks VALUES(1008, 2011, 69,44,52);
INSERT INTO student_marks VALUES(1009, 2011, 65,78,56);
INSERT INTO student_marks VALUES(1010, 2011, 78,58,74);
INSERT INTO student_marks VALUES(1011, 2011, 45,55,65);
INSERT INTO student_marks VALUES(1012, 2011, 78,79,78);
INSERT INTO student_marks VALUES(1013, 2011, 66,74,88);
INSERT INTO student_marks VALUES(1014, 2011, 65,64,90);
INSERT INTO student_marks VALUES(1015, 2011, 78,88,65);
INSERT INTO student_marks VALUES(1016, 2011, 65,86,54);
INSERT INTO student_marks VALUES(1017, 2011, 67,79,49);
INSERT INTO student_marks VALUES(1018, 2011, 72,55,55);
INSERT INTO student_marks VALUES(1019, 2011, 71,59,58);
INSERT INTO student_marks VALUES(1020, 2011, 55,45,78);
INSERT INTO student_marks VALUES(1021, 2011, 87,54,65);
--Data to be repeated for 2 more years
--Staff Masters
DROP TABLE STAFF_MASTER CASCADE CONSTRAINTS;
DROP TABLE STAFF_MASTERs CASCADE CONSTRAINTS;
CREATE TABLE staff_Master(
Staff_Code number(8) PRIMARY KEY,
Staff_Name varchar2(50) NOT NULL,
Design_Code REFERENCES Designation_Master(design_code),
Dept_Code REFERENCES Department_Master(dept_code),
Staff_dob DATE,
Hiredate DATE,
Mgr_code NUMBER(8),
Staff_sal NUMBER (10,2),
Staff_address VARCHAR2(240));
INSERT INTO staff_master
VALUES(100001,'Arvind',102,30,'15-Jan-80','15-Jan-03',100006,17000,'Bangalore');
INSERT INTO staff_master
VALUES(100002,'Shyam',102,20,'18-Feb-80','17-Feb-02',100007,20000,'Chennai');
INSERT INTO staff_master
VALUES(100003,'Mohan',102,10,'23-Mar-80','19-Jan-02',100006,24000,'Mumbai');
INSERT INTO staff_master
VALUES(100004,'Anil',102,20,'22-Apr-77','11-Mar-01',100006,20000,'Hyderabad');
INSERT INTO staff_master
VALUES(100005,'John',106,10,'22-May-76','21-Jan-01',100007,32000,'Bangalore');
INSERT INTO staff_master
VALUES(100006,'Allen',103,30,'22-Jan-80','23-Apr-01',100005,42000,'Chennai');
INSERT INTO staff_master
VALUES(100007,'Smith',103,20,'19-Jul-73','12-Mar-02',100005,62000,'Mumbai');
INSERT INTO staff_master
VALUES(100008,'Raviraj',102,40,'17-Jun-80','11-Jan-03',100006,18000,'Bangalore');
INSERT INTO staff_master
VALUES(100009,'Rahul',102,20,'16-Jan-78','11-Dec-03',100006,22000,'Hyderabad');
INSERT INTO staff_master
VALUES(100010,'Ram',103,30,'17-Jan-79','17-Jan-02',100007,32000,'Bangalore');
--Book Masters
DROP TABLE BOOK_MASTER CASCADE CONSTRAINTS;
DROP TABLE BOOK_MASTERs CASCADE CONSTRAINTS;
CREATE TABLE Book_Master(
Book_code NUMBER(10) PRIMARY KEY,
Book_name VARCHAR2(50) NOT NULL,
Book_pub_year NUMBER,
Book_pub_author VARCHAR2 (50) NOT NULL);
INSERT INTO book_master VALUES(10000001,'Let Us C++',2000,'Yashavant Kanetkar');
INSERT INTO book_master VALUES(10000002,'Mastersing VC++',2005,'P.J Allen');
INSERT INTO book_master VALUES(10000003,'JAVA Complete Reference',2004,'H.Schild');
INSERT INTO book_master VALUES(10000004,'J2EE Complete Reference',2000,'H. Schild');
INSERT INTO book_master VALUES(10000005,'Relational DBMS',2000,'B.C. Desai');
INSERT INTO book_master VALUES(10000006,'Let Us C',2000, 'Yashavant Kanetkar');
INSERT INTO book_master VALUES(10000007,'Intoduction To Algorithams',2001,'Cormen');
INSERT INTO book_master VALUES(10000008,'Computer Networks',2000,'Tanenbaum');
INSERT INTO book_master VALUES(10000009,'Introduction to O/S',2001,'Millan');
--Book Transactions
DROP TABLE BOOK_TRANSACTIONS;
CREATE TABLE Book_transactions(
Book_code NUMBER(10) REFERENCES Book_Master(Book_code),
Student_code NUMBER(6) REFERENCES Student_Master(student_code),
Staff_code number(8) REFERENCES Staff_Master(staff_code),
Book_issue_Date date not null,
Book_expected_return_date date not null,
Book_actual_return_date date);
INSERT INTO book_transactions
VALUES(10000006,1012,NULL,'02-Feb-2011','09-Feb-2011',NULL);
INSERT INTO book_transactions
VALUES(10000008,NULL,100006,'10-Mar-2011','17-Mar-2011','15-Mar-2011');
INSERT INTO book_transactions
VALUES(10000009,NULL,100010,'01-Apr-2011','08-Apr-2011','10-Apr-2011');
INSERT INTO book_transactions
VALUES(10000004,1015,NULL,'12-Feb-2011','19-Feb-2011',NULL);
INSERT INTO book_transactions
VALUES(10000005,NULL,100007,'14-Mar-2011','21-Mar-2011','21-Mar-2011');
INSERT INTO book_transactions
VALUES(10000007,NULL,100007,'01-Apr-2011','07-Apr-2011','06-Apr-2011');
INSERT INTO book_transactions
VALUES(10000007,NULL,100006,'01-Apr-2010','07-Apr-2010','06-Apr-2010');
INSERT INTO book_transactions
VALUES(10000005,1009,NULL,'31-May-2011','08-JUN-2011','08-JUN-2011');
=======================================================================================
sql
SQL> SELECT lower('Oracle') FROM dual;
LOWER(
------
oracle
SQL> SELECT upper('Oracle') FROM dual;
UPPER(
------
ORACLE
SQL> SELECT instr('Oracle','a') FROM dual;
INSTR('ORACLE','A')
-------------------
3
SQL> SELECT concat('Oracle','for') FROM dual;
CONCAT('O
---------
Oraclefor
SQL> SELECT concat(concat('Oracle',' for'),' developers') FROM dual;
CONCAT(CONCAT('ORACLE
---------------------
Oracle for developers
SQL> SELECT 'Oracle '||'for '||'Developers' FROM dual;
'ORACLE'||'FOR'||'DEV
---------------------
Oracle for Developers
SQL> SELECT substr('Oracle',2) FROM dual;
SUBST
-----
racle
SQL> SELECT substr('Oracle',2,2) FROM dual;
SU
--
ra
SQL> SELECT length('Oracle for Developers') FROM dual;
LENGTH('ORACLEFORDEVELOPERS')
-----------------------------
21
SQL> SELECT ltrim(' Oracle for Developers') FROM dual;
LTRIM('ORACLEFORDEVEL
---------------------
Oracle for Developers
SQL> SELECT rtrim(' Oracle for Developers ') FROM dual;
RTRIM('ORACLEFORDEVELOPER
-------------------------
Oracle for Developers
SQL> SELECT lpad('Oracle',10,'*') FROM dual;
LPAD('ORAC
----------
****Oracle
SQL> SELECT rpad('Oracle',10,'*') FROM dual;
RPAD('ORAC
----------
Oracle****
SQL> SELECT rpad(ename,15,'*') FROM employee;
RPAD(ENAME,15,'
---------------
king***********
blake**********
clark**********
allen**********
ward***********
jones**********
martin*********
scott**********
8 rows selected.
SQL> SELECT lpad('*',ename,'*') FROM employee;
SELECT lpad('*',ename,'*') FROM employee
*
ERROR at line 1:
ORA-01722: invalid number
SQL> SELECT replace(ename,length(ename),lpad('*',ename,'*')) FROM employee;
SELECT replace(ename,length(ename),lpad('*',ename,'*')) FROM employee
*
ERROR at line 1:
ORA-01722: invalid number
SQL> spool off
=========================================================================
SQL> SELECT TO_CHAR(2560,'$9,999.0') FROM dual;
TO_CHAR(2
---------
$2,560.0
SQL> SELECT TO_CHAR(2560,'$2,2222.0') FROM dual;
SELECT TO_CHAR(2560,'$2,2222.0') FROM dual
*
ERROR at line 1:
ORA-01481: invalid number format model
SQL> SELECT TO_CHAR(2560,'$0,00,000.00') FROM dual;
TO_CHAR(2560,
-------------
$0,02,560.00
SQL> SELECT TO_CHAR(sal,'$9,999.00') FROM employee;
TO_CHAR(SA
----------
$5,100.00
$2,850.00
$2,450.00
$1,600.00
$1,250.00
$2,975.00
$1,250.00
$3,000.00
8 rows selected.
SQL> SELECT TO_CHAR(sysdate,'DD MONTH YY') FROM dual;
TO_CHAR(SYSDATE
---------------
20 AUGUST 14
SQL> SELECT TO_CHAR(sysdate,'DDth MONTH YY') FROM dual;
TO_CHAR(SYSDATE,'
-----------------
20TH AUGUST 14
SQL> SELECT TO_CHAR(sysdate,'DDspth MONTH YY') FROM dual;
TO_CHAR(SYSDATE,'DDSPTHMONT
---------------------------
TWENTIETH AUGUST 14
SQL> SELECT TO_DATE('20TH MAY 14','DDth MONTH YY') FROM dual;
SELECT TO_DATE('20TH MAY 14','DDth MONTH YY') FROM dual
*
ERROR at line 1:
ORA-01861: literal does not match format string
SQL> SELECT TO_DATE('20TH MAY 14','DDth MONTH YY') FROM dual;
SELECT TO_DATE('20TH MAY 14','DDth MONTH YY') FROM dual
*
ERROR at line 1:
ORA-01861: literal does not match format string
SQL> select TO_DATE ('May 22,2014','Month DD, YYYY') FROM dual;
TO_DATE('
---------
22-MAY-14
SQL> select TO_DATE ('May 22,2014','Month DD, YYYY') FROM dual;
TO_DATE('
---------
22-MAY-14
SQL> SELECT TO_CHAR(sysdate,'D') FROM dual;
T
-
4
SQL> SELECT TO_CHAR(sysdate,'d') FROM dual;
T
-
4
SQL> SELECT TO_CHAR(sysdate,'DD') FROM dual;
TO
--
20
SQL> spool off
===========================================================
SQL> SELECT ename,lpad('*',length(ename),'*') as e FROM employee;
ENAME E
---------- ------------------------------
king ****
blake *****
clark *****
allen *****
ward ****
jones *****
martin ******
scott *****
8 rows selected.
SQL> SELECT sysdate FROM dual
2 ;
SYSDATE
---------
20-AUG-14
SQL> SELECT current_date FROM dual;
CURRENT_D
---------
20-AUG-14
SQL> SELECT sysdate+7 FROM dual;
SYSDATE+7
---------
27-AUG-14
SQL> SELECT sysdate-7 FROM dual;
SYSDATE-7
---------
13-AUG-14
SQL> SELECT ADD_MONTHS(sysdate,3) FROM dual;
ADD_MONTH
---------
20-NOV-14
SQL> SELECT MONTHS_BETWEEN(sysdate,'20-MAY-2014') FROM dual;
MONTHS_BETWEEN(SYSDATE,'20-MAY-2014')
-------------------------------------
3
SQL> SELECT LAST_DAY(sysdate) FROM dual;
LAST_DAY(
---------
31-AUG-14
SQL> SELECT NEXT_DAY(sysdate) FROM dual;
SELECT NEXT_DAY(sysdate) FROM dual
*
ERROR at line 1:
ORA-00909: invalid number of arguments
SQL> SELECT NEXT_DAY(sysdate,3) FROM dual;
NEXT_DAY(
---------
26-AUG-14
SQL> SELECT NEXT_DAY(sysdate,8) FROM dual;
SELECT NEXT_DAY(sysdate,8) FROM dual
*
ERROR at line 1:
ORA-01846: not a valid day of the week
SQL> SELECT NEXT_DAY(sysdate,1) FROM dual;
NEXT_DAY(
---------
24-AUG-14
SQL> SELECT NEXT_DAY(sysdate,2) FROM dual;
NEXT_DAY(
---------
25-AUG-14
SQL> SELECT NEXT_DAY(sysdate,7) FROM dual;
NEXT_DAY(
---------
23-AUG-14
SQL> SELECT NEXT_DAY(sysdate,'friday') FROM dual;
NEXT_DAY(
---------
22-AUG-14
SQL> SELECT NEXT_DAY(sysdate,'wednesday') FROM dual;
NEXT_DAY(
---------
27-AUG-14
SQL> SELECT NEXT_DAY(sysdate+30,'wednesday') FROM dual;
NEXT_DAY(
---------
24-SEP-14
SQL> SELECT EXTRACT(year FROM sysdate) FROM dual;
EXTRACT(YEARFROMSYSDATE)
------------------------
2014
SQL> SELECT EXTRACT(month FROM sysdate) FROM dual;
EXTRACT(MONTHFROMSYSDATE)
-------------------------
8
SQL> SELECT EXTRACT(day FROM sysdate) FROM dual;
EXTRACT(DAYFROMSYSDATE)
-----------------------
20
=====================================================
SQL> set linesize 300
SQL> column sal format A4
SQL> SELECT empno,ename FROM employee WHERE sal>3000;
EMPNO ENAME
---------- ----------
7839 king
SQL> ed
Wrote file afiedt.buf
1* SELECT empno,ename,sal FROM employee WHERE sal>3000
SQL> /
EMPNO ENAME SAL
---------- ---------- ----------
7839 king ##########
SQL> column sal format A30
SQL> /
EMPNO ENAME SAL
---------- ---------- ----------
7839 king ##########
SQL> ed
Wrote file afiedt.buf
1* SELECT empno,ename FROM employee WHERE sal>3000
SQL> /
EMPNO ENAME
---------- ----------
7839 king
SQL> SELECT empno as Employeenumber, ename "Employee Name" FROM employee
2 WHERE sal>3000;
EMPLOYEENUMBER Employee N
-------------- ----------
7839 king
SQL> column "Employee Name" format A30
SQL> /
EMPLOYEENUMBER Employee Name
-------------- ------------------------------
7839 king
SQL>
SQL> SELECT * from employee1;
EMPLOYEE_CODE EMPLOYEE_NAME EMPLOYEE_ EMPLOYE_SALARY TI
------------- -------------------- --------- -------------- --
101 Preetham 10-JAN-10 18000 Mr
102 Aakash 10-NOV-05 4800 Mr
103 Kishore 19-DEC-11 21000 Mr
104 Reena 23-JUN-06 4200 Ms
105 Kailash 05-FEB-04 4600 Mr
106 Sahana 18-NOV-03 5200 Ms
107 Keerthana 25-JUL-06 2700 Ms
7 rows selected.
SQL> SELECT employee_name FROM employee1
2 WHERE employee_name like '%a';
EMPLOYEE_NAME
--------------------
Reena
Sahana
Keerthana
SQL> SELECT employee_name FROM employee1
2 WHERE employee_name like 'k%';
no rows selected
SQL> SELECT employee_name FROM employee1
2 WHERE employee_name like 'K%';
EMPLOYEE_NAME
--------------------
Kishore
Kailash
Keerthana
SQL> SELECT employee_name FROM employee1
2 WHERE employee_name like 'Ree_a';
EMPLOYEE_NAME
--------------------
Reena
SQL> SELECT employee_name FROM employee1
2 WHERE employee_name like 'Ree\_a' escape '\';
no rows selected
SQL> SELECT job FROM employee;
JOB
--------------------------------------------------
president
manager
manager
salesman
salesman
manager
salesman
analyst
8 rows selected.
SQL> SELECT DISTINCT job FROM employee;
JOB
--------------------------------------------------
salesman
president
manager
analyst
SQL> SELECT ename,sal as salary FROM employee;
ENAME SALARY
---------- ----------
king 5100
blake 2850
clark 2450
allen 1600
ward 1250
jones 2975
martin 1250
scott 3000
8 rows selected.
SQL> SELECT ename,sal as salary FROM employee ORDER BY sal;
ENAME SALARY
---------- ----------
martin 1250
ward 1250
allen 1600
clark 2450
blake 2850
jones 2975
scott 3000
king 5100
8 rows selected.
SQL> SELECT ename,sal as salary FROM employee ORDER BY sal desc;
ENAME SALARY
---------- ----------
king 5100
scott 3000
jones 2975
blake 2850
clark 2450
allen 1600
martin 1250
ward 1250
8 rows selected.
SQL> SELECT ename,sal as salary FROM employee ORDER BY 2 desc;
ENAME SALARY
---------- ----------
king 5100
scott 3000
jones 2975
blake 2850
clark 2450
allen 1600
martin 1250
ward 1250
8 rows selected.
SQL> SELECT floor((sysdate-hiredate)\365) FROM employee ORDER BY 1;
SELECT floor((sysdate-hiredate)\365) FROM employee ORDER BY 1
*
ERROR at line 1:
ORA-00911: invalid character
SQL> SELECT floor((sysdate-hiredate)/365) FROM employee ORDER BY 1;
FLOOR((SYSDATE-HIREDATE)/365)
-----------------------------
31
32
32
33
33
33
33
33
8 rows selected.
SQL> SELECT employee_name FROM employee1 ORDER BY 1;
EMPLOYEE_NAME
--------------------
Aakash
Kailash
Keerthana
Kishore
Preetham
Reena
Sahana
7 rows selected.
SQL> desc employee1;
Name
Null? Type
-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------- -------- --------------------------------------------------------
------------------------------------------------------------
EMPLOYEE_CODE
NOT NULL NUMBER
EMPLOYEE_NAME
VARCHAR2(20)
EMPLOYEE_DATE_OF_JOINING
DATE
EMPLOYE_SALARY
NUMBER
TITLE
VARCHAR2(2)
SQL> SELECT ename,sal as salary FROM employee ORDER BY 2 desc;
ENAME SALARY
---------- ----------
king 5100
scott 3000
jones 2975
blake 2850
clark 2450
allen 1600
martin 1250
ward 1250
8 rows selected.
SQL> SELECT ename,sal as salary FROM employee ORDER BY 2 desc,ename desc;
ENAME SALARY
---------- ----------
king 5100
scott 3000
jones 2975
blake 2850
clark 2450
allen 1600
ward 1250
martin 1250
8 rows selected.
SQL> SELECT sal+comm FROM employee;
SAL+COMM
----------
5220
2950
2552
1900
1750
3175
2650
3220
8 rows selected.
SQL> SELECT sal||' '|| comm FROM employee;
SAL||''||COMM
--------------------------------------------------------------------------------
-
5100 120
2850 100
2450 102
1600 300
1250 500
2975 200
1250 1400
3000 220
8 rows selected.
SQL>
======================================================================================
SQL> SELECT floor((sysdate-hiredate)\365) FROM employee;
SELECT floor((sysdate-hiredate)\365) FROM employee
*
ERROR at line 1:
ORA-00911: invalid character
SQL> SELECT floor((sysdate-hiredate)/365) FROM employee;
FLOOR((SYSDATE-HIREDATE)/365)
-----------------------------
32
33
33
33
33
33
32
31
8 rows selected.
SQL> SELECT (sysdate-hiredate)/365 FROM employee;
(SYSDATE-HIREDATE)/365
----------------------
32.7772473
33.3251925
33.2183432
33.5169733
33.5114939
33.4046446
32.9142336
31.7169733
8 rows selected.
SQL> SELECT sal as salary FROM employee;
SALARY
----------
5100
2850
2450
1600
1250
2975
1250
3000
8 rows selected.
SQL> SELECT sum(sal) as TotalSalary FROM employee;
TOTALSALARY
-----------
20475
SQL> SELECT min(sal) FROM employee;
MIN(SAL)
----------
1250
SQL> SELECT max(sal) FROM employee;
MAX(SAL)
----------
5100
SQL> SELECT avg(sal) FROM employee;
AVG(SAL)
----------
2559.375
SQL> SELECT sum(sal)*12 as TotalSalary FROM employee;
TOTALSALARY
-----------
245700
SQL> SELECT count(*) FROM employee;
COUNT(*)
----------
8
SQL> SELECT count(mgr) FROM employee;
COUNT(MGR)
----------
7
SQL> SELECT count(*),deptno FROM employee;
SELECT count(*),deptno FROM employee
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> SELECT count(*) FROM employee;
COUNT(*)
----------
8
SQL> SELECT count(deptno) FROM employee;
COUNT(DEPTNO)
-------------
8
SQL> SELECT count(*) FROM employee GROUP BY deptno;
COUNT(*)
----------
3
2
3
SQL> SELECT count(*), deptno FROM employee GROUP BY deptno;
COUNT(*) DEPTNO
---------- ----------
3 30
2 20
3 10
SQL> SELECT count(*), deptno FROM employee GROUP BY deptno ORDER BY 2;
COUNT(*) DEPTNO
---------- ----------
3 10
2 20
3 30
SQL> SELECT count(*), deptno FROM employee GROUP BY deptno,sal ORDER BY 2;
COUNT(*) DEPTNO
---------- ----------
1 10
1 10
1 10
1 20
1 20
2 30
1 30
7 rows selected.
SQL> SELECT count(*), deptno FROM employee GROUP BY deptno HAVING count(*)>2 ORD
ER BY 2;
COUNT(*) DEPTNO
---------- ----------
3 10
3 30
SQL> SELECT count(*), job FROM employee
2 GROUP BY job HAVING min(sal)>1500;
COUNT(*) JOB
---------- --------------------------------------------------
1 president
3 manager
1 analyst
SQL>
================================================================================================================================
SQL> SELECT ename,dname FROM employee emp,dept d
2 WHERE emp.deptno=d.deptno;
ENAME DNAME
---------- --------------
clark ACCOUNTING
blake ACCOUNTING
king ACCOUNTING
scott RESEARCH
jones RESEARCH
martin SALES
ward SALES
allen SALES
8 rows selected.
SQL> SELECT s.staff_name,s.staff_sal,sl.grade
2 FROM staff_master s,salgrade sl
3 WHERE staff_sal BETWEEN sl.losal and sl.hisal
4 ;
FROM staff_master s,salgrade sl
*
ERROR at line 2:
ORA-00942: table or view does not exist
SQL> ed
Wrote file afiedt.buf
1 SELECT s.staff_name,s.staff_sal,sl.grade
2 FROM staff_masters s,salgrade sl
3* WHERE staff_sal BETWEEN sl.losal and sl.hisal
4 /
STAFF_NAME STAFF_SAL GRADE
-------------------------------------------------- ---------- -----
Sita 3322 C
SQL> sELECT empno,ename,mgr FROM employee;
EMPNO ENAME MGR
---------- ---------- ----------
7839 king
7698 blake 7839
7782 clark 7839
7499 allen 7698
7521 ward 7698
7566 jones 7839
7654 martin 7698
7788 scott 7566
8 rows selected.
SQL> SELECT emp.ename,m.ename FROM employee emp,employee m
2 WHERE emp.mgr=m.empno;
ENAME ENAME
---------- ----------
scott jones
ward blake
martin blake
allen blake
blake king
jones king
clark king
7 rows selected.
SQL> SELECT emp.ename "Emp Name",m.ename "MGR Name" FROM employee emp,employee m
2 WHERE emp.mgr=m.empno;
Emp Name MGR Name
---------- ----------
scott jones
ward blake
martin blake
allen blake
blake king
jones king
clark king
7 rows selected.
SQL> SELECT ename,dname FROM employee emp,dept d
2 WHERE emp.deptno=d.deptno;
ENAME DNAME
---------- --------------
clark ACCOUNTING
blake ACCOUNTING
king ACCOUNTING
scott RESEARCH
jones RESEARCH
martin SALES
ward SALES
allen SALES
8 rows selected.
SQL> SELECT ename,dname FROM employee emp,dept d
2 WHERE emp.deptno=d.deptno(+);
ENAME DNAME
---------- --------------
clark ACCOUNTING
blake ACCOUNTING
king ACCOUNTING
scott RESEARCH
jones RESEARCH
martin SALES
ward SALES
allen SALES
8 rows selected.
SQL> SELECT ename,dname FROM employee emp,dept d
2 WHERE emp.deptno(+)=d.deptno;
ENAME DNAME
---------- --------------
king ACCOUNTING
blake ACCOUNTING
clark ACCOUNTING
allen SALES
ward SALES
jones RESEARCH
martin SALES
scott RESEARCH
s
ee
HR
ENAME DNAME
---------- --------------
sdf
OPERATIONS
gh
Test
nb
16 rows selected.
SQL> SELECT ename,dname FROM employee emp,dept d
2 WHERE emp.deptno(+)=d.deptno;
ENAME DNAME
---------- --------------
king ACCOUNTING
blake ACCOUNTING
clark ACCOUNTING
allen SALES
ward SALES
jones RESEARCH
martin SALES
scott RESEARCH
s
ee
HR
ENAME DNAME
---------- --------------
sdf
OPERATIONS
gh
Test
nb
16 rows selected.
SQL> SELECT ename,dname FROM employee emp,dept d
2 WHERE emp.deptno=d.deptno(+);
ENAME DNAME
---------- --------------
clark ACCOUNTING
blake ACCOUNTING
king ACCOUNTING
scott RESEARCH
jones RESEARCH
martin SALES
ward SALES
allen SALES
8 rows selected.
SQL> INSERT insert into employee(empno,ename,job,mgr,hiredate,sal,comm) values (7850,'Test','president',7839,'17-nov-81',5000,120);
INSERT insert into employee(empno,ename,job,mgr,hiredate,sal,comm) values (7850,'Test','president',7839,'17-nov-81',5000,120)
*
ERROR at line 1:
ORA-00925: missing INTO keyword
SQL> insert into employee(empno,ename,job,mgr,hiredate,sal,comm) values (7850,'Test','president',7839,'17-nov-81',5000,120);
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT ename,dname FROM employee emp,dept d
2 WHERE emp.deptno=d.deptno(+);
ENAME DNAME
---------- --------------
clark ACCOUNTING
blake ACCOUNTING
king ACCOUNTING
scott RESEARCH
jones RESEARCH
martin SALES
ward SALES
allen SALES
Test
9 rows selected.
SQL> SELECT ename,dname FROM employee emp,dept d
2 WHERE emp.deptno=d.deptno(+);
ENAME DNAME
---------- --------------
clark ACCOUNTING
blake ACCOUNTING
king ACCOUNTING
scott RESEARCH
jones RESEARCH
martin SALES
ward SALES
allen SALES
Test
9 rows selected.
SQL> SELECT ename,dname FROM employee emp LEFT OUTER JOIN dept d
2 WHERE emp.deptno=d.deptno;
WHERE emp.deptno=d.deptno
*
ERROR at line 2:
ORA-00905: missing keyword
SQL> SELECT ename,dname FROM employee emp LEFT OUTER JOIN dept d
2 ON emp.deptno=d.deptno;
ENAME DNAME
---------- --------------
clark ACCOUNTING
blake ACCOUNTING
king ACCOUNTING
scott RESEARCH
jones RESEARCH
martin SALES
ward SALES
allen SALES
Test
9 rows selected.
SQL> SELECT ename,dname FROM employee emp LEFT OUTER JOIN dept d
2 ON emp.deptno=d.deptno(+);
ENAME DNAME
---------- --------------
clark ACCOUNTING
blake ACCOUNTING
king ACCOUNTING
scott RESEARCH
jones RESEARCH
martin SALES
ward SALES
allen SALES
Test
9 rows selected.
SQL> SELECT ename,dname FROM employee emp, dept d
2 WHERE emp.deptno=d.deptno(+);
ENAME DNAME
---------- --------------
clark ACCOUNTING
blake ACCOUNTING
king ACCOUNTING
scott RESEARCH
jones RESEARCH
martin SALES
ward SALES
allen SALES
Test
9 rows selected.
SQL> SELECT ename,dname FROM employee emp LEFT OUTER JOIN dept d
2 ON emp.deptno=d.deptno;
ENAME DNAME
---------- --------------
clark ACCOUNTING
blake ACCOUNTING
king ACCOUNTING
scott RESEARCH
jones RESEARCH
martin SALES
ward SALES
allen SALES
Test
9 rows selected.
SQL> SELECT ename,dname FROM employee emp, dept d
2 WHERE emp.deptno(+)=d.deptno;
ENAME DNAME
---------- --------------
king ACCOUNTING
blake ACCOUNTING
clark ACCOUNTING
allen SALES
ward SALES
jones RESEARCH
martin SALES
scott RESEARCH
s
ee
HR
ENAME DNAME
---------- --------------
sdf
OPERATIONS
gh
Test
nb
16 rows selected.
SQL> SELECT ename,dname FROM employee emp RIGHT OUTER JOIN dept d
2 ON emp.deptno=d.deptno;
ENAME DNAME
---------- --------------
king ACCOUNTING
blake ACCOUNTING
clark ACCOUNTING
allen SALES
ward SALES
jones RESEARCH
martin SALES
scott RESEARCH
s
ee
HR
ENAME DNAME
---------- --------------
sdf
OPERATIONS
gh
Test
nb
16 rows selected.
SQL> SELECT ename,dname FROM employee emp FULL OUTER JOIN dept d
2 ON emp.deptno=d.deptno;
ENAME DNAME
---------- --------------
Test
HR
nb
gh
clark ACCOUNTING
blake ACCOUNTING
king ACCOUNTING
scott RESEARCH
jones RESEARCH
martin SALES
ward SALES
ENAME DNAME
---------- --------------
allen SALES
OPERATIONS
s
ee
sdf
Test
17 rows selected.
SQL> SELECT ename,dname FROM employee emp, dept d
2 WHERE emp.deptno(+)=d.deptno(+);
WHERE emp.deptno(+)=d.deptno(+)
*
ERROR at line 2:
ORA-01468: a predicate may reference only one outer-joined table
SQL> SELECT ename,dname FROM employee CROSS JOIN dept;
ENAME DNAME
---------- --------------
Test Test
Test HR
Test nb
Test gh
Test ACCOUNTING
Test RESEARCH
Test SALES
Test OPERATIONS
Test s
Test ee
Test sdf
ENAME DNAME
---------- --------------
king Test
king HR
king nb
king gh
king ACCOUNTING
king RESEARCH
king SALES
king OPERATIONS
king s
king ee
king sdf
ENAME DNAME
---------- --------------
blake Test
blake HR
blake nb
blake gh
blake ACCOUNTING
blake RESEARCH
blake SALES
blake OPERATIONS
blake s
blake ee
blake sdf
ENAME DNAME
---------- --------------
clark Test
clark HR
clark nb
clark gh
clark ACCOUNTING
clark RESEARCH
clark SALES
clark OPERATIONS
clark s
clark ee
clark sdf
ENAME DNAME
---------- --------------
allen Test
allen HR
allen nb
allen gh
allen ACCOUNTING
allen RESEARCH
allen SALES
allen OPERATIONS
allen s
allen ee
allen sdf
ENAME DNAME
---------- --------------
ward Test
ward HR
ward nb
ward gh
ward ACCOUNTING
ward RESEARCH
ward SALES
ward OPERATIONS
ward s
ward ee
ward sdf
ENAME DNAME
---------- --------------
jones Test
jones HR
jones nb
jones gh
jones ACCOUNTING
jones RESEARCH
jones SALES
jones OPERATIONS
jones s
jones ee
jones sdf
ENAME DNAME
---------- --------------
martin Test
martin HR
martin nb
martin gh
martin ACCOUNTING
martin RESEARCH
martin SALES
martin OPERATIONS
martin s
martin ee
martin sdf
ENAME DNAME
---------- --------------
scott Test
scott HR
scott nb
scott gh
scott ACCOUNTING
scott RESEARCH
scott SALES
scott OPERATIONS
scott s
scott ee
scott sdf
99 rows selected.
SQL> spool off
======================================================================================================================================================================================================
SQL> SELECT mgr FROM employee;
MGR
----------
7839
7839
7698
7698
7839
7698
7566
8 rows selected.
SQL> SELECT nvl(mgr,0) FROM employee;
NVL(MGR,0)
----------
0
7839
7839
7698
7698
7839
7698
7566
8 rows selected.
SQL> SELECT NVL2(mgr,0,1) FROM employee;
NVL2(MGR,0,1)
-------------
1
0
0
0
0
0
0
0
8 rows selected.
SQL> SELECT NULLIF(deptno,10) FROM employee;
NULLIF(DEPTNO,10)
-----------------
30
30
20
30
20
8 rows selected.
SQL> SELECT ename,
2 DECODE (deptno, 10, 'Accounting',
3 20, 'Research',
4 30, 'Sales',
5 'Unknown') AS department
6 FROM employee;
ENAME DEPARTMENT
---------- ----------
king Accounting
blake Accounting
clark Accounting
allen Sales
ward Sales
jones Research
martin Sales
scott Research
8 rows selected.
SQL> SELECT ename,
2 CASE deptno
3 WHEN 10 THEN 'Accounting'
4 WHEN 20 THEN 'Research'
5 WHEN 30 THEN 'Sales'
6 ELSE 'Unknown'
7 END AS department
8 FROM employee;
ENAME DEPARTMENT
---------- ----------
king Accounting
blake Accounting
clark Accounting
allen Sales
ward Sales
jones Research
martin Sales
scott Research
8 rows selected.
SQL>
SQL> SELECT sal, CASE
2 WHEN sal < 2000 THEN 'category 1'
3 WHEN sal < 3000 THEN 'category 2'
4 WHEN sal < 4000 THEN 'category 3'
5 ELSE 'category 4'
6 end
7 FROM employee;
SAL CASEWHENSA
---------- ----------
5100 category 4
2850 category 2
2450 category 2
1600 category 1
1250 category 1
2975 category 2
1250 category 1
3000 category 3
8 rows selected.
SQL> spool off
=======================================================
SQL> SELECT ceil(24.567
2 ;
*
ERROR at line 2:
ORA-00907: missing right parenthesis
SQL> SELECT ceil(24.567) FROM dual;
CEIL(24.567)
------------
25
SQL> SELECT floor(24.567) FROM dual;
FLOOR(24.567)
-------------
24
SQL> SELECT round(24.567) FROM dual;
ROUND(24.567)
-------------
25
SQL> SELECT round(24.567,1) FROM dual;
ROUND(24.567,1)
---------------
24.6
SQL> SELECT round(24.567,-1) FROM dual;
ROUND(24.567,-1)
----------------
20
SQL> SELECT truncate(24.567,1) FROM dual;
SELECT truncate(24.567,1) FROM dual
*
ERROR at line 1:
ORA-00904: "TRUNCATE": invalid identifier
SQL> SELECT trunc(24.567,1) FROM dual;
TRUNC(24.567,1)
---------------
24.5
SQL> SELECT power(5,2) FROM dual;
POWER(5,2)
----------
25
SQL> SELECT sqrt(16) FROM dual;
SQRT(16)
----------
4
SQL> SELECT mod(5,2) FROM dual;
MOD(5,2)
----------
1
SQL> SELECT ceil(sal) FROM employee;
CEIL(SAL)
----------
5100
2850
2450
1600
1250
2975
1250
3000
8 rows selected.
SQL>
===================================================================================================================
SQL> CREATE INDEX job_index ON employee(job);
Index created.
SQL> desc USER_INDEXES;
Name Null? Type
----------------------------------------- -------- ----------------------------
INDEX_NAME NOT NULL VARCHAR2(30)
INDEX_TYPE VARCHAR2(27)
TABLE_OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
UNIQUENESS VARCHAR2(9)
COMPRESSION VARCHAR2(8)
PREFIX_LENGTH NUMBER
TABLESPACE_NAME VARCHAR2(30)
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
PCT_THRESHOLD NUMBER
INCLUDE_COLUMN NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
PCT_FREE NUMBER
LOGGING VARCHAR2(3)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE VARCHAR2(40)
INSTANCES VARCHAR2(40)
PARTITIONED VARCHAR2(3)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
BUFFER_POOL VARCHAR2(7)
FLASH_CACHE VARCHAR2(7)
CELL_FLASH_CACHE VARCHAR2(7)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
PCT_DIRECT_ACCESS NUMBER
ITYP_OWNER VARCHAR2(30)
ITYP_NAME VARCHAR2(30)
PARAMETERS VARCHAR2(1000)
GLOBAL_STATS VARCHAR2(3)
DOMIDX_STATUS VARCHAR2(12)
DOMIDX_OPSTATUS VARCHAR2(6)
FUNCIDX_STATUS VARCHAR2(8)
JOIN_INDEX VARCHAR2(3)
IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3)
DROPPED VARCHAR2(3)
VISIBILITY VARCHAR2(9)
DOMIDX_MANAGEMENT VARCHAR2(14)
SEGMENT_CREATED VARCHAR2(3)
SQL> SELECT INDEX_NAME, TABLE_NAME FROM user_indexes;
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
SYS_C0043987 SUBSCRIBER_ACCOUNT_DETAILS
SYS_C0043988 SUBSCRIBER_ACCOUNT_DETAILS
SYS_C0030696 STUDENT_MASTERS
SYS_C00156795 STUDENTS
SYS_C0030701 STAFF_MASTERS
SYS_C0033359 SHOWS
SYS_C00132766 REGIONS
SYS_C0044490 QUESTION_MASTER
SYS_C00151385 PRODUCT31
SYS_C0052489 PRODUCT27
PID23 PRODUCT25
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
TP_INDEX PRODUCT1
V_INDEX PROD
SYS_C0032939 PARTICIPANTS
SYS_C00132769 LOCATIONS
JHIST_EMP_ID_ST_DATE_PK JOB_HISTORY
SYS_C00132773 JOBS
SYS_IL0000102795C00003$$ INVENTORY
FLIGHTNO_PK FLIGHTINFORMATION
E EMP_22
SYS_C00132774 EMPLOYEES
SYS_C0032197 EMPLOYEE1
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
JOB_INDEX EMPLOYEE
SYS_C0079563 EMPLOYEE
SYS_C0032938 WORKSHOP
SYS_C0077695 USERS_23
ID27 USERS27
ID27F USERS27
UID3 USER24
SYS_IL0000154663C00002$$ USER2
SYS_IL0000154659C00003$$ USER1
SYS_C0046444 TRAINEES27
SYS_C0045357 TRAINEES
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
FNAME TBLPRODUCTS
SYS_C0036846 TBLPRODUCTS
SYS_C0030691 DESIGNATION_MASTERS
SYS_C0030692 DESIGNATION_MASTERS
SYS_C00156376 DEPT_COPY
SYS_C0045413 DEPT1
SYS_C0079562 DEPT
SYS_C0030693 DEPARTMENT_MASTERS
SYS_C0030694 DEPARTMENT_MASTERS
SYS_C00132771 DEPARTMENTS
DID DEMO
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
SYS_C0043985 DATASKY_PACKAGES
CUSTID_PK CUSTOMERMASTER
UQ_ID CUSTOMER23
SYS_C00143969 CUSTOMER1
SYS_C00137230 CUSTOMER
CID_PK COURSES
SYS_C00102427 COURSE414
SYS_C00132767 COUNTRIES
SYS_C00107173 COMPLAINT1
SYS_C0058170 COMPLAINT
INAME CATEGORY314
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
SYS_C0036716 CATEGORY314
SYS_C00151251 CATEGORY31
SYS_C0077723 CATEGORY23
SYS_C0052484 CATEGORY
SYS_C0030706 BOOK_MASTERS
SYS_C0092995 BOOKS
BOOK_ID_PK BOOKINGINFORMATION
SYS_C0042625 BOOKING
SYS_IL0000113051C00003$$ BOOK
ABBREVATION_PK AIRPORT
ADDR ADDRESS
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
ACC_PK ACCOUNTMASTER
67 rows selected.
SQL> SELECT ename FROM employee WHERE job='manager';
ENAME
----------
blake
clark
jones
SQL> CREATE UNIQUE INDEX ON emp_22(empno);
CREATE UNIQUE INDEX ON emp_22(empno)
*
ERROR at line 1:
ORA-00953: missing or invalid index name
SQL> CREATE UNIQUE INDEX uniindON emp_22(empno);
CREATE UNIQUE INDEX uniindON emp_22(empno)
*
ERROR at line 1:
ORA-00969: missing ON keyword
SQL> CREATE UNIQUE INDEX uniind ON emp_22(empno);
CREATE UNIQUE INDEX uniind ON emp_22(empno)
*
ERROR at line 1:
ORA-01408: such column list already indexed
SQL> CREATE UNIQUE INDEX uniind ON emp_22(job);
CREATE UNIQUE INDEX uniind ON emp_22(job)
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
SQL> CREATE UNIQUE INDEX uniind ON employee(ename);
Index created.
SQL> CREATE INDEX fnIndex31 ON employee(length(ename));
Index created.
SQL> SELECT ename FROM employee WHERE 3>length(ename);
no rows selected
SQL> SELECT ename FROM employee WHERE length(ename)>2;
ENAME
----------
Test
king
ward
blake
clark
allen
jones
scott
martin
9 rows selected.
SQL> DROP INDEX job_index;
Index dropped.
SQL>
=====================================================================================================================================================================================
SQL> CREATE SEQUENCE idseq;
Sequence created.
SQL> SELECT idseq.nextval FROM dual;
NEXTVAL
----------
1
SQL> SELECT idseq.nextval FROM dual;
NEXTVAL
----------
2
SQL> SELECT idseq.nextval FROM dual;
NEXTVAL
----------
3
SQL> SELECT idseq.currval FROM dual;
CURRVAL
----------
3
SQL> CREATE TABLE dept_details as select * from dept WHERE 1=2;
Table created.
SQL> INSE
SP2-0042: unknown command "INSE" - rest of line ignored.
SQL> INSERT INTO dept_details VALUES(1,'training','chennai');
1 row created.
SQL> INSERT INTO dept_details VALUES(idseq.nextval,'admin','pune');
1 row created.
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- -------------- -------------
14 Test CHN
22 HR CHN
778 nb gj
45 gh hh
10 ACCOUNTING NEWYORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
33 dd dd
83 ee
84 ee
DEPTNO DNAME LOC
---------- -------------- -------------
77 tt uu
66 ee gg
12 s r
1 ee ff
2 sdf fsd
16 rows selected.
SQL> SELECT * FROM dept_details;
DEPTNO DNAME LOC
---------- -------------- -------------
1 training chennai
4 admin pune
SQL> SELECT idseq.nextval FROM dual;
NEXTVAL
----------
5
SQL> INSERT INTO dept_details VALUES(idseq.nextval,'admin','pune');
1 row created.
SQL> commit;
Commit complete.
SQL> INSERT INTO emp_22 VALUES(idseq.nextval,'admin');
1 row created.
SQL> INSERT INTO dept_details VALUES(idseq.nextval,'admin','pune');
1 row created.
SQL> commit;
Commit complete.
SQL> DROP sequence idseq;
Sequence dropped.
SQL> CREATE SEQUENCE idseq
2 INCREMENT BY 5
3 START wITH 2000
4 MAXVALUE 2020
5 NOCYCLE;
Sequence created.
SQL> INSERT INTO dept_details VALUES(idseq.nextval,'admin','pune');
1 row created.
SQL> INSERT INTO dept_details VALUES(idseq.nextval,'admin','pune');
1 row created.
SQL> INSERT INTO dept_details VALUES(idseq.nextval,'admin','pune');
1 row created.
SQL> INSERT INTO dept_details VALUES(idseq.nextval,'admin','pune');
1 row created.
SQL> INSERT INTO dept_details VALUES(idseq.nextval,'admin','pune');
1 row created.
SQL> INSERT INTO dept_details VALUES(idseq.nextval,'admin','pune');
INSERT INTO dept_details VALUES(idseq.nextval,'admin','pune')
*
ERROR at line 1:
ORA-08004: sequence IDSEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated
SQL> ALTER SEQUENCE idseq
2 MAXVALUE 2050
3 ;
Sequence altered.
SQL> INSERT INTO dept_details VALUES(idseq.nextval,'admin','pune');
1 row created.
SQL> drop sequence idseq;
Sequence dropped.
SQL> SELECT * FROM dept_details;
DEPTNO DNAME LOC
---------- -------------- -------------
1 training chennai
4 admin pune
6 admin pune
8 admin pune
2000 admin pune
2005 admin pune
2010 admin pune
2015 admin pune
2020 admin pune
2025 admin pune
10 rows selected.
SQL> spool off
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> SELECT design_code FROM designation_masters WHERE design_name='Professor';
DESIGN_CODE
-----------
102
SQL> SELECT staff_name FROM staff_masters
2 WHERE design_code=(SELECT design_code FROM designation_masters
3 WHERE design_name='Professor');
STAFF_NAME
------------------------------
Arvind
Mohan
Anil
Raviraj
Rahul
Shyam
6 rows selected.
SQL> CREATE TABLE emp_copy31 AS SELECT * FROM employee;
Table created.
SQL> SELECT ename FROM employee;
ENAME
----------
Test
king
blake
clark
allen
ward
jones
martin
scott
9 rows selected.
SQL> SELECT ename FROM emp_copy31;
ENAME
----------
Test
king
blake
clark
allen
ward
jones
martin
scott
9 rows selected.
SQL> CREATE TABLE design_copy AS SELECT * FROM designation_masters WHERE 1=2;
Table created.
SQL> SELECT count(*) FROM designation_masters;
COUNT(*)
----------
6
SQL> SELECT count(*) FROM design_copy;
COUNT(*)
----------
0
SQL> desc designation_masters;
Name Null? Type
----------------------------------------- -------- ----------------------------
DESIGN_CODE NOT NULL NUMBER(3)
DESIGN_NAME VARCHAR2(50)
SQL> desc design_copy;
Name Null? Type
----------------------------------------- -------- ----------------------------
DESIGN_CODE NUMBER(3)
DESIGN_NAME VARCHAR2(50)
SQL> SELECT deptno, avg(sal) FROM employee GROUP BY deptno;
DEPTNO AVG(SAL)
---------- ----------
5000
30 1366.66667
20 2987.5
10 3466.66667
SQL> SELECT deptno, avg(sal) FROM employee GROUP BY deptno HAVING AVG(sal)>2500;
DEPTNO AVG(SAL)
---------- ----------
5000
20 2987.5
10 3466.66667
SQL> SELECT deptno FROM employee GROUP BY deptno HAVING AVG(sal)>2500;
DEPTNO
----------
20
10
SQL> SELECT ename,deptno FROM employee WHERE deptno IN
2 (SELECT deptno FROM employee GROUP BY deptno HAVING AVG(sal)>2500);
ENAME DEPTNO
---------- ----------
scott 20
jones 20
clark 10
blake 10
king 10
SQL> SELECT ename,deptno FROM employee WHERE deptno NOT IN
2 (SELECT deptno FROM employee GROUP BY deptno HAVING AVG(sal)>2500);
no rows selected
SQL> SELECT ename,deptno FROM employee;
ENAME DEPTNO
---------- ----------
Test
king 10
blake 10
clark 10
allen 30
ward 30
jones 20
martin 30
scott 20
9 rows selected.
SQL> DELETE FROM employee WHERE ename='Test';
1 row deleted.
SQL> SELECT ename,deptno FROM employee WHERE deptno NOT IN
2 (SELECT deptno FROM employee GROUP BY deptno HAVING AVG(sal)>2500);
ENAME DEPTNO
---------- ----------
martin 30
ward 30
allen 30
SQL> SELECT ename,deptno FROM employee WHERE sal>ANY
2 (SELECT avg(sal) FROM employee);
ENAME DEPTNO
---------- ----------
king 10
blake 10
jones 20
scott 20
SQL> SELECT ename,deptno FROM employee WHERE sal>ANY
2 (SELECT avg(sal) FROM employee group by job);
ENAME DEPTNO
---------- ----------
king 10
scott 20
jones 20
blake 10
clark 10
allen 30
6 rows selected.
SQL> SELECT avg(sal) FROM employee group by job;
AVG(SAL)
----------
1366.66667
5100
2758.33333
3000
SQL> SELECT job,avg(sal) FROM employee group by job;
JOB AVG(SAL)
-------------------------------------------------- ----------
salesman 1366.66667
president 5100
manager 2758.33333
analyst 3000
SQL> SELECT ename,sal,job FROM employee WHERE sal>ANY
2 (SELECT avg(sal) FROM employee group by job);
ENAME SAL JOB
---------- ---------- --------------------------------------------------
king 5100 president
scott 3000 analyst
jones 2975 manager
blake 2850 manager
clark 2450 manager
allen 1600 salesman
6 rows selected.
SQL> SELECT ename,deptno FROM employee WHERE sal>ALL
2 (SELECT avg(sal) FROM employee group by job);
no rows selected
SQL> SELECT job,min(sal) FROM employee group by job;
JOB MIN(SAL)
-------------------------------------------------- ----------
salesman 1250
president 5100
manager 2450
analyst 3000
SQL> SELECT deptno,min(sal) FROM employee group by deptno;
DEPTNO MIN(SAL)
---------- ----------
30 1250
20 2975
10 2450
SQL> SELECT ename,deptno FROM employee WHERE sal>ALL
2 (SELECT avg(sal) FROM employee group by deptno);
ENAME DEPTNO
---------- ----------
king 10
1 SELECT ename,sal,job FROM employee WHERE sal>ANY
2* (SELECT avg(sal) FROM employee group by job )
SQL> /
ENAME SAL JOB
---------- ---------- --------------------------------------------------
king 5100 president
scott 3000 analyst
jones 2975 manager
blake 2850 manager
clark 2450 manager
allen 1600 salesman
6 rows selected.
SQL> SELECT avg(sal) FROM employee group by job;
AVG(SAL)
----------
1366.66667
5100
2758.33333
3000
SQL> SELECT job, avg(sal) FROM employee group by job;
JOB AVG(SAL)
-------------------------------------------------- ----------
salesman 1366.66667
president 5100
manager 2758.33333
analyst 3000
SQL> SELECT ename,sal,job FROM employee e WHERE sal>ALL
2 (SELECT avg(sal) FROM employee e1 WHERE e.job=e1.job group by job );
ENAME SAL JOB
---------- ---------- --------------------------------------------------
blake 2850 manager
allen 1600 salesman
jones 2975 manager
SQL> SELECT ename FROM employee CONNECT BY empno=mgr;
ENAME
----------
king
blake
clark
allen
ward
jones
martin
scott
8 rows selected.
SQL> SELECT ename FROM employee CONNECT BY empno=mgr START WITH ename='allen';
SELECT ename FROM employee CONNECT BY empno=mgr START WITH ename='allen';
*
ERROR at line 1:
ORA-00911: invalid character
SQL> SELECT ename FROM employee CONNECT BY empno=mgr START WITH ename='allen';
ENAME
----------
allen
SQL> SELECT ename FROM employee CONNECT BY prior empno=mgr START WITH ename='a
en';
ENAME
----------
allen
SQL> SELECT ename FROM employee CONNECT BY prior empno=mgr START WITH ename='b
ke';
ENAME
----------
blake
allen
ward
martin
SQL> SELECT ename,mgr FROM employee;
ENAME MGR
---------- ----------
king
blake 7839
clark 7839
allen 7698
ward 7698
jones 7839
martin 7698
scott 7566
8 rows selected.
SQL> SELECT empno,ename,mgr FROM employee;
EMPNO ENAME MGR
---------- ---------- ----------
7839 king
7698 blake 7839
7782 clark 7839
7499 allen 7698
7521 ward 7698
7566 jones 7839
7654 martin 7698
7788 scott 7566
8 rows selected.
SQL> SELECT ename,empno FROM employee CONNECT BY prior empno=mgr;
ENAME EMPNO
---------- ----------
scott 7788
allen 7499
martin 7654
ward 7521
blake 7698
allen 7499
martin 7654
ward 7521
jones 7566
scott 7788
clark 7782
ENAME EMPNO
---------- ----------
king 7839
blake 7698
allen 7499
martin 7654
ward 7521
jones 7566
scott 7788
clark 7782
19 rows selected.
SQL> SELECT ename,empno FROM employee CONNECT BY prior empno=mgr
2 START WITH ename='blake';
ENAME EMPNO
---------- ----------
blake 7698
allen 7499
ward 7521
martin 7654
SQL> SELECT ename,empno FROM employee CONNECT BY empno=mgr
2 START WITH ename='blake';
ENAME EMPNO
---------- ----------
blake 7698
SQL> SELECT ename,sal,job FROM employee WHERE sal=
2 (SELECT avg(sal) FROM employee group by job);
(SELECT avg(sal) FROM employee group by job)
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
SQL>
SQL> spool off
===================================================================================================================
SQL> SELECT ename FROM employee;
ENAME
----------
Test
king
blake
clark
allen
ward
jones
martin
scott
9 rows selected.
SQL> SELECT ename FROM username.employee;
ENAME
----------
Test
king
blake
clark
allen
ward
jones
martin
scott
9 rows selected.
SQL> CREATE SYNONYM emp FOR employee;
Synonym created.
SQL> SELECT ename FROM emp WHERE deptno=10;
ENAME
----------
king
blake
clark
SQL>SQL> DROP SYNONYM emp;
Synonym dropped.
SQL> CREATE PUBLIC SYNONYM test FOR employee;
Synonym created.
SQL> DROP PUBLIC SYNONYM test;
Synonym dropped.
====================================================================================================================================================================================================================================================
SQL> CREATE TABLE students
2 (studentid NUMBER UNIQUE KEY,
3 name VARCHAR2(20) NOT NULL,
4 doj DATE default SYSDATE,
5 courseid NUMBER,
6 CONSTRAINT cid_FK FOREIGN KEY(courseid) references courses(courseid));
(studentid NUMBER UNIQUE KEY,
*
ERROR at line 2:
ORA-00907: missing right parenthesis
SQL> ed
Wrote file afiedt.buf
1 CREATE TABLE students
2 (studentid NUMBER UNIQUEKEY,
3 name VARCHAR2(20) NOT NULL,
4 doj DATE default SYSDATE,
5 courseid NUMBER,
6* CONSTRAINT cid_FK FOREIGN KEY(courseid) references courses(courseid))
SQL> /
(studentid NUMBER UNIQUEKEY,
*
ERROR at line 2:
ORA-00907: missing right parenthesis
SQL> ed
Wrote file afiedt.buf
1 CREATE TABLE students
2 (studentid NUMBER UNIQUE,
3 name VARCHAR2(20) NOT NULL,
4 doj DATE default SYSDATE,
5 courseid NUMBER,
6* CONSTRAINT cid_FK FOREIGN KEY(courseid) references courses(courseid))
SQL> /
Table created.
SQL> DROP TABLE students
2 ;
Table dropped.
SQL> CREATE TABLE students
2 (studentid NUMBER UNIQUE,
3 name VARCHAR2(20) NOT NULL,
4 doj DATE default SYSDATE,
5 courseid NUMBER,
6 age NUMBER,
7 CONSTRAINT cid_FK FOREIGN KEY(courseid) references courses(courseid));
Table created.
SQL> drop table students;
Table dropped.
SQL> ed
Wrote file afiedt.buf
1 CREATE TABLE students
2 (studentid NUMBER UNIQUE,
3 name VARCHAR2(20) NOT NULL,
4 doj DATE default SYSDATE,
5 courseid NUMBER,
6 age NUMBER,
7 CONSTRAINT cid_FK FOREIGN KEY(courseid) references courses(courseid),
8* CONSTRAINT age_ck CHECK(age>17))
SQL> /
Table created.
SQL> ALTER TABLE students ADD(address vARCHAR2(20));
Table altered.
SQL> ALTER TABLE students MODIFY(address vARCHAR2(22));
Table altered.
SQL> desc students
Name Null? Type
----------------------------------------- -------- ----------------------------
STUDENTID NUMBER
NAME NOT NULL VARCHAR2(20)
DOJ DATE
COURSEID NUMBER
AGE NUMBER
ADDRESS VARCHAR2(22)
SQL> aLTER tABLE students DROP COLUMN address;
Table altered.
SQL> spool off
===========================================================
SQL> CREATE TABLE courses(courseid NUMBER PRIMARY KEY,name VARCAHR2(20));
CREATE TABLE courses(courseid NUMBER PRIMARY KEY,name VARCAHR2(20))
*
ERROR at line 1:
ORA-00907: missing right parenthesis
SQL> CREATE TABLE courses(courseid NUMBER PRIMARY KEY,name VARCHAR2(20));
Table created.
SQL> DROP TABLE courses;
Table dropped.
SQL> CREATE TABLE courses(courseid NUMBER,name VARCHAR2(20),
2 CONSTRAINT cid_pk PRIMARY KEY(courseid));
Table created.
SQL> desc courses
Name Null? Type
----------------------------------------- -------- ----------------------------
COURSEID NOT NULL NUMBER
NAME VARCHAR2(20)
SQL> ALTER TABLE courses DROP CONSTRAINT cid_pk;
Table altered.
SQL> desc courses
Name Null? Type
----------------------------------------- -------- ----------------------------
COURSEID NUMBER
NAME VARCHAR2(20)
SQL>
SQL> CREATE TABLE courses(courseid NUMBER,name VARCHAR2(20),
2 CONSTRAINT cid_pk PRIMARY KEY(courseid));
Table created.
SQL> desc courses
Name Null? Type
----------------------------------------- -------- ----------------------
COURSEID NOT NULL NUMBER
NAME VARCHAR2(20)
SQL> ALTER TABLE courses DISABLE CONSTRAINT cid_pk;
Table altered.
SQL> desc courses
Name Null? Type
----------------------------------------- -------- ----------------------
COURSEID NUMBER
NAME VARCHAR2(20)
SQL> ALTER TABLE courses ENABLE CONSTRAINT cid_pk;
Table altered.
SQL> desc courses
Name Null? Type
----------------------------------------- -------- ----------------------
COURSEID NOT NULL NUMBER
NAME VARCHAR2(20)
SQL> INSERT INTO courses VALUES(1,'JSP');
1 row created.
SQL> INSERT INTO courses VALUES(1,'JS');
INSERT INTO courses VALUES(1,'JS')
*
ERROR at line 1:
ORA-00001: unique constraint (RATHNA.CID_PK) violated
SQL> ALTER TABLE courses DISABLE CONSTRAINT cid_pk;
Table altered.
SQL> INSERT INTO courses VALUES(1,'JS');
1 row created.
SQL> ALTER TABLE courses ENABLE CONSTRAINT cid_pk;
ALTER TABLE courses ENABLE CONSTRAINT cid_pk
*
ERROR at line 1:
ORA-02437: cannot validate (RATHNA.CID_PK) - primary key violated
SQL> TRUNCATE TABLE courses;
Table truncated.
SQL> ALTER TABLE courses ENABLE CONSTRAINT cid_pk;
Table altered.
SQL> DROP TABLE courses;
Table dropped.
SQL> CREATE TABLE courses(courseid NUMBER,name VARCHAR2(20));
Table created.
SQL> ALTER TABLE courses CONSTRAINT cid_pk;
ALTER TABLE courses CONSTRAINT cid_pk
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
SQL> ALTER TABLE courses ADD CONSTRAINT cid_pk PRIMARY KEY(courseid);
Table altered.
=========================================================================================================
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
SAL NUMBER
JOB VARCHAR2(20)
SQL> alter table emp rename to emp_22;
Table altered.
SQL> desc emp
ERROR:
ORA-04043: object emp does not exist
SQL> desc emp_22;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
SAL NUMBER
JOB VARCHAR2(20)
SQL> alter table emp rename column id to empno;
alter table emp rename column id to empno
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> alter table emp_22 rename column id to empno;
Table altered.
SQL> desc emp_22
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER
SAL NUMBER
JOB VARCHAR2(20)
SQL> ALTER TABLE EMP_22 SET UNUSED COLUMN sal;
Table altered.
SQL> desc emp_22
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER
JOB VARCHAR2(20)
SQL> ALTER TABLE EMP_22 DROP UNUSED COLUMNS
2 ;
Table altered.
SQL> desc emp_22;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER
JOB VARCHAR2(20)
SQL> INSERT INTO EMP_22 VALUES(9898,'TL');
1 row created.
SQL> INSERT INTO EMP_22(empno) VALUES(9896);
1 row created.
SQL> desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(4)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> INSERT INTO dept VALUES(33,'dd','dd');
1 row created.
SQL>
SQL> INSERT INTO dept(deptno,dname) VALUES(83,'ee');
1 row created.
SQL> INSERT INTO dept(dname,deptno) VALUES('ee',83);
INSERT INTO dept(dname,deptno) VALUES('ee',83)
*
ERROR at line 1:
ORA-00001: unique constraint (RATHNA.SYS_C0079562) violated
SQL> INSERT INTO dept(dname,deptno) VALUES('ee',84);
1 row created.
SQL> INSERT INTO dept(dname,deptno) VALUES(85,'ee');
INSERT INTO dept(dname,deptno) VALUES(85,'ee')
*
ERROR at line 1:
ORA-01722: invalid number
SQL> CREATE TABLE dept_c as SELECT * FROM dept WHERE 1=2;
Table created.
SQL> INSERT INTO dept_c SELECT * FROM dept;
14 rows created.
SQL> INSERT INTO dept VALUES(&num,'&name','&loc') ;
Enter value for num: 77
Enter value for name: tt
Enter value for loc: uu
old 1: INSERT INTO dept VALUES(&num,'&name','&loc')
new 1: INSERT INTO dept VALUES(77,'tt','uu')
1 row created.
SQL> /
Enter value for num: 66
Enter value for name: ee
Enter value for loc: gg
old 1: INSERT INTO dept VALUES(&num,'&name','&loc')
new 1: INSERT INTO dept VALUES(66,'ee','gg')
1 row created.
SQL> SELECT ename FROM employee WHERE deptno=&no;
Enter value for no: 10
old 1: SELECT ename FROM employee WHERE deptno=&no
new 1: SELECT ename FROM employee WHERE deptno=10
ENAME
----------
king
blake
clark
SQL> /
Enter value for no: 20
old 1: SELECT ename FROM employee WHERE deptno=&no
new 1: SELECT ename FROM employee WHERE deptno=20
ENAME
----------
jones
scott
SQL> /
Enter value for no: 30
old 1: SELECT ename FROM employee WHERE deptno=&no
new 1: SELECT ename FROM employee WHERE deptno=30
ENAME
----------
allen
ward
martin
SQL> DELETE FROM dept_c;
14 rows deleted.
SQL> TRUNCATE TABLE dept_c;
Table truncated.
SQL> DELETE FROM dept WHERE deptno=50;
0 rows deleted.
SQL> UPDATE employee SET sal=4000;
9 rows updated.
SQL> SELECT ename,sal FROM employee;
ENAME SAL
---------- ----------
Test 4000
king 4000
blake 4000
clark 4000
allen 4000
ward 4000
jones 4000
martin 4000
scott 4000
9 rows selected.
SQL> rollback;
Rollback complete.
SQL> SELECT ename,sal FROM employee;
ENAME SAL
---------- ----------
Test 5000
king 5100
blake 2850
clark 2450
allen 1600
ward 1250
jones 2975
martin 1250
scott 3000
9 rows selected.
SQL> UPDATE employee SET sal=sal+300,comm=comm+100;
9 rows updated.
SQL> SELECT ename,sal FROM employee;
ENAME SAL
---------- ----------
Test 5300
king 5400
blake 3150
clark 2750
allen 1900
ward 1550
jones 3275
martin 1550
scott 3300
9 rows selected.
SQL> commit;
Commit complete.
SQL> rollback;
Rollback complete.
SQL> SELECT ename,sal FROM employee;
ENAME SAL
---------- ----------
Test 5300
king 5400
blake 3150
clark 2750
allen 1900
ward 1550
jones 3275
martin 1550
scott 3300
9 rows selected.
SQL> SELECT max(sal) FROM employee WHERE deptno=10;
MAX(SAL)
----------
5400
SQL> UPDATE employee SET sal=
2 (SELECT max(sal) FROM employee WHERE deptno=10)
3 WHERE deptno=10;
3 rows updated.
SQL> SELECT ename,sal FROM employee WHERE deptno=10;
ENAME SAL
---------- ----------
king 5400
blake 5400
clark 5400
SQL> UPDATE employee SET comm=&c;
Enter value for c: 200
old 1: UPDATE employee SET comm=&c
new 1: UPDATE employee SET comm=200
9 rows updated.
SQL> CREATE TABLE emp_10 AS SELECT empno,ename,sal FROM employee WHERE deptno=10;
Table created.
SQL> CREATE TABLE emps AS SELECT empno,ename,sal FROM employee;
Table created.
SQL> SELECT count(*) FROM emp_10;
COUNT(*)
----------
3
SQL> SELECT count(*) FROM emps;
COUNT(*)
----------
9
SQL> desc emp_22;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER
JOB VARCHAR2(20)
SQL> SELECT count(*) FROM emp_22;
COUNT(*)
----------
14
SQL> INSERT INTO emp_22 VALUES(88,'ff');
1 row created.
SQL> INSERT INTO emp_22 VALUES(89,'ff');
1 row created.
SQL> CREATE TABLE ee AS SELECT * FROM dept;
Table created.
SQL> SELECT count(*) FROM emp_22;
COUNT(*)
----------
16
SQL> rollback;
Rollback complete.
SQL> SELECT count(*) FROM emp_22;
COUNT(*)
----------
16
SQL> INSERT INTO emp_22 VALUES(75,'ff');
1 row created.
SQL> INSERT INTO emp_22 VALUES(76,'ff');
1 row created.
SQL> SAVEPOINT a;
Savepoint created.
SQL> INSERT INTO emp_22 VALUES(77,'ff');
1 row created.
SQL> INSERT INTO emp_22 VALUES(78,'ff');
1 row created.
SQL> SAVEPOINT b;
Savepoint created.
SQL> INSERT INTO emp_22 VALUES(79,'ff');
1 row created.
SQL> SELECT * FROM emp_22;
EMPNO JOB
---------- --------------------
1 SE
2 SE
3 SE
4 SE
112 TL
101 SSE
102 ASE
103 SSE
104 Mgr
105 Assistant
111 SE
EMPNO JOB
---------- --------------------
211 ff
9898 TL
9896
88 ff
89 ff
75 ff
76 ff
77 ff
78 ff
79 ff
21 rows selected.
SQL> rollback to b;
Rollback complete.
SQL> SELECT * FROM emp_22;
EMPNO JOB
---------- --------------------
1 SE
2 SE
3 SE
4 SE
112 TL
101 SSE
102 ASE
103 SSE
104 Mgr
105 Assistant
111 SE
EMPNO JOB
---------- --------------------
211 ff
9898 TL
9896
88 ff
89 ff
75 ff
76 ff
77 ff
78 ff
20 rows selected.
SQL> rollback to a;
Rollback complete.
SQL> SELECT * FROM emp_22;
EMPNO JOB
---------- --------------------
1 SE
2 SE
3 SE
4 SE
112 TL
101 SSE
102 ASE
103 SSE
104 Mgr
105 Assistant
111 SE
EMPNO JOB
---------- --------------------
211 ff
9898 TL
9896
88 ff
89 ff
75 ff
76 ff
18 rows selected.
SQL> commit;
Commit complete.
SQL> SELECT * FROM emp_22;
EMPNO JOB
---------- --------------------
1 SE
2 SE
3 SE
4 SE
112 TL
101 SSE
102 ASE
103 SSE
104 Mgr
105 Assistant
111 SE
EMPNO JOB
---------- --------------------
211 ff
9898 TL
9896
88 ff
89 ff
75 ff
76 ff
18 rows selected.
SQL> spool off
======================================================================================================================================
Raw
gistfile3.txt
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 num NUMBER;
3 CURSOR cname(no NUMBER) IS SELECT ename FROM employee WHERE deptno=no;
4 vcEname employee.ename%TYPE;
5 BEGIN
6 num:=&dno;
7 OPEN cname(num);
8 FETCH cname INTO vcEname;
9 DBMS_OUTPUT.PUT_LINE('List of employees who are working in department'||num);
10 LOOP
11 DBMS_OUTPUT.PUT_LINE(vcEname);
12 FETCH cname INTO vcEname;
13 EXIT WHEN cname%NOTFOUND;
14 END LOOP;
15* END;
SQL> /
Enter value for dno: 10
old 6: num:=&dno;
new 6: num:=10;
List of employees who are working in department10
king
blake
clark
PL/SQL procedure successfully completed.
SQL> /
Enter value for dno: 20
old 6: num:=&dno;
new 6: num:=20;
List of employees who are working in department20
jones
scott
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 num NUMBER;
3 CURSOR cname(no dept.deptno%TYPE) IS SELECT ename FROM employee WHERE deptno=no;
4 vcEname employee.ename%TYPE;
5 BEGIN
6 num:=&dno;
7 OPEN cname(num);
8 FETCH cname INTO vcEname;
9 DBMS_OUTPUT.PUT_LINE('List of employees who are working in department'||num);
10 LOOP
11 DBMS_OUTPUT.PUT_LINE(vcEname);
12 FETCH cname INTO vcEname;
13 EXIT WHEN cname%NOTFOUND;
14 END LOOP;
15* END;
SQL> /
Enter value for dno: 10
old 6: num:=&dno;
new 6: num:=10;
List of employees who are working in department10
king
blake
clark
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 num NUMBER;
3 CURSOR cname(no dept.deptno%TYPE) IS SELECT ename FROM employee WHERE deptno=no;
4 BEGIN
5 num:=&dno;
6 DBMS_OUTPUT.PUT_LINE('List of employees who are working in department'||num);
7 FOR rec IN cname(num)
8 LOOP
9 DBMS_OUTPUT.PUT_LINE(rec.ename);
10 END LOOP;
11* END;
SQL> /
Enter value for dno: 10
old 5: num:=&dno;
new 5: num:=10;
List of employees who are working in department10
king
blake
clark
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 TYPE details IS REF CURSOR;
3 emp employee%ROWTYPE;
4 dept1 dept%ROWTYPE;
5 cname details;
6 BEGIN
7 OPEN cname FOR SELECT * FROM employee;
8 FETCH cname INTO emp;
9 LOOP
10 DBMS_OUTPUT.PUT_LINE(emp.ename);
11 EXIT WHEN cname%NOTFOUND;
12 FETCH cname INTO emp;
13 END LOOP;
14 CLOSE cname;
15 OPEN cname FOR SELECT * FROM dept;
16 FETCH cname INTO dept1;
17 LOOP
18 DBMS_OUTPUT.PUT_LINE(dept1.dname);
19 EXIT WHEN cname%NOTFOUND;
20 FETCH cname INTO dept1;
21 END LOOP;
22 CLOSE cname;
23* END;
SQL> /
Test
king
blake
clark
allen
ward
jones
martin
scott
scott
Test
HR
nb
gh
ACCOUNTING
RESEARCH
SALES
OPERATIONS
dd
ee
ee
tt
ee
s
ee
sdf
sdf
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 TYPE details IS REF CURSOR RETURN employee%rowtype;
3 emp employee%ROWTYPE;
4 cname details;
5 BEGIN
6 OPEN cname FOR SELECT * FROM employee WHERE deptno=10;
7 FETCH cname INTO emp;
8 LOOP
9 DBMS_OUTPUT.PUT_LINE(emp.ename);
10 EXIT WHEN cname%NOTFOUND;
11 FETCH cname INTO emp;
12 END LOOP;
13 CLOSE cname;
14 OPEN cname FOR SELECT * FROM employee WHERE deptno=20;
15 FETCH cname INTO emp;
16 LOOP
17 DBMS_OUTPUT.PUT_LINE(emp.ename);
18 EXIT WHEN cname%NOTFOUND;
19 FETCH cname INTO emp;
20 END LOOP;
21 CLOSE cname;
22* END;
SQL> /
king
blake
clark
clark
jones
scott
scott
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 CURSOR c_staff is SELECT staff_code, staff_masters.design_code FROM staff_masters,designation_masters
3 WHERE design_name = 'Professor' and staff_sal > 20000
4 and staff_masters.design_code =designation_masters.design_code
5 FOR UPDATE OF staff_masters.design_code NOWAIT;
6 d_code designation_masters.design_code%type;
7 BEGIN
8 SELECT design_code into d_code FROM designation_masters WHERE design_name='Director';
9 FOR v_rec in c_staff
10 LOOP
11 UPDATE staff_masters SET design_code = d_code
12 WHERE current of c_staff;
13 END LOOP;
14* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> spool off
==============================================================
SQL> BEGIN
2 UPDATE employee SET sal=sal+100;
3 DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
4 END;
5 /
9
PL/SQL procedure successfully completed.
SQL> DECLARE
2 CURSOR cName IS SELECT * FROM dept;
3 deptDetail dept%rowtype;
4 BEGIN
5 OPEN cName; -- Opening a cursor
6 FETCH cName INTO deptDetail;
7 CLOSE cName; --Closing cursor
8 DBMS_OUTPUT.PUT_LINE(deptDetail.dname);
9 END;
10 /
Test
PL/SQL procedure successfully completed.
SQL> DECLARE
2 CURSOR cName IS SELECT * FROM dept;
3 deptDetail dept%rowtype;
4 BEGIN
5 IF cname%ISOPEN
6 THEN
7 null;
8 ELSE
9 OPEN cname;
10 END IF;
11 FETCH cname INTO deptDetail;
12 LOOP
13 FETCH cname INTO deptDetail;
14 EXIT WHEN cname%NOTFOUND;
15 DBMS_OUTPUT.PUT_LINE(deptDetail.dname);
16 END LOOP;
17 END;
18 /
HR
nb
gh
ACCOUNTING
RESEARCH
SALES
OPERATIONS
dd
ee
ee
tt
ee
s
ee
sdf
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 CURSOR cName IS SELECT * FROM dept;
3 deptDetail dept%rowtype;
4 BEGIN
5 IF cname%ISOPEN
6 THEN
7 null;
8 ELSE
9 OPEN cname;
10 END IF;
11 FETCH cname INTO deptDetail;
12 LOOP
13 DBMS_OUTPUT.PUT_LINE(deptDetail.dname);
14 FETCH cname INTO deptDetail;
15 EXIT WHEN cname%NOTFOUND;
16 END LOOP;
17 CLOSE cname;
18* END;
SQL> /
Test
HR
nb
gh
ACCOUNTING
RESEARCH
SALES
OPERATIONS
dd
ee
ee
tt
ee
s
ee
sdf
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 CURSOR cName IS SELECT * FROM dept;
3 deptDetail dept%rowtype;
4 BEGIN
5 IF cname%ISOPEN
6 THEN
7 null;
8 ELSE
9 OPEN cname;
10 END IF;
11 FETCH cname INTO deptDetail;
12 WHILE cname%NOTFOUND
13 LOOP
14 DBMS_OUTPUT.PUT_LINE(deptDetail.dname);
15 FETCH cname INTO deptDetail;
16 END LOOP;
17 CLOSE cname;
18* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 CURSOR cName IS SELECT * FROM dept;
3 deptDetail dept%rowtype;
4 BEGIN
5 IF cname%ISOPEN
6 THEN
7 null;
8 ELSE
9 OPEN cname;
10 END IF;
11 FETCH cname INTO deptDetail;
12 WHILE cname%FOUND
13 LOOP
14 DBMS_OUTPUT.PUT_LINE(deptDetail.dname);
15 FETCH cname INTO deptDetail;
16 END LOOP;
17 CLOSE cname;
18* END;
SQL> /
Test
HR
nb
gh
ACCOUNTING
RESEARCH
SALES
OPERATIONS
dd
ee
ee
tt
ee
s
ee
sdf
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 CURSOR cName IS SELECT * FROM employee WHERE deptno=&no;
3 empDetail employee%rowtype;
4 BEGIN
5 IF cname%ISOPEN
6 THEN
7 null;
8 ELSE
9 OPEN cname;
10 END IF;
11 FETCH cname INTO empDetail;
12 IF cname%ROWCOUNT>0
13 THEN
14 WHILE cname%FOUND
15 LOOP
16 DBMS_OUTPUT.PUT_LINE(empDetail.ename);
17 FETCH cname INTO empDetail;
18 END LOOP;
19 CLOSE cname;
20 ELSE
21 DBMS_OUTPUT.PUT_LINE('Employees not working in this department');
22 END IF;
23* END;
SQL> /
Enter value for no: 10
old 2: CURSOR cName IS SELECT * FROM employee WHERE deptno=&no;
new 2: CURSOR cName IS SELECT * FROM employee WHERE deptno=10;
king
blake
clark
PL/SQL procedure successfully completed.
SQL> /
Enter value for no: 90
old 2: CURSOR cName IS SELECT * FROM employee WHERE deptno=&no;
new 2: CURSOR cName IS SELECT * FROM employee WHERE deptno=90;
Employees not working in this department
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 CURSOR cname IS SELECT * FROM dept;
3 BEGIN
4 FOR rec IN cname
5 LOOP
6 DBMS_OUTPUT.PUT_LINE(rec.dname);
7 END LOOP;
8* END;
SQL> /
Test
HR
nb
gh
ACCOUNTING
RESEARCH
SALES
OPERATIONS
dd
ee
ee
tt
ee
s
ee
sdf
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 CURSOR cname IS SELECT ename,dname FROM employee e,dept d WHERE e.deptno=d.deptno;
3 BEGIN
4 FOR rec IN cname
5 LOOP
6 DBMS_OUTPUT.PUT_LINE(rec.dname);
7 DBMS_OUTPUT.PUT_LINE(rec.ename);
8 END LOOP;
9* END;
SQL> /
ACCOUNTING
clark
ACCOUNTING
blake
ACCOUNTING
king
RESEARCH
scott
RESEARCH
jones
SALES
martin
SALES
ward
SALES
allen
PL/SQL procedure successfully completed.
SQL> spool off
=======================================================
SQL> DECLARE
2 nSal NUMBER;
3 nMinSal NUMBER;
4 BEGIN
5 SELECT sal INTO nSal FROM employee WHERE empno=7698;
6 SELECT min(sal) INTO nMinSal FROM employee;
7 IF nSal<nMinSal
8 THEN
9 UPDATE employee SET sal=sal*.2 WHERE empno=7698;
10 END IF;
11 END;
12 /
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> /
PL/SQL procedure successfully completed.
SQL> DECLARE
2 vcAddress VARCHAR2(30);
3 BEGIN
4 sELECT Student_Address INTO vcAddress FROM student_masters
5 WHERE Student_Code =1003;
6 IF vcAddress IS NULL
7 THEN
8 UPDATE student_masters SET student_address='Chennai'
9 WHERE student_code=1003;
10 ELSIF vcAddress='Chennai'
11 THEN
12 DBMS_OUTPUT.PUT_LINE('Residing in chennai');
13 ELSE
14 DBMS_OUTPUT.PUT_LINE('Residing in other city');
15 END IF;
16 END;
17 /
PL/SQL procedure successfully completed.
SQL> sELECT Student_Address FROM student_masters WHERE Student_Code =1003;
STUDENT_ADDRESS
--------------------------------------------------------------------------------
Chennai
SQL>
SQL> BEGIN
2 LOOP
3 DBMS_OUTPUT.PUT_LINE('test');
4 END LOOP;
5 END;
6 /
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
BEGIN
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 3
SQL> BEGIN
2 LOOP
3 DBMS_OUTPUT.PUT_LINE('test');
4 EXIT;
5 END LOOP;
6 END;
7 .
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 LOOP
3 DBMS_OUTPUT.PUT_LINE('test');
4 EXIT;
5 END LOOP;
6* END;
SQL> /
test
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 nNum NUMBER:=1;
3 BEGIN
4 LOOP
5 DBMS_OUTPUT.PUT_LINE('test');
6 nNum:=nNum+1;
7 EXIT WHEN nNum=5;
8 END LOOP;
9* END;
SQL> /
test
test
test
test
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 nNum NUMBER:=1;
3 BEGIN
4 LOOP
5 EXIT WHEN nNum=5;
6 DBMS_OUTPUT.PUT_LINE('test');
7 nNum:=nNum+1;
8 END LOOP;
9* END;
10 /
test
test
test
test
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 nNum NUMBER:=1;
3 BEGIN
4 LOOP
5 EXIT WHEN nNum<5;
6 DBMS_OUTPUT.PUT_LINE('test');
7 nNum:=nNum+1;
8 END LOOP;
9* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 nNum NUMBER:=1;
3 BEGIN
4 LOOP
5 EXIT WHEN nNum>5;
6 DBMS_OUTPUT.PUT_LINE('test');
7 nNum:=nNum+1;
8 END LOOP;
9* END;
SQL> /
test
test
test
test
test
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 nNum NUMBER:=1;
3 BEGIN
4 LOOP
5 DBMS_OUTPUT.PUT_LINE('test');
6 nNum:=nNum+1;
7 EXIT WHEN nNum>5;
8 END LOOP;
9* END;
10 /
test
test
test
test
test
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 nNum NUMBER:=1;
3 BEGIN
4 WHILE nNUM<5
5 LOOP
6 DBMS_OUTPUT.PUT_LINE('test');
7 nNum:=nNum+1;
8 END LOOP;
9* END;
SQL> /
test
test
test
test
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 FOR index1 IN 1..10
3 LOOP
4 DBMS_OUTPUT.PUT_LINE('test');
5 END LOOP;
6* END;
SQL> /
test
test
test
test
test
test
test
test
test
test
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 FOR index1 IN REVERSE 5..1
3 LOOP
4 DBMS_OUTPUT.PUT_LINE('test'||index1);
5 END LOOP;
6* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> /
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 FOR index1 IN REVERSE 5..1
3 LOOP
4 DBMS_OUTPUT.PUT_LINE('test');
5 END LOOP;
6* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 FOR index1 IN REVERSE 1..5
3 LOOP
4 DBMS_OUTPUT.PUT_LINE('test');
5 END LOOP;
6* END;
SQL> /
test
test
test
test
test
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 FOR index1 IN REVERSE 1..5
3 LOOP
4 DBMS_OUTPUT.PUT_LINE('test'||index1);
5 END LOOP;
6* END;
SQL> /
test5
test4
test3
test2
test1
PL/SQL procedure successfully completed.
SQL> DECLARE
2 nOuter NUMBER:=10;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('Outer Block');
5 DBMS_OUTPUT.PUT_LINE(nOuter);
6 DECLARE
7 nInner NUMBER:=10;
8 BEGIN
9 DBMS_OUTPUT.PUT_LINE('Inner Block');
10 DBMS_OUTPUT.PUT_LINE(nInner);
11 END;
12 END;
13 /
Outer Block
10
Inner Block
10
PL/SQL procedure successfully completed.
SQL> DECLARE
2 nNum NUMBER:=10;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('Outer Block');
5 DBMS_OUTPUT.PUT_LINE(nNum);
6 DECLARE
7 nNum NUMBER:=20;
8 BEGIN
9 DBMS_OUTPUT.PUT_LINE('Inner Block');
10 DBMS_OUTPUT.PUT_LINE(nNum);
11 END;
12 END;
13 /
Outer Block
10
Inner Block
20
PL/SQL procedure successfully completed.
SQL> DECLARE
2 nNum NUMBER:=10;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('Outer Block');
5 DBMS_OUTPUT.PUT_LINE(nNum);
6 DECLARE
7 nNum NUMBER:=20;
8 BEGIN
9 DBMS_OUTPUT.PUT_LINE('Inner Block');
10 DBMS_OUTPUT.PUT_LINE(nNum);
11 END;
12 DBMS_OUTPUT.PUT_LINE(nNum);
13 END;
14
15 /
Outer Block
10
Inner Block
20
10
PL/SQL procedure successfully completed.
SQL> <<OUTER>>
2 DECLARE
3 nNum NUMBER:=10;
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE('Outer Block');
6 DBMS_OUTPUT.PUT_LINE(nNum);
7 <<INNER>>
8 DECLARE
9 nNum NUMBER:=20;
10 BEGIN
11 DBMS_OUTPUT.PUT_LINE('Inner Block');
12 DBMS_OUTPUT.PUT_LINE(nNum);
13 DBMS_OUTPUT.PUT_LINE(OUTER.nNum);
14 END;
15 DBMS_OUTPUT.PUT_LINE(nNum);
16 END;
17 /
Outer Block
10
Inner Block
20
10
10
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 <<OUTER>>
2 DECLARE
3 nNum NUMBER:=10;
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE('Outer Block');
6 DBMS_OUTPUT.PUT_LINE(nNum);
7 <<INNER>>
8 DECLARE
9 nNum NUMBER:=20;
10 BEGIN
11 DBMS_OUTPUT.PUT_LINE('Inner Block');
12 DBMS_OUTPUT.PUT_LINE(nNum);
13 DBMS_OUTPUT.PUT_LINE(OUTER.nNum);
14 DBMS_OUTPUT.PUT_LINE(INNER.nNum);
15 END;
16* END;
17 /
Outer Block
10
Inner Block
20
10
20
PL/SQL procedure successfully completed.
SQL> spool off
=============================================================================================
Raw
gistfile4.txt
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE('Welcome');
3 END;
4 /
Welcome
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE('Welcome');
3 END;
4 /
Welcome
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE('Welcome'||' to PL/SQL session');
3 END;
4 /
Welcome to PL/SQL session
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 num NUMBER;
3 BEGIN
4 num:=25;
5 DBMS_OUTPUT.PUT_LINE('Result is '||num);
6 END;
7 /
Result is 25
PL/SQL procedure successfully completed.
SQL> DECLARE
2 num NUMBER:=20;
3 BEGIN
4 num:=25;
5 DBMS_OUTPUT.PUT_LINE('Result is '||num);
6 END;
7 /
Result is 25
PL/SQL procedure successfully completed.
SQL> DECLARE
2 num NUMBER:=20;
3 doj DATE DEFAULT SYSDATE;
4 BEGIN
5 --num:=25;
6 DBMS_OUTPUT.PUT_LINE('Result is '||num);
7 DBMS_OUTPUT.PUT_LINE('Result is '||doj);
8 END;
9 /
Result is 20
Result is 25-AUG-14
PL/SQL procedure successfully completed.
SQL> DECLARE
2 vcName VARCHAR2(20) NOT NULL;
3 id NUMBER;
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE(vcName||id);
6 END;
7 /
vcName VARCHAR2(20) NOT NULL;
*
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00218: a variable declared NOT NULL must have an initialization assignment
SQL> DECLARE
2 vcName VARCHAR2(20) NOT NULL:='Test';
3 id NUMBER;
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE(vcName||id);
6 END;
7 /
Test
PL/SQL procedure successfully completed.
SQL> DECLARE
2 vcName VARCHAR2(20) NOT NULL;
3 id NUMBER;
4 BEGIN
5 vcName:='sample';
6 DBMS_OUTPUT.PUT_LINE(vcName||id);
7 END;
8 /
vcName VARCHAR2(20) NOT NULL;
*
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00218: a variable declared NOT NULL must have an initialization assignment
SQL> DECLARE
2 vcName VARCHAR2(20) NOT NULL DEFAULT 'Example';
3 BEGIN
4 vcName:='sample';
5 DBMS_OUTPUT.PUT_LINE(vcName);
6 END;
7 /
sample
PL/SQL procedure successfully completed.
SQL> DECLARE
2 vcName VARCHAR2(20) NOT NULL DEFAULT 'Example';
3 nPF CONSTANT NUMBER;
4 BEGIN
5 vcName:='sample';
6 DBMS_OUTPUT.PUT_LINE(vcName);
7 END;
8 /
nPF CONSTANT NUMBER;
*
ERROR at line 3:
ORA-06550: line 3, column 2:
PLS-00322: declaration of a constant 'NPF' must contain an initialization
assignment
ORA-06550: line 3, column 6:
PL/SQL: Item ignored
SQL> DECLARE
2 vcName VARCHAR2(20) NOT NULL DEFAULT 'Example';
3 nPF CONSTANT NUMBER:=23;
4 BEGIN
5 vcName:='sample';
6 DBMS_OUTPUT.PUT_LINE(vcName);
7 END;
8 /
sample
PL/SQL procedure successfully completed.
SQL> DECLARE
2 vcName VARCHAR2(20) NOT NULL DEFAULT 'Example';
3 nPF CONSTANT NUMBER;
4 BEGIN
5 vcName:='sample';
6 nPF:=25;
7 DBMS_OUTPUT.PUT_LINE(vcName||nPF);
8 END;
9 /
nPF CONSTANT NUMBER;
*
ERROR at line 3:
ORA-06550: line 3, column 2:
PLS-00322: declaration of a constant 'NPF' must contain an initialization
assignment
ORA-06550: line 3, column 6:
PL/SQL: Item ignored
ORA-06550: line 6, column 2:
PLS-00363: expression 'NPF' cannot be used as an assignment target
ORA-06550: line 6, column 2:
PL/SQL: Statement ignored
SQL> DECLARE
2 vcName VARCHAR2(20) NOT NULL DEFAULT 'Example';
3 nPF CONSTANT NUMBER:=20;
4 BEGIN
5 vcName:='sample';
6 nPF:=25;
7 DBMS_OUTPUT.PUT_LINE(vcName||nPF);
8 END;
9 /
nPF:=25;
*
ERROR at line 6:
ORA-06550: line 6, column 2:
PLS-00363: expression 'NPF' cannot be used as an assignment target
ORA-06550: line 6, column 2:
PL/SQL: Statement ignored
SQL> DECLARE
2 vcName VARCHAR2(20);
3 vcLoc VARCHAR2(20);
4 nDeptno NUMBER;
5 BEGIN
6 vcName:='&name';
7 vcLoc:='&loc';
8 nDeptno:=93;
9 INSERT INTO dept VALUES(94,vcName,vcLoc);
10 END;
11 /
Enter value for name: Sales
old 6: vcName:='&name';
new 6: vcName:='Sales';
Enter value for loc: CHN
old 7: vcLoc:='&loc';
new 7: vcLoc:='CHN';
PL/SQL procedure successfully completed.
SQL> SELECT dname,loc FROM dept WHERE deptno=10;
DNAME LOC
-------------- -------------
ACCOUNTING NEWYORK
SQL> DECLARE
2 vcName VARCHAR2(20);
3 vcLoc VARCHAR2(20);
4 BEGIN
5 SELECT dname,loc INTO vcName,vcLoc FROM dept WHERE deptno=10;
6 DBMS_OUTPUT.PUT_LINE('Department Name: '||vcName);
7 DBMS_OUTPUT.PUT_LINE('Department Location: '||vcLoc);
8 END;
9 /
Department Name: ACCOUNTING
Department Location: NEWYORK
PL/SQL procedure successfully completed.
SQL> DECLARE
2 vcName VARCHAR2(20);
3 vcLoc VARCHAR2(20);
4 nDeptno NUMBER;
5 BEGIN
6 SELECT * INTO nDeptno,vcName,vcLoc FROM dept WHERE deptno=10;
7 DBMS_OUTPUT.PUT_LINE('Department No: '||nDeptno);
8 DBMS_OUTPUT.PUT_LINE('Department Name: '||vcName);
9 DBMS_OUTPUT.PUT_LINE('Department Location: '||vcLoc);
10 END;
11 /
Department No: 10
Department Name: ACCOUNTING
Department Location: NEWYORK
PL/SQL procedure successfully completed.
SQL> DECLARE
2 vcName VARCHAR2(20);
3 vcLoc VARCHAR2(20);
4 BEGIN
5 SELECT dname,loc FROM dept WHERE deptno=10;
6 DBMS_OUTPUT.PUT_LINE('Department Name: '||vcName);
7 DBMS_OUTPUT.PUT_LINE('Department Location: '||vcLoc);
8 END;
9 /
SELECT dname,loc FROM dept WHERE deptno=10;
*
ERROR at line 5:
ORA-06550: line 5, column 2:
PLS-00428: an INTO clause is expected in this SELECT statement
SQL> desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(4)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> DECLARE
2 vcName dept.dname%TYPE;
3 vcLoc dept.loc%TYPE;
4 BEGIN
5 SELECT dname,loc INTO vcName,vcLoc FROM dept WHERE deptno=10;
6 DBMS_OUTPUT.PUT_LINE('Department Name: '||vcName);
7 DBMS_OUTPUT.PUT_LINE('Department Location: '||vcLoc);
8 END;
9 /
Department Name: ACCOUNTING
Department Location: NEWYORK
PL/SQL procedure successfully completed.
SQL> DECLARE
2 deptDetail dept%ROWTYPE;
3 BEGIN
4 SELECT * INTO deptDetail FROM dept WHERE deptno=10;
5 DBMS_OUTPUT.PUT_LINE(deptDetail.deptno);
6 DBMS_OUTPUT.PUT_LINE(deptDetail.dname);
7 DBMS_OUTPUT.PUT_LINE(deptDetail.loc);
8 END;
9 /
10
ACCOUNTING
NEWYORK
PL/SQL procedure successfully completed.
SQL> DECLARE
2 SUBTYPE vc IS VARCHAR2;
3 vcName vc(20);
4 vcLoc vc(20);
5 BEGIN
6 SELECT dname,loc FROM dept WHERE deptno=10;
7 DBMS_OUTPUT.PUT_LINE('Department Name: '||vcName);
8 DBMS_OUTPUT.PUT_LINE('Department Location: '||vcLoc);
9 END;
10 /
SELECT dname,loc FROM dept WHERE deptno=10;
*
ERROR at line 6:
ORA-06550: line 6, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 SUBTYPE vc IS VARCHAR2;
3 vcName vc(20);
4 vcLoc vc(20);
5 BEGIN
6 SELECT dname,loc INTO vcName,vcLoc FROM dept WHERE deptno=10;
7 DBMS_OUTPUT.PUT_LINE('Department Name: '||vcName);
8 DBMS_OUTPUT.PUT_LINE('Department Location: '||vcLoc);
9* END;
SQL> /
Department Name: ACCOUNTING
Department Location: NEWYORK
PL/SQL procedure successfully completed.
SQL> DECLARE
2 SUBTYPE deptD IS dept%ROWTYPE;
3 deptDetail deptD;
4 BEGIN
5 SELECT * INTO deptDetail FROM dept WHERE deptno=10;
6 DBMS_OUTPUT.PUT_LINE(deptDetail.deptno);
7 DBMS_OUTPUT.PUT_LINE(deptDetail.dname);
8 DBMS_OUTPUT.PUT_LINE(deptDetail.loc);
9 END;
10 /
10
ACCOUNTING
NEWYORK
PL/SQL procedure successfully completed.
SQL> DECLARE
2 vcEname employee.ename%TYPE;
3 vcDname dept.dname%TYPE;
4 BEGIN
5 SELECT ename,dname INTO vcEname,vcDname FROM employee e,dept d
6 WHERE d.deptno=e.deptno;
7 DBMS_OUTPUT.PUT_LINE(vcEname);
8 DBMS_OUTPUT.PUT_LINE(vcDname);
9 END;
10 /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 vcEname employee.ename%TYPE;
3 vcDname dept.dname%TYPE;
4 BEGIN
5 SELECT ename,dname INTO vcEname,vcDname FROM employee e,dept d
6 WHERE d.deptno=e.deptno and empno=7689;
7 DBMS_OUTPUT.PUT_LINE(vcEname);
8 DBMS_OUTPUT.PUT_LINE(vcDname);
9* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 vcEname employee.ename%TYPE;
3 vcDname dept.dname%TYPE;
4 BEGIN
5 SELECT ename,dname INTO vcEname,vcDname FROM employee e,dept d
6 WHERE d.deptno=e.deptno and empno=7698;
7 DBMS_OUTPUT.PUT_LINE(vcEname);
8 DBMS_OUTPUT.PUT_LINE(vcDname);
9* END;
SQL> /
blake
ACCOUNTING
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 TYPE details IS RECORD(
3 vcEname employee.ename%TYPE,
4 vcDname dept.dname%TYPE);
5 rec details;
6 BEGIN
7 SELECT ename,dname INTO rec FROM employee e,dept d
8 WHERE d.deptno=e.deptno and empno=7698;
9 DBMS_OUTPUT.PUT_LINE(rec.vcEname);
10 DBMS_OUTPUT.PUT_LINE(rec.vcDname);
11* END;
SQL> /
blake
ACCOUNTING
PL/SQL procedure successfully completed.
SQL> DECLARE
2 TYPE EmpTable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 Emp_tab EmpTable;
4 BEGIN
5 Emp_tab(1):=200;
6 Emp_tab(2):=300;
7 DBMS_OUTPUT.PUT_LINE(Emp_tab(1) || ' '|| Emp_tab(2));
8 END;
9 /
200 300
PL/SQL procedure successfully completed.
SQL> spool off
=SQL> GRANT SELECT ON trg402.dept TO rathna;
Grant succeeded.
SQL> REVOKE SELECT ON trg402.dept FROM rathna;
Revoke succeeded.
SQL> SELECT * FROM trg402.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEWYORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> SELECT * FROM trg402.dept;
SELECT * FROM trg402.dept
*
ERROR at line 1:
ORA-00942: table or view does not exist
=================================================
SQL> select ename FROM employee WHERE deptno=20;
ENAME
----------
jones
scott
SQL> CREATE VIEW viewdemo1
2 AS
3 SELECT ename FROM employee WHERE deptno=10;
View created.
SQL> SELECT * FROM viewdemo1;
ENAME
----------
king
blake
clark
SQL> CREATE VIEW viewdemo1
2 AS
3 SELECT ename FROM employee WHERE deptno=20;
CREATE VIEW viewdemo1
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> CREATE OR REPLACE VIEW viewdemo1
2 AS
3 SELECT ename FROM employee WHERE deptno=20;
View created.
SQL> CREATE VIEW viewdemo2
2 AS
3 SELECT * FROM dept_details;
View created.
SQL> SELECT * FROM dept_details;
DEPTNO DNAME LOC
---------- -------------- -------------
1 training chennai
4 admin pune
6 admin pune
8 admin pune
2000 admin pune
2005 admin pune
2010 admin pune
2015 admin pune
2020 admin pune
2025 admin pune
10 rows selected.
SQL> SELECT * FROM viewdemo2;
DEPTNO DNAME LOC
---------- -------------- -------------
1 training chennai
4 admin pune
6 admin pune
8 admin pune
2000 admin pune
2005 admin pune
2010 admin pune
2015 admin pune
2020 admin pune
2025 admin pune
10 rows selected.
SQL> INSERT INTO viewdemo2 VALUES(10,'dd','f');
1 row created.
SQL> SELECT * FROM viewdemo2;
DEPTNO DNAME LOC
---------- -------------- -------------
1 training chennai
4 admin pune
6 admin pune
8 admin pune
2000 admin pune
2005 admin pune
2010 admin pune
2015 admin pune
2020 admin pune
2025 admin pune
10 dd f
11 rows selected.
SQL> SELECT * FROM dept_details;
DEPTNO DNAME LOC
---------- -------------- -------------
1 training chennai
4 admin pune
6 admin pune
8 admin pune
2000 admin pune
2005 admin pune
2010 admin pune
2015 admin pune
2020 admin pune
2025 admin pune
10 dd f
11 rows selected.
SQL> CREATE OR REPLACE VIEW viewdemo2
2 AS
3 SELECT * FROM dept_details WITH READ ONLY;
View created.
SQL> INSERT INTO viewdemo2 VALUES(10,'dd','f');
INSERT INTO viewdemo2 VALUES(10,'dd','f')
*
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view
SQL> CREATE OR REPLACE VIEW viewdemo2
2 AS
3 SELECT * FROM dept_details WHERE deptno=10 WITH CHECK OPTION;
View created.
SQL> UPDATE viewdemo2 SET loc='BGL';
1 row updated.
SQL> CREATE OR REPLACE VIEW viewdemo2
2 AS
3 SELECT * FROM test31;
SELECT * FROM test31
*
ERROR at line 3:
ORA-00942: table or view does not exist
SQL> CREATE OR REPLACE FORCE VIEW viewdemo2
2 AS
3 SELECT * FROM test31;
Warning: View created with compilation errors.
SQL> CREATE VIEW viewdemo3
2 AS
3 SELECT deptno,coun(*) FROM employee group by deptno;
SELECT deptno,coun(*) FROM employee group by deptno
*
ERROR at line 3:
ORA-00936: missing expression
SQL> ed
Wrote file afiedt.buf
1 CREATE VIEW viewdemo3
2 AS
3* SELECT deptno,count(*) FROM employee group by deptno
SQL> /
SELECT deptno,count(*) FROM employee group by deptno
*
ERROR at line 3:
ORA-00998: must name this expression with a column alias
SQL> ed
Wrote file afiedt.buf
1 CREATE VIEW viewdemo3
2 AS
3* SELECT deptno,count(*) as count FROM employee group by deptno
SQL> /
View created.
SQL> SELECT * FROM viewdemo3;
DEPTNO COUNT
---------- ----------
1
30 3
20 2
10 3
SQL> INSERT INTO viewdemo3 VALUES(40,4);
INSERT INTO viewdemo3 VALUES(40,4)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
SQL> CREATE VIEW viewdemo4
2 AS
3 SELECT e.ename "Manager name" FROM employee e, employee m WHERE e.empno=m.empno;
View created.
SQL> SELECT * FROM viewdemo4;
Manager na
----------
Test
king
blake
clark
allen
ward
jones
martin
scott
9 rows selected.
SQL> DROP VIEW viewdemo4;
View dropped.
SQL> SELECT * FROM viewdemo4;
SELECT * FROM viewdemo4
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> SELECT s.staff_name,v.strength FROM staff_masters s,
2 (SELECT mgr_code,count(*) strength FROM staff_masters group by mgr_code) v
3 WHERE s.staff_code=v.mgr_code;
STAFF_NAME STRENGTH
-------------------------------------------------- ----------
John 2
Allen 5
Smith 3
SQL> SELECT ename,sal FROM employee ORDER BY sal desc;
ENAME SAL
---------- ----------
king 5400
blake 5400
clark 5400
Test 5300
scott 3300
jones 3275
allen 1900
martin 1550
ward 1550
9 rows selected.
SQL> SELECT ename,sal FROM (SELECT ename,sal FROM employee ORDER BY sal desc)
2 WHERE rownum<3;
ENAME SAL
---------- ----------
king 5400
clark 5400
SQL> SELECT Emp.DEPTNO, Emp.SAL, Emp.JOB FROM
2 (SELECT DEPTNO, SAL, JOB FROM EMPLOYEE GROUP BY DEPTNO, SAL, JOB ORDER BY SAL DESC)Emp
3 WHERE ROWNUM<=3
4 ;
DEPTNO SAL JOB
---------- ---------- --------------------------------------------------
10 5400 manager
10 5400 president
5300 president
SQL> ed
Wrote file afiedt.buf
1 SELECT Emp.DEPTNO, Emp.SAL FROM
2 (SELECT DEPTNO, SAL, JOB FROM EMPLOYEE GROUP BY DEPTNO, SAL, JOB ORDER BY SAL DESC)Emp
3* WHERE ROWNUM<=3
4 ;
DEPTNO SAL
---------- ----------
10 5400
10 5400
5300
SQL> ed
Wrote file afiedt.buf
1 SELECT Emp.DEPTNO, Emp.SAL FROM
2 (SELECT DEPTNO, SAL, JOB FROM EMPLOYEE GROUP BY DEPTNO, SAL, JOB ORDER BY SAL DESC)Emp
3* WHERE ROWNUM<=3
4 ;
DEPTNO SAL
---------- ----------
10 5400
10 5400
5300
SQL> spool off
======================================================
SQL> DECLARE
2 emp employee%ROWTYPE;
3 BEGIN
4 SELECT * INTO emp FROM employee WHERE deptno=77;
5 END;
6 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
SQL> DECLARE
2 emp employee%ROWTYPE;
3 BEGIN
4 SELECT * INTO emp FROM employee WHERE deptno=77;
5 EXCEPTION
6 WHEN NO_DATA_FOUND THEN
7 DBMS_OUTPUT.PUT_LINE('No employees exits');
8 END;
9 /
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> /
No employees exits
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 emp employee%ROWTYPE;
3 BEGIN
4 SELECT * INTO emp FROM employee WHERE deptno=10;
5 EXCEPTION
6 WHEN NO_DATA_FOUND THEN
7 DBMS_OUTPUT.PUT_LINE('No employees exits');
8* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 emp employee%ROWTYPE;
3 BEGIN
4 SELECT * INTO emp FROM employee WHERE deptno=10;
5 EXCEPTION
6 WHEN NO_DATA_FOUND THEN
7 DBMS_OUTPUT.PUT_LINE('No employees exits');
8 WHEN TOO_MANY_ROWS THEN
9 DBMS_OUTPUT.PUT_LINE('More than 1 employee are working in this department');
10* END;
SQL> /
More than 1 employee are working in this department
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 emp employee%ROWTYPE;
3 result NUMBER;
4 BEGIN
5 SELECT * INTO emp FROM employee WHERE deptno=10;
6 result:=emp.sal/0;
7 EXCEPTION
8 WHEN NO_DATA_FOUND THEN
9 DBMS_OUTPUT.PUT_LINE('No employees exits');
10 WHEN TOO_MANY_ROWS THEN
11 DBMS_OUTPUT.PUT_LINE('More than 1 employee are working in this department');
12 WHEN OTHERS THEN
13 DBMS_OUTPUT.PUT_LINE('Other exception occurs');
14* END;
SQL> /
More than 1 employee are working in this department
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 emp employee%ROWTYPE;
3 result NUMBER;
4 BEGIN
5 SELECT * INTO emp FROM employee WHERE deptno=10 and empno=7698;
6 result:=emp.sal/0;
7 EXCEPTION
8 WHEN NO_DATA_FOUND THEN
9 DBMS_OUTPUT.PUT_LINE('No employees exits');
10 WHEN TOO_MANY_ROWS THEN
11 DBMS_OUTPUT.PUT_LINE('More than 1 employee are working in this department');
12 WHEN OTHERS THEN
13 DBMS_OUTPUT.PUT_LINE('Other exception occurs');
14* END;
SQL> /
Other exception occurs
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 emp employee%ROWTYPE;
3 result NUMBER;
4 BEGIN
5 SELECT * INTO emp FROM employee WHERE deptno=10 and empno=7698;
6 result:=emp.sal/0;
7 EXCEPTION
8 WHEN NO_DATA_FOUND THEN
9 DBMS_OUTPUT.PUT_LINE('No employees exits');
10 WHEN TOO_MANY_ROWS THEN
11 DBMS_OUTPUT.PUT_LINE('More than 1 employee are working in this department');
12 WHEN OTHERS THEN
13 DBMS_OUTPUT.PUT_LINE('Other exception occurs');
14 DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
15* END;
SQL> /
Other exception occurs
-1476ORA-01476: divisor is equal to zero
PL/SQL procedure successfully completed.
SQL> desc errorlog;
Name Null? Type
----------------------------------------- -------- ----------------------------
ERRORNO NUMBER
DESCRIPTION VARCHAR2(80)
ERRORDATE DATE
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 emp employee%ROWTYPE;
3 result NUMBER;
4 BEGIN
5 SELECT * INTO emp FROM employee WHERE deptno=10 and empno=7698;
6 result:=emp.sal/0;
7 EXCEPTION
8 WHEN NO_DATA_FOUND THEN
9 DBMS_OUTPUT.PUT_LINE('No employees exits');
10 WHEN TOO_MANY_ROWS THEN
11 DBMS_OUTPUT.PUT_LINE('More than 1 employee are working in this department');
12 WHEN OTHERS THEN
13 DBMS_OUTPUT.PUT_LINE('Other exception occurs');
14 DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
15 INSERT INTO errorlog VALUES(22,SQLERRM,sysdate);
16* END;
SQL> /
INSERT INTO errorlog VALUES(22,SQLERRM,sysdate);
*
ERROR at line 15:
ORA-06550: line 15, column 35:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 15, column 4:
PL/SQL: SQL Statement ignored
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 emp employee%ROWTYPE;
3 result NUMBER;
4 scode VARCHAR2(20);
5 msg VARCHAR2(40);
6 BEGIN
7 SELECT * INTO emp FROM employee WHERE deptno=10 and empno=7698;
8 result:=emp.sal/0;
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 DBMS_OUTPUT.PUT_LINE('No employees exits');
12 WHEN TOO_MANY_ROWS THEN
13 DBMS_OUTPUT.PUT_LINE('More than 1 employee are working in this department');
14 WHEN OTHERS THEN
15 DBMS_OUTPUT.PUT_LINE('Other exception occurs');
16 DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
17 scode:=SQLCODE;
18 msg:=SQLERRM;
19 INSERT INTO errorlog VALUES(22,msg,sysdate);
20* END;
SQL> /
Other exception occurs
-1476ORA-01476: divisor is equal to zero
PL/SQL procedure successfully completed.
SQL> SELECT * FROM errorlog;
ERRORNO
----------
DESCRIPTION
--------------------------------------------------------------------------------
ERRORDATE
---------
22
ORA-01476: divisor is equal to zero
26-AUG-14
1
rr
21-MAY-14
ERRORNO
----------
DESCRIPTION
--------------------------------------------------------------------------------
ERRORDATE
---------
-1422
ORA-01422: exact fetch returns more than requested number of rows
21-MAY-14
SQL> DECLARE
2 CURSOR cname IS SELECT ename FROM employee WHERE deptno=&num;
3 vcEname employee.ename%TYPE;
4 BEGIN
5 OPEN cname;
6 FETCH cname INTO vcEname;
7 LOOP
8 DBMS_OUTPUT.PUT_LINE(vcEname);
9 EXIT WHEN cname%NOTFOUND;
10 FETCH cname INTO vcEname;
11 END LOOP;
12 END;
13 /
Enter value for num: 10
old 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=&num;
new 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=10;
king
blake
clark
clark
PL/SQL procedure successfully completed.
SQL> /
Enter value for num: 33
old 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=&num;
new 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=33;
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 CURSOR cname IS SELECT ename FROM employee WHERE deptno=&num;
3 vcEname employee.ename%TYPE;
4 BEGIN
5 OPEN cname;
6 FETCH cname INTO vcEname;
7 IF cname%ROWCOUNT>0
8 THEN
9 LOOP
10 DBMS_OUTPUT.PUT_LINE(vcEname);
11 EXIT WHEN cname%NOTFOUND;
12 FETCH cname INTO vcEname;
13 END LOOP;
14 ELSE
15 DBMS_OUTPUT.PUT_LINE('msg');
16 END IF;
17* END;
SQL> /
Enter value for num: 77
old 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=&num;
new 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=77;
msg
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 CURSOR cname IS SELECT ename FROM employee WHERE deptno=&num;
3 vcEname employee.ename%TYPE;
4 no_data EXCEPTION;
5 BEGIN
6 OPEN cname;
7 FETCH cname INTO vcEname;
8 IF cname%ROWCOUNT>0
9 THEN
10 LOOP
11 DBMS_OUTPUT.PUT_LINE(vcEname);
12 EXIT WHEN cname%NOTFOUND;
13 FETCH cname INTO vcEname;
14 END LOOP;
15 ELSE
16 --DBMS_OUTPUT.PUT_LINE('msg');
17 RAISE no_data;
18 END IF;
19 EXCEPTION
20 WHEN no_data THEN
21 DBMS_OUTPUT.PUT_LINE('No data exists');
22* END;
SQL> /
Enter value for num: 88
old 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=&num;
new 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=88;
No data exists
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 CURSOR cname IS SELECT ename FROM employee WHERE deptno=&num;
3 vcEname employee.ename%TYPE;
4 no_data EXCEPTION;
5 PRAGMA EXCEPTION_INIT(no_data,-20888);
6 BEGIN
7 OPEN cname;
8 FETCH cname INTO vcEname;
9 IF cname%ROWCOUNT>0
10 THEN
11 LOOP
12 DBMS_OUTPUT.PUT_LINE(vcEname);
13 EXIT WHEN cname%NOTFOUND;
14 FETCH cname INTO vcEname;
15 END LOOP;
16 ELSE
17 --DBMS_OUTPUT.PUT_LINE('msg');
18 --RAISE no_data;
19 RAISE_APPLICATION_ERROR(-20888,'No data exists');
20 END IF;
21 EXCEPTION
22 WHEN no_data THEN
23 DBMS_OUTPUT.PUT_LINE('No data exists');
24 DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
25* END;
SQL> /
Enter value for num: 77
old 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=&num;
new 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=77;
No data exists
-20888ORA-20888: No data exists
PL/SQL procedure successfully completed.
=============================================================================
SQL> CREATE PROCEDURE spDemo1
2 AS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('Hello');
5 END spDemo1;
6 /
Procedure created.
SQL> execute spDemo1;
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> execute spDemo1;
Hello
PL/SQL procedure successfully completed.
SQL> exec spDemo1;
Hello
PL/SQL procedure successfully completed.
SQL> BEGIN
2 spDemo1;
3 END;
4 /
Hello
PL/SQL procedure successfully completed.
SQL> SELECT PROCEDURE_NAME FROM USER_PROCEDURES;
PROCEDURE_NAME
------------------------------
VIEWDEPT
CALCSAL
VIEWDEPT
UPDSALARY
PROCEDURE_NAME
------------------------------
VIEWDETAILS
P
F
PROCEDURE_NAME
------------------------------
SPVIEWEMPS
PROCEDURE_NAME
------------------------------
SPADDEMP
CALCULATEREVISEDSAL
SPDELETEEMP
SPVIEWEMP
SPHELLO
GETEMPS
PROCEDURE_NAME
------------------------------
PROCEDURE_NAME
------------------------------
PROCEDURE_NAME
------------------------------
74 rows selected.
SQL>
SQL> SELECT Text FROM User_Source WHERE Name ='SPDEMO1' ORDER BY Line;
TEXT
--------------------------------------------------------------------------------
PROCEDURE spDemo1
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello');
END spDemo1;
SQL> CREATE OR REPLACE PROCEDURE spDemo1
2 AS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('Helloworld');
5 END spDemo1;
6 /
Procedure created.
SQL> SELECT Text FROM User_Source WHERE Name ='SPDEMO1' ORDER BY Line;
TEXT
--------------------------------------------------------------------------------
PROCEDURE spDemo1
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Helloworld');
END spDemo1;
SQL> CREATE OR REPLACE PROCEDURE spAdd
2 AS
3 result NUMBER;
4 BEGIN
5 result:=10+10;
6 DBMS_OUTPUT.PUT_LINE(result);
7 END spAdd;
8 /
Procedure created.
SQL> exec spAdd
20
PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE PROCEDURE spNum
2 AS
3 num NUMBER;
4 BEGIN
5 num:=10;
6 IF num>0 THEN
7 DBMS_OUTPUT.PUT_LINE('Positive NUMBER');
8 ELSE
9 DBMS_OUTPUT.PUT_LINE('Negative NUMBER');
10 END IF;
11 END spNum;
12 /
Procedure created.
SQL> exec spNum
Positive NUMBER
PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE PROCEDURE spNum(num IN NUMBER)
2 AS
3 BEGIN
4 IF num>0 THEN
5 DBMS_OUTPUT.PUT_LINE('Positive NUMBER');
6 ELSE
7 DBMS_OUTPUT.PUT_LINE('Negative NUMBER');
8 END IF;
9 END spNum;
10 /
Procedure created.
SQL> exec spNum(30);
Positive NUMBER
PL/SQL procedure successfully completed.
SQL> exec spNum(-30);
Negative NUMBER
PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE PROCEDURE spAdd(num1 IN NUMBER,num2 IN NUMBER,num3 OUT NUMBER)
2 AS
3 BEGIN
4 num3:=num1+num2;
5 END;
6 /
Procedure created.
SQL> CREATE OR REPLACE PROCEDURE spAdd(num1 IN NUMBER,num2 IN NUMBER,num3 OUT NUMBER)
2 AS
3 BEGIN
4 num3:=num1+num2;
5 END;
6 /
Procedure created.
SQL> DECLARE
2 result NUMBER;
3 BEGIN
4 spAdd(4,5,result);
5 DBMS_OUTPUT.PUT_LINE(result);
6 END;
7 /
9
PL/SQL procedure successfully completed.
SQL> VARIABLE result NUMBER;
SQL> execute spAdd(7,3,:result);
PL/SQL procedure successfully completed.
SQL> print result
RESULT
----------
10
SQL> CREATE OR REPLACE PROCEDURE spEmp
2 (nEmpno IN employee.empno%TYPE,
3 nSal IN OUT NUMBER)
4 AS
5 nMinSal NUMBER;
6 BEGIN
7 SELECT min(sal) INTO nMinSal FROM employee;
8 IF nSal<nMinSal
9 THEN
10 nSal:=nSal*.3;
11 END IF;
12 END spEmp;
13 /
Procedure created.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 salno NUMBER;
3 BEGIN
4 salno:=&salno;
5 spEmp(&empno,salno);
6 DBMS_OUTPUT.PUT_LINE(salno);
7* END;
SQL> /
Enter value for salno: 1250
old 4: salno:=&salno;
new 4: salno:=1250;
Enter value for empno: 7521
old 5: spEmp(&empno,salno);
new 5: spEmp(7521,salno);
375
PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE PROCEDURE spEmp
2 (nEmpno IN employee.empno%TYPE,
3 nSal IN OUT NUMBER)
4 AS
5 nMinSal NUMBER;
6 BEGIN
7 SELECT min(sal) INTO nMinSal FROM employee;
8 IF nSal<nMinSal
9 THEN
10 nSal:=nSal+nSal*.3;
11 END IF;
12 END spEmp;
13 /
Procedure created.
SQL> DECLARE
2 salno NUMBER;
3 BEGIN
4 salno:=&salno;
5 spEmp(&empno,salno);
6 DBMS_OUTPUT.PUT_LINE(salno);
7 END;
8 /
Enter value for salno: 1250
old 4: salno:=&salno;
new 4: salno:=1250;
Enter value for empno: 7521
old 5: spEmp(&empno,salno);
new 5: spEmp(7521,salno);
1625
PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE FUNCTION fnLength(str VARCHAR2)
2 RETURN NUMBER
3 AS
4 len NUMBER;
5 BEGIN
6 len:=LENGTH(str);
7 RETURN len;
8 END fnLength;
9 /
Function created.
SQL> SELECT fnLength('Test') FROM dual;
FNLENGTH('TEST')
----------------
4
SQL> VARIABLE len NUMBER
SQL> execute :len:=fnLength('Test');
PL/SQL procedure successfully completed.
SQL> print len
LEN
----------
4
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(fnLength('TEst'));
3 END;
4 /
4
PL/SQL procedure successfully completed.
SQL> spool off
============================================================================================================================
Raw
sql dumps
1Z0-007
1. Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
Which three statements insert a row into the table? (Choose three.)
A. INSERT INTO employees VALUES (NULL.'John','Smith');
B. INSERT INTO employees (first_name, last_name) VALUES ('John', 'Smith');
C. INSERT INTO employees VALUES (1000, 'John', 'Smith');
D. INSERT INTO employees (first_name, last_name, employee_id) VALUES (1000,'John', 'Smith');
E. INSERT INTO employees (employee_id) VALUES (1000);
F. INSERT INTO emloyees (employee_id_first_name,last_name) VALUES (1000,'John', 'Smith');
Answer: CEF
2. Click the Exhibit button and examine the data in the EMPLOYEES table
LAST_NAME DEPARTMENT_ID SALARY
Getz 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000
...
Which three subqueries work? (Choose three)
A. SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees. GROUP BY department_id);
B. SELECT * FROM employees WHERE salary = (SELECT AVG (salary) FROM employees GROUP BY department_id);
C. SELECT distinct department_id FROM employees WHERE salary > ANY (SELECT AVG(salary)
FROM employees GROUP BY department _id);
D. SELECT department_id FROM employees WHERE salary > ANY (SELECT MAX (salary)
FROM employees GROUP BY department_id);
E. SELECT last_name FROM employees WHERE salary > ANY (SELECT MAX (salary)
FROM employees GROUP BY department_id);
F. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary).
FROM employees GROUP BY AVG (SALARY);
Answer: CDE
3. Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
JOB_CAT VARCHAR2(30)
SALARY NUMBER(8,2)
Which statement shows the maximum salary paid in each job category of each department?
A. SELECT dept_id, job_cat, MAX (salary) FROM employees WHERE salary > MAX (salary);
B. SELECT dept_id, job_cat, MAX (salary) FROM employees GROUP BY dept_id, job_cat
C. SELECT dept_id, job_cat, MAX(salary) FROM employees;
D. SELECT dept_id, job_cat, MAX (salary) FROM employees GROUP BY dept_id;
E. SELECT dept_id, job_cat, MAX (salary) FROM employees GROUP BY dept_id, job_cat, salary;
Answer: B
4. Which SELECT statement will get the result 'elloworld' fromt the string 'HelloWorld'?
A. SELECT SUBSTR ('HelloWorld',1) FROM dual;
B. SELECT INITCAP(TRIM('HellowWorld', 1,1) FROM dual
C. SELECT LOWER (SUBSTR ('HellowWorld', 2,1) FROM dual
D. SELECT LOWER (SUBSTR('HellowWorld', 2,1) FROM dual
E. SELECT LOWER (TRIM ('H' FROM 'Hello World')) FROM dual
Answer: E
5. Management has asked you to calculate the value 12* salary* commission_pct for all the employees in the EMP table. The EMP table contains these columns:
LAST NAME VARCHAR2(35) NOT NULL
SALARY NUMBER(9,2) NOT NULL
COMMISSION_PCT NUMBER(4,2)
Which statement ensures that a value is displayed in the calculated column for all employees?
A. SELECT last_name, 12 * salary* commission_pct FROM emp;
B. SELECT last_name, 12 * salary* (commission_pct,0) FROM emp;
C. SELECT last_name, 12 * salary* (nvl(commission_pct,0) FROM emp;
D. SELECT last_name, 12 * salary* (decode(commission_pct,0)) FROM emp;
Answer: C
6. Examine the description of the STUDENTS table:
STD_ID NUMBER(4)
COURSE_ID VARCHAR2(10)
START_DATE DATE
END_DATE DATE
Which two aggregate functions are valid on the START_DATE column? (Choose Two)
A. SUM(start_date)
B. AVG (start_date)
C. COUNT (start_date)
D. AVG(start_date, end_date)
E. MIN (start_date)
F. MAXIMUM (start_date)
Answer: CE
7. From SQL*Plus, you issue this SELECT statement:
SELECT * FROM orders;
You use this statement to retrieve data from a database table for _______________. (Choose all that apply)
A. updating
B. viewing
C. deleting
D. inserting
E. truncating
Answer: BD
8. Click the Exhibit button examine the data from the EMP table.
EMP_ID DEPT_ID COMMISSION
1 10 500
2 20 1000
3 10
4 10 600
5 30 800
6 30 200
7 10
8 20 300
The COMMISSION column shows the monthly commission earned by the employee.
Which three tasks would require sub queries or joins in order to be performed in a single step? (Choose three)
A. deleting the records of employees who do not earn commission
B. increasing the commission of employee 3 by the average commission earned in department 20
C. finding the number of employees who do NOT earn commission and are working for department 20
D. inserting into the table a new employee 10 who works for department 20 and earns a commission that is equal to the commission earned by employee 3
E. creating a table called COMMISSION that has the same structure and data as the columns EMP_ID and COMMISSION of the EMP table
F. decreasing the commission by 150 for the employees who are working in department 30 and earning a commission of more than 800.
Answer: BDE
9. Which four statements correctly describe functions that are available in SQL? (Choose four)
A. INSTR returns the numeric position of a named character
B. NVL2 returns the first non-null expression in the expression list.
C. TRUNCATE rounds the column, expression, or value to n decimal places
D. DECODE translates an expression after comparing it to each search value
E. TRIM trims the leading or trailing characters (or both) from a character string.
F. NVL compares two expressions and returns null if they are equal, or the first expression if they are not equal.
G. NULLIF compares two expressions and returns null if they are equal, or the first expression if they are not equal.
Answer : ADEG
10. The EMPLOYEES table has these columns:
LAST_NAME VARCHAR2(35)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER (5,2)
You want todisplay the name and annual salary multiplied by the commission_pct for all employees. For records that have a NULL commission_pct, a zero must be displayed against the calculated column.
Which SQL statement displays the desired results?
A. SELECT last_name, (salary*12)* commission_Pct FROM EMPLOYEES;
B. SELECT last_name, (salary*12)* IFNULL(commission_pct,0) FROM EMPLOYEES;
C. SELECT last_name, (salary*12)* NVL2(commission_pct,0) FROM EMPLOYEES;
D. SELECT last_name, (salary*12)* NVL(commission_pct,0) FROM EMPLOYEES;
Answer: D
11. Which two statements are true regarding the ORDER BY clause? (Choose two)
A. The sort is in ascending order by default
B. The sort is in descending order by default
C. The ORDER BY clause must precede the WHERE clause.
D. The ORDER BY clause is executed on the client side
E. The ORDER BY clause comes last in the SELECT statement
F. The ORDER BY clause is executed first in the query execution.
Answer: AE
12. Click the Exhibit button and examine the data from the ORDERS and CUSTOMERS tables.
ORDERS
ORD_ID ORD_DATE CUST_ID ORD_TOTAL
100 12.JAN.2000 15 10000
101 09.MAR.2000 40 8000
102 09.MAR.2000 35 12500
103 15.MAR.2000 15 12000
104 25.JUN.2000 15 6000
105 18.JUL.2000 20 5000
106 18.JUL.2000 35 7000
107 21.JUL.2000 20 6500
108 04.AUG.2000 10 8000
CUSTOMERS
CUST_ID CUST_NAME CITY
10 Smith Los Angeles
15 Bob San Francisco
20 Martin Chicago
25 Mary New York
30 Rina Chicago
35 Smith New York
40 Linda New York
Which SQL statement retrieves the order ID, customer ID, and order total for the orders that are placed on the same day that Martin paced his orders?
A. SELECT ord_id, cust_id, ord_total FROM orders, customers
WHERE cust_name='Martin' AND ord_date IN ('18-JUL-2000'; 21-JUL-2000');
B. SELECT ord_id, cust_id, ord_total FROM orders
WHERE ord_date IN (SELECT ord_date FROM orders
WHERE cust_id=(SELECT cust_id FROM customers WHERE cust_name= 'Martin'));
C. SELECT ord_id, cust_id, ord_total FROM orders
WHERE ord_date IN (SELECT ord_date FROM orders, customers WHERE cst_name='Martin');
D. SELECT ord_id, cust_id, ord_total FROM orders
WHERE cust_id IN (SELECT cust_id FROM customers WHERE cust name = 'Martin')
Answer: B
13. Evaluate the SQL statement:
1 SELECT a.emp_name, a.sal, a.dept_id, b.maxsal
2 FROM employees a,
3 (SELECT dept_id, MAX(sal) maxsal
4 FROM employees
5 GROUP BY dept_id)b
6 WHERE a.dept_id = b.dept_id
7 AND a.sal<b.maxsal;
What is the result of the statement?
A. The statement produces an error at line1.
B. The statement produces an error at line3.
C. The statement produces an error at line6.
D. The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all departments that pay less salary than the maximm salary aid in the company.
E. The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.
Answer: E
14. Which two tasks can you perform using only the TO_CHAR function? (Choose two).
A. convert 10 to 'TEN'
B. convert '10' to 10
C. convert 10 to '10'
D. convert'TEN' to 10
E. Convert a date to a character expression
F. Convert a character expression to a date
Answer: CE
15. Click the Exhibit button and examine the data in the EMPLOYEES and DEPARTMENTS tables.
EMPLOYEES
EMP_ID EMP_NAME DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 20 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT ADMIN 4200
104 John 30 108 HR_CLERK 2500
105 Diana 30 108 IT_ADMIN 5000
106 Smith 40 110 AD_ASST 3000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EX_DIR 8000
120 Ravi 20 110 SI_DIR 6500
DEPARTMENTS
DEPARTMENT_ID DEPARTMENT NAME
10 Admin
20 Education
30 IT
40 Human Resources
Also examine the SQL statements that create the EMPLOYEES and DEPARTMENTS tables:
CREATE TABLE departments
(department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(30));
CREATE TABLE employees
(EMPLOEE_ID NUMBER PRIMARY KEY,
EMP_NAME VARCHAR2(20),
DEPT_ID NUMBER REFERENCES departments (department_id)
MGR_ID NUMBER REFERENCES employees(employee id),
JOB_ID VARCHAR2(15).
SALARY NUMBER);
On the EMPLOYEES table, EMPLOYEE_ID is the primary key
MGR_ID is the ID of mangers and refers to the EMPLOYEE_ID
DEPT_ID is foreign key to DEPARTMENT_ID column of the DEPARTMENTS table
On the DEPARTMENTS table, DEPARTMENT_ID is the primary key.
Examine this DELETE statement:
DELETE FROM departments WHERE department id=40;
What happens when you execute the DELETE statement?
A. Only the row with department ID 40 is deleted in the DEPARTMENTS table.
B. The statement fails because there are child records in the EMPLOYEES table with department ID 40.
C. The row with department ID 40 is deleted in the DEPARTMENTS table. Also the rows with employee IDs 110 and 106 are deleted from the EMPLOYEES table.
D. The row with department ID 40 is deleted in the DEPARTMENTS table. Also the rows with employee IDs 106 and 110 and the employees working under employee 110 are deleted from the EMPLOYEES table.
E. The row with department ID 40 is deleted in the DEPARTMENTS table. Also all the rows in the EMPLOYEES table are deleted.
F. The statement fails because there are no columns specified in the DELETE clause of the DELETE statement.
Answer: B
16. Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She granted SELECT privilege to Scott on this view.
Which option enables Scott to eliminate the need to qualify the view with the name MARY.EMP_DEPT_LOC_VU each time the view is referenced?
A. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command
CREATE PRIVATE SYNONYM EDL_VU
FOR mary.EMP DEPT_LOC_VU; then he can prefix the columns with this synonym
B. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command
CREATE SYNONYM EDL_VU
FOR mary.EMP DEPT_LOC_VU; then he can prefix the columns with this synonym.
C. Scott can create a synoym for the EMP_DEPT_LOC_VU by using the command
CREATE LOCAL SYNONYM EDL_VU
FOR mary.emp dept_LOC_uv; then he can prefix the columns with the synonym.
D. Scott can create a synomym for the EMP_DEPT_LOC_VU by using the command
CRETE LOCAL SYNONYM EDL_VU
ON mary(EMP_DEPT_LOC_VU); then he can prefix the columns with this synonym
E. Scott cannot create a synonym because synonyms can be created only for tables.
F. Scott cannot create any synonym for Mary's view. Mary should create a private synonym for the view and grant SELECT privilege on that synonym to Scott.
Answer: B
17. Which SQL statement defines a FOREIGN KEY constraint on the DEPT NO column of the EMP table?
A. CREATE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES dept deptno);
B. CREATE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2)
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
C. CRETE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35)
deptno NUMBER (7,2) NOT NULL,
CONSTRAINT em_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));
D. CREATE TABLE EMP
(empno NUMBER (4),
ename VARCHAR2(35),
deptno NUMBER(7,2) FOREIGN KEY
CONSTRAINT emp deptno fk REFERENCES dept (deptno));
Answer: B
18. Evaluate the set of SQL statements:
CREATE TABLE dept
(deptbi NUMBER (2)
dname VARCHAR2(14),
Ioc VARCHAR2(13));
ROLLBACK;
DESCRIBE DEPT
What is true about the set?
A. The DESCRIBE DEPT statement displays the structure of the DEPT table
B. The ROLLBACK statement frees the storage space occupied by the DEPT table.
C. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist
D. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.
Answer: A
19. Which are DML statements? (Choose all that apply)
A. COMMIT...
B. MERGE...
C. UPDATE...
D. DELETE...
E. CREATE...
F. DROP...
Answer: BCD
20. Examine the structure of the EMPLOYEES and DEPARTMENTS tables:
EMPLOYEES
Column name Data Type Remarks
EMPLOYEE_ID NUMBER NOT NULL, PRIMARY KEY
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20)
SALARY NUMBER
MGR_ID NUMBER References employee ID column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of the DEPARTMENT table
DEPARTMENTS
Column name Data Type Remarks
DEPARTMENT_ID NUMBER NOT NULL, Primary key
DEPARTMENT_NAME VARCHAR2(30)
MGR_ID NUMBER References MGR_ID column of the EMPLOYEES table
Evaluate this SQL statement;
SELECT employee_id, e.department_id, department_name, salary
FROM employees e, departments d
WHERE e. department_ud=d.department_id;
Which SQL statement is equivalent to the above SQL statement?
A. SELECT employee_id, department_id, department_name, salary
FROM employees WHERE department_id IN (SELECT department_id FROM departments);
B. SELECT employee_id, department_id, department_name, salary
FROM employees NATURAL JOIN departments d ON e.department_id=d.department_id;
C. SELECT employee_id, department_id, department_name, salary
FROM employees e JOIN departments d ON e.department_id=d.department_id;
D. SELECT employee_id, department_id, department_name, salary
FROM employees JOIN departments USING (e.department_id, d.department_id);
Answer: C
21. Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?
A. SELECT ename, salary*12'Annual Salary' FROM employees;
B. SELECT ename, salary* 12 "Annual Salary" FROM employees
C. SELECT ename, salary* 12 AS Annual Salary FROM employees;
D. SELECT ename, salary* 12 AS INITCAP("ANNUAL SALARY") FROM employees
Answer:B
22. In which scenario would an index be most useful?
A. The indexed column is declared as NOT NULL.
B. The indexed columns are used in the FROM clause
C. The indexed columns are part of an expression
D. The indexed columns contains a wide range of values.
Answer: D
23. Which two are attributes of iSQL* Plus? (Choose two).
A. iSQL * Plus commands cannot be abbreviated
B. iSQL* Plus commands are accessed from a browser.
C. iSQL*Plus commands are used to manipulate data in tables
D. iSQL* Plus command manipulate table definitions in the database
E. iSQL* Plus is the Oracle proprietary interface for executing SQL statements.
Answer: BE
24. Which three statements about subqueries are true? (Choose three).
A. A single row subquery can retrieve only one column and one row
B. A single row subquery can retrieve only one row but many columns
C. A multiple row subquery can retrieve multiple rows and multiple columns
D. A multiple row subquery can be compared using the ">" operator
E. A single row subquery can use the IN operator
F. A multiple row subquery can use the "=" operator
Answer: BCD
25. When should you create a role? (Choose two)
A. to simplify the process of creating new users using the CREATE USER xxx IDENTIFIED by yyy statement
B. to grant a group of related privileges to a user
C. When the number of people using the database is very high
D. to simplify the process of granting and revoking privileges
E. to simplify profile maintenance for a user who is constantly traveling.
Answer: BD
26. Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater than 5000?
A. ORDER BY SALARY > 5000
B. GROUP BY SALARY > 5000
C. HAVING SALARY > 5000
D. WHERE SALARY > 5000
Answer: D
27. Which four are correct guidelines for naming database tables? (Choose four)
A. Must begin with either a number or a letter
B. must be 1-30 characters long
C. should not be an Oracle Server reserved word.
D. must contain only A-Z, a-z, 0-9, _,*, and #
E. must contain only A-Z, a-z, 0-9, _, $, and #
F. must begin with a letter
Answer: BCEF
28. Which two statements about sequences are true? (Choose two)
A. You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a sequence, without actually retrieving the value.
B. You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence.
C. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value form the sequence
D. You use a CURRVAL pseudo column to generate a value from a sequence that would be used for a specified database column.
E. If a sequence starting from a value 100 and incremented by 1 is used by more than one application, then all of these applications could have a value of 105 assigned to their column whose value is being generated by the sequence.
F. You use a REUSE clause when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.
Answer: BC
29. The EMP table contains these columns:
LAST NAME VARCHAR2(25)
SALARY NUMBER (6,2)
DEPARTMENT_ID NUMBER(6)
What is true about this SQL statement?
A. The SQL statement displays the desired results
B. The column in the WHERE clause should be changed to display the desired results.
C. The operator in the WHERE clause should be changed to display the desired results.
D. The WHERE clause should be changed to use an outer join to display the desired results.
Answer: C
30. Examine the description of the MARKS table:
STD_ID NUMBER(4)
STUDENT_NAME VARCHAR2(30)
SUBJ1 NUMBER(3)
SUBJ2 NUMBER(3)
SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects
Examine this SELECT statement based on the MARKS table:
SELECT subj1+subj2 total_marks, std_id
FROM marks WHERE subj1 > AVG (subj1) AND subj2 > AVG (subj2) ORDER BY total_marks;
What us the result of the SELECT statement?
A. The statement executes successfully and returns the student ID and sum of all marks for each student who obtained more than the average mark in each subject.
B. The statement returns an error at the SELECT clause
C. The statement returns an error at the WHERE clause
D. The statement returns an error at the ORDER BY clause
Answer: C
31. You want to display the titles of books that meet these criteria:
1. Purchased before January 21, 2001
2. Price is less than $ 500 or greater than $ 900
You want to sort the result by their date of purchase, starting with the most recently bought book.
Which statement should you use?
A. SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < '21 - Jan-2001'
ORDER BY purchase_date;
B. SELECT book_title FROM books WHERE price IN (500, 900) AND purchase_dae< '21-jan-2001'
ORDER BY purchase date ASC;
C. SELECT book_title FROM books WHERE price < 500 OR>900 AND purchase_date DESC;
D. SELECT BOOK_title FROM books WHERE price BETWEEN 500 AND 900 AND purchase_date<'21-JAN-2001'
ORDER BY purchase date DESC;
E. SELECT book_title FROM books WHERE (price< 500 OR price> 900 AND purchase date> '21 - JAN-2001')
ORDER BY purchase date ASC;
Answer: E
32. Click the Exhibit button to examine the structure of the EMPOLOYEES, DEPARTMENTS and TAX tables.
EMPLOYEES
EMPLOYEE_ID NUMBER NOT NULL primary key
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20)
SALARY NUMBER
MGR_ID NUMBER Reference EMPLOYEE_ID Column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID TO column of the DEPARTMENT table
DEPARTMENTS
DEPARTMENT_ID NUMBER NOT NULL primary key
DEPARTMENT_NAME VARCHAR2(30)
MGR_ID NUMBER Reference MGR_ID column of the EMPLOYEES table
TAX
MIN_SALARY NUMBER
MAX_SALARY NUMBER
TAX_PERCENT NUMBER
For which situation would you use a nonequijoin query?
A. to find the tax percentage for each of the employees
B. to list the name, job id, and manager name for all the employees
C. to find the name, salary and the department name of employees who are not working with Smith
D. to find the number of employees working for the Administrative department and earning less than 4000
E. to display name, salary, manager ID, and department name of all the employees, even if the employees do not have a department ID assigned
Answer: A
33. Which operator can be used with a multiple row sub-query?
A **
B LIKE
C. BETWEEN
D. NOT IN
E. Is
F. <>
Answer: D
34. You need to perform certain data manipulation operations through a view called EMP_DEPT_VU, which you previously created. You want to look at the definition of the view (the SELECT statement on which the view was created) How do you obtain the definition of the view?
A. Use the DESCRIBE command on the EMP_DEPT VU view
B. Use the DEFINE VIEW command on the EMP_DEPT VU view
C. Use the DESCRIBE VIEW command on the EMP_DEPT VU view
D. Query the USER_VIEWS data dictionary view to search for the EMP_DEPT_VU view
E. Query the USER_SOURCE data dictionary view to search for the EMP_DEPT_VU view
F. Query the USER_OBJECTS data dictionary view to search for the EMP_DEPT_VU view
Answer: D
35. Which statement explicitly names a constraint?
A. ALTER TABLE student_grades ADD FOREIGN KEY (student_id) REFERENCES students (student_id);
B. ALTER TABLE student_grades ADD CONSTRAINT NAME=student_id_fk
FOREIGN KEY (student_id) REFERENCES student(student_id);
C. ALTER TABLE student_grades ADD CONSTRAINT student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id);
D. ALTER TABLE student grades ADD NAMED CONSTRAINT student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id)
F. ALTER TABLE student grades ADD NAME student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id)
Answer: C
36. You need to display the last names of those employees who have the letter “A” as the second character in their names. Which SQL statement displays the required results?
A. SELECT last_name FROM EMP WHERE last_name LIKE’_A%;
B. SELECT last_name FROM EMP WHERE last name=’*A%
C. SELECT last_name FROM EMP WHERE last name =’* _A%;
D. SELECT last_name FROM EMP WHERE last name LIKE ‘* a%
Answer: A
37. In which case would you use a FULL OUTER JOIN?
A. Both tables have NULL values
B. You want all unmatched data from one table
C. You want all matched data from both tables
D. You want all unmatched data from both tables
E. One of the tables has more data than the other.
F. You want all matched and unmatched data from only one table.
Answer: D
38. Which two statements about creating constraints are true? (Choose two)
A. Constraint names must start with SYS_C.
B. All constraints must be defined at the column level
C. Constraints can be created after the table is created
D. Constraints can be created at the same time the table is created
E. Information about constraints is found in the VIEW_CONSTRAINTS dictionary view
Answer: CD
39. Examine the SQL statements that creates ORDERS table:
CREATE TABLE orders
(SER_NO NUMBER UNIQUE,
ORDER_ID NUMBER
ORDER_DATE DATE NOT NULL,
STATUS VARCHAR2(10) CHECK (status IN (‘CREDIT’, ‘CASH’)),
PROD_ID NUMBER REFERENCES PRODUCTS (PRODUCT_ID),
ORD_TOTAL NUMBER,
PRIMARY KEY (order id, order date));
For which columns would an index be automatically created when you execute the above SQL statement?
(Choose two.)
A. SER_NO
B. ORDER_ID
C. STATUS
D. PROD_ID
E. PRD_TOTAL
F. Composite index on ORDER_ID and ORDER_DATE
Answer: AF
40. You are granted the CREATE VIEW privilege. What does this allow you to do?
A. create a table view
B. create a view in any scheme
C. create a view in your schema
D. create a sequence view in any schema
E. create a view that is accessible by everyone
F. create a view only if it is based on tables that you created
Answer: C
41. You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and DEPARTMENTS tables EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME
The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key DEPARTMENT_ID column of the DEPARTMENTS table. You want to modify the view by adding a fourth column, MANAGER_Id of NUMBER data type from the EMPLOYEES table. How can you accomplish this task?
A. ALTER VIEW emp_dept_vu (ADD manager_id NUMBER);
B. MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);
C. ALTER VIEW emp_dept_vu AS SELECT employee_id, employee_name Department_name, manager_id
FROM employees e, departments d WHERE department_id = d.department_id;
D. MODIFY VIEW emp_depat_vu AS SELECT employee_id, employee_name, Department_name, manager_id
FROM employees e, departments d WHERE e.department_id = d.department_id;
E. CREATE OR REPLACE VIEW emp_dept_vu AS SELECT emplouee_id, employee_ name, Department_name, manager _id FROM employees e, departments d WHERE e.department_id=d.department_id;
F. You must remove the existing view first, and then run the CRATE VIEW command with a new column list to modify a view.
Answer: E
42. Which three SELECT statements display 2000 in the format “$2,000.00”? (Choose Three).
A. SELECT TO_CHAR (2000, ‘$#,###.##’) FROM dual;
B. SELECT TO_CHAR (2000, ‘$0,000.00’) FROM dual
C. SELECT TO_CHAR (2000, ‘$9,999.00’) FROM dual;
D. SELECT TO_CHAR (2000, ‘$9,999.99’) FROM dual;
E. SELECT TO_CHAR (2000, ‘$2,000.00’) FROM dual;
F. SELECT TO_CHAR (2000, ’$N, NNN.NN’) FROM dual
Answer: BCD
43. Evaluate the SQL statement DROP TABLE DEPT;
Which four statements are true of the SQL statement? (Choose four)
A. You cannot roll back this statement
B. All pending transactions are committed
C. All views based on the DEPT table are deleted
D. All indexes based on the DEPT table are dropped
E. All data in the table is deleted, and the table structure is also deleted
F. All data in the table is deleted, but the structure of the table is retained
G. All synonyms based on the DEPT table are deleted
Answer: ADEG
44. Which statement describes the ROWID data type?
A. binary data up to 4 gigabytes
B. character data up to 4 gigabytes
C. raw binary data of variable length up to 2 gigabytes
D. binary data stored in an external file, up to 4 gigabytes
E. a hexadecimal string representing the unique address of a row in its table
Answer: E
45. Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
NEW EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which UPDATE statement is valid?
A. UPDATE new_employees SET
name=(SELECT last_name||First_name FROM employees WHERE employee_id = 180)
WHERE employee_id = 180
B. UPDATE new_employees SET name = (SELECT Last_name || first_name FROM employees)
WHERE employee_id = 180;
C. UPDATE new_employees SET name = (SELECT last_name|| First_name FROM employees
WHERE employee_id = 180
WHERE employee_id = (SELECT employee_id FROM new employees);
D. UPDATE new_employees SET name = (SELECT last name|| First_name FROM employees
WHERE employee_id= (SELECT employee_id WHERE employee_id FROM new_employees))
WHERE employee_id = 180,
Answer: A
46. You need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address. The CUSTOMER table has these columns:
CUST_ID NUMBER(4) NOT NULL
CUST_NAME VARCHAR2(100) NOT NULL
CUST_ADDRESS VARCHAR2(150)
CUST_PHONE VARCHAR(20)
Which SELECT statement accomplishes this task?
A. SELECT * FROM customers
B. SELECT name, address FROM customers;
C. SELECT id, name, address, phone FROM customers;
D. SELECT cust_name, cust_address FROM customers;
E. SELECT cust_id, cust_name, cust_address, cust_phone FROM customers;
Answer: D
47. Click the Exhibit button to examine the structure of the EMPLOYEES, DEPARTMENTS and LOCATIONS tables.
EMPLOYEES
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP NAME VARCHAR2(30)
JOB_ID VARCHAR2(20)
SALARY NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMNET_ID column of the DEPARTMENTS table
DEPARTMENTS
DEPARTMENT_ID NUMBER NOT NULL, Primary Key
DEPARTMENT_NAME VARCHAR2(30)
MGR_ID NUMBER References MGR_ID column of the EMPLOYEES table
LOCATION_ID NUMBER Foreign key to LOCATION_ID column of the LOCATIONS table
LOCATIONS
LOCATIONS_ID NUMBER NOT NULL, Primary Key
CITY VARCHAR2(30)
Which two SQL statements produce the ;name, department name, and the city of all the employees who earn more than 10000? (Choose Two).
A. SELECT emp_name, department_name, city FROM employees e
JOIN departments d USING (department_id)
JOIN locations 1 USING (location_id) WHERE salary > 10000;
B. SELECT emp_name, department_name, city FROM employees e, departments d, locations 1
JOIN ON (e. department_id = d. department id) AND (d.location_id = 1.location_id) AND salary > 10000;
C. SELECT emp_name, department_name, city FROM employees e, departments d, locations 1
WHERE salary > 1000;
D. SELECT emp_name, department_name, city FROM employees e, departments d, locations 1
WHERE e.department_id = d.department_id AND d.location_id = 1.location_id AND salary > 10000;
E. SELECT emp_name, department_name, city FROM employees e
NATURAL JOIN departments, locations WHERE salary > 10000;
Answer: AD
48. Which two statements complete a transaction? (Choose two)
A. DELETE employees;
B. DESCRIBE employees
C. ROLLBACK TO SAVEPOINT C;
D. GRANT TABLE employees
E. ALTER TABLE employees SET UNUSED COLUMN sal;
F. SELECT MAX (sal) FROM employees
WHERE department_id = 20;
Answer: CE
49. Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
JOB_CAT VARCHAR(30)
SALARY NUMBER(8,2)
Which statement shows the department ID, minimum salary, and maximum salary paid in that department, only if the minimum salary is less than 5000 and maximum salary is more than 15000?
A. SELECT dept_id, MIN (salary), MAX (salary) FROM employees
WHERE MIN(salary) < 5000 AND MAX (salary) > 15000;
B. SELECT dept_id, MIN (salary), MAX (salary) FROM employees
WHERE MIN (salary) < 5000 AND MAX (salary) 15000 GROUP BY dept_id;
C. SELECT dept_id, MIN(salary), MAX(salary) FROM employees HAVING MIN (salary) < 5000 AND MAX (salary)
D. SELECT dept_id MIN (salary), MAX (salary) FROM employees GROUP BY dept_id
HAVING MIN(salary) < 5000 AND MAX (salary) > 15000
E. SELECT dept_id,MIN (salary), MAX (salary) FROM employees GROUP BY dept_id, salary
HAVING MIN (salary) < 5000 AND MAX (salary) > 15000;
Answer: D
50. The DBA issues this SQL command:
CREATE USER scott INDENTIFIED by tiger;
What privileges does the user Scott have at this point?
A. no privileges
B. only the SELECT privilege
C. only the CONNECT privilege
D. all the privileges of a default user
Answer: A
51. The EMPLOYEES table has these columns
LAST_NAME VARCHAR2 (35)
SALARY NUMBER (8,2)
HIRE_DATE DATE
Management wants to add a default value to the SALARY column. You plan to alter the table by using this SQL statement:
ALTER TABLE EMPLOYEES
MODIFY (SALARY DEFAULT 5000);
Which is true about your ALTER statement?
A. Column definitions cannot be altered to add DEFAULT values
B. A change to the DEFAULT value affects only subsequent insertions to the table
C. Column definitions cannot be altered to add DEFAULT values for columns with a NUMBER data type.
D. All the rows that have a NULL value for the SALARY column will be updated with the value 5000.
Answer: B
52. Which substitution variable would you use if you want to reuse the variable value without prompting the user each time?
A. &
B. ACCEPT
C. PROMPT
D. &&
Answer: D
53. Examine the structure of the EMPLOYEES table:
Column name Data type Remarks
EMPOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20) NOT NULL
SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column
Of the DEPARTMENTS table
You need to create a view called EMP_VU that allows the users to insert rows through the view.
Which SQL statement, when used to create the EMP_VU view, allows the users to insert rows?
A. CREATE VIEW emp_Vu AS
SELECT employee_id, emp_name, Department_id FROM employees WHERE mgr_id IN (102,120);
B. CREATE VIEW emp_Vu AS
SELECT employee_id, emp_name, job_id, Department_id FROM employees WHERE mgr_id IN (102, 120);
C. CREATE VIEW emp_Vu AS
SELECT department_id, SUM(sal) TOTAL SAL FROM employees WHERE mgr_id IN (102, 120)
GROUP BY department_id;
D. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, DISTINCT department_id
FROM employees
Answer: B
54. What is true about the WITH GRANT OPTION clause?
A. It allows a grantee DBA privileges
B. It is required syntax for object privileges
C. It allows privileges on specified columns of tables
D. It is used to grant an object privilege on a foreign key column
E. It allows the grantee to grant object privileges to other users and roles
Answer: E
55. The STUDENT_GRADES table has these columns
STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER (4,3)
The registrar has asked for a report on the average grade point average (GPA) for students enrolled during semesters that end in the year 2000. Which statement accomplishes this?
A. SELECT AVERAGE(gpa) FROM student_grades
WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’
B. SELECT COUNT (gpa) FROM student grades
WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’
C. SELECT MID (gpa) FROM student_grades
WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’
D. SELECT AVG (gpa) FROM student_grades
WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’
E. SELECT SUM (gpa) FROM student_grades
WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’
F. SELECT MEDIAN (gpa) FROM student_grades
WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’
Answer: D
56. Which constraint can be defined only at the column level?
A. UNIQUE
B. NOT NULL
C. CHECK
D. PRIMARY KEY
E. FOREIGN KEY
Answer: B
57. In which scenario would Top N analysis be the best solution?
A. You want to identify the most senior employee in the company
B. You want to find the manager supervising the largest number of employees
C. You want to identify the person who makes the highest salary of all employees
D. You want to rank the top three sales representatives who have sold the maximum number of products
Answer: D
58. Examine the structure of the EMPLOYEES and NEW EMPOYEES tables:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
NEW EMPLYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which MERGE statement is valid?
A. MERGE INTO new_employees e USING employees e ON (e.employee_id = e.employee_id)
WHEN MATCHED THEN UPDATE SET e.name = e.first_name ||’,’|| e.last_name
WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name||’,‘||e.last_name);
B. MERGE new_employee c USING employees e ON (c.employee_id = e.employee_id)
WHEN EXISTS THEN UPDATE SET c.name = e first_name||’,’|| e.last_name
WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name||’.‘||e.last_name);
C. MERGE INTO new employees c USING employees e ON (c.employee_id = e.employee_id)
WHEN EXISTS THEN UPDATE SET e.name = e.fist ||’,’|| e.last_name
WHEN NOT MATCHES THEN INSERT VALUES (e.employee_id, e.first _name||’,‘||e.last_name);
D. MERGE new_employees c FROM employees c ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN UPDATE SET e.name = e.first_name ||’,’|| e.last_name
WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES (e.employee_id, e.first_name||”.‘||e.last_name);
Answer: A
59. Which three are true regarding the use of outer joins? (Choose three.)
A. You cannot use IN operator in a condition that involves an outerjoin
B. You use (+) on both sides of the WHERE condition to perform an outerjoin
C. You use (*) on both sides of the WHERE condition to perform an outerjoin.
D. You use an outerjoin to see only the rows that do not meet the join condition
E. In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin
F. You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator
Answer: AEF
60. Click the Exhibit button to examine the data of the EMPLOYEES table.
EMPLOYEES (EMPLOYEE_ID is the primary key.
MGR_ID is the ID of managers and refers to the EMPLOYEE_ID)
EMPLOYEE_ID EMP_NINE DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 20 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 2500
105 Diana 30 108 HR_MGR 5000
106 Bryan 40 110 AD_ASST 5000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EX_DIR 8000
120 Ravi 20 110 SA_DIR 6500
Which statement lists the ID, name, and salary of the employee, and the ID and name of the employee’s manager, for all the employees who have a manager and earn more than 4000?
A. SELECT employee_id ”Emp_id”, emp_name “Employee”.Salary,Employee_id “Mgr_id”, emp_name “Manager”
FROM employees WHERE salary > 4000
B. SELECT e.employee_id “Emp_id”, e.emp_name “Employee” e.salary
m employee_id “Mgr_id”, m.emp_name “Employee”.
FROM employees e.employees m WHERE e.mgr_id = m.mgr_id AND e.salary > 4000;
C. SELECT e. employee_id “Emp_id”. E.emp_name “Employee” e.salary m employee_id “Mgr_id”,
m.emp_name “Manager” FROM employees e, employees mWHERE e.mgr_id = m.employee_id
AND e.salary > 4000
D. SELECT e.employee_id”Emp_id” e.emp_name “Employee” e.salary. m.mgr_id “Mgr_id”, m.emp_name “Employee”, FROM employees e, employees m
WHERE e.mgr_id = m.employee_id AND e.salary > 4000;
Answer: C
61. Which statement creates a new user?
A. CREATE USER susan
B. CREATE OR REPLACE USER susan
C. CREATE NEW USER susan DEFAULT,
D. CREATE USER susan INDENTIFIED BY blue
E. CREATE NEW USER susan IDENTIFIED BY blue
F. CREATE OR REPLACE USER susan IDENTIFIED BY blue;
Answer: D
62. The STUDENT_GRADES table has these columns
STUDENT_ID NUMBER (12)
SEMESTER_END DATE
GPA NUMBER (4,3)
The registrar has requested a report listing the students’ grade point averages (GPA), stored from highest grade point average to lowest within each semester, starting from the earliest date. Which statement accomplishes this?
A. SELECT student)_id, semester_end, gpa FROM student_grades
ORDER BY semester_end DESC, gpa DESC;
B. SELECT student_id, semester_end, gpa FROM student_grades
ORDER BY semester_end ASC, gpa ASC;
C. SELECT student _id, semester_end, gpa FROM student_grades
ORDER BY semester_end, gpa DESC;
D. SELECT student_id, semester_end, gpa FROM student_grades
ORDER BY gpa DESC, semester_end DESC;
E. SELECT student-id, semester_end, gpa FROM student_grades
ORDER BY gpa DESC, semester_end ASC;
Answer: C
63. You need to change the definition of an existing table. The COMMERCIALS table needs its DESCRIPTION column changed to hold varying length characters up to 2000 bytes. The column can currently hold 1000 bytes per value. The table contains 20000 rows. Which statement is valid?
A. ALTER TABLE commercial MODIFY (description CHAR2(2000))
B. ALTER TABLE commercials CHANGE (description CHAR2(2000))
C. ALTER TABLE commercials CHANGE (description varchar2(2000))
D. ALTER TABLE commercials MODIFY (description VARCHAR2(2000))
E. You cannot increase the size of a column if the able has rows.
Answer: D
64. What does the TRUNCATE statement do?
A. removes the table
B. removes all rows from a table
C. shortens the tale to 10 rows
D. removes all columns from a table
E. removes foreign keys from a table
Answer; B
65. The ORDERS table has these columns
ORDER_ID NUMBER(4) NOT NULL
CUSTOMER_ID NUMBER(12) NOT NULL
ORDER_TOTAL NUMBER(10,2)
The ORDERS table tracks the Order number, the order total and the customer to whom the Order belongs. Which two statements retrieve orders with an inclusive total that ranges between 100.00 and 200.00 dollars? (Choose Two).
A SELECT customer_id, order_id, order_total FROM orders
RANGE ON order_total (100 AND 2000) INCLUSIVE
B. SELECT customer_id, order_id, order_total FROM orders
HAVING order total BETWEEN 100 and 2000
C. SELECT customer_id, order_id, order_total FROM orders
WHERE order_total BETWEEN 100 and 2000
D. SELECT customer_id, orde_id, order_total FROM orders
WHERE order_total >= 100 and <=2000
E. SELECT customer_id, order_id, order _total FROM orders
WHERE order_total>= 100 and order_total <=2000.
Answer: CE
66. Which is an iSQL * Plus command?
A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE
E. DELETE
F. RENAME
Answer; D
67. Which SELECT statement should you use to extract the year form the system date and display it in the format “1998”?
A. SELECT TO_CHAR(SYSDATE, ‘yyyy’) FROM dual
B. SELECT TO_DATE(SYSDATE,’yyyy’) FROM dual
C. SELECT DECODE (SUBSTR (SYSDATE, 8), ‘YYYY’) FROM dual
D. SELECT DECODE (SUBSTR (SYSATE, 8),’year’) FROM dual
E. SELECT TO_CHAR (SUBSTR(SYSDATE, 8,2),’yyyy’) FROM dual
Answer: A
68. The EMPLOYEES table contains these columns:
LAST_NAME VARCHAR2(25)
SALARY NUMBER(6,2)
COMMISSION_PCT NUMBER(6)
You need to write a query that will produce these results:
1. Display the salary multiplied by the commission_pct
2. Exclude employees with a zero commission_pct
3. Display a zero for employees with a null commission value
4.
Evaluate the SQL statement:
SELECT LAST_NAME, SALARY * COMMISSION_PCT FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
What does the statement provide?
A. all of the desired results
B. two of the desired results
C. one of the desired results
D. an error statement
Answer: C
69. A subquery can be used to _________.
A. create groups of data
B. sort data in a specific order
C. convert data to a different format
D. retrieve data based on an unknown condition
Answer: D
70. Which clause should you use to exclude group results?
A. WHERE
B. HAVING
C. RESTRICT
D. GROUP BY
E. ORDER BY
Answer: B
71. Scott issues the SQL statements:
CREATE TABLE dept
(deptno number(2)
dname VARCHAR2(14)
loc VARCHAR2(13));
GRANT SELECT ON DEPT TO SUE;
If Sue needs to select from Scott’s DEPT table, which command should she use?
A. SELECT * FROM DEPT
B. SELECT * FROM SCOTT. DEPT.
C. SELECT * FROM DBA.SCOTT.DEPT.
D. SELECT * FROM ALL_USERS WHERE USER_NAME = ‘SCOTT’ AND TABLE NAME= ‘DEPT’;
Answer: B
72. Click the Exhibit button and examine the data in the EMPLOYEES and EMP_HIST tables.
EMPLOYEES
EMPLOYEE_ID NAME DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 20 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 2500
105 Diana 30 108 IT_ADMIN 5000
106 Smith 40 110 AD_ASST 3000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EX_DIR 8000
120 Ravi 20 110 SA_DIR 6500
EMP_HIST
EMPLOYEE_ID NAME JOB_ID SALARY
101 Smith SA_CLERK 2000
103 Chris IT_CLERK 2200
104 John HR_CLERK 2000
105 Smith AD_ASST 3000
108 Jennifer HR_MGR 4500
The EMP_HIST table is updated at the end of every year. The employee ID, name, job ID, and salary of each existing employee are modified with the latest date. New employee details are added to the table.
Which statement accomplishes this task?
A. UPDATE emp_hist SET employee_id, name, job_id, salary =
(SELECT employee id, name, job_id, salary FROM employees)
WHERE employee_id IN (SELECT employee_id FROM employees),
B. MERGE INTO emp_hist eh USING employees e ON (eh. Employee_id = e.employee_id)
WHEN MATCHED THEN UPDATE SET eh. Name= e.name, Ch.job_id = e.job_id, Eh. Salary = e.salary
WHEN NOT MATCHED THEN INSERT (eh.employee_id,eh.name,eh.job_id,eh.salary) VALUES (e.employee_id, e.name, e.job_id, e.salary);
C. MERGE INTO emp_hist eh USING employees e ON (eh.employee_id = e.employee_id)
WHEN MATCHED THEN UPDATE emp_hist
SET eh.name = e.name, eh.job_id = e.job_id, eh.salary = e.salary
WHEN NOT MATCHED THEN INSERT INTO emp_hist
VALUES (e.employee_id, e.name, e.job_id, e.salary);
D. MERGE INTO emp_hist eh USING employees e
WHEN MATCHED THEN UPDATE emp_hist
SET eh.name = e.name, eh.job_id = e.job_id, eh.salary = e.salary
WHEN NOT MATCHED THEN INSERT INTO emp_hist
VALUES (e.employee_id, e.name, e.job_id, e.salary);
Answer: B
73. Click the Exhibit button to examine the data of the EMPLOYEES table
EMPLOYEES (EMPLOYEE ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID)
EMPLOYEE_ID EMP_NAME DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 230 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 2500
105 Diana 30 108 HR_MGR 5000
106 Bryan 40 110 AD_ASST 3000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EX_DIR 8000
120 Ravi 20 110 SA_DIR 6500
Evaluate this SQL statement:
SELECT e.employee_id “emp_id”, e.emp_name “Employee”, e.salary,
m.employee_id “Mgr_id”, m.emp_name “Manager” FROM employees e,employees m
WHERE e.mgr_id = m.employee_id AND e.salary > 4000 What is its output?
A.
Emp_id Employee Salary Mgr_id Manager
110 Bob 8000 Bob
120 Ravi 6500 110 Ravi
108 Jennifer 6500 110 Jennifer
103 Chris 4200 120 Chris
105 Diana 5000 108 Diana
B.
Emp_id Employee Salary Mgr_id Manager
120 Ravi 6500 110 Bob
108 Jennifer 6500 110 Bob
103 Chris 4200 120 Ravi
105 Diana 5000 108 Jennifer
C.
Emp_id Employee Salary Mgr_id Manager
110 Bob 8000
120 Ravi 6500 110 Bob
108 Jennifer 6500 110 Bob
103 Chris 4200 120 Ravi
105 Diana 5000 108 Jennifer
D.
Emp_id Employee Salary Mgr_id Manager
110 Bob 8000 110 Bob
120 Ravi 6500 120 Ravi
108 Jennifer 6500 108 Jennifer
109 Chris 4200 105 Chris
105 Diana 5000 105 Diana
E. The SQL statement produces an error.
Answer: B
74. What is true about joining tables through an equation?
A. you can join a maximum of two tables through an equation
B. you can join a maximum, of two columns through an equation
C. you specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.
D. To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns.
E. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.
Answer: E
75. You need to calculate the total of all salaries in the accounting department. Which group function should you use?
A. MAX
B. MIN
C. SUM
D. COUNT
E. TOTAL
F. LARGEST
Answer: C
76. Click the Exhibit button and examine the data in the EMPLOYEES table.
LAST_NAME DEPARTMENT_ID SALARY
Get 2 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000
….
Which three subqueires work? (Choose three)
A. SELECT * FROM employees Where salary > (SELECT MIN(salary)
FROM employees GROUP BY department_id)
B. SELECT * FROM employees WHERE salary = (SELECT AVG (salary)
FROM employees GROUP BY department_id)
C. SELECT distinct department-id FROM employees
WHERE salary> ANY (SELECT AVG (salary) FROM employees GROUP BY department_id)
D. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG (salary)
FROM employees GROUP BY department_id)
E. SELECT last_name FROM employees WHERE salary> ANY (SELECT MAX (salary)
FROM employees GROUP BY department_id)
F. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG (salary)
FROM employees GROUP BY AVG (SALARY))
Answer: CDE
77. The EMP table has these columns:
ENAME VARCHAR2(35)
SALARY NUMBER (8,2)
HIRE_DATE DATE
Management wants a list of names of employees who have been with the company for more than five yeas. Which SQL statement displays the required results?
A. SELECT ENAME FROM EMP WHERE SYSDATE-HIRE_DATE>5
B. SELECT ENAME FROM EMP WHERE HIRE_DATE-SYSDATE > 5
C. SELECT ENAME FROM EMP WHERE (SYSDATE-_DATE)|365 > 5
D. SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)* 365 > 5
Answer: C
78. You would like to display the system date in the format *Monday, 01 June, 2001*
Which SELECT statement should you use?
A. SELECT TO_DATE (SYSDATE, ‘FMDAY, DD Month, YYYY’) FROM dual
B. SELECT TO_CHAR(SYSDATE, ‘FMDD, DY Month ‘YYY’) FROM dual
C. SELECT TO_CHAR(SYSDATE, ‘FMDay, DD Month YYYY’) FROM dual
D. SELECT TO_CHAR(SYSDATE, ‘FMDAY, DDD Month, YYYY’) FROM dual
E. SELECT TO_DATES(SYSDATE,’FMDY, DDD Month, YYYY’) FROM dual
Answer: C
79. The CUSTOMERS table has these columns:
CUSTOMER_ID NUMBER (4) NOT NULL
CUSTOMER_NAME VARCHAR2(100)
STREET_ADDRESS VARCHAR2(150)
CITY_ADDRESS VARCHAR2(50)
STATE_ADDRESS VARCHAR2(50)
PROVINCE_ADDRESS VARCHAR2(50)
COUNTRY_ADDRESS VARCHAR2(50)
POSTAL_CODE VARCHAR2(12)
CUSTOEMR_PHONE VARCHAR2(20)
Which statement finds the rows in the CUSTOMERS table that do not have a postal code ?
A. SELECT customer_id, customer_name FROM customers WHERE postal_code CONTAINS NULL
B. SELECT customer_id, customer name FROM customers WHERE posta_code=’______________’
C. SELECT customer_id, customer_name FROM customers WHERE postal_code IS NULL
D. SELECT customer_id, customer_name FROM customers WHERE postal code IS NVL
E. SELECT customer_id, customer_name FROM customers WHERE postal_code=NULL
Answer: C
80. Evaluate this SQL statement
SELECT e.employee_id, (15*e.salary) + .(5* e.commission_pct) +
(s.sales amount* (.35* e.bonus)) AS CALC_VALUE
FROM employees e,sales s
WHERE e.employee_id = s.emp_id;
What will happen if you remove al the parentheses from the calculation?
A. The value displayed in the CALC_VALUE column will be lower
B. The value displayed in the CALC_VALUE column will be higher
C. There will be no difference in the value displayed in the CALC_VALUE column
D. An error will be reported.
Answer: C
81. You define a multiple-row subquery in the WHERE clause of an SQL query with a comparison operator”=” What happens when the main query is executed?
A. the main query executes with the first value returned by the subquery
B. the main query executes with the last value returned by the subquery
C. the main query executes with all the values returned by the subquery
D. the main query fails because the multiple-row subquery cannot be used with the comparison operator.
E. You cannot define multiple-row subquery in the WHERE clause of a SQL query
Answer: D
82. which three statements correctly describe the functions and use of constraints? (Choose three)
A. constraints provide data independence
B. constraint make complex queries easy
C. constraints enforce rules at the view level
D. constraints enforce rules at the table level
E. constraints prevent the deletion of a table if there are dependencies
F. constraints prevent the deletion of an index if there are dependencies
Answer: CDE
83. Which two are character manipulation functions? (Choose two)
A. TRIM
B. REPLACE
C. TRUNC
D. TO_DATE
E. MOD
F. CASE
Answer: AB
84. You need to create a view EMP_VU. The view should allow the users to manipulate the records of only the employees that are working for departments 10 or 20. Which SQL statement would you use tocrete the view EMP_VU?
A. CREATE VIEW emp_vu AS SELECT employees WHERE department_id 1N (10,20)
B. CREATE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20) WITH READ ONLY
C. CREATE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20) WITH CHECK OPTION
D. CREATE FORCE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10, 20) NO UPDATE
Answer: C
85. Evaluate these two SQL statemens
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC
What is true about them?
A. the two statements produce identical results
B. the second statement returns a syntax error
C. there is no need to specify DESC because the results are sorted in descending order by default
D. the two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statements
Answer: A
86. Click the Exhibit button and examine the data on the EMPLOYEES table
EMPLOYEES
EMPLOYEE_ID EMP_NAME DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 20 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 3500
105 Diana 30 108 IT_ADMIN 5000
106 Smith 40 110 AD_ASST 3000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EX_DIR 8000
120 Ravi 20 110 SA_DIR 6500
On the EMPLOYEES table, EMPLOYEE_ID is the primary key.
MGR_ID is the ID of managers and refers to the EMPLOYEE_ID.
The JOB_ID column is a NOT NULL column
Evaluate This DELETE statement
DELETE employee_id, salary, job_id FROM employees WHERE dept_id = 90
Why does the DELETE statement fail when you execute it?
A. there is no row with dept_id 90 in the EMPLOYEES table
B. you cannot delete the JOB_ID column because it is a NOT NULL column
C. you cannot specify column names in the DELETE clause of the DELETE statement.
D. You cannot delete the EMPLOYEE_ID column because it is the primary key of the table
Answer: C
87. Which two statements accurately describe a role? (Choose two)
A. a role can be given to a maximum of 1000 users
B. a user can have access to a maximum of 10 roles
C. a role can have a maximum of 100 privileges contained in it.
D. Privileges are given to a role by using the CREATE ROLE statement.
E. A role is a named group of related privileges that can be granted to the user
F. A user can have access to several roles, and several users can be assigned the same role.
Answer: EF
89. You added a PHONE-NUMBER column of NUMBER data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 100 employees. Now, you want to enter the phone numbers of each of the 100 employees into the table Some of the employees may not have a phone number available.
Which data manipulation operation do you perform?
A. MERGE
B. INSERT
C. UPDATE
D. ADD
E. ENTER
F. You cannot enter the phone number for the existing employee records
Answer: C
90. Which two statements about subqueries are true? (Choose two)
A. A single row subquery can retrieve data from only one table.
B. A SQL query statement cannot display data from table B that is refered to in its subquery, unless table B is included in the main query’s FROM clause.
C. A SQL query statement cannot display data from table B that is refered to in its subquery, without including table B in its own FROM clause.
D. A single row subqery can retrieve data from more than one table
E. A single row subqery cannot be used in a condition where the LIKE operator is used for comparison.
F. A multiple-row subquery cannot be used in a condition where the LIKE operation is used for comparison.
Answer: BD
91. Examine the structure of the STUDENTS table
STUDENT_ID NUMBER NOT NULL., Primary Key
STUDENT_NAME VARCHAR2(30)
COURSE_ID VARCHAR2(10) NOT NULL
MARKS NUMBER
START_DATE DATE
FINISH_DATE DATE
You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.
Which SQL statement accomplishes this task?
A. SELECT student_id, marks, ROWNUM “Rank” FROM student WHERE ROWNUM <= 10
AND finish_data BETWEEN ’01-JAN-99’ AND ’31-DEC-99’ AND course_id=’INT_SQL’ ORDER BY marks DESC;
B. SELECT student_id, marks, ROWID “Rank” FROM students WHERE ROWID <= 10
AND finish_data BETWEEN ’01-JAN-99’ AND ’31-DEC-99’ AND course_id=’INT_SQL’ ORDER BY marks;
C. SELECT student_id, marks ROWNUM “Rank” FROM (SELECT student_id, marks) FROM students
WHERE ROWNUM <= 10 AND finish_date BETWEEN ’01-JAN99’ AND ’31-DEC-99’
AND COURSE_ID = ‘INT_SQL’ ORDER BY marks desc:
D. SELECT student_id, marks, ROWNUM ” Rank”
FROM (SELECT student_id.marks FROM students ORDER BY marks DESC )
WHERE ROWNUM <= 10 AND
finish_date BETWEEN ’01-JAN99’ AND ’31-DEC-99’ AND course_id = ‘INT_SQL;’
Answer: D
92. The CUSTOMERS table has these columns:
CUSTOMER_ID NUMBER(4) NOT NULL
CUSTOMER_NAME VARCHAR2(100) NOT NULL
STREET_ADDRESS VARCHAR2(150)
CITY_ADDRESS VARCHAR2(50)
STATE_ADDRESS VARCHAR2(50)
PROVINCE_ADDRESS VARCHAR2(50)
COUNTRY_ADDRESS VARCHAR2(50)
POSTE_CODE VARCHAR2(12)
CUSTOMER_PHONE VARCHAR2(20)
THE CUSTOMER_ID column is the primary key for the table which two statements find the number of customer? (Choose two.)
A. SELECT TOTAL (*) FROM customers;
B. SELECT COUNT (*) FROM customers;
C. SELECT TOTAL (customer_id) FROM customer;
D. SELECT COUNT(costomer_id) FROM customer;
E. SELECT COUNT(customers) FROM customers;
F. SELECT TOTAL (customer_name) FROM customers;
Anser : BD
93. In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed statement?
A. immediately after the SELECT clause
B. before the WHERE clause
C. before the FROM clause
D. after the ORDER BY clause
E. after the WHERE clause
Answer : E
94. Which two are true about aggregate functions? (Choose two)
A. You can use aggregate functions in any clause of a SELECT statement.
B. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.
C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns
D. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.
E. You can use aggregate functions on a table, only by grouping the whole table as one single group.
F. You cannot group the rows of a table by more than one column while using aggregate functions.
Answer CD
95. For which two constrains does the Oracle Server implicitly create a unique index? (Choose two)
A. NOT NULL
B. PRIMARY KEY
C. FOREIGN KEY
D. CHECK
E. UNIQUE
Answer: BE
96. Check the Exhibit button to examine the structures of the Employees and TAX tables Employees.
EMPLOYEE_ID NUMBER NOT NULL. PRIMARY KEY
EMP_NAME VARCHAR(30)
JOB_ID VARCHAR2(20)
SALARY NUMBER
MGR_ID NUMBER References EMPLOYEE_TO column
DEPARTMENT_ID NUMBER Foreign Employee_ID column of the DEPARTMENT table
TAX
MIN_SALARY NUMBER
MAX_SALARY NUMBER
TAX_PERCENT NUMBER Percentage tax for given salary range
You need find the percentage tax applicable for each employee. Which SQL statement would you use?
A. SELECT employee_id salary, tax_present FROM employee, tax t
WHERE e salary BETWEEN t.min_salary AND t.max_salary,
B. SELECT employee_id, salary, tax_percent
FROM employees e, tax t
WHERE e.salary> Lmin_salary,tax_percent
FROM employees e, tax t
WHERE MIN(e salary)= t.min_salary
C. SELECT employee_id, salary, tax_percent FROM employees e, tax t
WHERE MIN(e.salary) = t.min_salary AND MAX(e.salary) = t.max_salary;
D. You cannot find the information because there is no common column between the two tables.
Answer : A
97. Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
You issue these statements:
CREATE table new_emp
(employee_id NUMBER,
name VARCHAR2 (30));
INSERT INTO new_emp SELECT employee_id, last_name from employees;
Savepoint s2;
Delete from new_emp;
Rolback to s2;
Delete from new_emp where employee_id = 180;
UPDATE new_emp set name = ‘James’;
Rolback to s2;
UPDATE new_emp set name = ‘James’ WHERE employee_id = 180;
Rollback;
At the end of this transaction, what is true?
A. You have no rows in the table.
B. You have an employee with the name of James
C. You cannot roll back to the same savepoint more than once.
D. Your last update fails to update any rows because employee ID 180 was already deleted.
Answer : A
98. Which iSQL*Plus feature can be used to replace values in the where clause?
A. Substitution variables
B. replacement variables
C. prompt variables
D. instead-of variables
E. This feature cannot be implemented through | SQL*Plus
Answer : A
99.Evaluate the SQL statement:
SELECT ROUND(TRUNC(MOD(1600,10),-1),2) FROM dual;
What will be displayed?
A. 0
B. 1
C. 0.00
D. an error statement
Answer : A
100. Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER
SALARY NUMBER
What is the correct syntax for an inline view?
A SELECT a last_name, a salary, a department_id, b.maxsal FROM employees a,
(SELECT department_id, max(salary)maxsal FROM employees GROUP BY department_id) b
WHERE a department_id = department-id AND a_salary<b.maxsal;
B. SELECT a. last name, a salary, a. department_id FROM employees a
WHERE a. department_id IN (SELECT department_id FROM employees b
GROUP BY department_id having salary = SELECT max(salary) from employees
C. SELECT a last_name, a salary, a.department_id FROM employees a
WHERE a salary = SELECT max(salary) FROM employees b
WHERE a department_id = department_id);
D. SELECT a last_name, a salary, a.department_id FROM employees a
WHERE (a department_id, a salary) IN (SELECT department_id, a salary) IN
(SELECT department_id max(salary) FROM employees b GROUP BY department_id ORDER BY department_id);
Answer : A
101. Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER NOT NULL
EMP_ID VARCHAR2(30)
JOB_ID VARCHAR2(20) DEFAULT ‘SA_REP’
SAL NUMBER
COMM_PCT NUMBER
MGR_ID NUMBER
DEPARTMENT_ID NUMBER
you need to update the records of employees 103 and 115. The UPDATE statement you specify should update the rows with the values specified below:
JOB_ID Default value specified for this column definition
SAL maximum salary earned for the_job ID SA_REP
COMM_PCT Default value is specified for the column, the value should be NULL
DEPARTMENT_ID: Supplied by the user during run time through substitution variable
which UPDATE statement meets the requirements?
A. UPDATE employees
SET job_id=DEFAULT AND Sal=(SELECT MAX(sal) FROM emoployees WHERE job_id='SA_REP'
AND comm_pet=DEFALUT AND department_id =&did WHERE employee_id IN (103, 115),
B. UPDATE employees SET job_id = DEFAULT AND Sal = MAX(sal) AND comm_pct = DEFAULT OR NULL
AND department _id = & did WHERE employee_id IN (103,115) AND ob_id = 'SA_REP'
C. UPDATE employees SET job_id = DEFAULT, Sal = (SELECT MAX (sal) FROM employees WHERE job_id = 'SA_REP'),
comm_pct = DEFAULT, department _id = &did WHERE employee_id IN (103,115)
D. UPDATE emplouees SET job_id = DEFAULT sal = MAX (sal) comm_pct = DEFAULT department_id = &did
WHERE employee_id IN (103,115) AND job_id = 'SA_REP'
E. UPDATE employees SET job_id = DEFAULT Sal = (SELECT MAX(sal) FROM employees WHERE job_id = 'SA_REP')
comm_pct = DEFAULT OR NULL, department_id = &did WHEREemployee_id IN (103,115)
Answer: C
102. Which data dictionary table should you query to view the object privileges granted to the user on specific columns?
A. USER_TAB_PRIVS_MADE
B. USER_TAB_PRIVS_RECD
C. USER_COL_PRIVS_MADE
D. USER_COL_PRIVS_RECD
Answer: D
103. Which three are DATETIME data types that can be used when specifying column definitions? (Choose three)
A. TIMESTAMP
B. INTERVAL MONTH TO DAY
C. INTERVAL DAY TO SECOND
D. INTERVAL YEAR TO MONTH
E. TIMESTAMP WITH DATABASE TIMEZONE
Answer: ACD
104. Examine the structure of the EMPLOYEES table:
column name data type remarks
EMPLOYEE_ID NUMBER NOT NULL, primary key
LAST_NAME VARCHAR2(30)
FIRST_NAME VARCHAR2(30)
JOB_ID NUMBER
SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER
You need to create an index called NAME IDX on the first name and last name fields of the EMPLOYEES table. Which SQL statement would you use to perform this task?
A. CREATE INDEX NAME_IDX (first_name, last_name)
B. CREATE INDEX NAME_IDX (first_name AND last_name)
C. CREATE INDEX NAME_IDX ON (first_name, last_name)
D. CREATE INDEX NAME_IDX ON employees (first_name AND last_name)
E. CREATE INDEX NAME_IDX ON employees (first_name, last_name)
F. CREATE INDEX NAME_IDX FOR employees (first_name, last_name)
Answer: E
105. Click the Exhibit button and examine the data from the ORDERS and CUSTOMERS tables.
ORDERS
ORD_ID ORD_DATE CUST_ID ORD_TOTAL
100 12.JAN-2000 15 10000
101 09-MAR-2000 40 8000
102 09-MAR-2000 35 12500
103 15-MAR-2000 15 12000
104 25-JUN-2000 15 6000
105 18-JUL-2000 20 5000
106 18-JUL-2000 35 7000
107 21-JUL-2000 20 6500
108 04-AUG-2000 10 8000
CUSTOMERS
CUST_ID CUST_NAME CITY
10 Smith Los Angeles
15 Bob San Francisco
20 Martin Chicago
25 Mary New York
30 Rina Chicago
35 Smith New York
40 Linda New York
Evaluate the SQL statement:
SELECT * FROM orders WHERE cust_id = (SELECT cust_id FROM customers WHERE cust_name = 'Smith')
What is the result when the query is executed?
A.
ORD_ID ORD_DATE CUST_ID ORD_TOTAL
102 09-MAR-2000 35 12500
106 18-JUL-2000 35 7000
108 04-AUG-2000 10 8000
B.
ORD_ID ORD_DATE CUST_ID ORD_TOTAL
102 09-MAR-2000 35 12500
106 18-JUL-2000 35 7000
C.
ORD_ID ORD_DATE CUST_ID ORD_TOTAL
108 04-AUG-2000 10 8000
D. The query fails because the subquery returns more than one row.
E. The query fails because the outer query and the inner query are using different tables.
Answer: D
106 Evaluate this SQL statement:
SELECT e.EMPLOYEE_ID,e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM EMP e.DEPARTMENT d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;
In the statement, which capabilities of a SELECT statement are performed?
A. selection, projection, join
B. difference, projection, join
C. intersection, projection, join
D. difference, projection, product
Answer: A
107. You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty. Which statement accomplishes this task?
A. ALTER TABLE students ADD PRIMARY KEY student_id;
B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);
C.ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY stuent_id;
D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
E. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id)
Answer: D
108. Which syntax turns an existing constraint on?
A. ALTER TABLE table_name ENABLE constrain_name
B. ALTER TABLE table_name STATUS = ENABLE CONSTRAINT constrain_name
C. ALTER TABLE table_name ENABLE CONSTRAINT constraint_name
D. ALTER TABLE table_name STATUS ENABLE CONSTRAINT constraint_name
E. ALTER TABLE table_name TURN ON CONSTRAINT costrant_name
F. ALTER TABLE table_name TURN ON CONSTRAINT constraint_name
Answer: C
109. Which two statements about views are true? (Choose two)
A. A view can be created as read only
B. A view can be created as a join on two or more tables.
C. A view cannot have an ORDER BY clause in the SELECT statement.
D. A view cannot be created with a GROUP BY clause in the SELECT statement.
E. A view must have aliases defined for the column names in the SELECT statement.
Answer: AB
110. The database adminsrator of your company created a public synonym called HR for the HUMAN_RESOURCES table of the GENERAL schema, because many users frequentlyuse this table. As a user of the database, you created a table called HR in your chema. What happens when you execute this query?
SELECT * FROM HR;
A. you obtain the results retrieved from the public synonym HR created by the database administrator
B. you obtain the results retrieved form the HR table that belongs to your schema.
C. you get an error message because you cannot retrieve from a table that has te same ame as a public synonym
D. you obtain the results retrieved from both the public synonym HR and the HR table that belongs to your shema, as a Cartesian product.
E. You obtain the results retrieved form both the public synonym HR and the HR table that belongs to your shema, as a FULL JOIN.
Answer: B
111. You need to give the MANAGER role the ability to select from insert into and modify existing rows in the STUDENT_GRADES table. Anyone given this MANAGER role should be able to pass those privileges on to others. Which statement accomplishes this.
A. GRANT select, insert, update ON student_grades TO manager;
B. GRANT select, insert, update ON student_grades TO ROLE manager
C. GRANT select, insert, modify ON student_grades TO ROLE manager
C. GRANT select, insert, modity ON student_grades TO manager WITH GRANT OPTION;
D. GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION
E. GRANT select, insert, update ON student_grades TO ROLE manager WITH GRANT OPTION;
F. GRANT select, insert, modify ON student_grades TO ROLE manager WITH GRANT OPTION
Answer: D
112. Click the Exhibit button and examine the data in the EMPLOYEES and DEPARTMENTS tables.
EMPLOYEES
LAST_NAME DEPARTMENT_ID SALARY
Get z 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000
Kochhar 5000
DEPARTMENTS
DEPARTMENT_ID DEPARTMENT_NAME
10 Sales
20 Marketing
30 Accounts
40 Administration
You want to retrieve all employees whether or not they have matchig departments inthe departments table. Which query would you use?
A. SELECT last_name, department_name FROM employees, departments(+);
B. SELECT last_name, department_name FROM employees JOIN departments(+);
C. SELECT last_name, department_name FROM employees(+) e JOIN departments d
ON (e.department_id = d.departement_id);
D. SELECT last_name, department_name FROM emplouees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
E. SELECT last_name, department_name FROM employees (+), departments
ON (e.departments_id = department_id);
F. SELECT last_name, departement_name FROM employees e LEFT OUTER
JOIN departments d ON (e.department_id = d. department_id);
Answer: F
111. What is necessary for your query on an existing view to execute successfully?
A. The underlying tables must have data.
B. You need SELECT privileges on the view.
C. The underlying tables must be in the same schema.
D. You need SELECT privileges only on the underlying tables.
Ans: B
Recently I passed the exam. Go through the dump and prepare very well. I am attaching my own notes. Please correct the spelling mistakes.
Best of Lucks
________________________________________
0800 279 6620 amazon
1. The ESCAPE Option
When you need to have an exact match for the actual % and _ characters, use the ESCAPE option. This option specifies what the escape character is. If you want to search for strings that contain SA_, you can search for it using the following SQL statement:
SELECT employee_id, Last_Name, Job_id
FROM employees
WHERE Job_id LIKE '%SA\_%' ESCAPE '\';
The ESCAPE option identifies the backslash (\) as the escape character. In the pattern, the escape character precedes the unserscore( _ ). This causes the Oracle Server to interpret the underscore literally.
2. Rules of Precedence in Oracle 9i
1. Arithmetic Operators
2. Concatenation Operator
3. Comparison conditions
4. IS [NOT] NULL, LIKE, [NOT] IN
5. [NOT] BETWEEN
6. NOT logical condition
7. AND logical condition
8. OR logical condition
3. Use Parentheses to force priority
4. ORDER BY caluse ASCENDING is the default option
5. ASCENDING means least values comes first i.e 1995...1996...1997... etc
6. Manipulations
CONCAT('Hello','World') displays HelloWorld
SUBSTR('HelloWorld',1,5) displays Hello
LENGTH('HelloWorld') displays 10
INSTR('HelloWorld','W') displays 6
LPAD(salary,10,'*') displays *****24000
RPAD(salary, 10, '*') displays 24000*****
TRIM('H' FROM 'HelloWorld') displays elloWorld
7. Number Funcations
ROUND - Rounds value to specified decimal - ROUND(45.926,2) gives 45.93
TRUNC - Truncates value to specified decimal - TRUNC(45.926,2) gives 45.92
MOD - Returns remained of division - Mod(1600,300) gives 100
8. The DUAL table
The DUAL table is owned by the user SYS and can be accessed by all users. It contains one column, DUMMY
and one row with the value x. The DUAL table is useful when you wnat to return a value once only; for instance the value of a constant, pseudocolumn, or expression that is not derived from a table with user data. The DUAL table is generally used for SELECT clause syntax completness, becuase both SELECT and FROM clauses are mandatory, and several calculations do not need to select from actual tables.
9. Working with Dates
Oracle database stores dates in an internal numeric format: Century, year, month, day, hours, minutes,seconds
The default date display format is DD-MON-RR. Allows you to stoew 21st century dates in the 20th century by specifying only the last two digits of the year.
SELECT sysdate from dual
date + Number = Adds a number of days to a date
date - number = Substracts a number of days from a date
date - date = Substracts one date from another
Date + number /24 = Add a nsumber of hours to a date
Funcations :
MONTHS_BETWEEN - Number of months between two dates
ADD_MONTHS - Add calendar months to date
NEXT_DAY - Next day of the date specified
Assume SYSDATE = '25-JUL-95' :
ROUND (SYSDATE, 'MONTH') gives 01-Aug-95
ROUND(SYSDATE, 'YEAR') gives 01-Jan-96
TRUNC(SYSDATE,'MONTH') gives 01-Jul-95
TRUNC(SYSDATE,'YEAR') gives 01-Jan-95
10. Implicit Data-Type Conversion
VARCHAR2 Or Char --> Number
VARCHAR2 OR CHAR --> Date
NUMBER --> Varchar2
DATE --> Varchar2
11. Conversion Funcation
TO_CHAR(Number| date, [fmat],[nlsparams]) - Convert a number or date value to VARCHAR2 character string with format model fmt.
TO_NUMBER(char,[fmt],[nlsparams]) - Converts a character string containing digits to a number int he format specified by the optional format model fmt.
TO_DATE(char,[fmt],[nlsparams]) - Convert a character string representing a date to a date value according to the fmt specifed. IF fmt is omiotted, the format is DD-MON-YY.
YYYY = Full year in numbers
YEAR = Year spelled out
MM = Two-digit vale for month
MONTH = Full name of the month
MON = Three letter abbreviation of the month
DY - Three letter abbrevation of the day of the Week
DAY = Full name of the day of the weel
DD = Numeric day o the month
************Important*********** Understand more on To_CHAR functionality
12.
NVL - Converts a null value to an actual value
NVL2 - If expr1 is not null, NVL2 return expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have data type.
NULLIF - Compares two expression and returns null if they are equal, or the first expression if they ar not equal
COALESCE - Returns the first non-null expression in the expression list.
13. DECODE funcation is same as CASE statement in SQL Server
14. The ANY operator compares a value to each value returned by a subquery. ANY in equivalent to IN
15. The ALL operator compares a value to every value returned by a subquery.
16. You can predefine user variables before executing a SELECT statement using DEFINE.
17. Use the UNDEFINE command to clear it or EXIT iSQL*Plus session
18. Example of define
DEFINE employee_num = 200
SELECT employee_id, Last_name,Salary
FROM employees
WHERE employee_id = &employee_Num
19. You can use the double-ampersand (&&) substitution variable if you want to reuse the variable value wihout prompting the user each time. The user will see the prompt for the value only once.
20. Use the VERIFY command to toggle the display of the substitution variable, before and after iSQL*Plus replaces substitution variables with values. Usage SET VERIFY ON
21. SET system_variable value. Usage SET ECHO ON
22. SET Variable and Values
ARRAY[SIZE] {20 | n} - Sets the database data fetch size
FEED[BACK] {6| n| OFF | ON} - Displays the number of records returned by a query when the query selects at least n records
HEA[DING] {OFF | ON} - Determines whether column hadings are displayed in reports
LONG {80 |n} - Sets the maximum width for displaying LONG values
23. iSQL*Plus Format commands
COLUMN [Column option] - Controls column formats
TTITLE [text | OFF | ON] - Specifies a header to appear at the top of each page of the report
BTITLE [text | OFF | ON] - Specifies a footer to appear at the bottom of each page of the report
BREAK [ON report_element] - Suppresses duplicate values and divides roews of data into sections by using line breaks
24. Column Options
CLE[AR] - Clears any column formats
HEA[DING] text - Sets the column heading ( a vertical line (|) forces a line feed int he heading if you do not use justification)
FOR[MAT] format - Chnages the display of the column data
NOPRI[NT] - Hides the column
NUL[L] text - Specifies text to be displayed for null values
PRI[NT] - Shows the column
25. The BREAK command
Use the BREAK command to divide rows into section and suppress duplicate values. To ensure that the BREAK command works effectively, use the ORDER BY clause to order the columns that you are breaking on.
BREAK on column[ |alias | row]
CLEAR BREAK
26. The WITH CHECK OPTION keyword prohibits you from changing rows that are not in the subquery.
27. Specify DEFAULT to set the column to the value previously specified as the default for the column.
28. The MERGE statement
Provides ability to conditionally update or insert data into a database table
Perofrms an UPDATE if the row exists and an INSERT if it is a new row
Because the MERGE command combines the INSERT and UPDATE commands, you need both INSERT and UPDATE privileges on the target table and the SELECT privilege on the source table.
The MERGE statement is deterministic. You cannot update the same row of the target table multiple times in the same MERGE statement.
29. The MERGE SYNTAX
MERGE INTO table_name AS table_Alias
USING (Table|View|Sub_query) AS alias
ON (Join Condition)
WHEN MATCHED THEN
UPDATE SET
Col1 = Col_Val1
Col2 = Col2_Val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES(Column_Values);
INTO Clause - Specifies the target table you are updating or inserting into
USING Clause - identifies the source of the data to be updated or inserted can be a table view or subquery
ON clause - the condition upon which MERGE operation either updates or inserts
WHEN MATCHED | WHEN NOt MATCHED - Instructs the server how to respond to the result of the join condition.
See example in the ILT-007-Part1.PDF Page 350+
30. IF you create a second savepoint with the same name as an earlier savepoint, the earlier savepoint is deleted.
31. If a single DML statement fails during execution, only that statement is rolled back.
32. The oracle server issues an implicit commit before and after any data definition language(DDL) statement. So even if your DDL statement does not execute successfully, you cannot roll back the previous statement becuase the server issued a commit.
33. Implicit Locking
Exclusive : Locks out other users
Share: Allows other users to access the server
34. Data dictionary Is a collection of tables created and maintained by the Oracle Server, Contains database information. All data dictionary tables are owned bythe SYS user.
35. Four categories of data dictionary views
USER_ : These views contains information about objects owned by the user.
ALL_ : These views contain information anout all of the tables(Object tables and relational tables) accessible to the user.
DBA_ : These views are restricted views, which can be accessed only by people who have been assigned the DBA role.
V$ : These views are dynamic performance views, database server performance, memory and locking.
36. User_tables : See the names of the tables owned by the user.
USer_Objects : View distinct object types owned by the user
User_Catalog or Cat : View tables, views, synonyms and sequences owned by the user.
37. Data Types
VARCHAR2(Size) : Variable-length character data( a maximum size must be specified. Minimum size is 1, Maximum size is 4000)
CHAR [ (Size) ] : Fixed-length character data of length size bytes(default and minimum size is 1, maximum size is 2000)
NUMBER [ (p,s) ] : Number having precision p and scale s( The precision is the total number of decimal digits, and the scale is the number of digits to the right of the decimal point, the precision can range from 1 to 38 and the scale can range from -84 to 127)
DATE : Date and time values to the nearest second between January 1, 4712 B.C. and A.D. December 31 9999
LONG : Variable length character data up to 2 gigabytes
CLOB : Character data up to 4 gigabytes.
RAW(size): Raw binary data of length size(a maximum size must be specified maximum size is 2000)
LONG RAW : Raw binary data of variable lenght up to 2 gigabytes.
BLOB : Binary data up to 3 gigabytes.
BFILE : Binary data stored in an external file; up to 4 gigabytes.
ROWID : Hexadecimal string representing the unique addres of a row in its table. The datatype is primarily for values returned by the ROWID pseudocolumn.
38. LONG columns
is not copied when a table is created using a subquery
Cannot be included in a GROUP BY or an ORDER BY clause
Only one LONG column can be used per table
No constraint can be defined on a LONG column
You may wnat to use a CLOB column rather than a LONG column.
39. DATE
TIMESTAMP : Allows the time to be stored as a date with fractional seconds. There are several variations of the data type.
INTERVAL YEAR TO MONTH : Allow time to stored as an interval of years and months.
INTERVAL DAY TO SECOND : Allows time to be stored as an interval of days to hours minutes and seconds.
40. The integrity rules are not passed onto the new table, only the column data type definitions.
41. The SET UNUSED option marks one or more columns as unused so that they can be dropped when the demand on system resources is lower. This is a feature available in Oralce8i and later release. Specifying this clause does not actually remove the target columns from each row in the table(That is, it does not restore the disk space used by these columns). Therefore the response time is fater than if you executed the DROP clause. Unused columns are treated as if they were dropped, even though their column data remains in the tables rows. After a column has been marked as unused, you have to access to that column. A SELECT * query will not retrieve data from unused columns. In addition, the names and typoes of columns marked unused will nbot be displayed during a DESCRIBE, and you can add to the table a new column with the same bame as an unused column. SET UNUSED information is tored in the USER_UNUSED_COL_TABS dictionary view. (Page 402).
42. Comments can be viewed through the data dictionary views:
ALL_COL_COMMENTS
USER_COL_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
43. cONSTRAINTS
enforces rules at the table level
prevent the deleteion of a table if there are dependencies
44. Valid constraintes
NOT NULL : Specifies that the column cannot contain a null value
UNIQUE : Specifies a column or combination of folumns whose values must be unique for all rows in the table.
Primary Key : Uniquely identifies each row of the table
Foreign Key : Estabilishes and enforces a foreign key relationship between the column and a column of the refrenced table
CHECK : Specifies a condtion that must be true.
45. Constraints are stored int he data dictionary. If you do not name a constraint the Oralce server generates a name with the format SYS_Cn, where n is an integer so that the constraint nema is unique. You can view the constraints defined for a specific table by looking at the USER_CONSTRAINTS data dictionary table.
46. FOREIGN KEY constraint
FOREIGN KEY is used to define the column in the child table at the table constrinat level.
REFERENCES identifies the table and column in the parent table.
ON DELETE CASCADE indicates that when the row in the parent table is deleted, the dependent rows in the child table will also be deleted
ON DELETE SET NULL convers foreign key values to null when the parent value is removed.
Withou the ON DELETE CASECADE or the ON DELERE SET NULL options, the row in the parent table cannot be deleted if it is referenced in the child table.
47. The CHECK constraint defines a condition that each row must satisfy. The condtion can use the same contructs as query condtions, with the following exceptions.
Refernces to the CURRVAL,NEXTVAL,LEVEL and ROWNUM pseudocolumns
Calls to SYSDATE,UID,USER and USERENV functions
48. Query the USER_Constraints table to view all constraint definitions and names. View the columns associated with the constraint names in the USER_CONS_Columns view.
49. Creating VIEWS
FORCE : Creates the view regardless of whether or not the base tables exist
NOFORCE: Creates the view inly if the base tables exist(This is the default)
WITH CHECK OPTION: specifies that only rows accessible to the view can be inserted or updated
WITH READ ONLY : ensures that no DML operations can be performed on this view
50. You can remove a row from a view unless it contains any of the following
- Group functions
- A GROUP BY clause
- The DISTINCT keyword
- The pseudocolumn ROWNUM keyboard
51. You can add data through a view unless it contains any of the items listed in the slide and there are NOT NULL columns, without default values, in the base table that are not selected by the view.
The WITH CHECK OPTION clause specified that INSERTS and UPDATES performed throguh the view cannot creat rows which cannot select and therefore it allows integrity constraints and data validation checks to be enforced on data being inserted or updated.
52. INLINE VIEWS is a subquery with an alias(or correlation name) that you can use within SQL statement. A named sunquery in the FROM clause of the main query is an example of an inline view.. An inline view is not a schema object.
53. Performing Top-n analysis
Top-n queries use a consistent nested query structure with the elements describled below:
A subquery or an iline view to genereate the sorted list of data. The subquery or the iline view includes the ORDER BY clause to ensure that the ranking is in the desired order. For results retrieving the largest values, a DESC parameter is needed.
An outer query to limit the number of rows in the final result set. The outer query includes the following components:
The ROWNUM pseudocolumn, whcih assigns a sequential value starting with 1 to each of the following rows returned from the subquery
A WHERE clause, whcih specifies the n rows to be returned. The outer WHERE clause must use a < or <= operator.
54. A sequence is a number created database object that can be shared by multiple users to generate unique integers. A typical usage for sequences is to create a primary key value, whcih must be unique for each row. The sequence is generated and incremented by an internal Oralce routine. This can be a time saving object because it can reduce the amount of application code needed to write a sequence-generating routine.
Sequence numbers are stored and generated independently of tables. Therfore the same sequence can be used for multiple tables.
55. CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
NOMAXVALUE - specifies a maximum value of 10^27 for an ascending sequence and -1 for a descending sequence
CYCLE | NOCYCLE : specifies where the sequence continues to generate values after reaching its maximum or minimum value
CACHE n | NOCACHE: Specifies how many values the Oracle Server preallocates and keep in memory(By default, the Oracle Server caches 20 values)
Data dictionary table : USER_SEQUENCES
56. You can use NEXTVAL and CURRVAL in the follwoing contexts:
The SELECT list of a SELECT statement that is no part of a sunquery
The SELECT list of a subquery in an iNSERT statement
The VALUES clause of an INSERT statement
The SET clause of an UPDATE statement.
You cannot use NEXTVAL and CURRVAL in the following contexts :
A SELECT list of a view
A SELECT statement with the DISTINCT keyword
A SELECT statement with GROUP BY, HAVING or ORDER BY clause
A subquery in a SELECT,DELETE or UPDATE statement
The DEFAULT expression in a CREATE TABLE OR ALTER TABLE statement
57. Gaps in sequence values can occur when:
-A rollback occurs
- The system crashes
- A sequence is used in another table.
58. Two types of indexes can be created. One type is a unique index. The Oracle Server automatically creates this index when you define a column in a table to have a PRIMARY EKY or a UNIQUE key constraint. The name of the index is the name given to the constraint.
The other type of index is a nonunique inex, whcih a suer can create. For example, you can create a FOREIGN KEY column index for a join in a query to improve retrieval speed.
59. You should create an index if:
A Column contains a wide range of values
A column contains a large number of null values
One or more columns are frequently used toghether in a WHERE clause or a join condition
The table is large and most queries are expected to retieve less than 2 to 4% or the rows.
60. It is usually not worth creating an index if:
The table is small
The columns are not often used as a condtion in the query
Most queries are expected to retrieve more than 2 to 4% of the rows in the table
The table is updated frequently
The indexed columns are referenced as part of an expression.
The USER_INDEXES data dictionary contains the name of the index and its uniqueness
The USER_IND_COLUMNS view contains the index name, the table name, and the column name.
61. Function-based indexes defined with the UPPER(Column_Name) OR LOWER(Column_Name)
keywords allow case insensitive searches, For example the following index
CREATE INDEX Upper_last_name_idx ON employeees(UPPER(last_name));
Faciliates processing queries such as:
SELECT * FROM employee WHERE UPER(Last_Name) = 'KING';
62. Typical DBA privileges
CREATE USER: Grantee cab create other Oracle users( a privilege required for a DBA role)
DROP USER: Grantee can drop another user
DROP ANY TABLE: Grantee can drop table in any schema
BACKUP ANY TABLE: Grantee can back up any table in any schema with the export utility
SELECT ANY TABLE: Grantee can query tables, views or snapshot in any schema.
CREATE ANY TABLE: Grantee can create tables in any schema.
63. Creating USER
CREATE USER user IDENTIFIED BY password;
Once a user is created, the DBA can grant specific system privilages to a user
GRANT privilege [,privilege...] TO user [,user | role, PUBLIC...]
An application developer, for example, may have the following system privileges
CREATE SESSION - Connect to the database
CREATE TABLE - Create tables in the users schema
CREATE SEQUENCE - Create a sequence in the users schema
CREATE VIEW - Create a view in the users schema
CREATE PROCEDURE - Create a stored procedure, function or package in the users schema
Privilege - is the system privilege to be granted
user|role|PUBLIC - is the name of the user, the name fo the role, or PUBLIC designates that every user is granted the privilege
Current system privileges can be found in the dictionary view SESSION_PRIVS.
GRANT create session, create table,create sequence,create viee TO SCOTT;
ROLE
A role is a named group of related privileges that can be granted to the user. This method makes it easier it revoke and maintain privileges
CREATE ROLE role;
CREATE ROLE Manager;
GRANT create table, create view to manager
GRANT manager to Bhasker sarada ;
ALTER USER Scott IDENTIFIED by lion;
WITH GRANT OPTION : A privilege that is granted with the WITH GRANT OPTIoN caluse can be passed on to other users and roles by the grantee. Object privileges granted with the WITH GRANT OPTION clause are revoked when the grantors privilege is revoked.
The PUBLIC Keyword : An ownder of atable can grant access to all users by using the PUBLIC keyword.
ROLE_SYS_PRIVS - System privileges granted to roles
ROLE_TAB_PRIVS - Table privileges granted to roles
USER_ROLE_PRIVS - Roles accessible by the user
USER_TAB_PRIVS_MADE - object privileges granted on the users objects
USER_TAB_PRIVS_RECD - Object prvvileges granted to the user
USER_COL_PRIVS_MADE - Object privileges granted on the columns of the users objects
USER_COL_PRIVS_RECD - Object privileges granted to the user on specific columns
USER_SYS_PRIVS - Lists system privileges granted to the user.
REVOKE {privilege [,privilege...] | A::}
on object
FROM {user[, user....] |role|PUBLIC}
[CASCADE CONSTRAINT];
CASCADE : is required to remove any refrential integrity constraints made to the
CONSTRAINT : object by means of REFERENCE privilege
Privileges granted to others through the WITH GRANT OPTION clause are also revoked
64. A database link connection allows local user to access data on a remote database.
USER_DB_LINKS contains information on links to which a user has access
CREATE PUBLIC DATABASE LINK hq.acme.com USING 'sales;'
SELECT * FROM fred.emp@HQ.ACME.com;
65. SET Operators
UNION - All distinct rows selected by either query
UNION ALL - All rows selected by either query, including all duplicates
INTERSECT - All distinct rows selected by both queries
MINUS - All distinct rows that are selected by first SELECT statement abd that are not selected in the second SELECT statement.
66. Three new data types are added to DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE (TSTZ)
TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ)
Oracle 9i provides daylight savings support for datetime data types in the server.
CURRENT_DATE : function returns the current date in the sessions time zone. The return value is a date in the Gregorian calender.
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
ALTER SESSION SET TIME_ZONE = '-5:0';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM dual;
CURRENT_TIMESTAMP function returns the current date and time in the session time zone, as a value of the data type TIMESTAMP WITH TIME ZONE.
LOCALTIMESTAMP : function returns the current date and time in the session time zone in a value of data type TIMESTAMP.
DBTIMEZONE function returns the value of the database time zone.
SESSIONTIMEZONE function returns the value of the current session time zone.
EXTRACT expression extracts and returns the value of a specified datetime field from a datetime or interval value expression.
SELECT EXTRACT ([YEAR] [MONTH] [DAY] [HOUR] [MINUTE] [SECOND] TIMEZONE_HOUR] [TIMEZONE_MINUTE]
[TIMEZONE_REGION] [TIMEZONE_ABBR]
FROM [datetime_value_expression] [interval_value_expression]) ;
FROM_TZ function converts a time stamp value to a TIMESTAMP WITH TIME ZONE value.
TO_TimeSTAMP and TO_TimeSTAMP_TZ
The TO_TIMESTAMP function converts a string of CHAR,VARCHAR2, NCHAR or NVARCHAR2 data type to a value of TIMESTAMP data type. The syntax of the TO_TIMESTAMP function is
TO_TIMESTAMP (CHAR,[fmt],['nlsparam'])
TO_YMINTERVAL function convers a character string of CHAR,VARCHAR2,NCHAR or NVARCHAR2 data type to an INTERVAL YEAR TOMONTH data type.
TZ_OFFSET function returns the time zone offset corresponding to the value entered.
67.Guidelines for using GROUP functions
The data types for the arguments can be CHAR, VARCHAR2,NUMBER or DATE
All group functions except COUNT(*) ignores null values. To substitute a value for null values, use the NVL funcation. COUNT returns either a number of zero.
The Oracle Server implicitly sorts the result set in ascending order of the grouping columns specified, when you use a GROUP BY clause. To override this default ordering, you can use DESC in an ORDER BY clause.
68. Use the HAVING clause to specify which groups are to be displayed. You further restrict the groups on the basis of a limiting condition. The HAVING clause can precede the GROUP BY clause, but it is recommended that you place the GROUP BY clause first because it is more logical.
69. ROLLUP
The ROLLUP operator delivers aggreates and superaggregates for expression within a GROUP BY statement. The ROLLUP operator can be used by report writer to extract statistics and summary information from results sets. The cumulative aggregates can be used in reports, charts and graphs. The ROLLUP operator creates groupings by moving in one direction from right to left, along the list of columns specified in the GROUP BY clause. It then applies the aggregate function to these groupings.
To product subtotals in n dimensions(that is, n columns in the GROUP BY clause) without a ROLLUP operator, n+1 SELECT statements must be linked with UNION ALL. This makes the query execution inefficient becuase each of the SELECT statements cause table access. The ROLLUP operator gahters its results with just one table access. The ROLLUP operator is usdeful if there are many columns involved in producing the subtotals.
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY ROLLUP(department_id, job_id);
70. CUBE operator
The CUBE operator is an additional switch in the GROUP BY clause in a SELECT statement. The CUBE operator can be applied to all aggregate functions including AVG, SUM, MAX, MIN and COUNT. It is ued to produce results set that are typically used for cross-tabular reports. While ROLLUP produces only a fraction of possible subtotal combinations, CUBE produces subtotals for all possible combinations of groupings specified in the GROUP BY clause and a grand total.
The CUBE operator is used with an aggregate function to generate additional rows in a result set. Columns included in the GROUP BY clause are cross-referenced to produce a superset of groups. The aggregate function specified in the sleect list is applied to the groups to produce summary values for the additonal superaggregate rows. The number of extra groups in the results set is determined by the number of columns included in the GROUP BY clause.
SELECT department_id, job)id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY CUBE(department_id,Job_id)
71. GROUPING FUNCTION
The GROUPING function can be used with either the CUBE or ROLLUP operator to help you understand how a summary value has been obtained.
The GROUPING function uses a single column as its argument. The expr in the GROUPING function must match one of the expressions in the GROUP BY clause. The function returns a value of 0 or 1.
The values returned by the GROUPING function are useful to
Determine the level of aggregation of a given subtotal; that is the group or groups on whcih the subtotalis based.
Identify whether a NULL value in the expression column of a row of the result set indicates:
- A NULL value from the base table(stored NULL value)
- A NULL value created by ROLLUP/CUBE (as a result of a group function on that exression)
A value of 0 returned by the GROUPING function based on an expression indicates one of the following:
- The expression has been used to calculate the aggregate value.
- The NULL value in the expression column is a stored NULL value.
A value of 1 returned by the GROUPING function based on an expression indicates one of the following:
- The expression has not been used to calculate the aggregate value
- The NULL value in the expression column is created by ROLLUP or CUBE as a result of grouping.
SELECT department_id DEPTID, Job_id JOB, SUM(Salary),
GROUPING(department_id) grp_dept, GROUPING(Job_id) GRP_JOB
FROM employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, Job_id);
72. GROUPING SETS
GROUPING SETS are a further extension of the GROUP BY clause that let you specify multiple groupings of data. Doing so facilitates efficient aggregation and hence facilitates analysis of data across multiple dimensions.
A single SELECT statement can now be written using GROUPING SETS to specify various groupings(that can also include ROLLUP or CUBE operators), rather than multiple SELECT statements combined by UNION ALL operator.
73. Composite columns
A composite column is a collection of columns that are treated as a unit during the computation of groupings. You specify the columns in parentheses as in the following statement:
ROLLUP(a,(b,c),d)
Here (b,c) form a compositecolumn and are treated as a unit. In general composite columns are useful in ROLLUP,CUBE and GROUPING SETS. For example in CUBE or ROLLUP, composite columns would mean skipping aggregation across certain levels.
That is, GROUP BY ROLLUP(a,(b,c))
is equivalent to
GROUP BY a,b,c UNION ALL
GROUP BY a UNION ALL
GROUP BY()
HERE(B,c) are treated as a unit and rollup will not be applied across (b,c). It is as ir you have an alias, for example z, for (b,c) and the GROUP BY expression reduces to
GROUP BY ROLLUP(a,z)
SELECT department_id, job_id, manager_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id,(job_id,Manager_id));
74. Concatenated Columns
Concatenated groupings offer a concise way to generate useful combinations of groupings. The concatenated groupings are specified simply by listing multiple grouping sets, cubes and rollups and separating them with commas. Here is an example of concatenated grouping sets
GROUP BY GROUPING SETS(a.b), GROUPING SETS(C,d)
The preceding SQL defines the follwoing groupings:
(a,c), (a,d), (b,c), (b,d)
Concatenation of groupings sets is very helpful for these reasons:
Ease of query development you need not enumerate all groupings manually
Use by applications: SQL generated by OLAP application oftern involves concatenation of grouping sets
with eacn grouping set defining groupings needed for a dimension.
SELECT department_id,job_id, manager_id, SUM(salary)
FROM employees
GROUP BY department_id, ROLLUP(job_id), CUBE(Manager_id);
75. A scalar subquery expression is a subquery that returns exactly one column value from one row. In Oracle9i, scalar subqueries can be used in
- Condition and expression part od DECODE and CASE
- All clauses of SELECT except GROUP BY
76. The Oracle Server performs a correlated subquery when the subquery references a column from a table rererred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT,UPDATE or DELETE statement.
77. NESTED Subquery Execution
-The inner query executes first and finds a value
-The outer query executes once, using the value from the inner query.
Correlated subquery Execution
-Get a candidate row(fetched by the outer query)
-Execute the inner query using the value of the candidate row
-Use the values resulting from the inner query to qualify or disqualify the candidate
-Repeat until no candidate row remains
78. The WITH Clause
Using the WITH clause, you can define a query block before using it in a query. The WITH clause(formally known as subqyery_factoring_clause) enables you to reuse the same query block in a SELECT statement when it occurs more than once with in a complex query. This is particularly useful when a query has many references to the same query block and there are joins and aggregations.
Using the WITH clause, you can reuse the same query when it is high cost to evaluate the query block and it occurs more than once within a complex query. Using the WITH clause, the Oracle Server retrieves the results of a query block and stores it in the users temporary tablespace. This can improve performance
-- Makes the query easy tp read
-- Evaluates a clause only once, even if it appears multiple times in the query, therby enhancing performance.
WITH
dept_costs AS (
SELECT department_name, SUM(salary) AS dept_total
FROM employees, departments
WHERE employyes.department_id =
departments.department_id
GROUP BY department_name)
AVG_Cost AS
(SELECT SUM(dept_total) / COUNT(*) AS dept_avg
FROM dept_COSTS )
SELECT * FROM dept_COSTS
WHERE DEPT_total >
(SELECT FROM dept_avg)
ORDER BY department_name;
79. Hierarchical Queries
SELECT [LEVEL], column, expr....
FROM table
[WHERE condition(s) ]
[START WITH condition(s) ]
[CONNECT BY PRIOR condition(s)];
SELECT : is the standard SELECT clause
LEVEL : For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root rpw, 2 for a child of a root and so on.
FROM table :Specifies the table, view or snapshot containing the columns you can select from only one table.
WHERE : Restricts the rows returned by the query without affecting other rows of the hierarchy.
Condtion : is a comparision with expressions
START WITH : Specifed the root rows of the hierarchy(sher to start). This clause is rquired for a true hierarchical query.
CONNECT BY Prior : Specifieds the columns in which the relationship between parent and child rows exist. This clause is required for a hierarchical query.
The SELECT statement cannot contain a join or query from a view that contains a join.
The row or rows to be used as the root of the tree are determined by the START WITH clause. The START WITH clause can be used in conjunction with any valid condition.
Examples
Using the EMPLOYEES table,start with King, the president of the company
... START WITH manager_id IS NULL
Using the EMPLOYEES table, start with employee Kochhar. A START WITH condition can contain a subquery
... START WITH employee_id = (SELECT employee_id FROM employees WHERE last_name = 'Kochhar')
If the START WITH caluse is omitted, the tree walk is started with all of the rows in the table as root rows. If a WHERE clause is used, the walk is started with all the rows that satisfy the WHERE condition. This no longer reflects a true hierarchy.
The director of the query, whether it is from parent to child or from child to parent, is determined by the CONNECT BY PRIOR column placement. The PRIOR operator refers to the parent row. To find the children of a parent row, the Oracle Server evaluates the PRIOR expression for the parent row and the other expressions for each row in the table. Rows for whci the condition is ture are the children of the parent. The Oracle Server always selects children by evaluating the CONNECT BY condition with respect to a current parent row.
Examples
Walk from the top down using the EMPLOYEES table. Define a hierarchical relationship in whcih the EMPLOYEE_ID value of the parent row is equal to the MANAGER_ID value of the child row.
... CONNECT BY PRIOR employee_id = manager_id
Walk from the bottom up using the EMPLOYEES table
--- CONNECT BY PRIOR manager_id = employee_id
The PRIOR operator does not necessarily need to be coded immediately following th eCONNECT BY. Thus the following CONNECT BY PRIOR clause gives the same result as the one in the preceding example.
... CONNECT BY employee_id = PRIOR manager_id
Note: The CONNECT BY clause cannot contain subquery.
-- Understand more on hierarchy
80. MULTITABLE INSERT statement
In a multitable INSERT statement, you insert computed rows derived from the rows returned from the evaluation of a subquery into one or more table.
As with the existing INSERT .... SELECT statement, the new statement can be parallelized and used with the direct-load mechanism for faster performance.
Each record from any input stream, wuch as a nonrelational database table, can now be converted into multiple records for more relational table environment. To implement this functionality before Oracle9i you had to write multiple INSERT statements.
TYPES OF MULTITABLE INSERT Statements
Oracle 9i introduces the following types of multitable INSERT statements
Unconditional INSERT
Conditional ALL FIRST
Conditional FIRST INSERT
Pivoting INSERT
You use different clauses to indicate the type of INSERT to be executed.
SYNTAX
INSERT [ALL] [conditional_insert_clause]
[insert_into_clause Values_clause] (subquery)
Conditional_insert_clause
[ALL] [FIRST]
[WHEN condition THEN] [insert_into_clause values_clause]
[ELSE] [insert_into_clause values_clause]
Unconditonal INSERT: ALL into_clause
specify ALL followed by multiple insert_into_clause to perform an unconditional multitable insert. The Oracle Server executes each insert_into_clause once for each row returned by the subquery.
Conditional INSERT : conditonal_insert_clause
Specify the conditional_insert_clause to perform a conditional multitable insert. The Oracle server filters each insert_into_clause through the corresponding WHEN condition, which determines whether that insert_into_clause is executed. A single multitable insert statement can contain up to 127 WHEN clause.
Conditional INSERT: ALL
if you specify ALL, the Oracle server evaluates each WHEN clause regardless of the results of the evaluation of any other WHEN clause. For each WHEN clause whose condition evaluates to true, the Oracle server executes teh corresponding INTO clause list.
Conditional FIRST: INSERT
if you specify FIRST, the Oracle Server evaluates each WHEN clause in the order in which it appears iin the statement. If the first WHN clause evaluates to true, the Oracle Server executes the corresponding INTO clause and skips subsequent WHEN clause for the given row.
Condtional INSERT: ELSE Clause
For a given row, if no WHEN clause evaluates to true:
If you have specified an ELSE clause the Oracle Server executes th INTO clause list associated with the ELSE clause.
If you do not specify an ELSE clause, the Oralce server takes no action for that row.
Restrictions on Multitable INSERT statements
You can perform multitable inserts on tables, not on views or materialized views
You cannot perform a multitable insert into a remote table.
You cannot specify a table collection expression when performing a multitable insert
In a multitable insert, all of the insert_into_clauses cannot combine to specify more than 999 target columns
Unconditional INSERT ALL
INSERT ALL
INTO sal_history VALUES(empid, hiredate,sal)
INTO mgr_history VALUES(empid,MGR,SAL)
SELECT employee_id, EMPID, hire_date HIREDATE,salary SAL, manager_id MGR
FROM employees
WHERE employee_id > 200
( 8 rows created)
The example in the slide inserts rows into both SAM_History and MGR_HISTORY tables. The SELECT statement retrieves the details of employee ID, hire date, salary and manager ID of those employees whose employee ID is greater than 200 from the EMPLOYEE table. The details of the employee ID, hire date and salary are inserted into the SAL_HISTORY table. The details of employee ID, manager ID and salary are inserted into the MGR_History table.
This INSERT statement is referred toas an unconditional INSERT, as no further restriction is applied to the rows that are retrieved by the SELECT statement. All the rows retrieved by the SELECT statement are inserted into the tow tables SAL_HISTORY and MRG_HISTORY. The VALUES clause in the INSERT statements specifies the columns fromt he SELECT statment that have to be inserted into each of the tables. Each row returned by the SELECT statment results in two inserts, one for the SAL_HISTORY table and one for the MRG_HISTORY table.
The feedback 8 rows created can be interpreted to mean that a ttal of eight inserts were performed on the base tables, SAL_HISTORY and MRG_HISTORY.
Conditional INSERT ALL
INSERT ALL
WHEN sal > 10000 THEN
INTO sal_hisotry VALUES(EMPID,MGR,SAL)
WHEN MRG > 200 THEN
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID, hire_date HIREDATE,
Ssalary SAL, manager_id MGR
FROM employees
WHERE employee_id > 2000;
(4 rows created)
The example on the slide is similar to the example on the previous slide as its inserts rows into both the SAL_HISTORY and the MGR_HISTORY tables. The SELECT statement retrieves the details of employee ID,hire date, salary and Manager ID of these employees whose employee ID is greate then 200 fro the EMPLOYEEES table. The details of employee ID, hire date and salary are inserted into the SAL_HISTORY table. The details of employee ID, manager ID and salary are inserted into the MGR_HISTORY table.
This INSERT statement is referred to as a condtional ALL INSERT as a futhrer restrictions is applied to the rows that are retrieved by the SELECT statement. From the rows that are retrieved by the SELECT statement. From the rows that are retrieved by the SELECT statement, only those rows in whcih the value of the SAL column is more than 10000 are inserted int he SAL_HISOTRY table and similarly only those rows where the value of the MGR column is more than 200 are inserted in the MGR_HISOTRY table.
Observe that unlide the previos example, where eight rows were inserted into the tables, in this example only four rows are inserted.
The feedback 4 rows created can be interpreted to mean that a total of four inserts were performed on the base tables, SAL_HISTORY and MRG_HISTORY.
Condtional FIRST INSERT
INSERT FIRST
WHEN SAL > 25000 THEN
INTO special_sal VALUES(DEPTID,SAL)
WHEN HIREDATE LIKE ('%00%') THEN
INTO hiredate_history_oo VALUES(DEPTID,HIREDATE)
WHEN HIREDATE like ('%99%') THEN
INTO hiredate_hisotry_99 VALUES(DEPTID,HIREDATE)
ELSE
INTO hiredate_history VALYES(DEPTID,HIREDATE)
SELECT depart_id DEPTID, SUM(salary) SAL,
MAX(hireDATE) HIREDATE
FROM EMPLOYEES
GROUP BY department_id;
(8 rows created)
The example in the slide inserts rows into more than one table, using one single INSERT statement. The SELECT statement retrieves the details of deartment ID, total salary, and maximum hire date for every department in the EMPLOYEE table.
This INSERT statement is referred to as a conditional FIRST INSERT as an exception is made from the departments whose total salary is more than $25,000. The condition WHEN ALL > 25000 is evaluated first. If the total salary for a department is more then $25,000, then the record is inserted into the SPECIAL_SAL table irresective of the hire date. If this first WHEN clause evaluates to true, the Oracle server executes the corresponding INTO clause and skips subsequent WHEN clause for this row.
For the rows that do not satisfy the first WHEN conditon (WHEN SAL > 25000) the rest of the conditons are evaluated just as a conditonal INSERT statemnet and the records retrieved by the SELECT statement are inserted into the HIREDATE_HISTORY_00 or HIREDATE_HISTORY_99 or HIREDATE_HISTORY tables, based on the values in the HIREDATE column.
The feedback 8 rows created can be interpreted to mean that a total of eight INSERT statements where performed on the base tables, SPECIAL_SAL, HIREDATE_HISTORY_00, HIREDATE_HISTORY_99 and HIREDATE_HISTORY.
Pivoting INSERT
Pivoting is an operation in whcih you need to build a transformation such that each record from any input stream such as a nonrelational database table, must be converted into multiple records for a more relational database table environment.
In order to solve the problem mentioned in the slide, you need to build a tranformation wuch that each record from the original nonrelational database table, SALES_SOURCE_DATA is converted into five records for the data warehouse SALES_INFO table. This operation is commonly referred to as pivoting.
The problem statment for a pivoting INSERT statment is specified in the slide. The solution to the preceding problem is shown in the next page.
INSERT ALL
INTO sales_info VALUES (employee_id,week_id,sales_MON)
INTO sales_info VALUES (employee_id,week_id,sales_TUE)
INTO sales_info VALUES (employee_id,week_id,sales_WED)
INTO sales_info VALUES (employee_id,week_id,sales_THUR)
INTO sales_info VALUES (employee_id,week_id,sales_FRI)
SELECT EMPLOYEE_ID, week_id, sales_Mon,sales_TUE
sales_WED, sales_THUR,sales_FRI
FROM sales_Source_Date;
(5 rows Created)
EXTERNAL TABLE
An external table is a read-only table whose metdata is stred in the database but whose data is stored outside the database. Using the Oracle9 external table feature, you can use external data as virtual table. This dat can be queried and joined directly and in parallel without requiring the external data to be first loaded in the database. You can use SQL, PL/SQL and Hava to query the data in an external table.
The main difference between external tables and regular table sis that externally organized tables are read-only. NO DML operation (update.Insert.Delete) are possible and no indexes can be created on them.
This means of defining the metadata fro external tables is throgh CREATE tABLE .... ORGANIZATION EXTERNAL statement. This external table definition can be thought of as view that is used for running any SQL query against external data without requiring that the external data first be loaded into the database.
The Oracle Server provides tow major access drivers for external tables. One, the loader access driver or ORACLE_LOADER, is used for reading of data from external files using the ORACLE loader technology. This access driver allows the Oracle server to access data from any data source whose format can be interpreted by the SQL*Loader utility. The other Oracle proveded access driver, the import/export access driver or ORACLE_INTERNAL can be used for both the importing and exporting of data using a platform independent format.
Creating an external tables
You create external tables using the ORGANIZATION EXTERNAL clause of the CREATE TABLE statement. You are not in fact creating a table. Rather, you are creating metadata in the data dictionary that you can use to access external data. The ORGANIZATION clause lets you specify the order in which the data rows of the table are stored. By specifying EXTERNAL in the ORGANIZATION clause, you indicate that the table is a read-only table loacated outside the database.
TYPE access_driver_type indicates that access driver of the external table. The access driver is the Application Programming Interface(API) that interprests the external data for the database. If you do not specify TYPE, Oralce used the default access driver, ORACLE_Loader.
The REJECT LIMIT clause lets you specify how many conversion errors can occur during a query of the external data before an Oracle error is reutrned and the query is aborted. The defulat value is 0.
DEFAULT DIRECTORY let you specify one or more defulat directory objects corresponding to directories ion the file system where the external data sources may reside. Default directroies can also be used by the access driver to store auxiliary files such as error loges. Multiple defulats direcotries are permitted to facilitate load balanceing on multiple disk drivers.
The optional ACCESS PARAMETERS clause lets you assign values to the parameters of the specific access driver for the external table. Oracle does not interpret anything in this clause. It is up to the access driver to interpret this information in the context of the external data.
The LOCATION clause lets you specific one external locaotr for each external data source. Usually the location_specifier is a file, but it need not be. Oracle does not interpret this clause. It is up to the access drive to interpret this information in the context of the external data.
Example of creating an external table
Creat a Directory object that corresponds to the directyon the file system where the external data source resides
CREATE DIRECOTRY emp_id AS '/flat_files';
Use the CREATE DIRECTORY statement to create a directory object. A directory object specifies an alias for a directory on the server's file system where an external data source resides. You can use directory names when referring to an external data source, rahter than hard-code the operating system pathname, for greater file management flexibility.
You must have CREATE ANY DIRECTORY system privileges to create directories. When you create a directory, you are automatically granted the READ object privilege and can grant READ privileges to other users and roles. The DBA can also grant this privilege to the other users and roles.
Syntax
CREATE [OR REPLACE] DIRECOTRY AS 'path_name';
OR REPLACE : Specify OR REPLACE to re-create the direcotry databae object if it already exists. You can use this clause to change the definiton of an existing directory without dropping,re-creating and regranting database object privileges previuosly granted on the directory. Users who had previously been granted privileges on a redefined direcotry can still access the direcory without being regranted the privileges.
directory : Specify the name of the directory object to be created. The maximum lenght of the directory is 30 bytes. You cannot qualify a direcotry object with a scheme name.
'Path_name' : Specify the full pathname of the operating system directory on the server where the files are loacted. The single quotes are required withe the result that the path name is case sensitive.
CREATE TABLE oldemp (
empno Number, empname Char(20), birthdate DATE)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECOTRY emp_dir
ACCESS Parameters
(RECORDS DELIMITED BY NEWLINE
BADFILE 'bad emp'
LOGFILE 'log_emp'
FIELDS TERMINATED BY ','
(empno CHAR,
empName CHAR,
birthdate CHAR date_format date mask "dd-mon-YYY"))
LOCATION ('empl,txt'))
PARALLEL 5
REJECT LIMIT 2000;
Assume that there is a flat file that has records in the following format:
10,Jones,11-Dec-1934
20,smith,12-Jun-1972
Records are demimted by the new lines, and the fields are all terminated by a ",". The name fo the file is :
/flat_files/emp1.txt
To convert the files are the data soruce for an external table whoze metadata will resides int he database, you need to perform the following steps:
271
1. Create a directory object emp_dir as follows
CREATE DIRECTORY emp_dir AS '/flat_files';
2. Run the CREATE TABLE command shown in the slide
The example in the slide illustrates the table specification to create an external table for the file:
/flat_files/emp1.txt
In the example the TYPE specification is given only to illustrate its use. If not specified, ORACLE_LOADER is the default access driver. The ACCESS PARAMETERS provide values to parameters of the specific access driver and are interpreted by the access driver, not by the Oracle Server.
The PARALLEL clause enables five parallel execution servers to simultaneously scan the external data sources(files) when executing the INSERT INTO TABLE statement. For example if PARALLEL = 5 were specified, then more that one parallel execution server could be working on a data source. Beacuase external tables can be very large for performance reasons it is advisable to specify the PARALLEL clause, or a parallel hint for the query.
The REJECT LIMIT clause specifies that if nore than 200 conversion errors occur during the query of the external data, the query is aborted and an error returned. These conversion errors can arise when the access driver tries to transform the data in the data file to match the external table definition.
Querying External Table
An external table does not describe any data that is stored in the database. Nor does it describe how data is stored in the external source. Instead, it describes how the external tablelayer needs to present the data to the server. It is the responsibiliry of the access driver and the external table layer to do the necessary transformations required on the data in the data file so that is matches the external table definition.
When the database server needs to access data in an external soruce, it calls the appropriate access drive to get the data from an external source in a form that the database server expects.
It is important to remember that the dwcription of the data in the data source is separate from the definition of the external table. The source file can contain more or fewer fields than there are columns in the table. Also the data types for fields in the data source can be different from the columns in the table. The access driver takes care of ensuring the data from the data source is processed so that it matches the definition of the external table.
CREATE INDEX with CREATE TABLE statement
In the example in the slide, the CREATE INDEX clause is used with the CREATE TABLE statment to create a primary key index explicitly. This is an enhancement provided with Oracle 9i. You can now name your indexes at the time of PRIMARY key creation, unlike before where the Oracle Server would create an index, but you did not have any control over the name of the index. The following example illustrate this
CREATE TABLE EMP_UNNAMED_INDEX
(employee_id NUMBER(6) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2 (25));
(Table Created)
SELECT INDEX_NAME, TABLE_NAME
FROM USER_INDEXES
WHERE TABLE_NAME = 'EMP_UNNAMED_INDEX';
Observe that the Oracle server gives a name to the Index that it creates for the PRIMARY KEY column. But this name is cryptic and not easily understood. With Oracle9i, you can name your PRIMARY KEY column indexes as you create the table with the CREATE TABLE statement. However, proior to Oracle9i, if you named your primary key constraint at the time of constrainst creation, the index would also be creates with the same name as the constraint name.
Questions :
Refer to the SQL codes below:
SELECT manager_id, last_name, hire_date, salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
FROM employees;
What has been achieved?
A. it calculates, for each employee in the employees table, the average
salary of the employees reporting to his/her respective manager
B. it calculates, for each employee in the employees table, the average
salary of the employees reporting to the same manager who were hired in
the range just before through just after the employee
C. because of a syntax problem, no row will be returned
D. it calculates, for each employee in the employees table, the average
salary of the employees reporting to his/her respective manager who were
hired just after the employee
E. it calculates, for each employee in the employees table, the average
salary of the employees reporting to his/her respective manager who were
hired just before the employee
ANS:B
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment