SQL provides a powerful and structured way to work with relational databases. It can be used to create databases and tables, store data, retrieve information, modify records, control access, and manage transactions.
SQL uses readable keywords such as SELECT, FROM, WHERE, INSERT, UPDATE, and DELETE. This makes basic SQL statements relatively easy to understand.
SELECT name
FROM students
WHERE course = 'Python';SQL can retrieve specific information from one or more database tables.
SELECT name, email
FROM students;The SELECT statement allows you to specify the columns that you want to retrieve.
SQL provides commands for adding, changing, and removing data.
INSERT INTO students (name)
VALUES ('Rahul');
UPDATE students
SET name = 'Amit'
WHERE id = 1;
DELETE FROM students
WHERE id = 2;These operations are commonly referred to as data manipulation.
SQL includes statements for defining database objects such as tables.
CREATE TABLE students (
id INT,
name VARCHAR(100)
);Commands such as CREATE, ALTER, and DROP are used to define or change database structures.
The WHERE clause allows you to filter records according to specified conditions.
SELECT *
FROM students
WHERE age >= 18;Only records satisfying the condition are returned.
SQL can sort query results using ORDER BY.
SELECT *
FROM students
ORDER BY name ASC;You can sort data in ascending or descending order.
ORDER BY name ASC;
ORDER BY name DESC;SQL provides aggregate functions for calculations on groups of rows.
Common aggregate functions include:
SELECT COUNT(*)
FROM students;The GROUP BY clause groups rows based on one or more columns.
SELECT course, COUNT(*)
FROM students
GROUP BY course;This can be useful for generating summaries and reports.
SQL can combine related information stored in different tables using joins.
SELECT students.name, courses.course_name
FROM students
INNER JOIN courses
ON students.course_id = courses.id;Common joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN where supported.
SQL constraints help enforce rules on data stored in tables.
Common constraints include:
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);SQL constraints and relationships can help maintain the consistency and integrity of data.
For example, a foreign key can represent a relationship between a child table and a referenced parent table.
CREATE TABLE enrollments (
student_id INT,
course_id INT,
FOREIGN KEY (student_id)
REFERENCES students(id)
);A subquery is a query written inside another SQL statement.
SELECT name
FROM students
WHERE age > (
SELECT AVG(age)
FROM students
);Subqueries can be used to perform more advanced data retrieval.
A view is a database object based on a query. It can provide a convenient way to access a selected set of data.
CREATE VIEW student_view AS
SELECT name, course
FROM students;You can query the view like a table in supported database systems.
Indexes can help a database find rows more efficiently for suitable queries.
CREATE INDEX idx_student_name
ON students(name);Indexes can improve read performance, but they also require storage and can add overhead to data modifications.
Transactions allow a group of database operations to be treated as a unit of work.
START TRANSACTION;
UPDATE accounts
SET balance = balance - 500
WHERE id = 1;
UPDATE accounts
SET balance = balance + 500
WHERE id = 2;
COMMIT;Transaction behavior and exact commands can vary between database systems.
COMMIT makes the changes of a transaction permanent, while ROLLBACK can undo changes made during a transaction before they are committed, subject to the database system's transaction rules.
START TRANSACTION;
UPDATE students
SET course = 'Python'
WHERE id = 1;
ROLLBACK;Database systems can provide mechanisms for controlling who can access databases and what operations they can perform.
Depending on the database system, SQL-related access-control statements can be used to grant or revoke privileges.
GRANT SELECT
ON students
TO some_user;The exact syntax and privilege model depends on the database system.
SQL has standardized syntax that provides a common foundation across many relational database systems.
However, database products may support different features, functions, data types, and extensions.
Therefore, SQL code may sometimes need changes when moved between different database systems.
Relational database systems can store and query large collections of structured data. SQL provides features for filtering, grouping, joining and aggregating that data.
SELECT course, COUNT(*) AS total_students
FROM students
GROUP BY course;Database performance also depends on database design, indexes, queries, hardware, configuration and the specific database system.
SQL is frequently used together with programming languages.
For example:
The application sends database operations to a database system and then processes the returned results.
SQL can be used to generate reports by combining filtering, aggregation, grouping and sorting.
SELECT course,
COUNT(*) AS students,
AVG(marks) AS average_marks
FROM students
GROUP BY course;This can produce summarized information useful for applications and business reporting.
| Operation | Common SQL Statement |
|---|---|
| Create structure | CREATE |
| Add data | INSERT |
| Read data | SELECT |
| Modify data | UPDATE |
| Remove data | DELETE |
| Change structure | ALTER |
| Remove database objects | DROP |
SQL is primarily declarative. You generally describe the result or operation you want, while the database system determines how to execute the request.
SELECT name
FROM students
WHERE course = 'Python';The query describes which rows and columns are required without specifying the database's internal execution steps.
Relational databases can represent relationships between tables.
students
|
| student_id
↓
feesKeys such as primary keys and foreign keys can be used to represent relationships between related tables.
SQL is an important skill for many technology roles involving databases and data.
Question: Which SQL clause is used to filter rows according to a condition?