Scribd
Upload
28 views5 pages

Comprehensive MySQL Queries

The document contains a comprehensive set of SQL commands covering Data Definition Language (DDL), Data Manipulation Language (DML), Data Query Language (DQL), Transaction Control Language (TCL), and Data Control Language (DCL). It includes examples for creating, altering, and dropping tables, as well as inserting, updating, and deleting data. Additionally, it demonstrates various querying techniques, including joins, subqueries, window functions, and aggregate functions.

Uploaded by

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

Comprehensive MySQL Queries

The document contains a comprehensive set of SQL commands covering Data Definition Language (DDL), Data Manipulation Language (DML), Data Query Language (DQL), Transaction Control Language (TCL), and Data Control Language (DCL). It includes examples for creating, altering, and dropping tables, as well as inserting, updating, and deleting data. Additionally, it demonstrates various querying techniques, including joins, subqueries, window functions, and aggregate functions.

Uploaded by

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

-- 1.

DDL: Create Table


CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(10,2),
department VARCHAR(50),
hire_date DATE
);

-- 2. DDL: Alter Table (Add Column)


ALTER TABLE employees ADD email VARCHAR(100);

-- 3. DDL: Drop Table


DROP TABLE employees;

-- 4. DDL: Create Index


CREATE INDEX idx_salary ON employees(salary);

-- 5. DDL: Drop Index


DROP INDEX idx_salary ON employees;

-- 6. DML: Insert Data


INSERT INTO employees (employee_id, first_name, last_name, salary, department,
hire_date)
VALUES (1, 'John', 'Doe', 75000.00, 'IT', '2023-01-15');

-- 7. DML: Insert Multiple Rows


INSERT INTO employees (employee_id, first_name, last_name, salary, department,
hire_date)
VALUES
(2, 'Jane', 'Smith', 80000.00, 'HR', '2022-06-20'),
(3, 'Alice', 'Brown', 80000.00, 'IT', '2023-03-10');

-- 8. DML: Update Data


UPDATE employees
SET salary = salary + 5000
WHERE department = 'IT';

-- 9. DML: Delete Data


DELETE FROM employees
WHERE employee_id = 1;

-- 10. DQL: Basic Select


SELECT * FROM employees;

-- 11. DQL: Select with Condition


SELECT first_name, last_name, salary
FROM employees
WHERE salary > 70000;

-- 12. DQL: Order By


SELECT * FROM employees
ORDER BY salary DESC;

-- 13. DQL: Group By with Aggregate Functions


SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
-- 14. DQL: Having Clause
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;

-- 15. DQL: Inner Join


SELECT e.employee_id, e.first_name, e.department, d.dept_name
FROM employees e
INNER JOIN departments d ON e.department = d.dept_id;

-- 16. DQL: Left Join


SELECT e.employee_id, e.first_name, e.department, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.department = d.dept_id;

-- 17. DQL: Right Join


SELECT e.employee_id, e.first_name, e.department, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.department = d.dept_id;

-- 18. DQL: Full Join (Emulated in MySQL)


SELECT e.employee_id, e.first_name, e.department, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.department = d.dept_id
UNION
SELECT e.employee_id, e.first_name, e.department, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.department = d.dept_id;

-- 19. DQL: Subquery in WHERE


SELECT *
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

-- 20. DQL: Subquery in FROM


SELECT *
FROM (
SELECT employee_id, first_name, salary
FROM employees
WHERE department = 'IT'
) AS it_employees;

-- 21. DQL: Correlated Subquery


SELECT *
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department = e.department
);

-- 22. DQL: ROW_NUMBER() Window Function


SELECT *,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

-- 23. DQL: RANK() Window Function


SELECT *,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

-- 24. DQL: DENSE_RANK() Window Function


SELECT *,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS
salary_rank
FROM employees;

-- 25. DQL: NTILE() Window Function


SELECT *,
NTILE(4) OVER (ORDER BY salary) AS salary_quartile
FROM employees;

-- 26. DQL: LAG() Window Function


SELECT *,
LAG(salary) OVER (PARTITION BY department ORDER BY hire_date) AS prev_salary
FROM employees;

-- 27. DQL: LEAD() Window Function


SELECT *,
LEAD(salary) OVER (PARTITION BY department ORDER BY hire_date) AS
next_salary
FROM employees;

-- 28. DQL: Common Table Expression (CTE)


WITH high_salary AS (
SELECT *
FROM employees
WHERE salary > 70000
)
SELECT * FROM high_salary;

-- 29. DQL: CASE Statement


SELECT first_name, salary,
CASE
WHEN salary > 80000 THEN 'High'
WHEN salary > 60000 THEN 'Medium'
ELSE 'Low'
END AS salary_category
FROM employees;

-- 30. DQL: LIKE Operator


SELECT * FROM employees
WHERE first_name LIKE 'J%';

-- 31. DQL: IN Operator


SELECT * FROM employees
WHERE department IN ('IT', 'HR');

-- 32. DQL: BETWEEN Operator


SELECT * FROM employees
WHERE salary BETWEEN 60000 AND 80000;

-- 33. DQL: DISTINCT


SELECT DISTINCT department
FROM employees;
-- 34. DQL: LIMIT and OFFSET
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 2 OFFSET 1;

-- 35. DQL: UNION


SELECT employee_id, first_name FROM employees WHERE department = 'IT'
UNION
SELECT employee_id, first_name FROM employees WHERE department = 'HR';

-- 36. DQL: UNION ALL


SELECT employee_id, first_name FROM employees WHERE department = 'IT'
UNION ALL
SELECT employee_id, first_name FROM employees WHERE department = 'HR';

-- 37. DQL: EXISTS


SELECT *
FROM employees e
WHERE EXISTS (
SELECT 1
FROM departments d
WHERE d.dept_id = e.department AND d.budget > 100000
);

-- 38. DQL: ANY/ALL


SELECT *
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE department = 'HR'
);

-- 39. TCL: Start Transaction


START TRANSACTION;

-- 40. TCL: Commit


COMMIT;

-- 41. TCL: Rollback


ROLLBACK;

-- 42. TCL: Savepoint


SAVEPOINT savepoint1;

-- 43. TCL: Rollback to Savepoint


ROLLBACK TO SAVEPOINT savepoint1;

-- 44. DCL: Grant Privileges


GRANT SELECT, INSERT ON employees TO 'user'@'localhost';

-- 45. DCL: Revoke Privileges


REVOKE SELECT, INSERT ON employees FROM 'user'@'localhost';

-- 46. DQL: Date Functions


SELECT first_name, hire_date,
DATEDIFF(CURDATE(), hire_date) AS days_employed
FROM employees;
-- 47. DQL: String Functions
SELECT first_name, UPPER(last_name) AS last_name_upper
FROM employees;

-- 48. DQL: Aggregate Functions


SELECT department,
SUM(salary) AS total_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY department;

-- 49. DQL: COALESCE


SELECT first_name, COALESCE(email, 'No Email') AS email
FROM employees;

-- 50. DQL: Self Join


SELECT e1.first_name, e2.first_name AS colleague
FROM employees e1
JOIN employees e2 ON e1.department = e2.department AND e1.employee_id !=
e2.employee_id;

-- 51. DQL: GROUP_CONCAT


SELECT department,
GROUP_CONCAT(first_name) AS employees
FROM employees
GROUP BY department;

-- 52. DQL: JSON Functions


SELECT JSON_OBJECT('id', employee_id, 'name', first_name) AS json_data
FROM employees;

-- 53. DQL: Subquery with Highest Salary per Department


SELECT *
FROM employees e1
INNER JOIN (
SELECT department, MAX(salary) AS max_salary
FROM employees
GROUP BY department
) e2
ON e1.department = e2.department AND e1.salary = e2.max_salary;

-- 54. DQL: ROW_NUMBER() for Specific Rows


SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (ORDER BY id) AS rn
FROM Employee
) AS numbered
WHERE rn IN (2, 3, 7);

-- 55. DQL: First and Last Row


SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (ORDER BY id) AS rn
FROM Employee
) AS numbered
WHERE rn = 1 OR rn = (SELECT COUNT(*) FROM Employee);

You might also like