College of Information Technology
Second Semester, A.Y. 2025-2026
PRELIM MODULE
MODULE 3
CRUD commands/ advance commands
Introduction
Embarking on the journey of advanced databases involves navigating through
critical stages. Begin with the foundational step of Getting Started Overview and
Installation of XAMPP, a pivotal platform for database management. Delve into the
intricacies of Creating Databases and Tables, shaping the architecture that
underpins data organization. Explore the dynamic realm of CRUD commands,
fundamental for data manipulation, and elevate your proficiency with
advanced commands that unveil the true power of database management. This
comprehensive introduction lays the groundwork for a deep dive into the
multifaceted landscape of advanced databases, emphasizing practical skills in
installation, design, and command execution.
Date and Time Allotment
I. Objectives
At the end of the end of this prelim module, students should be able to:
1. Use Database
2. Introduction to Tables
3. Official Introduction to SELECT
4. Introduction to Aliases
5. The UPDATE Command.
[Link]
Certainly! Here are some basic database commands along with simple explanations:
[Link] DATABASE:
- Command: `CREATE DATABASE database_name;`
- Explanation: This command is used to create a new database with the specified name.
[Link]:
- Command: `USE database_name;`
- Explanation: Switches to the specified database, making it the active database for
subsequent commands.
[Link] TABLE:
- Command:
```sql
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);
```
- Explanation: Creates a new table with the specified columns and their data types.
[Link] INTO:
- Command:
```sql
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
```
- Explanation: Adds a new row of data to the specified table with the specified values.
[Link]:
- Command:
```sql
SELECT column1, column2, ... FROM table_name WHERE condition;
```
- Explanation: Retrieves data from one or more columns of a table based on the
specified condition.
[Link]:
- Command:
```sql
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
```
- Explanation: Modifies existing data in a table based on the specified condition.
[Link]:
- Command:
```sql
DELETE FROM table_name WHERE condition;
```
- Explanation: Removes rows from a table based on the specified condition.
[Link] TABLE:
- Command:
```sql
ALTER TABLE table_name ADD column_name datatype;
```
- Explanation: Adds a new column to an existing table.
[Link] TABLE:
- Command: `DROP TABLE table_name;`
- Explanation: Deletes an existing table and removes all data stored in it.
10. DROP DATABASE:
- Command: `DROP DATABASE database_name;`
- Explanation: Deletes an entire database, including all its tables and data.
These commands are written in SQL, the standard language for interacting with
relational databases. Understanding these basic commands is essential for managing
and manipulating data in a database system.
Normalization is a database design technique that organizes data in a relational
database to reduce redundancy and improve data integrity. The goal of normalization
is to eliminate data anomalies that may arise due to redundant storage of information.
The process involves breaking down a large table into smaller, related tables and
defining relationships between them. The primary motivation for normalization is to
minimize data redundancy and ensure that data is stored in a way that prevents
update anomalies.
There are several normal forms (1NF, 2NF, 3NF, BCNF, 4NF, 5NF, 6NF), each building on
the previous one. Here are examples of normalization up to the third normal form
(3NF):
First Normal Form (1NF):
Ensures that each column in a table contains atomic (indivisible) values.
Example:
Student ID Subjects
101 Math, English
Normalized (1NF):
Student ID Subjects
101 Math,
101 English
Second Normal Form (2NF):
Ensures that all non-key attributes are fully functionally dependent on the primary key..
Example:
Employee ID Project ID Hours
001 101 20
002 101 30
Normalized (2NF):
Student ID Subjects
101 Math,
101 English
Third Normal Form (3NF):
Ensures that there are no transitive dependencies—non-prime attributes depend only
on the primary key.
Example:
Employee ID Department Manager
001 IT John
002 HR Jane
Normalized (3NF):
Employee ID Department Manager
001 IT John
002 HR Jane
These are just a few examples of normalization. Higher normal forms, such as Boyce-
Codd Normal Form (BCNF) and Fourth Normal Form (4NF), address more complex
scenarios and dependencies. The choice of the appropriate normal form depends on
the specific characteristics and requirements of the data being model
1. SELECT * FROM people;
2. SELECT name, age, gender FROM people;
3. SELECT * FROM people WHERE gender = 'F';
4. SELECT * FROM people WHERE age > 30 AND marital_status = 'Single';
5. SELECT * FROM people WHERE skin_tone IN ('fair', 'light');
6. SELECT * FROM people ORDER BY age ASC;
7. SELECT * FROM people ORDER BY height DESC;
8. SELECT gender, COUNT(*) FROM people GROUP BY gender;
9. SELECT eye_color, AVG(height) FROM people GROUP BY eye_color;
[Link] people SET height = 178 WHERE name = 'Noah Martin';
[Link] people SET marital_status = 'Married' WHERE id = 11;
[Link] FROM people WHERE id = 7;
[Link] * FROM people WHERE height BETWEEN 170 AND 180;
[Link] eye_color, COUNT(*) FROM people GROUP BY eye_color;
[Link] * FROM people ORDER BY height DESC LIMIT 1;
[Link] * FROM people WHERE name LIKE '%son';
[Link] people SET marital_status = 'Single' WHERE age < 25;
REFERENCES:
“Build MySQL Databases Bootcamp (Beginner to Expert Course).”
TutorialsPoint, [Link]/course/the-ultimate-mysql-bootcamp-
go-from-sql-beginner-to- expert/.
[Link] databases
[Link]
[Link]
[Link]
evolved-over- time