Query Language MCQ Quiz - Objective Question with Answer for Query Language - Download Free PDF

The correct option is (2)

Candidate key

Concept:-

The candidate key can be called a super key, as each candidate key is a subset of the super key. The super key with all necessary attributes is known as the candidate key. The super key with unnecessary attributes cannot be considered a candidate key.

Key Points

• A Candidate key is a minimal super key, meaning that it would cease to be a super key if you removed any attribute from the set.
• A minimum super key is referred to as a candidate and the main key since the primary key is chosen from the candidate keys.
• The minimal set of attributes that can uniquely identify a tuple is known as candidate key. For example, STUD_NO in STUDENT relation. It is a minimal super key.

Additional InformationForeign keys:- The characteristic that establishes the relationship between tables is the foreign key of a table. A foreign key is a column or columns of data in one table that connects to the primary key data in the original table.

Primary key:- The very minimum set of characteristics necessary to identify each row in a database is known as the primary key. It is chosen from a list of potential keys. The primary key might be any candidate's key.

Reference key:- The primary key that is used as a reference in the other table is known as the Reference key.

The correct answer is (I) and (II) are true

Concept:

Statement 1: A super key is an attribute or combination of attributes that uniquely identify records in an RDBMS table.

True, A super key, or just key, is a combination of every attribute that may be used to specifically identify rows (or tuples) in a database. This indicates that a superkey may contain additional information that isn't required for uniquely identifying table rows.

Statement 2: A candidate key is a subset of a super key.

True, A Candidate key is a subset of Super keys and is clear of any unnecessary attributes that are not essential for tuple identification. For all tuples, the Candidate key value is distinct and non-null. Additionally, each table must include at least one Candidate key.

Statement 3: All super keys are candidate keys but vice versa is not true.

False, Candidate keys are a subset of Super keys. They contain only those attributes which are required to uniquely identify tuples. All Candidate keys are Super keys. But the vice-versa is not true.

Hence the correct answer is (I) and (II) are true.

The correct option is TRUNCATE

CONCEPT:

TRUNCATE command removes all rows from a table, but the table structure or definition remain.

DROP command can be used to remove the table definition in addition to its data.

DELETE command can be used to remove the specified rows(one or more) of the table.

Syntax:

TRUNCATE TABLE table_name;

DROP TABLE table_name;

DELETE FROM table_name WHERE condition;

The correct answer is Option 3) Duplicate Key.

Key Points

• In the Relational Model of databases, keys are used to uniquely identify tuples (rows) in a relation (table).
• Primary Key: A unique identifier for each record in a table. No two rows can have the same primary key, and it cannot contain NULLs.
• Foreign Key: A field (or collection of fields) in one table that refers to the primary key in another table. It is used to establish and enforce a link between the data in the two tables.
• Composite Key: A key made up of two or more attributes that together uniquely identify a record.
• Duplicate Key ❌ is not a recognized concept in the relational model. The term contradicts the basic purpose of a key, which is to ensure uniqueness.

Additional Information

• Candidate Key: A set of attributes that can uniquely identify a record. One of them is selected as the primary key.
• Super Key: A set of attributes that uniquely identifies each record in a table. It may contain extra attributes not necessary for uniqueness.
• All keys in relational models are meant to avoid duplication and enforce integrity.

Conclusion: Since the term "Duplicate Key" implies the presence of non-unique identifiers, it is not considered a valid key in the relational model.

The correct answer is Option 1) It is a wrong query as attribute in the GROUP BY clause is not specified in the SELECT clause.

Key Points

• In SQL, when using the GROUP BY clause, all selected columns that are not aggregated must appear in the GROUP BY clause.
• In the given query:

  SELECT CourseName, Fees, StartDate, Duration 
  FROM Students, Courses 
  WHERE Students.CourseID = Courses.CourseID 
  GROUP BY BookID

• BookID is used in the GROUP BY clause but is not present in the SELECT list, and it is also not a part of any aggregation (like COUNT(), SUM(), etc.).
• This violates the SQL standard rule and will result in an error in most RDBMS systems like MySQL, PostgreSQL, and SQL Server.

Additional Information

• Option 2 – It is a wrong query since is used in place of: This statement is unclear and grammatically incorrect; it doesn’t specify any real SQL issue.
• Option 3 – It is a wrong query as multiple tables are used in the SELECT clause: Not true. Joining multiple tables using WHERE is valid SQL.
• Option 4 – It is correctly formed query: ❌ Incorrect. The mismatch between SELECT and GROUP BY columns makes the query invalid.

Conclusion: The SQL query is incorrect because it uses GROUP BY on a column that is neither in the SELECT clause nor used in an aggregate function.

Answer: Option 3

Concept: 

Foreign Key :is the set of attributes in a particular relation whose values are belongs to primary key of same relation or other relation.

Explanation: 

Statement 1: A relation scheme can have at most one foreign key.

There is no such restriction on how many number of Foreign keys a particular relation can have. A relation can have as many number of Foreign keys as Required. 

So this statement is false.

Statement 2: foreign key in a relation scheme R cannot be used to refer to tuples of R.

There is no such constraint. Foreign key can be used to refer to primary key of the same relation. Self-referencing relations are examples of such foreign key. So this statement is also false.

So option 3 is the correct answer.

Function Dependencies:

{(E, F} → {G}, {F} → {I, J}, {E, H} → {K, L}, {K} → {M}, {L} → {N}}

Option 1: {E, F}

{E, F}⁺ = {E, F, G, I, J}

Since K, L, M and N is missing in RHS ∴ it is not a key

Also, {E} cannot be a key because {E} is subset of {E, F}

Option 2: {E, F, H}

{E, F, H}⁺ = {E, F, H, G, I, J, K, L, M, N}

∴ it is a key

Key for R is {E, F, H}.

Important Points:

In relation algebra, key is primary key or candidate key.

{E, F, H, K, L} is super key. 

• Attributes of the relation which exist in at least one of the possible candidate keys, are called prime or key attributes
• Candidate key is a minimal super key and a Super key is a set of attributes that uniquely identify a tuple in a relation.
• Therefore, a prime attribute of a relation scheme R is an attribute that appears in some candidate key of R

Important Point:

• Primary key is selected from a set of candidate keys of a relation.

Mistake Points 
Option 1) is not correct. Because a prime attribute is an attribute that can appear in any candidate key. The attribute need not appear in all candidate keys of R. Example:- if A is a prime attribute then for a relationship it is not required for A to be present in all candidate keys of relation R.

Key Points

 SQL (Structured Query Language) is a standardized programming language that's used to manage relational databases and perform various operations on the data in them. ... SQL became the de facto standard programming language for relational databases after they emerged in the late 1970s and early 1980s.

• SQL is regularly used not only by database administrators, but also by developers writing data integration scripts and data analysts looking to set up and run analytical queries.
• The uses of SQL include modifying database table and index structures; adding, updating and deleting rows of data; and retrieving subsets of information from within a database for transaction processing and analytics applications.

Hence the correct answer is Structured Query Language.

The * symbol is used to see every column of a table.

 For Example, Let us consider a table Table1

If you want to fetch only some specific columns from the table, then we can use this query,

Select ID,NAME,AGE from Table1;    / Syntax is Select (column_name1, coloumn_name2.....coloum_name) from table_name;

If you want to fetch all the fields of the Table1 table, then you should use the following query , 

Select * from Table1;    / Syntax is Select * from table_name;

Therefore Option 3 is correct

Concept:

Superkey is a set of attributes within a table whose values can be used to uniquely identify a tuple. A candidate key is a minimal superkey.

Superkey is superset of candidate key or primary key.

Explanation:

Primary key is VY. (given)

All superkeys must contain this primary key VY. From the given keys, key, which doesn’t contain

the VY.

Here, option 2: VWXZ

TRUNCATE

• Is a DDL command hence it cannot be rolled back.
• It resets the identity of the table and locks that state of the table.
• Hence, Commit and Rollback will have no effect after TRUNCATE.

DELETE

• Is a DML command hence it can be rolled back
• It does not rest the identity of the table, it just locks the table row
• Hence Commit and Rollback can have effect depending on the lock techniques used.

Hence, it can be said that after the execution of ‘TRUNCATE’ operation, COMMIT, and ROLLBACK statements cannot be performed to retrieve the lost data, while ‘DELETE’ allows it.

It can also be said that after the execution of ‘DELETE’ operation, COMMIT and ROLLBACK statements can be performed to retrieve the lost data, while TRUNCATE does not allow it

NOTE

Inner Query: SELECT AVG (cost) FROM Cataloque WHERE pno = ‘P4’ GROUP BY pno

The execution of the inner query gives the average of the cost of parts with part-id P4

Output:

Outer Query:

SELECT s.sno, s.sname FROM Suppliers s, Cataloque c WHERE s.sno = c.sno AND Cost > (225)

The execution of the entire query output the following table:

Hence, there are 4 rows in the resultant table. 

The correct option is (2)

Candidate key

Concept:-

The candidate key can be called a super key, as each candidate key is a subset of the super key. The super key with all necessary attributes is known as the candidate key. The super key with unnecessary attributes cannot be considered a candidate key.

Key Points

• A Candidate key is a minimal super key, meaning that it would cease to be a super key if you removed any attribute from the set.
• A minimum super key is referred to as a candidate and the main key since the primary key is chosen from the candidate keys.
• The minimal set of attributes that can uniquely identify a tuple is known as candidate key. For example, STUD_NO in STUDENT relation. It is a minimal super key.

Additional InformationForeign keys:- The characteristic that establishes the relationship between tables is the foreign key of a table. A foreign key is a column or columns of data in one table that connects to the primary key data in the original table.

Primary key:- The very minimum set of characteristics necessary to identify each row in a database is known as the primary key. It is chosen from a list of potential keys. The primary key might be any candidate's key.

Reference key:- The primary key that is used as a reference in the other table is known as the Reference key.

Super Key

• It is an attribute (or set of attributes) that is used to uniquely identifies all attributes in a relation. 
• All super keys can’t be candidate keys but its reverse is true. 
• There can be more than one super key.
• In relation, the number of super keys is always greater than or equal to the number of candidate keys.
• There always exists at least one super key in a table.

Candidate key

• It is a minimal set of attributes necessary to identify a tuple; this is also called a minimal super key.
• Candidate key can be more than one.
• One of the candidate keys is designated as the primary key.

Primary key

• Candidate key from the table selected by the database administrator to uniquely identify tuples in a table known as the primary key.
• Since the candidate is a minimal set of attributes necessary to identify a tuple therefore the primary key is also  a minimal set of attributes necessary to identify a tuple and hence primary key cannot be obtained by removing one or more attributes from a candidate key.

Therefore option 4 is false