More on SQL- Grouping Records and Table Joins
Very Short Question Answer
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 calculate so sum or avg functions are not valid for them.
2. What is default, Autocommit mode in MySQL ?
Answer : By default, Autocommit mode is on in MySQL.
3. Can where be added a savepoint in a transaction ?
Answer : We can add a savepoint anywhere in a transaction.
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.
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.
6. What is the purpose of GROUP BY clause?
Answer : GROUP BY: 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.
7. What is the difference between HAVING and WHERE clauses? Explain with the help of an example.
Answer : WHERE Vs HAVING: WHERE is used to put a condition on individual row of a table whereas HAVING is used to put condition on individual group formed by GROUP BY clause in a SELECT statement.
8. What is a Foreign key? What is its importance?
Answer : Foreign Key: It 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.
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 very important role for tables so are not hindrances.
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)?
Ansewr : Not NULL or Primary key constraints used.
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)?
12. Do Primary Key column(s) of a table accept NULL values?
13. There is a table T1 with combination of columns C1, C2, and C3 as its primary key? Is it possible to enter:
a. NULL values in any of these columns?
b. Duplicate values in any of these columns?
Answer : No.
14. What are the differences between DELETE and DROP commands of SQL?
Answer : Delete is used for row removing while drop is used for removing complete table.
15. What are Aggregate Functions ?
Answer : A multiple row function works on multiple values. These functions are called aggregate functions or group functions.
Q.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).
Q.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.
Please refer to attached file for CBSE Class 12 Informatics Practices More on SQL Grouping Records and Table Joins Assignment
Click for more Informatics Practices Study Material ›