Understanding Database Commands

How You Access and Interact with a Database

Modes of Accessing a Database

Method	Description	Use Cases
Direct Access	Users interact directly with the database using command-line tools or database management interfaces.	– Database administrators managing users, backups, and configurations. – Running SQL queries for direct database manipulation.
Query Builder	A graphical interface that allows users to create queries without writing SQL manually.	– Business analysts creating reports without SQL expertise. – Rapid query generation for non-technical users.
User Interface Access	Applications provide a graphical UI to interact with the database indirectly.	– Web applications using forms for user input. – Desktop applications like Microsoft Access managing small databases.
Programmatic Access	Applications access the database using programming languages via APIs or ORM frameworks.	– Web applications using Python, Java, or C# to interact with databases. – Backend services retrieving data for mobile applications.

Database Languages and Their Roles

Language	Description	Use Cases
Data Definition Language (DDL)	Used to define and modify database structures (tables, indexes, schemas).	– Creating a database and defining table structures. – Altering table schemas.
Data Manipulation Language (DML)	Used to insert, update, delete, and retrieve data.	– Adding new records. – Updating and deleting records in tables.
Data Control Language (DCL)	Controls access and permissions on the database.	– Granting or revoking user permissions. – Managing database security policies.
Transaction Control Language (TCL)	Manages transactions to ensure data consistency.	– Committing or rolling back transactions. – Maintaining ACID properties of the database.

Summary of Database Commands

1. Creating and Managing Databases

Command	Description	Example
CREATE DATABASE	Creates a new database.	CREATE DATABASE school;
DROP DATABASE	Deletes an entire database.	DROP DATABASE school;

2. Creating and Managing Tables

Command	Description	Example
CREATE TABLE	Creates a new table with specified columns.	CREATE TABLE students (id INT, name VARCHAR(50), age INT);
ALTER TABLE	Modifies an existing table (e.g., add, remove, modify columns).	ALTER TABLE students ADD email VARCHAR(100);
DROP TABLE	Deletes a table from the database.	DROP TABLE students;

3. Working with Records (DML)

Command	Description	Example
INSERT INTO	Adds a new record to a table.	INSERT INTO students (id, name, age) VALUES (1, ‘John’, 22);
UPDATE	Modifies existing records in a table.	UPDATE students SET age = 23 WHERE id = 1;
DELETE	Removes records from a table.	DELETE FROM students WHERE id = 1;

4. Querying Data (DML)

Command	Description	Example
SELECT	Retrieves data from tables.	SELECT * FROM students;
WHERE	Filters results based on conditions.	SELECT * FROM students WHERE age > 20;
ORDER BY	Sorts query results in ascending or descending order.	SELECT * FROM students ORDER BY name ASC;
GROUP BY	Groups rows with the same values in specified columns.	SELECT age, COUNT(*) FROM students GROUP BY age;
JOIN (INNER, OUTER)	Combines data from multiple tables.	SELECT students.name, courses.course_name FROM students INNER JOIN enrollments ON students.id = enrollments.student_id;

5. Managing Views and Indexes

Command	Description	Example
CREATE VIEW	Creates a virtual table based on a query.	CREATE VIEW student_names AS SELECT name FROM students;
CREATE INDEX	Improves query performance on specific columns.	CREATE INDEX idx_students_name ON students(name);

6. Backup, Security, and Permissions

Command	Description	Example
BACKUP DATABASE	Creates a backup of the database.	BACKUP DATABASE school TO DISK = ‘C:\backup.bak’;
RESTORE DATABASE	Restores a database from a backup file.	RESTORE DATABASE school FROM DISK = ‘C:\backup.bak’;
GRANT	Assigns specific permissions to users.	GRANT SELECT, INSERT ON students TO user1;
REVOKE	Removes permissions from users.	REVOKE INSERT ON students FROM user1;

Summary

Databases can be accessed directly via command-line tools, query builders, or user interfaces, and programmatically via applications. They use structured commands to define, manipulate, and control data. SQL commands are grouped into categories like DDL (defining structures), DML (managing data), DCL (security controls), and TCL (transaction management). Efficient database management ensures data integrity, security, and performance optimization.

Previous Topic

Back to Lesson

Next Topic