WEEK3 DATABASE NOTE

[Link]

Data Manipulation Language (DML)

[Link]

In SQL, Data Manipulation Language (DML) is used to manage and manipulate data stored in
the database. DML statements allow you to perform various operations like inserting new data,
updating existing data, or deleting unwanted data. It provides a way to interact with the actual
data in the database.

The INSERT statement

The INSERT statement allows you to insert one or more rows into a table. The following
illustrates the syntax of the INSERT statement:
INSERT INTO table_name(column1, column2,...)
VALUES (value1, value2,...);

In this syntax,

 First, specify the table name and a list of comma-separated columns inside parentheses
after the INSERT INTO clause.
 Then, put a comma-separated list of values of the corresponding columns inside the
parentheses following the VALUES keyword.

When using the INSERT statement, you need to ensure that the number of columns matches the
number of values.

Additionally, you need to specify that the positions of columns correspond precisely to the
positions of their corresponding values.

UPDATE statement

The UPDATE statement updates data in a table. It allows you to change the values in one or
more columns of a single row or multiple rows.

The following illustrates the basic syntax of the UPDATE statement:

UPDATE table_name
SET
column_name1 = expr1,
column_name2 = expr2,
[WHERE
condition];

In this syntax:

 First, specify the name of the table that you want to update data after the UPDATE
keyword.
 Second, specify which column you want to update and the new value in the SET clause.
To update values in multiple columns, you use a list of comma-separated assignments by
supplying a value in each column’s assignment in the form of a literal value, an
expression, or a subquery.
 Third, specify which rows to be updated using a condition in the WHERE clause. The
WHERE clause is optional. If you omit it, the UPDATE statement will modify all rows in
the table.

Notice that the WHERE clause is so important that you should not forget. Sometimes, you may
want to update just one row; However, you may forget the WHERE clause and accidentally
update all rows of the table.

DELETE Statement.
The DELETE command is used to delete data from a table.

DELETE FROM table_name WHERE condition;

More Resources:

INSERT

ACID Properties of Transactions

[Link]

Transactions follow four key properties to ensure accuracy and reliability, called ACID. Each
part of ACID plays a role in making sure that database transactions happen smoothly and safely.
Let’s explore these properties with an easy-to-understand example: a mobile money transfer! 📲💰

🌟 A - Atomicity: “All or Nothing”

Atomicity makes sure that all parts of a transaction are completed together as a single unit. If one
part of the transaction fails, then none of the transaction’s changes are saved.

Example: Let’s say you’re transferring Ksh 1000 from Alex’s account to Bella’s account. There
are two steps:

1. Deduct Ksh 1000 from Alex’s account.

2. Add Ksh 1000 to Bella’s account.
If either the deduction or addition fails, the transaction is rolled back, and no change is saved.
That way, there’s no “halfway” situation where Alex’s money disappears but Bella doesn’t
receive it! ✋

🌐 C - Consistency: Keep It Accurate

Consistency makes sure that transactions do not violate any database rules or constraints. It
ensures the data is always accurate and adheres to the defined standards.

Example: Imagine each account must have a non-negative balance. If Alex only has Ksh 500 in
his account and tries to send Ksh 1000, the database would reject the transaction. This keeps data
“consistent” with the rule that no account can go below zero! 🛑

🔒 I - Isolation: No Interference

Isolation keeps each transaction separate from others. If two people are transferring money at the
same time, isolation makes sure that each transaction happens independently without affecting
the other.

Example: Alex is sending money to Bella, and at the same time, Charles is transferring money to
David. Isolation makes sure these two transfers don’t interfere with each other. Alex’s
transaction won’t be affected by Charles’s, even if they’re happening at the same moment! 🔀

🔐 D - Durability: It’s Permanent!

Durability ensures that once a transaction is committed (completed), the changes made to the
database are saved permanently. Even if there’s a system failure or a power outage, the
transaction won’t be lost.

Example:
After Alex successfully transfers Ksh 1000 to Bella:

 Alex’s account balance should decrease from Ksh 1000 to Ksh 0.

 Bella’s account balance should increase from Ksh 1000 to Ksh 2000.

Once the transaction is committed, Durability makes sure that these changes are permanent. If
the system crashes right after the transaction is done, the new balances (Ksh 0 for Alex and Ksh
2000 for Bella) will still be there when the system recovers. This ensures that no changes are
lost, and Alex and Bella can trust that their balances are accurate and up-to-date. 💾

In this case, Durability guarantees that the updated account balances will remain consistent in the
database, even if there's an unexpected failure or restart of the system after the transaction has
been completed.

More Resources:
 ACID

WEEKLY CHALLENGE

Challenge: SQL Table Creation and Insertion

1. Write an SQL query to create a table called student with the following columns:

 name (String, maximum length of 100 characters)

 age (Integer)🎭
 gender (String, maximum length of 10 characters)🌟

3. Insert at least 3 records into the student table. Each record should have a unique name,
age, and gender.

SOLUTIONS

SQL Table Creation and Insertion Challenge

Solution

1. Creating the student table

sql
CREATE TABLE student (
name VARCHAR(100),
age INT,
gender VARCHAR(10)
);

2. Inserting 3 records into the student table

sql
INSERT INTO student (name, age, gender) VALUES
('Alice Johnson', 20, 'Female'),
('Bob Smith', 22, 'Male'),
('Charlie Brown', 21, 'Other');

Explanation:

1. The CREATE TABLE statement creates a new table called "student" with three columns:

o name as a variable-length string with max 100 characters (VARCHAR(100))

o age as an integer (INT)
o gender as a variable-length string with max 10 characters (VARCHAR(10))
2. The INSERT INTO statement adds three distinct records with:

o Unique names (Alice Johnson, Bob Smith, Charlie Brown)

o Different ages (20, 22, 21)
o Varied gender values (Female, Male, Other)

Each record is completely unique as required by the challenge

WEEK3 QUIZ

What is the function of a SELECT statement in SQL? To add new data into a table To retrieve
data from a database To remove data from a table To modify existing data in a tabl

We are given a multiple-choice question about the function of the SELECT statement in SQL.
The options are:
A. To add new data into a table
B. To retrieve data from a database
C. To remove data from a table
D. To modify existing data in a table
We know that in SQL, the SELECT statement is used to query the database and retrieve data
from one or more tables.
It does not add, remove, or modify data. Those operations are handled by INSERT (add),
DELETE (remove), and UPDATE (modify) statements.
Therefore, the correct answer is: To retrieve data from a database.
Let's mark the correct option.

The SELECT statement in SQL is used to retrieve data from a database. It allows you to
query and fetch specific information from one or more tables based on defined criteria.

Key Points:

 Retrieval Only: SELECT does not add, remove, or modify data.

 Syntax:
sql

SELECT column1, column2

 FROM table_name
WHERE condition;
 Flexibility: Can combine data from multiple tables (using JOIN), filter results (WHERE), sort
(ORDER BY), aggregate (GROUP BY), and more.

Other SQL Commands for Comparison:

 Add Data: INSERT (e.g., INSERT INTO table VALUES (...))

 Remove Data: DELETE (e.g., DELETE FROM table WHERE condition)
 Modify Data: UPDATE (e.g., UPDATE table SET column = value WHERE condition)

Example:

To get all names from an Employees table:

sql
SELECT name FROM Employees;

This returns a result set of names without altering the database.

Thus, the correct answer is:

To retrieve data from a database.

What is the function of an INSERT statement in SQL? To retrieve data from a database To
remove data from a table To add new data into a table To modify existing data in a table

We are asked about the function of the INSERT statement in SQL.

From the options:
- To retrieve data from a database -> This is SELECT.
- To remove data from a table -> This is DELETE.
- To add new data into a table -> This matches INSERT.
- To modify existing data in a table -> This is UPDATE.
So, the correct function of the INSERT statement is to add new data into a table.

The INSERT statement in SQL is used to add new data into a table.

Key Points:
 Adds New Rows: It inserts one or more new records into a table.
 Syntax:
sql

INSERT INTO table_name (column1, column2)

VALUES (value1, value2);
 Does Not Alter Existing Data: Unlike UPDATE, it only adds fresh data.

Comparison with Other Commands:

 Retrieve Data: SELECT (e.g., SELECT * FROM table)

 Remove Data: DELETE (e.g., DELETE FROM table WHERE condition)
 Modify Data: UPDATE (e.g., UPDATE table SET column = value WHERE condition)

Example:

To add a new employee to an Employees table:

sql
INSERT INTO Employees (name, role)
VALUES ('Jane Doe', 'Developer');

This creates a new row in the table without affecting existing data.

Correct Answer:
To add new data into a table.

What is the function of a DELETE statement in SQL? To retrieve data from a database To
modify existing data in a table To remove data from a table To add new data into a table

We are given a multiple-choice question about the SQL DELETE statement.

The options are:
A. To retrieve data from a database
B. To modify existing data in a table
C. To remove data from a table
D. To add new data into a table
We know that in SQL:
 - The SELECT statement is used to retrieve data.
- The INSERT statement is used to add new data.
- The UPDATE statement is used to modify existing data.
- The DELETE statement is used to remove existing data from a table.
Therefore, the correct function of the DELETE statement is to remove data from a table.
Let's format the answer accordingly.

The DELETE statement in SQL is used to remove data from a table.

Key Points:

 Removes Rows: It deletes existing records from a table based on specified conditions.
 Syntax:
sql

DELETE FROM table_name

WHERE condition;
 Permanent Action: Once executed, the removed data cannot be recovered (unless backed up).

Critical Notes:

 Use WHERE Carefully: Omitting WHERE deletes all rows in the table (use with extreme
caution!).
 Not for Dropping Tables: To remove an entire table structure, use DROP TABLE.

Comparison with Other Commands:

 Retrieve Data: SELECT

 Add Data: INSERT
 Modify Data: UPDATE

Example:

To remove an employee with id = 101:

sql
DELETE FROM Employees
WHERE id = 101;
 This permanently deletes the matching row(s) from the table.

Correct Answer:
To remove data from a table.

What is the function of an UPDATE statement in SQL? To add new data into a table To remove
data from a table To retrieve data from a database To modify existing data in a table

We are going through the core SQL statements. The previous ones were SELECT, INSERT,
DELETE.
Now the question is about the UPDATE statement.
The UPDATE statement in SQL is used to modify existing records in a table.
Let me check the options:
- To add new data into a table -> That's INSERT
- To remove data from a table -> That's DELETE
- To retrieve data from a database -> That's SELECT
- To modify existing data in a table -> This is UPDATE
Therefore, the correct function of the UPDATE statement is to modify existing data in a table.

The UPDATE statement in SQL is used to modify existing data in a table.

Key Points:

 Alters Existing Rows: Changes values in one or more columns of existing records based on
specified conditions.
 Syntax:
sql

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;
 Targeted Action: Requires a WHERE clause to specify which rows to update.
Omitting WHERE updates all rows (use cautiously!).

Comparison with Other Commands:

 Add Data: INSERT (adds new rows)
 Remove Data: DELETE (removes rows)
 Retrieve Data: SELECT (reads data without modification)

Example:

To update an employee's role in an Employees table:

sql
UPDATE Employees
SET role = 'Senior Developer'
WHERE id = 102;

This modifies the value in the role column for the employee with id = 102, leaving other data
intact.

Correct Answer:
To modify existing data in a table.

What does 'atomicity' mean in the context of a database transaction? A transaction is processed
in isolation from other transactions A transaction is either fully completed or fully rolled back,
without partial completion A transaction can be split into multiple smaller transactions A
transaction is always consistent and never fails

We are discussing the ACID properties of database transactions. Atomicity is one of these
properties.
Atomicity ensures that a transaction is treated as a single, indivisible unit of work. This means:
- Either all operations within the transaction are completed successfully (and committed),
- Or if any operation fails, the entire transaction is rolled back (i.e., all changes are undone) to
leave the database in its previous state.
Now, let's evaluate the options:
1. "A transaction is processed in isolation from other transactions" -> This describes Isolation,
not Atomicity.
2. "A transaction is either fully completed or fully rolled back, without partial completion" ->
This matches the definition of Atomicity.
 3. "A transaction can be split into multiple smaller transactions" -> This is the opposite of
atomicity (atomic means indivisible).
4. "A transaction is always consistent and never fails" -> This is not correct. Transactions can
fail, but atomicity ensures that if they fail, they are rolled back. Also, consistency is a separate
ACID property.
Therefore, the correct answer is the second option.

Atomicity in database transactions refers to the property that a transaction is either fully
completed or fully rolled back, with no partial completion.

Key Points:

 "All or Nothing" Principle:

o If every operation within a transaction succeeds, the entire transaction

is committed (permanently saved).
o If any operation fails (e.g., due to an error, crash, or constraint violation), the entire transaction
is rolled back (all changes are undone).
 No Middle Ground: Transactions cannot be partially applied. The database remains unchanged
if a transaction fails midway.

Example:

Consider a bank transfer transaction with two steps:

1. Deduct $100 from Account A.

2. Add $100 to Account B.
Atomicity ensures:

 Both steps succeed → $100 is transferred.

 If Step 2 fails → Step 1 is reversed (Account A is refunded). No "partial transfer" occurs.

Contrast with Other ACID Properties:

 Isolation: Transactions run independently of others (concurrency control).

 Consistency: Transactions leave the database in a valid state (following rules/constraints).
 Durability: Committed transactions survive system failures.

Why the Other Options Are Incorrect:

 ❌ "Processed in isolation" → Describes isolation, not atomicity.

 ❌ "Split into smaller transactions" → Violates atomicity (transactions are indivisible).
 ❌ "Always consistent and never fails" → Transactions can fail, but atomicity ensures clean
rollback.

In short: Atomicity guarantees that transactions are indivisible units of work.

Week 3 Assignment
Description

Assignment Submission Guidelines

1. Use the provided repository only – Do not create a new repository or add new files
outside the one shared with you. Use the link below to access the assignment
repository created under your name.
2. Submit the correct link – Any submission link that does not match the one provided
by the trainer will not be reviewed.
3. Follow the given questions – Do not create your own questions for this assignment.
4. Use the specified database – Complete the assignment in the salesdb database
provided in Week 2. Do not create a new database.

WEEK3 Assignment Questions

Question 1

Write an SQL statement to create a table named student with the following columns:

id (an integer and the primary key)

fullName (a text field with a maximum of 100 characters)

age (an integer)

Question 2 ➕

Write an SQL statement to insert at least 3 records into the student table.

Question 3 🔄
Write an SQL statement to update the age of the student with ID 2 to 20 in the student table.

# Week 3 Assignment Solutions

## SQL Queries File (assignment_week3.sql)

```sql

-- Question 1: Create student table

CREATE TABLE student (

id INT PRIMARY KEY,

fullName VARCHAR(100),

age INT

);

-- Question 2: Insert sample records

INSERT INTO student (id, fullName, age)

VALUES

(1, 'John Smith', 18),

(2, 'Emily Johnson', 19),

(3, 'Michael Williams', 20);

-- Question 3: Update student age

UPDATE student
SET age = 20

WHERE id = 2;

```

## Word Document: Week 3 Assignment Solutions

# Week 3 Assignment Solutions

**Student Name:** [Your Name]

**Date:** [Current Date]

**Course:** Database Management Systems

---

## Question 1: Create Student Table

**Objective:** Create a table named `student` with specified columns.

**SQL Query:**

```sql

CREATE TABLE student (

id INT PRIMARY KEY,

 fullName VARCHAR(100),

age INT

);

```

**Explanation:**

- Creates a new table named `student` in the existing salesdb database

- `id` column:

- Integer type

- Primary key (unique identifier for each student)

- `fullName` column:

- VARCHAR(100) - stores text up to 100 characters

- `age` column:

- Integer type

- This table structure follows relational database best practices with a primary key constraint

---

## Question 2: Insert Sample Records

**Objective:** Insert at least 3 records into the student table.

**SQL Query:**

```sql

INSERT INTO student (id, fullName, age)

VALUES

(1, 'John Smith', 18),

(2, 'Emily Johnson', 19),

(3, 'Michael Williams', 20);

```

**Explanation:**

- Inserts three student records with different information

- Each record includes:

- Unique ID number

- Full name (within 100 character limit)

- Age value

- Sample records demonstrate diverse student data

- Follows proper INSERT syntax with explicit column names

---

## Question 3: Update Student Age

**Objective:** Update the age of the student with ID 2 to 20.

**SQL Query:**

```sql

UPDATE student

SET age = 20

WHERE id = 2;

```

**Explanation:**

- Updates the `age` field specifically for student ID 2

- `WHERE id = 2` ensures only the targeted student record is modified

- Changes Emily Johnson's age from 19 to 20

- Demonstrates proper use of UPDATE statement with WHERE clause for precision

- Maintains data integrity by only modifying the specified field

## Summary

This solution demonstrates fundamental database operations:

1. **Table Creation**: Designed a normalized table structure

2. **Data Insertion**: Added sample data with proper syntax

3. **Data Modification**: Updated records with precision

All operations comply with the assignment requirements and use the existing salesdb database.
The solution follows best practices for SQL syntax and database management.

**Verification Steps:**

1. Execute CREATE TABLE statement first

2. Run INSERT statement to populate data

3. Verify records with: `SELECT * FROM student;`

4. Execute UPDATE statement

5. Verify update with: `SELECT * FROM student WHERE id = 2;`

**Signature:** _________________________

**Date:** _________________________

## Repository Submission Guidelines

1. Save this SQL file as `assignment_week3.sql` in your assigned repository

2. Add this Word document (`Week3_Solutions.docx`) to the repository

3. Commit both files to the repository

4. Push changes to the remote repository

5. Submit the repository link through the designated platform

Both files provide a complete solution to the Week 3 assignment questions following the
specified requirements.

//////////////////////////////////////////

Skip to content
Chat history
You said:
Create the database [Link]
Document
Use the specified database – Complete the assignment in the salesdb
database provided in Week 2. Do not create a new database. USE THE
[Link] TO EXECUTE THIS WEEK3 ASSIGNMENT WEEK3 Assignment
Questions Question 1 🧑🎓 Write an SQL statement to create a table named
student with the following columns: id (an integer and the primary key)
fullName (a text field with a maximum of 100 characters) age (an integer)
Question 2 ➕ Write an SQL statement to insert at least 3 records into the
student table. Question 3 🔄 Write an SQL statement to update the age of the
student with ID 2 to 20 in the student table. # Week 3 Assignment Solutions
ChatGPT said:

Great — I checked the [Link] you uploaded, and it sets up

the salesDB database and its schema. Since your Week 3 tasks only ask you
to create, insert, and update a new table (student), we’ll just work inside
the same salesDB without creating a new database.

Here are the Week 3 Assignment Solutions:

Question 1 🧑🎓

Create the student table

USE salesDB; CREATE TABLE student ( id INT PRIMARY KEY, fullName VARCHAR(100),
age INT );

Question 2 ➕

Insert at least 3 records

INSERT INTO student (id, fullName, age) VALUES (1, 'Alice Johnson', 19), (2,
'Michael Smith', 18), (3, 'Sophia Brown', 21);

Question 3 🔄
Update age of student with ID 2 to 20

UPDATE student SET age = 20 WHERE id = 2;

✅ These queries will work directly in your existing salesDB from Week 2
without changing the schema.

Would you like me to also prepare this as a clean [Link] file (so you
can submit/upload it directly)?

Sources
You said:
PLEASE DO IT AS PROFESSIONAL SQL
ChatGPT said:

Got it ✅ — let’s rewrite everything in a professional SQL style with clear

formatting, comments, and best practices.

Here’s the polished version for your Week 3 Assignment Solutions:

/* ----------------------------------------------------------- Week 3
Assignment Solutions Database: salesDB (from Week 2 setup)
----------------------------------------------------------- */ /*
----------------------------------------------------------- Question 1 🧑🎓
Create a table named 'student' with the following columns: - id (Primary Key,
integer) - fullName (text field, up to 100 characters) - age (integer)
----------------------------------------------------------- */ USE salesDB;
CREATE TABLE student ( id INT PRIMARY KEY, fullName VARCHAR(100) NOT NULL, age
INT NOT NULL ); /* -----------------------------------------------------------
Question 2 ➕ Insert at least 3 records into the 'student' table
----------------------------------------------------------- */ INSERT INTO
student (id, fullName, age) VALUES (1, 'Alice Johnson', 19), (2, 'Michael
Smith', 18), (3, 'Sophia Brown', 21); /*
----------------------------------------------------------- Question 3 🔄
Update the age of the student with ID = 2 to 20
----------------------------------------------------------- */ UPDATE student
SET age = 20 WHERE id = 2;
 ✨ Notes:

 Added structured comments so your professor/assessor can easily follow.

 Used uppercase SQL keywords for readability.
 Added NOT NULL to fullName and age for data consistency.

Would you like me to generate this into an [Link] file so you can
directly submit it?

You said:
YES PLEASE
ChatGPT said:

Here’s your professionally formatted SQL solution file ready for submission:

📂 Download Week3_Assignment_Solutions.sql

Would you also like me to combine this with your Week 2 schema setup so
you can run everything (Week 2 + Week 3) in one go, or keep it as a
standalone Week 3 file only?

You said:
KEEP ONLY WEEK3
ChatGPT said:

Perfect 👍 — the file I prepared already contains only the Week 3

assignment queries (no Week 2 schema).

You can directly use it for submission:

📂 Download Week3_Assignment_Solutions.sql

✅ It includes:

 CREATE TABLE student

 INSERT with 3 sample records
 UPDATE query for ID = 2

Do you also want me to make a short README note (in .txt) that explains
how to run the file step by step?

You said:
YES PLEASE
ChatGPT said:

Here’s the README with step-by-step instructions for running your Week 3
SQL file:
 📄 Download README_Week3.txt

Now you have:

 ✅ Week3_Assignment_Solutions.sql (your queries)

 ✅ README_Week3.txt (instructions for execution)

Do you want me to bundle both files together in a single zip package for
easy submission?

You said:
YES PLEASE
ChatGPT said:

All set ✅ — I bundled your files into one package:

📦 Download Week3_SQL_Assignment_Package.zip

It contains:

 Week3_Assignment_Solutions.sql (professional SQL queries)

 README_Week3.txt (step-by-step instructions)

Would you like me to also include a sample output screenshot (query

results) so your submission looks even more complete?

ChatGPT can make mistakes. Check important info.