Oracle Advanced SQL and PL/SQL Developer Certification Questions and Answers (Dumps and Practice Questions)

Question : Select the possible languages You can use in the Oracle c database, to derive the a particular stock return using a formula and data stored in the table.
A. SQL
B. C
C. PL/SQL
D. Java
E. Any other language linked to the OCI libraries


1. A,C,D
2. B,C,D
3. Access Mostly Uused Products by 50000+ Subscribers
4. B,D,E

Correct Answer : Get Lastest Questions and Answer :
Exp Oracle Call Interface (OCI) is the comprehensive, high performance, native C language interface to Oracle Database. It exposes the full power of Oracle Database to custom or packaged C applications. Oracle In-Memory Database Cache also supports access from OCI programs.

A broad range of customers including Fortune 500 customers and ISVs develop high performance applications directly in OCI.

OCI is highly reliable. Oracle tools such as SQL*Plus, Real Application Testing (RAT), SQL*Loader, and Data-Pump all use OCI. OCI also provides the foundation on which other language-specific interfaces such as Oracle JDBC-OCI, Oracle Data Provider for Net (ODP.Net), Oracle Precompilers, Oracle ODBC, and Oracle C++ Call Interface (OCCI) drivers are built. OCI is also used by leading scripting language drivers such as PHP OCI8, ruby-oci8, Perl DBD::Oracle, Python cx_Oracle, and the statistical programming language R's ROracle driver.





Question : Which is the most suitable form for data to be stored in RDBMS so can be processed easily.

1. First normal form
2. Second normal form
3. Access Mostly Uused Products by 50000+ Subscribers
4. Fourth normal form
5. Fifth normal form

Correct Answer : Get Lastest Questions and Answer :
Explanation: First Normal Form : An entity is in First Normal Form (1NF) when all tables are two-dimensional with no repeating groups.
A row is in first normal form (1NF) if all underlying domains contain atomic values only. 1NF eliminates repeating groups by putting each into a separate table and connecting them with a one-to-many relationship. Make a separate table for each set of related attributes and uniquely identify each record with a primary key.
Eliminate duplicative columns from the same table.
Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).
Second Normal Form: An entity is in Second Normal Form (2NF) when it meets the requirement of being in First Normal Form (1NF) and additionally:
Does not have a composite primary key. Meaning that the primary key can not be subdivided into separate logical entities.
All the non-key columns are functionally dependent on the entire primary key.
A row is in second normal form if, and only if, it is in first normal form and every non-key attribute is fully dependent on the key.
2NF eliminates functional dependencies on a partial key by putting the fields in a separate table from those that are dependent on the whole key. An example is resolving many:many relationships using an intersecting entity.
Third Normal Form: An entity is in Third Normal Form (3NF) when it meets the requirement of being in Second Normal Form (2NF) and additionally:
Functional dependencies on non-key fields are eliminated by putting them in a separate table. At this level, all non-key fields are dependent on the primary key.
A row is in third normal form if and only if it is in second normal form and if attributes that do not contribute to a description of the primary key are move into a separate table. An example is creating look-up tables.
Boyce-Codd Normal Form: Boyce Codd Normal Form (BCNF) is a further refinement of 3NF. In his later writings Codd refers to BCNF as 3NF. A row is in Boyce Codd normal form if, and only if, every determinant is a candidate key. Most entities in 3NF are already in BCNF.
BCNF covers very specific situations where 3NF misses inter-dependencies between non-key (but candidate key) attributes. Typically, any relation that is in 3NF is also in BCNF. However, a 3NF relation won't be in BCNF if (a) there are multiple candidate keys, (b) the keys are composed of multiple attributes, and (c) there are common attributes between the keys.
Fourth Normal Form : An entity is in Fourth Normal Form (4NF) when it meets the requirement of being in Third Normal Form (3NF) and additionally:
Has no multiple sets of multi-valued dependencies. In other words, 4NF states that no entity can have more than a single one-to-many relationship within an entity if the one-to-many attributes are independent of each other.
Many:many relationships are resolved independently.
Fifth Normal Form: An entity is in Fifth Normal Form (5NF) if, and only if, it is in 4NF and every join dependency for the entity is a consequence of its candidate keys.





Question : Select the features which define SQL as a set-oriented language.

1. Each row must have unique key
2. Each row must have primary key
3. Access Mostly Uused Products by 50000+ Subscribers
4. One SQL statement can affect multiple rows.


Correct Answer : Get Lastest Questions and Answer :

Explanation: To answer this question, it is important to remember that every Website you visit, every application you run is constructed from a stack of software technologies. At the top of the stack is the presentation layer, the screens or interactive devices with which the user directly interacts. (These days the most popular languages for implementing presentation layers are Java and .NET.) At the very bottom of the stack is the machine code that communicates with the hardware.

Somewhere in the middle of the technology stack you will find the database, software that enables us to store and manipulate large volumes of complex data. Relational database technology, built around SQL, is the dominant database technology in the world today.

SQL is a very powerful, set-oriented language whose sole purpose is to manipulate the contents of relational databases, one command can affect many rows (a set), whereas a procedural language processes rows one by one. If you write applications built on Oracle Database, you (or someone writing code at a lower level in the technology stack) must be executing SQL statements to retrieve data from or change data in that database. Yet SQL cannot be used to implement all business logic and end-user functionality needed in our applications. That brings us to PL/SQL.

PL/SQL stands for Procedural Language/Structured Query Language. PL/SQL offers a set of procedural commands (IF statements, loops, assignments), organized within blocks (explained below), that complement and extend the reach of SQL.

It is certainly possible to build applications on top of SQL and Oracle Database without using PL/SQL. Utilizing PL/SQL to perform database-specific operations, most notably SQL statement execution, offers several advantages, though, including tight integration with SQL, improved performance through reduced network traffic, and portability (PL/SQL programs can run on any Oracle Database instance). Thus, the front-end code of many applications executes both SQL statements and PL/SQL blocks, to maximize performance while improving the maintainability of those applications.

Related Questions

---

Question : Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?

1. It executes successfully and displays rows in the descending order of PROMO_CATEGORY.

2. It produces an error because positional notation cannot be used in the order by clause with set operators.

3. It executes successfully but ignores the order by clause because it is not located at the end of the compound statement.

4. It produces an error because the order by clause should appear only at the end of a compound query-that is, with the last select statement.


Question : View the Exhibit and examine the structure of the product, component, and PDT_COMP tables.
In product table, PDTNO is the primary key.
In component table, COMPNO is the primary key.
In PDT_COMP table, PDTNO, COMPNO) is the primary key, PDTNO is the foreign key
referencing PDTNO in product table and COMPNO is the foreign key referencing the COMPNO in
component table.
You want to generate a report listing the product names and their corresponding component
names, if the component names and product names exist.
Evaluate the following query:
SQL>SELECT pdtno, pdtname, compno, compname
FROM product _____________ pdt_comp
USING (pdtno) ____________ component USING (compno)
WHERE compname IS NOT NULL;
Which combination of joins used in the blanks in the above query gives the correct output?

1. JOIN; JOIN

2. FULL OUTER JOIN; FULL OUTER JOIN
3. RIGHT OUTER JOIN; LEFT OUTER JOIN
4. LEFT OUTER JOIN; RIGHT OUTER JOIN



Question : View the Exhibit for the structure of the student and faculty tables.
You need to display the faculty name followed by the number of students handled by the faculty at
the base location.
Examine the given two SQL statements:

Which statement is true regarding the outcome?


1. Only statement 1 executes successfully and gives the required result.
2. Only statement 2 executes successfully and gives the required result.
3. Both statements 1 and 2 execute successfully and give different results.
4. Both statements 1 and 2 execute successfully and give the same required result.


Question : View the Exhibits and
examine products and sales tables.
You issue the following query to display
product name and the number of times the product has
been sold: What happens when the
above statement is executed?

1. The statement executes successfully and produces the required output.
2. The statement produces an error because item_cnt cannot be displayed in the outer query.
3. The statement produces an error because a subquery in the from clause and outer-joins cannot be used together.
4. The statement produces an error because the group by clause cannot be used in a subquery in the from clause.


Question : You want to create a table employees in
which the values of columns EMPLOYEES_ID and
LOGIN_ID must be unique and not null.
Which two SQL statements would create the required table?

1. A,B
2. C,D
3. D,E
4. B,E
5. A,E


Question : View the Exhibit and examine the structure of the products table.
Using the products table, you issue the following query to generate the names, current list price,
and discounted list price for all those products whose list price falls below $10 after a discount of
25% is applied on it.
The query generates an error. What is the reason for the error?


1. The parenthesis should be added to enclose the entire expression.
2. The double quotation marks should be removed from the column alias.
3. The column alias should be replaced with the expression in the where clause.
4. The column alias should be put in uppercase and enclosed within double quotation marks in the where clause.