CBSE/Karnataka PU Board PUC II CHAPTER 9 : STRUCTURED QUERY LANGUAGE(SQL)

50 MCQ questions with answers
20 each of 1 Mark, 2 Mark, 3 Mark, and 5 Mark questions with answers
5 Mark questions are given special attention with in-depth answers

50 MCQ Questions (With Answers)

SQL stands for:
A) Structured Question Language
B) Structured Query Language
C) Simple Query Language
D) Sequential Query Language
Answer: B
Which one is not a RDBMS?
A) MySQL
B) Oracle
C) MS Excel
D) SQL Server
Answer: C
Which component is not part of SQL?
A) DDL
B) DCL
C) DML
D) HTML
Answer: D
Which SQL statement is used to extract data from a database?
A) GET
B) OPEN
C) SELECT
D) EXTRACT
Answer: C
Which clause is compulsory in a SELECT statement?
A) WHERE
B) HAVING
C) FROM
D) GROUP BY
Answer: C
Which SQL keyword is used to prevent duplication in output results?
A) DISTINCT
B) UNIQUE
C) DIFFERENT
D) EXIST
Answer: A
Which command is used to remove all records from a table and not the table itself?
A) DROP
B) DELETE
C) REMOVE
D) TRUNCATE
Answer: D
Which data type is used for storing numbers with decimal points?
A) INT
B) CHAR
C) FLOAT
D) VARCHAR
Answer: C
Which operator is suitable for pattern matching in SQL?
A) IN
B) LIKE
C) BETWEEN
D) EXISTS
Answer: B
Which of the following is a group function?
A) SUM
B) UCASE
C) ROUND
D) LCASE
Answer: A
Which constraint ensures a column cannot have NULL values?
A) UNIQUE
B) NOT NULL
C) PRIMARY KEY
D) CHECK
Answer: B
Which keyword is used to change structure of existing table?
A) UPDATE
B) ALTER
C) MODIFY
D) CHANGE
Answer: B
Which of these is NOT a valid SQL command?
A) UPDATE
B) INSERT
C) SELECT
D) CHANGE
Answer: D
What does SQL command ‘ROLLBACK’ do?
A) Saves changes
B) Reverts changes
C) Commits a transaction
D) Creates a table
Answer: B
Which function returns the largest value in a column?
A) MIN
B) MAX
C) AVG
D) SUM
Answer: B
Which symbol is used to represent one character in LIKE patterns?
A) %
B) _
C) #
D) $
Answer: B
Which clause is used to filter groups in SQL?
A) WHERE
B) HAVING
C) ORDER BY
D) GROUP BY
Answer: B
Which command is used to remove a table?
A) DELETE
B) REMOVE
C) DROP
D) CLEAR
Answer: C
MySQL is an example of:
A) Operating System
B) Programming Language
C) RDBMS
D) Spreadsheet
Answer: C
Which clause is used to arrange records in a specific order?
A) GET
B) ORDER BY
C) SORT BY
D) GROUP BY
Answer: B
Which function calculates average?
A) MAX
B) MIN
C) AVG
D) COUNT
Answer: C
Which of the following can act as a composite primary key?
A) One column
B) Two or more columns
C) FOREIGN KEY
D) None
Answer: B
What value does SQL assign as default to fields if not provided?
A) SPACE
B) 0
C) NULL
D) EMPTY
Answer: C
Which constraint is used to ensure unique values in a column?
A) FOREIGN KEY
B) UNIQUE
C) CHECK
D) NOT NULL
Answer: B
Which command is used to insert new data?
A) NEW
B) ADD
C) INSERT
D) UPDATE
Answer: C
Which command removes rows matching condition?
A) DELETE
B) REMOVE
C) DROP
D) CLEAR
Answer: A
Which SQL clause eliminates duplicate rows?
A) HAVING
B) DISTINCT
C) UNIQUE
D) ORDER BY
Answer: B
Which function returns the number of rows in result?
A) MAX
B) SUM
C) COUNT
D) LEN
Answer: C
Which operation combines rows from two tables?
A) UNION
B) INTERSECT
C) MINUS
D) DIFFERENCE
Answer: A
Which command restricts the column value to a range?
A) RANGE
B) CHECK
C) VALIDATE
D) CONSTRAINT
Answer: B
Which of the following is not a numeric data type in MySQL?
A) INT
B) FLOAT
C) DATE
D) DOUBLE
Answer: C
The PRIMARY KEY can be:
A) Null
B) Non-unique
C) Unique
D) Case-insensitive
Answer: C
Which clause is used to group rows sharing a property?
A) GROUP BY
B) ORDER BY
C) HAVING
D) DISTINCT
Answer: A
What is the default sorting order of ORDER BY clause?
A) Descending
B) Ascending
C) Random
D) None
Answer: B
Which function returns the smallest value in a column?
A) MIN
B) MAX
C) COUNT
D) AVG
Answer: A
Which SQL statement is used to update data?
A) CHANGE
B) REPLACE
C) UPDATE
D) EDIT
Answer: C
Which symbol is wildcard for multiple characters in LIKE?
A) _
B) %
C) $
D) #
Answer: B
What is the result of a Cartesian product?
A) Sum
B) Difference
C) All possible pairs
D) Subset
Answer: C
Which SQL statement creates a new table?
A) CREATE
B) MAKE
C) NEW
D) START
Answer: A
Which clause is used to filter records before grouping?
A) WHERE
B) HAVING
C) ORDER BY
D) DISTINCT
Answer: A
Which function returns characters in uppercase?
A) UPPER
B) LCASE
C) MID
D) TRIM
Answer: A
Which statement adds a column to existing table?
A) ALTER TABLE ... ADD
B) UPDATE TABLE ... ADD
C) NEW COLUMN
D) CHANGE COLUMN
Answer: A
Which clause is used to check values within a list?
A) ALL
B) ANY
C) IN
D) LIKE
Answer: C
Which function returns today’s date?
A) NOW()
B) TODAY()
C) DATE()
D) CURRDATE()
Answer: A
Which statement deletes a table’s structure?
A) DROP
B) DELETE
C) REMOVE
D) CLEAR
Answer: A
Which command is used to rollback a transaction?
A) ROLLBACK
B) CHANGEBACK
C) UNDO
D) REVERSE
Answer: A
Which key restricts relationship between tables?
A) PRIMARY KEY
B) FOREIGN KEY
C) UNIQUE
D) SUPER KEY
Answer: B
Which is not an SQL aggregate function?
A) SUM
B) AVG
C) MIN
D) FIND
Answer: D
Which of these is case sensitive in SQL?
A) Column Names
B) Table Names in LINUX
C) SQL Keywords
D) None
Answer: B
Which statement is true for ALTER?
A) Only adds columns
B) Only deletes columns
C) Adds, deletes, or modifies columns
D) Only modifies columns
Answer: C

1 Mark Questions (Any 20, with Answers)

Define SQL.
Answer: SQL stands for Structured Query Language, used for managing data in RDBMS.
Name two RDBMS software.
Answer: MySQL and Oracle.
What does DDL stand for?
Answer: Data Definition Language.
Write the purpose of SELECT command.
Answer: It retrieves data from the database.
Mention one numeric data type in MySQL.
Answer: INT.
Name a constraint that ensures values are unique.
Answer: UNIQUE.
Which operator is used to test for NULL values?
Answer: IS NULL.
Name the clause that sorts result.
Answer: ORDER BY.
Which keyword is used for pattern matching?
Answer: LIKE.
Which function returns the number of rows?
Answer: COUNT().
Expand RDBMS.
Answer: Relational Database Management System.
What is a primary key?
Answer: A field that uniquely identifies each record.
What data type stores character values of fixed length?
Answer: CHAR.
What does GROUP BY do?
Answer: Groups rows that have the same values.
Mention one aggregate function.
Answer: SUM().
Which statement is used to add rows?
Answer: INSERT.
Name the command to remove a table.
Answer: DROP.
Write one advantage of SQL.
Answer: It is easy to learn and use.
Which command modifies records?
Answer: UPDATE.
Write a query to display current date in SQL.
Answer: SELECT NOW();

2 Mark Questions (Any 20, with Answers)

What is the difference between DELETE and TRUNCATE commands?
Answer: DELETE removes rows one at a time and logs each deletion; TRUNCATE removes all rows at once and it cannot be rolled back in some RDBMS.
What is the use of HAVING clause?
Answer: HAVING filters groups created by GROUP BY based on aggregate conditions.
Define Foreign Key.
Answer: A Foreign Key is a field in one table that refers to the primary key of another table, enforcing referential integrity.
Why is data type important in SQL?
Answer: Data type defines the nature of data that can be stored in a field, helping in efficient storage and retrieval.
What does the DISTINCT keyword do?
Answer: It removes duplicate records from the result set.
Write a SQL query to get the max salary from EMPLOYEE.
Answer: SELECT MAX(Salary) FROM EMPLOYEE;
Give two differences between CHAR and VARCHAR.
Answer: CHAR is fixed length, always uses the same storage; VARCHAR is variable length and uses only necessary storage.
Why do we use SQL constraints?
Answer: Constraints enforce rules for data in tables, ensuring data accuracy and reliability.
Write the syntax for creating a database.
Answer: CREATE DATABASE databasename;
What is a composite key?
Answer: A Composite Key is a combination of two or more columns used to uniquely identify a record.
Define aggregate function with example.
Answer: Aggregate functions perform calculations on a set of values. Example: AVG(), SUM().
What is pattern matching?
Answer: Pattern matching allows searching for data that matches a specified pattern using LIKE along with % or _.
Give the purpose of ALTER TABLE command.
Answer: ALTER TABLE command modifies the structure of an existing table by adding, deleting, or altering columns.
Why use ORDER BY clause?
Answer: To display results in a specified sequence, such as ascending or descending order.
Write a query to count records in STUDENT table.
Answer: SELECT COUNT(*) FROM STUDENT;
What is meant by ‘case insensitive’ in MySQL?
Answer: MySQL treats keywords and column names the same regardless of case (except on Linux for table names).
What is a default value in SQL?
Answer: A default value is the value that is inserted if no value is provided for a column.
Mention use of UNIQUE constraint.
Answer: Ensures all values in a column are different.
Name two functions for converting case in SQL.
Answer: UPPER(), LOWER().
How do you delete all records of a table without removing the table?
Answer: Use TRUNCATE TABLE tablename;

3 Mark Questions (Any 20, with Answers)

Explain the difference between DDL and DML with suitable examples.
Answer: DDL (Data Definition Language) includes SQL commands that define database structure, e.g., CREATE TABLE, ALTER TABLE, DROP TABLE. DML (Data Manipulation Language) includes commands that interact with data, e.g., INSERT, UPDATE, DELETE, SELECT.
Define Primary Key and Foreign Key with examples.
Answer: Primary Key uniquely identifies each record in a table. Example: StudentID in STUDENT table. Foreign Key is a field in a table that allows reference to Primary Key of another table, ensuring referential integrity.
What is the function of GROUP BY clause? Write an example.
Answer: The GROUP BY clause groups rows with the same values in specified columns into summary rows.
Example: SELECT DeptId, COUNT(*) FROM EMPLOYEE GROUP BY DeptId;
What do you mean by SQL constraints? Mention any two.
Answer: Constraints are rules applied to columns to ensure valid data. Example: NOT NULL (prevents null values), CHECK (restricts data to specific values).
Write a query to display all employee names and their annual salary using SALARY * 12.
Answer: SELECT EName, Salary*12 AS AnnualSalary FROM EMPLOYEE;
List three differences between CHAR and VARCHAR data types.
Answer: CHAR is fixed length; VARCHAR is variable. CHAR pads spaces to the end; VARCHAR does not. CHAR is faster for same length values; VARCHAR is space-efficient for varying lengths.
Differentiate between WHERE and HAVING clauses.
Answer: WHERE filters rows before grouping; HAVING filters groups after GROUP BY is applied.
Write SQL statements to insert and update data in a table called STUDENT.
Answer: INSERT INTO STUDENT (RollNumber, SName) VALUES (1, 'Anmol');
UPDATE STUDENT SET SName='Anita' WHERE RollNumber=1;
What are aggregate functions? Name any three with examples.
Answer: Functions operating on a set of values: SUM(Salary), AVG(Marks), MIN(Price).
Describe the concept of joins. Write the SQL syntax for INNER JOIN.
Answer: Join combines rows from two or more tables based on related columns.
Example: SELECT A.,B. FROM TableA A INNER JOIN TableB B ON A.ID=B.ID;
What is meant by the term 'cardinality' in SQL?
Answer: Cardinality refers to the number of rows in a table.
List three data types supported in MySQL and their use.
Answer: INT (stores integers), CHAR (fixed-length text), FLOAT (decimal numbers).
Explain pattern matching symbols used with LIKE.
Answer: % for zero or more characters, _ for exactly one character.
How can you add a new column to an existing table? Give the syntax.
Answer: ALTER TABLE tablename ADD columnname datatype;
Illustrate the use of ORDER BY with an example.
Answer: SELECT * FROM STUDENT ORDER BY SName DESC;
Write SQL query to display total sales made by each employee in SALE table.
Answer: SELECT EmpID, SUM(SalePrice) FROM SALE GROUP BY EmpID;
What is meant by composite key? Give an example.
Answer: A key formed from two or more columns, e.g., (AttendanceDate, RollNumber) for ATTENDANCE.
Explain the use of the 'CHECK' constraint with an example.
Answer: Used to limit values in a column. Example: CHECK (Salary>0).
Which command is used to delete a table? Are records removed as well?
Answer: DROP TABLE tablename; Yes, records and structure are both removed.
What is referential integrity?
Answer: Ensures consistency between related tables by enforcing Foreign Key constraints.

5 Mark Questions (Any 20, with extended answers)

1. Explain the process of creating tables and adding constraints in MySQL with suitable examples.
To create a table in MySQL, you use the CREATE TABLE statement. This involves specifying the table name, column names, data types, and constraints. Constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, and NOT NULL help enforce data integrity. For example, consider the following command:

CREATE TABLE STUDENT (
  RollNumber INT PRIMARY KEY,
  SName VARCHAR(50) NOT NULL,
  SDateofBirth DATE,
  GUID CHAR(12),
  FOREIGN KEY (GUID) REFERENCES GUARDIAN(GUID)
);

In the above, 'RollNumber' is set as the primary key (unique identifier), 'SName' cannot be null, and 'GUID' in STUDENT is a foreign key referencing the 'GUID' attribute in GUARDIAN. This ensures that each student is linked to a guardian and that all required fields are filled with valid data.

2. Differentiate between Single row and Aggregate functions in SQL with examples.
Single row functions operate on each row and return a single value for every row, such as UPPER(), LOWER(), or LENGTH(). For example, SELECT UPPER(SName) FROM STUDENT returns student names in uppercase. Aggregate functions, like COUNT(), AVG(), SUM(), MIN(), and MAX(), work on sets of rows and return a single value for the entire set. For example, SELECT AVG(Salary) FROM EMPLOYEE calculates the average salary of all employees. The key differences are that single row functions process one row at a time, while aggregate functions process whole columns or result sets.

3. Describe the steps to modify a table’s structure using ALTER command, with examples for adding, modifying, and dropping columns.
The ALTER TABLE command in SQL is used to add, modify, or delete columns and constraints in an existing table. To add a column, use: ALTER TABLE tablename ADD columnname datatype;. To modify a column: ALTER TABLE tablename MODIFY columnname newdatatype;. To drop a column: ALTER TABLE tablename DROP columnname;.
For example,

To add an 'Email' column: ALTER TABLE STUDENT ADD Email VARCHAR(100);
To change data type of 'SName' to VARCHAR(100): ALTER TABLE STUDENT MODIFY SName VARCHAR(100);
To remove 'Email' column: ALTER TABLE STUDENT DROP Email;
Each command can be executed independently as required.

4. What are Join operations? Explain different types with examples.
Join operations are used to combine rows from two or more tables based on related columns between them. The main types are:

INNER JOIN: Returns only matching rows from both tables.
LEFT JOIN: Returns all rows from the left table, plus matching rows from the right table.
RIGHT JOIN: Returns all rows from the right table, plus matching rows from the left table.
FULL JOIN: Returns all rows when there is a match in either table.
Example:

SELECT A.RollNumber, B.GName
FROM STUDENT A
INNER JOIN GUARDIAN B ON A.GUID = B.GUID;

This returns all students and their corresponding guardians.

5. Discuss the importance of constraints in RDBMS and give examples of NOT NULL, UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints.
Constraints ensure the validity and integrity of the data in RDBMS. The NOT NULL constraint ensures a column cannot have NULL values, thus preventing missing data. UNIQUE ensures all values in a column are unique, avoiding duplicates. PRIMARY KEY uniquely identifies each row in a table. FOREIGN KEY creates a link between tables and helps maintain referential integrity.
For example:

CREATE TABLE TEACHER (
  TID INT PRIMARY KEY,
  TNAME VARCHAR(50) NOT NULL,
  EMAIL VARCHAR(100) UNIQUE,
  DEPT_ID INT,
  FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID)
);

Here, TID is the primary key, TNAME must not be null, EMAIL ensures uniqueness, and DEPT_ID relates to another table.

6. Write SQL queries to demonstrate the use of GROUP BY and HAVING clauses with an appropriate sample table.
Consider the EMPLOYEE table. To find the number of employees in each department:

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

To find departments with more than five employees:

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

The GROUP BY clause groups rows by DeptID and HAVING filters these groups.

7. Explain the difference between WHERE and HAVING with example queries.
WHERE is used to filter records before grouping; HAVING filters after grouping. For instance:

To select employees with salary over 50000:

SELECT * FROM EMPLOYEE WHERE Salary > 50000;

To find departments with an average salary above 50000:

SELECT DeptID, AVG(Salary) FROM EMPLOYEE GROUP BY DeptID HAVING AVG(Salary) > 50000;

WHERE cannot be used with aggregate functions, but HAVING can.

8. How would you implement referential integrity using SQL? Explain with example.
Referential integrity ensures a Foreign Key value always refers to an existing Primary Key value in another table. For example, if a STUDENT table references GUARDIAN by GUID, you set:

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

This ensures a GUID in STUDENT cannot exist unless it also exists in GUARDIAN.

9. What is the NULL value? How is it different from a zero or blank space?
NULL represents missing, unknown, or inapplicable data. It is not the same as zero, which is a number, or blank space, which is a string with characters (spaces). NULL cannot be compared using = or <>; instead, IS NULL or IS NOT NULL are used. Calculations with NULL result in NULL.

10. List 5 examples of SQL Data Manipulation Language (DML) commands and describe their usage.

SELECT: Retrieves data from one or more tables.
INSERT: Adds new records to a table.
UPDATE: Modifies existing records in a table.
DELETE: Deletes specified records in a table.
MERGE: Combines data from multiple tables (advanced feature in some databases).
These commands allow manipulation and retrieval of data in the database.

11. Explain the use and syntax of pattern matching using LIKE operator in SQL.
The LIKE operator is used to search for a specified pattern in a column. Symbols % and _ are wildcards. For example, SELECT SName FROM STUDENT WHERE SName LIKE 'A%'; returns names starting with 'A'. SELECT SName FROM STUDENT WHERE SName LIKE '_n%'; returns names with 'n' as the second character.

12. What are aggregate functions? Demonstrate the use of SUM, AVG, and COUNT with SQL examples.
Aggregate functions operate on a group of values and return a single value. Example:

SUM: SELECT SUM(Salary) FROM EMPLOYEE;
AVG: SELECT AVG(Marks) FROM SCORES;
COUNT: SELECT COUNT(*) FROM STUDENT WHERE City='Delhi';
They are useful for reporting and analysis.

13. What is the difference between ALTER and UPDATE commands?
ALTER changes the structure of a table (adding/dropping columns), while UPDATE modifies the data in existing columns. For example, ALTER TABLE ADD column; changes structure, while UPDATE STUDENT SET Name='John' updates data.

14. Describe the use of set operations UNION, INTERSECT, and MINUS with examples.
UNION combines result sets of two queries, removing duplicates. INTERSECT returns rows common to both queries. MINUS returns rows in the first query not in the second.
Example:

SELECT Name FROM DANCE
UNION
SELECT Name FROM MUSIC;

This lists unique names from both tables.

15. Describe the importance of transactions in SQL and the role of COMMIT and ROLLBACK.
Transactions ensure that a series of SQL commands are executed as a single unit. COMMIT saves changes permanently; ROLLBACK reverts to the previous state if there is an error. This is crucial for maintaining data integrity.

16. How do you implement a constraint to ensure salary cannot be negative?
Use a CHECK constraint:

CREATE TABLE EMPLOYEE (Salary INT CHECK (Salary >= 0));

Or alter an existing column:

ALTER TABLE EMPLOYEE ADD CONSTRAINT chk_salary CHECK (Salary >= 0);

17. Write SQL queries to demonstrate INNER JOIN and OUTER JOIN.

-- INNER JOIN
SELECT STUDENT.SName, GUARDIAN.GName 
FROM STUDENT 
INNER JOIN GUARDIAN ON STUDENT.GUID = GUARDIAN.GUID;

-- LEFT OUTER JOIN
SELECT STUDENT.SName, GUARDIAN.GName 
FROM STUDENT 
LEFT JOIN GUARDIAN ON STUDENT.GUID = GUARDIAN.GUID;

INNER JOIN shows only matching records; LEFT JOIN shows all students, even without a matching guardian.

18. Explain the use of DEFAULT constraint with an example.
DEFAULT provides a default value if none is specified in INSERT. Example:

CREATE TABLE STUDENT (
  RollNumber INT PRIMARY KEY,
  Name VARCHAR(50),
  City VARCHAR(50) DEFAULT 'Delhi'
);

If City is not specified, it will be 'Delhi'.

19. Describe the possible consequences of missing the WHERE clause in UPDATE and DELETE commands.
If WHERE is omitted, UPDATE and DELETE commands will affect all rows in the table. For instance, UPDATE STUDENT SET City='Delhi'; will set the City to 'Delhi' for all students, and DELETE FROM STUDENT; will remove all records from the table.

20. What is a Cartesian product in SQL and when can it be useful? Provide an example.
A Cartesian product is obtained by combining each row of the first table with every row of the second table.
Example:

SELECT * FROM A, B;

It is useful in cases where all possible combinations are needed, but in practice, it can result in very large result sets and should be used with caution.

Habbit2Code

Search This Blog