1Z0-171 Sample Questions

1Z0-171 Sample Questions & Answers

Free Database 23ai SQL Certified Associate practice questions with worked answers and explanations. See how the ExamJungle simulator prepares you — then jump into the full test.

Launch the full 1Z0-171 simulator →

Showing 6 of 12 free samples.

  1. Question 1Beginner

    Use DDL to manage tables and their relationships · Explain the TRUNCATE TABLE statement

    You are performing a data cleanup operation. You need to remove all rows from the STAGING_LOGS table to free up storage space immediately. The operation must not generate undo logs for individual rows and cannot be rolled back. Which statement should you use?

    Show answer & explanation

    Correct answer: B

    TRUNCATE TABLE is a DDL statement that removes all rows, resets the high-water mark, releases storage, generates minimal redo, and cannot be rolled back. DELETE is DML and generates undo.

  2. Question 2Intermediate

    Using Functions · Use DATE functions

    A developer needs to calculate the number of months between two dates: start_date and end_date. The result should be precise, including the fractional part of the month if the days differ. Which function should be used?

    Show answer & explanation

    Correct answer: C

    MONTHS_BETWEEN returns the number of months between two dates. If the days of the month are different, it calculates the fractional portion of the month based on a 31-day month.

  3. Question 3IntermediateSelect 2

    Displaying Data from Multiple Tables · Show understanding of using CARTESIAN PRODUCTS

    Which of the following scenarios describes the creation of a Cartesian Product (Cross Join) in a SQL query? (Select TWO)

    Show answer & explanation

    Correct answers: B, D

    Explicitly using the CROSS JOIN keywords instructs the database to produce a Cartesian product.

    A Cartesian product occurs when every row in the first table is joined to every row in the second table. This happens if the join condition is invalid or omitted entirely, or explicitly requested via CROSS JOIN.

  4. Question 4Intermediate

    Relational Database Concepts · Explain the theoretical and physical aspects of a relational database

    You are designing an Entity Relationship Diagram (ERD) for a university system. You have an entity Student and an entity Course. A student can enroll in multiple courses, and a course can have multiple students. To resolve this many-to-many relationship in a physical relational database design, what action must you take?

    Show answer & explanation

    Correct answer: C

    In a physical relational model, many-to-many relationships cannot be directly implemented. They are resolved by creating an intersection table (e.g., Enrollment) that holds Foreign Keys referencing the Primary Keys of both parent entities.

  5. Question 5Beginner

    Retrieving Data using the SQL SELECT Statement and Restricting and Sorting Data · Describe using the DEFINE and VERIFY commands

    Which SQL statement is used to define a substitution variable that remains available for the duration of the session or until explicitly undefined?

    Show answer & explanation

    Correct answer: B

    The DEFINE command creates a user variable that persists for the session. It can be referenced using the & or && prefix. ACCEPT is for user input; PROMPT is for display.

  6. Question 6Advanced

    Managing Tables using DML statements · Apply managing database transactions and controlling transactions

    Case Study: Global Logistics Corp

    Global Logistics Corp has a large database with a table named SHIPMENTS containing millions of rows. The table has columns SHIPMENT_ID, ORIGIN, DESTINATION, SHIP_DATE, and WEIGHT.

    Management wants to archive old shipments (where SHIP_DATE is older than 5 years) to a new table SHIPMENTS_ARCHIVE and then remove them from the main table. However, due to regulatory compliance, the data movement must be atomic—either the archive and delete both succeed, or neither happens. Additionally, the SHIPMENTS_ARCHIVE table does not exist yet.

    Which sequence of steps is the most efficient and transactionally safe approach?

    Show answer & explanation

    Correct answer: C

    CTAS is a DDL operation and performs an implicit commit. This ensures the archive table is created and populated. Then, the DELETE removes records from the source. While CTAS commits immediately (breaking full atomicity of the whole workflow strictly speaking), in Oracle, DDL implicitly commits. To achieve strict atomicity for the data movement if the table already existed, one would use INSERT then DELETE. Given the table does not exist, CTAS is the standard efficient path, followed by DELETE.

Ready for the real thing?

The full 1Z0-171 simulator has every exam-style question, timed mode, and instant scoring.

Go to the 1Z0-171 simulator →