Complete
Guide on
SQL
What is a Database?
A database is an organized collection of interrelated data stored
systematically for easy access and management.
What is a DBMS?
A Database Management System (DBMS) is software designed
to create, manage, and organize databases efficiently.
What is an RDBMS?
A Relational Database Management System (RDBMS) is a type
of DBMS where data is stored in tables, also called relations
These tables consist of rows (records) and columns (attributes)
Examples include MySQL, PostgreSQL, Oracle, etc.
What is SQL?
SQL, or Structured Query Language, is used to interact with
RDBMS for storing, retrieving, and manipulating data. It is not a
database itself but a language for interacting with databases.
SQL supports CRUD operations:
CREATE: Used to create databases, tables, and insert data
READ: Retrieve data from a database
UPDATE: Modify existing records
DELETE: Remove data, tables, or databases.
Note: SQL keywords are case-insensitive, e.g., select is equivalent to SELECT.
What is SQL?
SQL is a language for interacting with relational databases, while
MySQL is a relational database system that uses SQL.
SQL Data Types
Data types in SQL define the type of data a column or variable
can store. Refer to the official MySQL documentation for a
comprehensive list: MySQL Data Types.
Here are some commonly used data types:
Data Type DESCRIPTION USAGE
CHAR Fixed-length string (0-255 characters) CHAR(30)
VARCHAR Variable-length string (0-255 characters) VARCHAR(100)
BLOB B inary Large Object (up to 65,535 bytes) BLOB(2000)
INT Integer (-2, 147, 483, 648 to 2, 147, 483, 647 ) INT
TINYINT Small integer (-128 to 127 ) TINYINT
BIGINT Large integer (-9 quintillion to 9 quintillion). BIGINT
BIT Stores x-bit values (1 to 64) BIT(4)
FLOAT Floating-point number with precision up to 23 digits FLOAT
DOUBLE Floating-point number with precision from 24 to 53 digits DOUBLE
BOOLEAN B oolean values (TRUE or FALSE ) BOOLEAN
DATE Stores date values (YYYY-MM-DD) DATE
TIME Stores time values (HH:MM:SS) TIME
YEAR Stores year values in 4 digits (1901 to 2155) YEAR
Note: Use CHAR for fixed-length strings and VARCHAR for variable-length strings
for better memory efficiency. Data types can also include UNSIGNED when only
positive values are required, e.g., UNSIGNED INT.
Types of SQL Commands:
Data Query Language (DQL):
Used to retrieve data from databases.
Example Command: SELECT
Data Definition Language (DDL):
Used to define and manage database objects such
as tables and indexes
Example Commands: CREATE, DROP, ALTER, RENAME, TRUNCATE
Data Manipulation Language (DML):
Used to manipulate existing data in a database.
Example Commands: INSERT, UPDATE, DELETE
Data Control Language (DCL):
Used to manage user access and permissions.
Example Commands: GRANT, REVOKE
Transaction Control Language (TCL):
Used to manage transactions and ensure data consistency.
Example Commands: COMMIT, ROLLBACK, SAVEPOINT
Data Definition Language (DDL)
DDL commands manage the structure of databases and
their objects.
Key Commands:
CREATE TABLE: Used to create a new table.
CREATE TABLE staff (
emp_id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10, 2)
);
ALTER TABLE: Modifies an existing table's structure.
ALTER TABLE staff ADD COLUMN email VARCHAR(100);
DROP TABLE: Deletes a table along with its data.
DROP TABLE staff;
CREATE INDEX: Improves query performance by creating an index.
CREATE INDEX idx_name ON staff (name);
DROP INDEX: Removes an existing index.
DROP INDEX idx_name;
CREATE CONSTRAINT: Ensures data integrity.
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);
DROP CONSTRAINT: Removes an existing constraint.
ALTER TABLE orders DROP CONSTRAINT fk_customer;
TRUNCATE TABLE: Removes all rows but retains the table structure.
TRUNCATE TABLE orders;
Data Query Language (DQL)
DQL commands focus on retrieving data from databases.
Key Features:
SELECT: Retrieves specific columns from a table.
SELECT emp_name, department FROM employees;
WHERE Clause: Filters data based on a condition.
SELECT * FROM employees WHERE department = 'HR';
Operators
=: Equal
>: Greater tha
<: Less tha
>=: Greater than or equa
<=: Less than or equa
<> or !=: Not equal
Logical Operators:
AND: Combines multiple conditions.
SELECT * FROM employees WHERE age > 30 AND department = 'Finance';
OR: Filters records if any condition is true.
SELECT * FROM employees WHERE city = 'New York' OR city = 'Los Angeles';
NOT: Filters records when a condition is false.
SELECT * FROM employees WHERE NOT department = 'HR';
LIKE: Searches for patterns in strings.
SELECT * FROM employees WHERE name LIKE 'A%';
IN: Filters records by matching a list of values.
SELECT * FROM products WHERE category_id IN (101, 102, 103);
BETWEEN: Filters data within a range.
SELECT * FROM sales WHERE sale_date BETWEEN '2024-01-01' AND '2024-06-30';
ORDER BY: Sorts query results.
SELECT name, salary FROM employees ORDER BY salary DESC;
GROUP BY: Groups rows and uses aggregate functions.
SELECT department, AVG(salary) FROM employees GROUP BY department;
Data Manipulation Language (DML)
DML commands are used to insert, update, or delete data.
INSERT: Adds new records.
INSERT INTO employees (name, age, department) VALUES ('Alice', 30, 'HR');
UPDATE: Modifies existing records.
UPDATE employees SET age = 31 WHERE name = 'Alice';
DELETE: Removes records.
DELETE FROM employees WHERE name = 'Alice';
Data Control Language (DCL)
DCL manages database permissions.
GRANT: Provides access to users.
GRANT SELECT ON employees TO user1;
REVOKE: Removes user permissions.
REVOKE SELECT ON employees FROM user1;
Transaction Control Language (TCL)
TCL commands handle transaction management.
COMMIT: Saves all changes permanently.
UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 202;
COMMIT;
ROLLBACK: Reverts changes.
UPDATE accounts SET balance = balance - 500 WHERE account_id = 304;
ROLLBACK;
SAVEPOINT: Creates a transaction checkpoint.
SAVEPOINT before_update;
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 304;
ROLLBACK TO before_update;
COMMIT;
This paraphrased content preserves the structure and
completeness of the original while introducing altered datasets
and examples.
Joins in DBMS In a Database Management System (DBMS), a join
operation combines rows from two or more tables based on a
related column. Joins allow data retrieval from multiple tables by
establishing relationships between them using common keys or
columns.
Types of Joins:
Inner Joi
Outer Joi
Cross Joi
Self Join
Inner Join:
An inner join retrieves rows from two or more tables where the specified join
condition is satisfied. Only rows with matching values in all participating
tables
are included in the result, filtering out non-matching rows.
Syntax:
SELECT columns
FROM table1
INNER JOIN table2
ON
table1.column = table2.column;
Here:
Columns specifies the columns to retrieve
Table1 and table2 are the names of the tables being joined
Column is the common field used to match rows between tables
The ON clause defines the join condition.
Example: Tables
Customers Table: Orders Table:
CustomerID CustomerName OrderID CustomerID Product
1 Eva 201 1 Smartphone
2 Liam 202 2 Tablet
3 Noah 203 3 Laptop
Query:
SELECT Customers.CustomerName, Orders.Product
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
Result:
Customername Product
Eva Smartphone
Liam Tablet
Noah Laptop
Outer Join:
Outer joins retrieve rows from two or more tables while also including
unmatched rows. This ensures that even rows without a match are included,
with NULL values for columns of the unmatched table.
Types of Outer Joins:
a) Left Outer Join (Left Join):
Includes all rows from the left table and matching rows from the right table.
Rows from the left table without matches will have NULL values in the right
table columns.
Query:
SELECT Customers.CustomerName, Orders.Product
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Result:
Customername Product
Eva Smartphone
Liam Tablet
Noah Laptop
Mia Null
b) Right Outer Join (Right Join):
Includes all rows from the right table and matching rows from the left table.
Rows from the right table without matches will have NULL values in the left
table columns.
Query:
SELECT Customers.CustomerName, Orders.Product
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Result:
Customername Product
Eva Smartphone
Liam Laptop
Noah Tablet
Null Accessories
c) Full Outer Join (Full Join):
Includes all rows from both tables, with NULL values in columns
of unmatched rows.
Query:
SELECT Customers.CustomerName, Orders.Product
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Result:
Customername Product
Eva Smartphone
Liam Tablet
Noah Laptop
Mia Null
Null Accessories
Cross Join:
A cross join generates a Cartesian product of rows from two tables, creating
all possible combinations of rows. This join does not require a specific
condition.
Syntax:
SELECT columns
FROM table1
CROSS JOIN table2;
Example: Tables
Students Table: Courses Table:
StudentID StudentName StudentID StudentName
1 Sophia 101 Biology
2 Jackson 102 Chemistry
Query:
SELECT Students.StudentName, Courses.CourseName
FROM Students
CROSS JOIN Courses;
Result:
Customername Product
Sophia Chemistry
Sophia Biology
Jackson Chemistry
Jackson Biology
Self Join:
A self join is a join operation where a table is joined with itself. This is often
used to find relationships or patterns within the same table.
Syntax:
SELECT columns
FROM table1 AS alias1
JOIN table1 AS alias2 ON alias1.column = alias2.column;
Here:
alias1 and alias2 are aliases for the table being joined with itself
The ON clause specifies the join condition.
Example: Tables
Orders Table:
EmployeeID EmployeeName ManagerID
1 Olivia 3
2 Ethan 3
3 Emma Null
4 Lucas 3
Query:
SELECT e1.EmployeeName AS Employee, e2.EmployeeName
AS Manager
FROM Employees AS e1
JOIN Employees AS e2 ON e1.ManagerID = e2.EmployeeID;
Result:
Employee Manager
Olivia Emma
Ethan Emma
Lucas Olivia
This paraphrased content maintains the original structure while using different
datasets and examples. Let me know if further refinements are needed!
can you use tamil names as example
SET OPERATIONS
Set operations in SQL are used to combine or manipulate the
result sets of multiple SELECT queries. They allow you to
perform operations similar to those in set theory, such as union,
intersection, and difference, on the data retrieved from different
tables or queries. Set operations provide powerful tools for
managing and manipulating data, enabling you to analyze and
combine information in various ways.
There are four primary set operations in SQL:
UNIO
INTERSEC
EXCEPT (or MINUS
UNION ALL
UNION:
The UNION operator combines the result sets of two or more SELECT queries
into a single result set. It removes duplicates by default, meaning that if there
are identical rows in the result sets, only one instance of each row will appear
in the final result.
Example: Assume we have two tables: Students and Mentors.
Students Table: Mentors Table:
StudentID StudentName MentorID MentorName
1 Arya 101 Sneha
2 Raj 102 Arjun
UNION Query:
SELECT StudentName FROM Students
UNION
SELECT MentorName FROM Mentors;
Result:
Arya
Raj
Sneha
Arjun
INTERSECT:
The INTERSECT operator returns the common rows that exist in the result
sets of two or more SELECT queries. It only returns distinct rows that appear
in all result sets.
Example: Using the same tables as above.
INTERSECT Query:
SELECT StudentName FROM Students
INTERSECT
SELECT MentorName FROM Mentors;
Result:
Name
(Empty)
In this example, there are no common names between students and
mentors, so the result is an empty set
EXCEPT (or MINUS):
The EXCEPT operator (also known as MINUS in some databases) returns the
distinct rows that are present in the result set of the first SELECT query but
not in the result set of the second SELECT query.
Example: Using the same tables as above.
EXCEPT Query:
SELECT StudentName FROM Students
EXCEPT
SELECT MentorName FROM Mentors;
Result:
Name
Arya
Raj
In this example, the names "Arya" and "Raj" are students but not mentors,
so they appear in the result set.
UNION ALL:
The UNION ALL operator performs the same function as the UNION operator
but does not remove duplicates from the result set. It simply concatenates all
rows from the different result sets.
Example: Using the same tables as above.
UNION ALL Query:
SELECT StudentName FROM Students
UNION ALL
SELECT MentorName FROM Mentors;
Result:
Name
Arya
Raj
Sneha
Arjun
SUBQUERIES
Subqueries, also known as nested queries or inner queries, allow
you to use the result of one query (the inner query) as the input
for another query (the outer query). Subqueries are often used
to retrieve data that will be used for filtering, comparison, or
calculation within the context of a larger query. They are a way
to break down complex tasks into smaller, manageable steps.
Syntax:
SELECT columns
FROM table
WHERE column OPERATOR (SELECT column FROM table WHERE condition);
In this syntax
columns refers to the specific columns you want to retrieve from the outer query
table is the name of the table you're querying
column is the column you're applying the operator to in the outer query
OPERATOR is a comparison operator such as =, >, <, IN, NOT IN, etc
(SELECT column FROM table WHERE condition) is the subquery that provides the
input for the comparison.
Example: Consider two tables: Products and Orders.
Products Table: Orders Table:
ProductID ProductName Price OrderID ProductID Quantity
1 Tablet 1200 102 1 3
2 Phone 800 102 2 2
3 Charger 20
Subquery Example:
Retrieve the product names and quantities for orders with a total cost greater
than the average price of all products.
SELECT ProductName, Quantity
FROM Products
WHERE Price * Quantity > (SELECT AVG(Price) FROM Products);
Result:
ProductName Quantity
Tablet 3
Thank
