Oracle Database SQL Certified Associate (1Z0-071) Exam Dumps
Oracle Database SQL Certified Associate (1Z0-071) Exam Dumps
Visit Official SkillCertPro Website :-
For a full set of 350+ questions. Go to
https://skillcertpro.com/product/oracle-database-sql-certified-associate-1z0-071-exam-questions/
SkillCertPro offers detailed explanations to each question which helps to understand the concepts better.
It is recommended to score above 85% in SkillCertPro exams before attempting a real exam.
SkillCertPro updates exam questions every 2 weeks.
You will get life time access and life time free updates
SkillCertPro assures 100% pass guarantee in first attempt.
Question 1:
View and examine the following available responses.
Identity three true statements about the CREATE TABLE command. (Choose three)
A. To create a table in another user's schema, you must have the CREATE ANY TABLE system privilege.
B. The owner of the table must have space quota available on the tablespace where the table is defined
C. It can include the CREATE..INDEX statement for creating an index to enforce the primary key constraint
D. The owner of the table must have the UNLIMITED TABLESPACE system privilege
E. It implicitly executes a commit
F. It implicitly rolls back any pending transactions
Answer: A, B, E
Explanation:
To create a relational table in your own schema, you must have the CREATE TABLE system privilege. To create a table in another user’s schema, you must have the CREATE ANY TABLE system privilege. Also, the owner of the schema to contain the table must have either space quota on the tablespace to contain the table or the UNLIMITED TABLESPACE system privilege.
The owner of the table DOES NOT HAVE TO have the UNLIMITED TABLESPACE system privilege, but if they do, this will override the quota requirement.
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7002.htm#SQLRF01402
Question 2:
View and examine the following scenario.
Required: Create a table for a banking application with the following attributes.
1. A column is to store the duration of a short term loan.
2. The data should be stored in a format supporting DATE arithmetic with DATE datatypes without using conversion.
3. The maximum loan period is 30 days.
4. Interest must be calculated based on the number of day the loan is outstanding.
Identity the data type that should be used.
A. TIMESTAMP
B. DATE
C. NUMBER
D. INTERVAL DAY TO SECOND
E. INTERVAL YEAR TO MONTH
Answer: D
Explanation:
The INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds.
The following shows the syntax of the INTERVAL DAY TO SECOND data type:
1 INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]
In this syntax:
– day_precision is the number of digits in the DAY field. It ranges from 0 to 9. By default, its value is set to 2.
– fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. It ranges from 0 through 9. If you omit the fractional_seconds_precision, it defaults to 6.
https://www.oracletutorial.com/oracle-basics/oracle-interval/
Requirements checklist:
? A column can be used to store the duration of a short term loan.
? Supports DATE arithmetic with DATE datatypes without using conversion.
? Able to use for 30 day loan periods.
? Allows interest calculation based on days the loan is outstanding.
The other options do not meet the requirements.
Question 3:
View and examine the following available responses.
Identify two statements which are true about Data Manipulation Language (DML) statements. (Choose two.)
A. An UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table.
B. An INSERT INTO... VALUES..... statement can add a single row based on multiple conditions on a table.
C. A DELETE FROM..... statement removes a row based on each condition on a table.
D. An INSERT INTO...VALUES.. statement can add multiple rows per execution to a table.
E. A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.
F. An UPDATE....SET.... statement can only modify multiple rows based on a single condition on a table.
Answer: A, E
Explanation:
Data Manipulation Language which deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc., and it is used to store, modify, retrieve, delete and update data in a database.
SELECT – retrieve data from a database
INSERT – insert data into a table
UPDATE – updates existing data within a table
DELETE – Delete all records from a database table
MERGE – UPSERT operation (insert or update)
CALL – call a PL/SQL or Java subprogram
EXPLAIN PLAN – interpretation of the data access path
LOCK TABLE – concurrency Control
http://www.techonthenet.com/sql/and_or.php
Question 4:
View and examine the following available responses.
Identity two true statements about the MERGE statement. (Select two)
A. It can update, insert, or delete rows conditionally in multiple tables.
B. It can combine rows from multiple tables conditionally to insert into a single table.
C. It can use views to produce source rows.
D. It can use subqueries to produce source rows.
E. It can update the same row of the target table multiple times.
F. It can merge rows only from tables.
Answer: C, D
Question 5:
View and examine the following available responses.
Identity two statements which are true about the SET VERIFY ON command? (Select two)
A. It is both an SQL Developer and SQL*Plus native command
B. It can be used only in SQL*Plus.
C. It displays values for variables used only in the WHERE clause of a query.
D. It displays values for variables created by the DEFINE command.
E. It displays values for variables prefixed with &&.
Answer: B, D
Explanation:
Set Verify is a SQL*Plus command – it has nothing to do with the database or SQL.
The ‘Verify’ command will only display the old and new if there is a substitution variable involved in the SQL statement.
Verification is done only on lines that are part of a SQL statement or a PL/SQL block. When substitution occurs in a SQL*Plus command, before and after images are never displayed. The following example shows what verification looks like:
SQL> DEFINE dummy_char = ‘X’
SQL> SELECT * FROM dual
2 WHERE dummy = ‘&&dummy_char’;
old 2: where dummy = ‘&&dummy_char’
new 2: where dummy = ‘X’
D
–
X
https://community.oracle.com/thread/332058
https://www.oreilly.com/library/view/oracle-sqlplus-the/0596007469/re106.html
For a full set of 350+ questions. Go to
https://skillcertpro.com/product/oracle-database-sql-certified-associate-1z0-071-exam-questions/
SkillCertPro offers detailed explanations to each question which helps to understand the concepts better.
It is recommended to score above 85% in SkillCertPro exams before attempting a real exam.
SkillCertPro updates exam questions every 2 weeks.
You will get life time access and life time free updates
SkillCertPro assures 100% pass guarantee in first attempt.
Question 6:
View and examine the following available responses.
Identify the two minimal requirements for a self-join.
A. The table used for the self-join must have two different alias names in the query.
B. There must be a condition on which the self-join is performed.
C. No other condition except the self-join may be specified.
D. Outer joins must not be used in the query.
E. Only equijoin conditions may be used in the query.
Answer: A, B
Explanation:
The self-join statement is necessary when two sets of data, within the same table, are compared.
A self join is a join in which a table is joined with itself (which is also called Unary relationships), especially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table itself means that each row of the table is combined with itself and with every other row of the table.
The self join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.
The syntax of the command for joining a table to itself is almost same as that for joining two different tables. To distinguish the column names from one another, aliases for the actual the table name are used, since both the tables have the same name. Table name aliases are defined in the FROM clause of the SELECT statement. See the syntax :
SELECT a.column_name, b.column_name…
FROM table1 a, table1 b
WHERE a.common_filed = b.common_field;
https://www.w3resource.com/sql/joins/perform-a-self-join.php
Question 7:
View and examine the structure of the ORDERS table.
Table: ORDERS
Given: The ORDERS table contains data and all orders have been assigned a customer ID.
Identity the statement which would add a NOT NULL constraint to the CUSTOMER_ID column.
A. ALTER TABLE orders ADD customer_id NUMBER(6) CONSTRAINT orders_cust_id_nn NOT NULL;
B. ALTER TABLE orders MODIFY customer_id NOT NULL;
C. ALTER TABLE orders MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
D. ALTER TABLE orders ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
Answer: B
Explanation:
Question 8:
View and examine the following scenario.
Given: The first DROP operation is performed on the PRODUCTS table using this command.
Exhibit: 1
Then a FLASHBACK operation is performed using this command.
Exhibit: 2
Identity the result of the FLASHBACK command
A. It recovers only the table structure.
B. It recovers the table structure, data and the indexes.
C. It is not possible to recover the table structure, data or the related indexes.
D. It recovers the table structure and data but not the related indexes.
Answer: C
Explanation:
Use the PURGE statement to remove a table or index from your recycle bin and release all of the space associated with the object, or to remove the entire recycle bin, or to remove part of all of a dropped tablespace from the recycle bin.
You cannot roll back a PURGE statement, nor can you recover an object after it is purged.
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9018.htm
Use the DROP TABLE statement to move a table or object table to the recycle bin or to remove the table and all its data from the database entirely.
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9003.htm
Use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system. Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9012.htm#SQLRF01802
Question 9:
View and examine the description of the BOOKS_TRANSACTIONS table and exhibit.
Table: BOOKS_TRANSACTIONS
Examine this partial SQL statement.
Exhibit: 1
Identity two WHERE conditions render the same result. (Choose two)
A. WHERE (borrowed_date = SYSDATE AND transaction_type = 'RM') OR member_id IN ('A101','A102'));
B. WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' OR member_id IN ('A101','A102'));
C. WHERE borrowed_date = SYSDATE OR (transaction_type = 'RM' AND (member_id = 'A101' OR member_id = 'A102'));
D. WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' AND member_id = 'A101' OR member_id = 'A102');
E. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN ('A101','A102');
Answer: A, E
Explanation:
These two render the same outcome.
The others do not render the same outcome because the placement of the parentheses changes the breakdown of the query.
Question 10:
View and examine the following available responses.
Identify three true statements regarding the SQL WHERE and HAVING clauses. (Choose three)
A. The WHERE clause is used to exclude rows before grouping data.
B. The HAVING clause conditions can use aliases for the columns.
C. The HAVING clause conditions can have aggregating functions.
D. The HAVING clause is used to exclude one or more aggregated results after grouping data.
E. The WHERE and HAVING clauses cannot be used together in a SQL statement
Answer: A, C, D
Explanation:
WHERE clause executes just after FROM clause.
WHERE comes before the group by clause
WHERE cannot use group functions
WHERE clause restricts few rows from taking part in any other operation like joins, functions etc…
HAVING comes after the group by clause
HAVING can use the aggregate / group functions like MAX , MIN , AVG, SUM, etc.
HAVING clause just hides few rows from your eyes from the result set produced by the query.
HAVING is the last clause to be executed.
https://community.oracle.com/thread/492935
Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
https://docs.oracle.com/database/121/SQLRF/functions003.htm#SQLRF20035
For a full set of 350+ questions. Go to
https://skillcertpro.com/product/oracle-database-sql-certified-associate-1z0-071-exam-questions/
SkillCertPro offers detailed explanations to each question which helps to understand the concepts better.
It is recommended to score above 85% in SkillCertPro exams before attempting a real exam.
SkillCertPro updates exam questions every 2 weeks.
You will get life time access and life time free updates
SkillCertPro assures 100% pass guarantee in first attempt.