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

Question : Which of the following will not be supported as part of set-oriented language.
A. WHILE
B. FOR DO
C. IF..THEN..ELSE
D. COMMIT
E. ROLLBACK


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

Correct Answer : Get Lastest Questions and Answer :
Explanation: Building Blocks of PL/SQL Programs : PL/SQL is a block-structured language. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END, which break up the block into three sections: Declarative: statements that declare variables, constants, and other code elements, which can then be used within that block . Executable: statements that are run when the block is executed
Exception handling: a specially structured section you can use to "catch," or trap, any exceptions that are raised when the executable section runs. Only the executable section is required. You don't have to declare anything in a block, and you don't have to trap exceptions raised in that block. A block itself is an executable statement, so you can nest blocks within other blocks. Here are some examples: The classic "Hello World!" block contains an executable section that calls the DBMS_OUTPUT.PUT_LINE procedure to display text on the screen:
BEGIN DBMS_OUTPUT.put_line ('Hello World!'); END;
Functions and procedures-types of named blocks-are discussed later in this article in more detail, as are packages. Briefly, however, a package is a container for multiple functions and procedures. Oracle extends PL/SQL with many supplied or built-in packages. This next example block declares a variable of type VARCHAR2 (string) with a maximum length of 100 bytes to hold the string 'Hello World!'. DBMS_OUTPUT.PUT_LINE then accepts the variable, rather than the literal string, for display:
DECLARE l_message VARCHAR2 (100) := 'Hello World!'; BEGIN DBMS_OUTPUT.put_line (l_message); END;
Note that I named the variable l_message. I generally use the l_ prefix for local variables-variables defined within a block of code-and the g_ prefix for global variables defined in a package. This next example block adds an exception section that traps any exception (WHEN OTHERS) that might be raised and displays the error message, which is returned by the SQLERRM function (provided by Oracle).
DECLARE l_message VARCHAR2 (100) := 'Hello World!'; BEGIN DBMS_OUTPUT.put_line (l_message); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (SQLERRM); END;
The following example block demonstrates the PL/SQL ability to nest blocks within blocks as well as the use of the concatenation operator (||) to join together multiple strings.
DECLARE
l_message
VARCHAR2 (100) := 'Hello';
BEGIN
DECLARE
l_message2 VARCHAR2 (100) := l_message || ' World!';
BEGIN DBMS_OUTPUT.put_line (l_message2); END;
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
END;





Question :. You have two user called HadoopExamDev and HadoopExamTest in oracle database, now you have created a schema called HadoopExam with user
HadoopExamDev, and want to move it to HadoopExamTest, what is the best approach for doing that.

1. Using ALTER Schema command
2. Not possible
3. Access Mostly Uused Products by 50000+ Subscribers
4. USING UPDATE SCHEMA command

Correct Answer : Get Lastest Questions and Answer :

Explanation: In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account.

You create users with the create user statement. This also "creates" the schema (initially empty) - you cannot create a schema as such, it is tied to the user. Once the user is created, an administrator can grant privileges to the user, which will enable it to create tables, execute select queries, insert, and everything else.

The database is the thing contains all the users you've created, and their data (and a bunch of predefined system users, tables, views, etc. that make the whole thing work). You should look at the Oracle Database Architecture documentation in the Concepts Guide (actually, that whole page is worth a read - there's a section about users and schemas higher up in that page) to get an introduction to what a database is, and what a database instance is - two important concepts.

You can create a database with the create database statement (see also here), once you've installed the Oracle software stack. But using dbca (database creation assistant) is easier to get started.






Question : Which query creates a projection of the EMPLOYEE_NAME and LOCATION_ID columns from the EMPLOYEES table?

1. SELECT DISTINCT EMPLOYEE_NAME, LOCATION_ID FROM EMPLOYEES;
2. SELECT EMPLOYEE_NAME, LOCATION_ID FROM EMPLOYEES;
3. Access Mostly Uused Products by 50000+ Subscribers
4. SELECT EMPLOYEE_NAME AS "LOCATION_ID" FROM EMPLOYEES;

Correct Answer : Get Lastest Questions and Answer :
Explanation: In Relational algebra, projection means collecting a subset of columns for use in operations, i.e. a projection is the list of columns selected. In a query optimiser step, the projection will manifest itself as a buffer or spool area of some description containing a subset of the columns from the underlying table or operator, or a logical view based on those columns that is used by subsequent operations. In a view, a projection equates to the list of columns selected in the query underlying the view. Projection refers to that subset of the set of all columns found in a table, that you want returned. It can range anywhere from 0** up to the complete set.

There are two "sets" in a table that correspond to a table's two dimensions. Each table has a set of columns as well as a set of rows. Each individual value in a table can be found at a specific intersection of these two sets*. However, your value is not found by "going to" an address, such as T60, the way you would with Excel. There is no order inherent in relational tables. That's important to remember. Instead, to retrieve your single value, you would select for a subset of the columns (one in this case) and a subset of the rows (one in this case). To select a subset of one column from all those available, you only need to know its name, as well as the table name. The column name will be unique within its table. Once you've chosen the column your value is in, you'll need to choose the particular row it's in. However, rows don't have names the way columns do. The way we specify a row is . . . well, we don't exactly . . . We specify something that we know about the value we're looking for. You have to know something related to (literally) the value you're looking for. If you can specify enough related information then you may be able to reduce the set of returned rows to just the one you're looking for. Think of it as though a college student lost his or her backpack. Let's say a guy lost it. He calls lost and found, and the woman attending says "Yea, we have a couple dozen backpacks. Can you describe it?" You say "Well, it's blue?". To which she replies "Are you asking me or telling me", and then says "I have eight blue ones, gonna have to do better than that, and you didn't sound too sure about that." You say "Let's see, umm, oh yea! It had my math 1010 book in it." "Good, now you're down to four." Then you remember you're supposed to meet your girlfriend, Lucy, in 15 minutes, and that triggered another memory - of the time you were bored in Math 1010 and wrote, - in real small letters on the bottom of the backpack, "I love Lucy". She turns them over and sure enough, she says "the third one says 'I love Lucy' on it. Come pick it up, Ricky." Projection might be likened to saying what you lost - a backpack. Selection may be likened to describing its attributes: it's blue, has a math 1010 book inside, and has "I love Lucy" written on the bottom. You do the same thing with SQL. First, what kind of information you want to see. Second, criteria describing which one or ones you want to see. These are called predicates or truth statements. They are true for one or more members of the set. If they aren't, they do not return any values. Some vendors' SQL implementations provide means to break these rules, such as Nested Tables in Oracle. However, standard two dimensional tables are still the predominate form.

Related Questions

---

Question : Examine the structure proposed for the transactions table:
Which two statements are true regarding the creation and storage of data in the above table
structure?
A. The CUST_STATUS column would give an error.
B. The TRANS_VALIDITY column would give an error.
C. The CUST_STATUS column would store exactly one character.
D. The CUST_CREDIT_LIMIT column would not be able to store decimal values.
E. The TRANS_VALIDITY column would have a maximum size of one character.
F. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds



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



Question : You need to create a table for a banking application. One of the columns in the table has the following requirements:
1) You want a column in the table to store the duration of the credit period.
2) The data in the column should be stored in a format such that it can be easily added and
subtracted with date data type without using conversion functions.
3) The maximum period of the credit provision in the application is 30 days.
4) The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?

1. DATE
2. NUMBER
3. TIMESTAMP
4. INTERVAL DAY TO SECOND
5. INTERVAL YEAR TO MONTH


Question : Which three tasks can be performed using SQL functions built into Oracle Database?
A. Displaying a date in a nondefault format
B. Finding the number of characters in an expression
C. Substituting a character string in a text expression with a specified string
D. Combining more than two columns or expressions into a single column in the output

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




Question :

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



Question : Select the wrong statement from below.



1. TO_CHAR may convert date items to character items.
2. TO_DATE may convert character items to date items.
3. TO_CHAR may convert numbers to character items.
4. TO_DATE may convert date items to character items.



Question : What value is returned after executing the following statement?
TO_CHAR(1210.73, '9999.9')



1. 1210.9
2. 1210.7
3. 1210.73
4. 1210