Test Your Ddl And Dml Knowledge

Approved & Edited by ProProfs Editorial Team
The editorial team at ProProfs Quizzes consists of a select group of subject experts, trivia writers, and quiz masters who have authored over 10,000 quizzes taken by more than 100 million users. This team includes our in-house seasoned quiz moderators and subject matter experts. Our editorial experts, spread across the world, are rigorously trained using our comprehensive guidelines to ensure that you receive the highest quality quizzes.
Learn about Our Editorial Process
| By Sudha_test
S
Sudha_test
Community Contributor
Quizzes Created: 12 | Total Attempts: 35,422
Questions: 10 | Attempts: 3,571

SettingsSettingsSettings
Test Your Ddl And Dml Knowledge - Quiz

Are you an avid coder? We have a special Oracle quiz for you! Test your DDL and DML Knowledge with this super informative, interesting quiz! We are here to challenge your knowledge! Let's see how well you perform in this fantastic quiz. Answer the questions correctly in this quiz and get a high score. We also suggest you gather some of your close buddies for a fun, knowledgeable time! Compare your scores & see who's the winner! Good Luck and keep learning!


Questions and Answers
  • 1. 

    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?

    • A.

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

    • B.

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

    • C.

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

    • D.

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

    • E.

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

    Correct Answer(s)
    C. INSERT INTO employees VALUES ('1000','JOHN','NULL');
    E. INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, 'john',");
    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.

    Rate this question:

  • 2. 

    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?

    • A.

      MERGE

    • B.

      INSERT

    • C.

      UPDATE

    • D.

      ADD

    • E.

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

    Correct Answer
    C. UPDATE
    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.

    Rate this question:

  • 3. 

    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?

    • A.

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

    • B.

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

    • C.

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

    • D.

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

    Correct Answer
    C. You cannot specify column names in the DELETE clause of the DELETE statement.
    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.

    Rate this question:

  • 4. 

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

    • A.

      Updating

    • B.

      Viewing

    • C.

      Deleting

    • D.

      Inserting

    • E.

      Truncating

    Correct Answer
    B. Viewing
    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.

    Rate this question:

  • 5. 

    What does the TRUNCATE statement do?

    • A.

      Removes the table

    • B.

      Removes all rows from a table

    • C.

      Shortens the table to 10 rows

    • D.

      Removes all columns from a table

    • E.

      Removes foreign keys from a table

    Correct Answer
    B. Removes all rows from a table
    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.

    Rate this question:

  • 6. 

    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?

    • A.

      Column definitions cannot be altered to add DEFAULT values

    • B.

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

    • C.

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

    • D.

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

    Correct Answer
    B. A change to the DEFAULT value affects only subsequent insertions to the table.
    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.

    Rate this question:

  • 7. 

    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;

    • A.

      You get an error because of a primary key violation

    • B.

      The data and structure of the EMPLOYEES table are deleted.

    • C.

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

    • D.

      You get an error because the statement is not syntactically correct

    Correct Answer
    C. The data in the EMPLOYEES table is deleted but not the structure
    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.

    Rate this question:

  • 8. 

    A data manipulation language statement

    • A.

      Completes a transaction on a table

    • B.

      Modifies the structure and data in a table

    • C.

      Modifies the data but not the structure of a table

    • D.

      Modifies the structure but not the data of a table

    Correct Answer
    C. Modifies the data but not the structure of a table
    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.

    Rate this question:

  • 9. 

    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?

    • A.

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

    • B.

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

    • C.

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

    • D.

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

    Correct Answer
    D. UPDATE employee SET first_name = 'John', last_name = 'Smith' WHERE employee_id = 180;
    Explanation
    The correct answer is "UPDATE employee SET first_name = 'John', last_name = 'Smith' WHERE employee_id = 180;"

    This is the correct syntax for updating the first_name and last_name columns in the employees table. The table name should be "employee" (singular form), not "employees" (plural form). The SET keyword is used to specify the columns to be updated, and the WHERE clause is used to specify the condition for updating the specific row with employee_id = 180.

    Rate this question:

  • 10. 

    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

    • A.

      DELETE FROM MOVIE;

    • B.

      *DELETE ALL FROM MOVIE;

    • C.

      TRUNCATE MOVIE;

    • D.

      TRUNCATE TABLE MOVIE;

    • E.

      All of the above remove the same number of rows.

    Correct Answer(s)
    A. DELETE FROM MOVIE;
    D. TRUNCATE TABLE MOVIE;
    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.

    Rate this question:

Related Topics

Back to Top Back to top
Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.