In a Database Management System (DBMS), query operations are actions or commands that retrieve, manipulate, or manage data stored in a database. These operations are crucial for interacting with the database and extracting meaningful information. Here are some common query operations in DBMS:
SELECT Operation:
The SELECT operation is used to retrieve data from one or more tables in the database.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
INSERT Operation:
The INSERT operation is used to add new records (rows) to a table.
Syntax:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
UPDATE Operation:
The UPDATE operation is used to modify existing records in a table.
Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
DELETE Operation:
The DELETE operation is used to remove records from a table based on a specified condition.
Syntax:
DELETE FROM table_name
WHERE condition;
JOIN Operation:
The JOIN operation is used to combine rows from two or more tables based on a related column between them.
Syntax:
SELECT column1, column2, ...
FROM table1
JOIN table2 ON table1.column_name = table2.column_name;
GROUP BY Operation:
The GROUP BY operation is used to group rows based on the values in one or more columns, often used with aggregate functions like SUM, AVG, COUNT, etc.
Syntax:
SELECT column1, COUNT(column2), ...
FROM table_name
GROUP BY column1;
ORDER BY Operation:
The ORDER BY operation is used to sort the result set of a query based on one or more columns, either in ascending (ASC) or descending (DESC) order.
Syntax:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 ASC, column2 DESC;
DISTINCT Operation:
The DISTINCT operation is used to retrieve unique values from a specified column in a result set.
Syntax:
SELECT DISTINCT column_name
FROM table_name;
SUBQUERY Operation:
A subquery is a query nested inside another query, often used within WHERE or HAVING clauses to perform operations on the results of a query.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE column1 IN (SELECT column1 FROM another_table WHERE condition);