Commands in Sql With Examples

Understanding the Fundamentals of SQL Commands

Structured Query Language (SQL) is the standard language for managing and manipulating databases. SQL commands can be categorized into several types, each serving a unique purpose in the database management system. These categories include Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL). Let’s delve into each category with examples to illustrate their use.

Data Definition Language (DDL)

DDL commands are used to define, modify, and remove database structures. The primary commands in this category are CREATE, ALTER, and DROP.

CREATE: This command is used to create a new table or database.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE
);

ALTER: This command is used to modify an existing database structure, such as adding a new column to a table.
```
ALTER TABLE Students ADD Email VARCHAR(100);
```
DROP: This command is used to delete a table or database.
```
DROP TABLE Students;
```

Data Manipulation Language (DML)

DML commands are used for managing data within tables. The most commonly used DML commands are INSERT, UPDATE, DELETE, and SELECT.

INSERT: This command is used to insert data into a table.

INSERT INTO Students (StudentID, FirstName, LastName, BirthDate)
VALUES (1, 'John', 'Doe', '1990-01-01');

UPDATE: This command is used to update existing data within a table.

UPDATE Students
SET Email = '[email protected]'
WHERE StudentID = 1;

DELETE: This command is used to remove data from a table.
```
DELETE FROM Students WHERE StudentID = 1;
```
SELECT: This command is used to retrieve data from a database.
```
SELECT * FROM Students;
```

Data Control Language (DCL)

DCL commands are used to control access to data in the database. The two main commands are GRANT and REVOKE.

GRANT: This command gives a user permission to perform specific tasks.
```
GRANT SELECT ON Students TO UserExample;
```
REVOKE: This command removes user access to the database.
```
REVOKE SELECT ON Students FROM UserExample;
```

Transaction Control Language (TCL)

TCL commands manage the changes made by DML statements. They ensure that the database remains consistent even in cases of system failures. The key TCL commands are COMMIT, ROLLBACK, and SAVEPOINT.

COMMIT: This command saves all the transactions to the database.
```
COMMIT;
```
ROLLBACK: This command undoes transactions that have not yet been committed.
```
ROLLBACK;
```
SAVEPOINT: This command creates points within groups of transactions in which to ROLLBACK.
```
SAVEPOINT SavePoint1;
ROLLBACK TO SavePoint1;
```

Advanced SQL Command Usage

Joining Tables with SQL

SQL allows for the joining of two or more tables based on a related column between them. The most common types of joins are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

INNER JOIN: Returns records that have matching values in both tables.

SELECT Students.FirstName, Students.LastName, Enrollments.CourseID
FROM Students
INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;

LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table.

SELECT Students.FirstName, Students.LastName, Enrollments.CourseID
FROM Students
LEFT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;

RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table, and the matched records from the left table.

SELECT Students.FirstName, Students.LastName, Enrollments.CourseID
FROM Students
RIGHT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;

FULL OUTER JOIN: Returns all records when there is a match in either left or right table.

SELECT Students.FirstName, Students.LastName, Enrollments.CourseID
FROM Students
FULL OUTER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;

Subqueries and Nested Queries

Subqueries, also known as nested queries, are queries within another SQL query. They can be used in various places such as SELECT, FROM, and WHERE clauses.

SELECT FirstName, LastName
FROM Students
WHERE StudentID IN (SELECT StudentID FROM Enrollments WHERE CourseID = 'CS101');

Using SQL Functions

SQL provides a variety of functions to perform calculations on data, manipulate string values, and handle dates. Functions like COUNT(), SUM(), AVG(), UPPER(), and LOWER() are widely used.

COUNT(): Returns the number of rows that matches a specified criterion.
```
SELECT COUNT(StudentID) FROM Students;
```
SUM(): Returns the total sum of a numeric column.
```
SELECT SUM(TuitionFees) FROM Enrollments;
```
AVG(): Returns the average value of a numeric column.
```
SELECT AVG(TuitionFees) FROM Enrollments;
```
UPPER() and LOWER(): Converts a string to upper-case or lower-case letters, respectively.
```
SELECT UPPER(FirstName), LOWER(LastName) FROM Students;
```

SQL Command Best Practices and Performance Tips

When working with SQL, it’s important to follow best practices to ensure efficient and secure database management. Here are some tips:

Use explicit column names instead of * in SELECT statements for better performance.
Always back up your database before performing DDL operations.
Make use of indexing to speed up queries on large tables.
Regularly update statistics on your database to help the SQL server query optimizer make better decisions.
Avoid using functions on columns in the WHERE clause as it can prevent the use of indexes.
Be cautious with JOIN operations, as they can significantly impact performance if not used properly.
Use transactions and COMMIT/ROLLBACK to maintain data integrity.
Implement proper security measures, such as using DCL commands to control access to sensitive data.

Frequently Asked Questions

What is the difference between TRUNCATE and DELETE?

TRUNCATE is a DDL command that removes all rows from a table without logging the individual row deletions. DELETE is a DML command that removes rows one at a time and records an entry in the transaction log for each deleted row.

Can you use multiple tables in a single SELECT query?

Yes, you can retrieve data from multiple tables in a single SELECT query using various types of joins or subqueries.

How do you prevent SQL injection?

To prevent SQL injection, always validate user input, use parameterized queries or prepared statements, and employ stored procedures when possible. Additionally, limit database permissions and avoid displaying detailed error messages to users.

What is a primary key?

A primary key is a column or a set of columns that uniquely identifies each row in a table. It must contain unique values and cannot contain NULL values.

What is normalization in SQL?

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing a database into two or more tables and defining relationships between the tables.