Databases
A database is an organized collection of data, typically stored and accessed electronically from a computer system. Databases are designed to manage, store, and retrieve information efficiently, allowing users and applications to perform operations like querying, updating, and managing data.
Data: Information like names, dates, prices, etc.
Types of Databases:
- Relational Databases (RDBMS) - Store data in tables (rows and columns). Examples: MySQL, PostgreSQL, Oracle.
- NoSQL Databases - Store unstructured or semi-structured data (documents, key-value pairs, etc.). Examples: MongoDB, Redis, Cassandra.
- Cloud Databases - Hosted on cloud platforms for scalability and accessibility. Examples: AmazonRDS, Google Cloud Firestore.
- In-Memory Databases - Fast, temporary storage in RAM. Example: Redis.
- Other types are also there...
Main Components:
- Schema - The structure defining how data is organized (e.g., tables, fields, relationships).
- Fields (columns) - Describe data type (e.g., name, age)
- Records (rows) - Individual entries
- Queries - Requests to retrieve or modify data (using SQL)
- Indexes - Speed up data search
- Database Management System (DBMS) - Software that manages the database, enabling users to interact with the data (e.g., MySQL, MongoDB).
- Data - The actual information stored.
SQL, NoSQL
SQL and NoSQL are two broad categories of database management systems (DBMS), each with distinct approaches to storing, managing, and querying data.
SQL Databases (Relational Databases)
SQL stands for Structured Query Language. These databases are called relational because they store data in tables, and the tables can be related to each other.
Key Features:
- Structured data: Data is stored in rows and columns (like a spreadsheet).
- Schema-based: You must define the structure (columns and their data types) before adding data.
- Uses SQL language: For example, SELECT * FROM students WHERE age > 18;
- Strong consistency: Ensures that all users see the same data at the same time.
- Best suited for: Applications that need complex queries, transactions, and consistent data (e.g., banking, school systems).
- Examples: MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server e.t.c
NoSQL Databases (Non-relational Databases)
NoSQL means Not Only SQL. These databases are more flexible and are designed to handle unstructured or semi-structured data.
Key Features:
- Flexible schema: You don't need to define the structure beforehand.
- Can store various data formats: Documents, key-value pairs, graphs, etc.
- Highly scalable: Great for handling large volumes of data across multiple servers.
- Eventual consistency: Data might take some time to update across all systems, but it becomes consistent eventually.
- Best suited for: Real-time apps, large-scale systems, or when data structure is not fixed (e.g., social media, IoT).
- Examples: MongoDB (document-based), Cassandra (wide-column), Redis (key-value), Neo4j (graph-based), Firebase (real-time)
SQL
SQL (Structured Query Language) is a standardized language used to manage and manipulate data in relational databases, which organize data into tables with rows and columns. It's designed for querying, inserting, updating, deleting(— this is often referred to as CRUD operations.), and managing database structures like tables and schemas.
SQL works with a fixed schema, meaning the table structure (columns and data types) must be defined before adding data, ensuring consistency but requiring schema changes for modifications.
Basic SQL Commands:
- CREATE - Creates databases or tables
- INSERT - Adds new data
- SELECT - Retrieves data
- UPDATE - Modifies data
- DELETE - Removes data
- DROP - Deletes tables or databases
- ALTER - Changes table structure
SQL Writing Style:
SQL is case-insensitive. But writing like this is recomended:-
- Use UPPERCASE for all SQL keywords and functions to make your code easier to read.
- Use lowercase or camelCase for: Table names, Column names, Aliases
- Split complex queries into multiple lines with proper indentation.
-- Create a table
CREATE TABLE Students (
ID INT,
Name VARCHAR(100),
Age INT
);
-- Insert data
INSERT INTO Students (ID, Name, Age)
VALUES (1, 'Inderjit', 20);
-- Select data
SELECT * FROM Students;
-- Update data
UPDATE Students
SET Age = 21
WHERE ID = 1;
-- Delete data
DELETE FROM Students
WHERE ID = 1;
How to Use SQL in Terminal:
- Login to MySQL from Terminal Use - /usr/local/mysql/bin/mysql -u root -p
- Then enter your MySQL root password when prompted
- You will see the MySQL CLI: mysql>
- Select a Database - USE database_name;
- Run SQL Commands in Terminal
- To Exit - exit; OR press Ctrl + D
How to Use SQL using .sql file
A .sql file contains SQL commands.
- Create a file fileName.sql with your SQL code inside it.
- Login to MySQL from Terminal Use - /usr/local/mysql/bin/mysql -u root -p
- Select a Database - USE database_name;
- then use - source fileName.sql;
Table in SQL
A table is the basic structure in a relational database where data is stored. Think of it like an Excel sheet — with rows and columns.
Rows:
- A row, also called a record or tuple, represents a single, complete data entry in a table. Each row contains values for all columns defined in the table's schema.
- Purpose: Rows store individual instances of the entity the table represents, such as a specific customer, order, or product.
Columns:
- A column, also called a field or attribute, defines a specific property or attribute of the table's entity, with a fixed data type (e.g., INT, VARCHAR, DATE).
- The list of all columns in a table, along with their data types, forms the schema (structure) of the table.
- Purpose: Columns specify what kind of data is stored for each row and enforce consistency across all rows in the table.
Database Operations
Create a Database
The CREATE DATABASE statement is used to create a new SQL database.
Syntax:
CREATE DATABASE database_name;
or CREATE DATABASE IF NOT EXISTS database_name; - Without this, SQL will throw an error if you try to create a database that already exists.
Drop (Delete) a Database
The DROP DATABASE statement is used to drop an existing SQL database.
Syntax:
DROP DATABASE database_name;
DROP DATABASE IF EXISTS database_name; - Prevents an error if the database does not exist.
Show Databases
Lists all databases available on the SQL server.
Syntax:
SHOW DATABASES;
Use
The USE statement tells MySQL to set the default database for the current session. Once you run this command, all subsequent SQL statements (like SELECT, INSERT, UPDATE, etc.) will be executed within the specified database unless stated otherwise.
Syntax:
USE database_name;
Think of it like this: If you have multiple databases (e.g., my_database, test_db, inventory_system), and you want to work inside my_database, you run:
USE my_database;
Table Operations
CREATE TABLE
The CREATE TABLE statement is used to create a new table in a database.
Syntax:
CREATE TABLE table_name (
column_name1 datatype constraint,
column_name2 datatype constraint,
....
);
constraints are optional
INSERT
The INSERT operation is used to add new rows (records) to a table.
Syntax:
Insert into all columns (in order):
INSERT INTO table_name
VALUES (value1, value2, ..., valueN);
Values must match the order and count of the columns defined in the table.
Insert into specific columns:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
Safer and preferred — avoids confusion and errors.
//Example Table
CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR(100),
age INT,
grade CHAR(1)
);
Example 1: Insert into all columns
INSERT INTO Students
VALUES (1, 'Inderjit', 20, 'A');
Example 2: Insert into selected columns
INSERT INTO Students (roll_no, name)
VALUES (2, 'Lucy');
//age and grade will be NULL if not specified and no default is set.
Insert Multiple Rows at Once:
INSERT INTO Students (roll_no, name, age, grade)
VALUES
(3, 'Raj', 21, 'B'),
(4, 'Priya', 19, 'A');
UPDATE
The UPDATE statement in SQL is used to modify the values of existing records in a table. It does not add new rows—it only changes data in rows that already exist.
Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
WHERE condition - Filters which rows to update. Without this, all rows will be updated.
//Example - Update a Single Column:
UPDATE students
SET grade = 'A'
WHERE student_id = 101;
//Example - Update Multiple Columns
UPDATE employees
SET salary = salary + 2000, bonus = 500
WHERE department = 'HR';
//This increases salary by 2000 and sets bonus to 500 for all employees in the HR department.
DELETE
The DELETE statement is used to remove one or more rows from a table in a database — permanently. It does not delete the table itself, only the data inside it.
Syntax:
DELETE FROM table_name
WHERE condition;
WHERE condition - Filters which rows to delete. Without it deletes all rows in a table, But the table structure remains same. Always use a WHERE clause unless you really intend to delete everything.
//Example 1- Delete a Single Row:
DELETE FROM students
WHERE student_id = 101;
//This deletes the row where the student_id is 101.
//Example 2- Delete Multiple Rows:
DELETE FROM orders
WHERE status = 'Cancelled';
//Deletes all orders that are marked as "Cancelled".
RENAME TABLE
RENAME TABLE is used to change the name of a table without affecting its data, structure, indexes, or constraints.
Syntax:
RENAME TABLE old_table_name TO new_table_name;
Example:
RENAME TABLE employees TO staff;
DROP and TRUNCATE
DROP TABLE - Completely removes a table and its structure (including indexes, triggers, and constraints) from the database. The table and its data are permanently deleted.
TRUNCATE TABLE - Removes all rows from a table but keeps the table structure, indexes, triggers, and constraints intact. Essentially, it resets the table to its empty state.
Syntax:
DROP:- DROP TABLE table_name;
TRUNCATE:- TRUNCATE TABLE table_name;
Example:
//Remove table completely
DROP TABLE old_data;
//Clear all rows but keep table structure
TRUNCATE TABLE logs;
SELECT
The SELECT statement is used to retrieve data from one or more tables in a database.
Basic Syntax:
SELECT column1, column2, ... FROM table_name;
You list the columns you want, and the table from which you want them.
With DISTINCT (No Duplicates)
SELECT DISTINCT column1, column2, ... FROM table_name;
Shows only unique values.
//Example table
CREATE TABLE Students (
roll_no INT,
name VARCHAR(100),
age INT,
grade CHAR(1)
);
1. To Select All Columns - use *
SELECT * FROM Column_name;
2. Select Specific Columns:
SELECT name, grade FROM Students;
Show Tables in a Database
Lists all tables present in the selected database.
Syntax:
SHOW TABLES;
Before using this command, you need to select a database first: USE database_name;
ALTER TABLE
The ALTER TABLE statement is used to change the structure of an existing table.
It does not affect the existing data, unless a change (like reducing column size) makes it incompatible.
Basic Syntax:
ALTER TABLE table_name
action;
ADD COLUMN:
The ADD COLUMN clause is used with the ALTER TABLE statement to add a new column to an existing table.
Syntax:
ALTER TABLE table_name
ADD column_name datatype constraint;
Example:
ALTER TABLE employees
ADD department VARCHAR(50);
New columns are NULL for all existing rows, unless a DEFAULT is specified.
DROP COLUMN:
The ADD COLUMN clause is used with the ALTER TABLE statement to add a new column to an existing table.
All the data stored in that column will be lost. The table structure is updated — the column is removed from the schema.
Syntax:
ALTER TABLE table_name
DROP COLUMN column_name;
Example:
ALTER TABLE employees
DROP COLUMN middle_name;
//This removes the middle_name column and all its data from the employees table.
CHANGE (Rename column)
To rename a column, you use the ALTER TABLE statement with the CHANGE keyword.
Syntax:
ALTER TABLE table_name
CHANGE old_column_name new_column_name data_type;
You must always provide the data type when renaming a column, even if the data type is not changing.
Example:
ALTER TABLE employees
CHANGE full_name name VARCHAR(100);
//This renames the column from full_name to name and keeps its data type as VARCHAR(100).
MODIFY
The MODIFY COLUMN clause in MySQL is used with the ALTER TABLE statement to change the data type, constraints, or position of an existing column without changing its name.
Syntax:
ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype new_constraint;
Example - Change data type and make it NOT NULL:
ALTER TABLE employees
MODIFY COLUMN age SMALLINT NOT NULL;
Datatypes
| Data Type | Description | Syntax | Example Usage |
|---|---|---|---|
| CHAR(n) | Fixed-length character string. Pads with spaces if input is shorter than n. | CHAR(7) | 'Ind ' (take all 7 characters) |
| VARCHAR(n) | Variable-length character string (up to n characters). | VARCHAR(100) | 'Inderjit Singh' |
| BLOB | Binary Large Object. Used to store files, images, etc. | BLOB | ProfilePicture BLOB |
| INT | Integer (whole number). | INT | 25, -10, 400 |
| TINYINT | Very small integer (0 to 255). | TINYINT | 1, 255 |
| BIGINT | Very large integer (used for big numbers like IDs). | BIGINT | 123456789012345 |
| BIT(n) | Binary data (1s and 0s), often used for flags. | BIT(1) | 0, 1 |
| FLOAT | Approximate decimal number (less precision). | FLOAT(6,2) | 3.14, 5.67 |
| DOUBLE | Double-precision float (more precision). | DOUBLE(10,4) | 123.4567 |
| BOOLEAN | Stores TRUE or FALSE (internally 1 or 0). | BOOLEAN | TRUE, FALSE |
| DATE | Stores a date (YYYY-MM-DD). | DATE | '2025-06-16' |
| YEAR | Stores a year (4-digit format). | YEAR | 2025 |
UNSIGNED
UNSIGNED is a modifier used with numeric data types to indicate that the column cannot store negative numbers — only 0 and positive values. It also doubles the positive range of a numeric type. e.g use like - datatype UNSIGNED.
Constraints
Constraints are rules applied to columns in a table to ensure the validity, accuracy, and integrity of the data. They control what values are allowed in a column (like: no nulls, must be unique, must match another table, etc.)
NOT NULL
Ensures a column cannot have a NULL value.
UNIQUE
Ensures all values in a column are unique.
DEFAULT
Sets a default value for a column if no value is provided during INSERT.
Example:
CREATE TABLE employees (
id INT PRIMARY KEY,
status VARCHAR(20) DEFAULT 'Active' // default value - active
);
CHECK
Enforces a condition on column values. Ensures all values in a column meet a specified condition.
Example:
CREATE TABLE employees (
id INT PRIMARY KEY,
age INT CHECK (age >= 18) //Ensures age is at least 18.
);
//Single CHECK With Multiple Conditions
CREATE TABLE Students (
id INT PRIMARY KEY,
age INT,
marks INT,
CHECK (age >= 18 AND marks >= 0) //multiple checks like this
);
PRIMARY KEY
A Primary Key is a column or a combination of columns in a table that uniquely identifies each row in that table.
- Primary keys must contain UNIQUE values, and cannot contain NULL values.
- A table can have only ONE primary key.
Example:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100)
);
//OR define like this
CREATE TABLE employees (
id,
name VARCHAR(100)
PRIMARY KEY (id) // Syntax: PRIMARY KEY (column_name)
);
FOREIGN KEY
A Foreign Key is a column (or set of columns) in one table that refers to the Primary Key in another table.
- Foreign Key's can have duplicate & null values.
- It creates a relationship between two tables and ensures referential integrity.
- The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.
Syntax:
FOREIGN KEY (column_name) REFERENCES parent_table(parent_column)
Example:
//Parent Table
CREATE TABLE Departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(100)
);
//Child Table With Foreign Key
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);
Clause
A clause is a building block of an SQL statement. Each clause performs a specific function to retrieve, filter, or organize data from a database.
SELECT Clause
Specifies the columns you want to retrieve from a table.
Example:
SELECT name, age FROM students;
//This tells SQL to fetch only the name and age columns from the students table.
FROM Clause
Tells SQL which table the data should come from.
Example:
SELECT * FROM employees;
//SQL will look into the employees table for data.
WHERE Clause
Filters the data using conditions and operators.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example:
SELECT name, marks
FROM students
WHERE marks > 80;
//Returns only students whose marks are greater than 80.
UNIQUE Clause
Ensures all values in a column are unique.
DEFAULT Clause
Sets a default value for a column if no value is provided during INSERT.
Example:
CREATE TABLE employees (
id INT PRIMARY KEY,
status VARCHAR(20) DEFAULT 'Active' // default value - active
);
LIMIT Clause
The LIMIT clause is used to restrict the number of rows returned in a result set.
Syntax:
SELECT column1, column2, ...
FROM table_name
LIMIT number;
number - how many rows you want to retrive
Example:
SELECT * FROM students
LIMIT 5;
//Show only 5 students.
//Using OFFSET (Optional):
LIMIT 5 OFFSET 10;
//Meaning: Skip first 10 rows and return the next 5.
ORDER BY Clause
The ORDER BY clause is used to sort the result set by one or more columns in ascending (ASC) or descending (DESC) order.
Syntax:
ORDER BY column_name ASC|DESC;
For Multiple Columns:
ORDER BY column1 ASC|DESC, column2 ASC|DESC, ...;
Example:
//Example 1 - Ascending Order (Default):
SELECT name, age FROM students
ORDER BY age;
//Result: Students listed from youngest to oldest.
//Example 2 - Descending Order:
SELECT name, marks FROM students
ORDER BY marks DESC;
//Result: Students listed from highest to lowest marks.
//Example 3 - Multiple Columns:
SELECT name, class, marks FROM students
ORDER BY class ASC, marks DESC;
//Result: First sorted by class in ascending order, Then within each class, sorted by marks in descending order
GROUP BY Clause
The GROUP BY clause is used to group rows that have the same values in one or more columns. It is generally used with aggregate functions. It is used to summarize data.
Syntax:
SELECT column1, AGGREGATE_FUNCTION(column2)
FROM table_name
GROUP BY column1;
For Multiple:
SELECT column1, column2, ..., AGGREGATE_FUNCTION(column)
FROM table_name
GROUP BY column1, column2, ...;
Example:
SELECT department_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY department_id;
//Groups rows by department_id and calculates the count and average salary for each department.
Using Multiple group by:
SELECT class, section, AVG(marks) AS avg_marks
FROM students
GROUP BY class, section;
//Average marks per class and section
Use with HAVING:
If you want to filter groups, you cannot use WHERE. You must use HAVING.
Example:
SELECT class, COUNT(*) AS student_count
FROM students
GROUP BY class
HAVING COUNT(*) > 2;
//Only show classes with more than 2 students
Non-aggregated columns in the SELECT clause must appear in GROUP BY (e.g., SELECT name, AVG(salary) is invalid unless name is in GROUP BY).
Groups are created for each unique combination of GROUP BY column values.
HAVING Clause
The HAVING clause is used to filter the results after grouping, just like the WHERE clause filters rows before grouping.
WHERE filters rows before grouping. HAVING filters groups after aggregation. WHERE can't use with aggregate functions but HAVING can.
Syntax:
SELECT column_name, AGGREGATE_FUNCTION(column)
FROM table_name
GROUP BY column_name
HAVING condition;
Example:
SELECT class, COUNT(*) AS student_count
FROM students
GROUP BY class
HAVING COUNT(*) > 2;
//Selects Classes with more than 2 students
Operators
Operators in SQL are symbols or keywords that perform operations on values, columns, or expressions to filter, compare, combine, or compute data.
1. Comparison Operators
These compare two values and return TRUE or FALSE.
| Operator | Description | Example | Result |
|---|---|---|---|
| = | Equal to | age = 18 | True if age is 18 |
| != | Not equal to | name != 'John' | True if name is not John |
| <> | Not equal to (same as !=) | marks <> 100 | True if marks is not 100 |
| > | Greater than | salary > 50000 | True if salary > 50000 |
| >= | Greater than or equal | marks >= 80 | True if marks is 80 or more |
| < | Less than | price < 100 | True if price < 100 |
| <= | Less than or equal | age <= 25 | True if age is 25 or less |
2. Arithmetic Operators
Arithmetic operators are used to perform mathematical calculations on numeric data types.
| Operator | Description | Example | Result |
|---|---|---|---|
| + | Addition | 10 + 5 | 15 |
| - | Subtraction | 10 - 5 | 5 |
| * | Multiplication | 4 * 3 | 12 |
| / | Division | 10 / 2 | 5 |
| % | Modulus (Remainder) | 10 % 3 | 1 |
3. Logical Operators
Logical operators are used in SQL to combine multiple conditions in a WHERE clause or control flow. They return TRUE, FALSE, or UNKNOWN based on the condition evaluation.
| Operator | Description | Example | Result |
|---|---|---|---|
| AND | Returns TRUE if both conditions are TRUE | age > 18 AND city = 'Delhi' | TRUE only if age > 18 and city is Delhi |
| OR | Returns TRUE if at least one condition is TRUE | marks > 90 OR grade = 'A' | TRUE if either condition is met |
| NOT | Reverses the result of the condition | NOT(status = 'inactive') | TRUE if status is not inactive |
| IN | TRUE if value matches any value in a list | dept IN ('HR', 'IT') | TRUE if dept is either 'HR' or 'IT' |
| BETWEEN | TRUE if value lies within a range (inclusive) | salary BETWEEN 30000 AND 50000 | TRUE if salary is 30000 to 50000 |
| ALL | TRUE if condition is TRUE for all values | marks > ALL (SELECT marks FROM students) | TRUE if marks is greater than all students’ marks |
| LIKE | TRUE if value matches a pattern (uses % or _) | name LIKE 'A%' | TRUE for names starting with 'A' |
| ANY | TRUE if condition is TRUE for any value | salary > ANY (SELECT salary FROM interns) | TRUE if salary is greater than at least one intern |
4. Bitwise Operators
Bitwise operators work at the binary level. They compare each bit of two numbers and return a new number based on binary logic.
| Operator | Name | Example | Binary Calculation | Result |
|---|---|---|---|---|
| & | Bitwise AND | 6 & 3 | 110 & 011 = 010 | 2 |
| | | Bitwise OR | 6 | 3 | 110 | 011 = 111 | 7 |
Aggregate Functions
Aggregate functions perform a calculation on a set of values and return a single value as the result.
COUNT Function
Counts the number of rows (or non-NULL values in a column) in a result set.
Syntax:
SELECT COUNT(Column_name)
FROM table_name;
COUNT(*) (all rows)
//COUNT(*) counts all rows, including those with NULL values.
Example 1:
SELECT COUNT(*) AS total_employees
FROM employees;
SELECT department_id, COUNT(salary) AS salaried_employees
FROM employees
GROUP BY department_id;
Counts employees with non-NULL salaries per department
SUM Function
Calculates the total of numerical values in a column.
Syntax:
SELECT SUM(Column_name)
FROM table_name;
Example:
SELECT SUM(salary) AS total_salary
FROM employees; //Returns sum of all salaries
Example with GROUP BY:
SELECT department_id, SUM(salary) AS dept_salary
FROM employees
GROUP BY department_id; //Returns total salaries per department
AVG Function
Calculates the average (arithmetic mean) of numerical values in a column. Computes the sum of non-NULL values divided by the count of non-NULL values.
Syntax:
SELECT AVG(Column_name)
FROM table_name;
Example:
SELECT AVG(salary) AS avg_salary
FROM employees;
Can be formatted: SELECT ROUND(AVG(salary), 2) FROM employees; rounds to 2 decimal places.
MIN Function
Returns the smallest value in a column. Finds the minimum value for numeric, string, or date columns.
Syntax:
SELECT MIN(Column_name)
FROM table_name;
Example:
SELECT MIN(salary) AS lowest_salary
FROM employees; //Returns lowest salary.
MAX Function
Returns the largest value in a column. Finds the maximum value for numeric, string, or date columns.
Syntax:
SELECT MAX(Column_name)
FROM table_name;
Example 1:
SELECT MAX(salary) AS highest_salary
FROM employees; //Returns highest salary.
General Order
Syntactic Order of SELECT Query Clauses:
The clauses in a SELECT query must be written in a specific order to be syntactically correct. Below is the standard sequence:
- SELECT: Specifies the columns, expressions, or aggregate functions to retrieve.
- FROM: Identifies the table(s) or data source(s) to query.
- JOIN: Combines multiple tables based on relationships (e.g., using PRIMARY KEY and FOREIGN KEY).
- WHERE: Filters individual rows based on conditions.
- GROUP BY: Groups rows with identical values in specified columns for aggregation.
- HAVING: Filters groups based on aggregate conditions.
- ORDER BY: Sorts the final result set.
- LIMIT/OFFSET: (DBMS-specific): Restricts the number of rows returned or skips rows.
Example:
SELECT column1, AGG_FUNC(column2)
FROM table_name
JOIN other_table ON condition
WHERE condition
GROUP BY column1
HAVING condition
ORDER BY column1
LIMIT number;
Logical(Execution) Order:
The DBMS processes a SELECT query in a different order than the syntactic sequence, which affects how data is filtered, grouped, and returned. The logical processing order is:
- FROM and JOIN: Identifies the data source(s) and combines tables using joins (e.g., INNER JOIN, LEFT JOIN).
- WHERE: Filters individual rows based on conditions, reducing the dataset before grouping.
- GROUP BY: Groups remaining rows by specified columns, creating groups for aggregation.
- HAVING: Filters groups based on aggregate conditions, removing groups that don't meet the criteria.
- SELECT: Evaluates the columns, expressions, or aggregate functions to include in the result set.
- DISTINCT (if used): Removes duplicate rows from the result set.
- ORDER BY: Sorts the final result set based on specified columns or expressions.
- LIMIT/OFFSET (if used): Restricts the number of rows returned or skips rows.
Why It Matters: The logical order explains why certain operations occur before others. For example, WHERE filters rows before GROUP BY, so it cannot use aggregate functions (e.g., WHERE COUNT(*) > 2 is invalid), while HAVING can because it's applied after grouping.
MySql with NodeJs
mysql2 Package
To use Node.js with MySQL, the mysql2 package is a popular choice due to its support for both callbacks and promises, making it suitable for modern JavaScript applications.
Installation:
npm install mysql2
Setup Example: Connect Node.js to MySQL:
// db.js
const mysql = require('mysql2'); //1 - require it
// 2 - Create a connection
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'your_password',
database: 'your_database'
});
// Connect to the database
connection.connect((err) => {
if (err) throw err;
console.log('Connected to MySQL database');
});
module.exports = connection;
mysql.createConnection(config)
This method creates a single connection to a MySQL database. It returns a connection object used to interact with the database.
Syntax:
const mysql = require('mysql2');
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
database: 'your_database'
password: 'your_password',
});
Parameters:
host: The address of the MySQL server (usually 'localhost').
user: The MySQL username.
database: The name of the database to connect to.
password: The MySQL password for that user.
Optional: port (default: 3306), ssl, connectTimeout, etc.
//Returns: An object (connection) that lets you: Send SQL queries, Connect/disconnect from MySQL, Handle events and errors
connection.connect(callback)
This method explicitly establishes the connection to the MySQL database. It's optional, as connection.query implicitly connects if not already connected.
Example:
connection.connect((err) => {
if (err) {
console.error('Error connecting to MySQL:', err.message);
return;
}
console.log('Connected to MySQL database!');
});
//Useful if you want to check explicitly whether the connection is successful.
You can handle errors before running queries.
connection.query(sql, values, callback)
Sends an SQL query to the database. It can handle SELECT, INSERT, UPDATE, DELETE, and other SQL statements.
Basic Syntax:
connection.query(sql, [params], (err, results, fields) => {
if (err) throw err;
console.log('Query results:', results);
});
Parameters:
- sql: The SQL query string (e.g., SELECT * FROM users).
- params (optional): Array of values for parameterized queries
- Callback function with:
- err: Error object (null if successful).
- results: Query results (array for SELECT, object for INSERT/UPDATE/DELETE).
- fields(optional): Metadata about result columns (optional, mostly for SELECT).
Example:
// Simple query
connection.query('SELECT * FROM users', (err, results) => {
if (err) throw err;
console.log('Users:', results);
});
// Parameterized query
connection.query(
'INSERT INTO users (name, email) VALUES (?, ?)',
['John Doe', 'john@example.com'],
(err, results) => {
if (err) throw err;
console.log('Inserted ID:', results.insertId);
}
);
In a parameterized query, the ? symbol is used as a placeholder for values that will be safely inserted into the SQL query. This approach prevents SQL injection attacks and ensures proper handling of data types.
Each ? corresponds to a value in an array provided to connection.query. The order of values in the array matches the order of ? placeholders in the query.
connection.end()
connection.end() closes the connection to the MySQL database gracefully after all pending queries are completed.
Syntax:
connection.end([callback]);
callback (optional): A function called when the connection is fully closed or if an error occurs.
Example:
// Simple way:
connection.end();
//With callback:
connection.end((err) => {
if (err) {
console.error('Error closing connection:', err.message);
return;
}
console.log('MySQL connection closed.');
});
In a parameterized query, the ? symbol is used as a placeholder for values that will be safely inserted into the SQL query. This approach prevents SQL injection attacks and ensures proper handling of data types.
Each ? corresponds to a value in an array provided to connection.query. The order of values in the array matches the order of ? placeholders in the query.
Common SQL Operations Example
Select Data:-
connection.query('SELECT * FROM users', (err, results, fields) => {
if (err) throw err;
console.log('All Users:', results);
});
Insert Data:-
db.query('INSERT INTO users (name) VALUES ('John Doe')', [name, email], (err, result) => {
if (err) throw err;
console.log('Inserted ID:', result.insertId);
});
Update Data:-
db.query('UPDATE users SET name = ? WHERE id = ?', ['John Doe', 1], (err, result) => {
if (err) throw err;
console.log('Rows updated:', result.affectedRows);
});
Delete Data:-
db.query('DELETE FROM users WHERE id = ?', [1], (err, result) => {
if (err) throw err;
console.log('Rows deleted:', result.affectedRows);
});
We can also write query in seprate Variable:-
inserting multiple data:-
let query = "INSERT INTO user (id, username, email, password) VALUES ?";
//need only single question mark as we want to pass one array that is nested
let users = [
["123", "123_username", "abc@gmail.com", "abc"],
["456", "456_username", "def@gmail.com", "def"]
]; //multiple sub arrays for multiple rows we do like this also
connection.query(query, [users], (err, result) => {
//and users here in [] brackets as it is complete array
if(err) throw err;
console.log(result);
});
@faker-js/faker Package
Generates massive amounts of fake but realistic data, such as names, emails, addresses, dates, and more.
Installation:
npm install @faker-js/faker
require - const { faker } = require('@faker-js/faker');
// Step 1: Import faker
const { faker } = require('@faker-js/faker');
// Example - Generate fake user
const user = {
faker.string.uuid(), //Unique id
name: faker.person.fullName(), // full name
username: faker.internet.userName(), // username
email: faker.internet.email(), // email
password: faker.internet.password(10), // strong password
gender: faker.person.sex(), // gender
dob: faker.date.birthdate(), // date of birth
phone: faker.phone.number(), // phone number
address: faker.location.streetAddress(), // street address
city: faker.location.city(), // city
state: faker.location.state(), // state/province
country: faker.location.country(), // country name
zip: faker.location.zipCode(), // postal/ZIP code
avatar: faker.image.avatar(), // URL to a random avatar image
bio: faker.lorem.sentence(), // short sentence for user bio
job: faker.person.jobTitle(), // job title
company: faker.company.name(), // company name
website: faker.internet.url(), // website URL
// all these data generate will be random but like real
};
console.log(user);
Inserting in bulk Example:
let getRandomUsers = () => {
return[ //return an array as we need array
faker.person.uuid(),
faker.internet.userName(),
faker.internet.email(),
faker.internet.password(10)
];
}
let query = "INSERT INTO user (id, username, email, password) VALUES ?";
let data = [];
//generating 100 fake users
for(let i = 1; i <= 100; i++){
data.push(getRandomUsers());
}
connection.query(query, [data], (err, result) => {
if(err) throw err;
console.log(result);
});