Test Your Ddl And Dml Knowledge

1. From SQL*Plus, you issue this SELECT statement: SELECT* FROM order; You use this statement to retrieve data from a data table for __________.

Updating

Viewing

Deleting

Inserting

Truncating

The given SELECT statement is used to retrieve data from a data table. The "*" symbol in the statement indicates that all columns of the table will be selected. Therefore, the purpose of this statement is to view or display the data stored in the "order" table.

Explanation

The given SELECT statement is used to retrieve data from a data table. The "*" symbol in the statement indicates that all columns of the table will be selected. Therefore, the purpose of this statement is to view or display the data stored in the "order" table.

2. What does the TRUNCATE statement do?

Removes the table

Removes all rows from a table

Shortens the table to 10 rows

Removes all columns from a table

Removes foreign keys from a table

The TRUNCATE statement is used to remove all rows from a table. Unlike the DELETE statement, which removes specific rows based on a condition, TRUNCATE removes all the data from the table without logging individual row deletions. This operation is faster and more efficient than using DELETE to remove all rows, especially for large tables. It is commonly used when the intention is to clear the table completely and start fresh.

Explanation

The TRUNCATE statement is used to remove all rows from a table. Unlike the DELETE statement, which removes specific rows based on a condition, TRUNCATE removes all the data from the table without logging individual row deletions. This operation is faster and more efficient than using DELETE to remove all rows, especially for large tables. It is commonly used when the intention is to clear the table completely and start fresh.

3. A data manipulation language statement

Completes a transaction on a table

Modifies the structure and data in a table

Modifies the data but not the structure of a table

Modifies the structure but not the data of a table

The correct answer is "modifies the data but not the structure of a table." This means that a data manipulation language statement is used to update, delete, or insert data into a table without making any changes to the table's structure. It allows for the manipulation and modification of existing data within a table, but does not affect the table's overall structure or schema.

Explanation

The correct answer is "modifies the data but not the structure of a table." This means that a data manipulation language statement is used to update, delete, or insert data into a table without making any changes to the table's structure. It allows for the manipulation and modification of existing data within a table, but does not affect the table's overall structure or schema.

4. You added a PHONE_NUMBER column of NUMBER data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 100 employees. Now, you want to enter the phone numbers of each of the 100 employees into the table. Some of the employees may not have a phone number available. Which data manipulation operation do you perform?

MERGE

INSERT

UPDATE

ADD

You cannot enter the phone numbers for the existing employee records.

Since the question states that the EMPLOYEES table already contains records of 100 employees and you want to enter the phone numbers of each employee, the appropriate data manipulation operation to perform in this case would be an UPDATE. This operation allows you to modify existing records in a table, in this case, adding the phone numbers for each employee.

Explanation

Since the question states that the EMPLOYEES table already contains records of 100 employees and you want to enter the phone numbers of each employee, the appropriate data manipulation operation to perform in this case would be an UPDATE. This operation allows you to modify existing records in a table, in this case, adding the phone numbers for each employee.

5. Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2 (25) LAST_NAME VARCHAR2 (25) HIRE_DATE DATE Which UPDATE statement is valid?

UPDATE employees SET first_name = 'John' SET last_name = 'Smith' WHERE employee_id = 180;

UPDATE employees SET first_name = 'John',SET last_name = 'Smoth' WHERE employee_id = 180;

UPDATE employee SET first_name = 'John' AND last_name = 'Smith' WHERE employee_id = 180;

UPDATE employee SET first_name = 'John', last_name = 'Smith' WHERE employee_id = 180;

This statement correctly updates the first_name and last_name columns for the employee with employee_id = 180 in the EMPLOYEES table. The syntax follows the pattern of setting each column's value using "column_name = value", separated by commas.

Explanation

This statement correctly updates the first_name and last_name columns for the employee with employee_id = 180 in the EMPLOYEES table. The syntax follows the pattern of setting each column's value using "column_name = value", separated by commas.

6. You own a table called EMPLOYEES with this table structure: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE What happens when you execute this DELETE statement?
DELETE employees;

You get an error because of a primary key violation

The data and structure of the EMPLOYEES table are deleted.

The data in the EMPLOYEES table is deleted but not the structure

You get an error because the statement is not syntactically correct

When you execute the DELETE statement "DELETE employees;", it deletes the data in the EMPLOYEES table but does not delete the table structure. This means that all the rows in the table will be removed, but the table itself will remain with its columns and constraints intact. There will be no error due to primary key violation as the statement does not specify any conditions for deletion.

Explanation

When you execute the DELETE statement "DELETE employees;", it deletes the data in the EMPLOYEES table but does not delete the table structure. This means that all the rows in the table will be removed, but the table itself will remain with its columns and constraints intact. There will be no error due to primary key violation as the statement does not specify any conditions for deletion.

7. On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID. The JOB_ID column is a NOT NULL column. Evaluate this DELETE statement: DELETE employee_id, salary, job_id FROM employees WHERE dept_id = 90; Why does the DELETE statement fail when you execute it?

There is no row with dept_id 90 in the EMPLOYEES table.

You cannot delete the JOB_ID column because it is a NOT NULL column

You cannot specify column names in the DELETE clause of the DELETE statement.

You cannot delete the EMPLOYEE_ID column because it is the primary key of the table.

The DELETE statement fails because you cannot specify column names in the DELETE clause of the DELETE statement. In a DELETE statement, you can only specify the table from which you want to delete rows and the conditions for deleting those rows. Column names cannot be included in the DELETE clause.

Explanation

The DELETE statement fails because you cannot specify column names in the DELETE clause of the DELETE statement. In a DELETE statement, you can only specify the table from which you want to delete rows and the conditions for deleting those rows. Column names cannot be included in the DELETE clause.

8. The EMPLOYEES table has these columns: LAST NAME VARCHAR2(35) SALARY NUMBER(8,2) HIRE_DATE DATE Management wants to add a default value to the SALARY column. You plan to alter the table by using this SQL statement: ALTER TABLE EMPLOYEES MODIFY ( SALARY DEFAULT 5000); What is true about your ALTER statement?

Column definitions cannot be altered to add DEFAULT values

A change to the DEFAULT value affects only subsequent insertions to the table.

Column definitions cannot be altered at add DEFAULT values for columns with a NUMBER data type.

All the rows that have a NULL value for the SALARY column will be updated with the value 5000.

The statement is true because when a default value is added to a column using the ALTER TABLE statement, it only affects subsequent insertions to the table. It does not automatically update the existing rows with a NULL value for the SALARY column to the new default value. Therefore, option B, C, and D are incorrect.

Explanation

The statement is true because when a default value is added to a column using the ALTER TABLE statement, it only affects subsequent insertions to the table. It does not automatically update the existing rows with a NULL value for the SALARY column to the new default value. Therefore, option B, C, and D are incorrect.

9. Which of these commands removes the largest number of rows from the table? Assume that the table and all columns exist and that there are no constraint issues involved

DELETE FROM MOVIE;

*DELETE ALL FROM MOVIE;

TRUNCATE MOVIE;

TRUNCATE TABLE MOVIE;

All of the above remove the same number of rows.

The DELETE FROM MOVIE; and TRUNCATE TABLE MOVIE; commands both remove all rows from the MOVIE table. The DELETE FROM MOVIE; command deletes rows one by one, while the TRUNCATE TABLE MOVIE; command removes all rows at once. Therefore, both commands remove the same number of rows from the table.

Explanation

The DELETE FROM MOVIE; and TRUNCATE TABLE MOVIE; commands both remove all rows from the MOVIE table. The DELETE FROM MOVIE; command deletes rows one by one, while the TRUNCATE TABLE MOVIE; command removes all rows at once. Therefore, both commands remove the same number of rows from the table.

Submit

10. Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) Which three statements inserts a row into the table?

INSERT INTO employees VALUES (NULL, 'JOHN','Smith');

INSERT INTO employees( first_name, last_name) VALUES ('JOHN','Smith');

INSERT INTO employees VALUES ('1000','JOHN','NULL');

INSERT INTO employees(first_name,last_name, employee_id) VALUES ('1000, 'john','Smith');

INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, 'john',");

The first statement inserts a row into the table by providing values for all three columns, including a NULL value for the EMPLOYEE_ID column.

The second statement inserts a row into the table by only providing values for the FIRST_NAME and LAST_NAME columns, leaving the EMPLOYEE_ID column to be automatically generated by the database (assuming it has auto-increment functionality).

The fourth statement inserts a row into the table by providing values for all three columns in the correct order.

The fifth statement is incorrect because it has a syntax error. The last value for the LAST_NAME column is missing a closing quotation mark.

Explanation

The first statement inserts a row into the table by providing values for all three columns, including a NULL value for the EMPLOYEE_ID column.

The second statement inserts a row into the table by only providing values for the FIRST_NAME and LAST_NAME columns, leaving the EMPLOYEE_ID column to be automatically generated by the database (assuming it has auto-increment functionality).

The fourth statement inserts a row into the table by providing values for all three columns in the correct order.

The fifth statement is incorrect because it has a syntax error. The last value for the LAST_NAME column is missing a closing quotation mark.

Submit