CBSE Class 11 Informatics Practices Introduction to MySQL Questions

QUESTIONS & ANSWERS on MySQL

Question 1. What is MySQL?
Answer: It is an Open Source RDBMS (Relational Database Management System) software. It is available free of cost.
In simple words: MySQL is a free software used to store and manage data in tables.

Exam Tip: Remember that MySQL is both open-source and relational (uses tables with rows and columns).

Question 2. What is SQL?
Answer: SQL is a non-procedural universal data access language used to access and manipulate data stored in nearly all the databases available currently. SQL standards are defined by ANSI (American National Standards Institute). SQL statements are used to retrieve and update data in a database. SQL works with database programs like MySQL, MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc.
In simple words: SQL is the standard language used to talk to databases and perform operations like querying and updating.

Exam Tip: Note that SQL stands for Structured Query Language and is a non-procedural language (you specify what data you want, not how to get it).

Question 3. Differentiate between DDL and DML?
Answer: Data Definition Language (DDL): This is a category of SQL commands used to create, destroy, or restructure databases and tables. Examples of DDL commands are CREATE, DROP, and ALTER.
Data Manipulation Language (DML): This is a category of SQL commands used to manipulate data within tables. Examples of DML commands are INSERT, UPDATE, and DELETE.
In simple words: DDL manages the structure of the database, while DML manages the actual data inside the tables.

Exam Tip: Always associate DDL with table structure (CREATE, DROP, ALTER) and DML with table records (INSERT, UPDATE, DELETE).

Question 4. What is a constraint?
Answer: A constraint is a condition or check applied on a field or a set of fields to enforce rules on the data.
Examples include:
- NOT NULL (ensures that a column cannot have a null value)
- CHECK (ensures that all values in a column satisfy a certain condition)
- UNIQUE (ensures that all values in a column are distinct)
- PRIMARY KEY (uniquely identifies each record in a table)
In simple words: Constraints are rules set on table columns to prevent invalid data entry.

Exam Tip: List standard constraints like NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK when answering this.

Question 5. What are single row functions?
Answer: Single row functions work with a single row at a time. A single row function returns a result for every row of a queried table.
Examples of single row functions are Sqrt(), Concat(), Lcase(), Upper(), Day(), etc.
In simple words: Single row functions take one value at a time and return one output per row.

Exam Tip: Contrast single row functions with aggregate (multi-row) functions like SUM() or COUNT() which return a single value for a group of rows.

Question 6. Compare CHAR and VARCHAR data types.
Answer: The CHAR data-type stores fixed-length character strings such that strings having length smaller than the field size are padded on the right with spaces before being stored. The VARCHAR data-type supports variable-length character strings and therefore stores strings smaller than the field size without modification, saving storage space.
In simple words: CHAR uses fixed space with padding, while VARCHAR uses variable space depending on the actual string length.

Exam Tip: Mention space padding for CHAR and no padding for VARCHAR to get full credit.

Question 7. What are the differences between DELETE and DROP commands of SQL?
Answer: DELETE is a DML command used to delete specific or all rows from a table while retaining the table structure. DROP is a DDL command used to remove the entire table structure along with its data permanently from the database.
In simple words: DELETE removes rows inside a table, whereas DROP destroys the entire table itself.

Exam Tip: Remember that DELETE can be rolled back if used within a transaction, whereas DROP is an immediate DDL operation that cannot be rolled back in standard SQL.

Question 8. What do you understand by MySQL Server?
Answer: MySQL server is the core backend program that listens for client requests coming in over the network, accesses database contents according to those requests, and provides the results back to the client.
In simple words: MySQL Server is the program that processes database queries and manages database files.

Exam Tip: Understand the client-server architecture where the server waits for client requests and responds accordingly.

Question 9. What do you understand by MySQL Client?
Answer: MySQL clients are programs that connect to the MySQL Server and issue queries in a predefined format.
In simple words: A MySQL client is the user interface or application used to send queries to the server.

Exam Tip: Examples of MySQL clients include the MySQL command-line utility, MySQL Workbench, and PHP scripts.

Question 10. Explain with the help of an example that why should a transaction be executed as a whole or it should be not executed at all.
Answer: Suppose Raunak's account number is 3246 and his aunt's account number is 5135. In order to process the cheque presented by Raunak, the following two SQL commands need to be executed on the database maintained by the bank:
UPDATE Savings SET balance = balance - 2000 WHERE account_no = 5135;
UPDATE Savings SET balance = balance + 2000 WHERE account_no = 3246;
The above two updates should both take place. If the first update takes place and there is a system failure, the first updation should be undone. Either both the updations should be done, or if it is not possible for both to be done, then no updation should take place.
In simple words: Transactions follow the atomicity property - either all operations succeed together, or none of them happen.

Exam Tip: Mention the ACID property of transactions, specifically Atomicity, when explaining this concept.

Query Based Questions & Answers

Question 1. The Pincode column of table 'Post' is given below:
Pincode
10001
120012
300048
281001
i. SELECT Pincode from Post where Pincode LIKE "%1";
ii. SELECT Pincode from Post where Pincode LIKE "0%";
Answer:
i) 110001 (Note: Based on standard evaluation of the pattern matching ending with 1)
ii) No Output
In simple words: LIKE operator checks for pattern matching using wildcards like % for multiple characters.

Exam Tip: The symbol '%' represents zero or more characters in SQL LIKE patterns.

Question 2. A table "Animals" in a database has 3 columns and 10 records. What is the degree and cardinality of this table?
Answer: Degree = 3, Cardinality = 10
In simple words: Degree means the number of columns, and cardinality means the number of rows in a table.

Exam Tip: Degree = columns (attributes), Cardinality = rows (tuples).

Question 3. Answer the question based on the table VOTER given below:
Table: VOTER
Column Name: V_id, Data type: BIGINT, Size: 8, Constraints: Primary key, Description: Voter identification
Column Name: Vname, Data type: VARCHAR, Size: 25, Constraints: Not null, Description: Name of the voter
Column Name: Age, Data type: INT, Size: 3, Constraints: Check > 17, Description: Age should not less than equal to 17
Column Name: Address, Data type: VARCHAR2, Size: 30, Constraints: -, Description: Address of voter
Column Name: Phone, Data type: VARCHAR, Size: 10, Constraints: -, Description: Phone number of the voter

(i) Write the command to delete all the rows of particular voter from the table voter where voter ID between 10 and 20.
Answer: Delete from VOTER where V_id between 10 and 20;

(ii) Delete the table physically.
Answer: Drop table VOTER;
In simple words: Use DELETE to remove rows and DROP to remove the entire table.

Exam Tip: Differentiate between deleting records (DELETE) and deleting the schema/table (DROP).

Question 4. Write MySQL command to create a furniture table including all constraints.
Table: Furniture
ITEMNO: INT(5), PRIMARY KEY
ITEMNAME: VARCHAR(20), NOT NULL
TYPE: VARCHAR(20)
DATEOFSTOCK: DATE, DEFAULT '19/03/2010'
PRICE: INT(6)
DISCOUNT: INT(2)
Answer:
CREATE TABLE FURNITURE
(
ITEMNO INT(5) PRIMARY KEY,
ITEMNAME VARCHAR(20) NOT NULL,
TYPE VARCHAR(20),
DATE_STOCK DATE DEFAULT '2012-03-19',
PRICE INT(6),
DISCOUNT INT(2)
);
In simple words: Use the CREATE TABLE command along with appropriate data types and constraints specified for each column.

Exam Tip: Ensure data types, default values, and primary key constraints match the question specifications exactly.

Consider a database LOANS with the following table: Loan_Accounts

Question 1. Create the database LOANS.
Answer:
Mysql> Create Database LOANS;
In simple words: Use CREATE DATABASE to create a new database container.

Exam Tip: Always end SQL statements with a semicolon (;).

Question 2. Use the database LOANS.
Answer:
Mysql> Use LOANS;
In simple words: USE command selects the active database to work with.

Exam Tip: USE is a command to switch context to the specified database.

Question 3. Create the table Loan_Accounts and insert tuples in it.
Answer:
Mysql> Create table Loan_Acc (AccNo int primary key, Cust_Name varchar(30), Loan_Amount int, Installment int, Int_Rate decimal(5,3), Start_Date date, Interest decimal(7,2));
Mysql> Insert into Loan_Acc values(1,'R.K. GUPTA',300000,36,12.00,'2009-07-19',NULL);
In simple words: Define the columns with appropriate data types and insert rows using INSERT INTO.

Question 4. Display the details of all the loans.
Answer:
Mysql> Select * from Loan_Acc;
In simple words: Asterisk (*) fetches all columns from the table.

Exam Tip: SELECT * is shorthand for retrieving all columns.

Question 5. Display the AccNo, Cust_Name, and Loan_Amount of all the loans.
Answer:
Mysql> Select AccNo, Cust_Name, Loan_Amount from Loan_Acc;
In simple words: List specific column names separated by commas after SELECT.

Exam Tip: Only requested columns should be listed in projection queries.

Question 6. Display the details of all the loans with less than 40 instalments.
Answer:
Mysql> Select * from Loan_Acc where Instalment < 40;
In simple words: Use WHERE clause with condition < 40 to filter rows.

Exam Tip: Comparison operators like <, >, =, <=, >=, <> are used in WHERE clauses.

Question 7. Display the AccNo and Loan_Amount of all the loans started before 01-04-2009.
Answer:
Mysql> Select AccNo, Loan_Amount from Loan_Acc where Start_Date < '2009-04-01';
In simple words: Dates in SQL are compared in 'YYYY-MM-DD' format.

Exam Tip: Always enclose literal date strings in single quotes.

Question 8. Display the details of all the loans whose rate of interest is NULL.
Answer:
Mysql> Select * from Loan_Acc where Int_Rate is NULL;
In simple words: Use IS NULL to check for missing or empty values.

Exam Tip: Never use = NULL; always use IS NULL or IS NOT NULL.

Question 9. Display the details of all the loans whose rate of interest is not NULL.
Answer:
Mysql> Select * from Loan_Acc where Int_Rate is not NULL;
In simple words: Use IS NOT NULL to retrieve records where values are present.

Exam Tip: IS NOT NULL ensures excluded null rows are filtered out.

Question 10. Display the amounts of various loans from the table Loan_Accounts. A loan amount should appear only once.
Answer:
Mysql> Select DISTINCT Loan_Amount from Loan_Acc;
In simple words: DISTINCT removes duplicate rows from the query output.

Exam Tip: Use DISTINCT right after SELECT to eliminate duplicate values.

Question 11. Display the number of instalments of various loans from the table Loan_Accounts. An instalment should appear only once.
Answer:
Mysql> Select DISTINCT Instalment from Loan_Acc;
In simple words: DISTINCT ensures unique installment values are listed.

Exam Tip: DISTINCT applies to the combination of all selected columns in the projection list.

Question 12. Display the details of all the loans started after 31-12-2008 for which the number of instalments are more than 36.
Answer:
Mysql> Select * from Loan_Acc where Start_Date > '2008-12-31' and Instalment > 36;
In simple words: Use AND operator to combine multiple conditions where both must be true.

Exam Tip: Logical operators like AND, OR, and NOT connect multiple search conditions.

Question 13. Display the Cust_Name and Loan_Amount for all the loans which do not have number of instalments 36.
Answer:
Mysql> Select Cust_Name, Loan_Amount from Loan_Acc where Instalment <> 36;
In simple words: Inequality operator (<>) filters out rows matching a specific value.

Exam Tip: Both <> and != are valid inequality operators in SQL.

Question 14. Display the Cust_Name and Loan_Amount for all the loans for which the loan amount is less than 500000 or int_rate is more than 12.
Answer:
Mysql> Select Cust_Name, Loan_Amount from Loan_Acc where Loan_Amount < 500000 or Int_Rate > 12;
In simple words: OR operator returns rows satisfying at least one of the conditions.

Question 15. Display the details of all the loans which started in the year 2009.
Answer:
Mysql> Select * from Loan_Acc where Year(Start_Date) = 2009;
In simple words: YEAR() function extracts the year portion from a date field.

Exam Tip: Built-in date functions like YEAR(), MONTH(), and DAYOFMONTH() simplify date filtering.

Question 16. Display the details of all the loans whose Loan_Amount is in the range 400000 to 500000.
Answer:
Mysql> Select * from Loan_Acc where Loan_Amount between 400000 and 500000;
In simple words: BETWEEN operator checks if a value falls within a specified range inclusive of boundaries.

Exam Tip: BETWEEN is inclusive of both start and end values.

Question 17. Display the details of all the loans whose rate of interest is in the range 11% to 12%.
Answer:
Mysql> Select * from Loan_Acc where Int_Rate between 11 and 12;
In simple words: BETWEEN operator can be used with numeric rates as well.

Exam Tip: Range queries using BETWEEN simplify expressions involving lower and upper limits.

Question 19. Display the Cust_Name and Loan_Amount for all the loans for which the number of instalments are 24, 36, or 48. (Using IN operator)
Answer:
Mysql> Select Cust_Name, Loan_Amount from Loan_Acc where Instalment IN (24, 36, 48);
In simple words: IN operator checks if a value matches any value in a given set.

Exam Tip: IN is a cleaner alternative to multiple OR conditions.

Question 20. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name ends with 'Sharma'.
Answer:
Mysql> Select AccNo, Cust_Name, Loan_Amount from Loan_Acc where Cust_Name like '%Sharma';
In simple words: %Sharma matches any name ending with the exact string 'Sharma'.

Exam Tip: Wildcard % at the beginning matches any prefix string ending with the given suffix.

Question 21. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name ends with 'a'.
Answer:
Mysql> Select AccNo, Cust_Name, Loan_Amount from Loan_Acc where Cust_Name like '%a';
In simple words: %a matches names ending with the letter 'a'.

Exam Tip: Use LIKE with trailing wildcards for suffix matching.

Question 22. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name contains 'a'.
Answer:
Mysql> Select AccNo, Cust_Name, Loan_Amount from Loan_Acc where Cust_Name like '%a%';
In simple words: %a% matches names containing the letter 'a' anywhere.

Exam Tip: Enclosing a character in percent signs searches for substrings anywhere in the field.

Question 23. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name does not contain 'P'.
Answer:
Mysql> Select AccNo, Cust_Name, Loan_Amount from Loan_Acc where NOT (Cust_Name like '%P%');
In simple words: NOT operator negates the LIKE condition to exclude matching rows.

Exam Tip: You can also use NOT LIKE in MySQL for cleaner syntax.

Question 24. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name contains 'a' as the second last character.
Answer:
Mysql> Select AccNo, Cust_Name, Loan_Amount from Loan_Acc where Cust_Name like '%a_';
In simple words: Underscore (_) represents exactly one single character wildcard.

Exam Tip: % matches any number of characters, while _ matches exactly one character.

Question 25. Display the details of all the loans in the ascending order of their Loan_Amount.
Answer:
Mysql> Select * from Loan_Acc ORDER BY Loan_Amount;
In simple words: ORDER BY sorts query results in ascending order by default.

Exam Tip: ASC is ascending by default, while DESC specifies descending order.

Question 28. Display the details of all the loans in the descending order of their Start_Date.
Answer:
Mysql> Select * from Loan_Acc ORDER BY Start_Date DESC;
In simple words: DESC keyword sorts the results from highest to lowest or latest to oldest.

Exam Tip: Always place DESC after the column name in the ORDER BY clause.

Question 29. Display the details of all the loans in the ascending order of their Loan_Amount and within Loan_Amount in the descending order of their Start_Date.
Answer:
Mysql> Select * from Loan_Acc ORDER BY Loan_Amount, Start_Date DESC;
In simple words: Multiple columns can be specified in ORDER BY for secondary sorting.

Question 30. Put the interest rate 11.50% for all the loans for which interest rate is NULL.
Answer:
Mysql> Update Loan_Acc SET Int_Rate = 11.50 Where Int_Rate IS NULL;
In simple words: UPDATE statement modifies existing data based on conditions.

Exam Tip: Always include a WHERE clause in UPDATE statements unless updating all rows intentionally.

Question 31. Increase the interest rate by 0.5% for all the loans for which the loan amount is more than 400000.
Answer:
Mysql> Update Loan_Acc SET Int_Rate = Int_Rate + 0.5 Where Loan_Amount > 400000;
In simple words: Column values can be updated dynamically using arithmetic operations.

Exam Tip: Arithmetic expressions like column_name = column_name + value update existing figures.

Question 32. For each loan replace Interest with (Loan_Amount * Int_Rate * Instalments) / 12 * 100.
Answer:
Mysql> Update Loan_Acc SET Interest = (Loan_Amount * Int_Rate * Instalments) / (12 * 100);
In simple words: Calculate interest values across all rows using mathematical formulas.

Exam Tip: Use parentheses properly to maintain correct operator precedence in calculations.

Question 33. Delete the records of all the loans whose start date is before 2007.
Answer:
Mysql> Delete From Loan_Acc Where Year(Start_Date) < 2007;
In simple words: DELETE removes records matching the specified year criteria.

Exam Tip: Combine DELETE with conditional functions like YEAR() for date-based deletions.

Question 34. Delete the records of all the loans of 'K.P. Jain'.
Answer:
Mysql> Delete From Loan_Acc Where Cust_Name = 'K.P. Jain';
In simple words: Delete rows belonging to a specific customer name.

Exam Tip: String literals in comparisons must be enclosed in single quotes.

Question 35. Add another column Category of type CHAR(1) in the Loan table.
Answer:
Mysql> Alter Table Loan_Acc ADD (Category CHAR(1));
In simple words: ALTER TABLE is a DDL command used to modify table structure by adding new columns.

Exam Tip: Use ADD keyword with ALTER TABLE to include new fields.

Question 36. Find the output of the following query:
SELECT cust_name, LENGTH(Cust_Name), LCASE(Cust_Name), UCASE(Cust_Name) FROM Loan_Accounts WHERE Int_Rate < 11.00;
Answer:
The output table is as follows:

In simple words: String functions LENGTH(), LCASE(), and UCASE() compute character counts and change text case.

Exam Tip: Remember that spaces and special characters are counted in string length functions.

Question 37. Find the output of the following query:
SELECT LEFT(Cust_Name, 3), Right(Cust_Name, 3), SUBSTR(Cust_Name, 1, 3) FROM Loan_Accounts WHERE Int_Rate > 10.00;
Answer:
The output table is as follows:

In simple words: LEFT and RIGHT extract characters from respective string ends, while SUBSTR extracts a substring from a starting position.

Exam Tip: SUBSTR(string, start, length) extracts characters starting from the given index position.

Question 38. Find the output of the following query:
SELECT RIGHT(Cust_Name, 3), SUBSTR(Cust_Name, 5) FROM Loan_Accounts;
Answer:
The output table is as follows:

In simple words: SUBSTR without length extracts all characters from the specified start position to the end of the string.

Exam Tag: Pay attention to character indexing starting from position 1 in MySQL.

Question 39. Find the output of the following query:
SELECT DAYOFMONTH(Start_Date) FROM Loan_Accounts;
Answer:
The output table is as follows:

In simple words: DAYOFMONTH extracts the numeric day value from each date entry.

Exam Tip: Date extraction functions return numeric integers corresponding to date components.