DBMS Coding Guide – Learn to Write All SQL Codes
We'll break this down into practical, hands-on sections with syntax, examples, and
explanations.
1. Creating a Database
sql
CopyEdit
CREATE DATABASE SchoolDB;
Explanation: Creates a new database called SchoolDB.
2. Selecting a Database
sql
CopyEdit
USE SchoolDB;
Explanation: Sets SchoolDB as the current working database.
3. Creating Tables
sql
CopyEdit
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT,
Gender CHAR(1)
);
Explanation: Creates a Students table with defined column types. StudentID is the primary key.
4. Inserting Records
sql
CopyEdit
INSERT INTO Students (StudentID, FirstName, LastName, Age, Gender)
VALUES (1, 'John', 'Doe', 20, 'M');
Explanation: Adds a student record to the table.
5. Retrieving Data (SELECT)
sql
CopyEdit
SELECT * FROM Students;
Explanation: Retrieves all records from the Students table.
sql
CopyEdit
SELECT FirstName, Age FROM Students WHERE Age > 18;
Explanation: Retrieves specific columns with a filter condition.
6. Updating Records
sql
CopyEdit
UPDATE Students
SET Age = 21
WHERE StudentID = 1;
Explanation: Updates the Age of the student with StudentID = 1.
7. Deleting Records
sql
CopyEdit
DELETE FROM Students
WHERE StudentID = 1;
Explanation: Deletes the student with StudentID = 1.
8. Adding New Columns
sql
CopyEdit
ALTER TABLE Students
ADD Email VARCHAR(100);
9. Modifying Column Data Types
sql
CopyEdit
ALTER TABLE Students
MODIFY Age SMALLINT;
10. Deleting Tables and Databases
sql
CopyEdit
DROP TABLE Students;
DROP DATABASE SchoolDB;
11. Joining Tables
Example: Students and Courses
sql
CopyEdit
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100)
);
CREATE TABLE Enrollments (
StudentID INT,
CourseID INT,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
sql
CopyEdit
SELECT s.FirstName, c.CourseName
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;
12. Aggregate Functions
sql
CopyEdit
SELECT COUNT(*) FROM Students;
SELECT AVG(Age) FROM Students;
SELECT MAX(Age) FROM Students;
13. Grouping Data
sql
CopyEdit
SELECT Gender, COUNT(*) AS Total
FROM Students
GROUP BY Gender;
14. Creating Views
sql
CopyEdit
CREATE VIEW AdultStudents AS
SELECT * FROM Students WHERE Age >= 18;
sql
CopyEdit
SELECT * FROM AdultStudents;
15. Stored Procedures
sql
CopyEdit
DELIMITER //
CREATE PROCEDURE GetAllStudents()
BEGIN
SELECT * FROM Students;
END;
//
DELIMITER ;
sql
CopyEdit
CALL GetAllStudents();
16. Triggers
sql
CopyEdit
CREATE TRIGGER BeforeInsertStudents
BEFORE INSERT ON Students
FOR EACH ROW
SET NEW.Age = IF(NEW.Age < 0, 0, NEW.Age);
17. Creating Indexes
sql
CopyEdit
CREATE INDEX idx_lastname
ON Students (LastName);
18. Granting and Revoking Permissions
sql
CopyEdit
GRANT SELECT, INSERT ON SchoolDB.Students TO 'user1'@'localhost';
REVOKE INSERT ON SchoolDB.Students FROM 'user1'@'localhost';
19. Backup & Restore (MySQL Example)
Backup:
bash
CopyEdit
mysqldump -u root -p SchoolDB > school_backup.sql
Restore:
bash
CopyEdit
mysql -u root -p SchoolDB < school_backup.sql
