SQL Quiz By "The Cloud 9"

Reviewed by Editorial Team
The ProProfs editorial team is comprised of experienced subject matter experts. They've collectively created over 10,000 quizzes and lessons, serving over 100 million users. Our team includes in-house content moderators and subject matter experts, as well as a global network of rigorously trained contributors. All adhere to our comprehensive editorial guidelines, ensuring the delivery of high-quality content.
Learn about Our Editorial Process
| By Gaurav.4086
G
Gaurav.4086
Community Contributor
Quizzes Created: 3 | Total Attempts: 2,214
| Attempts: 276
SettingsSettings
Please wait...
  • 1/18 Questions

    With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table?

    • 1. DELETE ROW FirstName='Peter' FROM Persons
    • 2. DELETE FirstName='Peter' FROM Persons
    • 3. DELETE FROM Persons WHERE FirstName = 'Peter'
    • 4. None
Please wait...
About This Quiz

The SQL Quiz by 'The Cloud 9' assesses knowledge in SQL commands and concepts, focusing on operations like DELETE, understanding constraints, Oracle interfaces, and performance optimization. It's designed for learners to test and enhance their SQL skills effectively.

SQL Quizzes & Trivia

Quiz Preview

  • 2. 

    The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true

    • True

    • False

    • Bath correct

    • None

    Correct Answer
    A. True
    Explanation
    The given statement explains the functionality of the OR and AND operators in displaying records. The OR operator will display a record if any of the conditions listed are true, while the AND operator will only display a record if all of the conditions listed are true. Therefore, the answer "true" is correct as it accurately describes the behavior of these operators.

    Rate this question:

  • 3. 

    How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?

    • 1. MODIFY Persons SET LastName='Nilsen' WHERE LastName='Hansen'

    • 2. UPDATE Persons SET LastName='Hansen' INTO LastName='Nilsen'

    • 3. UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'

    • 4. MODIFY Persons SET LastName='Hansen' INTO LastName='Nilsen

    Correct Answer
    A. 3. UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'
    Explanation
    The correct answer is 3. UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'. This query updates the value of the LastName column from 'Hansen' to 'Nilsen' in the Persons table. The WHERE clause ensures that only the rows where the LastName is 'Hansen' are updated.

    Rate this question:

  • 4. 

    Examine the data in the EMPLOYEES and DEPARTMENTS tables. EMPLOYEES LAST_NAME  DEPARTMENT_ID   SALARY Getz                             10                     3000 Davis                            20                   1500 King                             20                    2200 Davis                         30                      5000 Kochhar                                            5000 DEPARTMENTS DEPARTMENT_ID          DEPARTMENT_NAME 10                                      Sales 20                                      Marketing 30                                     Accounts 40                                      Administration You want to retrieve all employees, whether or not they have matching departments in the departments table. Which query would you use?

    • 1. SELECT last_name, department_name FROM employees , departments(+);

    • 2. SELECT last_name, department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);

    • 3. SELECT last_name, department_name FROM employees(+) e JOIN departments d ON (e.department_id = d.department_id);

    • 4. SELECT last_name, department_name FROM employees(+) , departments ON (e.department_id = d.department_id);

    Correct Answer
    A. 2. SELECT last_name, department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
    Explanation
    The correct query to retrieve all employees, whether or not they have matching departments in the departments table is option 2. This query uses a LEFT OUTER JOIN to join the EMPLOYEES table with the DEPARTMENTS table on the department_id column. The "e" and "d" are aliases for the EMPLOYEES and DEPARTMENTS tables, respectively. The LEFT OUTER JOIN ensures that all rows from the EMPLOYEES table are included in the result, even if there is no matching row in the DEPARTMENTS table. The result will include the last_name column from the EMPLOYEES table and the department_name column from the DEPARTMENTS table.

    Rate this question:

  • 5. 

    Consider a table TAB with a single row NAME CHARRIS Then, the query SELECT SUBSTR(Name,-3) FROM TAB gives

    • 1. RIS

    • 2. CHA

    • 3. ARRIS

    • 4. ARR

    Correct Answer
    A. 1. RIS
    Explanation
    The query SELECT SUBSTR(Name,-3) from TAB retrieves the substring of the "Name" column starting from the third character from the end. In this case, the value in the "Name" column is "CHARRIS", so the substring starting from the third character from the end would be "RIS". Therefore, the correct answer is 1. RIS.

    Rate this question:

  • 6. 

    What best describes the relationship between indexes and SQL performance?

    • 1. Indexes are only used in special cases

    • 2. Indexes are used to make table storage more efficient

    • 3. Indexes rarely make a difference in SQL performance

    • 4. Indexes exist solely to improve query speed.

    Correct Answer
    A. 4. Indexes exist solely to improve query speed.
    Explanation
    Indexes in SQL are data structures that are created on columns in a database table to improve the performance of queries. They allow the database to quickly locate and retrieve specific data, thereby improving the speed of query execution. Indexes can significantly enhance the performance of SQL queries by reducing the number of disk reads required to retrieve the desired data. Therefore, option 4, "Indexes exist solely to improve query speed," accurately describes the relationship between indexes and SQL performance.

    Rate this question:

  • 7. 

    Examine the description of the EMPLOYEES table: EMP_ID             NUMBER(4)             NOT NULL LAST_NAME     VARCHAR2(30)       NOT NULL FIRST_NAME    VARCHAR2(30) DEPT_ID            NUMBER(2) JOB_CAT           VARCHARD2(30) SALARY              NUMBER(8,2) Which statement shows the maximum salary paid in each job category of each department?

    • SELECT dept_id, job_cat, MAX(salary) FROM employees WHERE salary > MAX(salary);

    • SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat;

    • SELECT dept_id, job_cat, MAX(salary) FROM employees;

    • SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id;

    Correct Answer
    A. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat;
    Explanation
    The correct answer is "SELECT dept_id, job_cat, MAX(salary)
    FROM employees
    GROUP BY dept_id, job_cat;"

    This statement uses the GROUP BY clause to group the records by department and job category. The MAX(salary) function is used to find the maximum salary within each group. This query will return the maximum salary paid in each job category of each department.

    Rate this question:

  • 8. 

    Management has asked you to calculate the value 12*salary* commission_pct for all the employees in the EMP table. The EMP table contains these columns: LAST NAME             VARCHAR2(35)         NOT NULL SALARY                    NUMBER(9,2)           NOT NULL COMMISION_PCT  NUMBER(4,2) Which statement ensures that a value is displayed in the calculated columns for all employees?

    • SELECT last_name, 12*salary* commission_pct FROM emp;

    • SELECT last_name, 12*salary* (commission_pct,0) FROM emp;

    • SELECT last_name, 12*salary*(nvl(commission_pct,0)) FROM emp;

    • SELECT last_name, 12*salary*(decode(commission_pct,0)) FROM emp;

    Correct Answer
    A. SELECT last_name, 12*salary*(nvl(commission_pct,0)) FROM emp;
    Explanation
    The correct answer is SELECT last_name, 12*salary*(nvl(commission_pct,0))
    FROM emp. This statement uses the NVL function to replace any NULL values in the commission_pct column with 0. This ensures that a value is displayed in the calculated columns for all employees, even if they do not have a commission_pct value.

    Rate this question:

  • 9. 

    Which syntax turns an existing constraint on?

    • ALTER TABLE table_name ENABLE constraint_name;

    • ALTER TABLE table_name STATUS = ENABLE CONSTRAINT constraint_name;

    • ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

    • ALTER TABLE table_name STATUS ENABLE CONSTRAINT constraint_name;

    • ALTER TABLE table_name TURN ON CONSTRAINT constraint_name;

    Correct Answer
    A. ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
    Explanation
    The correct answer is "ALTER TABLE table_name ENABLE CONSTRAINT constraint_name." This syntax is used to turn on or enable an existing constraint on a table. By executing this command, the specified constraint will be activated and enforced for future data modifications on the table.

    Rate this question:

  • 10. 

    Which  statement about views is true?

    • A view can be created as read only.

    • A view can be created as a join on two or more tables.

    • A view must have aliases defined for the column names in the SELECT statement.

    • A view cannot be created with a GROUP BY clause in the SELECT statement.

    Correct Answer
    A. A view can be created as a join on two or more tables.
    Explanation
    A view can be created as a join on two or more tables. This means that a view can be created by combining data from multiple tables into a single virtual table. This allows for easier data retrieval and analysis, as the view can be treated like a regular table. By joining tables in a view, it is possible to access and manipulate data from multiple sources in a more efficient and organized manner.

    Rate this question:

  • 11. 

    What is a TRUNCATE statement in Oracle?

    • 1. A DDL command and can be rolled back

    • 2. A DML command and cannot be rolled back

    • 3. A DDL command and cannot be rolled back.

    • 4. A TCL command and can be rolled back

    Correct Answer
    A. 3. A DDL command and cannot be rolled back.
    Explanation
    The TRUNCATE statement in Oracle is a Data Definition Language (DDL) command used to quickly delete all rows from a table. It is not possible to rollback a TRUNCATE statement because it is a DDL command, not a Data Manipulation Language (DML) command. DDL commands are used to define or modify the structure of database objects, while DML commands are used to manipulate data within the database.

    Rate this question:

  • 12. 

    You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty. Which statement accomplishes this task?

    • ALTER TABLE students ADD PRIMARY KEY student_id;

    • ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);

    • ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;

    • ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

    • ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

    Correct Answer
    A. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
    Explanation
    The correct statement to add a primary key on the STUDENT_ID column in the STUDENTS table is "ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);". This statement adds a constraint named "stud_id_pk" to the table, specifying that the student_id column should be the primary key.

    Rate this question:

  • 13. 

    The database administrator of your company created a public synonym called HR for the HUMAN_RESOURCES table of the GENERAL schema, because many users frequently use this table. As a user of the database, you created a table called HR in your schema. What happens when you execute this query? SELECT * FROM HR;

    • You obtain the results retrieved from the public synonym HR created by the database administrator.

    • You obtain the results retrieved from the HR table that belongs to your schema.

    • You get an error message because you cannot retrieve from a table that has the same name as a public synonym.

    • You obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a Cartesian product.

    Correct Answer
    A. You obtain the results retrieved from the HR table that belongs to your schema.
    Explanation
    When you execute the query SELECT * FROM HR, you obtain the results retrieved from the HR table that belongs to your schema. This is because when you create a table in your schema with the same name as a public synonym, the table in your schema takes precedence over the public synonym. Therefore, the query will retrieve the data from your table rather than the public synonym.

    Rate this question:

  • 14. 

    OCI stands for

    • 1. Open Call Interface

    • 2. Oracle Call Interface

    • 3. Oracle Communication Interface

    • 4. Oracle Connection Interface

    Correct Answer
    A. 2. Oracle Call Interface
    Explanation
    OCI stands for Oracle Call Interface. It is a programming interface that allows applications to interact with Oracle databases. It provides a set of functions that enable applications to perform tasks such as connecting to a database, executing SQL statements, and retrieving results. OCI is commonly used by developers to build high-performance and scalable applications that interact with Oracle databases.

    Rate this question:

  • 15. 

    Examine the description of the STUDENTS table: STD_ID                   NUMBER(4) COURSE_ID          VARCHARD2(10) START_DATE         DATE END_DATE             DATE Which two aggregate functions are valid on the START_DATE column? (Choose two)

    • SUM(start_date)

    • AVG(start_date)

    • COUNT(start_date)

    • AVG(start_date, end_date)

    • MIN(start_date)

    • MAXIMUM(start_date)

    Correct Answer(s)
    A. COUNT(start_date)
    A. MIN(start_date)
  • 16. 

    You need to give the MANAGER role the ability to select from, insert into, and modify existing rows in the STUDENT_GRADES table. Anyone given this MANAGER role should be able to pass those privileges on to others. Which statement accomplishes this?

    • GRANT select, insert, update ON student_grades TO manager;

    • GRANT select, insert, update ON student_grades TO ROLE manager;

    • GRANT select, insert, modify ON student_grades TO manager WITH GRANT OPTION;

    • GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION;

    • GRANT select, insert, update ON student_grades TO ROLE manager WITH GRANT OPTION;

    Correct Answer
    A. GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION;
    Explanation
    The correct answer is "GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION." This statement grants the MANAGER role the ability to select from, insert into, and modify existing rows in the STUDENT_GRADES table. Additionally, the WITH GRANT OPTION allows anyone given the MANAGER role to pass on these privileges to others.

    Rate this question:

  • 17. 

    For which two types of constraints will a unique index be    automatically created??

    • Unique

    • Primary key

    • Not null

    • Foreign key

    • Check

    Correct Answer(s)
    A. Primary key
    A. Foreign key
    Explanation
    A unique index is automatically created for the primary key constraint because it ensures that each value in the column is unique, which helps in identifying each row uniquely. Similarly, a unique index is also automatically created for the foreign key constraint because it ensures the referential integrity between two tables, where the values in the foreign key column must match the values in the primary key column of the referenced table.

    Rate this question:

  • 18. 

    Which three are DATETIME data types that can be used when specifying column definitions? (Choose three.)

    • TIMESTAMP

    • INTERVAL MONTH TO DAY

    • INTERVAL DAY TO SECOND

    • INTERVAL YEAR TO MONTH

    • TIMESTAMP WITH DATABASE TIMEZONE

    Correct Answer(s)
    A. TIMESTAMP
    A. INTERVAL DAY TO SECOND
    A. INTERVAL YEAR TO MONTH
    Explanation
    The three DATETIME data types that can be used when specifying column definitions are TIMESTAMP, INTERVAL DAY TO SECOND, and INTERVAL YEAR TO MONTH. These data types allow for the storage and manipulation of date and time values. TIMESTAMP represents a point in time, while INTERVAL DAY TO SECOND represents a duration in terms of days, hours, minutes, and seconds. INTERVAL YEAR TO MONTH represents a duration in terms of years and months. These data types are commonly used in database systems to handle date and time-related operations and calculations.

    Rate this question:

Quiz Review Timeline (Updated): Mar 21, 2023 +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

  • Current Version
  • Mar 21, 2023
    Quiz Edited by
    ProProfs Editorial Team
  • Oct 24, 2011
    Quiz Created by
    Gaurav.4086
Back to Top Back to top
Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.