CBSE Grade 12 SQL Theory & Questions
Uploaded by
hostelid3CBSE Grade 12 SQL Theory & Questions
Uploaded by
hostelid3Common questions
Powered by AINormalization is the process of organizing data to minimize redundancy and dependency by dividing a database into two or more tables and defining relationships between them . Achieving 2NF is crucial as it ensures that all non-key attributes are fully dependent on the primary key, thus eliminating partial dependencies that can lead to anomalies in data insertion, deletion, and updating. In a practical context, 2NF eliminates subsets of data that apply to multiple rows and places them in separate tables to maintain consistency .
The implementation of a FOREIGN KEY constraint ensures referential integrity by preventing changes in a related table that would produce invalid links, thus maintaining database consistency . A major benefit is the automatic enforcement of relational links that prevent orphaned records. However, risks include increased complexity in schema design and potential performance bottlenecks, as every insert, update, or delete must be verified against the foreign key constraint, which could affect performance in high-transaction environments. Additionally, mishandling of foreign keys can lead to circular dependencies during the design phase .
CHAR is a fixed-length data type that always stores strings of a specified length, padding with spaces if necessary, while VARCHAR is a variable-length data type that stores strings as entered, using only the required space . This means CHAR can be less efficient in terms of disk space, especially when storing a large number of short strings. However, it can offer performance benefits in terms of data retrieval speed because the fixed size can simplify memory access .
CHECK constraints validate data against specified conditions before insertion or update, ensuring that only data meeting certain criteria can be entered into a table, thus enhancing data integrity by systematically avoiding unwanted entries . DEFAULT constraints automatically populate a column with a predetermined value if no explicit data is provided, increasing usability by simplifying data entry processes and ensuring that critical fields are not left blank, which aids in maintaining consistency across records .
A DBMS ensures data integrity and consistency by implementing constraints such as primary keys, foreign keys, and unique constraints, which enforce rules on data storage and relationships . Unlike traditional file systems, which rely on manual processes, a DBMS uses transaction management to ensure atomicity, consistency, isolation, and durability (ACID properties), preventing inconsistencies during concurrent data operations .
The DELETE command is used when there is a need to remove specific rows from a table without affecting the table's structure, which is essential when only a subset of data needs to be purged, and the table will continue to be used . In contrast, the DROP command should be used when the complete removal of a table, including its structure and all of its records, is necessary. This is suitable for permanently retiring unused tables, freeing up database space, and simplifying schema management. Choosing DELETE avoids the loss of table structure and permissions, which could be beneficial when anticipating future use of the corresponding data schema .
Aggregate functions such as SUM() and AVG() process multiple rows of data and return a single summarized result . These functions, except for COUNT(*), ignore NULL values during calculations, meaning they only operate on rows where the specified column is non-null. For example, SUM() will add up all non-null values in a column, and AVG() will compute the average by dividing the sum by the count of non-null entries, ensuring more accurate results in the presence of missing data .
LEFT JOIN returns all records from the left table and the matched records from the right table, filling non-matching rows with NULLs. This is advantageous when it is necessary to include all data from the left table irrespective of matches, such as when performing audits or generating comprehensive reports . Conversely, INNER JOIN only returns records with matching values in both tables, optimizing performance as fewer rows are processed, which can be beneficial in performance-critical applications. However, LEFT JOIN might lead to increased processing time and memory use with larger datasets due to the inclusion of potentially entire tables .
Primary keys uniquely identify each record in a table, ensuring that each entry can be efficiently referenced and accessed. Foreign keys, on the other hand, reference primary keys in another table to establish a relational link between the data entities, thereby maintaining referential integrity . A foreign key cannot contain null values when it is used to establish a mandatory relationship because a null in the foreign key would indicate a lack of association with a primary key, thus breaking the linkage and potentially leading to orphaned records and compromised data integrity .
Practicing query writing without computer assistance is crucial because it forces students to internalize SQL syntax and logic, enhancing their problem-solving skills without relying on automated feedback from software tools . This practice helps in developing a deeper understanding of query structures and reduces dependence on syntax highlighting and autocompletion, which can lead to over-reliance on tools rather than a comprehensive understanding of the syntax. During exams, students cannot use computers for verification, so this practice prepares them to write accurate queries in a test environment .