Handle relational databases using standard Structured Query Language (SQL)

 

1.1 Selected DBMS Installed and Setup

 What is a DBMS?

A Database Management System (DBMS) is software used to store, manage, and retrieve data from a database.
Common DBMS examples:

• MySQL (Open source)
• Microsoft SQL Server
• Oracle DB
• PostgreSQL

Steps to Install a DBMS (e.g., MySQL)

1. Download Installer
   Go to the official MySQL website and download the installer.
2. Run Installer
   Follow the wizard to install the MySQL server.
3. Set Root Password
   During installation, you will set the admin (root) password.
4. Start the DBMS Service
   Use MySQL Workbench or Command Line Client to connect.
5. Verify Installation
   Run a simple SQL query like:

SELECT VERSION();

 

1.2 User Logged into DBMS as per SOP

 What is SOP?

SOP = Standard Operating Procedure.
It is the step-by-step instruction followed to log in securely.

2.1 Database Created as Required

To create a new database:

CREATE DATABASE school_db;

To use the database:

USE school_db;

 

2.2 Tables Created with Proper Columns, Data Types, and Keys

CREATE TABLE students (

    student_id INT PRIMARY KEY,

    name VARCHAR(100),

    dob DATE,

    grade INT

);

• INT – for numbers
• VARCHAR(100) – text up to 100 characters
• DATE – for dates
• PRIMARY KEY – unique identifier

 

2.3 Columns Inserted, Deleted, and Altered as Required

 Add a New Column:

ALTER TABLE students ADD email VARCHAR(100);

 Delete a Column:

ALTER TABLE students DROP COLUMN email;

 Change Column Type or Name:

ALTER TABLE students MODIFY grade VARCHAR(10);

 

2.4 Foreign Keys Defined When Creating Tables

Example:

CREATE TABLE classes (

    class_id INT PRIMARY KEY,

    class_name VARCHAR(50)

);

 

CREATE TABLE students (

    student_id INT PRIMARY KEY,

    name VARCHAR(100),

    class_id INT,

    FOREIGN KEY (class_id) REFERENCES classes(class_id)

);

 

2.5 Add / Modify / Delete Primary & Foreign Keys

 Add Primary Key:

ALTER TABLE students ADD PRIMARY KEY (student_id);

Add Foreign Key:

ALTER TABLE students

ADD CONSTRAINT fk_class FOREIGN KEY (class_id)

REFERENCES classes(class_id);

Drop Foreign Key:

ALTER TABLE students DROP FOREIGN KEY fk_class;

 

2.6 Table Structures Imported / Exported

Export Table Structure (MySQL):

mysqldump -u root -p school_db students > students.sql

Import Table:

mysql -u root -p school_db < students.sql

Can also be done in phpMyAdmin or Workbench.

 

2.7 Backup / Restore Database

 Backup Database:

mysqldump -u root -p school_db > school_db_backup.sql

 Restore Database:

mysql -u root -p school_db < school_db_backup.sql

 

2.8 Delete Databases / Tables as per SOP

Delete a Table:

DROP TABLE students;

Delete a Database:

DROP DATABASE school_db;

3.1 Records Inserted to the Table(s) as Required

This step involves adding new data (rows) into a database table using the INSERT statement.

Example in SQL:

INSERT INTO Students (StudentID, Name, Age, Course)

VALUES (101, 'Kumar', 20, 'IT');

3.2 Records Modified in the Table(s) as Required

This refers to updating existing data in one or more columns of a table using the UPDATE statement.

Example in SQL:

UPDATE Students

SET Age = 21

WHERE StudentID = 101;

3.3 Records Deleted in the Table(s) as Required

This means removing specific rows from the table using the DELETE statement.

Example in SQL:

DELETE FROM Students

WHERE StudentID = 101;

4.1 Query Created to Retrieve Data from Single Table (With/Without Conditions)

a) Without conditions – fetch all data from a table:

SELECT * FROM Students;

b) With conditions – fetch students from a specific course:

SELECT Name, Age FROM Students

WHERE Course = 'IT';

4.2 Query Created to Retrieve Data from Multiple Tables (With/Without Conditions)

Assume you have two tables:

• Students(StudentID, Name, CourseID)
• Courses(CourseID, CourseName)

a) Without conditions (INNER JOIN):

SELECT Students.Name, Courses.CourseName

FROM Students

INNER JOIN Courses ON Students.CourseID = Courses.CourseID;

b) With conditions (filtering by course name):

SELECT Students.Name, Courses.CourseName

FROM Students

INNER JOIN Courses ON Students.CourseID = Courses.CourseID

WHERE Courses.CourseName = 'Computer Science';

4.3 Aggregated Function/s Added to the Query as Required

Example: Count number of students in each course:

SELECT CourseID, COUNT(*) AS Total_Students

FROM Students

GROUP BY CourseID;

Example: Average age of students:

SELECT AVG(Age) AS Average_Age FROM Students;

5.1 Views Created as Required

A view is a virtual table based on a SQL query. It does not store data itself but displays data from one or more tables.

Example: Create a view to show student names and their course names

CREATE VIEW StudentCourseView AS

SELECT Students.Name, Courses.CourseName

FROM Students

JOIN Courses ON Students.CourseID = Courses.CourseID;

5.2 Views Modified as Required

To modify a view, you use the CREATE OR REPLACE VIEW (in MySQL, PostgreSQL) or ALTER VIEW (in SQL Server).

Example: Add Age column to the view

CREATE OR REPLACE VIEW StudentCourseView AS

SELECT Students.Name, Students.Age, Courses.CourseName

FROM Students

JOIN Courses ON Students.CourseID = Courses.CourseID;

5.3 Views Deleted as Required

To delete a view, use the DROP VIEW command.

Example:

DROP VIEW StudentCourseView;