CBSE Class 12 Informatics Practices MYSQL Revision Tour Assignment

Question 1. What do you mean by a Database Management System?
Answer: Database Management System (DBMS) is a collection of programs and files that allow a user to define the structure of a database, store data into it, modify the structure, and manipulate the stored data.
In simple words: A DBMS is software that helps us create, store, manage, and retrieve data in an organized way.

Exam Tip: Remember the core components: defining structure, storing data, and manipulating data.

Question 2. What do you mean by Relational database?
Answer: Relational Database is a type of database in which relations (tables consisting of rows and columns) are used as its basic building elements to store and organize data.
In simple words: A relational database stores data in rows and columns inside tables that can be linked together.

Exam Tip: Key terms to include in your answer are rows, columns, tables, and relations.

Question 3. What is a foreign key?
Answer: A foreign key is an attribute (or a set of attributes) in one table whose values are derived from the primary key of another table, establishing a link between the two tables.
In simple words: A foreign key points to the primary key of another table to connect them.

Exam Tip: Always mention that a foreign key references a primary key in another relation.

Question 4. What is primary key?
Answer: A primary key is a unique key in a relation which can uniquely identify each tuple (row) in a given relation and does not accept null values.
In simple words: A primary key is a unique identifier for every row in a table.

Exam Tip: Mention both uniqueness and non-null properties for full marks.

Question 5. What is SQL?
Answer: SQL stands for Structured Query Language. It is a standard language used to create, access, maintain, and manipulate data stored in a relational database.
In simple words: SQL is the standard language used to talk to and manage databases.

Exam Tip: Write the full form clearly and state its purpose.

Question 6. What is referential integrity?
Answer: Referential integrity is a rule in DBMS which ensures that relationships between records in related tables remain valid, preventing users from accidentally deleting or changing related data inconsistently.
In simple words: It ensures that table links remain valid and prevents orphaned records.

Exam Tip: Emphasize that it maintains consistency across related tables.

Question 7. What is MySql?
Answer: MySQL is a popular open-source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL) for database management.
In simple words: MySQL is a free, open-source database software that runs SQL queries.

Exam Tip: Mention both open-source and RDBMS.

Question 8. What is DDL?
Answer: DDL stands for Data Definition Language. It provides commands to define, create, alter, and drop the schema and structure of database objects like tables.
In simple words: DDL commands are used to create or modify the structure of database tables.

Exam Tip: Give examples like CREATE, ALTER, and DROP.

Question 9. What are DML commands?
Answer: DML stands for Data Manipulation Language. DML commands are used to query and manipulate data stored within database tables, including insertion, deletion, and modification.
In simple words: DML commands are used to add, delete, update, and view data inside tables.

Exam Tip: Give examples like SELECT, INSERT, UPDATE, and DELETE.

Question 10. Maximum how many characters can be stored in a (i) text literals (ii) numeric literal
Answer: (i) Text literals can have up to 4000 bytes.
(ii) A numeric literal can store up to 53 digits.
In simple words: Text data can hold 4000 bytes, and numeric data can hold up to 53 digits.

Exam Tip: Memorize these standard limits for database literals.

Question 11. What is null value in MySql?
Answer: In MySQL, if a column in a row has no assigned value or an unknown value, then the column is said to contain a null value.
In simple words: A null value means the field is completely empty or has no value.

Exam Tip: Distinguish between null (no value) and zero or blank space.

Question 12. Which keyword eliminates redundant data in from a query result?
Answer: DISTINCT
In simple words: The DISTINCT keyword removes duplicate rows from query outputs.

Exam Tip: Use SELECT DISTINCT column_name FROM table_name to filter duplicates.

Question 13. How would you display system date as the result of a query?
Answer: CURDATE()
In simple words: Use the CURDATE() function to get the current system date.

Exam Tip: Syntax: SELECT CURDATE();

Question 14. What is NOW() function in MySql?
Answer: The NOW() function returns the current system date and time.
In simple words: NOW() gives both today's date and the exact current time.

Exam Tip: Syntax: SELECT NOW();

Question 15. What is NOT NULL constraint?
Answer: The NOT NULL constraint imposes a restriction that a column cannot accept or be left with a blank or null value during insertions or updates.
In simple words: It ensures a column must always have a value and cannot be left empty.

Exam Tip: Applied during table creation to make mandatory fields.

Question 16. What is error in following statement? UPDATE EMPL;
Answer: The WHERE clause is missing in the given query, which would cause all rows in the table to be updated unintentionally.
In simple words: An UPDATE command requires a WHERE clause to specify which rows to change.

Exam Tip: Always verify that UPDATE statements include a WHERE clause.

Question 17. Identify the error? DELETE ALL FROM TABLE EMP;
Answer: There is no need to write the keywords ALL and TABLE in the DELETE query.
Correct form is: DELETE FROM EMP;
In simple words: The correct syntax to delete records is DELETE FROM tablename.

Exam Tip: Do not mix SQL DELETE syntax with DROP or extraneous keywords.

Question 18. Differentiate WHERE and HAVING clause?
Answer: The WHERE clause is used to filter individual rows before any grouping or aggregation takes place, whereas the HAVING clause is used to filter groups created by the GROUP BY clause in connection with aggregate functions.
In simple words: WHERE filters individual rows, while HAVING filters aggregated groups.

Exam Tip: Remember: WHERE comes before GROUP BY, and HAVING comes after GROUP BY.

Question 19. How SQL commands are classified?
Answer: SQL commands are classified into three main categories:
(i) Data Definition Language (DDL) - Commands that allow us to perform tasks related to data definition, such as creating, altering, and dropping tables.
(ii) Data Manipulation Language (DML) - Commands that allow us to perform data retrieval, insertion, and modification of data stored in a database.
(iii) Transaction Control Language (TCL) - Commands that allow us to manage and control database transactions.
In simple words: SQL commands are divided into DDL (for structure), DML (for data), and TCL (for transactions).

Exam Tip: List all three categories with clear examples for full marks.

Question 20. What is difference between char and varchar?
Answer: The main difference between CHAR and VARCHAR is fixed length versus variable length storage. The CHAR datatype specifies a fixed-length character string where MySQL pads values with spaces to match the defined length \(n\). On the other hand, VARCHAR(\(n\)) stores variable-length strings, utilizing only the storage required for the actual string length up to \(n\) characters.
In simple words: CHAR uses fixed space regardless of string size, while VARCHAR adjusts its storage space based on the actual length of the string.

Exam Tip: Use CHAR for fixed-size fields like country codes and VARCHAR for variable-length fields like names.

Question 21. What do you understand by the terms primary key and degree of a relation in relational data base?
Answer: Primary Key: A primary key is a set of one or more attributes that can uniquely identify tuples within a relation.
Degree of a relation: The total number of attributes (columns) present in a relation is called the degree of that relation.
In simple words: Primary key uniquely identifies rows, and degree counts the number of columns.

Exam Tip: Do not confuse degree (columns) with cardinality (rows).

Question 22. What do you understand by the candidate key and cardinality of a relation in relational data base?
Answer: Candidate Key: All attribute combinations inside a relation that can uniquely identify a row are candidate keys, as they are candidates for the primary key position.
Cardinality of a relation: The total number of tuples (rows) present in a relation is known as its cardinality.
In simple words: Candidate keys are potential unique identifiers, and cardinality counts the number of rows.

Exam Tip: Cardinality = number of rows; Degree = number of columns.

Question 23. Consider the following tables Item and Customer. Write SQL commands for the statement (i) to (iv) and give outputs for SQL queries (v) to (viii)
Answer:
(i) To display the details of those Customers whose city is Delhi:
\(\text{SELECT} * \text{FROM Customer WHERE City} = \text{'Delhi'};\)
(ii) To display the details of Item whose Price is in the range of 35000 to 55000 (Both values included):
\(\text{SELECT} * \text{FROM Item WHERE Price BETWEEN 35000 AND 55000};\)
(iii) To display the Customer Name, City from table Customer, and Item Name and Price from table Item, with their corresponding matching I_ID:
\(\text{SELECT Customer.CustomerName, Customer.City, Item.ItemName, Item.Price FROM Item, Customer WHERE Item.I\_ID} = \text{Customer.I\_ID};\)
(iv) To increase the Price of all Items by 1000 in the table Item:
\(\text{UPDATE Item SET Price} = \text{Price} + 1000;\)
(v) SELECT DISTINCT City FROM Customer;
Output: Delhi, Mumbai, Bangalore
(vi) SELECT ItemName, MAX(Price), COUNT(*) FROM Item GROUP BY ItemName;
Output:
Personal Computer 37000 3
Laptop 57000 2
(vii) SELECT CustomerName, Manufacturer FROM Item, Customer WHERE Item.I_ID = Customer.I_ID;
Output:
N.Roy PQR
H.Singh XYZ
R.Pandey COMP
C.Sharma PQR
K.Agarwal ABC
(viii) SELECT ItemName, Price * 100 FROM Item WHERE Manufacturer = 'ABC';
Output:
Personal Computer 3500000
Laptop 5500000
In simple words: These queries demonstrate selection, joining, updating, and aggregate functions on tables.

Exam Tip: Pay close attention to table aliases and matching foreign key conditions in joins.

UNSOLVED QUESTIONS

Question 1. Write MySql command that will be used to show all the databases which is already created in MySql.
Answer: SHOW DATABASES;
In simple words: Use the SHOW DATABASES command to list all existing databases.

Exam Tip: Command must end with a semicolon (;).

Question 2. The Department column and date of joining of a table Employee is given below: (Biology, Zoology, Bio_Tech, Psycology)
(i) Based on the above table write SQL Query to display the name of those departments whose name ends with logy?
(ii) Based on the above table write SQL Query to display the name of those departments whose name starts with Bi.
Answer:
(i) SELECT Department FROM Employee WHERE Department LIKE '%logy';
(ii) SELECT Department FROM Employee WHERE Department LIKE 'Bi%';
In simple words: Use wildcard characters like % with LIKE to search for patterns at the start or end of strings.

Exam Tip: '%' matches any sequence of characters in pattern matching.

Question 3. What is the degree and cardinality of the above given Employee table?
Answer:
Degree = 2 (since there are 2 columns: Department and Date_of_Joining)
Cardinality = 4 (since there are 4 rows of data)
In simple words: Degree counts columns (2), and cardinality counts rows (4).

Exam Tip: Always count columns for degree and rows for cardinality.

Question 4. Differentiate between Primary key and Unique Key?
Answer: A Primary Key uniquely identifies each row in a table and does not allow any NULL values. A table can have only one primary key. On the other hand, a Unique Key also ensures that all values in a column are distinct, but it allows a single NULL value per column, and a table can have multiple unique keys.
In simple words: Primary key cannot be null and there is only one per table, whereas a unique key can accept one null value and a table can have multiple unique keys.

Exam Tip: Mention the allowance of NULL values to distinguish between them clearly.

Question 5. Consider the following tables WORKERS and DESIG. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii).
Answer:
(i) To display W_ID, Firstname, address and City of all employees living in New York from the Table Workers:
\(\text{SELECT W\_ID, FIRSTNAME, ADDRESS, CITY FROM WORKERS WHERE CITY} = \text{'New York'};\)
(ii) To display the content of workers table in ascending order of LASTNAME:
\(\text{SELECT} * \text{FROM WORKERS ORDER BY LASTNAME ASC};\)
(iii) To display the FIRSTNAME, LASTNAME and Total Salary of all Clerks from the tables WORKERS And DESIG, where Total salary is calculated as Salary + benefits:
\(\text{SELECT FIRSTNAME, LASTNAME, SALARY} + \text{BENEFITS FROM WORKERS, DESIG WHERE WORKERS.W\_ID} = \text{DESIG.W\_ID AND DESIGINATION} = \text{'Clerk'};\)
(iv) To display the minimum salary among managers and Clerks From the tables DESIG:
\(\text{SELECT MIN(SALARY) FROM DESIG WHERE DESIGINATION IN ('Manager', 'Clerk');}\)
(v) SELECT FIRSTNAME, SALARY FROM WORKERS, DESIG WHERE DESIGNATION = 'MANAGER' AND WORKERS.W_ID = DESIG.W_ID;
Output: Displays the firstname and salary of all managers.
(vi) SELECT COUNT(DISTINCT DESIGNATION) FROM DESIG;
Output: 3 (Manager, Director, Clerk, Salesman count unique designations)
(vii) SELECT DESIGNATION, SUM(SALARY) FROM DESIG GROUP BY DESIGNATION HAVING COUNT(*) < 3;
Output: Displays the designation and sum of salaries for groups having fewer than 3 employees.
(viii) SELECT SUM(BENEFITS) FROM DESIG WHERE DESIGNATION = 'salesman';
Output: Total benefits for all salesmen combined.
In simple words: These queries cover selection, sorting, joins, aggregate functions, and grouping.

Exam Tip: Verify table joins and column names carefully in complex queries.

Question 1. Define a transaction.
Answer: A transaction is a logical unit of database work that comprises a sequence of operations which must either all succeed together or fail together completely as an atomic unit.
In simple words: A transaction is a group of database steps that execute entirely or not at all.

Exam Tip: Mention atomicity as a core property of transactions.

Question 2. What are the two ways in which multiple transactions can be executed?
Answer: Multiple transactions can be executed in one of the following two ways:
(i) Serially
(ii) Concurrently
In simple words: Transactions can run one after another (serial) or simultaneously (concurrent).

Exam Tip: List both execution modes clearly.

Question 3. What is a savepoint?
Answer: A savepoint is a marker within a transaction up to which all changes have been safely saved, allowing a transaction to roll back only part of its operations instead of undoing the entire transaction.
In simple words: A savepoint lets you roll back to a specific checkpoint without canceling the whole transaction.

Exam Tip: Essential for partial rollbacks in complex transactions.

Question 4. What do you understand by a database transaction?
Answer: A database transaction is a sequence of one or more SQL operations treated as a single logical unit of work that maintains database consistency by either committing all changes permanently or rolling back everything upon failure.
In simple words: A database transaction ensures that all related database operations succeed together or get cancelled completely.

Exam Tip: Reinforce the all-or-nothing execution rule.

Question 5. What do you understand by transaction COMMIT and ROLLBACK?
Answer: Committing a transaction means all steps have executed successfully, and all data modifications are made permanent in the database. Transaction ROLLBACK means the transaction failed or was aborted before completion, so all temporary data changes made by that transaction are undone, and the database returns to its previous consistent state.
In simple words: COMMIT saves changes permanently, while ROLLBACK undoes all changes made during the transaction.

Exam Tip: Use COMMIT to finalize and ROLLBACK to abort and restore database state.