Answer: AExplanation:Schema Object Naming RulesEvery database object has a name. In a SQL statement, you represent the name of an object with a quoted identifier or a nonquoted identifier. A quoted identifier begins and ends with double quotation marks (“). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.A nonquoted identifier is not surrounded by any punctuation. The following list of rules applies to both quoted and nonquoted identifiers unless otherwise indicated:Names must be from 1 to 30 bytes long with these exceptions:Names of databases are limited to 8 bytes.Names of database links can be as long as 128 bytes. If an identifier includes multiple parts separated by periods, then each attribute can be up to 30 bytes long.Each period separator, as well as any surrounding double quotation marks, counts as one byte. For example, suppose you identify a column like this:“schema”.”table”.”column”Nonquoted identifiers cannot be Oracle Database reserved words (ANSWER D). Quoted identifiers can be reserved words, although this is not recommended. Depending on the Oracle product you plan to use to access a database object, names might be further restricted by other product-specific reserved words. The Oracle SQL language contains other words that have special meanings. These words include datatypes, schema names, function names, the dummy system table DUAL, and keywords (the uppercase words in SQL statements, such as DIMENSION, SEGMENT, ALLOCATE, DISABLE, and so forth). These words are not reserved. However, Oracle uses them internally in specific ways. Therefore, if you use these words as names for objects and object parts, then your SQL statements may be more difficult to read and may lead to unpredictable results.In particular, do not use words beginning with SYS_ as schema object names, and do not use the names of SQL built-in functions for the names of schema objects or user-defined functions.You should use ASCII characters in database names, global database names, and database link names, because ASCII characters provide optimal compatibility across different platforms and operating systems.Nonquoted identifiers must begin with an alphabetic character (ANSWER B-begins with 9) from your database character set. Quoted identifiers can begin with any character.Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and “at” signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers.Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0).Within a namespace, no two objects can have the same name. Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive. By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace:employees“employees”“Employees”“EMPLOYEES”Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace:employeesEMPLOYEES“EMPLOYEES”Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name.Procedures or functions contained in the same package can have the same name, if their arguments are not of the same number and datatypes. Creating multiple procedures or functions with the same name in the same package with different arguments is called overloading the procedure or function.

A. A table name can be of any length.B. A table can have any number of columns.C. A column that has a DEFAULT value cannot store null values.D. A table and a view can have the same name in the same schema.E. A table and a synonym can have the same name in the same schema.F. The same table name can be used in different schemas in the same database.

Answer: EFExplanation:SynonymsSynonyms are database objects that enable you to call a table by another name. You can create synonyms to give an alternative name to a table.

A. A foreign key cannot contain NULL values.B. A column with the UNIQUE constraint can contain NULL values.C. A constraint is enforced only for the INSERT operation on a table.D. A constraint can be disabled even if the constraint column contains data.E. All constraints can be defined at the column level as well as the table level.

Answer: BDExplanation:Including Constraints– Constraints enforce rules at the table level.– Constraints prevent the deletion of a table if there are dependencies.The following constraint types are valid:– NOT NULL– UNIQUE– PRIMARY KEY– FOREIGN KEY– CHECK

A. A foreign key cannot contain NULL values.B. The column with a UNIQUE constraint can store NULLS .C. A constraint is enforced only for an INSERT operation on a table.D. You can have more than one column in a table as part of a primary key.

Answer: BD

QUESTION 5Evaluate the following CREATE TABLE commands:The above command fails when executed. What could be the reason?

A. SYSDATE cannot be used with the CHECK constraint.B. The BETWEEN clause cannot be used for the CHECK constraint.C. The CHECK constraint cannot be placed on columns having the DATE data type.D. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

Answer: AExplanation:CHECK ConstraintThe 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 functionsQueries that refer to other values in other rowsA 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_minCHECK (salary > 0),

QUESTION 6Evaluate the following SQL commands:The command to create a table fails. Identify the reason for the SQL statement failure? (Choose all that apply.)

A. You cannot use SYSDATE in the condition of a CHECK constraint.B. You cannot use the BETWEEN clause in the condition of a CHECK constraint.C. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the FOREIGN KEY.

Answer: ACExplanation:CHECK ConstraintThe 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 rowsA 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_minCHECK (salary > 0),

QUESTION 7Examine the structure and data in the PRICE_LIST table:name Null Type—— ——— ——-PROD_ID NOT NULL NUMBER(3)PROD_PRICE VARCHAR2(10)PROD_ID PROD_PRICE———- ————100 $234.55101 $6,509.75102 $1,234You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.Which SQL statement would give the required result?

Answer: BExplanation:Use TO_NUMBER on the prod_price column to convert from char to number to be able to multiply it with 0.25. Then use the TO_CHAR function (with formatting’$99,999.00′) to convert the number back to char.Incorrect:Not C: Use the formatting’$99,999.00′ with the TO_CHAR function, not with the TO_NUMBER function.Note:– Using the TO_CHAR FunctionThe TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available. The syntax is as follows:TO_CHAR(number1, [format], [nls_parameter]),The number1 parameter is mandatory and must be a value that either is or can be implicitly converted into a number. The optional format parameter may be used to specify numeric formatting information like width, currency symbol, the position of a decimal point, and group (or thousands) separators and must be enclosed in single– Syntax of Explicit Data Type ConversionFunctionsTO_NUMBER(char1, [format mask], [nls_parameters]) = num1 TO_CHAR(num1, [format mask], [nls_parameters]) = char1 TO_DATE(char1, [format mask], [nls_parameters]) = date1 TO_CHAR(date1, [format mask], [nls_parameters]) = char1

QUESTION 8View the Exhibit and examine the structure of the PROMOTIONS table.Which two SQL statements would execute successfully? (Choose two.)

QUESTION 9View the Exhibit and examine the data in the PROMO_NAME and PROMO_END_DATE columns of the PROMOTIONS table, and the required output format.Which two queries give the correct result? (Choose two.)

QUESTION 10View the Exhibit and examine the structure of the CUSTOMERS table.Using the CUSTOMERS table, y ou 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?

Answer: DExplanation:NVL FunctionConverts 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’)