MySQL_Advanced_Queries_Presentation.pptx

MySQL SELECT
• Syntax: SELECT column1, column2 FROM
table_name;
• Example:
• SELECT Name, Age FROM Students;
• Purpose: Retrieve specific data from a
database table.

MySQL WHERE
• Syntax: SELECT * FROM table_name WHERE
condition;
• Example:
• SELECT * FROM Students WHERE Age > 18;
• Purpose: Filter records that meet specific
criteria.

MySQL AND, OR, NOT
• Example:
• SELECT * FROM Students WHERE Age > 18
AND Gender = 'Male';
• SELECT * FROM Students WHERE Age < 18 OR
Age > 60;
• SELECT * FROM Students WHERE NOT Age =
25;
• Purpose: Combine multiple conditions in a
WHERE clause.

MySQL ORDER BY
• Syntax: SELECT * FROM Students ORDER BY
Age ASC/DESC;
• Example:
• SELECT * FROM Students ORDER BY Name
ASC;
• Purpose: Sort the result set by one or more
columns.

MySQL INSERT INTO
• Syntax: INSERT INTO table_name (column1,
column2) VALUES (value1, value2);
• Example:
• INSERT INTO Students (Name, Age) VALUES
('Ali', 20);
• Purpose: Add new records to a table.

MySQL NULL Values
• Check for NULL: SELECT * FROM Students
WHERE Email IS NULL;
• Purpose: Identify rows where data is missing.

MySQL UPDATE
• Syntax: UPDATE table_name SET column1 =
value1 WHERE condition;
• Example:
• UPDATE Students SET Age = 21 WHERE Name
= 'Ali';
• Purpose: Modify existing records.

MySQL DELETE
• Syntax: DELETE FROM table_name WHERE
condition;
• Example:
• DELETE FROM Students WHERE Age < 18;
• Purpose: Remove records from a table.

MySQL LIMIT
• Syntax: SELECT * FROM Students LIMIT 5;
• Purpose: Restrict the number of rows
returned.

MySQL MIN and MAX
• Syntax:
• SELECT MIN(Age) FROM Students;
• SELECT MAX(Age) FROM Students;
• Purpose: Find the smallest and largest values.

MySQL COUNT, AVG, SUM
• Syntax:
• SELECT COUNT(*) FROM Students;
• SELECT AVG(Age) FROM Students;
• SELECT SUM(Age) FROM Students;
• Purpose: Perform calculations on data.

MySQL LIKE
• Syntax: SELECT * FROM Students WHERE
Name LIKE 'A%';
• Purpose: Search for patterns in text.

MySQL Wildcards
• Use: % (any number of characters), _ (a single
character)
• Example:
• SELECT * FROM Students WHERE Name LIKE
'_li';

MySQL IN
• Syntax: SELECT * FROM Students WHERE Age
IN (18, 20, 22);
• Purpose: Match multiple values.

MySQL BETWEEN
• Syntax: SELECT * FROM Students WHERE Age
BETWEEN 18 AND 25;
• Purpose: Match values in a range.

MySQL Aliases
• Syntax: SELECT Name AS StudentName FROM
Students;
• Purpose: Rename columns or tables in output.

MySQL Joins
• Used to combine rows from two or more
tables.
• Types include INNER JOIN, LEFT JOIN, RIGHT
JOIN, CROSS JOIN, SELF JOIN.

MySQL INNER JOIN
• Syntax: SELECT * FROM Students INNER JOIN
Classes ON Students.ClassID = Classes.ID;
• Purpose: Return rows with matching values in
both tables.

MySQL LEFT JOIN
• Syntax: SELECT * FROM Students LEFT JOIN
• Purpose: Return all rows from the left table
and matched rows from the right.

MySQL RIGHT JOIN
• Syntax: SELECT * FROM Students RIGHT JOIN
• Purpose: Return all rows from the right table
and matched rows from the left.

MySQL CROSS JOIN
• Syntax: SELECT * FROM Students CROSS JOIN
Subjects;
• Purpose: Return the Cartesian product of two
tables.

MySQL Self Join
• Syntax: SELECT A.Name, B.Name FROM
Employees A, Employees B WHERE
A.ManagerID = B.ID;
• Purpose: Join a table to itself.

MySQL UNION
• Syntax: SELECT column FROM table1 UNION
SELECT column FROM table2;
• Purpose: Combine result sets of two queries.

MySQL GROUP BY
• Syntax: SELECT Age, COUNT(*) FROM Students
GROUP BY Age;
• Purpose: Group rows that have the same
values.

MySQL HAVING
• Syntax: SELECT Age, COUNT(*) FROM Students
GROUP BY Age HAVING COUNT(*) > 1;
• Purpose: Filter grouped data.

MySQL EXISTS
• Syntax: SELECT Name FROM Students WHERE
EXISTS (SELECT * FROM Classes WHERE
Students.ClassID = Classes.ID);
• Purpose: Test for existence of rows.

MySQL ANY, ALL
• ANY: SELECT * FROM Students WHERE Age >
ANY (SELECT Age FROM Graduates);
• ALL: SELECT * FROM Students WHERE Age >
ALL (SELECT Age FROM Graduates);

MySQL INSERT SELECT
• Syntax: INSERT INTO Graduates (Name, Age)
SELECT Name, Age FROM Students WHERE
Age > 22;
• Purpose: Copy data from one table to another.

MySQL CASE
• Syntax: SELECT Name, Age, CASE WHEN Age <
18 THEN 'Minor' ELSE 'Adult' END AS Category
FROM Students;
• Purpose: Apply conditional logic.

MySQL Null Functions
• Functions: IFNULL(), COALESCE()
• Example:
• SELECT IFNULL(Email, 'No Email') FROM
Students;
• Purpose: Handle NULL values.

MySQL Comments
• Single-line: -- comment
• Multi-line: /* comment */
• Purpose: Annotate SQL code for clarity.

MySQL Operators
• Types:
• - Arithmetic: +, -, *, /
• - Comparison: =, !=, <, >
• - Logical: AND, OR, NOT
• Purpose: Perform operations and comparisons
in queries.

MySQL_Advanced_Queries_Presentation.pptx

More Related Content

Similar to MySQL_Advanced_Queries_Presentation.pptx

Recently uploaded

MySQL_Advanced_Queries_Presentation.pptx