SQL Summary Notes and Key Concepts
Uploaded by
keerthanadevig27SQL Summary Notes and Key Concepts
Uploaded by
keerthanadevig27Common questions
Powered by AIThe main categories of SQL commands are: DDL (Data Definition Language) for defining database structure using commands like CREATE, ALTER, DROP, and TRUNCATE; DML (Data Manipulation Language) for manipulating data using SELECT, INSERT, UPDATE, and DELETE; TCL (Transaction Control Language) for controlling transactions using COMMIT, ROLLBACK, and SAVEPOINT; and DCL (Data Control Language) for controlling permissions using GRANT and REVOKE .
A primary key constraint ensures that each value in a column is both unique and not null, providing a unique identifier for each row in a table. A foreign key constraint, on the other hand, links two tables together and enforces referential integrity by ensuring that the value in a foreign key column corresponds to a primary key value in another table .
Transaction control commands like COMMIT, ROLLBACK, and SAVEPOINT are used to manage changes in a database transaction. COMMIT saves changes permanently, ensuring data is intact and consistent. ROLLBACK undoes changes to provide a fail-safe mechanism against errors, thus maintaining data integrity. SAVEPOINT allows partial rollbacks by marking specific points within a transaction. These commands collectively ensure that database transactions are completed accurately and that the database remains in a consistent state .
A view in SQL is a virtual table based on the result of a SELECT query. It simplifies queries by allowing users to select from the view instead of writing complex queries repeatedly. Additionally, views can enhance security by restricting access to sensitive columns; users can be granted access to views with limited columns instead of the full table .
A correlated subquery relies on each row that it evaluates from the outer query to complete its execution, running for each row processed by the outer query. Non-correlated subqueries operate independently of the outer query, running once and applying the same result to each row. A correlated subquery is useful in scenarios like comparing an employee’s salary to the average salary within the same department, where the subquery must access data from each row of the outer query .
Normalization organizes data to reduce redundancy and improve data integrity by ensuring that each attribute in a table is functionally dependent on the primary key. However, highly normalized databases can suffer from performance issues in complex queries. Denormalization, on the other hand, involves combining tables to reduce the complexity of queries and improve read performance, but it can introduce redundancy and potential data anomalies .
SQL indexes improve the speed of data retrieval operations. The PRIMARY index enforces uniqueness and is used for primary key columns. UNIQUE indexes ensure no duplicate values in columns. SIMPLE indexes are used for single columns, while COMPOSITE indexes cover multiple columns and are useful in complex multi-column queries. FULLTEXT indexes are for searching text-based columns. Index choice depends on database design, query patterns, and specific performance optimization needs .
DELETE removes rows from a table based on a condition specified by the WHERE clause, but the table structure and its indexes remain. TRUNCATE removes all rows from a table without using a WHERE clause but retains the table structure for future use. DROP permanently removes a table and its structure, including all associated data and indexes .
INNER JOIN returns only the rows with matching values in both tables. LEFT JOIN returns all rows from the left table and matched rows from the right table, and fills NULLs for unmatched rows from the right. RIGHT JOIN behaves similarly, returning all rows from the right table. FULL JOIN combines the results of both LEFT and RIGHT JOIN, returning all rows from both tables with NULLs filled in where there is no match. CROSS JOIN returns the Cartesian product of the two tables .
SQL constraints are rules applied to columns to ensure data integrity and accuracy. A PRIMARY KEY constraint enforces unique rows and non-null values. FOREIGN KEY constraints maintain referential integrity between tables. UNIQUE constraints prevent duplicate values. NOT NULL constraints require entries in a column. CHECK constraints enforce specific criteria within columns. DEFAULT constraints set default values. These constraints collectively ensure that data is entered correctly and remains consistent .