All Courses
Get the App Signup / Login
Sign in Open App

All questions of SQL for Computer Science Engineering (CSE) Exam

Clear all your Computer Science Engineering (CSE) doubts with EduRev

A_____ is a query that retrieves rows from more than one table or view:
  • a)
    Start
  • b)
    End
  • c)
    Join
  • d)
    All of the mentioned
Correct answer is option 'C'. Can you explain this answer?

Sudhir Patel answered
An SQL join clause combines records from two or more tables in a database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables by using values common to each.

The SQL expression :
SELECT distinct 7.branch_name
FROM branch T, branch S
WHERE T.assets >S.assets and S.branch_city = "PONDICHERRY”
Finds the names of
  • a)
    All branches that have greater assets than any branch located in PONDICHERRY.
  • b)
    All branches that have greater assets than all branches located in PONDICHERRY.
  • c)
    The branch that have greater assetin PONDICHERRY.
  • d)
    Any branches that have greater asset than any branch located in PONDICHERRY.
Correct answer is option 'A'. Can you explain this answer?

Aravind Sengupta answered
The condition T. assets > S. assets and S. branch_city = “PONDICHERRY” checks for the all those branch-name who have greater assets than any branch located in Pondicherry. The keyword distinct removes the duplicacy of branch_name satisfy the given condition.

Which is a join condition contains an equality operator:
  • a)
    Equijoins
  • b)
    Cartesian
  • c)
    Both Equijoins and Cartesian
  • d)
    None of the mentioned
Correct answer is option 'A'. Can you explain this answer?

Mrinalini Deshpande answered
Equijoins:
An equijoin is a type of join operation that uses the equality operator to match rows between two tables based on a common column. It performs a comparison between the values of the specified columns in the two tables and returns the rows where the values are equal. The equality operator used in equijoins is typically the "=" operator.

Cartesian Join:
A Cartesian join, also known as a Cartesian product, is a join operation that combines each row from one table with every row from another table. It does not require any specific condition or operator to match the rows between the tables. Instead, it results in a combination of all possible pairs of rows from both tables, resulting in a large result set.

Join Condition with Equality Operator:
To perform a join operation, a join condition is required to specify how the rows from the two tables should be matched. This condition typically includes an equality operator to compare the values of a specific column in both tables.

Answer Explanation:
The question asks which join condition contains an equality operator.
- Option A: Equijoins specifically use the equality operator to match rows based on a common column. Therefore, the join condition in equijoins contains an equality operator.
- Option B: Cartesian joins do not require a specific condition or operator to match rows. Instead, they combine all possible pairs of rows from both tables.
- Option C: The correct answer is option A because equijoins use the equality operator, while Cartesian joins do not have a specific join condition.
- Option D: None of the mentioned is incorrect because equijoins do contain an equality operator in the join condition.

In summary, the join condition that contains an equality operator is an equijoin, making option A the correct answer.

A relation (from the relational database model) consists of a set of tuples, which implies that
  • a)
    Relational model supports multi-valued attributes whose values can be represented in sets.
  • b)
    For any two tuples, the values associated with all of their attributes may be the same.
  • c)
    For any two tuples, the value associated with one or more of their attributes must differ.
  • d)
    All tuples in a particular relation may have different attributes.
Correct answer is option 'C'. Can you explain this answer?

Baishali Dasgupta answered
Explanation:

Relational database model is based on the concept of relations or tables. A relation consists of a set of tuples, where each tuple represents a single entity or object in the real world. Each tuple has a set of attributes or fields, which represent the properties or characteristics of that entity. The values of these attributes are stored in the corresponding columns of the table.

Let us now understand the given options one by one:

a) Relational model supports multi-valued attributes whose values can be represented in sets.

This statement is incorrect. Relational model does not support multi-valued attributes. Each attribute in a relation can have only a single value. However, we can represent multiple values of an attribute by creating a separate table and establishing a relationship between the two tables.

b) For any two tuples, the values associated with all of their attributes may be the same.

This statement is also incorrect. In a relation, each tuple represents a unique entity, and therefore, the values associated with all of their attributes cannot be the same. There must be at least one attribute whose value differs between the two tuples.

c) For any two tuples, the value associated with one or more of their attributes must differ.

This statement is correct. As explained above, each tuple in a relation represents a unique entity, and therefore, the values associated with all of their attributes cannot be the same. There must be at least one attribute whose value differs between the two tuples.

d) All tuples in a particular relation may have different attributes.

This statement is also incorrect. In a relation, all tuples must have the same set of attributes, although some attributes may have null values in some tuples.

Therefore, the correct answer is option 'C', which states that for any two tuples, the value associated with one or more of their attributes must differ.

Consider the set of relations given below and the SQL query that follows:
Students: (Roll_number, Name, date_of_birth)
Courses: (Course_number, Course_name, Instructor)
Grades: (RolL_number, Course_number, Grade)
SELECT DISTINCT Name
FROM Students, Courses, Grades
WHERE Students. RolLnumber = Grades.
RolLnumber
ANDCourse.lnstructor = Korth
AND Courses.Course_number =. Grades. Course_number AND Grades.Grade = A
Which of the following sets is computed by the above query?
  • a)
    Names of students who have got an A grade in all courses taught by Korth.
  • b)
    Names of students who have got an A grade in all courses.
  • c)
    Names of students who have got an A grade in at least one of the courses taught by Korth.
  • d)
    None of the above.
Correct answer is option 'C'. Can you explain this answer?

Rishabh Pillai answered
Explanation:

The given SQL query selects the distinct names of students who have received an A grade in at least one course taught by Korth. Let's break down the query and understand it step by step.

1. FROM Students, Courses, Grades
- This clause specifies the tables from which we are fetching the data: Students, Courses, and Grades.

2. WHERE Students.Roll_number = Grades.Roll_number
- This condition joins the Students and Grades tables based on the Roll_number attribute. It ensures that we only consider the records where the Roll_number matches in both tables.

3. AND Courses.Instructor = 'Korth'
- This condition further filters the joined result by only considering the records where the Instructor attribute of the Courses table is equal to 'Korth'.

4. AND Courses.Course_number = Grades.Course_number
- This condition ensures that we only consider the records where the Course_number matches in both the Courses and Grades tables.

5. AND Grades.Grade = 'A'
- This condition further filters the result by only considering the records where the Grade attribute of the Grades table is equal to 'A'.

6. SELECT DISTINCT Name
- Finally, we select the distinct names from the result obtained after applying the above conditions.

Conclusion:
The query retrieves the distinct names of students who have received an A grade in at least one course taught by Korth. Therefore, option C is the correct answer: "Names of students who have got an A grade in at least one of the courses taught by Korth."

The employee information in a company is stored in the relation. Assume name is the primary key:
Employee: (name, sex, salary, deptName)
Consider the SQL query;
SELECT deptName
FROM Employee
WHERE sex = M
GROUP By deptName
HAVING avg(salary) > (SELECT avg(salary)
FROM Employee)
It returns the names of the departments in which the average salary. 
  • a)
    Is more than the average salary in the company.
  • b)
    Of the male employees is more than the average salary of all the male employees in the company.
  • c)
    Of the male employees is more than the average salary of the employees in the same department.
  • d)
    Of the male employees is more than the average salary in the company.
Correct answer is option 'D'. Can you explain this answer?

Tarun Saini answered
The given query first calculates the average salary from the relation Employee, then it selects the department name where employees who have average salary more than the average salary of the company.

Consider the following relation schema pertaining to a students database:
Student (rollno, name, address)
Enroll (rollno, courseno, coursename)
where the primary keys are shown underlined. The number of tuples in the Student and Enroll tables are 120 and 8 respectively. What are the maximum and minimum number of tuples that can be present in (Student * Enroll), where '*' denotes natural join ?
  • a)
    8, 0
  • b)
    120, 8
  • c)
    960, 8
  • d)
    960, 120
Correct answer is option 'A'. Can you explain this answer?

Nishanth Roy answered
The result of the natural join is the set of all combinations of tuples in R and S that are equal on their common attribute names. What is the maximum possible number of tuples? The result of natural join becomes equal to the Cartesian product when there are no common attributes. The given tables have a common attribute, so the result of natural join cannot have more than the number of tuples in larger table.
What is the maximum possible number of tuples? It might be possible that there is no rollnumber common. In that case, the number of tupples would be 0.

State true or false: We can use Subqueries inside the from clause
  • a)
    True
  • b)
    False
Correct answer is option 'A'. Can you explain this answer?

Sudhir Patel answered
SQL allows the use of Subqueries inside the from clause. The subquery used in the from clause must have in its result the attributes that are specified in the select clause.

Which are the join types in join condition:
  • a)
    Cross join
  • b)
    Natural join
  • c)
    Join with USING clause
  • d)
    All of the mentioned
Correct answer is option 'D'. Can you explain this answer?

Niharika Ahuja answered
Join Types in Join Condition:

There are several types of join conditions that can be used to combine data from multiple tables in a database. Three common types of join conditions are:

1. Cross Join:
A cross join, also known as a Cartesian join, returns the Cartesian product of the two tables involved in the join. In other words, it combines every row from the first table with every row from the second table, resulting in a potentially large result set. The cross join does not require a join condition, so it can be used when there is no common column between the tables.

2. Natural Join:
A natural join is a type of join that combines two or more tables based on their common column names. It automatically matches the columns with the same name from the two tables and returns the rows where the values in those columns are equal. This type of join eliminates the need to specify a join condition explicitly.

3. Join with USING Clause:
A join with the USING clause is another type of join condition that specifies one or more columns that are common between the tables being joined. It is similar to the natural join, but instead of automatically matching all columns with the same name, it only matches the columns specified in the USING clause. This allows for more control over the join condition and can be useful when there are columns with the same name but different data types.

All of the mentioned:
The correct answer is option 'D' - All of the mentioned. This means that all three join types mentioned above (cross join, natural join, join with USING clause) are valid join conditions that can be used in SQL queries to combine data from multiple tables.

These join types provide different ways to combine data based on the requirements of the query. The choice of join type depends on factors such as the relationship between the tables, the columns to be matched, and the desired result set. By understanding and utilizing these different join types, database developers can efficiently retrieve and combine data from multiple tables.

SQL subqueries that can occur wherever a value is permitted provided the subquery gives only one tuple with a single attribute are called _________
  • a)
    Exact Subqueries
  • b)
    Vector Subqueries
  • c)
    Positive Subqueries
  • d)
    Scalar Subqueries
Correct answer is option 'D'. Can you explain this answer?

Gitanjali Mishra answered
Scalar Subqueries

Scalar subqueries are subqueries in SQL that can occur wherever a value is permitted, provided the subquery gives only one tuple with a single attribute. These subqueries return a single value, rather than a table or set of rows.

Usage

Scalar subqueries are commonly used in SQL queries to perform calculations, comparisons, or filtering based on a single value returned from a subquery. They can be used in various clauses of a SQL statement, such as SELECT, WHERE, HAVING, and ORDER BY.

Examples

Here are a few examples to illustrate the usage of scalar subqueries:

1. SELECT statement:

```sql
SELECT column1, (SELECT MAX(column2) FROM table2) AS max_value
FROM table1;
```

In this example, the scalar subquery `(SELECT MAX(column2) FROM table2)` returns the maximum value from `column2` in `table2` as a single value. This value is then aliased as `max_value` in the outer query.

2. WHERE clause:

```sql
SELECT column1, column2
FROM table1
WHERE column2 = (SELECT MAX(column2) FROM table2);
```

In this example, the scalar subquery `(SELECT MAX(column2) FROM table2)` is used in the WHERE clause to filter rows from `table1` where `column2` matches the maximum value from `table2`.

3. HAVING clause:

```sql
SELECT column1, AVG(column2) AS avg_value
FROM table1
GROUP BY column1
HAVING AVG(column2) > (SELECT AVG(column2) FROM table1);
```

In this example, the scalar subquery `(SELECT AVG(column2) FROM table1)` is used in the HAVING clause to filter groups of rows based on the average value of `column2`. Only groups with an average value greater than the overall average will be included in the result.

Conclusion

Scalar subqueries are powerful tools in SQL for performing calculations, comparisons, and filtering based on single values returned from subqueries. They can be used in various parts of a SQL statement to enhance query flexibility and functionality.

In SQL, which data type is best suited for storing large texts such as articles or comments?
  • a)
    TEXT
  • b)
    VARCHAR
  • c)
    CHAR
  • d)
    BLOB
Correct answer is option 'A'. Can you explain this answer?

Shalini Chopra answered
Introduction
When it comes to storing large texts such as articles or comments in SQL databases, choosing the right data type is crucial for efficient data handling and retrieval. The best option for this purpose is the TEXT data type.
Why TEXT is the Best Choice
- Capacity: The TEXT data type can store up to 65,535 characters, making it ideal for lengthy entries like articles or comments. In contrast, VARCHAR is limited to 65,535 bytes, which can be restrictive if using multi-byte character sets.
- Flexibility: TEXT allows for variable-length storage, meaning it only uses as much space as needed for the actual content. This efficiency is advantageous when dealing with varying lengths of text.
- Performance: Although TEXT may not be as fast as VARCHAR in certain scenarios, the performance implications are often negligible for large text data. TEXT is optimized for handling larger volumes of data, ensuring that operations remain efficient.
- Indexing Limitations: While VARCHAR can be indexed more effectively, TEXT is still suitable for full-text searches, which are essential for applications involving large texts.
Other Data Types Considered
- VARCHAR: Generally used for shorter strings, VARCHAR can become cumbersome when dealing with large texts since it may require explicit length management.
- CHAR: Best for fixed-length strings, CHAR is not suitable for large texts due to its rigid size and potential for wasted space.
- BLOB: Primarily used for binary data, BLOB is inappropriate for textual content as it does not support direct text manipulation.
Conclusion
In summary, the TEXT data type is the most suitable choice for storing large texts in SQL databases due to its capacity, flexibility, and performance advantages. By using TEXT, developers can ensure efficient handling of extensive content like articles and comments.

Employee salary should not be greater than Rs. 12,000. This is
  • a)
    Integrity constraint
  • b)
    Referential constraint
  • c)
    Over-defined constraint
  • d)
    None of the above
Correct answer is option 'A'. Can you explain this answer?

Machine Experts answered
Key Points
  • An Integrity constraint is a rule that ensures the accuracy and consistency of data within a relational database.
  • In this context, the constraint ensures that an employee's salary should not exceed Rs. 12,000.
  • Integrity constraints can be applied to ensure that the data entered into a database adheres to certain rules or conditions.
Additional Information
  • Referential constraint: This type of constraint ensures that a foreign key value always points to an existing row in another table, maintaining referential integrity between tables.
  • Over-defined constraint: This term is not commonly used in database terminology. It might imply an excessive or redundant constraint, but it is not a standard term.
  • Feasible constraint: This term suggests a constraint that is practical and possible to implement but does not specifically refer to database constraints.

_______ symbol is used to see every column of a table.
  • a)
    /
  • b)
    _ _
  • c)
    *
  • d)
    !
Correct answer is option 'C'. Can you explain this answer?

Akshita Choudhury answered
Explanation:

Symbol for Viewing Every Column in a Table:
- In SQL, the asterisk symbol (*) is used to represent all columns in a table.
- When you use the asterisk symbol in the SELECT statement, it retrieves all columns from the specified table.

Example:
- For example, if you have a table called "employees" with columns such as "id", "name", "department", and "salary", you can use the following query to select all columns:
sql
SELECT * FROM employees;
- This query will return all columns for every row in the "employees" table.

Benefits of Using the Asterisk Symbol:
- Using the asterisk symbol (*) is convenient when you want to retrieve all columns from a table without specifying each column individually.
- It saves time and effort, especially when dealing with tables that have a large number of columns.

Limitations:
- While using the asterisk symbol is convenient, it is important to note that it may not be the most efficient way to retrieve data, especially in large databases.
- Retrieving unnecessary columns can impact the performance of your queries and increase network traffic.

Conclusion:
- In summary, the asterisk symbol (*) is used to view every column of a table in SQL queries. It provides a quick and easy way to retrieve all columns from a table, but it is important to consider the potential drawbacks in terms of performance and efficiency.

Choose the correct option regarding the following query
WITH max_marks (VALUE) AS
(SELECT MAX(marks)
FROM student)
SELECT studentID
FROM student,max_marks
WHERE student.marks = max_marks.value;
  • a)
    The query is syntactically wrong
  • b)
    The query gives the studentID of the student with the maximum marks
  • c)
    The query gives the maximum marks amongst all the students
  • d)
    The query gives all the studentID values except the student with the maximum marks
Correct answer is option 'B'. Can you explain this answer?

Nayanika Sharma answered
Explanation:

  • The given query first creates a common table expression (CTE) named max_marks which selects the maximum marks from the student table.

  • Then it selects the studentID from the student table and max_marks CTE where student's marks are equal to the maximum marks obtained by any student.

  • Therefore, the query returns the studentID of the student with the maximum marks.

  • Option 'a' is incorrect as the query is syntactically correct.

  • Option 'c' is incorrect as the query only selects the studentID, not the maximum marks.

  • Option 'd' is incorrect as the query specifically selects the studentID with the maximum marks.


Therefore, the correct option is B.

What is the result of the following query?
SELECT studname
FROM college
WHERE marks > SOME (SELECT marks
                                          FROM student
                                       WHERE SECTION = 'c');
  • a)
    The query gives all the studnames for which marks are greater than all the students in section c
  • b)
    The query gives all the studnames for which the marks are greater than at least on student in section c
  • c)
    The query gives all the studnames for which the marks are less than all the students in section c
  • d)
    The query is syntactically incorrect
Correct answer is option 'B'. Can you explain this answer?

Nisha Das answered
Understanding the SQL Query
The provided SQL query is designed to retrieve student names based on their marks in relation to other students in a specific section.
Query Breakdown
- Main Query:
- `SELECT studname FROM college WHERE marks > SOME (...)`
- This part selects student names from the "college" table where their marks exceed those of at least one student from a specified section.
- Subquery:
- `(SELECT marks FROM student WHERE SECTION = c)`
- This subquery fetches the marks of all students who belong to section 'c'.
Logic of the Query
- The usage of `SOME` indicates that the condition checks against a set of values rather than a single value.
- When it states `marks > SOME (subquery)`, it means the main query will return student names for which the marks are greater than at least one of the marks returned by the subquery.
Conclusion
- Given this understanding, the correct interpretation of the query is:
- It retrieves the names of students whose marks are greater than those of at least one student in section 'c'.
Therefore, the correct answer is option B: "The query gives all the studnames for which the marks are greater than at least one student in section c."
This distinguishes it from other options that misinterpret the logic of using `SOME`.

Which join refers to join records from the write table that have no matching key in the left table are include in the result set:
  • a)
    Left outer join
  • b)
    Right outer join
  • c)
    Full outer join
  • d)
    Half outer join
Correct answer is option 'B'. Can you explain this answer?

Sudhir Patel answered
A right outer join will return all the rows that an inner join returns plus one row for each of the other rows in the second table that did not have a match in the first table. It is the same as a left outer join with the tables specified in the opposite order.

Which of the following statements are TRUE about an SQL query?
P : An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause
Q : An SQL query can contain a HAVING clause only if it has a GROUP BY clause
R : All attributes used in the GROUP BY clause must appear in the SELECT clause
S : Not all attributes used in the GROUP BY clause need to appear in the SELECT clause
  • a)
    P and R
  • b)
    P and S
  • c)
    Q and R
  • d)
    Q and S
Correct answer is option 'C'. Can you explain this answer?

Vaishnavi Yadav answered
According to standard SQL answer should be C. Refer If we talk about different SQL implementations like MySQL, then option B is also right. But in question they seem to be talking about standard SQL not about implementation. For example below is a P is correct in most of the implementations. HAVING clause can also be used with aggregate function. If we use a HAVING clause without a GROUP BY clause, the HAVING condition applies to all rows that satisfy the search condition. In other words, all rows that satisfy the search condition make up a single group. See this for more details. S is correct . To verify S, try following queries in SQL.
CREATE TABLE temp
      (
             id INT,
             name VARCHAR(100)
      );
INSERT INTO temp VALUES (1, "abc"); INSERT INTO temp VALUES (2, "abc"); INSERT INTO temp VALUES (3, "bcd"); INSERT INTO temp VALUES (4, "cde");
SELECT Count(*)
FROM temp
GROUP BY name;
Output:
count(*)
--------
2
1
1

Let R(a, b, c) and S(d, e, f) be two relations in which d is the foreign key of S that refers to the primary key of R. Consider the following four operations R and S
1. Insert into R
2. Insert into S
3. Delete from R
4. Delete from S
Which of the following is true about the referential integrity constraint above?
  • a)
    None of 1, 2, 3 or 4 can cause its violation
  • b)
    All of 1, 2, 3 and 4 can cause its violation
  • c)
    Both 1 and 4 can cause its violation
  • d)
    Both 2 and 3 can cause its violation
Correct answer is option 'D'. Can you explain this answer?

Keerthana Sarkar answered
Referential integrity constraint: In relational model, two relation are related to each other over the basis of attributes, Every value of referencing attribute must be null or be available in the referenced attribute.

Here d is the foreign key of S that refers to the primary key of R.
1. Insert into R will not cause any violation.
2. Insert into S may cause violation because for each entry in ‘S ’ it must be. in ‘R ’ .
3. Delete from R may cause violation because for the deleted entry in R there may be referenced entry in the reIation S.
4. Delete from S will not cause any violation. 
Hence (d) is the correct option.

The _________ construct returns true if the argument in the sub-query is void of duplicates
  • a)
    not null
  • b)
    not unique
  • c)
    unique
  • d)
    null
Correct answer is option 'C'. Can you explain this answer?

Sudhir Patel answered
The unique construct returns true if the argument in the sub-query is void of duplicates. The not null construct avoids the specification of null values into the attribute.

Given relations R(w, x) and S(y, z), the result of
SELECT DISTINCT w,x
FROM R, S
Is guaranteed to be same as R, if
  • a)
    R has no duplicates and S is non-empty
  • b)
    R and S have no duplicates
  • c)
    S has no duplicates and R is non-empty
  • d)
    R and S have the same number of tuples
Correct answer is option 'A'. Can you explain this answer?

Preethi Iyer answered
The given query
SELECT DISTINCT W, X
FROM R, S
Is guaranteed to be same as R, if R has no duplicates and ‘S’ is non-empty.
Since, if R is having a duplicates, then the tuples selected by SELECT operation of the R and the given query will not be same also if ‘S’ is empty then the given query outputs null.

Which view that contains more than one table in the top-level FROM clause of the SELECT statement:
  • a)
    Join view
  • b)
    Datable join view
  • c)
    Updatable join view
  • d)
    All of the mentioned
Correct answer is option 'C'. Can you explain this answer?

Ashwini Ghoshal answered
Updatable join view

An updatable join view is a view that contains more than one table in the top-level FROM clause of the SELECT statement and is designed to be updatable, meaning that modifications can be made to the underlying tables through the view.

Join view

A join view is a view that combines data from two or more tables using a join operation. It can be created by joining multiple tables in the top-level FROM clause of the SELECT statement. However, a join view may or may not be updatable, depending on the specific conditions and restrictions imposed by the database management system.

Datable join view

There is no specific term called "datable join view". It seems to be a typo or an incorrect term.

All of the mentioned

The correct answer is not "All of the mentioned" because the term "datable join view" is not valid.

In conclusion, the correct answer is option 'C' - Updatable join view. This type of view allows modifications to be made to the underlying tables through the view and is created by joining multiple tables in the top-level FROM clause of the SELECT statement.

Select operation in SQL is equivalent to
  • a)
    the selection operation in relational algebra
  • b)
    the selection operation in relational algebra, except that select in SQL retains duplicates
  • c)
    the projection operation in relational algebra
  • d)
    the projection operation in relational algebra, except that select in SQL retains duplicates
Correct answer is option 'D'. Can you explain this answer?

Varun Sen answered
Introduction
In SQL (Structured Query Language), the SELECT operation is used to retrieve data from a database table. It is equivalent to the selection operation in relational algebra, which is a theoretical framework for working with relational databases. However, there is a difference between the two operations when it comes to handling duplicates.

Selection Operation in Relational Algebra
The selection operation in relational algebra allows us to select rows from a table that satisfy a given condition. It is denoted by the sigma (σ) symbol. The condition is specified using predicates, such as equality (=), inequality (!=), greater than (>), less than (<), etc.="" the="" result="" of="" the="" selection="" operation="" is="" a="" new="" table="" that="" contains="" only="" the="" rows="" that="" meet="" the="" specified="">

Selection Operation in SQL
In SQL, the SELECT statement is used to retrieve data from one or more tables. It allows us to specify the columns we want to retrieve and the conditions that must be satisfied by the rows. The SELECT statement includes the SELECT, FROM, WHERE, and optional ORDER BY clauses. The WHERE clause is used to specify the condition for selecting rows.

Difference in Handling Duplicates
The main difference between the selection operation in relational algebra and SQL is the handling of duplicates. In relational algebra, the selection operation eliminates duplicates from the result, whereas in SQL, the SELECT operation retains duplicates by default.

This means that if the selection operation in relational algebra produces duplicate rows, they are eliminated in the result. On the other hand, if the SELECT operation in SQL produces duplicate rows, they are included in the result.

Projection Operation in Relational Algebra
The projection operation in relational algebra is used to select specific columns from a table. It is denoted by the pi (π) symbol. The result of the projection operation is a new table that contains only the selected columns.

Conclusion
In summary, the SELECT operation in SQL is equivalent to the selection operation in relational algebra. However, the SELECT operation in SQL retains duplicates by default, whereas the selection operation in relational algebra eliminates duplicates. Therefore, the correct answer is option D - the SELECT operation in SQL is equivalent to the projection operation in relational algebra, except that SELECT in SQL retains duplicates.

SQL allows tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below:
select * from R where a in (select S.a from S)
  • a)
    select R.* from R, S where R.a=S.a (D)
  • b)
    select distinct R.* from R,S where R.a=S.a
  • c)
    select R.* from R,(select distinct a from S) as S1 where R.a=S1.a
  • d)
    select R.* from R,S where R.a=S.a and is unique R
Correct answer is option 'C'. Can you explain this answer?

Sounak Joshi answered
The solution of this question lies in the data set(tuples) of Relations R and S we define. If we miss some case then we may get wrong answer. Let's say, Relation R(BCA) with attributes B, C and A contains the following tuples.
And Relation S(AMN) with attributes A, M, and N contains the following tuples.
Now ,the original Query will give result as: "select * from R where a in (select S.a from S) " - The query asks to display every tuple of Relation R where R.a is present in the complete set S.a.
Option A query will result in : "select R.* from R, S where R.a=S.a"
Option B query will result in : " select distinct R.* from R,S where R.a=S.a"
----------------------------------------------------------------------------------------------------------- Option C query will result in : "select R.* from R,(select distinct a from S) as S1 where R.a=S1.a" B C A --------- 7 2 1 7 2 1 8 9 5 8 9 5 ----------------------------------------------------------------------------------------------------------- Option D query will result in : NULL set "select R.* from R,S where R.a=S.a and is unique R" ---------------------------------------------------------------------------------------------------------- Hence option C query matches the original result set. Note : As mentioned earlier, we should take those data sets which can show us the difference in different queries. Suppose in R if you don't put identical tuples then you will get wrong answers. (Try this yourself, this is left as an exercise for you).

In SQL, like condition allows you to use wild card characters to perform matching. Which of the following is a valid wild card character?
  • a)
    _
  • b)
    $
  • c)
    %
  • d)
    More than one of the above
Correct answer is option 'D'. Can you explain this answer?

Gate Gurus answered
Key Points
The commonly used wildcard characters in SQL LIKE:
  • %: Matches zero, one, or more characters.
  • _: Matches a single character (any letter, number, or symbol).
Examples:
  • SELECT * FROM customers WHERE name LIKE '%en%'; - This query will find all customer names that contain the letters "en" anywhere in the name (e.g., "John", "Steven", "Weekend").
  • SELECT * FROM products WHERE code LIKE 'PR%'; - This query will find all product codes that start with "PR" followed by any characters (e.g., "PR123", "PR-ABC").
  • SELECT * FROM users WHERE username LIKE 'user_'; - This query will find all usernames that start with "user_" followed by a single character (e.g., "user_a", "user_1").

Which SQL aggregate function is used to retrieve minimum value?
  • a)
    max
  • b)
    min
  • c)
    avg
  • d)
    None of the above
Correct answer is option 'B'. Can you explain this answer?

Machine Experts answered
Aggregate functions are functions that take a collection (a set or multiset) of values as input and return a single value. SQL offers five built-in aggregate functions:
  • Average: avg
  • Minimum: min
  • Maximum: max
  • Total: sum
  • Count: count

Which of the following statements are TRUE about an SQL query?
P: An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause
Q: An SQL query can contain a HAVING clause only if it has a GROUP BY clause
R: All attributes used in the GROUP BY clause must appear in the SELECT clause
S: Not all attributes used in the GROUP BY clause need to appear in the SELECT clause
  • a)
    P and R
  • b)
    P and S
  • c)
    Q and R
  • d)
    Q and S
Correct answer is option 'C'. Can you explain this answer?

Crack Gate answered
GROUP BY clause:
  • The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".
  • The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
Example:
SELECT COMPANY, COUNT(*)  
FROM PRODUCT_MAST   
GROUP BY COMPANY 
Therefore all attributes used in the GROUP BY clause must appear in the SELECT clause 
HAVING clause:
HAVING clause is used to specify a search condition for a group or an aggregate.
Having is used in a GROUP BY clause.
If you are not using GROUP BY clause then you can use HAVING function like a WHERE clause.
Syntax:
SELECT column1, column2   
FROM table_name  
WHERE conditions   
GROUP BY column1, column2   
HAVING conditions  
ORDER BY column1, column2;  
 
Example:
SELECT COMPANY, COUNT(*)  
FROM PRODUCT_MAST   
GROUP BY COMPANY  
HAVING COUNT(*)>2; 
Therefore an SQL query can contain a HAVING clause only if it has a GROUP BY clause.

Consider the following three table to store student enrollements in different courses.
Student(EnrollNo, Name)
Course(CourseID, Name)
EnrollMents(EnrollNo, CourseID)
Q. What does the following query do?
  • a)
    Name of all students who are either enrolled in "DBMS" or "OS" courses
  • b)
    Name of all students who are enrolled in "DBMS" and "OS"
  • c)
    Name of all students who are either enrolled in "DBMS" or "OS" or both.
  • d)
    Non of the above
Correct answer is option 'B'. Can you explain this answer?

Nilesh Mukherjee answered
Background Reading: The above query is an example of nested query i.e. query within a query. Firstly the inner query is solved and then the outer one depending on the result of the inner query.
  • WHERE IN returns values that matches values in a list or subquery.
  • WHERE IN is a shorthand for multiple OR conditions.
Here, firstly the inner query is solved. It returns all the Enrollment
Numbers (SELECT S2.EnrollNo) of students where the students’ enrollment
number matches with the enrollment number of the courses
(WHERE S2.EnrollNo = E2.EnrollNo) which have the course IDs whose Course
Name is “OS” (E2.CourseID = C2.CourseID and C2.Name = “OS”).
Hence all the enrollment IDs are filtered out for the students who are enrolled for the “OS” course.
The outer query works similarly and filters out all the all tuples where the Students Enrollment Number matches with the Enrollment Number where the course ID’s are for the course names “DBMS”
(S.EnrollNo = E.EnrollNo AND C.Name =”DBMS” AND E.CourseID = C.CourseId) and additionally matches with the ones that are returned by the inner query i.e. Enrollment Number of students who are enrolled for the course “OS”.
Hence the above queries returns names of all students (SELECT S.Name) who have enrolled for both courses “DBMS” and “OS”. Hence option (B).

Given relations r(w, x) and s(y, z), the result of
is guaranteed to be same as r, provided
  • a)
    r has no duplicates and s is non-empty
  • b)
    r and s have no duplicates
  • c)
    s has no duplicates and r is non-empty
  • d)
    r and s have the same number of tuples
Correct answer is option 'A'. Can you explain this answer?

Anirban Khanna answered
R has no duplicates and s is non-empty The query selects all attributes of r. Since we have distinct in query, result can be equal to r only if r doesn’t have duplicates. If we do not give any attribute on which we want to join two tables, then the queries like above become equivalent to Cartesian product. Cartisian product of two sets will be empty if any of the two sets is empty. So, s should have atleast one record to get all rows of r. 

Which of the following relational algebraic operation is not a commutative operation?
  • a)
    Union
  • b)
    Intersection
  • c)
    Selection
  • d)
    Projection
Correct answer is option 'D'. Can you explain this answer?

Navya Choudhary answered
Explanation:

Relational algebra is a mathematical query language used to manipulate the relational database. It consists of a set of operations that can be applied on relations (tables) to produce another relation as a result. These operations are based on set theory and provide a way to retrieve, modify, and combine data in a relational database.

Commutative operation is an operation that produces the same result regardless of the order of the operands. In other words, the order of the operands does not matter in commutative operations.

Now let's analyze each option to determine which one is not a commutative operation.

1. Union: The union operation combines two relations and returns a new relation that contains all the tuples from both relations, removing any duplicates. The order of the operands does not matter in the union operation, and it produces the same result regardless of the order. Therefore, union is a commutative operation.

2. Intersection: The intersection operation returns a new relation that contains only the common tuples between two relations. Like the union operation, the order of the operands does not matter in the intersection operation, and it produces the same result regardless of the order. Therefore, intersection is a commutative operation.

3. Selection: The selection operation filters the tuples of a relation based on a given condition and returns a new relation that contains only the selected tuples. The order of the operands does not matter in the selection operation, and it produces the same result regardless of the order. Therefore, selection is a commutative operation.

4. Projection: The projection operation selects specific attributes (columns) from a relation and returns a new relation with only those attributes. Unlike the other operations, the order of the operands does matter in the projection operation. Changing the order of the attributes in the projection operation will result in a different relation with different attribute order. Therefore, projection is not a commutative operation.

Conclusion: The relational algebraic operation that is not a commutative operation is Projection (option D).

Consider the following SQL query
select distinct al, a2,........., an
from r1, r2,........, rm
where P
For an arbitrary predicate P, this query is equivalent to which of the following relational algebra expressions ? 
  • a)
  • b)
  • c)
  • d)
Correct answer is option 'A'. Can you explain this answer?

Alok Desai answered
Cross product (x) combines the tuples of one relation with all the tuples of the other relation. Thus, tuples of relation r1, r2 …. rn are combined. 
Select operator  is used to select resultant tuples. 
Projection operator  is used to select a subset of attributes from the resultant tuples by specifying the names of the attributes. So attributes a1, a2, an are projected from the resultant tuples. 
 
Thus, option (A) is correct. 
 
Please comment below if you find anything wrong in the above post.

The statement that is executed automatically by the system as a side effect of the modification of the database is
  • a)
    backup
  • b)
    assertion
  • c)
    recovery
  • d)
    trigger
Correct answer is option 'D'. Can you explain this answer?

Nilesh Chavan answered
Understanding Triggers in Database Systems
Triggers are an essential component of database management systems, enabling automated responses to specific events that occur within the database.
What is a Trigger?
- A trigger is a set of instructions that are automatically executed in response to certain events on a particular table or view.
- These events can include actions such as INSERT, UPDATE, or DELETE operations.
How Triggers Work
- When a specified modification occurs in the database, the trigger is invoked automatically without the need for user intervention.
- This automatic execution can help maintain data integrity, enforce business rules, or perform logging and auditing.
Key Benefits of Using Triggers
- Data Integrity: Triggers can enforce rules to ensure that the data remains consistent and valid, such as preventing invalid entries.
- Automation: They reduce the need for manual checks or operations, thereby streamlining processes and enhancing efficiency.
- Auditing: Triggers can be utilized to maintain a history of changes made to data, which is invaluable for auditing purposes.
Conclusion
In summary, the correct answer to the question is option 'D' – trigger. Triggers are executed automatically by the database system as a direct consequence of modifications, ensuring that the necessary actions are taken without additional commands from the user. This functionality is crucial for maintaining the robustness and reliability of database systems.

Consider the following relational schema pertaining to a students database.
Student: (rollno. name, address)
Enroll: (rollno. courseno. coursename) Where the primary keys are shown underlined. The number of tuples in the Student and the Enroll tables are 120 and 8 respectively. What are the maximum and the minimum number of tuples that can be present in (Student * Enroll), where * denotes natural join?
  • a)
    8, 8
  • b)
    120,8
  • c)
    960,120 
  • d)
    960,8
Correct answer is option 'A'. Can you explain this answer?

Gate Gurus answered
Determining the Maximum and Minimum Number of Tuples in the Natural Join of Student and Enroll Tables
Step 1: Understanding the Given Tables
Student Table: Contains 120 tuples with attributes rollno, name, and address. The primary key is rollno.
Enroll Table: Contains 8 tuples with attributes rollno, courseno, and coursename. The primary key is a combination of rollno and courseno.
Step 2: Concept of Natural Join
A natural join between Student and Enroll will match tuples based on the common attribute rollno. This means:
Only those tuples from Student that have a matching rollno in Enroll will appear in the result.
The number of tuples in the resulting table depends on how rollno values are distributed between both tables.
Step 3: Finding the Maximum Number of Tuples
The maximum number of tuples occurs when each tuple in the Enroll table has a matching rollno in the Student table.
Since Enroll has only 8 tuples, the maximum number of tuples in the join is 8.
Step 4: Finding the Minimum Number of Tuples
The minimum number of tuples occurs when all rollno values in Enroll match the same single rollno in Student.
Even in this case, all 8 tuples from Enroll will be present in the join result (since each enroll record must have a matching student).
Therefore, the minimum number of tuples is also 8.
Final Answer:
Maximum number of tuples = 8
Minimum number of tuples = 8
Thus, the correct answer is:
8, 8.

The ________ comparison checker is used to check “each and every” condition
  • a)
    all
  • b)
    and
  • c)
    every
  • d)
    each
Correct answer is option 'A'. Can you explain this answer?

Sudhir Patel answered
The all comparison checker is used to check “each and every” condition. The “each” and “every” comparison checkers do not exist in SQL.

The ______ construct returns true if a given tuple is present in the subquery.
  • a)
    not exists
  • b)
    present
  • c)
    not present
  • d)
    exists
Correct answer is option 'D'. Can you explain this answer?

Sudhir Patel answered
The exists construct returns true if a given tuple is present in the subquery. The not exists construct gives true if a given tuple is not present in the subquery.

Chapter doubts & questions for SQL - Database Management System (DBMS) 2025 is part of Computer Science Engineering (CSE) exam preparation. The chapters have been prepared according to the Computer Science Engineering (CSE) exam syllabus. The Chapter doubts & questions, notes, tests & MCQs are made for Computer Science Engineering (CSE) 2025 Exam. Find important definitions, questions, notes, meanings, examples, exercises, MCQs and online tests here.

Chapter doubts & questions of SQL - Database Management System (DBMS) in English & Hindi are available as part of Computer Science Engineering (CSE) exam. Download more important topics, notes, lectures and mock test series for Computer Science Engineering (CSE) Exam by signing up for free.

Database Management System (DBMS)

62 videos|69 docs|35 tests

Signup to see your scores go up within 7 days!

Study with 1000+ FREE Docs, Videos & Tests
10M+ students study on EduRev
© EduRev
Education Revolution
Signup to see your scores go up within 7 days!
Access 1000+ FREE Docs, Videos and Tests
Takes less than 10 seconds to signup