CHAPTER9_QA December 2, 2025

MULTIPLE CHOICE QUESTIONS (MCQs)

1. What does SQL stand for?

a) Structured Question Language
b) Simple Query Language
c) Structured Query Language
d) System Query Language
Answer: c

R
2. Which of the following is not a type of RDBMS?

L
a) MySQL
b) Oracle

N
c) MongoDB

EE
d) PostgreSQL
Answer: c

3. SQL is case sensitive in which environment?

a) Windows H
AT
b) Linux
c) Mac
M

d) Android
Answer: b
ED

4. Which of the following is a correct SQL statement to create a database named Library?
a) MAKE DATABASE Library;
M

b) NEW DATABASE Library;

c) CREATE DATABASE Library;
M

d) DATABASE Library;
Answer: c
A

5. Which command is used to list all databases in MySQL?

H

a) LIST DATABASES;
O

b) SHOW ALL DATABASES;

c) DISPLAY DATABASES;
M

d) SHOW DATABASES;
Answer: d

6. What is the purpose of the ‘USE’ command in SQL?

a) Create a new table
b) Delete a database
c) Select a database for use

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
4
CHAPTER9_QA December 2, 2025

d) Exit SQL
Answer: c

7. Which of the following is a valid datatype for storing dates in MySQL?

a) DATE
b) DATETIME
c) TIME
d) All of the above

R
Answer: d

8. CHAR(n) datatype in MySQL stores data of:

L
a) Fixed length

N
b) Variable length
c) Integer

EE
d) Binary type
Answer: a

H
9. Which constraint ensures uniqueness of values in a column?
AT
a) PRIMARY KEY
b) FOREIGN KEY
M

c) UNIQUE
d) DEFAULT
Answer: c
ED

10. Which two constraints together can form a primary key?

a) UNIQUE and DEFAULT
M

b) NOT NULL and UNIQUE

c) FOREIGN KEY and UNIQUE
M

d) NOT NULL and DEFAULT

A

Answer: b
H

11. What does the ALTER TABLE command do in SQL?

a) Deletes a table
O

b) Changes the structure of a table

M

c) Adds data to a table

d) Queries data
Answer: b

12. Which keyword is used to remove a column from a table?

a) REMOVE
b) DELETE
c) DROP

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
5
CHAPTER9_QA December 2, 2025

d) ALTER
Answer: c

13. Which command is used to view the structure of a table?

a) SHOW tablename
b) DESCRIBE tablename
c) VIEW tablename
d) PRINT tablename

R
Answer: b

14. What type of key is formed by combining multiple attributes?

L
a) Foreign key

N
b) Alternate key
c) Composite key

EE
d) Candidate key
Answer: c

H
15. What happens if you run an UPDATE statement without a WHERE clause?
AT
a) Nothing happens
b) Syntax error
M

c) All records are updated

d) Only the first record is updated
Answer: c
ED

16. Which statement is used to insert data into a table?

a) ADD INTO
M

b) INSERT INTO
c) PUT INTO
M

d) ENTER INTO
A

Answer: b
H

17. In which format is the DATE stored in MySQL?

a) DD-MM-YYYY
O

b) MM-DD-YYYY
M

c) YYYY-MM-DD
d) Any format
Answer: c

18. Which operator is used to compare a value with a set of values?

a) IN
b) LIKE
c) BETWEEN

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
6
CHAPTER9_QA December 2, 2025

d) ALL
Answer: a

19. Which SQL function returns the remainder of a division operation?

a) DIV
b) REM
c) MOD
d) ROUND

R
Answer: c

20. Which function is used to calculate exponential power in SQL?

L
a) EXP()

N
b) ROUND()
c) POW()

EE
d) SQRT()
Answer: c

H
21. Which clause is used to sort records in ascending or descending order?
AT
a) GROUP BY
b) ORDER BY
M

c) SORT BY
d) ARRANGE BY
Answer: b
ED

22. Which keyword is used with ORDER BY to sort in descending order?

a) REVERSE
M

b) DOWN
c) DESC
M

d) LOWER
A

Answer: c
H

23. Which function returns the name of the day from a date?
a) DAYNAME()
O

b) DAY()
M

c) DATENAME()
d) DAYOFWEEK()
Answer: a

24. Which clause helps in fetching only unique values from a column?
a) ONLY
b) UNIQUE
c) DISTINCT

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
7
CHAPTER9_QA December 2, 2025

d) NON-REPEATED
Answer: c

25. What is the result of any arithmetic operation with NULL?

a) 0
b) NULL
c) Error
d) Infinity

R
Answer: b

26. Which function returns current date and time in SQL?

L
a) CURRENT()

N
b) NOW()
c) TIME()

EE
d) SYSDATE()
Answer: b

H
27. Which of the following is used for pattern matching in SQL?
AT
a) LIKE
b) IN
M

c) AND
d) IS
Answer: a
ED

28. Which symbol is used to match any number of characters in SQL pattern matching?
a) _
M

b) %
c) *
M

d) #
A

Answer: b
H

29. Which SQL function removes leading spaces from a string?

a) RTRIM()
O

b) LTRIM()
M

c) TRIM()
d) REMOVE()
Answer: b

30. Which function converts string to uppercase in SQL?

a) UPPER()
b) UCASE()
c) TOUPPER()

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
8
CHAPTER9_QA December 2, 2025

d) CAP()
Answer: b

31. Which clause is used to group rows that have the same values?
a) ORDER BY
b) GROUP BY
c) SET BY
d) UNION

R
Answer: b

32. Which function returns the number of characters in a string?

L
a) LEN()

N
b) LENGTH()
c) SIZE()

EE
d) COUNT()
Answer: b

33. Which clause is used to filter rows before grouping?

H
AT
a) WHERE
b) HAVING
M

c) FILTER
d) SELECT
Answer: a
ED

34. Which clause is used to filter groups after grouping?

a) WHERE
M

b) HAVING
c) GROUP BY
M

d) FILTER
A

Answer: b
H

35. Which aggregate function returns the highest value in a column?

a) MAX()
O

b) TOP()
M

c) LAST()
d) HIGH()
Answer: a

36. Which clause in SQL is used to group records that have the same values in specified columns?
a) ORDER BY
b) GROUP BY
c) WHERE

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
9
CHAPTER9_QA December 2, 2025

d) HAVING
Answer: b

37. Which operator is used to test for NULL values?

a) = NULL
b) == NULL
c) IS NULL
d) EQUAL NULL

R
Answer: c

38. What will be the result of 5 + NULL in SQL?

L
a) 5

N
b) 0
c) NULL

EE
d) Error
Answer: c

39. Which function extracts a substring from a string?

H
AT
a) MID()
b) SUBSTRING()
M

c) SLICE()
d) CUT()
Answer: a
ED

40. What is the output of ROUND(123.456, 1)?

a) 123.4
M

b) 123.5
c) 123.45
M

d) 123
A

Answer: b
H

41. Which keyword is used to permanently remove a table from a database?

a) ERASE
O

b) DELETE
M

c) REMOVE
d) DROP
Answer: d

42. What is the output of MOD(11, 3)?

a) 2
b) 3
c) 1

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
10
CHAPTER9_QA December 2, 2025

d) 0
Answer: a

43. What is the purpose of the DEFAULT constraint?

a) Allows duplicate values
b) Adds a default value when no input is given
c) Restricts NULL values
d) Sets a foreign key

R
Answer: b

44. Which operator is used to match a value within a range?

L
a) IN

N
b) RANGE
c) BETWEEN

EE
d) BOUND
Answer: c

45. Which one is not a single row function?

H
AT
a) ROUND()
b) MOD()
M

c) AVG()
d) LCASE()
Answer: c
ED

46. Which function is used to get the month from a date?

a) MONTHNAME()
M

b) MONTH()
c) GETMONTH()
M

d) MTH()
A

Answer: b
H

47. Which of the following is a valid constraint that can be applied to enforce referential integrity
between two tables?
O

a) DEFAULT
M

b) PRIMARY KEY
c) UNIQUE
d) FOREIGN KEY
Answer: d

48. Which of the following conditions must be satisfied for a FOREIGN KEY to be added to a
table?
a) The table must have a DATE column

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
11
CHAPTER9_QA December 2, 2025

b) The referenced attribute must be of FLOAT type

c) The referenced column must be a PRIMARY KEY
d) The referenced table must be empty
Answer: c

49. Which SQL operator is used to check whether a column value falls between two values (inclu-
sive)?
a) IN

R
b) LIKE
c) RANGE

L
d) BETWEEN
Answer: d

N
50. Which keyword is used to delete a particular row in SQL?

EE
a) REMOVE
b) ERASE
c) DELETE
H
AT
d) DROP
Answer: c
M

51. Assertion (A): SQL is used only for retrieving data from databases. Reason (R): SQL stands
for Structured Query Language.
a) Both A and R are true, and R is the correct explanation of A
ED

b) Both A and R are true, but R is not the correct explanation of A

c) A is false, but R is true
M

d) A is true, but R is false

Answer: c
M

52. Assertion (A): VARCHAR stores variable-length character strings. Reason (R): CHAR and
A

VARCHAR allocate the same amount of memory for any string.

a) Both A and R are true, and R is the correct explanation of A
H

b) Both A and R are true, but R is not the correct explanation of A

O

c) A is true, but R is false

d) A is false, but R is true
M

Answer: c

53. Assertion (A): PRIMARY KEY allows duplicate values in a column. Reason (R): PRIMARY
KEY is used to uniquely identify each record.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is false, but R is true

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
12
CHAPTER9_QA December 2, 2025

d) A is true, but R is false

Answer: c

54. Assertion (A): ALTER TABLE statement can be used to add a new column. Reason (R): ALTER
TABLE can modify the structure of an existing table.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is false, but R is true

R
d) A is true, but R is false
Answer: a

L
55. Assertion (A): The keyword DESC is used to sort records in descending order. Reason (R): The

N
ORDER BY clause displays records in random order by default.
a) Both A and R are true, and R is the correct explanation of A

EE
b) Both A and R are true, but R is not the correct explanation of A
c) A is true, but R is false
d) A is false, but R is true
H
AT
Answer: c

56. Assertion (A): SQL is a case-insensitive language. Reason (R): In SQL, ‘salary’ and ‘SALARY’
M

are treated as the same column names.

a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
ED

c) A is false, but R is true

d) A is true, but R is false
M

Answer: a

57. Assertion (A): The CREATE TABLE command is used to define the structure of a new table.
M

Reason (R): CREATE TABLE can define attribute names, data types, and constraints.
A

a) Both A and R are true, and R is the correct explanation of A

b) Both A and R are true, but R is not the correct explanation of A
H

c) A is true, but R is false

O

d) A is false, but R is true

Answer: a
M

58. Assertion (A): DEFAULT constraint ensures that every column must have a unique value.
Reason (R): DEFAULT sets a predefined value for a column when no value is provided.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is false, but R is true
d) A is true, but R is false

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
13
CHAPTER9_QA December 2, 2025

Answer: c

59. Assertion (A): DELETE statement removes a table from the database. Reason (R): DROP
TABLE statement is used to remove a table permanently.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is false, but R is true
d) A is true, but R is false

R
Answer: c

60. Assertion (A): The WHERE clause in SQL is used to filter groups formed by GROUP BY.

L
Reason (R): HAVING clause filters records before they are grouped.

N
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A

EE
c) A is false, but R is true
d) Both A and R are false
Answer: d
H
AT
61. Assertion (A): INSERT INTO command adds new records into a table. Reason (R): Records
can be inserted without specifying column names if all values are provided in correct order.
M

a) Both A and R are true, and R is the correct explanation of A

b) Both A and R are true, but R is not the correct explanation of A
c) A is true, but R is false
ED

d) A is false, but R is true

Answer: a
M

62. Assertion (A): A FOREIGN KEY can reference a column that is not a primary key. Reason
(R): A foreign key ensures that each value exists in another table’s primary key column.
M

a) Both A and R are true, and R is the correct explanation of A

A

b) A is true, but R is false

c) A is false, but R is true
H

d) Both A and R are false

O

Answer: c
M

63. Assertion (A): ROUND() is used to round numbers to specified decimal places. Reason (R):
ROUND(283.2, 0) will return 283.2.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is true, but R is false
d) A is false, but R is true
Answer: c

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
14
CHAPTER9_QA December 2, 2025

64. Assertion (A): The % symbol in SQL is used to match zero or more characters. Reason (R):
The LIKE clause in SQL supports pattern matching using wildcards.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is true, but R is false
d) A is false, but R is true
Answer: a

R
65. Assertion (A): The BETWEEN operator is inclusive of boundary values. Reason (R): BE-
TWEEN is used to test if a value lies between a specified range.

L
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A

N
c) A is true, but R is false

EE
d) A is false, but R is true
Answer: a

H
66. Assertion (A): You can use the alias keyword AS to rename a column in the output. Reason
AT
(R): Column aliasing affects the actual table structure permanently.
a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
M

c) A is true, but R is false

d) A is false, but R is true
ED

Answer: c

67. Assertion (A): NOW() function returns the current system date and time. Reason (R): NOW()
M

is a date function used in MySQL.

a) Both A and R are true, and R is the correct explanation of A
M

b) Both A and R are true, but R is not the correct explanation of A

c) A is false, but R is true
A

d) A is true, but R is false

H

Answer: a
O

68. Assertion (A): MID() function is used to extract a part of a string. Reason (R): MID() is a
numeric function used to perform arithmetic operations.
M

a) Both A and R are true, and R is the correct explanation of A

b) Both A and R are true, but R is not the correct explanation of A
c) A is true, but R is false
d) A is false, but R is true
Answer: c

69. Assertion (A): The TRIM() function is used to remove trailing spaces only. Reason (R): TRIM()

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
15
CHAPTER9_QA December 2, 2025

removes both leading and trailing spaces from a string.

a) Both A and R are true, and R is the correct explanation of A
b) Both A and R are true, but R is not the correct explanation of A
c) A is true, but R is false
d) A is false, but R is true
Answer: d

70. Assertion (A): The IS NULL operator is used to check for zero values. Reason (R): IS NULL

R
checks whether a column has NULL as its value.
a) Both A and R are true, and R is the correct explanation of A

L
b) Both A and R are true, but R is not the correct explanation of A
c) A is false, but R is true

N
d) A is true, but R is false

EE
Answer: c

FILL IN THE BLANKS

H
AT
1. SQL stands for __________.
Answer: Structured Query Language
M

2. The clause used to sort records in SQL is __________.

Answer: ORDER BY
ED

3. The __________ key uniquely identifies each record in a table.

Answer: Primary
M

4. To insert new records in a table, we use the __________ statement.

M

Answer: INSERT INTO

5. In MySQL, date values are stored in the format __________.

A

Answer: YYYY-MM-DD
H

6. The __________ clause is used to filter records before grouping.

O

Answer: WHERE
M

7. The __________ clause is used to filter grouped records.

Answer: HAVING

8. The wildcard character __________ represents zero or more characters in SQL.

Answer: %

9. To display all columns from a table, we use the symbol __________ in a SELECT statement.
Answer: *

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
16
CHAPTER9_QA December 2, 2025

10. The command to remove an entire table from the database is __________.
Answer: DROP TABLE

11. To change the structure of a table, we use the __________ statement.

Answer: ALTER TABLE

12. The __________ constraint ensures a column cannot have NULL values.
Answer: NOT NULL

13. The __________ function returns the current date and time.

R
Answer: NOW()

L
14. To define a column with fixed length string data, we use the datatype __________.
Answer: CHAR

N
15. The __________ datatype stores numeric values with decimal points.

EE
Answer: FLOAT

16. The __________ function is used to round off numbers in SQL.

Answer: ROUND()
H
AT
17. __________ is a special value in SQL used to represent missing or unknown information.
Answer: NULL
M

18. The __________ constraint automatically fills in a column with a predefined value when no
input is given.
ED

Answer: DEFAULT

19. To rename a column in the output of a SELECT query, we use the keyword __________.
M

Answer: AS

20. The __________ operator is used to check if a column value is within a set of values.
M

Answer: IN
A

21. The __________ function returns the number of characters in a string.

H

Answer: LENGTH()
O

22. The __________ function removes both leading and trailing spaces from a string.
Answer: TRIM()
M

23. A composite primary key consists of __________ or more columns.

Answer: two

24. The __________ command is used to view the structure of an existing table.
Answer: DESCRIBE

25. The __________ clause is used with SELECT to retrieve only unique values.
Answer: DISTINCT

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
17
CHAPTER9_QA December 2, 2025

26. The __________ constraint ensures all values in a column are different.
Answer: UNIQUE

27. The __________ operator is used to retrieve records where a column value lies within a specific
range.
Answer: BETWEEN

28. The __________ function extracts a part of a string.

Answer: MID()

R
29. A column that refers to a primary key in another table is called a __________ key.

L
Answer: foreign

30. The __________ command permanently removes all records from a table and the table structure

N
itself.

EE
Answer: DROP

2 MARKS QUESTIONS
H
AT
1. What is SQL?
Answer: SQL stands for Structured Query Language. It is a language used to create, manipulate, and
M

retrieve data from relational databases like MySQL, Oracle, SQL Server, etc.

2. Why is SQL considered easy to learn?

ED

Answer: SQL is considered easy because its statements are made up of descriptive English words and it
is case-insensitive.
M

3. What is the difference between CHAR and VARCHAR datatypes?

Answer: CHAR is a fixed-length datatype that pads unused characters with spaces, while VARCHAR is
M

a variable-length datatype that stores only the required number of characters.

A

4. What is the significance of constraints in SQL?

H

Answer: Constraints enforce rules on data in a table to ensure accuracy and integrity, such as NOT NULL,
UNIQUE, PRIMARY KEY, FOREIGN KEY, and DEFAULT.
O

5. Give the syntax and an example to create a database named StudentAttendance.

M

Answer:
Syntax: CREATE DATABASE databasename;
Example: CREATE DATABASE StudentAttendance;

6. Differentiate between PRIMARY KEY and FOREIGN KEY.

Answer: A PRIMARY KEY uniquely identifies each record and does not allow NULLs, while a FOR-
EIGN KEY is used to link two tables and refers to the PRIMARY KEY of another table.

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
18
CHAPTER9_QA December 2, 2025

7. Write the syntax to create a table in SQL and give an example.

Answer:
Syntax:
CREATE TABLE tablename (column1 datatype constraint, column2 datatype constraint,
...);
Example:

CREATE TABLE STUDENT(

R
RollNumber INT PRIMARY KEY,
SName VARCHAR(20) NOT NULL,

L
SDateofBirth DATE,
GUID CHAR(12)

N
);

EE
8. What is the use of the DESCRIBE command in SQL?
Answer: The DESCRIBE or DESC command is used to view the structure of a table including column
names, data types, and constraints.
H
AT
9. What is a composite primary key? Give an example.
Answer: A composite primary key consists of two or more columns used together to uniquely identify a
record.
M

Example:
PRIMARY KEY (AttendanceDate, RollNumber);
ED

10. Give an example to add a foreign key in the STUDENT table.

Answer:
M

ALTER TABLE STUDENT

ADD FOREIGN KEY(GUID) REFERENCES GUARDIAN(GUID);
M

11. How do you add a new column to an existing table? Give syntax and example.
A

Answer:
H

Syntax: ALTER TABLE tablename ADD column_name datatype;

Example:
O

ALTER TABLE GUARDIAN ADD Income INT;

M

12. Differentiate between DELETE and DROP commands in SQL.

Answer: DELETE removes rows from a table based on a condition but retains the table structure, whereas
DROP permanently deletes the entire table and its structure.

13. Write the syntax to insert data into a table and give an example.
Answer:
Syntax: INSERT INTO tablename VALUES (value1, value2, ...);

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
19
CHAPTER9_QA December 2, 2025

Example:
INSERT INTO STUDENT VALUES (1, 'Atharv Ahuja', '2003-05-15', 444444444444);
14. What is the use of the SELECT statement? Give a simple example.
Answer: SELECT is used to retrieve data from a table.
Example:
SELECT * FROM STUDENT;
15. What is the purpose of the DISTINCT clause in a SELECT query?

R
Answer: DISTINCT is used to return only unique (non-duplicate) values in the output of a SELECT
statement.

L
16. Give the syntax of the ORDER BY clause and state its default order.

N
Answer:
Syntax: SELECT * FROM tablename ORDER BY column_name [ASC|DESC];

EE
Default order is ascending (ASC).
17. What is the difference between WHERE and HAVING clause?
H
Answer: WHERE filters rows before grouping, while HAVING filters groups formed by GROUP BY.
AT
18. Write a query to display employee names and their annual income (Salary * 12), using column
aliases.
M

Answer:

SELECT Ename AS Name, Salary*12 AS 'Annual Income'

ED

FROM EMPLOYEE;

19. What does the LIKE operator do in SQL? Give an example.

M

Answer: The LIKE operator is used for pattern matching using wildcards.
Example:
M

SELECT * FROM EMPLOYEE WHERE Ename LIKE 'K%';

A

20. What are single-row functions? Give two examples.

Answer: Single-row functions operate on a single value and return a single value.
H

Examples: UCASE(), ROUND()

O
M

3 MARKS QUESTIONS

1. What are the main features of SQL? List any three.

Answer:

1. SQL is a non-procedural, declarative language.

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
20
CHAPTER9_QA December 2, 2025

2. It is used for creating and managing relational databases.

3. It is case-insensitive and easy to understand due to English-like syntax.

2. Differentiate between DDL and DML commands with one example each.
Answer:
DDL (Data Definition Language) defines the structure of a database. Example: CREATE TABLE.
DML (Data Manipulation Language) is used to manage data in tables. Example: INSERT INTO.

R
3. Write the syntax of the CREATE TABLE command and give an example to create a STUDENT

L
table with RollNumber, Name, and DateOfBirth.
Answer:

N
Syntax:

EE
CREATE TABLE tablename (column1 datatype, column2 datatype, ...);

Example:

CREATE TABLE STUDENT ( H

AT
RollNumber INT,
Name VARCHAR(25),
DateOfBirth DATE
M

);
ED

4. Explain any three constraints used in SQL.

Answer:
M

1. NOT NULL – Ensures that a column cannot have NULL values.

M

2. UNIQUE – Ensures that all values in a column are different.

A

3. PRIMARY KEY – Uniquely identifies each record and does not allow NULLs.
H

5. What is a foreign key? Give its syntax and explain with an example.
O

Answer:
A foreign key is a column that refers to the primary key of another table.
M

Syntax:

FOREIGN KEY (column_name) REFERENCES other_table(column_name);

Example:

ALTER TABLE STUDENT ADD FOREIGN KEY(GUID) REFERENCES GUARDIAN(GUID);

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
21
CHAPTER9_QA December 2, 2025

6. Write a query to insert a record into the GUARDIAN table. Also, write a query to add a new
column Occupation.
Answer:
Insert:

INSERT INTO GUARDIAN VALUES (444444444444, 'Prithvi Raj', 'M', 'Doctor', 1200000);

Add column:

R
ALTER TABLE GUARDIAN ADD Occupation VARCHAR(30);

L
7. Write a query to update the salary of employee code 103 to 80000 and delete employee with code
104.

N
Answer:

EE
Update:

UPDATE EMPLOYEE SET Salary = 80000 WHERE Ecode = 103;

Delete:
H
AT
DELETE FROM EMPLOYEE WHERE Ecode = 104;

8. What is the difference between DELETE and DROP commands? Give one use case each.
M

Answer:
DELETE removes specific rows but retains the table structure.
ED

Example: DELETE FROM STUDENT WHERE RollNumber = 1;

DROP removes the table structure entirely.
M

Example: DROP TABLE STUDENT;

9. Explain the use of SELECT statement with WHERE and ORDER BY clauses using an example.
M

Answer:
SELECT retrieves records from a table.
A

WHERE filters rows based on a condition.

H

ORDER BY sorts the results.

O

Example:
M

SELECT * FROM EMPLOYEE WHERE Salary > 40000 ORDER BY Ename;

10. Write a query using DISTINCT to display unique city names from the CUSTOMER table.
Also, explain its use.
Answer:
Query:

SELECT DISTINCT City FROM CUSTOMER;

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
22
CHAPTER9_QA December 2, 2025

Explanation: DISTINCT is used to remove duplicate values from the result set.

11. Differentiate between the WHERE and HAVING clauses with example queries.
Answer:
WHERE filters rows before grouping;
HAVING filters groups after grouping.
Example:

SELECT * FROM EMPLOYEE WHERE Salary > 30000;

R
SELECT DeptID, COUNT(*) FROM EMPLOYEE GROUP BY DeptID HAVING COUNT(*) > 2;

L
12. Write queries to demonstrate the use of LIKE operator with the patterns:
a) Names starting with ‘A’

N
b) Names ending with ‘n’

EE
c) Names containing ‘sh’
Answer:
a) SELECT * FROM EMPLOYEE WHERE Ename LIKE 'A%';
b) SELECT * FROM EMPLOYEE WHERE Ename LIKE '%n';
H
AT
c) SELECT * FROM EMPLOYEE WHERE Ename LIKE '%sh%';

13. What are wildcard characters in SQL? Explain % and _ with suitable examples.
M

Answer:
Wildcards are used with LIKE for pattern matching.
ED

% – Matches zero or more characters.

Example: 'K%' matches ‘Kiran’, ‘Kunal’
_ – Matches exactly one character.
M

Example: 'K_nal' matches ‘Kunal’, not ‘Kiran’

M

14. Write SQL queries to:

a) Display all records from SALE where SAmount is greater than 200000
A

b) Display all records between SAmount 150000 and 250000

H

Answer:
a) SELECT * FROM SALE WHERE SAmount > 200000;
O

b) SELECT * FROM SALE WHERE SAmount BETWEEN 150000 AND 250000;

M

15. List any three aggregate functions in SQL with their purpose.
Answer:

1. SUM() – Returns total sum of values.

2. AVG() – Returns average of values.

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
23
CHAPTER9_QA December 2, 2025

3. COUNT() – Returns number of records.

16. Write a query using GROUP BY to count the number of employees in each department.
Answer:

SELECT DeptID, COUNT(*) FROM EMPLOYEE GROUP BY DeptID;

17. Explain the use of MOD() and ROUND() functions with examples.
Answer:

R
MOD(x, y) returns the remainder. Example: MOD(11, 3) returns 2
ROUND(x, y) rounds to ‘y’ decimal places. Example: ROUND(283.28, 1) returns 283.3

L
18. What does the NOW() function return? Also, write a query to retrieve it.

N
Answer:
NOW() returns the current date and time.

EE
Query:

SELECT NOW();

H
AT
19. Write the difference between single-row functions and aggregate functions. Give two examples
of each.
Answer:
M

Single-row functions operate on each row. Example: UCASE(), ROUND()

Aggregate functions operate on a group of rows. Example: SUM(), MAX()
ED

20. Write queries to:

a) Display names in lowercase
M

b) Display first 4 characters of Cname

c) Display length of each name in EMPLOYEE
M

Answer:
a) SELECT LCASE(Ename) FROM EMPLOYEE;
A

b) SELECT MID(Cname, 1, 4) FROM CUSTOMER;

H

c) SELECT LENGTH(Ename) FROM EMPLOYEE;

O

5 MARKS QUESTIONS
M

QUESTION 1
Given Table: STUDENT

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
24
CHAPTER9_QA December 2, 2025

RollNumber SName SDateofBirth GUID

1 Atharv Ahuja 2003-05-15 444444444444

2 Daizy Bhutia 2002-02-28 111111111111
3 Taleem Shah 2002-02-28 NULL

R
Write SQL queries for the following:

a. Write a CREATE TABLE command to create the STUDENT table with appropriate data types.

L
N
b. Display all students whose names end with ‘Shah’.

EE
c. Count students born after the year 2002.

H
d. Update the GUID of student with RollNumber 3 to 101010101010.
AT
e. Show the name and birth year of each student.
M

ANSWER: 1

a.
ED

CREATE TABLE STUDENT (

RollNumber INT PRIMARY KEY,
SName VARCHAR(30),
M

SDateofBirth DATE,
GUID CHAR(12)
M

);
A

b.
H

SELECT * FROM STUDENT

WHERE SName LIKE '%Shah';
O

c.
M

SELECT COUNT(*) FROM STUDENT

WHERE YEAR(SDateofBirth) > 2002;

d.
UPDATE STUDENT
SET GUID = '101010101010'
WHERE RollNumber = 3;

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
25
CHAPTER9_QA December 2, 2025

e.

SELECT SName, YEAR(SDateofBirth) AS BirthYear FROM STUDENT;

QUESTION 2
Given Table: EMPLOYEE

EmpID Ename Salary DeptId

R
E001 Alok 25000 D01

L
E002 Bhavna 30000 D02
E003 Chetan 28000 D01

N
EE
Write SQL queries for the following:

H
a. Write the CREATE TABLE command for EMPLOYEE with relevant types.
AT
b. List employees working in department D01.
M

c. Count number of employees in each department.

ED

d. Update the salary of ‘Chetan’ to 32000.

e. Display employees in descending order of salary.

M

ANSWER: 2
M

a.
A

CREATE TABLE EMPLOYEE (

H

EmpID CHAR(5) PRIMARY KEY,

Ename VARCHAR(30),
O

Salary INT,
DeptId CHAR(4)
M

);

b.

SELECT * FROM EMPLOYEE

WHERE DeptId = 'D01';

c.

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
26
CHAPTER9_QA December 2, 2025

SELECT DeptId, COUNT(*) AS NumEmployees FROM EMPLOYEE

GROUP BY DeptId;

d.

UPDATE EMPLOYEE
SET Salary = 32000
WHERE Ename = 'Chetan';

e.

R
SELECT * FROM EMPLOYEE

L
ORDER BY Salary DESC;

N
QUESTION 3
Given Table: GUARDIAN

EE
GUID GName GPhone Income

111111111111 Bhutia
H
9812345670 420000
AT
222222222222 Raj Ahuja NULL 310000
M

Write SQL queries for the following:

ED

a. Create the GUARDIAN table with required fields.

b. Display guardian names with NULL phone numbers.

M

c. Count number of guardians with income above 3 lakh.

M
A

d. Update GPhone for GUID 222222222222 to ‘9012345678’.

H

e. Display names sorted by income descending.

O

ANSWER: 3
M

a.

CREATE TABLE GUARDIAN (

GUID CHAR(12) PRIMARY KEY,
GName VARCHAR(30),
GPhone CHAR(10),
Income INT
);

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
27
CHAPTER9_QA December 2, 2025

b.

SELECT GName FROM GUARDIAN

WHERE GPhone IS NULL;

c.

SELECT COUNT(*) FROM GUARDIAN

WHERE Income > 300000;

R
d.

L
UPDATE GUARDIAN
SET GPhone = '9012345678'

N
WHERE GUID = '222222222222';

EE
e.

SELECT GName FROM GUARDIAN

ORDER BY Income DESC;
H
AT
QUESTION 4
Given Table: INVENTORY
M

CarId CarName Price FuelType

ED

C01 Swift 525000.00 Petrol

C02 EECO 350000.00 CNG
M
M

Write SQL queries for the following:

a. Write the CREATE TABLE command for INVENTORY.

A
H

b. Display all Petrol cars.

O

c. Count cars for each fuel type.

M

d. Update the price of EECO to 360000.

e. Show car names sorted by price descending.

ANSWER: 4

a.

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
28
CHAPTER9_QA December 2, 2025

CREATE TABLE INVENTORY (

CarId CHAR(5) PRIMARY KEY,
CarName VARCHAR(30),
Price DECIMAL(10,2),
FuelType VARCHAR(10)
);

b.

R
SELECT * FROM INVENTORY
WHERE FuelType = 'Petrol';

L
c.

N
SELECT FuelType, COUNT(*) FROM INVENTORY

EE
GROUP BY FuelType;

d.

UPDATE INVENTORY
H
AT
SET Price = 360000.00
WHERE CarId = 'C02';
M

e.

SELECT CarName FROM INVENTORY

ED

ORDER BY Price DESC;

QUESTION 5
M

Given Table: SALE

M

InvoiceNo CarId CustId SalePrice

A

I001 C01 CU01 600000.00

H

I002 C02 CU02 540000.00

O
M

Write SQL queries for the following:

a. Write the CREATE TABLE statement for SALE.

b. Show all sales above 550000.

c. Count number of cars sold per car ID.

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
29
CHAPTER9_QA December 2, 2025

d. Update SalePrice for invoice I002 to 550000.

e. Show sale records sorted by price.

ANSWER: 5

a.

CREATE TABLE SALE (

R
InvoiceNo CHAR(5) PRIMARY KEY,
CarId CHAR(5),

L
CustId CHAR(5),
SalePrice DECIMAL(10,2)

N
);

EE
b.

SELECT * FROM SALE

WHERE SalePrice > 550000;
H
AT
c.

SELECT CarId, COUNT(*) AS TotalSold FROM SALE

M

GROUP BY CarId;

d.
ED

UPDATE SALE
SET SalePrice = 550000.00
M

WHERE InvoiceNo = 'I002';

M

e.

SELECT * FROM SALE

A

ORDER BY SalePrice;
H

QUESTION 6
O

Given Table: CUSTOMER

M

CustId CustName CustAdd Phone Email

C001 Ananya Sen Lajpat Nagar 9811122233 ananya@[Link]

C002 Ravi Verma Rohini Sector 3 9822233344 ravi@[Link]

Write SQL queries for the following:

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
30
CHAPTER9_QA December 2, 2025

a. Write the CREATE TABLE command for CUSTOMER.

b. Display names of customers who live in ‘Rohini’.

c. Count customers with Gmail addresses.

d. Update the phone number of ‘Ravi Verma’ to ‘9876543210’.

R
e. Show all customers sorted alphabetically by name.

L
ANSWER: 6

N
a.

EE
CREATE TABLE CUSTOMER (
CustId CHAR(5) PRIMARY KEY,
CustName VARCHAR(50),
CustAdd VARCHAR(100),
H
AT
Phone CHAR(10),
Email VARCHAR(50)
);
M

b.
ED

SELECT CustName FROM CUSTOMER

WHERE CustAdd LIKE '%Rohini%';
M

c.
M

SELECT COUNT(*) FROM CUSTOMER

WHERE Email LIKE '%[Link]';
A

d.
H

UPDATE CUSTOMER
O

SET Phone = '9876543210'

WHERE CustName = 'Ravi Verma';
M

e.

SELECT * FROM CUSTOMER

ORDER BY CustName;

QUESTION 7
Given Table: PRODUCT

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
31
CHAPTER9_QA December 2, 2025

ProdID ProdName Price Stock

P01 Monitor 8000 10

P02 Keyboard 1200 30

Write SQL queries for the following:

R
a. Create the PRODUCT table with correct data types.

L
b. Display all products with stock more than 20.

N
c. Count number of products priced over 5000.

EE
d. Update stock of ‘Monitor’ to 12.

H
AT
e. Show product names sorted by price.

ANSWER: 7
M

a.

CREATE TABLE PRODUCT (

ED

ProdID CHAR(5) PRIMARY KEY,

ProdName VARCHAR(30),
M

Price INT,
Stock INT
M

);

b.
A

SELECT * FROM PRODUCT

H

WHERE Stock > 20;

O

c.
M

SELECT COUNT(*) FROM PRODUCT

WHERE Price > 5000;

d.

UPDATE PRODUCT
SET Stock = 12
WHERE ProdName = 'Monitor';

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
32
CHAPTER9_QA December 2, 2025

e.

SELECT ProdName FROM PRODUCT

ORDER BY Price;

QUESTION 8
Given Table: BOOK

BookID Title Author Price

R
B101 Python Programming Sumita Arora 450

L
B102 Data Structures Narayana 550

N
EE
Write SQL queries for the following:

a. Write the CREATE TABLE statement for BOOK.

H
AT
b. Show titles priced above 500.

c. Count books written by ‘Sumita Arora’.

M

d. Update price of ‘Python Programming’ to 500.

ED

e. Display books sorted by title.

M

ANSWER: 8
M

a.
A

CREATE TABLE BOOK (

BookID CHAR(5) PRIMARY KEY,
H

Title VARCHAR(50),
O

Author VARCHAR(50),
Price INT
M

);

b.

SELECT Title FROM BOOK

WHERE Price > 500;

c.

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
33
CHAPTER9_QA December 2, 2025

SELECT COUNT(*) FROM BOOK

WHERE Author = 'Sumita Arora';

d.

UPDATE BOOK
SET Price = 500
WHERE Title = 'Python Programming';

R
e.

SELECT * FROM BOOK

L
ORDER BY Title;

N
QUESTION 9

EE
Given Table: SUPPLIER

SupplierID Name City

S001 Ram TradersH Delhi

AT
S002 Om Supplies Gurgaon
M

Write SQL queries for the following:

ED

a. Create the SUPPLIER table.

M

b. Show all suppliers in Delhi.

M

c. Count number of suppliers in Gurgaon.

A

d. Update city of ‘Om Supplies’ to ‘Noida’.

H

e. Display supplier names in reverse alphabetical order.

O
M

ANSWER: 9

a.

CREATE TABLE SUPPLIER (

SupplierID CHAR(5) PRIMARY KEY,
Name VARCHAR(50),
City VARCHAR(50)
);

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
34
CHAPTER9_QA December 2, 2025

b.

SELECT * FROM SUPPLIER

WHERE City = 'Delhi';

c.

SELECT COUNT(*) FROM SUPPLIER

WHERE City = 'Gurgaon';

R
d.

L
UPDATE SUPPLIER
SET City = 'Noida'

N
WHERE Name = 'Om Supplies';

EE
e.

SELECT * FROM SUPPLIER

ORDER BY Name DESC;
H
AT
QUESTION 10
Given Table: ATTENDANCE
M

RollNo Date Status

ED

1 2024-08-01 P
2 2024-08-01 A
M
M

Write SQL queries for the following:

a. Write the CREATE TABLE command for ATTENDANCE.

A
H

b. Show all students who were present.

O

c. Count number of absent students.

M

d. Update status of RollNo 2 to ‘P’.

e. List attendance sorted by RollNo.

ANSWER: 10

a.

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
35
CHAPTER9_QA December 2, 2025

CREATE TABLE ATTENDANCE (

RollNo INT,
Date DATE,
Status CHAR(1)
);

b.

SELECT * FROM ATTENDANCE

R
WHERE Status = 'P';

c.

L
SELECT COUNT(*) FROM ATTENDANCE

N
WHERE Status = 'A';

EE
d.

UPDATE ATTENDANCE
SET Status = 'P'
H
AT
WHERE RollNo = 2 AND Date = '2024-08-01';

e.
M

SELECT * FROM ATTENDANCE

ORDER BY RollNo;
ED

QUESTION 11
Given Table: MARKS
M

RollNo Subject Marks

M

1 Math 85
A

2 Math 78
H

1 Science 90
O
M

Write SQL queries for the following:

a. Create the MARKS table.

b. Find the average marks in Math.

c. Show maximum marks scored in Science.

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
36
CHAPTER9_QA December 2, 2025

d. Update marks of RollNo 2 in Math to 82.

e. List all marks sorted by RollNo and Subject.

ANSWER: 11

a.

CREATE TABLE MARKS (

R
RollNo INT,
Subject VARCHAR(30),

L
Marks INT
);

N
b.

EE
SELECT AVG(Marks) AS AvgMarks FROM MARKS
WHERE Subject = 'Math';

c. H
AT
SELECT MAX(Marks) AS MaxScience FROM MARKS
WHERE Subject = 'Science';
M

d.
ED

UPDATE MARKS
SET Marks = 82
WHERE RollNo = 2 AND Subject = 'Math';
M

e.
M

SELECT * FROM MARKS

A

ORDER BY RollNo, Subject;

H

QUESTION 12
Given Table: PRODUCT
O
M

ProdID Name Price Quantity

P01 Pen 10 100

P02 Notebook 50 80

Write SQL queries for the following:

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
37
CHAPTER9_QA December 2, 2025

a. Write the CREATE TABLE query for PRODUCT.

b. Calculate total stock value for each product (Price × Quantity).

c. Find the highest product price.

d. Update quantity of Pen to 120.

R
e. List all products sorted by name.

L
ANSWER: 12

N
a.

EE
CREATE TABLE PRODUCT (
ProdID CHAR(5) PRIMARY KEY,
Name VARCHAR(30),
Price INT,
H
AT
Quantity INT
);
M

b.

SELECT Name, (Price * Quantity) AS StockValue FROM PRODUCT;

ED

c.
M

SELECT MAX(Price) AS MaxPrice FROM PRODUCT;

d.
M

UPDATE PRODUCT
A

SET Quantity = 120

WHERE Name = 'Pen';
H
O

e.
M

SELECT * FROM PRODUCT

ORDER BY Name;

QUESTION 13
Given Table: SALES

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
38
CHAPTER9_QA December 2, 2025

SaleID Item Amount

S001 Pen 200

S002 Notebook 800
S003 Pen 150

R
Write SQL queries for the following:

L
a. Create the SALES table.

N
b. Find total sales amount for ‘Pen’.

EE
c. Find the number of sales entries for ‘Notebook’.

d. Update amount of SaleID S003 to 180.

H
AT
e. Display sales sorted by amount descending.
M

ANSWER: 13

a.
ED

CREATE TABLE SALES (

SaleID CHAR(5) PRIMARY KEY,
M

Item VARCHAR(30),
Amount INT
M

);
A

b.
H

SELECT SUM(Amount) AS TotalPenSales FROM SALES

WHERE Item = 'Pen';
O

c.
M

SELECT COUNT(*) FROM SALES

WHERE Item = 'Notebook';

d.

UPDATE SALES
SET Amount = 180
WHERE SaleID = 'S003';

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
39
CHAPTER9_QA December 2, 2025

e.

SELECT * FROM SALES

ORDER BY Amount DESC;

QUESTION 14
Given Table: ATTENDANCE

Date RollNo Status

R
2024-08-01 1 P

L
2024-08-01 2 A

N
2024-08-02 1 P

EE
Write SQL queries for the following:

a. Write the CREATE TABLE statement for ATTENDANCE. H

AT
b. Count how many days RollNo 1 was present.
M

c. Count total records for each attendance status.

ED

d. Update status of RollNo 2 on 2024-08-01 to ‘P’.

M

e. Show all records sorted by date then roll number.

M

ANSWER: 14
A

a.
H

CREATE TABLE ATTENDANCE (

Date DATE,
O

RollNo INT,
Status CHAR(1)
M

);

b.

SELECT COUNT(*) AS DaysPresent FROM ATTENDANCE

WHERE RollNo = 1 AND Status = 'P';

c.

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
40
CHAPTER9_QA December 2, 2025

SELECT Status, COUNT(*) AS Count FROM ATTENDANCE

GROUP BY Status;

d.

UPDATE ATTENDANCE
SET Status = 'P'
WHERE RollNo = 2 AND Date = '2024-08-01';

R
e.

SELECT * FROM ATTENDANCE

L
ORDER BY Date, RollNo;

N
QUESTION 15

EE
Given Table: EMPLOYEE

EmpID Name Salary

E01 Aarav H 45000

AT
E02 Tanya 50000
M

E03 Rohan 42000

ED

Write SQL queries for the following:

a. Create the EMPLOYEE table.

M

b. Find average salary.

M
A

c. Find minimum salary.

H

d. Update salary of ‘Rohan’ to 46000.

O

e. List employees sorted by salary descending.

M

ANSWER: 15

a.

CREATE TABLE EMPLOYEE (

EmpID CHAR(5) PRIMARY KEY,
Name VARCHAR(30),

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
41
CHAPTER9_QA December 2, 2025

Salary INT
);

b.

SELECT AVG(Salary) AS AvgSalary FROM EMPLOYEE;

c.

SELECT MIN(Salary) AS MinSalary FROM EMPLOYEE;

R
d.

L
UPDATE EMPLOYEE

N
SET Salary = 46000
WHERE Name = 'Rohan';

EE
e.

SELECT * FROM EMPLOYEE

ORDER BY Salary DESC; H
AT
QUESTION 16
Given Table: GUARDIAN
M

GUID GName Income

ED

G001 Aman Kumar 420000

M

G002 Rekha Singh 390000

G003 D’Souza 280000
M
A

Write SQL queries for the following:

H

a. Write the CREATE TABLE command for GUARDIAN.

O

b. Find the average income of all guardians.

M

c. Count the number of guardians earning more than Rs.3 lakh.

d. Update the income of G003 to Rs.310000.

e. Display all guardians in descending order of income.

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
42
CHAPTER9_QA December 2, 2025

ANSWER: 16

a.

CREATE TABLE GUARDIAN (

GUID CHAR(5) PRIMARY KEY,
GName VARCHAR(50),
Income INT
);

R
b.

L
SELECT AVG(Income) AS AverageIncome FROM GUARDIAN;

N
c.

EE
SELECT COUNT(*) AS CountAbove3Lakh FROM GUARDIAN
WHERE Income > 300000;

d.
H
AT
UPDATE GUARDIAN
SET Income = 310000
WHERE GUID = 'G003';
M

e.
ED

SELECT * FROM GUARDIAN

ORDER BY Income DESC;
M

QUESTION 17
Given Table: LIBRARY
M

BookID Title Price

A

B001 DBMS Concepts 450

H

B002 Python Programming 500

O

B003 Java Basics 475

M

Write SQL queries for the following:

a. Write the CREATE TABLE command for LIBRARY.

b. Find the total cost of all books.

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
43
CHAPTER9_QA December 2, 2025

c. Display the most expensive book.

d. Update the price of ‘DBMS Concepts’ to 470.

e. Show books in ascending order of title.

ANSWER: 17

R
a.

CREATE TABLE LIBRARY (

L
BookID CHAR(5) PRIMARY KEY,
Title VARCHAR(100),

N
Price INT

EE
);

b.

SELECT SUM(Price) AS TotalCost FROM LIBRARY;

H
AT
c.

SELECT * FROM LIBRARY

M

ORDER BY Price DESC

LIMIT 1;
ED

d.

UPDATE LIBRARY
M

SET Price = 470

WHERE Title = 'DBMS Concepts';
M

e.
A

SELECT * FROM LIBRARY

H

ORDER BY Title ASC;

O

QUESTION 18
M

Given Table: PURCHASE

PurchaseID Item Quantity Rate

P001 Pencil 100 5

P002 Eraser 50 3

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
44
CHAPTER9_QA December 2, 2025

PurchaseID Item Quantity Rate

P003 Sharpener 60 6

Write SQL queries for the following:

a. Create the PURCHASE table.

R
b. Calculate total quantity of items purchased.

L
c. Find maximum rate among all items.

N
EE
d. Update quantity of Eraser to 70.

e. Show all purchase entries sorted by item name.

H
AT
ANSWER: 18

a.
M

CREATE TABLE PURCHASE (

PurchaseID CHAR(5) PRIMARY KEY,
ED

Item VARCHAR(30),
Quantity INT,
Rate INT
M

);

b.
M

SELECT SUM(Quantity) AS TotalQuantity FROM PURCHASE;

A

c.
H

SELECT MAX(Rate) AS HighestRate FROM PURCHASE;

O

d.
M

UPDATE PURCHASE
SET Quantity = 70
WHERE Item = 'Eraser';

e.

SELECT * FROM PURCHASE

ORDER BY Item;

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
45
CHAPTER9_QA December 2, 2025

QUESTION 19
Given Table: ORDERS

OrderID Customer Amount

O001 Aaliya 4500

O002 Tanya 5000

R
O003 Rohan 4200

L
Write SQL queries for the following:

N
a. Create the ORDERS table.

EE
b. Show total order amount.

c. Count how many customers ordered more than Rs.4500. H

AT
d. Update amount of OrderID O003 to 4600.
M

e. Show all orders sorted by amount descending.

ED

ANSWER: 19

a.
M

CREATE TABLE ORDERS (

M

OrderID CHAR(5) PRIMARY KEY,

Customer VARCHAR(30),
A

Amount INT
);
H

b.
O

SELECT SUM(Amount) AS TotalOrders FROM ORDERS;

M

c.

SELECT COUNT(*) FROM ORDERS

WHERE Amount > 4500;

d.

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
46
CHAPTER9_QA December 2, 2025

UPDATE ORDERS
SET Amount = 4600
WHERE OrderID = 'O003';

e.

SELECT * FROM ORDERS

ORDER BY Amount DESC;

R
QUESTION 20
Given Table: CLASSMARKS

L
RollNo Subject Marks

N
101 English 88

EE
102 English 92
103 English 80

H
AT
Write SQL queries for the following:
M

a. Create the CLASSMARKS table.

ED

b. Find average marks in English.

c. Find minimum marks in the subject.

M

d. Update marks of RollNo 103 to 85.

M

e. Display all marks sorted by highest to lowest.

A
H

ANSWER: 20
O

a.
M

CREATE TABLE CLASSMARKS (

RollNo INT,
Subject VARCHAR(30),
Marks INT
);

b.

SELECT AVG(Marks) AS AverageMarks FROM CLASSMARKS;

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
47
CHAPTER9_QA December 2, 2025

c.

SELECT MIN(Marks) AS LowestMarks FROM CLASSMARKS;

d.

UPDATE CLASSMARKS
SET Marks = 85
WHERE RollNo = 103;

R
e.

L
SELECT * FROM CLASSMARKS
ORDER BY Marks DESC;

N
EE
CHAPTER END EXERCISES

Exercise 1: Answer the following questions Answer: a) RDBMS stands for Relational Database Man-
agement System.
H
AT
Two RDBMS software: MySQL, Oracle

b)
M

- ORDER BY: Sorts query results based on one or more columns.

- GROUP BY: Groups rows with identical values for aggregate functions.
ED

c)

Single Row Functions Aggregate Functions

M

Work on one row at a time Work on a group of rows

M

Return one result per row Return one result for entire group
A
H

d) Cartesian Product: The result of combining every row from one table with every row from another
O

table.
M

e)
- ALTER changes table structure.
- UPDATE changes table content.
- DELETE removes specific rows.
- DROP removes entire table.

f)
i) DAYNAME()

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
48
CHAPTER9_QA December 2, 2025

ii) MID() or SUBSTRING()

iii) MONTHNAME()
iv) UPPER() or UCASE()

Exercise 2: Write the output produced by the following SQL statements Answer: a)

SELECT POW(2,3);

→8

R
b)

L
SELECT ROUND(342.9234,-1);

N
→ 340

EE
c)

SELECT LENGTH("Informatics Practices");

→ 23
H
AT
d)

SELECT YEAR("1979/11/26"), MONTH("1979/11/26"), DAY("1979/11/26"),

M

↪ MONTHNAME("1979/11/26");

→ 1979, 11, 26, November

ED

e)

SELECT LEFT("INDIA",3), RIGHT("Computer Science",4), MID("Informatics",3,4),

M

↪ SUBSTR("Practices",3);
M

→ IND, ence, form, actices

A

Exercise 3: Based on MOVIE Table Answer: Given table MOVIE with columns: MovieID, MovieName,
Category, ReleaseDate, ProductionCost, BusinessCost
H

a)
O

SELECT * FROM MOVIE;

M

b)

SELECT MovieID, MovieName, (ProductionCost + BusinessCost) AS Total_Earning FROM

↪ MOVIE;

c)

SELECT DISTINCT Category FROM MOVIE;

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
49
CHAPTER9_QA December 2, 2025

d)

SELECT MovieID, MovieName, (BusinessCost - ProductionCost) AS NetProfit FROM MOVIE;

e)

SELECT MovieID, MovieName, ProductionCost FROM MOVIE WHERE ProductionCost > 10000 AND
↪ ProductionCost < 100000;

f)

R
SELECT * FROM MOVIE WHERE Category IN ('Comedy', 'Action');

L
g)

N
SELECT * FROM MOVIE WHERE ReleaseDate IS NULL;

EE
Exercise 4: Creating and Modifying Tables in SPORTS Database Answer: a)

CREATE DATABASE Sports;

b) H
AT
CREATE TABLE TEAM (
TeamID INT CHECK(TeamID BETWEEN 1 AND 9),
M

TeamName VARCHAR(50) CHECK(LENGTH(TeamName) >= 10),

PRIMARY KEY (TeamID)
ED

);

c) Already included with PRIMARY KEY on TeamID.

M

d)
M

DESC TEAM;
A

e)
H

INSERT INTO TEAM VALUES

(1, 'Team Titan'),
O

(2, 'Team Rockers'),

(3, 'Team Magnet'),
M

(4, 'Team Hurricane');

f)

SELECT * FROM TEAM;

g)

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
50
CHAPTER9_QA December 2, 2025

CREATE TABLE MATCH_DETAILS (

MatchID CHAR(3) PRIMARY KEY,
MatchDate DATE,
FirstTeamID INT,
SecondTeamID INT,
FirstTeamScore INT,
SecondTeamScore INT
);

R
Exercise 5: Queries on MATCH_DETAILS Table Answer: a)

L
SELECT MatchID FROM MATCH_DETAILS WHERE FirstTeamScore > 70 AND SecondTeamScore > 70;

N
b)

EE
SELECT MatchID FROM MATCH_DETAILS WHERE FirstTeamScore < 70 AND SecondTeamScore > 70;

c)

H
SELECT MatchID, MatchDate FROM MATCH_DETAILS WHERE FirstTeamID = 1 AND FirstTeamScore
AT
↪ > SecondTeamScore;

d)
M

SELECT MatchID
FROM MATCH_DETAILS
ED

WHERE (FirstTeamID = 2 AND FirstTeamScore < SecondTeamScore)

OR (SecondTeamID = 2 AND SecondTeamScore < FirstTeamScore);
M

e)

ALTER TABLE TEAM RENAME TO T_DATA;

M

ALTER TABLE T_DATA CHANGE TeamID T_ID INT;

ALTER TABLE T_DATA CHANGE TeamName T_NAME VARCHAR(50);
A

Exercise 6: SCHOOLUNIFORM Database Constraints Answer: a) Insert handkerchief:

H
O

INSERT INTO UNIFORM VALUES (7, 'Handkerchief', 'Red');

INSERT INTO COST VALUES (7, 'M', 100);
M

b) Enforce referential integrity:

ALTER TABLE COST ADD CONSTRAINT FK_UCode FOREIGN KEY (UCode) REFERENCES
↪ UNIFORM(UCode);

c) Valid UName constraint:

ALTER TABLE UNIFORM ADD CONSTRAINT chk_UName CHECK (UName IS NOT NULL);

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
51
CHAPTER9_QA December 2, 2025

d) Price must be > 0:

ALTER TABLE COST ADD CONSTRAINT chk_price_gt_zero CHECK (Price > 0);

Exercise 7: Product Table Operations Answer: a)

CREATE TABLE Product (

PCode CHAR(4) PRIMARY KEY,
PName VARCHAR(30),
UPrice INT,

R
Manufacturer VARCHAR(30)
);

L
b) Primary Key: PCode

N
c)

EE
SELECT PCode, PName, UPrice FROM Product ORDER BY PName DESC, UPrice ASC;

d)
H
AT
ALTER TABLE Product ADD Discount INT;

e)
M

UPDATE Product SET Discount = 0;

UPDATE Product SET Discount = 10 WHERE UPrice > 100;
ED

OR
M

UPDATE Product SET Discount =

CASE
M

WHEN UPrice > 100 THEN UPrice * 0.10

ELSE 0
A

END;
H

f)
O

UPDATE Product SET UPrice = UPrice * 1.12 WHERE Manufacturer = 'Dove';

M

g)

SELECT Manufacturer, COUNT(*) FROM Product GROUP BY Manufacturer;

h)

SELECT PName, AVG(UPrice) FROM Product GROUP BY PName;

i)

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
52
CHAPTER9_QA December 2, 2025

SELECT DISTINCT Manufacturer FROM Product;

j)

SELECT COUNT(DISTINCT PName) FROM Product;

k)

SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;

R
Exercise 8: Queries on CARSHOWROOM - INVENTORY Table Answer: a)

L
ALTER TABLE INVENTORY ADD Discount INT;

b)

N
UPDATE INVENTORY SET Discount =

EE
CASE
WHEN Model = 'LXI' THEN 0
WHEN Model =
ELSE Price *
'VXI' THEN Price * 0.10
0.12 H
AT
END;

or
M

UPDATE INVENTORY SET Discount = 0 WHERE Model = 'LXI';

UPDATE INVENTORY SET Discount = 10 WHERE Model = 'VXI';
ED

UPDATE INVENTORY SET Discount = 12 WHERE Model NOT IN (LXI', VXI');

c)
M

SELECT CarName FROM INVENTORY WHERE FuelType = 'Petrol' ORDER BY Price DESC LIMIT 1;
M

d)
A

SELECT AVG(Discount), SUM(Discount) FROM INVENTORY WHERE CarName = 'Baleno';

H

e)
O

SELECT COUNT(*) FROM INVENTORY WHERE Discount = 0;

M

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
53