1Z0-006 Basic SQL Statements Questions and Answers

Question 1

A user wants to add a new employee record to the 'employees' table, which has columns for 'employee_id', 'last_name', and 'hire_date'. Which of the following SQL statements is the correct syntax for this operation?

Accepted Answer

INSERT INTO employees (employee_id, last_name, hire_date) VALUES (101, 'Smith', '17-OCT-23');

Answer

The correct syntax for adding a new row of data to a table is the `INSERT INTO` statement. It specifies the table, optionally the columns, and then the `VALUES` clause provides the data for the new row.

Question 2

Which of the following statements would permanently remove all rows from a table named 'products' but leave the table structure intact?

Accepted Answer

DELETE FROM products;

Answer

The `DELETE FROM` statement is a DML command used to remove rows from a table. Without a `WHERE` clause, it will remove all rows. `DROP TABLE` removes the entire table structure, and `TRUNCATE TABLE` is a DDL command that also removes all rows but is generally faster and cannot be rolled back in the same way as `DELETE`.

Question 3

A database administrator needs to change the job title for an employee with the ID 7839 to 'SENIOR MANAGER'. The table is named 'EMP' and the columns are 'JOB' and 'EMPNO'. Which SQL statement correctly performs this update?

Accepted Answer

UPDATE emp SET job = 'SENIOR MANAGER' WHERE empno = 7839;

Answer

The `UPDATE` statement is used to modify existing records. The syntax is `UPDATE table_name SET column_name = new_value WHERE condition`. The `WHERE` clause is crucial to specify which row(s) to update.

Question 4

A user needs to retrieve the current system date and time from the database without querying any specific user-created table. Which query should be used in an Oracle database to accomplish this?

Accepted Answer

SELECT SYSDATE FROM DUAL;

Answer

In Oracle, `DUAL` is a special one-row, one-column table owned by SYS that is accessible to all users. It is used for selecting pseudocolumns like `SYSDATE` or for performing calculations when a `FROM` clause is syntactically required but no actual table data is needed.

Question 5

Which statement correctly retrieves the `employee_id` and `last_name` for all employees in `department_id` 50 from the `employees` table?

Accepted Answer

SELECT employee_id, last_name FROM employees WHERE department_id = 50;

Answer

The basic structure of a retrieval query is `SELECT [columns] FROM [table] WHERE [condition]`. This statement correctly specifies the columns to be shown, the table to query, and uses the `WHERE` clause to filter the results for the specified department.

(1Z0-006) Oracle Database Foundations Associate Practice Test

(1Z0-006) Oracle Database Foundations Associate Practice Test

1Z0-006 Basic SQL Statements Questions and Answers