Database develop. life cycle - writing SQL scripts

1. What is an SQL Script?

  • An SQL script is a file containing one or more SQL statements executed sequentially.

  • It is used to create, modify, or query databases.

  • Typically saved with .sql extension.

Uses:

  • Database creation

  • Table creation and modification

  • Data insertion, updating, deletion

  • Running queries and generating reports

2. Basic Structure of an SQL Script

a) Create Database

CREATE DATABASE UniversityDB;
USE UniversityDB;

b) Create Tables

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    DOB DATE,
    Email VARCHAR(50)
);

CREATE TABLE Course (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(50),
    Credits INT
);

CREATE TABLE Enrollment (
    StudentID INT,
    CourseID INT,
    Grade CHAR(1),
    PRIMARY KEY(StudentID, CourseID),
    FOREIGN KEY(StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY(CourseID) REFERENCES Course(CourseID)
);

c) Insert Data

INSERT INTO Student VALUES (101, 'Alice', '2002-05-10', '[email protected]');
INSERT INTO Student VALUES (102, 'Bob', '2001-08-22', '[email protected]');

INSERT INTO Course VALUES (1, 'DBMS', 3);
INSERT INTO Course VALUES (2, 'AI', 4);

INSERT INTO Enrollment VALUES (101, 1, 'A');
INSERT INTO Enrollment VALUES (102, 2, 'B');

d) Query Data

-- Simple SELECT
SELECT * FROM Student;

-- Join tables
SELECT s.Name, c.CourseName, e.Grade
FROM Enrollment e
JOIN Student s ON e.StudentID = s.StudentID
JOIN Course c ON e.CourseID = c.CourseID;

-- Conditional query
SELECT Name FROM Student WHERE DOB < '2002-01-01';

e) Update and Delete

-- Update grade
UPDATE Enrollment
SET Grade = 'A+'
WHERE StudentID = 102 AND CourseID = 2;

-- Delete a student
DELETE FROM Student WHERE StudentID = 102;

f) Comments in SQL Script

-- This is a single-line comment

/*
This is a
multi-line comment
*/

3. Tips for Writing SQL Scripts

  1. Use uppercase for SQL keywords (CREATE, INSERT, SELECT) for readability.

  2. Use meaningful table and column names.

  3. Comment your script for clarity.

  4. Test queries in small batches before running the full script.

  5. Ensure foreign key order: insert parent tables before child tables.

Summary

  • SQL scripts automate database creation, data manipulation, and queries.

  • Basic flow: Create Database → Create Tables → Insert Data → Queries/Updates/Deletes.

  • Scripts can be executed in a DBMS like MySQL, PostgreSQL, or SQL Server.