MySQL
Learn
MySQL
www.ameerpettechnologies.com
MySQL
Banking
Application(Java)
BANK DB
<Project Account table Login table
M
Code>
Y
College Mgmt
Sys(Python) S CMS DB
Q Student table Login table
<Project L
Code>
REPOSITORY
www.ameerpettechnologies.com
MySQL
SQL Commands
DDL DML DRL TCL
Create Insert Select Commit
Alter Update Rollback
Drop Delete Save Point
Truncate
Rename
www.ameerpettechnologies.com
MySQL
DDL commands
www.ameerpettechnologies.com
MySQL
www.ameerpettechnologies.com
MySQL
www.ameerpettechnologies.com
MySQL
www.ameerpettechnologies.com
MySQL
www.ameerpettechnologies.com
MySQL
DML commands
www.ameerpettechnologies.com
MySQL
www.ameerpettechnologies.com
MySQL
www.ameerpettechnologies.com
MySQL
www.ameerpettechnologies.com
MySQL
DRL – where clause
www.ameerpettechnologies.com
MySQL
www.ameerpettechnologies.com
MySQL
www.ameerpettechnologies.com
MySQL
Relational Operators
www.ameerpettechnologies.com
MySQL
www.ameerpettechnologies.com
MySQL
www.ameerpettechnologies.com
MySQL
Logical Operators
www.ameerpettechnologies.com
MySQL
www.ameerpettechnologies.com
MySQL
www.ameerpettechnologies.com
MySQL
www.ameerpettechnologies.com
MySQL
LIKE Operator
www.ameerpettechnologies.com
MySQL
The LIKE operator is used for pattern matching with wildcards:
% (percent sign) → Matches zero or more characters
_ (underscore) → Matches a single character
Names starting with 'A’:
SELECT * FROM Employee WHERE name LIKE 'A%';
Names ending with 'a’:
SELECT * FROM Employee WHERE name LIKE '%a';
Names containing 'ri' anywhere:
SELECT * FROM Employee WHERE name LIKE '%ri%';
Names having exactly 5 characters:
SELECT * FROM Employee WHERE name LIKE '_____';
Location starts with 'B' or 'H’:
SELECT * FROM Employee WHERE location LIKE 'B%' OR location LIKE 'H%';
www.ameerpettechnologies.com
MySQL
Department ending with 'e’:
SELECT * FROM Employee WHERE dept LIKE '%e';
Employees whose names have 'a' in the second position:
SELECT * FROM Employee WHERE name LIKE '_a%';
Names that start with 'R' and have 'y' somewhere after:
SELECT * FROM Employee WHERE name LIKE 'R%y%';
Department does NOT contain 'IT’:
SELECT * FROM Employee WHERE dept NOT LIKE '%IT%';
Names where the third character is 'i’:
SELECT * FROM Employee WHERE name LIKE '__i%';
Names containing 'a' and ending with 'a’:
SELECT * FROM Employee WHERE name LIKE '%a%a';
www.ameerpettechnologies.com
MySQL
Aggregate Functions
www.ameerpettechnologies.com
MySQL
Aggregate Functions in MySQL
Aggregate functions in MySQL perform calculations on a set of values and return a
single value.
www.ameerpettechnologies.com
MySQL
Count the Total Number of Employees:
SELECT COUNT(*) FROM EMPLOYEE;
SELECT COUNT(*) AS total_employees FROM Employee;
Find the Average Salary of Employees:
SELECT AVG(salary) AS average_salary FROM Employee;
Find the Highest Salary in the Company:
SELECT MAX(salary) AS highest_salary FROM Employee;
Find the Lowest Salary in the Company:
SELECT MIN(salary) AS lowest_salary FROM Employee;
Find the Total Salary Paid to Employees:
SELECT SUM(salary) AS total_salary FROM Employee;
www.ameerpettechnologies.com
MySQL
Find the Average Salary of Employees Aged 30 or Above:
SELECT AVG(salary) AS avgSalAbove30 FROM Employee
WHERE age >= 30;
Find the Highest Salary in the IT Department:
SELECT MAX(salary) AS highSalIT FROM Employee
WHERE dept = 'IT';
Calculate Total Salary for Employees in HR or Finance:
SELECT SUM(salary) AS totalSal FROM Employee
WHERE dept = 'HR' OR dept = 'Finance';
Find the Lowest Salary of Employees Not in Mumbai:
SELECT MIN(salary) AS result FROM Employee
WHERE NOT location = 'Mumbai';
www.ameerpettechnologies.com
MySQL
OrderBy Clause
www.ameerpettechnologies.com
MySQL
Display all employees sorted by salary in ascending order:
SELECT * FROM Employee ORDER BY salary ASC;
Display employees in descending order of age:
SELECT * FROM Employee ORDER BY age DESC;
Display emps from the IT department, sorted by salary in descending order:
SELECT * FROM Employee WHERE dept = 'IT' ORDER BY salary DESC;
Display employees who earn more than 50,000, sorted by name alphabetically:
SELECT * FROM Employee WHERE salary > 50000 ORDER BY name ASC;
Display emps whose names start with 'A', sorted by salary in ascending order:
SELECT * FROM Employee WHERE name LIKE 'A%' ORDER BY salary ASC;
www.ameerpettechnologies.com
MySQL
GroupBy Clause
www.ameerpettechnologies.com
MySQL
Queries to count employees for each department individually:
SELECT COUNT(*) AS it_emps FROM Employee WHERE dept = 'IT';
SELECT COUNT(*) AS hr_emps FROM Employee WHERE dept = 'HR';
SELECT COUNT(*) AS finance_emps FROM Employee WHERE dept = 'Finance';
SELECT COUNT(*) AS admin_emps FROM Employee WHERE dept = 'Admin';
SELECT COUNT(*) AS sales_emps FROM Employee WHERE dept = 'Sales';
Single Query Using Group By:
SELECT dept, COUNT(*) AS total_employees
FROM Employee
GROUP BY dept;
www.ameerpettechnologies.com
MySQL
Count Employees in Each Department:
SELECT dept, COUNT(*) AS total_employees
FROM Employee GROUP BY dept;
Find the Average Salary in Each Department:
SELECT dept, AVG(salary) AS avg_salary
FROM Employee GROUP BY dept;
Find the Maximum Salary in Each Location:
SELECT location, MAX(salary) AS max_salary
FROM Employee GROUP BY location;
Find the Total Salary Paid in Each Department:
SELECT dept, SUM(salary) AS total_salary
FROM Employee GROUP BY dept;
www.ameerpettechnologies.com
MySQL
Having Clause
www.ameerpettechnologies.com
MySQL
Count Employees in Each Department (Only Where Count > 2):
SELECT dept, COUNT(*) AS total_employees
FROM Employee GROUP BY dept HAVING COUNT(*) > 2;
Find Departments Where the Average Salary is Greater Than 50,000:
SELECT dept, AVG(salary) AS avg_salary
FROM Employee GROUP BY dept HAVING AVG(salary) > 50000;
Find Locations Where the Maximum Salary Exceeds 80,000:
SELECT location, MAX(salary) AS max_salary
FROM Employee GROUP BY location HAVING MAX(salary) > 80000;
Find Departments Where the Total Salary Paid Exceeds 1,50,000:
SELECT dept, SUM(salary) AS total_salary
FROM Employee GROUP BY dept HAVING SUM(salary) > 150000;
www.ameerpettechnologies.com
MySQL
Constraints
www.ameerpettechnologies.com
MySQL
Constraints in MySQL are rules applied to table columns to ensure data integrity
and accuracy:
www.ameerpettechnologies.com
MySQL
www.ameerpettechnologies.com
MySQL
Create Voters Table with Constraints:
CREATE TABLE Voters (
id INT NOT NULL UNIQUE,
name VARCHAR(50) NOT NULL,
age INT CHECK (age >= 18),
state VARCHAR(50) DEFAULT 'Odisha' CHECK (state = 'Odisha')
);
Valid Insertions
Automatically Sets Odisha:
INSERT INTO Voters (id, name, age) VALUES (101, 'Amit', 25);
Explicitly Setting Odisha:
INSERT INTO Voters (id, name, age, state) VALUES (102, 'Rahul', 30, 'Odisha');
www.ameerpettechnologies.com
MySQL
Violations
Inserting a Voter with a Different State:
INSERT INTO Voters (id, name, age, state) VALUES (103, 'Priya', 28, 'Bihar');
Inserting a Duplicate id:
INSERT INTO Voters (id, name, age, state) VALUES (101, 'Sita', 29, 'Odisha');
Inserting a Voter Without id:
INSERT INTO Voters (id, name, age, state) VALUES (NULL, 'Kiran', 22, 'Odisha');
Inserting a Voter Below 18 Years:
INSERT INTO Voters (id, name, age, state) VALUES (105, 'Raj', 16, 'Odisha');
www.ameerpettechnologies.com
MySQL
Primary Key
Foreign Key
www.ameerpettechnologies.com
MySQL
www.ameerpettechnologies.com
MySQL
www.ameerpettechnologies.com
MySQL
Creating the Course Table (Primary Key):
CREATE TABLE Course (
cid INT PRIMARY KEY,
course VARCHAR(20) UNIQUE NOT NULL,
fee DECIMAL(10,2),
duration INT
);
Inserting Data into Course Table:
INSERT INTO Course (cid, course, fee, duration) VALUES
(1, 'Java', 5000, 3),
(2, 'Python', 4500, 2);
www.ameerpettechnologies.com
MySQL
Creating the Students Table (Foreign Key):
CREATE TABLE Students (
num INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
cid INT,
FOREIGN KEY (cid) REFERENCES Course(cid)
);
Inserting Data into Students Table:
INSERT INTO Students (num, name, cid) VALUES
(1, 'Amit', 1), (2, 'Rahul', 2), (3, 'Priya', 1),
(4, 'Kiran', 2), (5, 'Sita', 1), (6, 'Ajay', 2),
(7, 'Ravi', 1), (8, 'Neha', 2), (9, 'Vikas', 1),
(10, 'Pooja', 2), (11, 'Manoj', 1), (12, 'Sneha', 2);
www.ameerpettechnologies.com
MySQL
Joins
www.ameerpettechnologies.com
MySQL
Get All Records from both the Tables:
SELECT s.num, s.name, c.course, c.fee, c.duration
FROM Students s
JOIN Course c ON s.cid = c.cid;
Only Java Students:
SELECT s.num, s.name, c.course, c.fee, c.duration
FROM Students s
JOIN Course c ON s.cid = c.cid
WHERE c.course = 'Java';
www.ameerpettechnologies.com
MySQL
Sub Queries
www.ameerpettechnologies.com
MySQL
What is a Subquery?
• A subquery (also called a nested query) is a query that is written
inside another query.
• It is used to retrieve data from one query and pass it to another
query for further processing.
Types of Subqueries
Single-row Subqueries – Returns one value (used with =, >, <, >=,
<=).
Multi-row Subqueries – Returns multiple values (used with IN, ANY,
ALL).
www.ameerpettechnologies.com
MySQL
Display Maximum Age:
SELECT MAX(age) AS maxAge FROM Employees;
Display Second Maximum Age:
SELECT MAX(age) AS secondMax
FROM Employees
WHERE age < (SELECT MAX(age) FROM Employees);
Display Minimum Age:
SELECT MIN(age) AS minAge FROM Employees;
Display Second Minimum Age:
SELECT MIN(age) AS secondMin
FROM Employees
WHERE age > (SELECT MIN(age) FROM Employees);
www.ameerpettechnologies.com
MySQL
www.ameerpettechnologies.com
MySQL
Query to Find Employees in the HR Department:
SELECT * FROM Employees
WHERE dept_id = (SELECT dept_id FROM Department WHERE
dept_name = 'HR');
www.ameerpettechnologies.com
MySQL
Thank You
Best Wishes for your Interviews
www.ameerpettechnologies.com
