1. Suppose a relation R has eight attributes JKLMNOPQ. The set of functional dependencies is F = [LQ -> P, J -> KL, K -> LOQ, N -> J, O ->NP ] is such that F+ is exactly the set of Functional Dependencies(FDs) that hold for R. Also, fields of the relation R contain only atomic values. What is the number of candidate keys for the relation R?

Answer: A. 4

Explanation: On calculating, we will get,

J+ as JKLNOPQ, which has all attributes except the attribute M.

K+ as JKLNOPQ, which has all attributes except the attribute M.

N+ as JKLNOPQ, which has all attributes except the attribute M.

O+ as JKLNOPQ, which has all attributes except the attribute M.

Hence for the relation R, there are four candidate keys JM, KM, NM, and OM.

2. Among the following four statements, which statement is/are true regarding SQL query?

W: In an SQL query, a GROUP BY clause is not necessary for the existence of a HAVING clause.

X: In an SQL query, a GROUP BY clause is necessary for a HAVING clause to exist.

Y: All the attributes which are used by the GROUP BY clause must be there in a SELECT clause.

Z: It is not necessary for a SELECT clause to contain all the attributes which are used by the GROUP BY clause.

W and Z
X and Y
W and Y
X and Z

Answer: B. X and Y

Explanation: This question is also asked in Gate 2012 CS paper. The question should be solved based on the standard SQL concepts. A GROUP By clause must be there for a HAVING clause to exist. However, you may find that you can use the HAVING clause in your application instead of the GROUP BY clause. But you have to follow the standard SQL rules to answer this question. Additionally, all the attributes which are used by the GROUP BY clause must be there in a SELECT clause.

3. What are the types of fields on which a clustering index is defined?

ordering and key
non-ordering and non-key
ordering and non-key
non-ordering and key

Answer: C. ordering and non-key

Explanation: The clustering index is defined over the data, which can be grouped in the form of clusters.

Hence, to create cluster-indexed files, fields that are in ordered form and fields which can be non-key attributes can form clusters easily.

4. Which among the following SQL queries is the example of a subquery in SQL?

SELECT *
FROM students
WHERE age>20
SELECT *
FROM students
WHERE department = ‘Electronics’
SELECT *
FROM students
WHERE student_id IN (SELECT student_id FROM Electronics and Electrical)
SELECT *
FROM students
WHERE student_name LIKE ‘%David%’

Answer: C. SELECT* FROM students WHERE student_id IN (SELECT

student_id FROM Electronics and Electrical)

Explanation: A subquery in SQL is a query embedded within another query to retrieve data from several tables. Subqueries are shown in parentheses and are executed or implemented before the outer query. An outer query uses the outcomes or results of the given subquery to filter or sort the additional data. Subqueries can be used in the SELECT, WHERE, and HAVING clauses of a SQL statement and can be used to perform complex queries that would otherwise be difficult to achieve with a single query.

5. Which of the following is a key feature of distributed database management system?

Low availability
Low scalability
High fault tolerance
Centralized control

Answer: C. High fault tolerance

Explanation: Some of the key features of Distributed database management system are data distribution, transparency, replication, high scalability, security, heterogeneity, consistency, fault tolerance, and concurrency control.

6. Which option among the following is a type of database constraint?

Primary key
Foreign key
Unique key
All of the above

Answer: D. All of the above

Explanation: The several types of database constraints defined to ensure data integrity and consistency are primary key constraint, foreign key constraint, unique constraint, check constraint, Not null constraint, Default constraint, Index constraint, etc.

7. Which option among the following is an example of a composite key?

Student ID
Professor ID
Institute ID
Student Name + Student Address

Answer: D. Student Name + Student Address

Explanation: In DBMS, a composite key is the result of the combination of two or more columns in a table that identifies each row uniquely. Unlike a single-column primary key, a composite key is made up of multiple columns that are combined to form a unique identifier.

8. In the context of databases, what is ACID?

A type of database management system
A set of the database user
A collection of characteristics that guarantee dependable processing of database transactions
None of the above options

Answer: C. A collection of characteristics that guarantee dependable processing of database transactions.

Explanation: ACID is a term that represents four essential properties of a database system, namely Atomicity, Isolation, Consistency, and Durability. In the context of database systems, Atomicity refers to the property where a transaction is treated as a unit of work that is indivisible, while Consistency is a property where a transaction takes the database from one valid state to another. Isolation means that each transaction is executed independently of any other transaction. Durability, in the realm of database systems, signifies that the modification made to the database during a transaction is permanent and persist even in the face of subsequent failures. These are a set of properties that are important in ensuring that database transactions are processed reliably.

9. According to the database management system, what is the meaning of database view?

A physical table within a database
A virtual table is generated from the output of a database query.
A classification of a user in a database system.
None of the above options

Answer: C. A virtual table is generated from the output of a database query.

Explanation: A database view is a type of virtual table that is created by running a database query and storing its result. It is essentially a saved query that can be utilized as a table within the database. The data in a view is not actually stored in the database but is dynamically generated based on the query used to create the view.

10. What is a database schema in database management systems?

A set of database transactions that are executed together
A schema is a plan that establishes the arrangement and configuration of a database
A set of SQL statements stored in a database
None of the above options

Answer: B. A schema is a plan that establishes the arrangement and configuration of a database.

Explanation: A database schema is a representation of the entire database, including tables, columns, relationships, constraints, and other metadata. A schema outlines the specifics of the data types and attributes assigned to each column in a table and describes the connections between various tables within the database. In other words, the database schema is like a blueprint or map of the database, showing how the data is organized and how different tables are related to each other.

11. What does the acronym CRUD stand for in the context of databases?

Collect, Read, Understand, and Deploy
Convert, Rearrange, Update, and Deploy
Create, Read, Update, Delete
None of the above options

Answer: C. Create, Read, Delete, Update

Explanation: CRUD stands for Create, Read, Delete, and Update. Create: This function allows new data to be added to the database. Read: This operation is utilized to extract data from the database. Update: This function allows existing data in the database to be modified. Delete: This function allows data to be removed from the database.

12. What is normalization in the context of a database?

The process of converting data within tables in a database from one form to another.
The process of arranging data in the database, which decreases data duplication and enhances data consistency
The act of removing data/records from the database
None of the above options

Answer: B. The process of arranging data in the database, which decreases data duplication and enhances data consistency.

Explanation: Normalization is the method of arranging and structuring data in a database to decrease the level of dependency between different data elements. The goal of normalization is to remove data duplication and inconsistencies while ensuring that data is accurate, reliable, and efficient to query.

As a technique for organizing data, normalization generally entails dividing extensive tables into smaller and more specialized tables and establishing relationships among them.

13. What is the right outer join in a database?

Right outer join provides only the similar rows of the left and right table.
Right outer join provides only those rows from the right table which are similar to rows in the left table.
Right outer join provides the rows of the left table, which are similar to the rows of the right table, along with all rows of right schema.
None of the above options

Answer: C. Right outer join provides the rows of the left table, which are similar to the rows of the right table, along with rows of right schema.

Explanation: As mentioned, the right outer join provides the rows of the left table, which are similar to the rows of the right table, along with all rows of right schema. Hence it will give data of all the rows of the right table along with rows of the left table, which are similar to the right table.

14. The following question is based on SQL queries:

Consider the table “Professors” provided in the question:

Professor ID	Professor Name	Department	Monthly Salary
1	Deepak	Electronics	50,000
2	Anuj	Computer Science	55,000
3	Ananda	Electronics	60,000
4	David	Computer Science	65,000
5	Amit	Electronics	45,000
6	Ankit	Computer Science	20,000

Now provide, what is the result of the SQL query given below.

SQL Query is: SELECT COUNT (*) FROM Professors WHERE Department = ‘Electronics’;

Answer: D. 3

Explanation: Answer to the given query is 3.The given query in the question provides the total number of rows in the table “Professors” where the department is equivalent to “Electronics”. Now you can find three rows in the table “Professors” where the department is “Electronics” namely rows 1, 3, and 5.

15. The following question is based on the concept of SQL queries:

Consider the table “Demands” provided in the question:

Demand ID	Consumer ID	Product Name	Quantity	Amount in Rupees
1	201	Fridge	2	1000
2	202	Television	4	600
3	201	Remote	1	500
4	203	Microwave Oven	5	800
5	204	Stove	6	600
6	205	Fan	8	700
7	206	Bulb	9	900

Now provide, what is the result of the SQL query given below.

SQL Query is: SELECT SUM (Quantity*Amount in Rupees) FROM Demands WHERE Consumer ID = ‘201’;

2600
2800
2500
2700

Answer: C. 2500

Explanation: Answer to the given query is 2500.The given query in the question provides the sum of the product of Quantity and Amount in Rupee for the rows in the table “Demands” where the Consumer ID is equivalent to the “201”. Now you can find two rows in the table “Demands” where the consumer ID is “201” namely rows 1 and 3. Hence for Row 1, price * amount in rupee = 2 * 1000 = 2000, for row 3, price * amount in rupees = 1 * 500 = 500 and hence sum = 2000 + 500 = 2500.

16. The following question is based on SQL queries:

Consider the table “Graduates” provided in the question:

Graduate ID	Graduate Name	Minor	CGPA
1	Deepak	Math	3.5
2	Anuj	Sanskrit	4.5
3	Ananda	Physics	3.9
4	David	Hindi	2.6
5	Amit	Geography	2.4
6	Ankit	Polity	4.9
7	Dom	Sociology	4.6

Now provide, what is the result of the SQL query given below.

SQL Query is: SELECT AVG (CGPA) FROM Graduates WHERE Minor = ‘Math’ OR Minor = ‘Physics’;

Answer: A. 3.7

Explanation: Answer to the given query is 3.7.The given query in the question provides the average of the CPGA in rows in the table “Graduates” where the Minor is equivalent to “Math” or it is equivalent to “Physics”. Now you can find two rows in the table “Graduates” where Minor is either “Math” or “Physics” namely rows 1 and 3. Now the sum of CGPA in two rows = 3.5 + 3.9 = 7.4, and hence average is 7.4 / 2 = 3.7.

17. What do you think is the use of a primary key in the database tables?

In order to sort data given in a table
To recognize each row in a table uniquely
Tells about relationships between various attributes of different tables
None of the above options

Answer: B. to recognize each row in a table uniquely.

Explanation: Primary key is defined for a database table in order to recognize each row of the database table uniquely.

18. Which of the language is not used in databases?

PHP
SQL
PL/SQL
XML

Answer: A. PHP

Explanation: SQL (structured query language), XML (extended markup language), and PL/SQL are all database languages, but PHP is a language used in web development for server-side scripting.

19. The next five questions, including this question, will be based on the table given below:

Consider the table “Workers” provided in the question:

	Data Type	Nullable or not	Constraints
Worker ID	int	Not	Primary Key
First Name	varchar	Not
Middle Name	varchar	Not
Last Name	varchar	Not
Department	varchar	Yes
Earning	float	Not

Now provide the answer to the following multiple-choice questions based on the given table.

Which of the following SQL Queries will provide the worker with the highest earning?

SELECT MAX (Earning) FROM Workers;
SELECT First Name, Middle Name, Last Name, MAX (Earning) FROM Workers;
SELECT First Name, Middle Name, Last Name, MAX (Earning) FROM Workers WHERE Earning = MAX (Earning);
None of the above options

Answer: B. SELECT First Name, Middle Name, Last Name, MAX (Earning) FROM Workers;

Explanation: The answer to the given question is SQL query given in option B as the MAX function will return the maximum Earning, and it will also select the First Name, Middle Name, and Last Name of the Worker corresponding to the highest Earning at the same time. The query in option A will return only the maximum earning and not the details of the worker corresponding to the highest Earning. The query given in option C is incorrect because we cannot use MAX aggregate function in the WHERE clause.

20. Which of the following SQL Query will provide the average earning of workers in the public works department?

SELECT AVG (Earning) FROM Workers WHERE Department = ‘Public Works’;
SELECT AVG (Earning) FROM Workers WHERE Department LIKE ‘%Public Works%’;
SELECT AVG (Earning) FROM Workers WHERE Department IN (‘Public Works’, ‘Public Works Department’);
None of the above options

Answer: A. SELECT AVG (Earning) FROM Workers WHERE Department = ‘Public Works’;

Explanation: The answer to the given question is the SQL query given in option A, as the AVG function will find the average earning of the workers working in the public works department. Option B is incorrect because it uses the LIKE operator, which will search for the same pattern, which is not necessary. Option C is incorrect because the query will search in only two specified departments. It will not consider other possibilities of department names.

21. Which of the following SQL Query can be used to find the number of workers who have earnings greater than $ 50,000?

SELECT COUNT (*) FROM Workers WHERE Earning > 50,000;
SELECT COUNT (Earning) FROM Workers WHERE Earning > 50,000;
SELECT COUNT (Worker ID) FROM Workers WHERE Earning > 50,000;
None of the above options

Answer: C. SELECT COUNT (Worker ID) FROM Workers WHERE Earning > 50,000;

Explanation: The answer to the given question is the SQL query given in option C as the COUNT function with Worker ID (not null able attribute) as an argument will count the workers with earnings greater than 50,000. Option A is incorrect because it will count all the records, and option B is incorrect as it will count only the non-null values in the Earning column.

22. Which of the following SQL Query can be used to find the top five highest-paid workers?

SELECT TOP 5 First Name, Middle Name, Last Name, Earning FROM Workers ORDER BY Earning DESC;
SELECT First Name, Middle Name, Last Name, Earning FROM Workers ORDER BY Earning DESC LIMIT 5;
SELECT First Name, Middle Name, Last Name, Earning FROM Workers WHERE ROWNUM <= 5 ORDER BY Earning DESC;
None of the above options

Answer: A. SELECT TOP 5 First Name, Middle Name, Last Name, Earning FROM Workers ORDER BY Earning DESC;

Explanation: The answer to the given question is the SQL query given in option A, as the TOP keyword will return the first five highest earnings, and the ORDER BY keyword will arrange them in descending order. It will also provide uswith the First Name, Middle Name, and Last Name of the worker. Option B is incorrect because it uses the LIMIT keyword, which is not supported by all database systems. The same problem exists with option C as the ROWNUM keyword used in the SQL query is only supported by Oracle databases.

23. Which of the following SQL Query can be used to find the total earning earned by workers in the Food Safety and Public departments?

SELECT SUM (Earning) FROM Workers WHERE Department IN (‘Food Safety’, ‘Public Works’);
SELECT TOTAL (Earning) FROM Workers WHERE Department = ‘Food Safety’ OR Department = ‘Public Works’;
SELECT COUNT (Earning) FROM Workers WHERE Department = ‘Food Safety’ AND Department = ‘Public Works’;
None of the above options

Answer: A. SELECT SUM (Earning) FROM Workers WHERE Department IN (‘Food Safety’, ‘Public Works’);

Explanation: The answer to the given question is the SQL query given in option A, as the SUM function will find the total of the salary paid to the workers in the food safety and public works departments. Option B is incorrect because it uses the wrong keyword, ‘TOTAL’. Option C uses the COUNT function which is also not correct because the function will return the count of records rather than returning the sum of a column.

24. Which among the following is not a constraint used in the database management systems?

Check
Primary Key
Subquery
Foreign Key

Answer: B. Subquery.

Explanation: In database management systems, particularly in relational database systems, a constraint is used to maintain data consistency and data integrity. Types of constraints in databases include Check, Foreign Key, Not Null, Unique, Primary Key, etc. Subquery in database systems is not a constraint, but it is a nested query used to obtain data from more than one table.

25. Which among the following commands is used as a data manipulation command in Structured Query Language (SQL)?

CREATE
DROP
ALTER
SELECT

Answer: B. SELECT.

Explanation: In SQL, there are two main types of commands DML commands and DDL commands. DML stands for Data Manipulation Language, which is used to do operations on data stored in tables. On the other hand, DDL stands for Data Definition Language, which is used to create tables and store data in them.

26. In database design, which of the following techniques is/are normalization techniques?

First Normal Form (1 NF)
Third Normal Form (3 NF)
Second Normal Form (2 NF)
All of the above options

Answer: B. All of the above options.

Explanation: In relational database design, normalization is a technique used to reduce the redundancy built in the data and in the structure in which data is stored. There are several levels in normalization, namely the third normal form (3 NF), second normal form (2 NF), first normal form (1 NF), and BCNF. These normal forms are built level wise means BCNF can’t exist without 3 NF, 3 NF can’t exist without 2 NF, and so on.

27. The next two questions, including this question, will be based on the table given below:

Consider the table “Demands” provided in the question:

Demand ID	Consumer ID	Demand Date	Demand Price
1	101	2023-01-01	500.00
2	201	2023-02-01	1000.00
3	101	2023-03-01	750.00
4	301	2023-04-01	1250.00
5	101	2023-05-01	250.00

Now provide the answer to the following multiple-choice questions based on the given table.

What is the Consumer ID of the consumer who has the highest total demand price of demand?

101
201
301
None of the above options

Answer: B. 101.

Explanation: The consumer with ID 101 has the highest demand with 500 + 750 + 250 = 1500 (Which is the highest demand among all the consumers). Hence the highest total demand was placed by consumer ID 101.

28. How many demands were placed in the month of February?

2
3
4
None of the above options

Answer: B. 2.

Explanation: There are two orders placed in the month of February with Demand IDs 2 and 3.

29. In a Single SQL query in relational database systems, what is the maximum number of tables that can be joined?

15
100
1000
There is no limit on the number of tables that can be joined

Answer: D. There is no limit on the number of tables that can be joined.

Explanation: There is no limit on how many tables can be joined in SQL. But increasing the number of tables to be joined has its own consequences, like it will decrease the performance of the database. Hence, it is advised to keep the number of tables less to increase the performance of the database system.

MCQ Test Preparation

MCQ Test Preparation

DBMS Aptitude MCQs