Oracle Advanced SQL and PL/SQL Developer Certification Questions and Answers (Dumps and Practice Questions)
Question : What is/are correct statement for the following query
SELECT p.product_id, p.product_name, categories.category_name
FROM products p
INNER JOIN categories
ON p.category_id = categories.category_id
ORDER BY p.product_name ASC, categories.category_name ASC;
1. You can not use Alias in Order by clause
2. You can not use Alias in JOIN clause
3. When you use Alias, use for all the tables
4. None of the above
Correct Answer : Get Lastest Questions and Answer :
Explanation: When you create an alias on a table, it is either because you plan to list the same table name more than once in the FROM clause (ie: self join), or you want to shorten the table name to make the SQL statement shorter and easier to read.
Let's look at an example of how to alias a table name in Oracle/PLSQL.
For example:
SELECT p.product_id, p.product_name, categories.category_name
FROM products p
INNER JOIN categories
ON p.category_id = categories.category_id
ORDER BY p.product_name ASC, categories.category_name ASC;
In this example, we've created an alias for the products table called p. Now within this SQL statement, we can refer to the products table as p.
When creating table aliases, it is not necessary to create aliases for all of the tables listed in the FROM clause. You can choose to create aliases on any or all of the tables.
For example, we could modify our example above and create an alias for the categories table as well.
SELECT p.product_id, p.product_name, c.category_name
FROM products p
INNER JOIN categories c
ON p.category_id = c.category_id
ORDER BY p.product_name ASC, c.category_name ASC;
Now we have an alias for categories table called c as well as the alias for the products table called p.
Question : Which type of Join illustrated in the image. (Blue color is for selected data)
1. Right
2. Left
3. Inner
4. None of the above
Correct Answer : Get Lastest Questions and Answer :
Explanation: Another type of join is called an Oracle RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
Syntax : The syntax for the Oracle RIGHT OUTER JOIN is:
SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
In some databases, the RIGHT OUTER JOIN keywords are replaced with RIGHT JOIN.The Oracle RIGHT OUTER JOIN would return the all records from table2 and only those records from table1 that intersect with table2.
Example : Here is an example of an Oracle RIGHT OUTER JOIN:
SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
This RIGHT OUTER JOIN example would return all rows from the orders table and only those rows from the suppliers table where the joined fields are equal.
If a supplier_id value in the orders table does not exist in the suppliers table, all fields in the suppliers table will display as (null) in the result set.
Question : Select the equivalent statement of below query
SELECT *
FROM customers
WHERE customer_id NOT BETWEEN 3000 AND 3500;
1. SELECT *
FROM customers
WHERE customer_id < 3000
OR customer_id > 3500;
2. SELECT *
FROM customers
WHERE customer_id = < 3000
OR customer_id > 3500;
3. SELECT *
FROM customers
WHERE customer_id < 3000
OR customer_id >= 3500;
4. None of the above
Correct Answer : Get Lastest Questions and Answer :
Explanation: The Oracle BETWEEN condition can also be combined with the Oracle NOT operator. Here is an example of how you would combine the BETWEEN condition with the NOT Operator.
For example:
SELECT *
FROM customers
WHERE customer_id NOT BETWEEN 3000 AND 3500;
This Oracle BETWEEN example would return all rows from the customers table where the customer_id was NOT between 3000 and 3500, inclusive. It would be equivalent to the following SELECT statement:
SELECT *
FROM customers
WHERE customer_id < 3000
OR customer_id > 3500;
Related Questions
Question : Which of these set operators will not sort the rows?
1. INTERSECT
2. MINUS
3. Access Mostly Uused Products by 50000+ Subscribers
4. UNION ALL
Question : Select the correct statement which applies to single-row functions?
1. It will always retun value else error
2. It will be executed for each record
3. Access Mostly Uused Products by 50000+ Subscribers
4. All of the above
Question : Select the function which could do case conversion on latter
1. SMALLER
2. INITCASE
3. Access Mostly Uused Products by 50000+ Subscribers
4. All of the above
Question : What value is returned after executing the following statement?
LENGTH('Tech on the Net')
1. 15
2. 16
3. Access Mostly Uused Products by 50000+ Subscribers
4. 12
Question : What value is returned after executing the following statement?
LENGTH(NULL)
1. 0
2. 1
3. Access Mostly Uused Products by 50000+ Subscribers
4. Null
Question : What value is returned after executing the following statement?
LENGTH('')
1. 0
2. 1
3. Access Mostly Uused Products by 50000+ Subscribers
4. Null