SQL Notes
Step-by-step guide to creating your first SQL database, adding tables, inserting data, and running your first queries with practical examples.
You have installed MySQL (or PostgreSQL or SQL Server). The software is running. Now what? It is time to create your very first database, build some tables, fill them with data, and run your first real SQL queries. By the end of this lesson, you will have a working database that you can experiment with throughout this course.
Step 1: Create the Database
Open your MySQL Workbench (or terminal) and run:
CREATE DATABASE college_db;That is it. You now have an empty database named college_db. Let us verify it exists:
SHOW DATABASES;You should see college_db in the list alongside system databases like mysql, information_schema, and performance_schema.
Now tell MySQL to use this database for all subsequent commands:
USE college_db;Step 2: Design Your Tables
Before writing SQL, think about what data you want to store. Let us build a simple college management system with three tables:
- Students — information about enrolled students
- Courses — available courses
- Enrollments — which student is in which course
This represents a classic many-to-many relationship: one student takes many courses, and one course has many students.
Step 3: Create the Students Table
CREATE TABLE Students (
StudentID INT PRIMARY KEY AUTO_INCREMENT,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100) NOT NULL UNIQUE,
DateOfBirth DATE,
City VARCHAR(50) DEFAULT 'Unknown',
EnrollmentDate DATE DEFAULT (CURRENT_DATE)
);Let us break down what each line does:
StudentID INT PRIMARY KEY AUTO_INCREMENT— unique identifier, auto-generatedFirstName VARCHAR(50) NOT NULL— required text field, up to 50 charactersEmail VARCHAR(100) NOT NULL UNIQUE— required and must be different for each studentDateOfBirth DATE— optional date fieldCity VARCHAR(50) DEFAULT 'Unknown'— if not provided, defaults to "Unknown"EnrollmentDate DATE DEFAULT (CURRENT_DATE)— defaults to today
Step 4: Create the Courses Table
CREATE TABLE Courses (
CourseID INT PRIMARY KEY AUTO_INCREMENT,
CourseName VARCHAR(100) NOT NULL,
CourseCode VARCHAR(10) NOT NULL UNIQUE,
Credits INT NOT NULL DEFAULT 3,
Department VARCHAR(50) NOT NULL,
MaxSeats INT DEFAULT 60
);Step 5: Create the Enrollments Table
This junction table connects students to courses:
Junction tables (also called bridge tables or associative tables) are how relational databases handle many-to-many relationships. One student enrolls in many courses, and one course has many students. Without a junction table, you would need to repeat student information for each course or course information for each student, creating massive redundancy. The junction table elegantly solves this by storing only the relationship — which student is connected to which course — along with any relationship-specific data like the enrollment date and grade.
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY AUTO_INCREMENT,
StudentID INT NOT NULL,
CourseID INT NOT NULL,
EnrollDate DATE DEFAULT (CURRENT_DATE),
Grade VARCHAR(5),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID),
UNIQUE (StudentID, CourseID)
);The FOREIGN KEY constraints ensure that every enrollment references a valid student and course. The UNIQUE constraint on (StudentID, CourseID) prevents a student from enrolling in the same course twice.
Step 6: Verify Your Tables
SHOW TABLES;Output:
Check a table's structure:
DESCRIBE Students;Step 7: Insert Data into Students
INSERT INTO Students (FirstName, LastName, Email, DateOfBirth, City)
VALUES
('Rahul', 'Sharma', 'rahul.sharma@email.com', '2004-03-15', 'Mumbai'),
('Priya', 'Patel', 'priya.patel@email.com', '2004-07-22', 'Delhi'),
('Amit', 'Kumar', 'amit.kumar@email.com', '2003-11-08', 'Bangalore'),
('Sneha', 'Verma', 'sneha.verma@email.com', '2004-01-30', 'Pune'),
('Vikas', 'Gupta', 'vikas.gupta@email.com', '2003-09-14', 'Chennai');Notice we did not specify StudentID or EnrollmentDate — AUTO_INCREMENT and DEFAULT handle those automatically.
Step 8: Insert Data into Courses
INSERT INTO Courses (CourseName, CourseCode, Credits, Department, MaxSeats)
VALUES
('Database Management Systems', 'CS201', 4, 'Computer Science', 60),
('Data Structures', 'CS101', 4, 'Computer Science', 60),
('Operating Systems', 'CS301', 3, 'Computer Science', 45),
('Calculus', 'MA101', 3, 'Mathematics', 80),
('Digital Electronics', 'EC201', 3, 'Electronics', 50);Step 9: Insert Data into Enrollments
INSERT INTO Enrollments (StudentID, CourseID, Grade)
VALUES
(1, 1, 'A'),
(1, 2, 'B+'),
(1, 4, 'A-'),
(2, 1, 'A+'),
(2, 3, 'B'),
(3, 2, 'A'),
(3, 5, 'B+'),
(4, 1, 'B'),
(4, 2, 'A-'),
(5, 3, 'A'),
(5, 4, 'B+');Step 10: Run Your First Queries
Now the fun part — querying your data:
See all students
SELECT * FROM Students;Find students from Mumbai
SELECT FirstName, LastName, Email
FROM Students
WHERE City = 'Mumbai';See all courses with 4 credits
SELECT CourseName, CourseCode, Department
FROM Courses
WHERE Credits = 4;Join tables to see who is enrolled in what
SELECT
s.FirstName,
s.LastName,
c.CourseName,
e.Grade
FROM Enrollments e
JOIN Students s ON e.StudentID = s.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
ORDER BY s.LastName, c.CourseName;Count students per course
SELECT
c.CourseName,
COUNT(e.StudentID) AS StudentCount
FROM Courses c
LEFT JOIN Enrollments e ON c.CourseID = e.CourseID
GROUP BY c.CourseName
ORDER BY StudentCount DESC;Find students with Grade A or above
SELECT s.FirstName, s.LastName, c.CourseName, e.Grade
FROM Enrollments e
JOIN Students s ON e.StudentID = s.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
WHERE e.Grade IN ('A', 'A+', 'A-')
ORDER BY s.FirstName;Modifying Data
Update a student's city
UPDATE Students SET City = 'Hyderabad' WHERE StudentID = 3;Update a grade
UPDATE Enrollments SET Grade = 'A' WHERE StudentID = 4 AND CourseID = 1;Delete an enrollment
DELETE FROM Enrollments WHERE StudentID = 5 AND CourseID = 4;Dropping the Database (Careful!)
If you ever want to start over completely:
DROP DATABASE college_db;This permanently deletes the database and all its data. Use with extreme caution.
What You Have Accomplished
You now have a working database with:
- Three properly designed tables with constraints
- Relationships between tables using foreign keys
- Real data you can query and manipulate
- Experience with CREATE, INSERT, SELECT, UPDATE, DELETE, and JOIN
Best Practices for Your First Database
- Always use meaningful names —
Studentsis better thantbl1 - Add PRIMARY KEY to every table — essential for identification
- Use appropriate data types — DATE for dates, INT for numbers, VARCHAR for text
- Add constraints early — NOT NULL, UNIQUE, FOREIGN KEY prevent bad data
- Insert test data immediately — you cannot practice queries on empty tables
- Back up before dropping — DROP DATABASE is irreversible
Congratulations! You have created your first database from scratch. Keep this college_db as your practice playground throughout the rest of this course. Every SQL concept you learn — JOINs, subqueries, aggregations, indexes — you can practice right here.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Create Your First Database.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, setup, create, first
Related SQL Complete Guide Topics