Trainees Database Concepts.pdf grwrwewwe

aa

1. Database Concepts
1.Database Design Concepts
• Database Models: Relational, Hierarchical, Network, Object-oriented
• Tables: Structure of data storage, rows (records), and columns (fields)
• Primary Key: Uniquely identifies each record in a table
• Foreign Key: Ensures referential integrity by linking to a primary key in another
table
• Normalization: Process of organizing data to avoid redundancy (1NF, 2NF, 3NF)
• Denormalization: Combining tables for performance reasons (usually for read-heavy
applications)
• Indexes: Speed up data retrieval operations
2. SQL Command Categories
• DDL (Data Definition Language):
o CREATE, ALTER, DROP, TRUNCATE, RENAME
• DML (Data Manipulation Language):
o INSERT, UPDATE, DELETE, MERGE
• DRL (Data Retrieval Language):
o SELECT, JOIN, GROUP BY, ORDER BY, HAVING
• DCL (Data Control Language):
o GRANT, REVOKE
• TCL (Transaction Control Language):
o COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION
3. SQL Clauses & Operations
• WHERE: Filter rows based on a condition
• AND, OR, NOT: Combine or negate conditions
• IN, BETWEEN, LIKE, IS NULL: Specialized operators for filtering data
• JOIN: Combine rows from two or more tables based on related columns
o INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN
• GROUP BY: Group rows sharing a property and perform aggregate functions
• HAVING: Filter the results after grouping (works with aggregate functions)
• ORDER BY: Sort results by one or more columns (ascending/descending)
• DISTINCT: Remove duplicate rows from the result set
4. Aggregate Functions
• COUNT(): Returns the number of rows in a group
• SUM(): Returns the sum of a numeric column
• AVG(): Returns the average value of a numeric column
• MIN(): Returns the minimum value in a group
• MAX(): Returns the maximum value in a group
5. Subqueries

2. • Subquery in SELECT: Nested queries to retrieve specific columns or perform
calculations
• Subquery in WHERE: Filter results using a nested query
• Subquery in FROM: Treat a subquery as a virtual table
• Correlated Subquery: A subquery that depends on the outer query for its values
6. Data Integrity
• Constraints:
o NOT NULL: Ensures a column cannot have NULL values
o UNIQUE: Ensures all values in a column are unique
o CHECK: Validates data based on a condition
o DEFAULT: Sets a default value for a column
o FOREIGN KEY: Establishes a relationship between two tables
o PRIMARY KEY: Uniquely identifies a record in a table
• Triggers: Automatically execute a predefined action in response to changes in a table
(e.g., AFTER INSERT, BEFORE UPDATE)
• Transactions: A series of operations executed as a single unit, ensuring consistency,
isolation, and durability
o COMMIT, ROLLBACK
7. Indexes & Performance Optimization
• Indexes: Speed up data retrieval by creating a structure for fast search
• Unique Index: Ensures no duplicate values in a column
• Composite Index: An index on multiple columns
• Full-text Index: Index for fast searching in large text fields
• Query Optimization: Writing efficient SQL queries to improve performance
o EXPLAIN: Used to analyze and improve query performance
8. Views & Stored Procedures
• Views: Virtual tables created by a query that can simplify complex queries or provide
security
• Stored Procedures: Precompiled SQL code that can be executed on demand
• Functions: Similar to stored procedures but return a value
• Triggers: Automatically execute in response to changes in the database
9. SQL Joins
• INNER JOIN: Returns records with matching values in both tables
• LEFT JOIN: Returns all records from the left table and matching records from the
right table
• RIGHT JOIN: Returns all records from the right table and matching records from the
left table
• FULL OUTER JOIN: Returns all records when there is a match in either left or right
table
• SELF JOIN: Joining a table with itself

3. 10. Database Security
• User Authentication: Ensuring only authorized users can access the database
• Role-based Access Control (RBAC): Assigning privileges based on roles to limit
access
• SQL Injection Prevention: Writing secure SQL to prevent malicious attacks
• Encryption: Encrypting sensitive data for security
11. Data Types
• Numeric Types: INT, FLOAT, DECIMAL
• String Types: CHAR, VARCHAR, TEXT
• Date and Time Types: DATE, TIME, DATETIME, TIMESTAMP
• Boolean Type: BOOLEAN
• Binary Types: BLOB, BINARY
12. Normalization & Denormalization
• Normalization: Reducing data redundancy and dependency (1NF, 2NF, 3NF, BCNF)
• Denormalization: Combining tables for better read performance (though it introduces
redundancy)