Oracle Database: SQL Fundamentals I

QUESTION NO: 1

Evaluate the SQL statement:

TRUNCATE TABLE DEPT;

Which three are true about the SQL statement? (Choose three.)

A. It releases the storage space used by the table.

B. It does not release the storage space used by the table.

C. You can roll back the deletion of rows after the statement executes.

D. You can NOT roll back the deletion of rows after the statement executes.

E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.

F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table

Answer: A,D,F

Explanation:

A: The TRUNCATE TABLE Statement releases storage space used by the table,

D: Can not rollback the deletion of rows after the statement executes,

F: You must be the owner of the table or have DELETE ANY TABLE system privilege to truncate the DEPT table.

Incorrect answer:

Cis not true

Dis not true

Eis not true

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-18

QUESTION NO: 2

You need to design a student registration database that contains several tables storing academic information.

The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student's grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key.

You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. Which statement creates the foreign key?

A. CREATE TABLE student_grades (student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY students(student_id));

B. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));

C. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id));

D. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));

Answer: D

Explanation:

CONSTRAINT name FOREIGN KEY (column_name) REFERENCES table_name (column_name);

Incorrect answer:

Ainvalid syntax

Binvalid syntax

Cinvalid syntax

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-14

QUESTION NO: 3

Here is the structure and data of the CUST_TRANS table:

Exhibit:

Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table.

Which three SQL statements would execute successfully? (Choose three.)

A. SELECT transdate + '10' FROM cust_trans;

B. SELECT * FROM cust_trans WHERE transdate = '01-01-07';

C. SELECT transamt FROM cust_trans WHERE custno > '11';

D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';

E. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;

Answer: A,C,D

Explanation:

QUESTION NO: 4

See the Exhibit and examine the structure and data in the INVOICE table:

Exhibit:

Which two SQL statements would executes successfully? (Choose two.)

A. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;

B. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;

C. SELECT (AVG(inv_date) FROM invoice;

D. SELECT AVG(inv_date - SYSDATE),AVG(inv_amt) FROM invoice;

Answer: A,D

Explanation:

QUESTION NO: 5

Which three statements are true regarding sub queries? (Choose three.)

A. Multiple columns or expressions can be compared between the main query and sub query

B. Main query and sub query can get data from different tables

C. Sub queries can contain GROUP BY and ORDER BY clauses

D. Main query and sub query must get data from the same tables

E. Sub queries can contain ORDER BY but not the GROUP BY clause

F. Only one column or expression can be compared between the main query and subqeury

Answer: A,B,C

Explanation:

QUESTION NO: 6

See the Exhibit and examine the structure of the CUSTOMERS table:

Using the CUSTOMERS table, you need to generate a report that shown the average credit limit for customers in WASHINGTON and NEW YORK.

Which SQL statement would produce the required result?

A. SELECT cust_city, AVG(cust_credit_limit)FROM customersWHERE cust_city IN ('WASHINGTON','NEW YORK')GROUP BY cust_credit_limit, cust_city;

B. SELECT cust_city, AVG(cust_credit_limit)FROM customersWHERE cust_city IN ('WASHINGTON','NEW YORK')GROUP BY cust_city,cust_credit_limit;

C. SELECT cust_city, AVG(cust_credit_limit)FROM customersWHERE cust_city IN ('WASHINGTON','NEW YORK')GROUP BY cust_city;

D. SELECT cust_city, AVG(NVL(cust_credit_limit,0))FROM customersWHERE cust_city IN ('WASHINGTON','NEW YORK');

Answer: C

Explanation:

Creating Groups of Data: GROUP BY Clause Syntax

You can use the GROUP BY clause to divide the rows in a table into groups. You can then use the group functions to return summary information for each group.

In the syntax:

group_by_expression Specifies the columns whose values determine the basis for grouping rows

Guidelines

• If you include a group function in a SELECT clause, you cannot select individual results as well, unless the individual column appears in the GROUP BY clause. You receive an error message if you fail to include the column list in the GROUP BY clause.

• Using a WHERE clause, you can exclude rows before dividing them into groups.

• You must include the columns in the GROUP BY clause.

• You cannot use a column alias in the GROUP BY clause.

QUESTION NO: 7

Evaluate these two SQL statements:

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

Answer: A

Explanation:

Explanation: the two statement produce identical results as ORDER BY 2 will take the second column as sorting column.

Incorrect answer:

Bthere is no syntax error

Cresult are sorted in ascending order by default

DORDER BY 2 will take the second column as sorting column.

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22

QUESTION NO: 8

Where can sub queries be used? (Choose all that apply)

A. field names in the SELECT statement

B. the FROM clause in the SELECT statement

C. the HAVING clause in the SELECT statement

D. the GROUP BY clause in the SELECT statement

E. the WHERE clause in only the SELECT statement

F. the WHERE clause in SELECT as well as all DML statements

Answer: A,B,C,F

Explanation:

SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query.

A subquery can have any of the usual clauses for selection and projection. The following are required clauses:

■ A SELECT list

■ A FROM clause

The following are optional clauses:

■ WHERE

■ GROUP BY

■ HAVING

The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent.

QUESTION NO: 9

Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

A. SELECT TO_CHAR(1890.55,'$99G999D00')FROM DUAL;

B. SELECT TO_CHAR(1890.55,'$9,999V99')FROM DUAL;

C. SELECT TO_CHAR(1890.55,'$0G000D00')FROM DUAL;

D. SELECT TO_CHAR(1890.55,'$99G999D99')FROM DUAL;

E. SELECT TO_CHAR(1890.55,'$9,999D99')FROM DUAL;

Answer: A,C,D

Explanation:

QUESTION NO: 10

Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?

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

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

C. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement

D. It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators

Answer: A

Explanation:

Using the ORDER BY Clause in Set Operations

The ORDER BY clause can appear only once at the end of the compound query.

Component queries cannot have individual ORDER BY clauses.

The ORDER BY clause recognizes only the columns of the first SELECT query.

By default, the first column of the first SELECT query is used to sort the output in an ascending order.

QUESTION NO: 11

Which statement correctly describes SQL and /SQL*Plus?

A. Both SQL and /SQL*plus allow manipulation of values in the database.

B. /SQL*Plus recognizes SQL statements and sends them to the server; SQL is the Oracle proprietary interface for executing SQL statements.

C. /SQL*Plus is a language for communicating with the Oracle server to access data; SQL recognizes SQL statements and sends them to the server.

D. SQL manipulates data and table definitions in the database; /SQL*Plus does not allow manipulation of values in the database.

Answer: A

Explanation:

QUESTION NO: 12

Which four are types of functions available in SQL? (Choose 4)

A. string

B. character

C. integer

D. calendar

E. numeric

F. translation

G. date

H. conversion

Answer: B,E,G,H

Explanation:

Explanation: SQL have character, numeric, date, conversion function.

Incorrect answer:

ASQL have character, numeric, date, conversion function.

CSQL have character, numeric, date, conversion function.

DSQL have character, numeric, date, conversion function.

FSQL have character, numeric, date, conversion function.

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 3-3

QUESTION NO: 13

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

Which MERGE statement is valid?

A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET

B. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);

C. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET

D. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||', '||e.last_name);

E. MERGE INTO new_employees cUSING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET

F. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);

G. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET

H. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees valueS(e.employee_id, e.first_name ||', '||e.last_name);

Answer: A

Explanation:

Explanation: this is the correct MERGE statement syntax

Incorrect answer:

Bit should MERGE INTO table_name

Cit should be WHEN MATCHED THEN

Dit should MERGE INTO table_name

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29

QUESTION NO: 14

Which view should a user query to display the columns associated with the constraints on a table owned by the user?

A. USER_CONSTRAINTS

B. USER_OBJECTS

C. ALL_CONSTRAINTS

D. USER_CONS_COLUMNS

E. USER_COLUMNS

Answer: D

Explanation:

view the columns associated with the constraint names in the USER_CONS_COLUMNS view.

Incorrect answer:

Atable to view all constraints definition and names

Bshow all object name belong to user

Cdoes not display column associated

Eno such view

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-25

QUESTION NO: 15

The COMMISSION column shows the monthly commission earned by the employee.

Exhibit

Which two tasks would require sub queries or joins in order to be performed in a single step? (Choose two.)

A. listing the employees who earn the same amount of commission as employee 3

B. finding the total commission earned by the employees in department 10

C. finding the number of employees who earn a commission that is higher than the average commission of the company

D. listing the departments whose average commission is more that 600

E. listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID

F. listing the employees whose annual commission is more than 6000

Answer: A,C

Explanation:

QUESTION NO: 16

Examine the structure of the STUDENTS table:

You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.

Which SQL statement accomplishes this task?

A. SELECT student_ id, marks, ROWNUM "Rank"FROM studentsWHERE ROWNUM <= 10AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99AND course_id = 'INT_SQL'ORDER BY marks DESC;

B. SELECT student_id, marks, ROWID "Rank"FROM studentsWHERE ROWID <= 10AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'AND course_id = 'INT_SQL'ORDER BY marks;

C. SELECT student_id, marks, ROWNUM "Rank"FROM (SELECT student_id, marksFROM studentsWHERE ROWNUM <= 10AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'AND course_id = 'INT_SQL'ORDER BY marks DESC);

D. SELECT student_id, marks, ROWNUM "Rank”FROM (SELECT student_id, marksFROM studentsWHERE (finish_date BETWEEN ’01-JAN-99 AND ’31-DEC-99’AND course_id = ‘INT_SQL’ORDER BY marks DESC)WHERE ROWNUM <= 10 ;

E. SELECTstudent id, marks, ROWNUM “Rank”FROM(SELECT student_id, marksFROM studentsORDER BY marks)WHEREROWNUM <= 10ANDfinish date BETWEEN ’01-JAN-99’ AND ’31-DEC-99’ANDcourse_id = ‘INT_SQL’;

Answer: D

Explanation:

QUESTION NO: 17

Evaluate the following SQL statements:

Exhibit:

Exhibit:

The above command fails when executed. What could be the reason?

A. The BETWEEN clause cannot be used for the CHECK constraint

B. SYSDATE cannot be used with the CHECK constraint

C. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY

D. The CHECK constraint cannot be placed on columns having the DATE data type

Answer: B

Explanation:

CHECK Constraint

The CHECK constraint defines a condition that each row must satisfy. The condition can use the

same constructs as the query conditions, with the following exceptions:

References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns

Calls to SYSDATE, UID, USER, and USERENV functions

Queries that refer to other values in other rows

A single column can have multiple CHECK constraints that refer to the column in its definition.

There is no limit to the number of CHECK constraints that you can define on a column.

CHECK constraints can be defined at the column level or table level.

CREATE TABLE employees

(...

salary NUMBER(8,2) CONSTRAINT emp_salary_min

CHECK (salary > 0),

QUESTION NO: 18

Evaluate the following SQL statements:

DELETE FROM sales;

There are no other uncommitted transactions on the SALES table.

Which statement is true about the DELETE statement?

A. It removes all the rows as well as the structure of the table

B. It removes all the rows in the table and deleted rows cannot be rolled back

C. It removes all the rows in the table and deleted rows can be rolled back

D. It would not remove the rows if the table has a primary key

Answer: C

Explanation:

QUESTION NO: 19

Examine the structure of the EMPLOYEES table:

You want to create a SQL script file that contains an INSERT statement. When the script is run, the INSERT statement should insert a row with the specified values into the EMPLOYEES table. The INSERT statement should pass values to the table columns as specified below:

Which INSERT statement meets the above requirements?

A. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);

B. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did IN (20,50));

C. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50)) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);

D. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50) WITH CHECK OPTION)VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);

E. INSERT INTO (SELECT * FROM employees WHERE (department_id = 20 AND department_id = 50) WITH CHECK OPTION ) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);

Answer: D

Explanation:

QUESTION NO: 20

Which two statements are true regarding constraints? (Choose two.)

A. A constraint can be disabled even if the constraint column contains data

B. A constraint is enforced only for the INSERT operation on a table

C. A foreign key cannot contain NULL values

D. All constraints can be defined at the column level as well as the table level

E. A columns with the UNIQUE constraint can contain NULL values

Answer: A,E

Explanation:

QUESTION NO: 21

Which two statements are true about sequences created in a single instance database? (Choose two.)

A. CURRVAL is used to refer to the last sequence number that has been generated

B. DELETE <sequencename> would remove a sequence from the database

C. The numbers generated by a sequence can be used only for one table

D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement

E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted

Answer: A,D

Explanation:

Gaps in the Sequence

Although sequence generators issue sequential numbers without gaps, this action occurs independent of a commit or rollback. Therefore, if you roll back a statement containing a sequence, the number is lost.

Another event that can cause gaps in the sequence is a system crash. If the sequence caches values in memory, those values are lost if the system crashes.

Because sequences are not tied directly to tables, the same sequence can be used for multiple tables.

However, if you do so, each table can contain gaps in the sequential numbers.

Modifying a Sequence

If you reach the MAXVALUE limit for your sequence, no additional values from the sequence are allocated and you will receive an error indicating that the sequence exceeds the MAXVALUE. To continue to use the sequence, you can modify it by using the ALTER SEQUENCE statement

To remove a sequence, use the DROP statement:

DROP SEQUENCE dept_deptid_seq;

QUESTION NO: 22

The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.

Which statement would create a synonym ORD so that HR can execute the following query successfully?

SELECT * FROM ord;

A. CREATE SYNONYM ord FOR orders; This command is issued by OE.

B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.

C. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator.

D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.

Answer: D

Explanation:

Creating a Synonym for an Object

To refer to a table that is owned by another user, you need to prefix the table name with the name of the user who created it, followed by a period. Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects.

This method can be especially useful with lengthy object names, such as views.

In the syntax:

PUBLIC Creates a synonym that is accessible to all users synonym Is the name of the synonym to be created object Identifies the object for which the synonym is created

Guidelines

The object cannot be contained in a package.

A private synonym name must be distinct from all other objects that are owned by the same user.

If you try to execute the following command (alternative B, issued by OE):

QUESTION NO: 23

Evaluate this SQL statement:

SELECT e.emp_name, d.dept_name

FROM employees e

JOIN departments d

USING (department_id)

WHERE d.department_id NOT IN (10,40)

ORDER BY dept_name;

The statement fails when executed. Which change fixes the error?

A. remove the ORDER BY clause

B. remove the table alias prefix from the WHERE clause

C. remove the table alias from the SELECT clause

D. prefix the column in the USING clause with the table alias

E. prefix the column in the ORDER BY clause with the table alias

F. replace the condition”d.department_id NOT IN (10,40)”in the WHERE clause with”d.department_id <> 10 AND d.department_id <> 40”

Answer: B

Explanation:

QUESTION NO: 24

Examine the statement:

Create synonym emp for hr.employees;

What happens when you issue the statement?

A. An error is generated.

B. You will have two identical tables in the HR schema with different names.

C. You create a table called employees in the HR schema based on you EMP table.

D. You create an alternative name for the employees table in the HR schema in your own schema.

Answer: D

Explanation:

QUESTION NO: 25

Evaluate the following SQL query;

What would be the outcome?

A. 200

B. 16

C. 160

D. 150

E. 100

Answer: C

Explanation:

Function Purpose

ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.)

TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero

QUESTION NO: 26

Which two statements are true regarding single row functions? (Choose two.)

A. They can be nested only to two levels

B. They always return a single result row for every row of a queried table

C. Arguments can only be column values or constant

D. They can return a data type value different from the one that is referenced

E. They accept only a single argument

Answer: B,D

Explanation:

A function is a program written to optionally accept input parameters, perform an operation, or return a single value. A function returns only one value per execution.

Three important components form the basis of defining a function. The first is the input parameter list. It specifies zero or more arguments that may be passed to a function as input for processing. These arguments or parameters may be of differing data types, and some are mandatory while others may be optional. The second component is the data type of its resultant value. Upon execution, only one value is returned by the function. The third encapsulates the details of the processing performed by the function and contains the program code that optionally manipulates the input parameters, performs calculations and operations, and generates a return value.

QUESTION NO: 27

Which statement is true regarding the UNION operator?

A. The number of columns selected in all SELECT statements need to be the same

B. Names of all columns must be identical across all SELECT statements

C. By default, the output is not sorted

D. NULL values are not ignored during duplicate checking

Answer: A

Explanation:

The SQL UNION query allows you to combine the result sets of two or more SQL SELECT statements. It removes duplicate rows between the various SELECT statements.

Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types.

QUESTION NO: 28

Which two statements are true regarding working with dates? (Choose two.)

A. The default internal storage of dates is in the numeric format

B. The RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required

C. The default internal storage of dates is in the character format

D. The RR date format automatically calculates the century from the SYSDATE function and does not allow the user to enter the century

Answer: A,B

Explanation:

Working with Dates

The Oracle Database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds.

The default display and input format for any date is DD-MON-RR.

RR Date Format

The RR date format is similar to the YY element, but you can use it to specify different centuries. Use the RR date format element instead of YY so that the century of the return value varies according to the specified two digit year and the last two digits of the current year. The table in the slide summarizes the behavior of the RR element.

Note the values shown in the last two rows of the above table. As we approach the middle of the century, then the RR behavior is probably not what you want.

This data is stored internally as follows:

CENTURY YEAR MONTH DAY HOUR MINUTE SECOND 19 87 06 17 17 10 43

QUESTION NO: 29

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?

A. The VALUES clause cannot be used in an INSERT with a subquery

B. The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table

C. The WHERE clause cannot be used in a sub query embedded in an INSERT statement

D. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match

Answer: A

Explanation:

Copying Rows from Another Table

Write your INSERT statement with a subquery:

Do not use the VALUES clause.

Match the number of columns in the INSERT clause to those in the subquery.

Inserts all the rows returned by the subquery in the table, sales_reps.

QUESTION NO: 30

View the Exhibit and examine the description for the CUSTOMERS table.

You want to update the CUST_CREDIT_LIMIT column to NULL for all the customers, where

CUST_INCOME_LEVEL has NULL in the CUSTOMERS table. Which SQL statement will accomplish the task?

A. UPDATE customersSET cust_credit_limit = NULLWHERE CUST_INCOME_LEVEL = NULL;

B. UPDATE customersSET cust_credit_limit = NULLWHERE cust_income_level IS NULL;

C. UPDATE customersSET cust_credit_limit = TO_NUMBER(NULL)WHERE cust_income_level = TO_NUMBER(NULL);

D. UPDATE customersSET cust_credit_limit = TO_NUMBER(' ',9999)WHERE cust_income_level IS NULL;

Answer: B

Explanation:

QUESTION NO: 31

Which two statements about sub queries are true? (Choose two.)

A. A sub query should retrieve only one row.

B. A sub query can retrieve zero or more rows.

C. A sub query can be used only in SQL query statements.

D. Sub queries CANNOT be nested by more than two levels.

E. A sub query CANNOT be used in an SQL query statement that uses group functions.

F. When a sub query is used with an inequality comparison operator in the outer SQL statement, the column list in the SELECT clause of the sub query should contain only one column.

Answer: B,F

Explanation:

Explanation: sub query can retrieve zero or more rows, sub query is used with an inequality comparison operator in the outer SQL statement, and the column list in the SELECT clause of the sub query should contain only one column.

Incorrect answer:

Asub query can retrieve zero or more rows

Csub query is not SQL query statement

Dsub query can be nested

Egroup function can be use with sub query

QUESTION NO: 32

View the Exhibit and examine the structure of the PROMOTIONS table.

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category.

Which query would give you the required output?

A. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions )ANDpromo_category = 'INTERNET';

B. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_dateFROM promotionsWHERE promo_category='INTERNET');

C. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = 'INTERNET');

D. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_category = 'INTERNET');

Answer: C

Explanation:

QUESTION NO: 33

Which are /SQL*Plus commands? (Choose all that apply.)

A. INSERT

B. UPDATE

C. SELECT

D. DESCRIBE

E. DELETE

F. RENAME

Answer: D

Explanation:

Describe is a valid iSQL*Plus/ SQL*Plus command.

INSERT, UPDATE & DELETE are SQL DML Statements. A SELECT is an ANSI Standard SQL Statement not an iSQL*Plus Statement.

RENAME is a DDL Statement.

QUESTION NO: 34

Which two statements are true regarding the COUNT function? (Choose two.)

A. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns

B. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column

C. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column

D. A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE clause

E. The COUNT function can be used only for CHAR, VARCHAR2 and NUMBER data types

Answer: A,C

Explanation:

Using the COUNT Function

The COUNT function has three formats:

COUNT(*)

COUNT(expr)

COUNT(DISTINCT expr)

COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns. If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of rows that satisfy the condition in the WHERE clause.

In contrast,

COUNT(expr) returns the number of non-null values that are in the column identified by expr.

COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the column identified by expr.

QUESTION NO: 35

Examine the description of the EMP_DETAILS table given below:

Exhibit:

Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table? (Choose two.)

A. An EMP_IMAGE column can be included in the GROUP BY clause

B. You cannot add a new column to the table with LONG as the data type

C. An EMP_IMAGE column cannot be included in the ORDER BY clause

D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column

Answer: B,C

Explanation:

LONG Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type they should be converted to CLOB. There can only be one LONG column in a table.

Guidelines

A LONG column is not copied when a table is created using a subquery.

A LONG column cannot be included in a GROUP BY or an ORDER BY clause.

Only one LONG column can be used per table.

No constraints can be defined on a LONG column.

You might want to use a CLOB column rather than a LONG column.

QUESTION NO: 36

Which CREATE TABLE statement is valid?

A. CREATE TABLE ord_details(ord_no NUMBER(2) PRIMARY KEY,item_no NUMBER(3) PRIMARY KEY,ord_date DATE NOT NULL);

B. CREATE TABLE ord_details(ord_no NUMBER(2) UNIQUE, NOT NULL,item_no NUMBER(3),ord_date DATE DEFAULT SYSDATE NOT NULL);

C. CREATE TABLE ord_details(ord_no NUMBER(2) ,item_no NUMBER(3),ord_date DATE DEFAULT NOT NULL,CONSTRAINT ord_uq UNIQUE (ord_no),CONSTRAINT ord_pk PRIMARY KEY (ord_no));

D. CREATE TABLE ord_details(ord_no NUMBER(2),item_no NUMBER(3),ord_date DATE DEFAULT SYSDATE NOT NULL,CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));

Answer: D

Explanation:

PRIMARY KEY Constraint

A PRIMARY KEY constraint creates a primary key for the table. Only one primary key can be created for each table. The PRIMARY KEY constraint is a column or a set of columns that uniquely identifies each row in a table. This constraint enforces the uniqueness of the column or column combination and ensures that no column that is part of the primary key can contain a null value.

Note: Because uniqueness is part of the primary key constraint definition, the Oracle server enforces the uniqueness by implicitly creating a unique index on the primary key column or columns.

QUESTION NO: 37

See the exhibit and examine the structure of the CUSTOMERS and GRADES tables:

You need to display names and grades of customers who have the highest credit limit.

Which two SQL statements would accomplish the task? (Choose two.)

A. SELECT custname, gradeFROM customers, gradesWHERE (SELECT MAX(cust_credit_limit)FROM customers) BETWEEN startval and endval;

B. SELECT custname, gradeFROM customers, gradesWHERE (SELECT MAX(cust_credit_limit)FROM customers) BETWEEN startval and endvalAND cust_credit_limit BETWEEN startval AND endval;

C. SELECT custname, gradeFROM customers, gradesWHERE cust_credit_limit = (SELECT MAX(cust_credit_limit)FROM customers)AND cust_credit_limit BETWEEN startval AND endval;

D. SELECT custname, gradeFROM customers , gradesWHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit)FROM customers)AND MAX(cust_credit_limit) BETWEEN startval AND endval;

Answer: B,C

Explanation:

QUESTION NO: 38

See 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?

A. SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT" FROM customers;

B. SELECT NVL(cust_credit_limit*.15,'Not Available') "NEW CREDIT" FROM customers;

C. SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT" FROM customers;

D. SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT" FROM customers;

Answer: D

Explanation:

NVL Function

Converts a null value to an actual value:

Data types that can be used are date, character, and number.

Data types must match:

– NVL(commission_pct,0)

– NVL(hire_date,'01-JAN-97')

– NVL(job_id,'No Job Yet')

QUESTION NO: 39

You need to calculate the number of days from 1st Jan 2007 till date:

Dates are stored in the default format of dd-mm-rr.

Which two SQL statements would give the required output? (Choose two.)

A. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;

B. SELECT TO_DATE(SYSDATE,'DD/MONTH/YYYY')-'01/JANUARY/2007' FROM DUAL;

C. SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL

D. SELECT SYSDATE - '01-JAN-2007' FROM DUAL

E. SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY')-'01-JAN-2007' FROM DUAL;

Answer: A,C

Explanation:

QUESTION NO: 40

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 parameter 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: A,D

Explanation:

Site Search:

Close

Close
Download Free Demo of VCE
Exam Simulator

Experience Avanset VCE Exam Simulator for yourself.


Simply submit your e-mail address below to get started with our interactive software demo of your free trial.


Enter Your Email Address

Free Demo Limits: In the demo version you will be able to access only first 5 questions from exam.