Best Quality Oracle 1z0-071 Exam Questions BraindumpsVCE Realistic Practice Exams [2021]
Critical Information To Oracle Database SQL Pass the First Time
NEW QUESTION 14
View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.
The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table.
Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true regarding the above command?
- A. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table.
- B. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
- C. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
- D. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
Answer: D
NEW QUESTION 15
Which three statements are true? (Choose three.)
- A. The USER_CONS_COLUMNS view should be queried to find the names of columns to which constraints apply.
- B. Both USER_OBJECTS and CAT views provide the same information about all objects that are owned by the user.
- C. The usernames of all users including database administrators are stored in the data dictionary.
- D. The data dictionary is created and maintained by the database administrator.
- E. Data dictionary views consist of joins of dictionary base tables and user-defined tables.
- F. Views with the same name but different prefixes, such as DBA, ALL and USER, reference the same base tables from the data dictionary.
Answer: A,C,F
Explanation:
https://docs.oracle.com/cd/B10501_01/server.920/a96524/c05dicti.htm
NEW QUESTION 16
Examine the data in the EMPLOYEES table:
Which statement will compute the total annual compensation for each employee?
- A. SELECT last name,
(monthly salary*12) + (monthly_commission_pct * 12) AS
annual comp
FROM employees
; - B. SELECT last name, (monthly_ salary *12) + (monthly_ salary * 12 * NVL
(monthly commission pct,0) ) As annual _ comp
FROM employees; - C. SELECT last_ name, monthly_ salary*12) + (monthly_ salary * 12 * Monthly commission _Pct) AS
annual_ comp
FROM employees; - D. SELECT last_ name (monthly_ salary+ monthly_ commission _ pct) *12 AS annual_
FROM employees ;
Answer: B
NEW QUESTION 17
Examine the description of the sales table.
The sales table has 55,000 rows.
Examine this statements:
Which two statements are true?
- A. SALES1 has PRIMARY KEY and UNIQUE constraints on any selected columns which had those constraints in the SALES table.
- B. SALES1 created with 1 row.
- C. SALES1 has NOT NULL constraints on any I selected columns which had those constraints I in the SALES table.
- D. SALES1 created with no rows.
- E. SALES1 created with 55, 000 rows
Answer: C,E
NEW QUESTION 18
Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data?
- A. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted
- B. A table can have only one primary key but multiple foreign keys.
- C. Primary key and foreign key constraints can be defined at both the column and table level.
- D. Only the primary key can be defined at the column and table level.
- E. It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted.
- F. A table can have only one primary key and one foreign key.
- G. The foreign key columns and parent table primary key columns must have the same names.
Answer: A,B,C,E
NEW QUESTION 19
Which three statements are true regarding the data types?
- A. Only one LONGcolumn can be used per table.
- B. The value for a CHARdata type column is blank-padded to the maximum defined column width.
- C. A TIMESTAMPdata type column stores only time values with fractional seconds.
- D. The BLOBdata type column is used to store binary data in an operating system file.
- E. The minimum column width that can be specified for a VARCHAR2data type column is one.
Answer: A,B,E
NEW QUESTION 20
Examine this business rule:
Each student can work on multiple projects and earth project can have multiple students.
You must decide an Entity Relationship (ER) model for optional data storage and allow generating reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK Which two statements are true?
- A. The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships.
- B. The ER must have a 1-to-many relationship between the STUDENTS and PROJECTS entities.
- C. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.
- D. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.
- E. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.
Answer: A,C
NEW QUESTION 21
Examine the description of the EMPLOYEES table:
Which query requires explicit data type conversion?
- A. SELECT join_date || ' ' || salary FROM employees;
- B. SELECT join_date + '20' FROM employees;
- C. SELECT salary + '120.50' FROM employees;
- D. SELECT join_date FROM employees WHERE join_date > '10-02-2018';
- E. SELECT SUBSTR(join_date, 1, 2) - 10 FROM employees;
Answer: D
NEW QUESTION 22
Which two statements are true about views?
- A. The with check clause prevents certain rows from being displayed when querying the view.
- B. The with check clause prevents certain rows from being updated or inserted in the underlying table through the view.
- C. Views can be indexed.
- D. Tables in the defining query of a view must always exist in order to create the view.
- E. Views can be updated without the need to re-grant privileges on the view.
Answer: A,B
NEW QUESTION 23
Which two are the minimal requirements for a self-join? (Choose two.)
- A. Only equijoin conditions may be used in the query.
- B. The table used for the self-join must have two different alias names in the query.
- C. Outer joins must not be used in the query.
- D. No other condition except the self-join may be specified.
- E. There must be a condition on which the self-join is performed.
Answer: B,E
NEW QUESTION 24
Which statement adds a column called SALARYto the EMPLOYEEStable having 100 rows, which cannot contain null?
ALTER TABLE EMPLOYEES
- A. ADD SALARY NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL;
ALTER TABLE EMPLOYEES - B. ADD SALARY NUMBER(8,2) DEFAULT NOT NULL;
ALTER TABLE EMPLOYEES - C. ADD SALARY NUMBER(8,2) DEFAULT 0 NOT NULL;
ALTER TABLE EMPLOYEES - D. ADD SALARY NUMBER(8,2) NOT NULL;
Answer: B
NEW QUESTION 25
View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose two.)
In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You must list each product ID and the number of times it has been sold.
Examine this query which is missing a JOIN operator:
Which two JOIN operations can be used to obtain the required output? (Choose two.)
- A. LEFT OUTER JOIN
- B. RIGHT OUTER JOIN
- C. JOIN
- D. FULL OUTER JOIN
Answer: A,D
NEW QUESTION 26
Which two statements are true about Data Manipulation Language (DML) statements? (Choose two.)
- A. A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.
- B. An UPDATE....SET.... statement can modify multiple rows based on only a single condition on a table.
- C. An INSERT INTO... VALUES..... statement can add a single row based on multiple conditions on a table.
- D. An UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table.
- E. An INSERT INTO...VALUES.. statement can add multiple rows per execution to a table.
- F. A DELETE FROM..... statement can remove rows based on only a single condition on a table.
Answer: A,D
Explanation:
Explanation
References:
http://www.techonthenet.com/sql/and_or.php
NEW QUESTION 27
View the Exhibit and examine the structure in the EMPLOYEES tables.
Evaluate the following SQL statement:
SELECT employee_id, department_id
FROM employees
WHERE department_id= 50 ORDER BY department_id
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id=90
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id=10;
What would be the outcome of the above SQL statement?
- A. The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.
- B. The statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.
- C. The statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.
- D. The statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.
Answer: A
NEW QUESTION 28
Which two statements are true regarding savepoints? (Choose two.)
- A. Savepoints are effective for both COMMITand ROLLBACK.
- B. Savepoints are effective only for COMMIT.
- C. Savepoints may be used to ROLLBACK.
- D. Savepoints can be used for only DML statements.
- E. Savepoints can be used for both DML and DDL statements.
Answer: C,D
Explanation:
Explanation/Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10001.htm#SQLRF01701
NEW QUESTION 29
Evaluate the following CREATE TABLE command:
Which statement is true regarding the above SQL statement?
- A. It would give an error because the USING INDEX clause cannot be used on a composite primary.
- B. It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID_PK would be created.
- C. It would execute successfully and only ORD_ITM_IDX index would be created.
- D. It would give an error because the USING INDEX clause is not permitted in the CREATE TABLE command.
Answer: C
NEW QUESTION 30
Which two statements are true regarding roles? (Choose two.)
- A. Roles are named groups of related privileges that can be granted to users or other roles.
- B. The REVOKE command can be used to remove privileges but not roles from other users.
- C. A role can be granted to PUBLIC.
- D. A role can be granted to itself.
- E. A user can be granted only one role at any point of time.
Answer: A,C
Explanation:
References:
http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authorization.htm#autoId28
NEW QUESTION 31
......
1z0-071 EXAM DUMPS WITH GUARANTEED SUCCESS: https://www.braindumpsvce.com/1z0-071_exam-dumps-torrent.html
Best Quality Oracle 1z0-071 Exam Questions: https://drive.google.com/open?id=1GRcoJ3WLRVVSbhBrWMAsZyw_wHo5gpXD