DATATYPES IN SQL.

1. Numeric Data Types

• INT (or INTEGER):

o Used to store whole numbers (integers) without any decimal places.
o Range depends on the DBMS, but typically:
▪ INT (4 bytes) can store values between -2^31 and 2^31 - 1.
• SMALLINT:
o A smaller version of INT, used to store small whole numbers.
o Typically range: -32,768 to 32,767.
• BIGINT:
o Used for larger integers than INT.
o Range: -2^63 to 2^63 - 1.
• DECIMAL (or NUMERIC):
o Used for exact precision for numbers with a fixed number of digits before and after the
decimal point.
o Example: DECIMAL(10, 2) means up to 10 digits in total, with 2 digits after the decimal.
o Often used in financial calculations where precision is important.
• FLOAT and REAL:
o Used for storing approximate numeric values with floating-point precision.
o FLOAT has more precision than REAL.
o These are not exact values and are used when exact precision is not required (e.g., scientific
measurements).
• DOUBLE PRECISION:
o Similar to FLOAT, but with more precision and range.

2. String Data Types

• CHAR(n):
o Fixed-length string. Always stores exactly n characters, padding with spaces if necessary.
o Example: CHAR(10) will always store 10 characters (even if fewer characters are provided).
• VARCHAR(n):
o Variable-length string. Can store up to n characters but only uses the space required for the
actual string length.
o Example: VARCHAR(255) can store up to 255 characters but won’t use extra space for
shorter strings.
• TEXT:
o Used to store large amounts of text (e.g., articles, descriptions, etc.).
o The length can be much larger than VARCHAR, often limited by the system, but no length
specification is needed.

3. Date and Time Data Types

• DATE:
o Stores a date in the format YYYY-MM-DD.
o Example: 2024-11-05.
• TIME:
o Stores time in the format HH:MM:SS (hours, minutes, seconds).
o Example: 14:30:00.
• DATETIME (or TIMESTAMP):
o Stores both date and time in the format YYYY-MM-DD HH:MM:SS.
o Example: 2024-11-05 14:30:00.
 • TIMESTAMP:
o Similar to DATETIME, but in some systems, this type might be used to automatically store
the time when a record is created or modified.
o Often includes time zone information.
• YEAR:
o Stores a year in a 4-digit format (YYYY), often used in year-based data like birthdates.

4. Boolean Data Type

• BOOLEAN (or BOOL):

o Represents boolean values, typically TRUE or FALSE.
o In some systems, it might be stored as 1 (for TRUE) and 0 (for FALSE).

5. Binary Data Types

• BINARY(n):
o Stores fixed-length binary data, like images or other binary files.
o Example: BINARY(16) would store 16 bytes of binary data.
• VARBINARY(n):
o Stores variable-length binary data, similar to VARCHAR but for binary data.
o Example: VARBINARY(255) stores up to 255 bytes of binary data.
• BLOB (Binary Large Object):
o Used for storing large binary data (e.g., images, audio files).
o No length restriction in most systems, just limited by system resources.

6. UUID and GUID

• UUID (Universally Unique Identifier):

o A 128-bit value used to store unique identifiers.
o Example: 123e4567-e89b-12d3-a456-426614174000.
• GUID (Globally Unique Identifier):
o Similar to UUID, this is an identifier used in some systems (like SQL Server) to guarantee
uniqueness across databases.

7. JSON and XML Data Types

• JSON:
o Stores JSON (JavaScript Object Notation) formatted data.
o This type is commonly used for NoSQL-like flexibility in SQL databases (supported in MySQL,
PostgreSQL, SQL Server, etc.).
o You can store complex objects and arrays directly.
• XML:
o Used to store XML (eXtensible Markup Language) formatted data.
o Some databases provide specific functions to query and manipulate XML data.

8. Other Data Types

• ENUM:
o A string object with a predefined set of values.
o Example: ENUM('small', 'medium', 'large') would allow only one of these values.
• SET:
 o A type for storing multiple values from a predefined list. Typically used to store a set of flags
or options.
• ARRAY (PostgreSQL):
o Allows you to store an array of values (e.g., INTEGER[] to store a list of integers).
• GEOMETRY / POINT, LINESTRING, POLYGON (PostgreSQL, MySQL Spatial Extensions):
o Used for storing spatial data such as coordinates and shapes.
o Can store points, lines, and polygons for geographic applications.

9. Special Types in Some Systems

• MONEY:
o A type used for monetary values in some systems (e.g., SQL Server, PostgreSQL).
o Stores values in a fixed-point format with a specific precision.

Example Summary of Common Data Types

Data Type Description Example
INT Integer (whole number) 42
VARCHAR(255) Variable-length string "Hello World"
DECIMAL(10, 2) Fixed-point number with 2 decimal places 12345.67
DATE Date (YYYY-MM-DD) "2024-11-05"
Date and time (YYYY-MM-DD HH:MM
DATETIME "2024-11-05 14:30:00"
)
BOOLEAN Boolean value (TRUE or FALSE) TRUE
BLOB Binary Large Object (binary data)
JSON JSON formatted text '{"name": "John"}'
UUID Universally Unique Identifier "123e4567-e89b-12d3-a456-426614174000"

Notes:

• Always check your specific DBMS documentation, as there can be slight differences in data type
implementations.
• Some data types are more suited for particular applications (e.g., DECIMAL for financial data, TEXT
for long descriptions, DATE for dates).

Comments in SQL

In SQL, comments are used to add explanations, notes, or reminders to the code without affecting
its execution. Comments are very helpful for documenting your SQL queries or scripts, especially
when working on complex code, or when sharing SQL with others.

There are two primary ways to implement comments in SQL:

1. Single-Line Comments

Single-line comments are used for short explanations or notes that apply to only one line of code. In
SQL, they are marked with either:

• Two dashes (--)

or

• A hash/pound sign (#) in some databases like MySQL.

Syntax:
-- This is a single-line comment

# This is also a single-line comment (MySQL specific)

Example:
-- Select all rows from the customers table
SELECT * FROM customers;

# This is a comment in MySQL

SELECT * FROM orders;

Both of these comments will be ignored by the SQL engine and will not affect the execution of the
code.

2. Multi-Line Comments

Multi-line comments are used for comments that span multiple lines. They begin with /* and end
with */. This allows you to comment out larger sections of code or provide more detailed
documentation.

Syntax:
/*
This is a multi-line comment.
It can span multiple lines.
Everything between the opening '/*' and closing '*/' will be treated as a comment.
*/

Example:
/*
This query selects all data from the employees table.
It retrieves employee names, departments, and hire dates.
*/
SELECT name, department, hire_date FROM employees;

You can also use multi-line comments to comment out blocks of SQL code temporarily:

/*
SELECT * FROM employees;
SELECT * FROM customers;
*/

In this case, both SELECT statements are commented out, and the SQL engine will ignore them
during execution.
3. Inline Comments

You can also add comments at the end of a line of SQL code. This is often used to explain or clarify
individual components of a query. To do this, simply add a comment after the SQL code on the same
line, starting with --.

Example:
SELECT name, age, salary FROM employees -- Fetch employee details
WHERE age > 30; -- Only employees older than 30

Benefits of Using Comments in SQL:

1. Code Readability: Comments make your code easier to understand, especially for other
developers or for you when you return to it after some time.
2. Documentation: They allow you to explain why certain queries are structured the way they
are, or why certain conditions are used.
3. Debugging: You can comment out parts of a query for debugging purposes.
4. Code Maintenance: In large projects, comments help track changes and provide context for
why certain decisions were made.

Database
To create a database known as classpractice
create database classpractice;

To Verify the Database Creation

1. Check if the Database was Created:
o After executing the command, go to the "Schemas" tab on the left side of the
MySQL Workbench window.
o Right-click on the Schemas area and click Refresh to update the list of databases.
o You should see the newly created database listed there.
2. Verify using SQL: You can also verify that the database was created by executing the
SHOW DATABASES; command:
SHOW DATABASES;
This will display a list of all databases, including the one you just created.
To Select the New Database (Optional)
If you want to start using the newly created database right away, you can select it by executing:
USE my_database;
ie use classpractice;
else you can Replace my_database with the name of your database.
Create the tables below

create three tables: Users, Orders, and Products.

These tables will be related through foreign keys to demonstrate how relationships are established
in an SQL environment.

1. Users Table

The Users table will store information about users, such as their name, email, and date of birth.

CREATE TABLE Users (

user_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100),
last_name VARCHAR(100),
email VARCHAR(100) UNIQUE NOT NULL,
date_of_birth DATE
);

2. Products Table

The Products table will store information about products, such as the product name, description,
and price.

CREATE TABLE Products (

product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL
);

3. Orders Table

The Orders table will store information about customer orders, including a reference to the user
who placed the order and the product they ordered. This table will have foreign keys to the
Users and Products tables.

CREATE TABLE Orders (

order_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
product_id INT,
order_date DATE,
quantity INT,
FOREIGN KEY (user_id) REFERENCES Users(user_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
To view the schema (structure) of a table in MySQL, which shows the table's column names,
data types, constraints, and other attributes, you can use several methods in MySQL Workbench.
Below are a few approaches:

Method 1: Use DESCRIBE Command

The DESCRIBE command (or EXPLAIN) provides a detailed description of the structure of a table.
It shows the column names, data types, nullability, keys, and other details.

1. Open a new SQL tab in MySQL Workbench.

2. Run the following query to describe the Users table:

DESCRIBE Users;

or

EXPLAIN Users;

Result Example:

The result will show something like this:

Field Type Null Key Default Extra

user_id INT NO PRI NULL auto_increment
first_name VARCHAR(100) YES NULL
last_name VARCHAR(100) YES NULL
email VARCHAR(100) NO UNI NULL
date_of_birth DATE YES NULL

• Field: Column names in the table.

• Type: Data types for each column.
• Null: Whether the column can contain NULL values (YES or NO).
• Key: If the column is part of a key (PRI for primary key, UNI for unique key).
• Default: Default value for the column (if any).
• Extra: Any additional properties, like auto_increment.

Method 2: Use MySQL Workbench GUI

1. Navigate to the "Schemas" panel: On the left side of the MySQL Workbench, you will see a
"Schemas" panel listing all databases.
2. Expand your database: Find the database that contains the Users table and click the plus
sign (+) to expand it.
3. View the Tables: Under the expanded database, find and right-click on the Users table.
4. Select "Table Inspector": Right-click on the Users table and choose "Table Inspector"
from the context menu.
o This will open a new window displaying the schema of the Users table, including
columns, indexes, foreign keys, and more.
Method 3: Use SHOW CREATE TABLE Command

To see the full CREATE TABLE statement (including all constraints and indexes), you can use the
SHOW CREATE TABLE command:

SHOW CREATE TABLE Users;

This will return the SQL statement used to create the table, including all column definitions,
primary keys, foreign keys, and any other table attributes.

Example:

SHOW CREATE TABLE Users;

Output might look like this:

| Table | Create
Table
|
| Users | CREATE TABLE `Users` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(100) DEFAULT NULL,
`last_name` varchar(100) DEFAULT NULL,
`email` varchar(100) NOT NULL,
`date_of_birth` date DEFAULT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |

This statement shows the column definitions, primary key, and unique key, as well as other table
settings like the storage engine (InnoDB) and character set.

Summary of Methods to View Schema:

1. DESCRIBE Users; – Displays column names, types, and constraints.

2. SHOW CREATE TABLE Users; – Shows the complete CREATE TABLE statement.
3. Table Inspector in Workbench – Provides a graphical view of the table schema with
details on columns, indexes, etc.

How to view relationships created

It’s possible to view the relationships between tables in MySQL, particularly foreign key
relationships, using MySQL Workbench. Below are the steps to visualize the relationships
between the Users, Products, and Orders tables (or any other tables with foreign key
relationships) in MySQL Workbench.

Method 1: Use the "EER Diagram" in MySQL Workbench

MySQL Workbench allows you to create an EER (Enhanced Entity-Relationship) Diagram
that visually displays the relationships between tables, including foreign keys and their
constraints. Here's how to view the relationships:
Steps to Create an EER Diagram:
1. Open MySQL Workbench and connect to your MySQL database.
2. Navigate to the Database:
o In the Schemas tab (on the left side of the screen), right-click on the database that
contains your tables (for example, my_database).
o Choose "Create EER Model" from the context menu.
3. Add Tables to the Diagram:
o In the EER Diagram window that opens, right-click inside the canvas and select
"Add Table".
o From the list of available tables in the selected database, select the Users,
Products, and Orders tables, then click OK.
4. View Relationships:
o MySQL Workbench will automatically add foreign key relationships between the
tables (if you've already defined them with FOREIGN KEY constraints).
o You should see lines connecting the Users and Orders tables, and the Orders and
Products tables, indicating the relationships.
▪ The user_id in the Orders table will be connected to the user_id in the
Users table (one-to-many).
▪ The product_id in the Orders table will be connected to the product_id in
the Products table (one-to-many).
5. Customize the Diagram (Optional):
o You can rearrange the tables in the diagram to make it more readable.
o You can also zoom in or out to view the relationships clearly.
6. Save the Diagram:
o After the diagram is created, you can save it by going to File → Save Model.
o This will save the diagram as a .mwb file, which you can open later.

Method 2: Use SHOW CREATE TABLE to View Foreign Keys

While the EER diagram is the most visual way to see relationships, you can also check foreign
keys manually by inspecting the table definitions using the SHOW CREATE TABLE command.
Steps:
1. View Foreign Keys in the Orders Table: To see the foreign key relationships, run the
following query:
SHOW CREATE TABLE Orders;
This will display the CREATE TABLE statement for the Orders table, including the foreign key
constraints. The output will show something like:
CREATE TABLE `Orders` (
`order_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`product_id` int(11) DEFAULT NULL,
`order_date` date DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
PRIMARY KEY (`order_id`),
 KEY `user_id` (`user_id`),
KEY `product_id` (`product_id`),
CONSTRAINT `Orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES
`Users` (`user_id`),
CONSTRAINT `Orders_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES
`Products` (`product_id`)
);
Here, the FOREIGN KEY constraints indicate the relationships:
o user_id in the Orders table references user_id in the Users table.
o product_id in the Orders table references product_id in the Products table.
2. Check the Users and Products Tables: Similarly, you can run the following commands
to inspect the foreign key constraints for the other tables:
SHOW CREATE TABLE Users;
SHOW CREATE TABLE Products;
These commands will show the structure of the tables, but foreign keys will only be shown in the
Orders table (since that's where they are defined).
Method 3: View Relationships in Workbench's "Foreign Keys" Tab
1. Open Table Inspector:
o In MySQL Workbench, go to the Schemas tab, expand your database, and locate
the Orders table (where the foreign keys are defined).
o Right-click on the Orders table and select "Table Inspector".
2. Foreign Keys Tab:
o In the "Table Inspector" window, there is a tab called "Foreign Keys".
o This tab will list all foreign keys for the Orders table, including their referenced
tables and columns.
o You'll see two foreign keys:
▪ One linking user_id in the Orders table to user_id in the Users table.
▪ One linking product_id in the Orders table to product_id in the Products
table.
Summary of Methods to View Relationships:
1. EER Diagram: Visualize relationships between tables (best method for visualizing).
2. SHOW CREATE TABLE: Check foreign keys by inspecting the CREATE TABLE
statements.
3. Table Inspector: View the foreign key constraints in the "Foreign Keys" tab in MySQL
Workbench.
These methods allow you to explore and understand the relationships between tables in your
database. If you'd like any more details or have any questions, feel free to ask!

If you're not seeing the EER Model option in MySQL Workbench, it's likely that you're using a
version of MySQL Workbench where the EER Model feature is located elsewhere, or you may need
to create a new Model to visualize your database structure. Let's go through a more detailed step-
by-step guide for creating an EER diagram in MySQL Workbench, which will allow you to visualize
your tables and relationships.
How to Create an EER Diagram in MySQL Workbench
1. Open MySQL Workbench:
 o Launch MySQL Workbench and connect to your MySQL server.
2. Create a New Model: If the "EER Model" is not showing, you can manually create a new
model from the "Model" menu.
o Go to the "File" menu at the top-left corner of MySQL Workbench.
o Click on "File" → "New Model". This will create a new model file for you to work
with.
o A new tab will open with an empty model layout.
3. Add Your Existing Database: You will now want to import the schema (the tables) from
your MySQL database into the model.
o On the right-hand side of the Model tab, you should see an option that says "Add
Table" or "Database".
o Click on the Database icon (a cylinder symbol) on the left panel, and then select
"Reverse Engineer" to import your existing schema (tables) into the model.
4. Reverse Engineer Your Database:
o When prompted, choose "Reverse Engineer" to start the process.
o In the wizard that appears, select your MySQL connection (the one that connects to
your running MySQL server).
o Click Next and proceed to choose the schema (your database) that you want to work
with.
o After selecting your database, click Next to start the process. Workbench will fetch
all tables, relationships, and other database objects.
5. Generate the Diagram:
o Once the reverse engineering process is complete, MySQL Workbench will import
your tables and automatically generate an EER Diagram showing the relationships
(foreign keys) between them.
o The tables will be displayed in the workspace, and relationships will be represented
with lines connecting the relevant columns.
6. View and Adjust the Diagram:
o If the tables aren't automatically arranged neatly, you can drag them around in the
workspace to make the diagram easier to read.
o You’ll see the lines connecting the Orders table to the Users table and the Products
table, which represents the foreign key relationships.
7. Save the Diagram:
o After you’re done working with the diagram, you can save the model by clicking File
→ Save Model, which will save your changes in a .mwb file.
o You can later open this file in MySQL Workbench to continue editing or reviewing
the schema and relationships.

Alternative: Use SQL Queries to Inspect Relationships

If you don't want to use the graphical model, you can always use SQL queries to check for foreign
key relationships, as mentioned earlier:
1. Show Foreign Keys Using SHOW CREATE TABLE:
For example, if you want to check the foreign key relationships in the Orders table, you can use the
following SQL command:
SHOW CREATE TABLE Orders;
This will show you the CREATE TABLE statement, including any foreign key constraints. You can
then manually interpret the relationships.
2. View Foreign Keys in the "Foreign Keys" Tab:
You can also inspect the foreign key constraints in the Table Inspector tab:
• Right-click on the Orders table in the Schemas pane.
 • Select "Table Inspector".
• Go to the Foreign Keys tab to see the foreign key relationships defined for that table.

Relationships

1. Users ↔ Orders:
o The Orders table has a foreign key (user_id) that references the Users table.
This establishes a one-to-many relationship: one user can place many orders, but
each order belongs to only one user.
2. Products ↔ Orders:
o The Orders table has a foreign key (product_id) that references the Products
table. This creates a one-to-many relationship as well: one product can be ordered
many times, but each order refers to a single product.

Summary of Steps to Create an EER Diagram in MySQL Workbench:

1. Open MySQL Workbench and create a New Model (File → New Model).
2. Reverse engineer your database schema into the model (Database → Reverse Engineer).
3. View the automatically generated EER Diagram.
4. Adjust the layout of the diagram as needed.
5. Save the diagram for future use.
If you don’t see the exact options mentioned, it could be due to the version of MySQL Workbench
you’re using. Make sure that your installation is up-to-date, or you can work with the SQL queries to
examine relationships.

INSERTING SAMPLE RECORDS

Now, let's add some sample records to each of the tables.

Insert Records into Users

INSERT INTO Users (first_name, last_name, email, date_of_birth)
VALUES
('John', 'Doe', '[email protected]', '1990-04-15'),
('Jane', 'Smith', '[email protected]', '1985-07-22'),
('Alice', 'Johnson', '[email protected]', '1992-11-30');
('Janet', 'Jackson', '[email protected]', ' ');

Insert Records into Products

INSERT INTO Products (product_name, description, price)
VALUES
('Laptop', 'A high-performance laptop with 16GB RAM and 512GB SSD.', 1200.00),
('Smartphone', 'A latest model smartphone with a 12MP camera.', 799.99),
('Headphones', 'Noise-cancelling over-ear headphones.', 150.50);

Insert Records into Orders

Now, let's create some orders. We'll reference user_id from the Users table and product_id from the Products
table.

INSERT INTO Orders (user_id, product_id, order_date, quantity)

VALUES
(1, 1, '2024-10-20', 1), -- John orders 1 Laptop
(2, 2, '2024-10-21', 2), -- Jane orders 2 Smartphones
(3, 3, '2024-10-22', 1); -- Alice orders 1 Headphones

To view the records you have keyed in type the code below
Select * from users;
Select * from products;
Select * from orders;

HOW TO USE JOINS TO QUERY DATA IN TABLES

Let's go over how to use the different types of JOIN operations to query the tables we created (Users,
Products, and Orders).
These joins will help you combine data from multiple tables based on the relationships between them.
We already know that:
• The Orders table has a foreign key relationship to the Users table (user_id).
• The Orders table has a foreign key relationship to the Products table (product_id).

Basic Example of the Tables:

Here are the tables we'll be working with:
1. Users
CREATE TABLE Users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100),
last_name VARCHAR(100),
email VARCHAR(100) UNIQUE NOT NULL,
date_of_birth DATE
);
2. Products
CREATE TABLE Products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL
);
3. Orders
CREATE TABLE Orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
product_id INT,
order_date DATE,
quantity INT,
FOREIGN KEY (user_id) REFERENCES Users(user_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

1. INNER JOIN
An INNER JOIN returns only the rows that have matching values in both tables. It excludes rows where there
is no match.
Example: Retrieve all orders along with user information and product details.
Explanation:
• The query retrieves all orders (Orders table), along with the corresponding user (Users table) and
product (Products table) details.
• The INNER JOIN ensures that only orders with matching users and products are included.

SELECT
o.order_id,
u.first_name,
u.last_name,
p.product_name,
o.order_date,
o.quantity
FROM Orders o
INNER JOIN Users u ON o.user_id = u.user_id
INNER JOIN Products p ON o.product_id = p.product_id;
Explanation of the query
This SQL query retrieves information about orders, users, and products by joining three tables: Orders, Users,
and Products. Let's break it down:
Breakdown of the Query:
1. SELECT Clause:
• o.order_id: Selects the order_id from the Orders table (aliased as o).
• u.first_name: Selects the first_name from the Users table (aliased as u).
• u.last_name: Selects the last_name from the Users table.
• p.product_name: Selects the product_name from the Products table (aliased as p).
• o.order_date: Selects the order_date from the Orders table.
• o.quantity: Selects the quantity of products ordered from the Orders table.
2. FROM Clause:
• FROM Orders o: This specifies the primary table is Orders, which is aliased as o for
convenience in referencing its columns.
3. INNER JOINs:
• INNER JOIN Users u ON o.user_id = u.user_id: This joins the Orders table to the Users table,
linking them through the user_id field, which exists in both tables. This allows retrieving
user details (like first_name and last_name) for each order.
• INNER JOIN Products p ON o.product_id = p.product_id: This joins the Orders table to
the Products table, linking them through the product_id field, which exists in both tables.
This allows retrieving product details (like product_name) for each order.
What the Query Does:
The query will return a result set that includes the following details for each order:
• The order_id of the order.
• The first_name and last_name of the user who placed the order.
• The product_name of the product that was ordered.
• The order_date (when the order was placed).
• The quantity (how many of that product was ordered).

2. LEFT JOIN (or LEFT OUTER JOIN)

A LEFT JOIN returns all the rows from the left table (the table before JOIN), and the matching rows from the
right table (the table after JOIN). If there is no match, NULL values will be returned for columns from the right
table.
Example: Retrieve all orders with user information, even if the order does not have a product.

Explanation:
 • The LEFT JOIN ensures that all orders are returned, even if there is no matching user or product for
an order.
• If an order has no corresponding user or product, those columns will have NULL values.
SELECT
o.order_id,
u.first_name,
u.last_name,
p.product_name,
o.order_date,
o.quantity
FROM Orders o
LEFT JOIN Users u ON o.user_id = u.user_id
LEFT JOIN Products p ON o.product_id = p.product_id;
Explanation of the query
Breakdown of the Query:
1. SELECT Clause:
• The selected columns are the same as in the previous query:
• o.order_id: From the Orders table.
• u.first_name: From the Users table.
• u.last_name: From the Users table.
• p.product_name: From the Products table.
• o.order_date: From the Orders table.
• o.quantity: From the Orders table.
2. FROM Clause:
• FROM Orders o: The primary table is Orders, aliased as o.
3. LEFT JOINs:
• LEFT JOIN Users u ON o.user_id = u.user_id: This performs a left join between
the Orders table and the Users table on the user_id field. A left join means:
• If there is a matching user_id in both the Orders and Users tables, it will return the
corresponding user data (e.g., first_name, last_name).
• If there is no matching user_id in the Users table for a given order, the query will
still return that order, but the user fields (first_name, last_name) will be NULL.
• LEFT JOIN Products p ON o.product_id = p.product_id: This performs a left join between
the Orders table and the Products table on the product_id field. Similarly, it behaves as
follows:
• If there is a matching product_id in both the Orders and Products tables, it will
return the corresponding product_name.
• If there is no matching product_id in the Products table, the product_name will
be NULL.
What the Query Does:
• The query will return the same set of columns as the previous query (order ID, user details, product
name, order date, and quantity).
• The key difference is that it uses LEFT JOINs, meaning:
• If an order exists without a matching user or a matching product, it will still be included in
the result set.
• For orders that don't have a corresponding user or product in the database,
the first_name, last_name, or product_name fields will be NULL.

NOTE BETTER: HOW TO KNOW WHICH IS THE LEFT TABLE AND WHICH IS THE RIGHT TABLE

1. First LEFT JOIN (Orders LEFT JOIN Users)

• Left table: Orders (o)

 • Right table: Users (u)

Explanation:

• In this part of the query, you're doing a LEFT JOIN between Orders and Users.
o The left table is Orders because it appears first in the LEFT JOIN.
o The right table is Users because it appears second.

This means that all rows from the Orders table will be included, even if there is no corresponding row in the
Users table. If there's no matching user_id for an order, the Users columns (like first_name, last_name) will be
NULL.

2. Second LEFT JOIN (Orders LEFT JOIN Products)

• Left table: Orders (o)

• Right table: Products (p)

Explanation:

• In this part of the query, you're doing another LEFT JOIN, but this time between Orders and
Products.
o The left table is still Orders, because Orders is the first table mentioned in this join clause.
o The right table is Products because it is the second table in this join clause.

This means that all rows from the Orders table will be included in the result, even if there is no matching
product_id in the Products table. If there's no corresponding product, the Products columns (like
product_name) will be NULL.

Visualizing the Joins:

Let's summarize the behavior for each part of the query:

1. First LEFT JOIN (Orders LEFT JOIN Users):

o The left table is Orders. All rows from Orders will be included.
o The right table is Users. If there is no matching user_id in the Users table, the Users columns
(first_name, last_name) will be NULL.
2. Second LEFT JOIN (Orders LEFT JOIN Products):
o The left table is Orders (again). All rows from Orders will be included.
o The right table is Products. If there is no matching product_id in the Products table, the
Products columns (product_name) will be NULL.

Final Outcome:

• The query will return all rows from the Orders table, along with matching Users and Products data (if
available).
• If there is no matching user for an order, the first_name and last_name columns will be NULL.
• If there is no matching product for an order, the product_name column will be NULL.
In Summary:

• Left table: Always the table listed before JOIN.

• Right table: Always the table listed after JOIN.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

A RIGHT JOIN returns all the rows from the right table (the table after JOIN), and the matching rows from the
left table (the table before JOIN). If there is no match, NULL values will be returned for columns from the left
table.
Example: Retrieve all products, even if no orders have been placed for them.
Explanation:
• The RIGHT JOIN ensures that all orders are included, even if there is no product associated with an
order (though this case may not happen often if the foreign key constraint is properly enforced).
• If there is no order for a product, those rows will show NULL for order-related columns (order_id,
order_date, quantity).

SELECT
p.product_id,
p.product_name,
o.order_id,
o.order_date,
o.quantity
FROM Products p
RIGHT JOIN Orders o ON p.product_id = o.product_id;

4. FULL JOIN (or FULL OUTER JOIN)

A FULL JOIN returns all rows when there is a match in either the left or right table. If there is no match, the
result will include NULL values for columns from the table without a match.
Unfortunately, MySQL does not support FULL JOIN directly. However, you can simulate a FULL JOIN by
combining a LEFT JOIN and a RIGHT JOIN using a UNION operation.
Example: Retrieve all orders and products, even if there are no matching orders or products.
SELECT
o.order_id,
u.first_name,
u.last_name,
p.product_name,
o.order_date,
o.quantity
FROM Orders o
LEFT JOIN Users u ON o.user_id = u.user_id
LEFT JOIN Products p ON o.product_id = p.product_id

UNION

SELECT
o.order_id,
u.first_name,
u.last_name,
p.product_name,
o.order_date,
o.quantity
FROM Orders o
 RIGHT JOIN Users u ON o.user_id = u.user_id
RIGHT JOIN Products p ON o.product_id = p.product_id;
Explanation:
• The first part of the query (LEFT JOIN) ensures that all orders, users, and products are returned, even
if there is no matching product or user.
• The second part (RIGHT JOIN) ensures that all users and products are returned, even if there is no
matching order for them.
• UNION combines the results of both parts while removing duplicates.

Summary of SQL Joins and Their Use Cases:

• INNER JOIN: Returns rows that have matching values in both tables.
• LEFT JOIN: Returns all rows from the left table and matched rows from the right table. If no match,
returns NULL for the right table's columns.
• RIGHT JOIN: Returns all rows from the right table and matched rows from the left table. If no match,
returns NULL for the left table's columns.
• FULL JOIN (simulated using UNION): Returns rows when there is a match in either table. If no
match, returns NULL for the missing table's columns.
Let me know if you'd like further examples or explanations!

Difference between Rollback, commit and savepoint in SQL

mysql> use classpractice;
Database changed
mysql> create table monday(
-> indvno int,
-> name varchar(30),
-> address varchar(30),
-> city varchar(30)
-> );
Query OK, 0 rows affected (0.06 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into individuals values (007,'kiarie','6777','nairobi');

Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

Check the changes in the workbench side

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into Monday values (008,'kiki','6778','mombasa');

Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.04 sec)

Check the changes in the workbench side

mysql> Start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into Monday values (011,'kikiki','6779','kisumu');

Query OK, 1 row affected (0.00 sec)

mysql> SAVEPOINT savepoint1;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into Monday values (012,'kikikiki','6780','nakuru');

Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK TO SAVEPOINT savepoint1;

Query OK, 0 rows affected (0.00 sec)

Check for the records

- Check the state of the database after rollback
SELECT * FROM monday;
-- If everything looks good, we can commit the transaction COMMIT;

Types of failures that may occur in database

In a database system, failures refer to events or conditions that disrupt the normal operation of the database,
causing the system to stop functioning correctly, or causing data corruption or loss. Database failures can
occur at various levels, such as hardware, software, or operational levels. Below are the types of failures that
may occur in a database system:
1. Transaction Failures
A transaction represents a single unit of work (such as an insert, update, or delete) that is executed in a
database. Transaction failures can occur due to various reasons:
• Violation of Integrity Constraints: For example, attempting to insert a record with a primary key
that already exists, or violating foreign key constraints.
• Application or User Errors: Incorrect or invalid operations requested by the user or application
(e.g., an invalid SQL query or update).
• System Errors: Internal errors within the DBMS or application logic (e.g., dividing by zero, data type
mismatch).
• Resource Exhaustion: A lack of system resources (e.g., memory, CPU) could prevent the transaction
from completing.
• Concurrency Control Issues: For example, deadlocks or race conditions may result in one or more
transactions failing.
2. System Failures
A system failure refers to an issue with the underlying hardware or software infrastructure that causes the
entire database system to crash or stop functioning:
• Hardware Failures: Issues such as disk crashes, power outages, or server hardware malfunctions
can cause a database to stop working or become corrupted.
• Software Failures: Bugs, faults, or crashes in the Database Management System (DBMS) software
can lead to unexpected shutdowns, loss of connection, or corruption of in-memory data.
• Operating System Failures: The operating system might crash due to memory leaks, kernel panics,
or file system corruption, which would affect the database’s functioning.
• Network Failures: If the database is accessed over a network, network failures (e.g., connectivity
issues, DNS problems, or latency) can prevent users from accessing the database or cause transaction
issues.
3. Media Failures
These occur when the physical media used to store database files or logs become damaged, leading to data
loss:
 • Disk Failures: Hard drive crashes, bad sectors, or disk corruption can cause database files to become
unreadable or lost.
• Storage Subsystem Failures: Failures in storage systems (e.g., RAID failures, SAN/NAS failures) can
lead to unavailability of critical data.
• File System Failures: Corruption of the file system, such as errors in writing to or reading from files
(including data files, log files, etc.), may make data or indexes unreadable.
4. Media/Storage Failures
These occur when data stored on physical devices such as disks or tape drives becomes corrupted or
unavailable:
• Corruption of Backup Data: If the backup media (e.g., tape drives, cloud storage) is damaged, the
backup may become useless, and data recovery will be impossible.
• Loss of Archived Data: Older versions of data may be lost due to disk failure or improper backup
retention policies.
5. Communication Failures
These occur when there are issues with the communication between different parts of the system,
particularly in distributed database systems:
• Network Partitioning: In distributed databases, network partitioning (when nodes in the database
network become unreachable) may cause a temporary loss of service or inconsistent views of data.
• Communication Timeouts: Network or system communication failures could lead to timeouts or
connection losses between client applications and the database server.
6. Concurrency Failures
These occur when multiple transactions attempt to access the same data simultaneously in ways that cause
conflicts:
• Deadlocks: This happens when two or more transactions are blocked, each waiting for the other to
release a resource, creating a situation where none of them can proceed.
• Lost Updates: When two transactions update the same data concurrently, one update may overwrite
the other, causing data loss or inconsistency.
• Temporary Inconsistency: While multiple transactions are being executed concurrently, there may
be brief periods where the data is in an inconsistent state, violating the ACID properties (Atomicity,
Consistency, Isolation, Durability).
7. Integrity Failures
These occur when the database's integrity constraints are violated:
• Constraint Violations: Errors where the data does not meet the integrity rules defined in the
schema. For example, a foreign key violation when a reference is made to a non-existing record.
• Referential Integrity Loss: When relationships between tables are broken due to actions like
deleting a record that other records are dependent on (i.e., deleting a record without updating
related foreign key constraints).
8. Application Failures
These failures happen due to bugs, errors, or issues within the application that interacts with the database:
• Code Bugs: Errors in application logic can lead to incorrect SQL queries, leading to database
inconsistencies or crashes.
• Incorrect Query Handling: Queries that are not properly optimized can overload the database,
causing performance issues or timeouts.
• Incorrect Data Handling: In some cases, an application may pass incorrect or malformed data to the
database, causing unexpected behavior.
9. Data Corruption Failures
These occur when the actual data in the database becomes corrupted, either partially or entirely:
• Logical Corruption: In this case, the data might become logically incorrect or inconsistent, but the
DBMS still reads the data as valid. For example, a transaction that does not properly complete may
leave inconsistent data in a table.
• Physical Corruption: This refers to situations where the actual data file becomes corrupted due to
issues like bad sectors or disk failures, causing the database to become unreadable or the data to be
lost.
10. Human Errors
Errors made by database administrators or end users that can lead to failures:
• Accidental Deletion: Accidental removal or modification of data due to a wrong query or operation.
• Schema Changes: Incorrect changes to the database schema (e.g., dropping a table, changing column
data types) can lead to loss of data or cause applications to fail.
• Improper Backups or Restores: Failing to back up critical data or incorrectly restoring data can
result in significant data loss or corruption.
11. Security Failures
Security-related failures can lead to unauthorized access or loss of data:
• SQL Injection: Malicious users may execute unauthorized queries that compromise the integrity or
security of the database.
• Privilege Escalation: If a user or attacker gains higher privileges than they should have, it could lead
to unauthorized access, data corruption, or data loss.
• Data Breaches: Unauthorized access to sensitive data could lead to leaks, theft, or destruction of
information.
12. Performance Failures
These are failures due to performance degradation, which can impact the availability and responsiveness of
the database:
• Query Performance Degradation: Poorly optimized queries that result in excessive resource usage,
causing slow performance or timeouts.
• Overloaded System Resources: Resource exhaustion (e.g., CPU, memory, disk space) leading to
system crashes or slow performance, making the database unresponsive.

Differences between nosql model and relational model