What is a Query?
Query is basically use for request to database for get information and data. Normally create a query for get data from data base with certen condition and specific data related to any table.
Basically Use multiple type Query for get data from database. and same like save data , update data and delete data from database.
DBMS Query Types
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Control Language (DCL)
- Transaction Control Language (TCL)
- Join Queries
- Subqueries
- Recursive Queries
- Analytical Queries
- Search Queries
- NoSQL Queries
Description of Each Query Type with Examples
1. Data Definition Language (DDL)
DDL Query use for modify database structure.
CREATE TABLE student_detail (id INT, student_name VARCHAR(50), age INT);
ALTER TABLE student_detail ADD address VARCHAR(100);
DROP TABLE student_detail;
2. Data Manipulation Language (DML)
DML queries are used to manipulate data within tables.
INSERT INTO student_detail (id, name, age) VALUES (1, 'Alice', 20);
UPDATE student_detail SET age = 21 WHERE id = 1;
DELETE FROM student_detail WHERE id = 1;
SELECT * FROM student_detail;
3. Data Control Language (DCL)
DCL queries manage access permissions and security.
GRANT SELECT, INSERT ON students TO user1;
REVOKE SELECT ON students FROM user1;
4. Transaction Control Language (TCL)
TCL queries ensure data consistency and manage database transactions.
BEGIN TRANSACTION;
COMMIT;
ROLLBACK;
5. Join Queries
Join queries retrieve data from multiple related tables.
SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id;
6. Subqueries
Basically write a query and inner another query write this is nested and subqueries.
SELECT name FROM students
WHERE age = (SELECT MAX(age) FROM students);
7. Recursive Queries
Recursive queries are used for get hierarchical or tree-structured data.
WITH RECURSIVE employee_hierarchy AS (
SELECT employee_id, manager_id FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id
FROM employees e
INNER JOIN employee_hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM employee_hierarchy;
8. Analytical Queries
These queries are used for reporting and data analysis.
SELECT department, AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;
9. Search Queries
Search queries retrieve data based on specific conditions.
SELECT * FROM students WHERE name LIKE '%A%';
10. NoSQL Queries
NoSQL queries work with non-relational databases like MongoDB.
db.students.find({ age: { $gt: 20 } });