1Z0-006 Basic SQL Statements Questions and Answers — 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?
- ADD INTO employees VALUES (101, 'Smith', '17-OCT-23');
- INSERT INTO employees (employee_id, last_name, hire_date) VALUES (101, 'Smith', '17-OCT-23'); (Correct answer)
- UPDATE employees SET employee_id=101, last_name='Smith', hire_date='17-OCT-23';
- CREATE NEW ROW IN employees VALUES (101, 'Smith', '17-OCT-23');
Correct answer: INSERT INTO employees (employee_id, last_name, hire_date) VALUES (101, 'Smith', '17-OCT-23');
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?
- DROP TABLE products;
- DELETE FROM products; (Correct answer)
- TRUNCATE products;
- REMOVE ALL FROM products;
Correct answer: DELETE FROM products;
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?
- MODIFY emp SET job = 'SENIOR MANAGER' WHERE empno = 7839;
- CHANGE emp.job TO 'SENIOR MANAGER' WHERE emp.empno = 7839;
- UPDATE emp SET job = 'SENIOR MANAGER' WHERE empno = 7839; (Correct answer)
- UPDATE emp WHERE empno = 7839 SET job = 'SENIOR MANAGER';
Correct answer: UPDATE emp SET job = 'SENIOR MANAGER' WHERE empno = 7839;
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?
- SELECT SYSDATE FROM SYSTEM;
- GET CURRENT_TIMESTAMP;
- SELECT SYSDATE FROM DUAL; (Correct answer)
- SELECT SYSDATE;
Correct answer: SELECT SYSDATE FROM DUAL;
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?
- SELECT employee_id, last_name FROM employees WHERE department_id = 50; (Correct answer)
- RETRIEVE employee_id, last_name FROM employees WHERE department_id = 50;
- SELECT employee_id, last_name WHERE department_id = 50 FROM employees;
- GET employee_id, last_name FROM employees FOR department_id = 50;
Correct answer: SELECT employee_id, last_name FROM employees WHERE department_id = 50;
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.
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?
- INSERT ALL sales_reps FROM employees WHERE department_id = 90;
- INSERT INTO sales_reps SELECT * FROM employees WHERE department_id = 90; (Correct answer)
- CREATE TABLE sales_reps WITH SELECT * FROM employees WHERE department_id = 90;
- COPY FROM employees TO sales_reps WHERE department_id = 90;
Correct answer: INSERT INTO sales_reps SELECT * FROM employees WHERE department_id = 90;
The `INSERT INTO ... SELECT` statement is used to copy data from one table and insert it into another. The `SELECT` statement acts as a subquery to provide the rows of data that will be inserted into the target table (`sales_reps`).
1Z0-006 Basic SQL Statements Questions and Answers
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?