Premium

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



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?
: View the Exhibits and
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.

Correct Answer : 4
Explanation: RIGHT OUTER JOIN operation, A RIGHT OUTER JOIN is one of the JOIN operations that allow you to specify a JOIN clause. It preserves the unmatched rows from the second (right) table, joining them with a NULL in the shape of the first (left) table. A LEFT OUTER JOIN B is equivalent to B RIGHT OUTER JOIN A, with the columns in a different order. Syntax
TableExpression RIGHT [ OUTER ] JOIN TableExpression
{ ON booleanExpression | USING clause }
The scope of expressions in the ON clause includes the current tables and any tables in query blocks outer to the current SELECT. The ON clause can reference tables not being joined and does not have to reference either of the tables being joined (though typically it does).
Example 1 :
-- get all countries and corresponding cities, including
-- countries without any cities
SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME FROM CITIES RIGHT OUTER JOIN COUNTRIES ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE
-- get all countries in Africa and corresponding cities, including
-- countries without any cities

SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME FROM CITIES RIGHT OUTER JOIN COUNTRIES ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE WHERE Countries.region = 'Africa'
-- use the synonymous syntax, RIGHT JOIN, to achieve exactly
-- the same results as in the example above

SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME FROM CITIES RIGHT JOIN COUNTRIES ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE WHERE Countries.region = 'Africa'
Example 2
-- a TableExpression can be a joinOperation. Therefore -- you can have multiple join operations in a FROM clause -- List every employee number and last name
-- with the employee number and last name of their manager

SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME FROM EMPLOYEE E RIGHT OUTER JOIN DEPARTMENT RIGHT OUTER JOIN EMPLOYEE M ON MGRNO = M.EMPNO ON E.WORKDEPT = DEPTNO








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?
: You want to create a table employees in
1. A,B
2. C,D
3. D,E
4. B,E
5. A,E

Correct Answer : 3
Exp: you can have NULLs in your columns unless the columns are specified NOT NULL. You will be able to store only one instance of NULLs however (no two sets of same columns will be allowed unless all columns are NULL) :
SQL> CREATE TABLE t (id1 NUMBER, id2 NUMBER);
SQL> ALTER TABLE t ADD CONSTRAINT u_t UNIQUE (id1, id2);
SQL> INSERT INTO t VALUES (1, NULL);
1 row inserted
SQL> INSERT INTO t VALUES (1, NULL);
INSERT INTO t VALUES (1, NULL)
ORA-00001: unique constraint (VNZ.U_T) violated
SQL> /* you can insert two sets of NULL, NULL however */
SQL> INSERT INTO t VALUES (NULL, NULL);
1 row inserted
SQL> INSERT INTO t VALUES (NULL, NULL);
1 row inserted
An inline not null constraint is declared after each individual column name:
CREATE TABLE test ( ... NAME VARCHAR2(64), PRODUCT_ID VARCHAR2(24) not null, ... )
If you wish to name the constraint explicitly then you also do this after each column:
CREATE TABLE test ( ... NAME VARCHAR2(64), PRODUCT_ID VARCHAR2(24) CONSTRAINT PRODUCT_ID_NN NOT NULL, ... )
You can of course modify the column after the table has been created:
ALTER TABLE test MODIFY product_id varchar2(24) not null;







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?

: View the Exhibit and examine the structure of the products table.
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.



Correct Answer : 3

Explanation: An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column:

SELECT SQRT(a*b) AS root FROM tbl_name
GROUP BY root HAVING root > 0;
SELECT id, COUNT(*) AS cnt FROM tbl_name
GROUP BY id HAVING cnt > 0;
SELECT id AS 'Customer identity' FROM tbl_name;
Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined. For example, the following query is illegal:

SELECT id, COUNT(*) AS cnt FROM tbl_name
WHERE cnt > 0 GROUP BY id;
The WHERE clause determines which rows should be included in the GROUP BY clause, but it refers to the alias of a column value that is not known until after the rows have been selected, and grouped by the GROUP BY.

In the select list of a query, a quoted column alias can be specified using identifier or string quoting characters:

SELECT 1 AS `one`, 2 AS 'two';
Elsewhere in the statement, quoted references to the alias must use identifier quoting or the reference is treated as a string literal. For example, this statement groups by the values in column id, referenced using the alias `a`:

SELECT id AS 'a', COUNT(*) AS cnt FROM tbl_name
GROUP BY `a`;
But this statement groups by the literal string 'a' and will not work as expected:

SELECT id AS 'a', COUNT(*) AS cnt FROM tbl_name
GROUP BY 'a';




Related Questions

Question : View the Exhibit and examine the
structure of the SALES table.
The following query is written to retrieve all
those product IDs from the SALES table that have
more than 55000 sold and have been ordered more than 10 times.
Which statement is true regarding this SQL statement?
 : View the Exhibit and examine the
1. It executes successfully and generates the required result.
2. It produces an error because count(*) should be specified in the SELECT clause also.
3. Access Mostly Uused Products by 50000+ Subscribers
4. It executes successfully but produces no result because COUNT (prod_id) should be used instead of COUNT (*).



Question : View the Exhibit and examine the structure of the customers table.
Using the customers table, you need to generate a report that shows an increase in the credit limit
by 15% for all customers. Customers whose credit limit has not been entered should have the
message "Not Available" displayed.
Which SQL statement would produce the required result?
 : View the Exhibit and examine the structure of the customers table.
1. A
2. B
 3. Access Mostly Uused Products by 50000+ Subscribers
4. D


Question : View the Exhibit and examine the structure of the promotions table.
Evaluate the given SQL statement:
Which statement is true regarding the outcome of the above query?

 : View the Exhibit and examine the structure of the promotions table.
1. It shows COST_REMARK for all the promos in the table.
2. It produces an error because the SUBQUERY gives an error.
 3. Access Mostly Uused Products by 50000+ Subscribers
4. It produces an error because SUBQUERIES cannot be used with the case expression.


Question : Examine the structure and data of the CUST_TRANS table:
Dates are stored in the default date format dd-mon-rr in the CUST_TRANS table. Which three
SQL statements would execute successfully?
1. SELECT transdate + '10' FROM cust_trans;
2. SELECT * FROM cust_trans WHERE transdate = '01-01-07';
 3. Access Mostly Uused Products by 50000+ Subscribers
4. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';
5. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;

: Examine the structure and data of the CUST_TRANS table:
1. 2, 4, 5
2. 2, 1, 4, 3, 5
 3. Access Mostly Uused Products by 50000+ Subscribers
4. 1, 2, 4, 5
5. 1, 3,4


Question : View the Exhibit and examine the structure of the customers table.
NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY
that have the same data types and size as the corresponding columns in the customers table.
Evaluate the following insert statement:
The insert statement fails when executed.
What could be the reason?
 : View the Exhibit and examine the structure of the customers table.
1. The values clause cannot be used in an INSERT with a subquery.
2. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match.
 3. Access Mostly Uused Products by 50000+ Subscribers
4. The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table.




Question : YOU need to display the date ll-oct- in words as 'Eleventh of October, Two Thousand
Seven'.
Which SQL statement would give the required result?

 : YOU need to display the date ll-oct- in words as 'Eleventh of October, Two Thousand
1. A
2. B
 3. Access Mostly Uused Products by 50000+ Subscribers
4. D