MCQs / Computer Science / NSCT: Databases

NSCT: Databases MCQs — Computer Science (page 3)

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.

51. Using the tables below, what does this query return? sql SELECT COUNT() FROM student, course; 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. 30
  2. 27
  3. 28
  4. 29

Answer: C) 28

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

Discuss this question

52. A partial dependency (non-key attribute depending on part of a composite key) violates:

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

Answer: A) 2NF

Explanation: 2NF requires full functional dependency on the whole key. Lesson 4.3 · Databases

Discuss this question

53. Two-Phase Locking (2PL) has a growing phase and a:

  1. Logging phase
  2. Shrinking phase
  3. Commit phase
  4. Recovery phase

Answer: B) Shrinking phase

Explanation: Once a lock is released, no new locks may be acquired. Lesson 4.4 · Databases

Discuss this question

54. A virtual table based on a stored query is a:

  1. View
  2. Index
  3. Sequence
  4. Trigger

Answer: A) View

Explanation: Views simplify queries and restrict access. Lesson 4.2 · Databases

Discuss this question

55. Using the tables below, what does this query return? sql SELECT dept FROM student GROUP BY dept ORDER BY AVG(cgpa) 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

  1. SE
  2. AI
  3. NULL
  4. CS

Answer: B) AI

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

Discuss this question

56. The SQL command that shows how the database will execute a query is:

  1. EXPLAIN
  2. SHOW USERS
  3. DESCRIBE TABLE
  4. ANALYZE PASSWORD

Answer: A) EXPLAIN

Explanation: It reveals scans, joins and index use. Lesson 4.5 · Databases

Discuss this question

57. Using the tables below, what does this query return? sql SELECT title FROM course WHERE code NOT IN (SELECT code FROM enrol); 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. (no rows)
  2. Programming, Databases
  3. Machine Learning
  4. Networks

Answer: A) (no rows)

Explanation: Running the query on the given data returns: (no rows). Lesson 4.2 · Databases

Discuss this question

58. Using the tables below, what does this query return? sql SELECT COUNT() FROM student WHERE city = 'Lahore'; 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
  2. 4
  3. 5
  4. 2

Answer: A) 3

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

Discuss this question

59. An attribute like Age that can be calculated from DateOfBirth is a:

  1. Multivalued attribute
  2. Derived attribute
  3. Composite attribute
  4. Key attribute

Answer: B) Derived attribute

Explanation: Shown with a dashed ellipse. Lesson 4.1 · Databases

Discuss this question

60. Using the tables below, what does this query return? sql SELECT city FROM student GROUP BY city ORDER BY COUNT() 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

  1. Karachi
  2. Lahore
  3. Islamabad
  4. Quetta

Answer: B) Lahore

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

Discuss this question

61. After a crash, a committed transaction whose changes were not yet written to disk must be:

  1. Deleted from the log
  2. Ignored
  3. Redone
  4. Undone

Answer: C) Redone

Explanation: Durability requires redo of committed work. Lesson 4.4 · Databases

Discuss this question

62. A transitive dependency (non-key attribute depending on another non-key attribute) violates:

  1. BCNF only
  2. 2NF only
  3. 1NF
  4. 3NF

Answer: D) 3NF

Explanation: 3NF removes non-key → non-key dependencies. Lesson 4.3 · Databases

Discuss this question

63. Referential integrity ensures that:

  1. Rows are sorted
  2. A foreign key value matches an existing primary key or is null
  3. Passwords are encrypted
  4. Every column is unique

Answer: B) A foreign key value matches an existing primary key or is null

Explanation: No orphan references are allowed. Lesson 4.1 · Databases

Discuss this question

64. Using the tables below, what does this query return? sql SELECT s.name 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. Zain, Ali
  2. Ali
  3. Sara
  4. Zain

Answer: D) Zain

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

Discuss this question

65. In an ER diagram, a relationship is drawn as a:

  1. Double ellipse
  2. Rectangle
  3. Diamond
  4. Ellipse

Answer: C) Diamond

Explanation: Rectangles are entities; ellipses are attributes. Lesson 4.1 · Databases

Discuss this question

66. Using the tables below, what does this query return? sql SELECT SUM(marks) FROM enrol WHERE code = 'CS101'; 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. 194
  2. 195
  3. 192
  4. 193

Answer: D) 193

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

Discuss this question

67. An entity that cannot be identified by its own attributes alone is a:

  1. Weak entity
  2. Associative attribute
  3. Strong entity
  4. Derived entity

Answer: A) Weak entity

Explanation: It depends on an owner entity (double rectangle). Lesson 4.1 · Databases

Discuss this question

68. Deliberately adding redundancy to speed up read-heavy reporting is called:

  1. Normalization
  2. Denormalization
  3. Partitioning only
  4. Indexing

Answer: B) Denormalization

Explanation: Common in data warehouses. Lesson 4.3 · Databases

Discuss this question

69. Which keyword removes duplicate values from the result of a SELECT?

  1. DISTINCT
  2. UNIQUE
  3. GROUP
  4. DIFFERENT

Answer: A) DISTINCT

Explanation: SELECT DISTINCT city FROM customer. Lesson 4.2 · Databases

Discuss this question

70. Transaction T1 waits for a lock held by T2, while T2 waits for a lock held by T1. This is a:

  1. Lost update
  2. Dirty read
  3. Deadlock
  4. Phantom

Answer: C) Deadlock

Explanation: Neither can proceed; one must be aborted. Lesson 4.4 · Databases

Discuss this question

71. COMMIT and ROLLBACK belong to:

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

Answer: A) TCL

Explanation: Transaction Control Language. Lesson 4.2 · Databases

Discuss this question

72. The main purpose of an index is to:

  1. Encrypt data
  2. Remove duplicates
  3. Back up the table
  4. Speed up data retrieval

Answer: D) Speed up data retrieval

Explanation: Like a book index. Lesson 4.5 · Databases

Discuss this question

73. Using the tables below, what does this query return? sql SELECT COUNT() FROM student WHERE NOT (dept = 'CS' OR city = 'Lahore'); 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
  2. 4
  3. 2
  4. 5

Answer: A) 3

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

Discuss this question

74. A LEFT JOIN returns:

  1. All rows from the left table and matching rows from the right (NULLs where none)
  2. Only matching rows
  3. All rows from the right table
  4. Only unmatched rows

Answer: A) All rows from the left table and matching rows from the right (NULLs where none)

Explanation: The left side is fully preserved. Lesson 4.2 · Databases

Discuss this question

75. Using the tables below, what does this query return? sql SELECT name FROM student WHERE name LIKE '%a'; 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. Sara, Hina, Ayesha
  2. Sara, Hina
  3. Sara, Hina, Ayesha, Ali
  4. Sara, Hina, Zain

Answer: A) Sara, Hina, Ayesha

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

Discuss this question

Previous Page 3 of 5 Next

Practise NSCT: Databases MCQs

More Computer Science chapters