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

ADD INTO employees VALUES (101, 'Smith', '17-OCT-23');

Answer

UPDATE employees SET employee_id=101, last_name='Smith', hire_date='17-OCT-23';

Answer

CREATE NEW ROW IN employees VALUES (101, 'Smith', '17-OCT-23');

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

DROP TABLE products;

Answer

TRUNCATE products;

Answer

REMOVE ALL FROM products;

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

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

Answer

CHANGE emp.job TO 'SENIOR MANAGER' WHERE emp.empno = 7839;

Answer

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

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

SELECT SYSDATE FROM SYSTEM;

Answer

GET CURRENT_TIMESTAMP;

Answer

SELECT SYSDATE;

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

RETRIEVE employee_id, last_name FROM employees WHERE department_id = 50;

Answer

SELECT employee_id, last_name WHERE department_id = 50 FROM employees;

Answer

GET employee_id, last_name FROM employees FOR department_id = 50;

Question 6

A developer wants to copy all employees from the 'employees' table who are in 'department_id' 90 into a new table called 'sales_reps'. Which statement combines the creation of a new record and a subquery to achieve this?

Accepted Answer

INSERT INTO sales_reps SELECT * FROM employees WHERE department_id = 90;

Answer

INSERT ALL sales_reps FROM employees WHERE department_id = 90;

Answer

CREATE TABLE sales_reps WITH SELECT * FROM employees WHERE department_id = 90;

Answer

COPY FROM employees TO sales_reps WHERE department_id = 90;