Database 23ai SQL Certified Associate Free Sample Questions

Use DDL to manage tables and their relationships · Use CREATE TABLE statements

1. A

   Create a standard SEQUENCE with CACHE 100 and use a BEFORE INSERT trigger

2. B

   Define the column as NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY (CACHE 20)

3. C

   Define the column as NUMBER DEFAULT SEQ_CAT.NEXTVAL

4. D

   Define the column as NUMBER GENERATED ALWAYS AS IDENTITY (NOCACHE ORDER)

Using Functions · Apply the NVL, NULLIF, and COALESCE functions to data

1. A

   NVL(TO_CHAR(COMMISSION_PCT), 'No Commission')

2. B

   COALESCE(COMMISSION_PCT, 'No Commission')

3. C

   NULLIF(COMMISSION_PCT, 'No Commission')

4. D

   NVL2(COMMISSION_PCT, 'No Commission', TO_CHAR(COMMISSION_PCT))

Retrieving Data using the SQL SELECT Statement and Restricting and Sorting Data · Demonstrate how to use rules of precedence for operators in an expression

SELECT employee_id, last_name
FROM employees
WHERE job_id = 'IT_PROG'
OR job_id = 'AD_PRES'
AND salary > 15000;

1. A

   The query will fail because parentheses are required when mixing AND and OR.

2. B

   It returns IT_PROG employees with salary > 15000 and all AD_PRES employees.

3. C

   It returns employees who are either IT_PROG or AD_PRES, provided they earn more than 15000.

4. D

   It returns all IT_PROG employees regardless of salary, plus AD_PRES employees who earn more than 15000.

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

1. A

   ALTER TABLE STAGING_LOGS DROP ALL ROWS;

2. B

   TRUNCATE TABLE STAGING_LOGS;

3. C

   DELETE FROM STAGING_LOGS;

4. D

   DROP TABLE STAGING_LOGS PURGE;

Using Functions · Use DATE functions

1. A

   ROUND(end_date - start_date) / 30

2. B

   TRUNC(MONTHS_BETWEEN(end_date, start_date))

3. C

   MONTHS_BETWEEN(end_date, start_date)

4. D

   ADD_MONTHS(start_date, end_date)

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

1. A

   When two tables are joined using a NATURAL JOIN but share no common column names.

2. B

   When the CROSS JOIN syntax is explicitly used.

3. C

   When a LEFT OUTER JOIN is used on tables with different row counts.

4. D

   When a join condition is omitted in a join between two tables.