SQL Data Manipulation and Table Design Tutorial

This tutorial introduces the basic SQL commands used for managing data within a database: INSERT, UPDATE, DELETE, along with table design using CREATE TABLE. These are essential for creating, populating, and managing your database schema.

1. Designing Tables with CREATE TABLE

Before inserting or updating data, you need to define the structure of your database tables. Here are example table designs for a simple student enrollment system:

A. Students Table


CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100),
  age INT,
  country VARCHAR(50)
);

This table stores student information with a unique student_id.

B. Courses Table


CREATE TABLE courses (
  course_code VARCHAR(10) PRIMARY KEY,
  course_name VARCHAR(100),
  credits INT
);

This table contains course details identified by a unique course_code.

C. Enrollments Table


CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
  student_id INT,
  course_code VARCHAR(10),
  enrollment_date DATE,
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_code) REFERENCES courses(course_code)
);

This table records each enrollment, linking students and courses with foreign keys.

2. Data Manipulation Commands

Once tables are created, you can add, modify, or delete data using SQL commands.

3. INSERT Statement

The INSERT statement adds new data to your tables.


INSERT INTO students (student_id, name, email, age, country)
VALUES (1, 'Rana', 'rana@example.com', 22, 'Bangladesh');

4. UPDATE Statement

The UPDATE statement modifies existing data in the table.


UPDATE students
SET email = 'rana.newemail@example.com'
WHERE student_id = 1;

5. DELETE Statement

The DELETE statement removes data from the table.


DELETE FROM students
WHERE student_id = 1;

Summary Table of Basic SQL Commands

Statement	Purpose	Sample Use Case
CREATE TABLE	Design the database structure	Create students, courses, enrollments tables
INSERT	Add new data to tables	Add a new student or course
UPDATE	Modify existing records	Update student email
DELETE	Remove records	Delete a student record

Practice creating your own tables and manipulating data to build a functional database!

Trust Coding : Programming

🗃️ Basic SQL Table Design & Data Management Tutorial