SQL and NoSQL Databases

PhD Students Course

SQL and NoSQL Databases 1 / 19

Course Outline

1 Overview of Databases

2 SQL Databases

3 NoSQL Databases

SQL and NoSQL Databases 2 / 19

What is a Database?

Organized collection of data

Managed by a Database Management System (DBMS)
Enables storage, modification, and retrieval of data

SQL and NoSQL Databases 3 / 19

Definition of Collection of Data

Definition
An organized collection of data refers to data that is systematically
arranged and stored so that it can be easily accessed, managed, and
updated.

Data is structured in a meaningful way (e.g., tables, records, fields).

Enables efficient retrieval and manipulation of information.
Supports consistency, accuracy, and relevance.
Forms the core of any database system—relational or non-relational.

Example: A student database that stores student IDs, names, courses,

and grades in a structured table.

SQL and NoSQL Databases 4 / 19

What is a DBMS?

Definition
A Database Management System is a software tool that interacts with
users, applications, and the database itself to collect and analyze data.

Composed of components such as operations, schemes, programs,

descriptions, users, and interfaces.
Provides methods for defining, creating, and managing databases.
Acts as an interface between users/applications and the database
system.
Allows users or application programs to access data efficiently.
Includes user-friendly interfaces for non-technical users to create
databases and run queries using forms or buttons.

A DBMS is essential for effective and efficient database management.

SQL and NoSQL Databases 5 / 19

Example of a DBMS: MySQL

MySQL
MySQL is a widely used, open-source relational based on SQL.

Developed by Oracle Corporation.

Ideal for web applications and online data storage.
Supports ACID properties for reliable transactions.
Uses tables to store structured data.
Commonly used with PHP and Apache in web development stacks
(e.g., LAMP).

Use case: Storing and managing user data for websites like WordPress
and e-commerce platforms.

SQL and NoSQL Databases 6 / 19

SQL & NoSQL DBMS

Types of DBMS
There are two well-known types of DBMS: SQL (Relational) and NoSQL
(Non-relational).

SQL (Structured Query Language)

Relational databases with a fixed schema and structured tables (e.g.,
MySQL, PostgreSQL).

NoSQL (Not Only SQL)

Non-relational databases designed for flexible, scalable data models (e.g.,
MongoDB, Cassandra).

SQL and NoSQL Databases 7 / 19

SQL Databases
Tabular Structure: Data is stored in tables consisting of rows and
columns, where each row represents a record and each column
represents an attribute.
Fixed Schema: The structure of the data (schema) must be defined
before inserting data. This ensures consistency and data integrity.
ACID Compliance: Ensures reliable transactions through:
Atomicity – all operations in a transaction complete or none do.
Consistency – maintains valid data according to defined rules.
Isolation – concurrent transactions do not interfere with each other.
Durability – once a transaction is committed, it is permanently
recorded.
SQL Language Support: SQL is used for querying, updating,
inserting, and deleting data through standard commands like SELECT,
INSERT, UPDATE, and DELETE.
Strong Data Integrity and Constraints: Enforces primary keys,
foreign keys, unique constraints, and data types to ensure valid and
related data.
SQL and NoSQL Databases 8 / 19
Installing and Starting MySQL on Windows
1 Download MySQL Installer:
Go to [Link]
Download the MySQL Installer for Windows.
2 Run the Installer:
Choose Developer Default for a full setup.
Follow the prompts to install MySQL Server and tools.
3 Configure MySQL Server:
Set root password and create user accounts.
Choose default port (3306) and authentication method.
4 Start MySQL Server:
Use the MySQL Workbench or Services app to start the server.
Alternatively, open Command Prompt and run:
net start MySQL

5 Verify Installation:
Open Command Prompt and type:
mysql -u root -p

Enter your password to access the MySQL shell.

SQL and NoSQL Databases 9 / 19
Example: Creating Student Table and Inserting Data

-- Create student table

CREATE TABLE student (
student_id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
major VARCHAR(50)
);

-- Insert 4 rows into the student

INSERT INTO student VALUES
(1, 'Alice', 20, 'Computer Science'),
(2, 'Bob', 22, 'Mathematics'),
(3, 'Charlie', 21, 'Physics'),
(4, 'Diana', 23, 'Chemistry');

SQL and NoSQL Databases 10 / 19

Example: Creating Lecturer Table and Inserting Data

-- Create lecturer table

CREATE TABLE lecturer (
lecturer_id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
email VARCHAR(100)
);

-- Insert 4 rows into lecturer

INSERT INTO lecturer VALUES
(101, 'Dr. Smith', 'Computer Science', 'smith@[Link]'),
(102, 'Dr. Johnson', 'Mathematics', 'johnson@[Link]'),
(103, 'Dr. Lee', 'Physics', 'lee@[Link]'),
(104, 'Dr. Patel', 'Chemistry', 'patel@[Link]');

SQL and NoSQL Databases 11 / 19

Selecting Data (Contains)

-- Select all students

SELECT * FROM student;

-- Select students majoring in Computer Science

SELECT * FROM student
WHERE major = 'Computer Science';

SQL and NoSQL Databases 12 / 19

Adding Data (Insert)

-- Insert a new student

INSERT INTO student (student_id, name, age, major)
VALUES (5, 'Eva', 20, 'Biology');

SQL and NoSQL Databases 13 / 19

Removing Data (Delete)

-- Remove student with student_id = 2

DELETE FROM student
WHERE student_id = 2;

SQL and NoSQL Databases 14 / 19

Updating Data (Update)

-- Update Diana's major to 'Environmental Science'

UPDATE student
SET major = 'Environmental Science'
WHERE name = 'Diana';

SQL and NoSQL Databases 15 / 19

Research Query Examples

-- Find students older than 21

SELECT * FROM student
WHERE age > 21;

-- Count number of students per major

SELECT major, COUNT(*) AS total
FROM student
GROUP BY major;

SQL and NoSQL Databases 16 / 19

Lecturers and Students in Computer Science

-- List all Computer Science lecturers

SELECT name, email
FROM lecturer
WHERE department = 'Computer Science';

-- List all students majoring in Computer Science

SELECT name, age
FROM student
WHERE major = 'Computer Science';

-- Optional: Join lecturers and students by department/major

-- (if you want to show them together)
SELECT [Link] AS lecturer, [Link] AS student
FROM lecturer l
JOIN student s ON [Link] = [Link]
WHERE [Link] = 'Computer Science';

SQL and NoSQL Databases 17 / 19

Exercises: SQL Practice with Students and Lecturers
1 Write a query to select all students who are older than 21.
2 Insert a new lecturer into the lecturer table with your data.
3 Delete the student whose name is ’Bob’.
4 Update the major of the student ’Charlie’ to ’Data Science’.
5 Retrieve the names and emails of all lecturers in the ’Mathematics’
department.
6 Count how many students are enrolled in each major.
7 Write a query to list all students and their corresponding lecturers from the
same department/major.
8 Add a column ‘email‘ to the student table and update it with example email
addresses.
9 Find all students whose name starts with the letter ’D’.
10 Write a query to list all lecturers who do not have any students in their
department.
SQL and NoSQL Databases 18 / 19
NoSQL

SQL and NoSQL Databases 19 / 19