MCQs / Computer Science / NSCT: Databases

NSCT: Databases MCQs — Computer Science

112 multiple-choice questions on NSCT: Databases from Computer Science, each with the correct answer and, where available, a teacher's explanation. Read through them to revise the chapter, then practise the same questions one by one to test yourself.

1. Keeping copies of the same database on several servers for availability is:

  1. Sharding
  2. Denormalization
  3. Hashing
  4. Replication

Answer: D) Replication

Explanation: Replicas can take over if one fails. Lesson 4.5 · Databases

Discuss this question

2. Using the tables below, what does this query return? sql SELECT s.name, SUM(c.credits) FROM student s JOIN enrol e ON s.id = e.sid JOIN course c ON c.code = e.code GROUP BY s.name ORDER BY 2 DESC LIMIT 1; student id name dept cgpa city --------------- 1 Ali CS 3.4 Lahore 2 Sara SE 3.8 Karachi 3 Usman CS 2.9 Lahore 4 Hina AI 3.6 Islamabad 5 Bilal SE 3.1 Lahore 6 Ayesha CS 3.9 Karachi 7 Zain AI NULL Quetta enrol (sid, code, marks): (1, CS101, 78), (1, CS201, 65), (2, CS201, 88), (3, CS101, 55), (4, AI401, 91), (6, CS201, 72), (6, CS301, 80), (5, CS101, 60) course (code, title, credits): (CS101, Programming, 4), (CS201, Databases, 3), (CS301, Networks, 3), (AI401, Machine Learning, 3)

  1. (Ali, 4)
  2. (Ali, 7)
  3. (Usman, 4)
  4. (Ayesha, 6)

Answer: B) (Ali, 7)

Explanation: Running the query on the given data returns: (Ali, 7). Lesson 4.2 · Databases

Discuss this question

3. ORDER BY salary DESC sorts the result:

  1. From lowest to highest
  2. Alphabetically
  3. Randomly
  4. From highest to lowest salary

Answer: D) From highest to lowest salary

Explanation: DESC = descending. Lesson 4.2 · Databases

Discuss this question

4. Using the tables below, what does this query return? sql SELECT c.title FROM course c JOIN enrol e ON c.code = e.code GROUP BY c.title ORDER BY AVG(e.marks) DESC LIMIT 1; student id name dept cgpa city --------------- 1 Ali CS 3.4 Lahore 2 Sara SE 3.8 Karachi 3 Usman CS 2.9 Lahore 4 Hina AI 3.6 Islamabad 5 Bilal SE 3.1 Lahore 6 Ayesha CS 3.9 Karachi 7 Zain AI NULL Quetta enrol (sid, code, marks): (1, CS101, 78), (1, CS201, 65), (2, CS201, 88), (3, CS101, 55), (4, AI401, 91), (6, CS201, 72), (6, CS301, 80), (5, CS101, 60) course (code, title, credits): (CS101, Programming, 4), (CS201, Databases, 3), (CS301, Networks, 3), (AI401, Machine Learning, 3)

  1. Networks
  2. Programming
  3. Databases
  4. Machine Learning

Answer: D) Machine Learning

Explanation: Running the query on the given data returns: Machine Learning. Lesson 4.2 · Databases

Discuss this question

5. Using the tables below, what does this query return? sql SELECT name FROM student WHERE cgpa BETWEEN 3.0 AND 3.5; student id name dept cgpa city --------------- 1 Ali CS 3.4 Lahore 2 Sara SE 3.8 Karachi 3 Usman CS 2.9 Lahore 4 Hina AI 3.6 Islamabad 5 Bilal SE 3.1 Lahore 6 Ayesha CS 3.9 Karachi 7 Zain AI NULL Quetta

  1. Ali, Bilal, Sara
  2. Ali, Bilal
  3. Ali
  4. Ali, Zain

Answer: B) Ali, Bilal

Explanation: Running the query on the given data returns: Ali, Bilal. Lesson 4.2 · Databases

Discuss this question

6. A disadvantage of having many indexes on a table is:

  1. Slower SELECT queries always
  2. Loss of data
  3. No primary key allowed
  4. Slower inserts and updates

Answer: D) Slower inserts and updates

Explanation: Every index must be updated on each write. Lesson 4.5 · Databases

Discuss this question

7. Using the tables below, what does this query return? sql SELECT MAX(cgpa) FROM student WHERE dept = 'SE'; student id name dept cgpa city --------------- 1 Ali CS 3.4 Lahore 2 Sara SE 3.8 Karachi 3 Usman CS 2.9 Lahore 4 Hina AI 3.6 Islamabad 5 Bilal SE 3.1 Lahore 6 Ayesha CS 3.9 Karachi 7 Zain AI NULL Quetta

  1. 3.8
  2. 3.9
  3. 4
  4. 3.7

Answer: A) 3.8

Explanation: Running the query on the given data returns: 3.8. Lesson 4.2 · Databases

Discuss this question

8. Using the tables below, what does this query return? sql SELECT code, COUNT() FROM enrol GROUP BY code HAVING COUNT() = 3; student id name dept cgpa city --------------- 1 Ali CS 3.4 Lahore 2 Sara SE 3.8 Karachi 3 Usman CS 2.9 Lahore 4 Hina AI 3.6 Islamabad 5 Bilal SE 3.1 Lahore 6 Ayesha CS 3.9 Karachi 7 Zain AI NULL Quetta enrol (sid, code, marks): (1, CS101, 78), (1, CS201, 65), (2, CS201, 88), (3, CS101, 55), (4, AI401, 91), (6, CS201, 72), (6, CS301, 80), (5, CS101, 60) course (code, title, credits): (CS101, Programming, 4), (CS201, Databases, 3), (CS301, Networks, 3), (AI401, Machine Learning, 3)

  1. (CS101, 3); (CS201, 3); (CS301, 1)
  2. (CS101, 3); (CS201, 3)
  3. (CS201, 3)
  4. (CS101, 3)

Answer: B) (CS101, 3); (CS201, 3)

Explanation: Running the query on the given data returns: (CS101, 3); (CS201, 3). Lesson 4.2 · Databases

Discuss this question

9. Redis is mainly used as a:

  1. Graph database
  2. Key-value store and cache
  3. Data warehouse
  4. Spreadsheet

Answer: B) Key-value store and cache

Explanation: In-memory and very fast. Lesson 4.5 · Databases

Discuss this question

10. Using the tables below, what does this query return? sql SELECT COUNT() FROM student s LEFT JOIN enrol e ON s.id = e.sid WHERE e.sid IS NULL; student id name dept cgpa city --------------- 1 Ali CS 3.4 Lahore 2 Sara SE 3.8 Karachi 3 Usman CS 2.9 Lahore 4 Hina AI 3.6 Islamabad 5 Bilal SE 3.1 Lahore 6 Ayesha CS 3.9 Karachi 7 Zain AI NULL Quetta enrol (sid, code, marks): (1, CS101, 78), (1, CS201, 65), (2, CS201, 88), (3, CS101, 55), (4, AI401, 91), (6, CS201, 72), (6, CS301, 80), (5, CS101, 60) course (code, title, credits): (CS101, Programming, 4), (CS201, Databases, 3), (CS301, Networks, 3), (AI401, Machine Learning, 3)

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

Answer: D) 1

Explanation: Running the query on the given data returns: 1. Lesson 4.2 · Databases

Discuss this question

11. In the relational model, a row is called a:

  1. Domain
  2. Attribute
  3. Schema
  4. Tuple

Answer: D) Tuple

Explanation: Rows are tuples; columns are attributes. Lesson 4.1 · Databases

Discuss this question

12. Using the tables below, what does this query return? sql SELECT UPPER(SUBSTR(name, 1, 3)) FROM student WHERE id = 6; student id name dept cgpa city --------------- 1 Ali CS 3.4 Lahore 2 Sara SE 3.8 Karachi 3 Usman CS 2.9 Lahore 4 Hina AI 3.6 Islamabad 5 Bilal SE 3.1 Lahore 6 Ayesha CS 3.9 Karachi 7 Zain AI NULL Quetta

  1. AYE
  2. Aye
  3. AYESHA
  4. SHA

Answer: A) AYE

Explanation: Running the query on the given data returns: AYE. Lesson 4.2 · Databases

Discuss this question

13. The "A" in ACID stands for:

  1. Authorization
  2. Atomicity
  3. Accuracy
  4. Availability

Answer: B) Atomicity

Explanation: All or nothing. Lesson 4.4 · Databases

Discuss this question

14. The best defence against SQL injection is:

  1. Using SELECT
  2. Disabling indexes
  3. Parameterised queries (prepared statements)
  4. Longer passwords

Answer: C) Parameterised queries (prepared statements)

Explanation: User input is never concatenated into SQL. Lesson 4.5 · Databases

Discuss this question

15. Deleting the last student of a course also deletes the course information. This is a/an:

  1. Deletion anomaly
  2. Dirty read
  3. Update anomaly
  4. Insertion anomaly

Answer: A) Deletion anomaly

Explanation: Unrelated facts are lost together. Lesson 4.3 · Databases

Discuss this question

16. Using the tables below, what does this query return? sql SELECT name FROM student WHERE cgpa (SELECT AVG(cgpa) FROM student) ORDER BY name; student id name dept cgpa city --------------- 1 Ali CS 3.4 Lahore 2 Sara SE 3.8 Karachi 3 Usman CS 2.9 Lahore 4 Hina AI 3.6 Islamabad 5 Bilal SE 3.1 Lahore 6 Ayesha CS 3.9 Karachi 7 Zain AI NULL Quetta

  1. Ayesha, Hina, Sara
  2. Ayesha, Hina, Zain
  3. Ayesha, Hina
  4. Ayesha, Hina, Sara, Ali

Answer: A) Ayesha, Hina, Sara

Explanation: Running the query on the given data returns: Ayesha, Hina, Sara. Lesson 4.2 · Databases

Discuss this question

17. Using the tables below, what does this query return? sql SELECT COUNT() FROM student; student id name dept cgpa city --------------- 1 Ali CS 3.4 Lahore 2 Sara SE 3.8 Karachi 3 Usman CS 2.9 Lahore 4 Hina AI 3.6 Islamabad 5 Bilal SE 3.1 Lahore 6 Ayesha CS 3.9 Karachi 7 Zain AI NULL Quetta

  1. 7
  2. 8
  3. 9
  4. 6

Answer: A) 7

Explanation: Running the query on the given data returns: 7. Lesson 4.2 · Databases

Discuss this question

18. GRANT and REVOKE belong to:

  1. DCL
  2. DML
  3. TCL
  4. DDL

Answer: A) DCL

Explanation: Data Control Language manages permissions. Lesson 4.2 · Databases

Discuss this question

19. Joining an Employee table with itself to show each employee's manager is a:

  1. Self join
  2. Cross join
  3. Full outer join
  4. Natural join only

Answer: A) Self join

Explanation: The same table plays two roles. Lesson 4.2 · Databases

Discuss this question

20. The pattern LIKE 'Sn%' matches:

  1. Sn
  2. Asim
  3. Usman
  4. Sana

Answer: D) Sana

Explanation: S, any one character, n, then anything. Lesson 4.2 · Databases

Discuss this question

21. A many-to-many relationship between Students and Courses is implemented using:

  1. Only a foreign key in Students
  2. A separate junction table with both foreign keys
  3. An index
  4. A single table

Answer: B) A separate junction table with both foreign keys

Explanation: The junction (Enrolment) table holds both keys. Lesson 4.1 · Databases

Discuss this question

22. Which SQL command is used to retrieve data?

  1. INSERT
  2. UPDATE
  3. CREATE
  4. SELECT

Answer: D) SELECT

Explanation: SELECT queries data. Lesson 4.2 · Databases

Discuss this question

23. Which ACID property ensures concurrent transactions do not affect each other?

  1. Atomicity
  2. Durability
  3. Consistency
  4. Isolation

Answer: D) Isolation

Explanation: Each transaction behaves as if alone. Lesson 4.4 · Databases

Discuss this question

24. Using the tables below, what does this query return? sql SELECT dept, COUNT() FROM student GROUP BY dept HAVING COUNT() 2; student id name dept cgpa city --------------- 1 Ali CS 3.4 Lahore 2 Sara SE 3.8 Karachi 3 Usman CS 2.9 Lahore 4 Hina AI 3.6 Islamabad 5 Bilal SE 3.1 Lahore 6 Ayesha CS 3.9 Karachi 7 Zain AI NULL Quetta

  1. (CS, 3)
  2. (no rows)
  3. (CS, 3); (SE, 2)
  4. (CS, 3); (SE, 2); (AI, 2)

Answer: A) (CS, 3)

Explanation: Running the query on the given data returns: (CS, 3). Lesson 4.2 · Databases

Discuss this question

25. The functional dependency RollNo → Name means:

  1. RollNo is a foreign key
  2. Each RollNo determines exactly one Name
  3. Names are unique
  4. Each Name determines one RollNo

Answer: B) Each RollNo determines exactly one Name

Explanation: Same roll number always gives the same name. Lesson 4.3 · Databases

Discuss this question

Page 1 of 5 Next

Practise NSCT: Databases MCQs

More Computer Science chapters