Scribd
Upload
6 views5 pages

MySQL Interview Preparation Guide

This document is a MySQL interview preparation guide covering essential SQL commands and concepts. It includes instructions for creating databases and tables, inserting and selecting data, using clauses like WHERE and ORDER BY, and performing joins. Additionally, it outlines advanced topics such as indexing, constraints, views, stored procedures, and common interview questions related to SQL.

Uploaded by

somireddythatikonda15
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views5 pages

MySQL Interview Preparation Guide

This document is a MySQL interview preparation guide covering essential SQL commands and concepts. It includes instructions for creating databases and tables, inserting and selecting data, using clauses like WHERE and ORDER BY, and performing joins. Additionally, it outlines advanced topics such as indexing, constraints, views, stored procedures, and common interview questions related to SQL.

Uploaded by

somireddythatikonda15
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

MySQL Interview Prep Guide

1. CREATE DATABASE & USE IT


----------------------------
CREATE DATABASE CompanyDB;
USE CompanyDB;

2. CREATE TABLE
----------------
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT,
DepartmentID INT,
Salary DECIMAL(10, 2)
);

CREATE TABLE Departments (


DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);

3. INSERT DATA
--------------
INSERT INTO Employees VALUES (1, 'Alice', 30, 1, 60000.00);
INSERT INTO Employees VALUES (2, 'Bob', 28, 2, 55000.00);
INSERT INTO Departments VALUES (1, 'HR');
INSERT INTO Departments VALUES (2, 'IT');

4. SELECT DATA
--------------
SELECT * FROM Employees;
SELECT Name, Salary FROM Employees;

5. WHERE CLAUSE
----------------
SELECT * FROM Employees WHERE Age > 28;

6. ORDER BY
------------
SELECT * FROM Employees ORDER BY Salary DESC;

7. GROUP BY & HAVING


---------------------
SELECT DepartmentID, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 1;

8. UPDATE & DELETE


-------------------
UPDATE Employees SET Salary = 65000 WHERE EmpID = 1;
DELETE FROM Employees WHERE EmpID = 2;

9. JOINS
--------
INNER JOIN:
SELECT [Link], [Link]
FROM Employees e
INNER JOIN Departments d ON [Link] = [Link];

LEFT JOIN:
SELECT [Link], [Link]
FROM Employees e
LEFT JOIN Departments d ON [Link] = [Link];
RIGHT JOIN:
SELECT [Link], [Link]
FROM Employees e
RIGHT JOIN Departments d ON [Link] = [Link];

10. INDEXING
-------------
CREATE INDEX idx_dept ON Employees(DepartmentID);

11. SCHEMA
-----------
CREATE SCHEMA MySchema;

12. CONSTRAINTS
----------------
CREATE TABLE Projects (
ProjectID INT PRIMARY KEY,
ProjectName VARCHAR(100) NOT NULL,
StartDate DATE,
EndDate DATE,
UNIQUE(ProjectName)
);

13. ALIASES
------------
SELECT Name AS EmployeeName, Salary AS MonthlySalary FROM Employees;

14. SUBQUERIES
---------------
SELECT * FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
15. LIMIT CLAUSE
-----------------
SELECT * FROM Employees LIMIT 5;

16. BETWEEN, IN, LIKE


----------------------
SELECT * FROM Employees WHERE Age BETWEEN 25 AND 35;
SELECT * FROM Employees WHERE DepartmentID IN (1, 2);
SELECT * FROM Employees WHERE Name LIKE 'A%';

17. VIEWS
----------
CREATE VIEW View_EmployeeSalary AS
SELECT Name, Salary FROM Employees;

SELECT * FROM View_EmployeeSalary;

18. STORED PROCEDURE


---------------------
DELIMITER //
CREATE PROCEDURE GetAllEmployees()
BEGIN
SELECT * FROM Employees;
END //
DELIMITER ;

CALL GetAllEmployees();

19. INTERVIEW QUESTIONS


------------------------
- Difference between DELETE, TRUNCATE, DROP?
- What is normalization? 1NF, 2NF, 3NF?
- Indexes and performance benefits?
- JOIN types: INNER, LEFT, RIGHT, FULL?
- View vs Table?
- Composite Key?
- WHERE vs HAVING?
- SQL injection prevention?
- Subquery vs Correlated subquery?

You might also like