Introduction to SQL
SQL (Structured Query Language): A programming language used to
communicate with and manipulate databases. It enables CRUD (Create,
Read, Update, Delete) operations.
Why SQL?
Standard language for relational database management systems
(RDBMS)
Allows interaction with databases to:
o Retrieve data
o Manipulate data
o Define database structure
o Control access/permissions
MySQL vs SQL
SQL: The language itself
MySQL: A specific RDBMS that uses SQL
Database Fundamentals
Database
A database is a structured collection of interrelated data organized in a
way that enables efficient storage, retrieval, and manipulation of
information
DBMS (Database Management System)
Software to manage databases (e.g., MySQL, Oracle, MongoDB).
Types of Databases:
1. Relational (RDBMS)
oStores data in tables with rows and columns
o Examples: MySQL, Oracle, MariaDB
2. Non-Relational (NoSQL)
o Doesn't use tables but key value pairs.
o Examples: MongoDB
SQL Commands Classification
Category Description Commands
DQL (Data Query
Retrieves data SELECT
Language)
DML (Data Manipulation
Modifies data INSERT, UPDATE, DELETE
Language)
DDL (Data Definition Defines database CREATE, ALTER, DROP,
Language) structure TRUNCATE, RENAME
DCL (Data Control
Controls access GRANT, REVOKE
Language)
TCL (Transaction Control Manages COMMIT, ROLLBACK,
Language) transactions SAVEPOINT
Database Operations
Creating a Database
CREATE DATABASE databaseName;
-- Or to avoid errors if database exists
CREATE DATABASE IF NOT EXISTS databaseName;
Using a Database
USE databaseName;
Viewing Databases
SHOW DATABASES;
Deleting a Database
DROP DATABASE databaseName;
-- Or to avoid errors
DROP DATABASE IF EXISTS databaseName;
Table Operations
Creating Tables
CREATE TABLE tableName (
column1 datatype constraint,
column2 datatype constraint,
...
);
Example:
CREATE TABLE employee (
empId INT PRIMARY KEY,
name VARCHAR(50),
salary INT
);
Viewing Tables
SHOW TABLES;
Inserting Data
INSERT INTO tableName (column1, column2, ...)
VALUES (value1, value2, ...);
-- Or for multiple rows
INSERT INTO tableName VALUES
(value1, value2, ...),
(value1, value2, ...);
Viewing Data
SELECT * FROM tableName; -- All columns
SELECT column1, column2 FROM tableName; -- Specific columns
Updating Data
UPDATE tableName
SET column1 = value1, column2 = value2
WHERE condition;
Deleting Data
DELETE FROM tableName
WHERE condition;
Modifying Tables
Alter: To modify existing db objects such as tables, indexes or constarints(schema) or
columns
-- Add column
ALTER TABLE tableName ADD columnName datatype;
-- Drop column
ALTER TABLE tableName DROP COLUMN columnName;
-- Modify column datatype
ALTER TABLE tableName MODIFY columnName newDatatype;
-- Rename column
ALTER TABLE tableName CHANGE oldName newName datatype;
-- Or
ALTER TABLE tableName RENAME COLUMN oldName TO newName;
-- Rename table
RENAME TABLE oldName TO newName;
Truncating vs Deleting vs Dropping
Operation Description SQL Syntax
TRUNCATE Removes all rows, keeps structure TRUNCATE TABLE tableName
Removes specific rows based on DELETE FROM tableName WHERE
DELETE
condition condition
DROP Completely removes table/database DROP TABLE tableName
Data Types
Numeric
INT: Whole numbers (-2,147,483,648 to 2,147,483,647)
BIGINT: Larger whole numbers
FLOAT: Floating-point numbers (4-byte)
DOUBLE: Floating-point numbers (8-byte)
DECIMAL(p,s): Exact numeric (p=precision, s=scale)
UNSIGNED: Only positive numbers (e.g., INT UNSIGNED)
String/Character
CHAR(n): Fixed-length strings (0-255 chars)
VARCHAR(n): Variable-length strings (0-255 chars)
TEXT: Variable-length with no specified limit
Date/Time
DATE: YYYY-MM-DD
TIME: HH:MM:SS
DATETIME/TIMESTAMP: YYYY-MM-DD HH:MM:SS
Boolean
BOOLEAN: TRUE/FALSE values
Binary
BINARY(n): Fixed-length binary data
VARBINARY(n): Variable-length binary data
BLOB: Binary Large Object
Constraints
Rules applied to columns:
PRIMARY KEY: Uniquely identifies each record (UNIQUE + NOT
NULL)
FOREIGN KEY: Links two tables
UNIQUE: Ensures all values are unique
NOT NULL: Ensures column cannot have NULL values
CHECK: Ensures condition is met
DEFAULT: Sets default value if none specified
Example:
CREATE TABLE example (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT CHECK (age >= 18),
status VARCHAR(20) DEFAULT 'active'
);
Keys in SQL
Primary Key
Uniquely identifies each record in a table
Cannot contain NULL values
Only one per table (can be composite)
Foreign Key
Field in one table that refers to primary key in another
Establish relationship between tables
Maintains referential integrity(any modifications happening in
parent table should happen in child table too) or perform
joining tables
Can have cascading actions:
o ON DELETE CASCADE
o ON UPDATE CASCADE
Referenecd:parent
referencing:child
Example:
CREATE TABLE orders (
orderId INT PRIMARY KEY,
customerId INT,
FOREIGN KEY (customerId) REFERENCES customers(id)
ON DELETE CASCADE
);
Clauses
WHERE
Filters rows before aggregation
SELECT * FROM table WHERE condition;
GROUP BY
Groups rows with same values
SELECT column, aggregate(column)
FROM table
GROUP BY column;
HAVING
Filters groups after aggregation
SELECT column, aggregate(column)
FROM table
GROUP BY column
HAVING condition;
ORDER BY
Sorts results asc or desc. Default: ascending order
SELECT * FROM table ORDER BY column ASC|DESC;
LIMIT
Restricts number of rows returned/only first n rows from table returned
SELECT * FROM table LIMIT number;
-- Or with offset
SELECT * FROM table LIMIT offset, number;
DISTINCT : Returns unique values
SELECT DISTINCT column FROM table;
Operators
Arithmetic
+, -, *, /, %
Comparison
=, <> or !=, >, <, >=, <=
Logical
AND: Both conditions true
OR: Either condition true
NOT: Reverses condition
Other
IN: Matches any value in a list
BETWEEN: Within a range
LIKE: Pattern matching (% = any sequence, _ = single char)
IS NULL/IS NOT NULL: Checks for NULL values
Aggregate Functions
Perform calculations on sets of values: used with select
COUNT(): Number of rows
SUM(): Sum of values
AVG(): Average of values
MIN(): Minimum value
MAX(): Maximum value
GROUP_CONCAT(): Concatenates values
Example:
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
Joins
Combine rows from two or more tables based on a related or shared or
common columns between them.
Types:
1. INNER JOIN: Matching rows in both tables
SELECT * FROM table1
INNER JOIN table2 ON table1.column = table2.column;
2. LEFT JOIN: All rows from left table + matching from right
3. RIGHT JOIN: All rows from right table + matching from left
4. FULL JOIN: All rows when there's a match in either table
-- MySQL doesn't support FULL JOIN directly
(SELECT * FROM table1 LEFT JOIN table2 ON...)
UNION
(SELECT * FROM table1 RIGHT JOIN table2 ON...);
5. CROSS JOIN: Cartesian product (all possible combinations)
6. SELF JOIN: Join a table with itself
SELECT a.column, b.column
FROM table a, table b
WHERE a.common_field = b.common_field;
Exclusive Joins:
LEFT EXCLUSIVE: Only rows from left with no match in right
RIGHT EXCLUSIVE: Only rows from right with no match in left
FULL EXCLUSIVE: Rows from both with no matches
Unions:
Subqueries
Query nested inside another query:
-- In WHERE
SELECT * FROM table
WHERE column = (SELECT column FROM table2 WHERE...);
-- In FROM
SELECT * FROM (SELECT * FROM table) AS temp;
-- In SELECT
SELECT column, (SELECT MAX(column) FROM table) AS max
FROM table;
Example (find employees with above average salary):
SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Nth Highest Salary
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
LIMIT n-1, 1;
Stored Procedures
Pre-written SQL code that can be saved and reused:
DELIMITER //
CREATE PROCEDURE procedureName(parameters)
BEGIN
SQL statements;
END //
DELIMITER ;
-- Call procedure
CALL procedureName(arguments);
Views
Virtual tables based on result sets:
CREATE VIEW viewName AS
SELECT columns FROM tables WHERE conditions;
-- Use view
SELECT * FROM viewName;
-- Drop view
DROP VIEW viewName;
Transactions
START TRANSACTION;
-- SQL statements
COMMIT; -- or ROLLBACK;
Interview Questions
Common Questions:
1. Difference between DELETE and TRUNCATE?
oDELETE removes rows with conditions and can be rolled back
o TRUNCATE removes all rows quickly and can't be rolled back
2. What is a primary key?
oUnique identifier for each record (UNIQUE + NOT NULL)
3. What is a foreign key?
o Field that refers to primary key in another table
4. Difference between WHERE and HAVING?
oWHERE filters before grouping
o HAVING filters after grouping
5. Types of joins?
o INNER, LEFT, RIGHT, FULL, CROSS, SELF
Practical Questions:
1. Find second highest salary:
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
2. Find duplicate records:
SELECT column, COUNT(*)
FROM table
GROUP BY column
HAVING COUNT(*) > 1;
3. Find employees with names starting with 'A':
SELECT * FROM employees
WHERE name LIKE 'A%';
4. Get department-wise average salary:
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
