Download Oracle Database 11g SQL Fundamentals I.1z0-051.PracticeDumps.2018-03-22.152q.vcex

Vendor: Oracle
Exam Code: 1z0-051
Exam Name: Oracle Database 11g SQL Fundamentals I
Date: Mar 22, 2018
File Size: 9 MB
Download Exam

How to open VCEX files?

Files with VCEX extension can be opened by ProfExam Simulator.

Download
ProfExam Simulator

Purchase
Coupon: TAURUSSIM_20OFF

Discount: 20%

ProfExam Simulator ProfExam Simulator ProfExam Simulator ProfExam Simulator ProfExam Simulator

Demo Questions

Question 1
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?
  1. The VALUES clause cannot be used in an INSERT with a subquery
  2. The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table
  3. The WHERE clause cannot be used in a sub query embedded in an INSERT statement
  4. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match
Correct 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.
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 2
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?
  1. UPDATE customersSET cust_credit_limit = NULLWHERE CUST_INCOME_LEVEL = NULL;
  2. UPDATE customersSET cust_credit_limit = NULLWHERE cust_income_level IS NULL;
  3. UPDATE customersSET cust_credit_limit = TO_NUMBER(NULL)WHERE cust_income_level = TO_NUMBER(NULL);
  4. UPDATE customersSET cust_credit_limit = TO_NUMBER(' ',9999)WHERE cust_income_level IS NULL;
Correct answer: B
Question 3
Which two statements about sub queries are true? (Choose two.)
  1. A sub query should retrieve only one row.
  2. A sub query can retrieve zero or more rows.
  3. A sub query can be used only in SQL query statements.
  4. Sub queries CANNOT be nested by more than two levels.
  5. A sub query CANNOT be used in an SQL query statement that uses group functions.
  6. 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.
Correct answer: BF
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
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 4
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?
  1. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions )ANDpromo_category = 'INTERNET';
  2. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_dateFROM promotionsWHERE promo_category='INTERNET');
  3. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = 'INTERNET');
  4. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_category = 'INTERNET');
Correct answer: C
Question 5
Which are /SQL*Plus commands? (Choose all that apply.)
  1. INSERT
  2. UPDATE
  3. SELECT
  4. DESCRIBE
  5. DELETE
  6. RENAME
Correct 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.
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 6
Which two statements are true regarding the COUNT function? (Choose two.)
  1. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns
  2. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column
  3. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column
  4. A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE clause
  5. The COUNT function can be used only for CHAR, VARCHAR2 and NUMBER data types
Correct answer: AC
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.
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 7
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.)
  1. An EMP_IMAGE column can be included in the GROUP BY clause
  2. You cannot add a new column to the table with LONG as the data type
  3. An EMP_IMAGE column cannot be included in the ORDER BY clause
  4. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column
Correct answer: BC
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.
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 8
Which CREATE TABLE statement is valid?
  1. CREATE TABLE ord_details(ord_no NUMBER(2) PRIMARY KEY,item_no NUMBER(3) PRIMARY KEY,ord_date DATE NOT NULL);
  2. CREATE TABLE ord_details(ord_no NUMBER(2) UNIQUE, NOT NULL,item_no NUMBER(3),ord_date DATE DEFAULT SYSDATE NOT NULL);
  3. 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));
  4. 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));
Correct 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.
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 9
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.)
  1. SELECT custname, gradeFROM customers, gradesWHERE (SELECT MAX(cust_credit_limit)FROM customers) BETWEEN startval and endval;
  2. SELECT custname, gradeFROM customers, gradesWHERE (SELECT MAX(cust_credit_limit)FROM customers) BETWEEN startval and endvalAND cust_credit_limit BETWEEN startval AND endval;
  3. SELECT custname, gradeFROM customers, gradesWHERE cust_credit_limit = (SELECT MAX(cust_credit_limit)FROM customers)AND cust_credit_limit BETWEEN startval AND endval;
  4. 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;
Correct answer: BC
Question 10
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?
  1. SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT" FROM customers;
  2. SELECT NVL(cust_credit_limit*.15,'Not Available') "NEW CREDIT" FROM customers;
  3. SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT" FROM customers;
  4. SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT" FROM customers;
Correct 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')
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')
CONNECT US
HOW TO OPEN VCE FILES

Use VCE Exam Simulator to open VCE files
Avanaset

HOW TO OPEN VCEX FILES

Use ProfExam Simulator to open VCEX files
ProfExam Screen

ProfExam
ProfExam at a 20% markdown

You have the opportunity to purchase ProfExam at a 20% reduced price

Get Now!