Basic SQL Statements Flashcards

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?

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.

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

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`.

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?

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.

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?

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.

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

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.

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?

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`).