DBMS and SQL Quiz

Processed and meaningful data is called:

• A) Data
• B) Field
• C) Information ✔
• D) Record

Why: Information is data that has been processed and organised so it has meaning. Example: a list of marks is data; '45 students passed' is information.

An organised collection of related data is a:

• A) Spreadsheet only
• B) Folder
• C) Program
• D) Database ✔

Why: A database is an organised collection of related data, such as all staff records of an office, stored so it can be searched and updated easily.

Software used to create and manage databases is a:

• A) DBMS ✔
• B) Word processor
• C) Browser
• D) Compiler

Why: A DBMS (Database Management System) is software to create, store, update and query databases, such as MySQL, Oracle or MS Access.

Which is a DBMS?

• A) MS Paint
• B) MySQL ✔
• C) Notepad
• D) WinRAR

Why: MySQL is a popular open-source relational DBMS. Other examples are Oracle, SQL Server, PostgreSQL and MS Access.

In a relational database, a table is also called a:

• A) Tuple
• B) Domain
• C) Relation ✔
• D) Schema only

Why: In the relational model (E. F. Codd), a table is called a relation: a set of rows with the same columns.

A row in a table is called a:

• A) Field
• B) Attribute
• C) Key
• D) Record (tuple) ✔

Why: A row is a record (tuple): all the information about one item, for example one employee.

A column in a table is called a:

• A) Field (attribute) ✔
• B) Record
• C) Tuple
• D) Row

Why: A column is a field (attribute): one type of information, such as Name or Salary, for every record.

A key that uniquely identifies each record is the:

• A) Foreign key
• B) Primary key ✔
• C) Composite key only
• D) Secondary index

Why: The primary key uniquely identifies each record, such as Employee_ID. No two rows can have the same primary key value.

A primary key field cannot be:

• A) Numeric
• B) Text
• C) Empty or duplicate ✔
• D) Indexed

Why: A primary key must be unique and NOT NULL, so it cannot be left empty or repeated.

A field that refers to the primary key of another table is a:

• A) Primary key
• B) Candidate key
• C) Super key
• D) Foreign key ✔

Why: A foreign key is a field in one table that refers to the primary key of another, for example Dept_ID in an Employee table. It links the tables.

A key made of two or more fields is a:

• A) Composite key ✔
• B) Foreign key
• C) Single key
• D) Alternate key only

Why: A composite key uses two or more fields together to identify a record, for example Student_ID + Subject_Code in a marks table.

Any field that could serve as the primary key is a:

• A) Foreign key
• B) Candidate key ✔
• C) Composite key
• D) Dummy key

Why: A candidate key is any field (or set of fields) that could uniquely identify records. One is chosen as the primary key; the others are alternate keys.

One department having many employees is a:

• A) One-to-one relationship
• B) Many-to-many relationship
• C) One-to-many relationship ✔
• D) No relationship

Why: One department has many employees, but each employee belongs to one department, so it is a one-to-many (1:M) relationship.

Many students taking many subjects is a:

• A) One-to-one relationship
• B) One-to-many relationship
• C) No relationship
• D) Many-to-many relationship ✔

Why: Each student takes many subjects and each subject has many students, so it is many-to-many (M:N). It needs a linking table such as Enrolment.

One person having one citizenship number is a:

• A) One-to-one relationship ✔
• B) One-to-many relationship
• C) Many-to-many relationship
• D) Recursive relationship

Why: Each person has exactly one citizenship number and each number belongs to one person, so it is one-to-one (1:1).

Repetition of the same data in many places is:

• A) Data integrity
• B) Data redundancy ✔
• C) Data security
• D) Data independence

Why: Data redundancy is the same data stored many times. It wastes space and causes errors when one copy is updated and others are not.

The process of organising tables to reduce redundancy is:

• A) Compilation
• B) Formatting
• C) Normalisation ✔
• D) Encryption

Why: Normalisation splits data into well-designed related tables (1NF, 2NF, 3NF …) to reduce redundancy and update problems.

First normal form (1NF) requires:

• A) No primary key
• B) Duplicate rows
• C) Only one table
• D) Atomic (single) values in each field ✔

Why: 1NF requires each field to hold a single (atomic) value, with no lists like 'Nepali, English' in one cell, and no repeating groups.

Accuracy and consistency of data is called:

• A) Data integrity ✔
• B) Data redundancy
• C) Data mining
• D) Data entry

Why: Data integrity means data stays accurate and consistent, for example through primary keys, foreign keys and validation rules.

SQL stands for:

• A) Simple Question Language
• B) Structured Query Language ✔
• C) System Query Logic
• D) Sequential Query List

Why: SQL is Structured Query Language, the standard language used to create, query and manage relational databases.

The SQL command to retrieve data is:

• A) INSERT
• B) UPDATE
• C) SELECT ✔
• D) CREATE

Why: SELECT retrieves data, for example SELECT name FROM staff; It belongs to the query part of SQL (often grouped with DML).

The SQL command to add a new record is:

• A) SELECT
• B) ALTER
• C) GRANT
• D) INSERT ✔

Why: INSERT adds new rows, for example INSERT INTO staff (id, name) VALUES (1, 'Ram');

The SQL command to change existing data is:

• A) UPDATE ✔
• B) INSERT
• C) DROP
• D) SELECT

Why: UPDATE changes existing rows, for example UPDATE staff SET salary = 40000 WHERE id = 1; Without WHERE, every row changes.

The SQL command to remove records is:

• A) DROP
• B) DELETE ✔
• C) REMOVE
• D) ERASE

Why: DELETE removes rows, for example DELETE FROM staff WHERE id = 5; The table itself stays.

The SQL command to create a new table is:

• A) INSERT
• B) SELECT
• C) CREATE ✔
• D) GRANT

Why: CREATE TABLE defines a new table with its columns and data types, for example CREATE TABLE staff (id INT, name VARCHAR(50));

The SQL command to change a table's structure is:

• A) UPDATE
• B) INSERT
• C) COMMIT
• D) ALTER ✔

Why: ALTER TABLE changes a table's structure, such as adding, changing or removing a column.

The SQL command to delete a whole table is:

• A) DROP ✔
• B) DELETE
• C) TRUNCATE only
• D) REMOVE

Why: DROP TABLE deletes the whole table, its structure and all data permanently.

CREATE, ALTER and DROP belong to:

• A) DML
• B) DDL ✔
• C) DCL
• D) TCL

Why: CREATE, ALTER, DROP (and TRUNCATE) are DDL (Data Definition Language): they define or change the database structure.

INSERT, UPDATE and DELETE belong to:

• A) DDL
• B) DCL
• C) DML ✔
• D) TCL

Why: INSERT, UPDATE and DELETE are DML (Data Manipulation Language): they change the data inside tables.

GRANT and REVOKE belong to:

• A) DML
• B) DDL
• C) TCL
• D) DCL ✔

Why: GRANT and REVOKE are DCL (Data Control Language): they give or remove users' permissions.

COMMIT and ROLLBACK belong to:

• A) TCL ✔
• B) DDL
• C) DML
• D) DCL

Why: COMMIT, ROLLBACK and SAVEPOINT are TCL (Transaction Control Language): they control whether a group of changes is saved or cancelled.

The SQL clause used to filter records is:

• A) ORDER BY
• B) WHERE ✔
• C) GROUP BY
• D) FROM

Why: WHERE keeps only rows that meet a condition, for example SELECT * FROM staff WHERE district = 'Kaski';

The SQL clause used to sort results is:

• A) WHERE
• B) GROUP BY
• C) ORDER BY ✔
• D) HAVING

Why: ORDER BY sorts results, ascending (ASC, the default) or descending (DESC), for example ORDER BY salary DESC.

The SQL clause used to group rows is:

• A) ORDER BY
• B) WHERE
• C) JOIN
• D) GROUP BY ✔

Why: GROUP BY groups rows with the same value so you can total them, for example SELECT dept, COUNT(*) FROM staff GROUP BY dept;

The SQL function that counts rows is:

• A) COUNT() ✔
• B) SUM()
• C) AVG()
• D) MAX()

Why: COUNT() returns the number of rows, for example SELECT COUNT(*) FROM staff; gives the total number of staff.

The SQL function that gives the average is:

• A) SUM()
• B) AVG() ✔
• C) COUNT()
• D) MIN()

Why: AVG() returns the average (mean) of a numeric column, for example SELECT AVG(salary) FROM staff;

The SQL keyword to combine rows from two tables is:

• A) UNION ALL only
• B) MERGE ONLY
• C) JOIN ✔
• D) LINK

Why: JOIN combines rows from related tables using a common field, for example staff JOIN department ON staff.dept_id = department.id.

ROLLBACK is used to:

• A) Save changes
• B) Create a table
• C) Grant rights
• D) Undo uncommitted changes ✔

Why: ROLLBACK cancels the changes made since the last COMMIT, so a failed or wrong transaction leaves no half-finished data.

COMMIT is used to:

• A) Save transaction changes permanently ✔
• B) Undo changes
• C) Delete a table
• D) Sort data

Why: COMMIT saves all the changes of the current transaction permanently in the database.

Giving a user permission on a table is done with:

• A) REVOKE
• B) GRANT ✔
• C) ALTER
• D) DROP

Why: GRANT gives privileges, for example GRANT SELECT ON staff TO clerk; lets the user clerk read the staff table.

Removing a user's permission is done with:

• A) GRANT
• B) DELETE
• C) REVOKE ✔
• D) TRUNCATE

Why: REVOKE takes back privileges given earlier with GRANT.

Which is an advantage of a DBMS?

• A) More paperwork
• B) Data cannot be shared
• C) No backup possible
• D) Reduced redundancy and better security ✔

Why: A DBMS stores data centrally, so it reduces redundancy, keeps data consistent, controls access (security) and supports backup and recovery.

The overall design or structure of a database is its:

• A) Schema ✔
• B) Record
• C) Tuple
• D) Report

Why: The schema is the overall design of a database: its tables, fields, data types and relationships.

A person who manages a database system is a:

• A) Typist
• B) Database Administrator (DBA) ✔
• C) Network cable
• D) Compiler

Why: A Database Administrator (DBA) manages the database: users and permissions, security, performance, backup and recovery.

Keeping a copy of data to restore after loss is:

• A) Normalisation
• B) Indexing
• C) Backup ✔
• D) Querying

Why: A backup is a copy of the data kept separately, so it can be restored after deletion, disk failure or virus attack.

An index in a database mainly helps to:

• A) Delete data
• B) Encrypt data
• C) Print reports
• D) Speed up searching ✔

Why: An index works like the index of a book: it lets the database find rows quickly without reading the whole table.

SELECT * FROM student; returns:

• A) All columns of all rows in student ✔
• B) Only the first row
• C) Only column names
• D) Nothing

Why: The * means all columns, and with no WHERE clause all rows are returned, so this shows the whole student table.

A query that shows employees with salary above 30000 needs:

• A) ORDER BY salary
• B) WHERE salary > 30000 ✔
• C) GROUP BY salary
• D) COUNT(salary)

Why: Use WHERE with a condition: SELECT * FROM employee WHERE salary > 30000;

A field value that is unknown or missing is called:

• A) Zero
• B) Blank key
• C) NULL ✔
• D) Primary

Why: NULL means no value (unknown or missing). It is not the same as 0 or an empty text.

Removing all rows but keeping the table structure is done by:

• A) DROP
• B) ALTER
• C) CREATE
• D) TRUNCATE ✔

Why: TRUNCATE TABLE removes all rows quickly but keeps the table structure. DROP removes the table too, and DELETE removes rows one by one.