NTA UGC NET Computer Science Questions with Solutions | SQL, ER Model, Keys

Prepare for NTA UGC NET Computer Science with these carefully curated questions covering SQL queries, relational algebra, entity-relationship models, foreign keys, normalization, and more. This comprehensive guide provides concept clarity and includes essential topics like referential integrity, joins (INNER and FULL), subqueries, and SQL operations. These questions are structured to help you understand the core DBMS concepts required for success in the exam. Whether you’re revising ER Models, SQL syntax, or entity relationships, this blog ensures you’re well-prepared to tackle the exam confidently. Start practicing now and excel in your preparation journey.

Question 1

Let R₁(a, b, c) and R₂(x, y, z) be two relations in which a is the foreign key of R₁ that refers to the primary key of R₂. Consider the following four options.

1. Insert into R₁
2. Insert into R₂
3. Delete from R₁
4. Delete from R₂

Which of the following is correct about the referential integrity constraint with respect to above?

1. Operations a and b will cause violation
2. Operations b and c will cause violation
3. Operations c and d will cause violation
4. Operations d and a will cause violation

---

Question 2

Consider the following ORACLE relations:

One (x, y) = { < 2, 5 >, < 1, 6 >, < 1, 6 >, < 1, 6 >, < 4, 8 >, < 4, 8 > }

Two (x, y) = { < 2, 55 >, < 1, 1 >, < 4, 4 >, < 1, 6 >, < 4, 8 >, < 4, 8 >, < 9, 9 >, < 1, 6 > }

Consider the following two SQL queries SQ1 and SQ2:

SQ1:
SELECT * FROM One
EXCEPT
(SELECT * FROM Two);

SQ2:
SELECT * FROM One
EXCEPT ALL
(SELECT * FROM Two);

For each of the SQL queries, what is the cardinality (number of rows) of the result obtained when applied to the instances above?

1. 2 and 1 respectively
2. 1 and 2 respectively
3. 2 and 2 respectively
4. 1 and 1 respectively

---

Question 3

Referential integrity is directly related to:

1. Relation key
2. Foreign key
3. Primary key
4. Candidate key

---

Question 4

Consider the table student (stuid, name, course, marks). Which one of the following two queries is correct to find the highest marks student in course 5?

Q.1: 
Select S.stuid from student S 
where not exist 
(select * from student e 
 where e.course ='5' and e.marks ≥ s.marks);

Q.2:
select s.stu.id From student S 
where s.marks > any 
(select distinct marks from student S 
 where s.course = 5);

1. Q.1
2. Q.2
3. Both Q.1 and Q.2
4. Neither Q.1 nor Q.2

Question 5

Division operation is ideally suited to handle queries of the type:

1. Customers who have no account in any of the branches in Delhi.
2. Customers who have an account at all branches in Delhi.
3. Customers who have an account in at least one branch in Delhi.
4. Customers who have only joint account in any one branch in Delhi.

---

Question 6

Which of the following is true?

1. I. Implementation of self-join is possible in SQL with table alias.
2. II. Outer-join operation is basic operation in relational algebra.
3. III. Natural join and outer join operations are equivalent.

1. I and II are correct.
2. II and III are correct.
3. Only III is correct.
4. Only I is correct.

---

Question 7

Consider a “CUSTOMERS” database table having a column “CITY” filled with all the names of Indian cities (in capital letters). The SQL statement that finds all cities that have “GAR” somewhere in its name, is:

1. select * from customers where city='%GAR%';
2. select * from customers where city='$GAR$';
3. select * from customers where city like '%GAR%';
4. select * from customers where city as'%GAR';

---

Question 8

Consider the following database table:

Create table test(
    one integer,
    two integer,
    primary key(one),
    unique(two),
    check(one>=1 and <=10), check(two>=1 and <=5)
);

How many data records/tuples at most can this table contain?

1. 5
2. 10
3. 15
4. 50

Question 9

Consider the following three SQL queries (Assume the data in the people table):

1. Select Name from people where Age > 21;
2. Select Name from people where Height > 180;
3. Select Name from people where (Age > 21) or (Height > 180);

If the SQL queries a and b above, return 10 rows and 7 rows in the result set respectively, then what is one possible number of rows returned by the SQL query c?

1. 3
2. 7
3. 10
4. 21

---

Question 10

Suppose ORACLE relation R(A, B) currently has tuples {(1,2), (1,3), (3,4)} and relation S(B, C) currently has {(2,5), (4,6), (7,8)}. Consider the following two SQL queries SQ1 and SQ2:

SQ1: Select * From R Full Join S On R.B=S.B;
SQ2: Select * From R Inner Join S On R.B=S.B;

The numbers of tuples in the result of the SQL query SQ1 and the SQL query SQ2 are given by:

1. 2 and 6 respectively
2. 6 and 2 respectively
3. 2 and 4 respectively
4. 4 and 2 respectively

---

Question 11

Consider the following relation:

Works(emp_name, company_name, salary)

Here, emp_name is primary key.

Consider the following SQL query:

Select emp name
From works T
where salary > (select avg(salary)
                from works S
                where T.company name = S.company name)

The above query is for following:

1. Find the highest paid employee who earns more than the average salary of all employees of his company.
2. Find the highest paid employee who earns more than the average salary of all the employees of all the companies.
3. Find all employees who earn more than the average salary of all employees all the companies.
4. Find all employees who earn more than the average salary of all employees of their company.

---

Question 12

Given two tables:

EMPLOYEE (EID, ENAME, DEPTNO)
DEPARTMENT (DEPTNO, DEPTNAME)

Find the most appropriate statement of the given query:

Select count (*) 'total'
from EMPLOYEE
where DEPTNO IN (D1,D2)
group by DEPTNO
having count (*) >5

1. Total number of employees in each department D1 and D2
2. Total number of employees of department D1 and D2 if their total is > 5
3. Display total number of employees in both departments D1 and D2
4. The output of the query must have at least two rows

Question 13

Given two relations R₁(A, B) and R₂(C, D), the result of the following query:

Select distinct A, B
From R1, R2

Is guaranteed to be same as R₁ provided one of the following condition is satisfied.

1. R₁ has no duplicates and R₂ is empty.
2. R₁ has no duplicates and R₂ is non-empty.
3. Both R₁ and R₂ have no duplicates.
4. R₂ has no duplicates and R₁ is non-empty.

---

Question 14

For a weak entity set to be meaningful, it must be associated with another entity set in combination with some of their attribute values, is called as:

1. Neighbour Set
2. Strong Entity Set
3. Owner Entity Set
4. Weak Set

---

Question 15

Let R₁(a, b, c) and R₂(x, y, z) be two relations in which a is the foreign key of R₁ that refers to the primary key of R₂. Consider the following four options:

1. Insert into R₁
2. Insert into R₂
3. Delete from R₁
4. Delete from R₂

Which of the following is correct about the referential integrity constraint with respect to above?

1. Operations a and b will cause violation
2. Operations b and c will cause violation
3. Operations c and d will cause violation
4. Operations d and a will cause violation

---

Question 16

An entity has:

1. (i) a set of properties
2. (ii) a set of properties and values for all the properties
3. (iii) a set of properties and the values for some set of properties may non-uniquely identify an entity
4. (iv) a set of properties and the values for some set of properties may uniquely identify an entity

Which of the above are valid?

1. (i) only
2. (ii) only
3. (iii) only
4. (iv) only

---

Question 17

Let E₁ and E₂ be two entities in E-R diagram with simple single valued attributes. R₁ and R₂ are two relationships between E₁ and E₂ where R₁ is one-many and R₂ is many-many. R₁ and R₂ do not have any attribute of their own. How many minimum number of tables are required to represent this situation in the Relational Model?

1. 4
2. 3
3. 2
4. 1

---

Question 18

Which of the following statements is false about weak entity set?

1. Weak entities can be deleted automatically when their strong entity is deleted
2. Weak entity set avoids the data duplication and consequent possible inconsistencies caused by duplicating the key of the strong entity
3. A weak entity set has no primary keys unless attributes of the strong entity set on which it depends are included
4. Tuples in a weak entity set are not partitioned according to their relationship with tuples with a strong entity set

---

Question 19

An entity instance is a single occurrence of an ______.

1. Entity type
2. Relationship type
3. Entity and relationship type
4. None of these

---

Question 20

Relations produced from E-R Model will always be in ______.

1. 1 NF
2. 2 NF
3. 3 NF
4. 4 NF

Question 21

Let M and N be two entities in an E-R diagram with simple single value attributes. R₁ and R₂ are two relationships between M and N, where as R₁ is one-to-many and R₂ is many-to-many.

The minimum number of tables required to represent M, N, R₁, and R₂ in the relational model are __________.

1. 4
2. 6
3. 7
4. 3

---

Question 22

What kind of mechanism is to be taken into account for converting a weak entity set into strong entity set in entity-relationship diagram?

1. Generalization
2. Aggregation
3. Specialization
4. Adding suitable attributes