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

The correct answer is option 1.

Concept:

Option 1: A relation with only two attributes is always in BCNF.

True, Relation with two attributes always in BCNF

Example:

A relation R(XY) and functional dependency are {X→Y} it is in BCNF.

{Y→X} it is in BCNF.

{x→Y, Y→X} it is in BCNF.

Option 2: If all attributes of a relation are prime attributes, then the relation is in BCNF.

False, If all prime attributes then the relation is always in 3NF but may not be in BCNF.

Example:

A relation R(ABCD) and functional dependency are {AB→C, B→D, D→B } it is in 3NF.

Candidate key= AB, AD

AB→C is BCNF

B→D is in 3NF

D→B is in 3NF

Hence the relation is 3NF.

Option 3: Every relation has at least one non-prime attribute.

False, It is not mandatory for at least one non-prime attribute in the Relational database management system table.

Option 4: BCNF decompositions preserve functional dependencies.

False, It is not every relation can decompose into BCNF with dependency preserving. Every non-prime attribute in BCNF should be functionally dependent on one of the schema's super keys. If there is any FD that does not follow this, we must divide it into a new relationship in that case. Now, if any other FD employs the prior FD, the FD will not be preserved in BCNF.

The correct answer is A is a candidate key.

Key Points

• A candidate key is a column, or set of columns, in a table that can uniquely identify any database record without referring to any other data. A candidate key's values are unique and can be used as a primary key.
• In the given relational schema R (A, B, C), column A has distinct values including NULL values. This means that each value in column A is unique among all entries in that column, which is a requirement for being a candidate key.
• A candidate key must have unique values, and while it can have NULL values, it means it is not suitable to be a primary key (as primary keys cannot have NULL values). Hence, column A qualifies as a candidate key.
• A primary key is a candidate key that is chosen by the database designer to uniquely identify records in a table. Since A contains NULL values, it cannot be a primary key.

Additional Information

• Relational database systems typically require that each table has a primary key. This key is used to enforce entity integrity.Other candidate keys can be used as alternative keys or for creating unique constraints.
• NULL values in candidate keys can be useful in certain scenarios where not all records need to have a value for the key column.
• In SQL, the UNIQUE constraint can be used to designate a column (or combination of columns) as a candidate key.
• When designing a database schema, it's essential to identify candidate keys to ensure data integrity and efficient data retrieval.

The correct answer is Both I and II are true.

Key Points

• The primary key of a relation cannot contain null values. This is because the primary key uniquely identifies each record in a database table, and having a null value would violate this uniqueness constraint.
• A unique key can have null values. While unique keys ensure that all values in a column are different, they do allow for null values, as nulls are not considered equal to any other value, including other nulls.

Additional Information

• Primary keys are used to establish and enforce entity integrity, ensuring that each record in a table is unique and identifiable.
• Unique keys help in ensuring the uniqueness of the column values but can be used in scenarios where null values are permissible.
• Both primary keys and unique keys can be used to enforce constraints and indexes, improving the performance of database queries.
• In a relational database, both primary and unique keys are crucial for maintaining data integrity and efficient data retrieval.

The correct answer is THEN().

Key Points

• Date functions are used in SQL to manipulate and retrieve date and time values from the database. They are essential for performing operations on date and time data types.
• Common date functions include:
  • DATE(): Returns the current date.
  • NOW(): Returns the current date and time.
  • DAY(): Returns the day part of a date.
• The function THEN() is not a recognized date function in SQL. It does not perform any operations related to dates or times.

Additional Information

• Date functions are pivotal in database management systems for scheduling, logging, and tracking events over time.
• They help in calculating intervals between dates, formatting dates, and extracting specific parts of date values.
• Other notable date functions include:
  • MONTH(): Returns the month part of a date.
  • YEAR(): Returns the year part of a date.
  • TIMESTAMP(): Returns a date and time value.
• Understanding and using date functions can significantly enhance the capability to perform temporal queries and reports.

The correct answer is MOD.

Key Points

• The SQL MOD function is used to find the remainder of a division operation between two numbers.
  • In the given example, MOD(53, 10), 53 is divided by 10.
  • The quotient of this division is 5 (since 53 divided by 10 equals 5 with a remainder).
  • The remainder of this division is 3, hence the output of MOD(53, 10) is 3.

Additional Information

• The MOD function is supported by many SQL databases, including MySQL, PostgreSQL, Oracle, and SQL Server.
• The syntax of the MOD function can vary slightly between different SQL databases.
• In some databases, the MOD function might be called REMAINDER.
• Understanding the MOD function is essential for mathematical calculations and data analysis in SQL.

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