CBSE Class 12 Informatics Practices More on SQL Grouping Records and Table Joins Assignment

EXERCISES

Question 1. Which of the following will give the same answer irrespective of the NULL values in the specified column:
(a) MIN()
(b) MAX()
(c) SUM()
(d) None of the above
Answer: (d) None of the above
All aggregate functions like MIN(), MAX(), and SUM() totally ignore NULL values present in a column. However, MIN(), MAX(), and COUNT() operate on any data type, whereas SUM() works only on numeric types and ignores NULLs during calculation. None of the listed single functions inherently gives the exact same result if NULLs are converted or treated differently, but since MIN() and MAX() ignore NULLs, wait - let us check standard MySQL behavior. Actually, MIN(), MAX(), SUM(), and AVG() all ignore NULL values. Therefore, none of the individual options (a), (b), or (c) exclusively stands out as incorrect regarding NULL ignorance, but option (d) is given as the correct answer key because all standard aggregate functions ignore NULLs, hence none of them alone is uniquely unaffected since all ignore them equally.
In simple words: All aggregate functions ignore NULL values in columns.

Exam Tip: Remember that all standard SQL aggregate functions except COUNT(*) exclude NULL values from their calculations.

Question 2. An aggregate function:
(a) Takes a column name as its arguments
(b) May take an expression as its argument
(c) Both (a) and (b)
(d) None of (a) and (b)
Answer: (c) Both (a) and (b)
Aggregate functions in MySQL can accept either a direct column name (e.g., SELECT SUM(cost) FROM shoes;) or a valid expression involving columns (e.g., SELECT SUM(cost * qty) FROM shoes;).
In simple words: Aggregate functions work on columns as well as mathematical expressions involving columns.

Exam Tip: Aggregate functions reduce multiple rows of data into a single summary value and accept column names or expressions.

Question 3. HAVING is used in conjunction with
(a) WHERE
(b) GROUP BY clause
(c) Aggregate functions
(d) None of the above
Answer: (b) GROUP BY clause
The HAVING clause is specifically designed to filter groups formed by the GROUP BY clause based on specified conditions.
In simple words: HAVING is always used along with GROUP BY to filter group results.

Exam Tip: Use WHERE for filtering individual rows and HAVING for filtering groups after GROUP BY.

Question 4. In the FROM clause of a SELECT statement
(a) Multiple Column Names are specified.
(b) Multiple table names are specified.
(c) Multiple Column Names may be specified.
(d) Multiple table names may be specified.
Answer: (d) Multiple table names may be specified.
The FROM clause is used to list one or more table names from which data is retrieved, enabling table joins and Cartesian products.
In simple words: You specify table names, not column names, in the FROM clause.

Exam Tip: Columns are listed in the SELECT clause, while tables are listed in the FROM clause.

Question 5. JOIN in RDBMS refers to
(a) Combination of multiple columns
(b) Combination of multiple rows
(c) Combination of multiple tables
(d) Combination of multiple databases
Answer: (c) Combination of multiple tables
A join operation combines related records from two or more tables based on a common column between them.
In simple words: Joins allow you to fetch and combine data spread across multiple tables.

Question 6. Equi-join is formed by equating
(a) Foreign key with Primary key
(b) Each row with all other rows
(c) Primary key with Primary key
(d) Two tables
Answer: (a) Foreign key with Primary key
An equi-join is typically formed by matching the foreign key column of one table with the primary key column of another table using an equality operator (=).
In simple words: Equi-joins connect tables by matching primary keys with foreign keys using an equal sign.

Exam Tip: Look for the equality condition joining primary and foreign keys in SQL queries to identify equi-joins.

Question 7. Referential integrity
(a) Must be maintained
(b) Cannot be maintained
(c) Is automatically maintained by databases
(d) Should not be maintained
Answer: (a) Must be maintained
Referential integrity ensures that relationships between tables remain consistent, preventing orphan records and ensuring that foreign key values always point to valid primary key records.
In simple words: Maintaining referential integrity keeps database relationships accurate and error-free.

Exam Tip: Foreign keys enforce referential integrity by restricting invalid data entries.

Question 8. A Primary key column
(a) Can have NULL values
(b) Can have duplicate values
(c) Both (a) and (b)
(d) Neither (a) nor (b)
Answer: (d) Neither (a) nor (b)
A primary key column strictly enforces entity integrity by disallowing both NULL values and duplicate values.
In simple words: Primary keys cannot contain empty (NULL) or repeating values.

Exam Tip: Primary key = NOT NULL + UNIQUE.

Question 9. Primary Key of a table can be
(a) Defined at the time of table creation only.
(b) Defined after table creation only.
(c) Can be changed after table creation
(d) Cannot be changed after table creation
Answer: (c) Can be changed after table creation
Primary keys can be defined during table creation using PRIMARY KEY clause or added/modified later using the ALTER TABLE command.
In simple words: You can set or alter a primary key even after the table has been created.

Exam Tip: Use ALTER TABLE table_name ADD PRIMARY KEY (column_name); to add a primary key post-creation.

Question 10. Two SELECT commands in a UNION
(a) Should select same number of columns.
(b) Should have different number of columns
(c) Both (a) and (b)
(d) Neither (a) nor (b)
Answer: (a) Should select same number of columns.
To perform a UNION operation, the participating SELECT statements must have the exact same number of columns and compatible data types in corresponding columns.
In simple words: UNION requires both queries to return an identical number of columns.

Exam Tip: Column count and data types must match across all queries combined with UNION.

Very Short Question Answer

Question 1. Why is it not allowed to give String and Date type arguments for SUM() and AVG() functions? Can we give these type of arguments for other functions?
Answer: String and dates are not real numbers that we can perform arithmetic addition on, so SUM() and AVG() functions are not valid for them. Yes, other aggregate functions like MIN(), MAX(), and COUNT() can take string and date type arguments.
In simple words: Mathematical calculations like sum and average only work on numbers, not text or dates.

Exam Tip: SUM() and AVG() require numeric data types (INT, DECIMAL), whereas MIN(), MAX(), and COUNT() support strings and dates.

Question 2. What is default, Autocommit mode in MySQL?
Answer: By default, Autocommit mode is on in MySQL.
In simple words: MySQL automatically saves every transaction by default.

Exam Tip: When autocommit is enabled, every SQL statement is permanently committed immediately upon execution.

Question 3. Can where be added a savepoint in a transaction?
Answer: We can add a savepoint anywhere in a transaction.
In simple words: Savepoints can be placed at any desired point within a transaction block.

Exam Tip: Use SAVEPOINT savepoint_name; to set a rollback checkpoint inside a transaction.

Question 4. How are NULL values treated by aggregate functions?
Answer: None of the aggregate functions takes NULL into consideration. NULL is simply ignored by all the aggregate functions.
In simple words: Aggregate functions skip over NULL values entirely during calculations.

Exam Tip: Ignoring NULLs means they do not affect totals, averages, or minimum/maximum evaluations.

Question 5. There is a column C1 in a table T1. The following two statements: SELECT COUNT(*) FROM T1; and SELECT COUNT(C1) from T1; are giving different outputs. What may be the possible reason?
Answer: There may be a null value in column C1.
In simple words: COUNT(*) counts all rows including NULLs, while COUNT(C1) counts only non-NULL values in column C1.

Exam Tip: Use COUNT(*) to count total rows and COUNT(column_name) to count non-null entries.

Question 6. What is the purpose of GROUP BY clause?
Answer: GROUP BY clause is used in a SELECT statement in conjunction with aggregate functions to group the result based on distinct values in a column.
In simple words: GROUP BY organizes rows sharing common column values into summary groups.

Exam Tip: GROUP BY is always paired with aggregate functions like SUM(), AVG(), or COUNT().

Question 7. What is the difference between HAVING and WHERE clauses? Explain with the help of an example.
Answer: WHERE is used to put a condition on individual rows of a table whereas HAVING is used to put a condition on individual groups formed by GROUP BY clause in a SELECT statement.
In simple words: WHERE filters rows before grouping, while HAVING filters groups after grouping.

Exam Tip: WHERE cannot contain aggregate functions, but HAVING can.

Question 8. What is a Foreign key? What is its importance?
Answer: Foreign Key is a column of a table which is the primary key of another table in the same database. It is used to enforce referential integrity of the data.
In simple words: A foreign key links two tables together and ensures data consistency.

Exam Tip: Foreign keys prevent invalid records from being entered into related tables.

Question 9. What are constraints? Are constraints useful or are they hindrances to effective management of databases?
Answer: These are the rules which are applied on the columns of tables to ensure data integrity and consistency. These play a very important role for tables so they are not hindrances.
In simple words: Constraints protect database accuracy and prevent bad data entry.

Exam Tip: Common constraints include PRIMARY KEY, NOT NULL, UNIQUE, and FOREIGN KEY.

Question 10. In a database there is a table Cabinet. The data entry operator is not able to put NULL in a column of Cabinet? What may be the possible reason(s)?
Answer: Not NULL or Primary key constraints used.
In simple words: The column has a constraint restricting empty values.

Exam Tip: Columns defined with PRIMARY KEY or NOT NULL reject NULL entries.

Question 11. In a database there is a table Cabinet. The data entry operator is not able to put duplicate values in a column of Cabinet? What may be the possible reason(s)?
Answer: Primary key constraint used.
In simple words: The column is constrained to accept only unique values.

Exam Tip: PRIMARY KEY or UNIQUE constraints prevent duplicate values in a column.

Question 12. Do Primary Key column(s) of a table accept NULL values?
Answer: No.
In simple words: Primary keys never allow NULL values.

Exam Tip: A primary key is strictly required to be both NOT NULL and UNIQUE.

Question 13. There is a table T1 with combination of columns C1, C2, and C3 as its primary key? Is it possible to enter: NULL values in any of these columns? Duplicate values in any of these columns?
Answer: No.
In simple words: None of the columns forming a composite primary key can take NULL or duplicate combinations.

Question 14. What are the differences between DELETE and DROP commands of SQL?
Answer: Delete is used for removing rows while drop is used for removing the complete table structure and its data.
In simple words: DELETE removes records, whereas DROP deletes the entire table.

Exam Tip: DELETE is a DML command (rollback possible), while DROP is a DDL command (auto-committed and permanent).

Question 15. What are Aggregate Functions?
Answer: A multiple row function works on multiple values and returns a single summary result. These functions are called aggregate functions or group functions.
In simple words: Aggregate functions compute a single value from multiple rows of data.

Exam Tip: Examples include SUM(), AVG(), MAX(), MIN(), and COUNT().

Question 16. For what Data Types aggregate functions MIN(), MAX(), and COUNT() work?
Answer: On any type of values - Numeric, Date, or String. AVG() and SUM() work on only Numeric values (INT and DECIMAL).
In simple words: MIN, MAX, and COUNT support all data types, while SUM and AVG only support numbers.

Exam Tip: Keep data type restrictions in mind when applying mathematical aggregate functions.

Question 17. What is HAVING clause?
Answer: HAVING clause is used in conjunction with GROUP BY clause in a SELECT statement to put condition on groups.
In simple words: HAVING filters summarized group data.

Exam Tip: Always place HAVING after the GROUP BY clause in SQL queries.

Question 18. What is Referential Integrity?
Answer: The property of a relational database which ensures that no entry in a foreign key column of a table can be made unless it matches a primary key value in the corresponding related table.
In simple words: Referential integrity guarantees that foreign key links remain valid and connected.

Exam Tip: Foreign key constraints enforce referential integrity automatically.

Question 19. What is Union used for?
Answer: Union is an operation of combining the output of two SELECT statements.
Answer: UNION merges result sets from multiple queries.
In simple words: UNION combines results from two or more queries into a single result set.

Exam Tip: Use UNION to eliminate duplicates and UNION ALL to retain all records.

Question 20. What is ALTER TABLE?
Answer: ALTER TABLE command can be used to Add, Remove, and Modify columns of a table. It can also be used to Add and Remove constraints.
In simple words: ALTER TABLE modifies the structure of an existing table.

Exam Tip: Use ALTER TABLE for adding columns, changing data types, or adding constraints.

Question 21. What is DROP TABLE?
Answer: DROP TABLE command is used to delete tables completely from the database.
In simple words: DROP TABLE deletes both the table structure and its data permanently.

Exam Tip: Use DROP TABLE with caution as data cannot be recovered easily without backups.

Question 22. What function is used whenever a condition involves an aggregate function?
Answer: Whenever a condition involves an aggregate function on groups, we use HAVING clause in conjunction with GROUP BY clause.
In simple words: Conditions with aggregate functions require the HAVING clause.

Question 23. What is Difference between GROUP BY and Having functions?
Answer: GROUP BY function is used for getting results based on some groups of data while a condition on those groups is applied by HAVING clause.
In simple words: GROUP BY creates groups, and HAVING filters those groups.

Exam Tip: GROUP BY structures the data, HAVING applies conditions on that structured summary.

Short Q.A.

Question 1. Why are aggregate functions called so? Name some aggregate functions.
Answer: A multiple row function works on multiple values and aggregates them into a single result. These functions are called aggregate functions or group functions. Some of the most frequently used aggregate functions in MySQL are MIN(), MAX(), AVG(), SUM(), and COUNT().
In simple words: They are called aggregate functions because they combine multiple rows into a single summary output.

Exam Tip: Aggregate functions operate on sets of rows rather than individual rows.

Question 2. What is ALTER TABLE command? Write all the commands that can be applied using alter table.
Answer: The ALTER TABLE command modifies table structures. It can be used to: - Add a constraint
- Remove a constraint
- Add or remove a column from a table
- Modify a table column data type
In simple words: ALTER TABLE lets you update, add, or remove columns and constraints in existing tables.

Exam Tip: Memorize the syntax for adding columns, dropping columns, and modifying data types with ALTER TABLE.

Question 3. What is the Cartesian product of two tables? Is it same as an Equi-join?
Answer: Cartesian Product (or Cross Join) of two tables is a table obtained by pairing each row of one table with each row of the other table, containing all columns of both tables. No, it is not the same as an Equi-join. An Equi-join applies an equality condition on the Cartesian product to filter only matching rows based on common columns.
In simple words: Cross join pairs every row with every row, whereas equi-join filters them using matching key values.

Exam Tip: Cartesian product row count equals the product of rows in both tables (\(R_1 \times R_2\)).

LONG QUESTION-ANSWER

Question 1. Does Union display any duplicate rows?
Answer: Union does not display any duplicate rows unless ALL is specified with it.
In simple words: UNION removes duplicate rows automatically, whereas UNION ALL keeps them.

Exam Tip: Use UNION ALL when you need faster execution and want to retain duplicate records.

Question 2. Name the Aggregate Functions.
Answer: These functions are listed below:

In simple words: Aggregate functions summarize data columns into single values.

Exam Tip: Know the specific return types and constraints of each aggregate function for exams.

Question 3. What is Max Function? Give few Examples.
Answer: MAX() function is used to find the highest value of any column or any expression based on a column. MAX() takes one argument which can be any column name or a valid expression involving a column name.
Examples:
1. To find the highest cost of any type of shoe in the factory:
SELECT MAX(cost) FROM shoes;
2. To find the highest cost of any shoe of type 'School':
SELECT MAX(cost) FROM shoes WHERE type = 'School';
In simple words: MAX() finds the maximum value in a column or expression.

Exam Tip: MAX() works on numbers, strings, and dates.

Question 4. What is min() Function? Give Some Examples.
Answer: MIN() function is used to find the lowest value of any column or an expression based on a column. MIN() takes one argument which can be any column name or a valid expression involving a column name.
Examples:
1. To find the lowest cost of any type of shoe in the factory:
SELECT MIN(cost) FROM shoes;
2. To find the lowest cost of any shoe of type 'School':
SELECT MIN(cost) FROM shoes WHERE type = 'School';
In simple words: MIN() finds the minimum value in a column or expression.

Exam Tip: MIN() ignores NULL values just like all other aggregate functions.

Question 5. What is AVG() Function? Give Some Examples.
Answer: AVG() function is used to find the average value of any column or an expression based on a column. AVG() takes one argument which can be any column name or a valid expression involving a column name. Limitation: the argument of AVG() function can be of numeric (int/decimal) type only.
Examples:
1. To find the average margin from shoes table:
SELECT AVG(margin) FROM shoes;
2. To find the average cost from the shoes table:
SELECT AVG(cost) FROM shoes;
In simple words: AVG() calculates the mathematical mean of numeric columns.

Exam Tip: AVG() divides the sum of non-null values by the count of non-null values.

Question 6. What is Sum() Function? Give Some Examples.
Answer: SUM() function is used to find the total value of any column or an expression based on a column. SUM() takes one numeric argument only.
Examples:
1. To find the total quantity present in the stock:
SELECT SUM(Qty) FROM shoes;
2. To find the total order quantity:
SELECT SUM(order_qty) FROM orders;
In simple words: SUM() adds up all numeric values in a column.

Exam Tip: SUM() only works on numeric data types (INT, DECIMAL, FLOAT).

Question 7. What is COUNT() Function? Give Some Examples.
Answer: COUNT() function is used to count the number of values in a column or rows in a table. If the argument is a column name, it counts non-NULL values. If the argument is *, it counts all rows.
Examples:
1. To count the total number of records in the table Shoes:
SELECT COUNT(*) FROM shoes;
2. To count the different types of shoes that the factory produces:
SELECT COUNT(DISTINCT type) FROM shoes;
In simple words: COUNT() tallies rows or non-null column entries.

Exam Tip: Use COUNT(DISTINCT column) to count unique occurrences.

Question 8. What is 'GROUP BY'? Give Examples.
Answer: GROUP BY function is used for getting results based on grouped data.
Example:
To find the total quantity of shoes of various types:
SELECT type, SUM(qty) FROM shoes GROUP BY type;
In simple words: GROUP BY organizes rows into summary categories.

Exam Tip: Every column in the SELECT list must be either an aggregate function or included in the GROUP BY clause.

Question 9. What is Role of HAVING in SQL? Give Examples. How it is related with Group by?
Answer: The HAVING clause is used to filter groups formed by the GROUP BY clause based on specified conditions.
Example:
SELECT type, SUM(qty) FROM shoes GROUP BY type HAVING SUM(qty) > 1500;
In simple words: HAVING filters summarized groups just like WHERE filters individual rows.

Question 10. How will you Display Data from Multiple Tables?
Answer: Data from multiple tables is displayed using table joins or Cartesian products combined with matching conditions in the WHERE clause.
Example:
SELECT order_no, name, phone FROM orders, customers WHERE orders.cust_code = customers.cust_code;
In simple words: Joins link multiple tables together using matching common columns.

Exam Tip: Always qualify column names with table prefixes (e.g., table.column) if column names are identical across joined tables.

Question 11. What do you understand by Cartesian Product or Cross Join of tables? Give Example.
Answer: Cartesian product (also called Cross Join) of two tables is a table obtained by pairing each row of one table with every row of another table.
Example:
SELECT * FROM order_table, product;
In simple words: Cross join combines every row of table A with every row of table B.

Exam Tip: Avoid cross joins without WHERE conditions unless all-to-all pairing is explicitly intended.

Question 12. What is Equi-Join of tables? Show by examples.
Answer: An equi-join is formed by putting an equality condition on the Cartesian product of tables based on common matching columns.
Example:
SELECT * FROM order_table, product WHERE p_code = code;
In simple words: Equi-join retrieves matching records between tables using an equal sign.

Exam Tip: Equi-joins represent the most common type of inner join used in relational databases.

Question 13. Explain the Foreign Key.
Answer: A foreign key is a column in one table that references the primary key of another table, used to enforce referential integrity and establish table relationships.
In simple words: Foreign keys connect child tables to parent tables securely.

Exam Tip: Foreign keys ensure that data entered in child tables matches existing records in parent tables.

Question 14. What do you understand by Referential Integrity?
Answer: Referential integrity is a database property ensuring that no entry can be made in a foreign key column unless it matches a primary key value in the related parent table.
In simple words: Referential integrity prevents orphan records and invalid data relationships.

Exam Tip: Database management systems enforce referential integrity automatically when foreign key constraints are active.

Question 15. Describe Union operation by giving examples.
Answer: Union combines the result sets of two SELECT statements having identical columns and data types, removing duplicates by default.
Example:
SELECT cust_code FROM customer_cat_1 UNION SELECT cust_code FROM customer_cat_2;
In simple words: UNION merges matching query outputs into one clean list.

Exam Tip: Use UNION ALL when you want to keep duplicate rows in combined results.

Question 16. What are Constraints for a table? List all the constraints with their purpose. How these are applied?
Answer: Constraints are rules applied on table columns to ensure data integrity and validity. Key constraints include:
- PRIMARY KEY: Uniquely identifies each record, disallowing NULLs and duplicates.
- NOT NULL: Ensures columns cannot accept empty values.
- FOREIGN KEY: Ensures data matches primary key values in related tables.
- UNIQUE: Ensures all values in a column are distinct.
- CHECK / ENUM / SET: Restricts domain values.
In simple words: Constraints act as automated validation rules for database tables.

Exam Tip: Define primary keys and constraints during CREATE TABLE or via ALTER TABLE.

Question 17. What is PRIMARY KEY? Give Examples.
Answer: Primary key of a table is a column or group of columns that uniquely identifies each row in the table.
Example:
CREATE TABLE shoes (code CHAR(4) PRIMARY KEY, name VARCHAR(20));
In simple words: A primary key is a unique identifier for every row in a table.

Question 18. How 'DESC' is used for showing structure of the table?
Answer: The DESC command displays the schema, field names, data types, null permissions, key indicators, and default values of a table.
Example: DESC bills;
In simple words: DESC shows the structural blueprint and field properties of a table.

Exam Tip: Use DESC table_name; to quickly inspect table columns and constraints.

Question 19. How will you create a table in which NULL values should not be accepted?
Answer: We use the NOT NULL constraint on specific columns during table creation.
Example:
CREATE TABLE bills (order_num INT(4) NOT NULL, bill_amt DECIMAL(8,2) NOT NULL);
In simple words: Add NOT NULL to column definitions to prevent empty entries.

Exam Tip: Primary key columns are inherently NOT NULL by default.

Question 20. How can we view the Columns Associated with Constraints?
Answer: We can view constraints and column associations by executing the DESC command on the table.
Example: DESC shoes;
In simple words: The DESC command lists keys and null constraints alongside column definitions.

Exam Tip: Look at the 'Key' and 'Null' columns in DESC output to verify constraints.

Question 21. Show Add, Modify, and Remove constraints for altering a table.
Answer: Constraints can be managed post-creation using ALTER TABLE.
Examples:
- Add primary key: ALTER TABLE shoes ADD PRIMARY KEY(code);
- Drop primary key: ALTER TABLE shoes DROP PRIMARY KEY;
- Modify column constraint: ALTER TABLE bills MODIFY bill_date DATE NOT NULL;
In simple words: ALTER TABLE lets you add, remove, or modify table constraints.

Exam Tip: Ensure data has no duplicates before adding primary key or unique constraints via ALTER TABLE.

Question 22. What is DROPPING a TABLE?
Answer: Dropping a table removes both its structure and all stored data permanently from the database using the DROP TABLE command.
Example: DROP TABLE orders;
In simple words: DROP TABLE deletes the entire table and its contents permanently.

Exam Tip: DROP TABLE is irreversible, unlike DELETE which removes rows only.