SQL Basics: An Introduction to Structured Query Language

 Getting Started with SQL: Foundational Concepts and Syntax

📚 What is a Database?

A database is a digital place where we store, organize, and manage information in a structured way. Think of it like a digital filing cabinet that helps keep data neat and easy to find.

---

🧠 Real-Life Example:

Imagine you're keeping a list of:

• Your favorite books
• Friends’ contact numbers
• Grocery items

If this info is scattered across paper or files, it becomes messy and hard to manage.

📂 How a Database Works:

A database has tables (like folders), and each table contains:

• Rows – represent individual records or entries
• Columns – represent specific details about each record

📑 Example: Contacts Table

Name	Phone Number	Email
Alice	123-456-7890	alice@email.com
Bob	987-654-3210	bob@email.com

✅ Why Use a Database?

• 🔍 Easy to search and filter data
• 🛠️ Easy to update or delete records
• 🔒 Helps in securely managing information
• 💡 Essential for websites, apps, and business systems

🛠️ How Does a Database Work?

A database works by storing and managing information in an organized structure. Below is a simplified breakdown of how it functions:

• 📊 Structure: Information is organized into tables (like spreadsheets). Each table has rows (individual items) and columns (details about those items).


• ✏️ Data Entry: You can add new data to tables, just like entering a new row in a spreadsheet with all the details filled in.


• 🔍 Retrieval: Databases allow quick searching and retrieval of information — like searching for a file in a well-labeled cabinet.


• 📝 Updates: You can edit existing data. For example, updating a contact's phone number or correcting a typo.


• 🔗 Relationships: Tables can be linked using relationships. Example: linking a students table to a courses table to track enrollments.


• ❓ Queries (Questions): You can use SQL (Structured Query Language) to ask questions like: SELECT name FROM students WHERE age > 20;


• 🔒 Security: Access control ensures only authorized users can read, write, or delete information. Like locking your filing cabinet!


• ✅ Consistency: Rules (called constraints) keep data accurate — e.g., no two users with the same unique ID.

📌 In short: A database helps you store, organize, retrieve, and manage data efficiently and securely!

Local Database vs. Server Database

The terms "local database" and "server database" refer to where the database is stored and how it is accessed:

1. Local Database:

• A local database is stored and managed on an individual computer or device.
• Typically used by a single user or a small group on the same machine.
• Common in personal projects or small-scale applications.
• No need for internet or network to access the data.

2. Server Database:

• A server database is stored on a central server and accessed over a network.
• Allows multiple users or applications to access data remotely.
• Used in large-scale applications, businesses, and enterprise environments.
• Supports centralized management and collaborative access.

Key Differences:

Aspect	Local Database	Server Database
Access	Accessed only on the same device	Accessed over a network by multiple users
Scope	Best for personal or small-scale use	Ideal for large-scale or business use
Scalability	Limited by local machine capacity	Highly scalable for growth and performance
Collaboration	Limited or single-user collaboration	Supports team and multi-user collaboration
Maintenance	Easier to maintain locally	Needs proper setup, backups, and security

In summary: Choose a local database for personal or low-scale projects. Use a server database when you need sharing, scalability, and centralized management.

---

RDBMS (Relational Database Management System)

RDBMS stands for Relational Database Management System. It stores data in the form of tables (rows and columns) and defines relationships between those tables to maintain data integrity.

Key Features of RDBMS:

• Structured format: Data is stored in predefined tables.
• Relationships: Tables can be linked using keys (primary and foreign keys).
• Data Integrity: Ensures consistency using constraints and normalization.
• SQL: Uses Structured Query Language for accessing and managing data.
• Multi-user access: Supports concurrent data access by multiple users.
• Security: Provides user roles and permissions to control access.

Popular RDBMS Examples:

• MySQL
• Oracle Database
• Microsoft SQL Server
• PostgreSQL
• SQLite

Advantages of RDBMS:

• Easy to understand and use with table structure
• Supports complex queries with JOIN operations
• Ensures data accuracy and integrity
• Scalable and secure for business use

Conclusion: RDBMS is a powerful system for organizing and managing data through relational tables. It is widely used in all industries due to its flexibility, reliability, and data integrity features.

📘 SQL NOTES – Complete Guide in Simple Language

📌 What is SQL?

SQL (Structured Query Language) is used to communicate with databases. It helps you:

• Retrieve specific data
• Add, delete, or modify records
• Organize and control database access
• Perform actions as groups or transactions

SQL Commands: DDL, DML, DCL

1. DDL – Data Definition Language

DDL commands are used to define and manage the structure of database objects like tables, schemas, and indexes.

Key Commands:

• CREATE: Creates a new database object (e.g., table, schema)
• ALTER: Modifies an existing object (e.g., add a column to a table)
• DROP: Deletes an object (e.g., table, schema)
• TRUNCATE: Removes all records from a table but retains the structure

Example: CREATE Command

CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), age INT );

Example: ALTER Command

ALTER TABLE employees ADD email VARCHAR(100);

Example: DROP Command

DROP TABLE employees;

Example: TRUNCATE Command

TRUNCATE TABLE employees;

2. DML – Data Manipulation Language

DML commands are used to manipulate the data in the database, such as inserting, updating, or deleting data.

Key Commands:

• INSERT: Adds new data to a table
• UPDATE: Modifies existing data in a table
• DELETE: Removes data from a table

Example: INSERT Command

INSERT INTO employees (id, name, age) VALUES (1, 'John Doe', 30);

Example: UPDATE Command

UPDATE employees SET age = 31 WHERE id = 1;

Example: DELETE Command

DELETE FROM employees WHERE id = 1;

3. DCL – Data Control Language

DCL commands are used to control the access and permissions for users to perform specific actions on database objects.

Key Commands:

• GRANT: Provides specific privileges to a user or role
• REVOKE: Removes privileges from a user or role

Example: GRANT Command

GRANT SELECT, INSERT ON employees TO user1;

Example: REVOKE Command

REVOKE INSERT ON employees FROM user1;

Summary of SQL Commands

SQL Type	Command	Purpose
DDL	CREATE	Create database objects
DML	INSERT	Add data to a table
DCL	GRANT	Assign privileges to users

SQL Operators

1. Arithmetic Operators

Operator	Description	Example
+	Addition	SELECT 5 + 3; (returns 8)
-	Subtraction	SELECT 10 - 5; (returns 5)
*	Multiplication	SELECT 5 * 3; (returns 15)
/	Division	SELECT 10 / 2; (returns 5)
%	Modulus (Remainder)	SELECT 10 % 3; (returns 1)

2. Comparison Operators

Operator	Description	Example
=	Equal to	SELECT * FROM employees WHERE age = 30;
!= or <>	Not equal to	SELECT * FROM employees WHERE age != 30;
>	Greater than	SELECT * FROM employees WHERE salary > 50000;
<	Less than	SELECT * FROM employees WHERE salary < 50000;
>=	Greater than or equal to	SELECT * FROM employees WHERE salary >= 50000;
<=	Less than or equal to	SELECT * FROM employees WHERE salary <= 50000;

3. Logical Operators

Operator	Description	Example
AND	Combines multiple conditions, all must be true	SELECT * FROM employees WHERE age > 25 AND salary > 50000;
OR	Combines multiple conditions, at least one must be true	SELECT * FROM employees WHERE age > 25 OR salary > 50000;
NOT	Negates a condition	SELECT * FROM employees WHERE NOT age > 30;

4. Other Operators

Operator	Description	Example
BETWEEN	Selects values within a range	SELECT * FROM employees WHERE age BETWEEN 30 AND 40;
LIKE	Searches for a specified pattern	SELECT * FROM employees WHERE name LIKE 'A%';
IN	Selects values within a list	SELECT * FROM employees WHERE department IN ('HR', 'Finance');

SQL JOIN Operators

Used to combine rows from two or more tables based on a related column between them.

Operator	Description	Example
INNER JOIN	Returns records that have matching values in both tables	SELECT * FROM employees INNER JOIN departments ON employees.department_id = departments.id;
LEFT JOIN	Returns all records from the left table and matched records from the right table	SELECT * FROM employees LEFT JOIN departments ON employees.department_id = departments.id;
RIGHT JOIN	Returns all records from the right table and matched records from the left table	SELECT * FROM employees RIGHT JOIN departments ON employees.department_id = departments.id;
FULL JOIN	Returns all records when there is a match in either left or right table	SELECT * FROM employees FULL JOIN departments ON employees.department_id = departments.id;

🧮 Aggregate Functions in SQL

Aggregate functions perform calculations on a set of values and return a single value. They are often used with the GROUP BY clause in SQL.

📋 List of SQL Aggregate Functions

Function	Description
COUNT()	Counts the number of rows
SUM()	Adds up numeric values
AVG()	Calculates the average value
MIN()	Finds the smallest value
MAX()	Finds the largest value

🔢 1. COUNT()

Counts the number of non-NULL rows.

Syntax:

SELECT COUNT(column_name) FROM table_name;

Example:

SELECT COUNT(*) FROM employees;

✅ Returns total number of employees.

➕ 2. SUM()

Returns the total sum of a numeric column.

Syntax:

SELECT SUM(column_name) FROM table_name;

Example:

SELECT SUM(salary) FROM employees;

✅ Returns the total salary paid to all employees.

➗ 3. AVG()

Returns the average value of a numeric column.

Syntax:

SELECT AVG(column_name) FROM table_name;

Example:

SELECT AVG(salary) FROM employees;

✅ Returns the average salary.

⬇️ 4. MIN()

Returns the smallest value in a column.

Syntax:

SELECT MIN(column_name) FROM table_name;

Example:

SELECT MIN(salary) FROM employees;

✅ Returns the lowest salary in the table.

⬆️ 5. MAX()

Returns the largest value in a column.

Syntax:

SELECT MAX(column_name) FROM table_name;

Example:

SELECT MAX(salary) FROM employees;

✅ Returns the highest salary in the table.

📊 Using Aggregate Functions with GROUP BY

To apply aggregate functions per group, use the GROUP BY clause.

Example:

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

✅ Returns the average salary per department.

⚠️ Notes

• Aggregate functions ignore NULL values (except COUNT(*)).
• Use with HAVING to filter grouped results.

Example with HAVING:

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

✅ Shows departments with more than 5 employees.