In a Database Management System (DBMS), several essential concepts ensure efficient data storage, manipulation, and retrieval. Creating, altering, and deleting tables define and manage the database structure. Data types specify the kind of values stored in each column, while constraints maintain data integrity. Aggregate functions help summarize data, and joins connect multiple tables to extract related information. Sub-queries allow nesting of one query inside another for complex retrievals, and views provide virtual tables for simplified and secure data access. Together, these components form the foundation of relational databases, enabling accurate, flexible, and organized data management.
1. Creating Tables
The CREATE TABLE statement is used to define a new table in a database. It specifies the table name, column names, data types, and constraints.
Example:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Course VARCHAR(30)
);
This command establishes the table structure that will store data in rows and columns. Each column has a specific data type defining the kind of values it can hold. The CREATE TABLE command is essential for setting up a database schema before inserting or manipulating data.
2. Altering Tables
The ALTER TABLE command allows modification of an existing table’s structure without deleting stored data. It is used to add, delete, rename, or modify columns and constraints.
Examples:
ALTER TABLE Students ADD Email VARCHAR(50);
ALTER TABLE Students DROP COLUMN Course;
ALTER TABLE Students RENAME TO Learners;
This flexibility ensures that database design can evolve as requirements change. Using ALTER TABLE helps maintain and expand the structure of the database without data loss, ensuring the system remains adaptable to future needs.
3. Deleting Tables
The DROP TABLE command is used to permanently delete a table and all its data from the database.
Example:
DROP TABLE Students;
Once executed, this action removes the table structure, data, and related constraints. The DROP TABLE command should be used carefully because the deleted data cannot be recovered unless a backup exists. It is typically used during database maintenance, restructuring, or when a table becomes obsolete. This command ensures that unused or redundant tables do not occupy storage space unnecessarily, keeping the database organized and efficient.
4. Data Types
Data types define the kind of data that can be stored in each column of a table. They ensure accuracy, consistency, and optimized storage. Common SQL data types include:
-
INT: Whole numbers
-
FLOAT/DECIMAL: Decimal numbers
-
CHAR/VARCHAR: Character strings
-
DATE/TIME: Date and time values
-
BOOLEAN: True or False values
Example:
Name VARCHAR(50), Age INT, Salary DECIMAL(10,2);
Choosing the correct data type improves database performance and prevents invalid data entry. Each database system (like MySQL, Oracle, or SQL Server) may have slight variations in supported data types.
5. Constraints
Constraints are rules applied to table columns to maintain data accuracy and integrity. Common types include:
-
PRIMARY KEY: Uniquely identifies each record.
-
FOREIGN KEY: Ensures valid relationships between tables.
-
UNIQUE: Prevents duplicate values.
-
NOT NULL: Ensures a column cannot have NULL values.
-
CHECK: Validates specific conditions.
Example:
CREATE TABLE Employee (
ID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT CHECK (Age >= 18)
);
Constraints ensure reliable, valid, and consistent data, reducing redundancy and improving overall data quality within a database.
6. Aggregate Functions
Aggregate functions perform calculations on a set of values and return a single result. They are commonly used in reports and summaries.
Key aggregate functions include:
-
COUNT(): Counts rows
-
SUM(): Adds numeric values
-
AVG(): Finds average
-
MAX(): Finds the highest value
-
MIN(): Finds the lowest value
Example:
SELECT AVG(Salary), MAX(Salary), MIN(Salary) FROM Employees;
These functions are often used with the GROUP BY clause to group results by a specific column. Aggregate functions simplify analysis, enabling users to draw insights from large datasets efficiently.
7. Joins
Joins combine data from two or more tables based on related columns. They are essential for retrieving related data stored in multiple tables.
Types of joins are:
-
INNER JOIN: Returns matching rows.
-
LEFT JOIN: Returns all rows from the left table and matched rows from the right.
-
RIGHT JOIN: Opposite of LEFT JOIN.
-
FULL JOIN: Returns all rows when a match exists in either table.
Example:
SELECT Students.Name, Courses.CourseName
FROM Students
INNER JOIN Courses ON Students.CourseID = Courses.CourseID;
Joins enable efficient data retrieval from normalized relational structures.
8. Sub-Queries
A Sub-query (or nested query) is a query within another query used to fetch results dynamically. It can appear in SELECT, INSERT, UPDATE, or DELETE statements.
Example:
SELECT Name FROM Students
WHERE Age > (SELECT AVG(Age) FROM Students);
Here, the sub-query calculates the average age, and the main query retrieves students older than that average. Sub-queries can be single-row, multiple-row, or correlated. They help simplify complex operations by breaking them into smaller, manageable parts, improving flexibility and accuracy in data retrieval.
9. Views
A View is a virtual table created from one or more tables using a query. It doesn’t store data physically but displays results dynamically when accessed.
Example:
CREATE VIEW ActiveStudents AS
SELECT Name, Course FROM Students WHERE Status = ‘Active’;
Views enhance data security, simplify complex queries, and present data in a user-friendly way. They also restrict access to sensitive columns while allowing users to see relevant information. Since views are based on SQL queries, they update automatically when the underlying tables change, ensuring data consistency and accuracy.