SQL Quiz By "The Cloud 9"

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

The correct answer is option 3 because it uses the correct syntax to delete records from the Persons table where the FirstName is 'Peter'. The DELETE statement is followed by the FROM keyword to specify the table, and the WHERE clause is used to specify the condition for deletion, which is FirstName = 'Peter' in this case. Option 1 is incorrect because it uses an incorrect syntax for the DELETE statement. Option 2 is also incorrect because it does not specify the table to delete from. Option 4 is incorrect because there is a valid way to delete the records in this scenario.

Explanation

The correct answer is option 3 because it uses the correct syntax to delete records from the Persons table where the FirstName is 'Peter'. The DELETE statement is followed by the FROM keyword to specify the table, and the WHERE clause is used to specify the condition for deletion, which is FirstName = 'Peter' in this case. Option 1 is incorrect because it uses an incorrect syntax for the DELETE statement. Option 2 is also incorrect because it does not specify the table to delete from. Option 4 is incorrect because there is a valid way to delete the records in this scenario.

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

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.

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.

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

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.

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.

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);

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.

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.

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

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.

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.

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.

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.

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.

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 employeesWHERE salary > MAX(salary);

SELECT dept_id, job_cat, MAX(salary)FROM employeesGROUP BY dept_id, job_cat;

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

SELECT dept_id, job_cat, MAX(salary)FROM employeesGROUP BY dept_id;

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.

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.

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_pctFROM 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;

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.

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.

9. Which syntax turns an existing constraint on?

ALTER TABLE table_nameENABLE constraint_name;

ALTER TABLE table_nameSTATUS = ENABLE CONSTRAINT constraint_name;

ALTER TABLE table_nameENABLE CONSTRAINT constraint_name;

ALTER TABLE table_nameSTATUS ENABLE CONSTRAINT constraint_name;

ALTER TABLE table_nameTURN ON CONSTRAINT constraint_name;

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.

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.

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.

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.

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.

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

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.

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.

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 studentsADD PRIMARY KEY student_id;

ALTER TABLE studentsADD CONSTRAINT PRIMARY KEY (student_id);

ALTER TABLE studentsADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;

ALTER TABLE studentsADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

ALTER TABLE studentsMODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

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.

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.

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

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 samename as a public synonym.

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.

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.

14. OCI stands for

1. Open Call Interface

2. Oracle Call Interface

3. Oracle Communication Interface

4. Oracle Connection Interface

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.

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.

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)

not-available-via-ai

Explanation

not-available-via-ai

Submit

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, updateON student_gradesTO manager;

GRANT select, insert, updateON student_gradesTO ROLE manager;

GRANT select, insert, modifyON student_gradesTO managerWITH GRANT OPTION;

GRANT select, insert, updateON student_gradesTO managerWITH GRANT OPTION;

GRANT select, insert, updateON student_gradesTO ROLE managerWITH GRANT OPTION;

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.

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.

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

Unique

Primary key

Not null

Foreign key

Check

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.

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.

Submit

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

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.

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.

Submit