0

databases Online Quiz - 116

Description: databases Online Quiz - 116
Number of Questions: 20
Created by:
Tags: databases
Attempted 0/20 Correct 0 Score 0

Which character is used to continue a statement in SQL*Plus?

  1. *

  2. /

  3. -

  4. @


Correct Option: C

Assuming today is Monday, 10 July 2000, what is returned by this statement: SELECT to_char(NEXT_DAY(sysdate, 'MONDAY'), 'DD-MON-RR') FROM dual;

  1. 17-JUL-00

  2. 11-JUL-00

  3. 09-JUL-00

  4. 03-JUL-00


Correct Option: A

SELECT NULLIF('testing','testing') FROM DUAL ;

  1. testing

  2. True

  3. null

  4. error


Correct Option: C

SELECT NULLIF(NULL,'testing') FROM DUAL ;

  1. testing

  2. True

  3. null

  4. error


Correct Option: D

SELECT NVL2(null,'second','third') FROM DUAL ;

  1. second

  2. third

  3. null

  4. error


Correct Option: B

What is true about subqueries ?

  1. A single row subquery can retrieve data from only one table.

  2. A SQL query statement cannot display data from table B that is referred to in its subquery,unless table B is included in the main query's FROM clause.

  3. A SQL query statement can display data from table B that is referred to in its subquery, without including table B in its own FROM clause

  4. A single row subquery can retrieve data from more than one table.


Correct Option: B,D

AI Explanation

To answer this question, let's go through each option to understand why it is correct or incorrect:

Option A) A single row subquery can retrieve data from only one table. - This option is incorrect because a single row subquery can retrieve data from multiple tables. The number of tables involved in the subquery depends on the specific query and its requirements.

Option B) A SQL query statement cannot display data from table B that is referred to in its subquery, unless table B is included in the main query's FROM clause. - This option is correct. In SQL, a subquery is a query nested within another query. The main query cannot directly access tables that are referred to in the subquery, unless those tables are included in the main query's FROM clause. This is because the main query needs to have access to the table's data in order to display it.

Option C) A SQL query statement can display data from table B that is referred to in its subquery, without including table B in its own FROM clause. - This option is incorrect. As mentioned in the explanation for option B, the main query needs to include table B in its FROM clause in order to display data from it that is referred to in the subquery.

Option D) A single row subquery can retrieve data from more than one table. - This option is correct. A single row subquery can retrieve data from multiple tables, depending on the specific query and its requirements.

The correct answer is B, D. These options are correct because a subquery cannot directly access tables referred to in it unless those tables are included in the main query's FROM clause (option B), and a single row subquery can retrieve data from more than one table (option D).

What is true about an equijoin?

  1. You can join a maximum of two tables through an equijoin.

  2. You can join a maximum of two columns through an equijoin.

  3. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.

  4. To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns.

  5. You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.


Correct Option: C

What type of object privileges can be granted on a view?

  1. DELETE, INSERT,SELECT

  2. DELETE, INSERT, SELECT, UPDATE

  3. ALTER, DELETE, INSERT, SELECT

  4. NONE


Correct Option: B

AI Explanation

To answer this question, you need to understand the concept of object privileges and views in a database.

A view is a virtual table that is based on the result of a query. It does not store any data itself but provides a way to access and manipulate data from one or more underlying tables. When granting privileges on a view, you can specify the types of operations that can be performed on the view.

Let's go through each option to understand which object privileges can be granted on a view:

Option A) DELETE, INSERT, SELECT - This option is incorrect because it does not include the UPDATE privilege. The correct option should include the UPDATE privilege.

Option B) DELETE, INSERT, SELECT, UPDATE - This option is correct because it includes all the necessary object privileges for performing delete, insert, select, and update operations on the view. Therefore, this option is the correct answer.

Option C) ALTER, DELETE, INSERT, SELECT - This option is incorrect because it includes the ALTER privilege, which is not applicable to views. The correct option should not include the ALTER privilege.

Option D) NONE - This option is incorrect because it suggests that no object privileges can be granted on a view, which is not true. You can grant object privileges on a view.

The correct answer is B) DELETE, INSERT, SELECT, UPDATE. This option is correct because it includes all the necessary object privileges for performing delete, insert, select, and update operations on the view.

Two reasons to create table alias :

  1. You have too many tables.

  2. You have too many columns in your tables.

  3. You want to work on your own tables.

  4. You want to use another schema's tables.

  5. Your tables have difficult names.

  6. Your tables are too long.


Correct Option: D,E
  1. You want all unmatched data from both tables.

  2. One of the tables has more data than the other.

  3. You want all matched data from both tables.

  4. You want all matched and unmatched data from only one table.


Correct Option: A
  1. A WHERE clause can be used to restrict rows only.

  2. A WHERE clause CANNOT be used in a query if the query uses a HAVING clause.

  3. WHERE clause can be used to restrict both rows and groups.

  4. WHere clause filters the columns from the output


Correct Option: A

What is true about the Truncate statement?

  1. It is faster than the Delete operation.

  2. You can roll back the deletion of rows after the statement executes.

  3. You must be the owner of the table or have DELETE ANY TABLE system privileges

  4. It releases the storage space used by the table.


Correct Option: A,C,D
  1. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.

  2. The UNIQUE constraint does not permit a null value for the column

  3. The NOT NULL constraint ensures that null values are not permitted for the column

  4. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index


Correct Option: B,D

Once a sequence is created

  1. It is automatically used in all INSERT and UPDATE

  2. It is linked to a specific table.

  3. It belongs to a specific schema.

  4. It is automatically available to all users.


Correct Option: C

PRODUCT ======= Product ID Product Description Manufacturer ID MANUFACTURER ============ Manufacturer ID Manufacturer Name Referring to the above table, what type of relationship exists between the Product table and the Manufacturer table?

  1. Product - Many,Manufacturer - Many

  2. Product - One or Many,Manufacturer - One or Many

  3. Product - Many,Manufacturer - One

  4. Product - One,Manufacturer - One

  5. Product - One,Manufacturer - Many


Correct Option: B

AI Explanation

To determine the type of relationship between the Product table and the Manufacturer table, we need to analyze the cardinality of the relationship.

Looking at the table, we can see that the Product ID in the Product table corresponds to the Manufacturer ID in the Manufacturer table. This indicates that each product can be associated with one manufacturer.

However, the Product table can have multiple products, and the Manufacturer table can have multiple manufacturers. This suggests that there can be multiple products associated with multiple manufacturers.

Therefore, the correct answer is:

B. Product - One or Many, Manufacturer - One or Many

You are writing a database application to run on your DBMS. You do not want your users to be able to view the underlying table structures. At the same time you want to allow certain update operations. Referring to the above scenario, what structure will you deploy?

  1. Cursor table

  2. Table filter

  3. Dynamic procedure

  4. View

  5. Summary table


Correct Option: D

AI Explanation

To answer this question, let's go through each option to understand why it is correct or incorrect:

Option A) Cursor table - This option is incorrect because a cursor table is used to hold the result set of a query temporarily and does not provide any control over the visibility of underlying table structures.

Option B) Table filter - This option is incorrect because a table filter is used to limit the rows displayed in a result set based on specified conditions, but it does not provide control over the visibility of table structures.

Option C) Dynamic procedure - This option is incorrect because a dynamic procedure is used to execute a sequence of SQL statements dynamically, but it does not provide control over the visibility of table structures.

Option D) View - This option is correct because a view is a virtual table that is based on the result of a query. It allows users to interact with the data without directly accessing the underlying table structures. Views can be used to restrict access to certain columns or rows, providing control over what data users can see. In this scenario, using a view would allow the users to perform update operations while hiding the underlying table structures.

Option E) Summary table - This option is incorrect because a summary table is created by aggregating data from one or more tables to provide summary information. It does not provide control over the visibility of underlying table structures.

The correct answer is D) View. This option is correct because it allows users to interact with the data without directly accessing the underlying table structures, while still allowing certain update operations.

- Hide questions