How to recreate or reload Datapump Utility EXPDP/IMPDP to overcome internal corruption

In some cases DataPump utility expdp and impdp binary might get corrupted and in such scenarios we need to recreate DataPump utility to overcome internal corruption. To do this, you need to run a set of scripts as given below.

At first you must need to login to database as sysdba.
$ sqlplus / as sysdba

For Oracle version 10.1g
1. Run the Catdp.sql script which orders the installation of all its components including the Metadata API which was previously installed separately. By default catproc.sql invoke this script.

SQL> @$ORACLE_HOME/rdbms/admin/catdp.sql

2. Next run the dbmspump.sql script will create DBMS procedures for dataPUMP

SQL> @$ORACLE_HOME/rdbms/admin/dbmspump.sql

For Oracle version 10.2, 11.1 and 11.2
1. Run Catdph.sql script which will Re-Install DataPump types and views.

SQL> @$ORACLE_HOME/rdbms/admin/catdph.sql

Note: If XDB is installed the it is required to run "catmetx.sql" script also.

2. Run prvtdtde.plb which will Re-Install tde_library packages

SQL> @$ORACLE_HOME/rdbms/admin/prvtdtde.plb

3. Catdpb.sql will Re-Install DataPump packages

SQL> @$ORACLE_HOME/rdbms/admin/catdpb.sql

4. Dbmspump.sql will Re-Install DBMS DataPump objects

SQL> @$ORACLE_HOME/rdbms/admin/dbmspump.sql

lastly, you can check invalid objects by running script utlrp.sql,

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

INS-06001: Failed to perform operation due to internal driver error INS-06006: Passwordless SSH connectivity

Problem Description
While installing Oracle 11gR2 Grid Infrastructure on HP-UX or AIX machin OUI giving error message whenever it is clicked on setup of configuring ssh.
INS-06006: Passwordless SSH connectivity not set up between the following node(s).

You might also face following on your AIX machine after clicking ssh setup.
INS-06001: Failed to perform operation due to internal driver error.

Cause of the Problem
1. The problem happened as the grid user or oracle user (the user you used to run runInstaller) does not have password on OS level.

2. If OUI is able to setup passwordless SSH connectivity but still shows the error, likely "ping" command is inaccessible - grid or oracle user should be able to execute ping command.

3. OS command ssh or scp are not in expected location.

4. May be $HOME/.ssh or files in it are owned by different user.

Solution of the Problem
1. Test the ssh.

Issue "passwd" command to set password in both machines. Note that if you don't set the password in the RAC nodes then these error will fire.

Also, please test ssh command in both nodes of your RAC.

on node1 execute:
ssh node1 date
ssh node2 date

and on node 2:
ssh node1 date
ssh node2 date

2. If OS command ssh or scp are not in expected location, the workaround is to create symbolic link on all nodes:

ln -s /usr/local/bin/ssh /usr/bin/ssh
ln -s /usr/local/bin/scp /usr/bin/scp

3. Check the permission on file $HOME/.ssh
$ ls -l $HOME/.ssh

root.sh failed to create OCR in Oracle version 11.2.0.1 on Solaris

Problem Description
When installing Grid Infrastructure on Solaris SPARC (64-bit), the root.sh script fails with the following errors:

2011-01-07 11:04:28: Creating or upgrading OCR keys
2011-01-07 11:04:42: Command return code of 255 (65280) from command: /opt/oracle/app/11.2.0/grid/bin/ocrconfig -upgrade oracle dba
2011-01-07 11:04:42: Failed to create Oracle Cluster Registry configuration, rc 255
2011-01-07 11:04:42: Exiting exclusive mode
2011-01-07 11:04:42: Command return code of 1 (256) from command: /opt/oracle/app/11.2.0/grid/bin/crsctl stop resource ora.crsd -init
2011-01-07 11:04:42: Stop of resource "ora.crsd -init" failed
2011-01-07 11:04:42: Failed to stop CRSD
2011-01-07 16:03:22: Initial cluster configuration failed. See /opt/oracle/app/11.2.0/grid/cfgtoollogs/crsconfig/rootcrs_racnode1.log for details

From the ocrconfig log file

2011-01-07 11:04:42.150: [ OCRASM][1]proprasmo: Error in open/create file in dg [DATA]
[ OCRASM][1]SLOS : SLOS: cat=8, opn=kgfolclcpi1, dep=203, loc=kgfokge
AMDU-00203: File Deleted. Cannot extract file DATA.255
AMDU-00204: Disk N0002 is in currently mounted diskgroup DATA
AMDU-00201: Disk N0002: '/dev/rdsk/c4t48495

2011-01-07 11:04:42.252: [ OCRASM][1]proprasmo: kgfoCheckMount returned [7]
2011-01-07 11:04:42.252: [ OCRASM][1]proprasmo: The ASM instance is down
2011-01-07 11:04:42.269: [ OCRRAW][1]proprioo: Failed to open [+DATA]. Returned proprasmo() with [26]. Marking location as UNAVAILABLE.
..
2011-01-07 11:04:42.556: [ OCRAPI][1]a_init:7!: Backend init unsuccessful : [33]
2011-01-07 11:04:42.556: [ OCRCONF][1]There was no previous version of OCR. error:[PROC-33: Oracle Cluster Registry is not configured]


Cause of the Problem
The CRS clients are not able to connect to the ASM instance. This is caused by unpublished Bug 8979500 11.2_SOL.X64_090813.0928: ROOT.SH FAILING DURING CRS INSTALL WITH GNS ON ASM
Solution.

Solution of the Problem
1. De-config the cluster by running

$GRID_HOME/crs/installl/rootcrs.pl -deconfig -force -lastnode

2. Apply Patch 8979500 available for 11.2.0.1 on Solaris Sparc 64-Bit.

3. Run root.sh again.

Oracle DBA Interview Questions Answered - Part 01

If you ever want to do Oracle DBA jobs then you might waste your time to find generic Oracle DBA questions which are frequently asked in an interview session. I myself also ask some generic Interview questions while recruiting new DBAs. In this blog post I will list common questions that is asked in a interview session. I will also provide solution of the each question so that you can have an idea about how to answer those questions.

Question 01: Explain the different types of shutdown that can be performed in Oracle database.

Answer: There exists 1)Shutdown NORMAL / Shutdown 2)SHUTDOWN IMMEDIATE 3)SHUTDOWN TRANSACTIONAL 4)SHUTDOWN ABORT 5)Shutdown Transactional LOCAL. In order to learn about details about have a look at Shutdown Modes in Oracle.

Question 02: State the ways to startup Oracle Database.

Answer: We can start the database from Sql*plus or RMAN or from Enterprise Manager. In order to know the step have a look at Different ways of Starting Up a Database.

Question 03: Give the stages of an Oracle database instance startup.
Answer:
- STARTUP NOMOUNT - Instance startup. Only pfile in read in this stage.

- STARTUP MOUNT - The database is mounted. Control file is read in this stage.

- STARTUP OPEN - The database is opened. Data file and redo log file is read in this stage.
To know about the details of startup phase and to check them have a look at Different Oracle Database Status and Active State.

Question 04: How to create a backup control file?

Answer: To make backup as binary controlfile issue,
ALTER DATABASE BACKUP CONTROLFILE TO '/oradata2/control.bak';
To take backup as text file issue,
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/oradata2/control.bak';
Details with description is discussed in Backup Database control file - User Managed.

Question 05: What column differentiates the V$ views to the GV$ views and how?
Answer:
In GV$ and extra column INST_ID is there which indicates the instance in a RAC environment the information came from. In order to know more follow V$ and GV$ Dynamic Performance Views.

Question 06: Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

Answer: $ORACLE_BASE is the root directory for oracle. $ORACLE_HOME path is located beneath ORACLE_BASE where the oracle products reside.

Question 07: How you can generate Explain plan and check the execution of your statement.

Answer: You can check the explain plan by using AUTOTRACE setting of SQL*Plus as well as using DBMS_XPLAN Package. In details it is described in How to see Explain Plan from SQL*Plus.

Question 08: Explain the common Oracle error ORA-01555

Answer: You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.

Question 09: Explain the differences between difference types of Oracle backup, state Answer: what is hot and a cold backup and the benefits associated with each.
In terms of Oracle, backup can be A)Whole or Partial Backups B)Full or Incremental Backups C)Consistent or Inconsistent Backups D)Image Copies or Backup Sets. Detail description about these types of backup is discussed in Types of Oracle Database Backup.

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk. Hot backup procedures discussed in User managed hot backup procedure.

Question 10: You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?

Answer: I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.

Question 11: How do you switch between spfile and pfile.

Answer: Pfile can be created from an SPFILE using the "create pfile from spfile" command and Spfile can be created from a PFILE using the "create spfile from pfile" command. Details about pfile and spfile is discussed in Pfile and spfile in Oracle.


Question 12: Explain the difference between a data block, an extent and a segment.

Answer: A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

Question 13: Give two examples of how you might determine the structure of the table EMP.

Answer: We can just issue "describe emp" in order to view emp table structure or use the dbms_metadata.get_ddl package to see detail structure about emp table. A more descriptive example can be seen from Table, View, Tablespace Creation Script in Oracle.

Question 14: Where would you look for errors from the database.

Answer: In the database alert log all errors are listed. In 10g the alert log location is defined by initialization parameter background_dump_dest. In Oracle database 11g, if you want to look for alert log file just like plain text file not in XML file then navigate to
[diagnostic_dest]/diag/rdbms/[dbname]/[instname]/trace or ORACLE_HOME/log/trace folder and then open the alert_[instance_name].log. I have also written a fine document about how to check alert log in 11g in the post How to check alert log in Oracle 11g. To check more about alert log have a look at What is alert log. Also you can monitor it from Database itself and I have written a document in How to monitor alert log file in Oracle.

Question 15: Compare and contrast TRUNCATE and DELETE for a table.

Answer: Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete. With example it is demonstrated in Difference between TRUNCATE, DELETE and DROP commands.

Question 16: Give the reasoning behind using an index.

Answer: Index is created in order increase the performance of query and to fasten update operation based on where clause.

Question 17: Give the two types of tables involved in producing a star schema and the type of data they hold.

Answer: Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.

Question 18: What type of index should you use on a fact table?

Answer: A Bitmap index. More idea about which type of Indexes can be created is given in When you would make Index and when not.

Question 19: Give two examples of referential integrity constraints.

Answer: A primary key and a foreign key constraint. A detail description is found in the post Type of constraints in oracle.

Question 20: A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?

Answer: First disable the foreign key constraint to the parent, then drop the table, re-create the table again and at last enable the foreign key constraint. I have written a script in the post ORA-02297: cannot disable constraint -dependencies exist in order to disable foreign key constraint.

Question 21: Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.

Answer: ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all online redo log files in other words backup all transactions that have occurred in the database so that you can recover to any point in time. In case of NOARCHIVELOG modes online redo log files are not saved in some other placed and thus they are overwritten as needed. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly. In case of production database you should enable archivelog so that you can't miss any data in case of failure occurs.

Oracle Certified Professional Exams IZ007 Questions Set 38

Q. Scott issues the SQL statements: to be done

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

Q. 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

Q. 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

Q. 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

Q. 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

Q. 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

Q . 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

Q. 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

Q. 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: BD

Q . 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 m
WHERE 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

Q. 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);

Answer: B

Q. 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

Q. 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

Q . 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

Q . 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
WHERE MIN(e salary)= t.max_salary

D. You cannot find the information because there is no common column between the two tables.

Answer : A

Oracle Certified Professional Exams IZ007 Questions Set 37

Q. 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_ADDRESSVARCHAR2(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

Q. 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

Q. 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 BD 

Q. 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

Q. 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
 

Q. Which / SQL* 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

Q.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

Q. 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. 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

Q. 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 emloyees 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

Q. 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

Q. 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

Q. 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

Q. 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

Q. 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

Q. 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

Q. 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

Q. 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

Q. 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

Q.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, updateON student_grades TO ROLE  manager

C. GRANT select, insert, modity ON  student_grades TO manager WITH GRANT OPTION;

D. GRANT select, insert, updateON 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

Q. 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

Oracle Certified Professional Exams IZ007 Questions Set 36

Q. 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

Q. 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

Q. Which is an /SQL * Plus command?

A. INSERT

B. UPDATE

C. SELECT

D. DESCRIBE

E. DELETE

F. RENAME

Answer; D

Q. 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

Q. 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

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

Q. 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

Q. Which clause should you use to exclude group results?

A. WHERE

B. HAVING

C. RESTRICT

D. GROUP BY

E. ORDER BY

Answer:  B

Q. 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

Q. 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

Q. 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

Q. 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

Q. 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

Q. 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

Q. 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

Q. 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

Q. 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:  ACD

Q. Which two are character manipulation functions? (Choose two)

A. TRIM

B. REPLACE

C. TRUNC

D. TO_DATE

E. MOD

F. CASE

Answer:  AB

Q. 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

Q. 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

Q. 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

Q. 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