Day 38: SQL Command Types for PM | SQL Commands | Operators | Sorting | Limiting

Today, we dive into the various SQL command types and their significance. SQL commands are categorized based on the kind of operation they perform, and understanding these types is crucial for efficient database management. The operations performed in SQL are divided into five major command types:

1. Data Definition Language (DDL)

DDL commands define, modify, and delete the structure of database objects like tables, indexes, and constraints. These commands are focused on the structure, not the data itself.

Examples:

• CREATE: Creates new tables or databases.
• ALTER: Modifies the structure of an existing database object.
• DROP: Deletes a table or a database.
• TRUNCATE: Deletes all records in a table without deleting the table structure.

2. Data Manipulation Language (DML)

DML commands manage the data stored in schema objects, allowing you to insert, update, or delete records.

Examples:

• INSERT: Adds new records to a table.
• UPDATE: Modifies existing records.
• DELETE: Removes records from a table.

3. Data Control Language (DCL)

DCL commands control access to the data in the database by managing user rights and permissions.

Examples:

• GRANT: Provides access privileges.
• REVOKE: Removes granted access privileges.

4. Transaction Control Language (TCL)

TCL commands manage transactions in SQL, ensuring the integrity and consistency of data.

Examples:

• COMMIT: Saves all the transactions to the database.
• ROLLBACK: Restores the database to the last committed state.
• SAVEPOINT: Sets a specific point within a transaction to which you can roll back.

5. Data Query Language (DQL)

DQL primarily uses one command, SELECT, which retrieves data from databases. It is key for querying and fetching records.

DDL in Detail: The ALTER Command

The ALTER command allows changes to the structure of a table, such as modifying column names, updating data types, adding or removing columns.

Examples:

1. Changing a column name:

ALTER TABLE table_name CHANGE COLUMN old_column new_column data_type;        

2. Modifying data type or constraints:

ALTER TABLE table_name MODIFY COLUMN existing_column_name data_type constraint;        

3. Adding a new column:

ALTER TABLE table_name ADD COLUMN new_column_name data_type constraints;        

4. Dropping a column:

ALTER TABLE table_name DROP COLUMN column_name;        

DDL - DROP & TRUNCATE

• DROP: Removes an entire table along with all its data.

DROP TABLE table_name;        

• TRUNCATE: Removes all rows from a table, but keeps its structure intact.

TRUNCATE TABLE table_name;        

Arithmetic Operators in SQL

SQL supports basic arithmetic operations like addition, subtraction, multiplication, division, and modulus, which are useful in queries for data analysis.

• Addition:

SELECT (column1 + column2) FROM table_name;        

• Subtraction:

SELECT (column1 - column2) FROM table_name;        

• Multiplication:

SELECT (column1 * column2) FROM table_name;        

• Division:

SELECT (column1 / column2) FROM table_name;        

• Modulus (returns the remainder when dividing):

SELECT (column1 % column2) FROM table_name;        

Arithmetic Operators With WHERE Clause

• Addition:

SELECT * FROM table_name WHERE column1 + column2 = column_value;        

• Subtraction:

SELECT * FROM table_name WHERE column1 - column2 = column_value;        

• Multiplication:

SELECT  FROM table_name WHERE column1  column2 < column_value;        

• Division:

SELECT * FROM table_name WHERE column1 / column2 = column_value;        

• Modulus:

SELECT * FROM table_name WHERE column1 % 2 = column_value;        

Comparison Operators in SQL

Comparison operators allow you to filter data based on conditions.

• Equal:

SELECT * FROM employees WHERE department = 'Sales';        

• Not Equal:

SELECT * FROM employees WHERE salary <> 50000;        

• Greater Than:

SELECT * FROM employees WHERE age > 30;        

• Less Than:

SELECT * FROM employees WHERE age < 25;        

• Between:

SELECT * FROM employees WHERE salary BETWEEN 30000 AND 60000;        

• Is NULL:

SELECT * FROM employees WHERE department IS NULL;        

• Is NOT NULL:

SELECT * FROM employees WHERE department IS NOT NULL;        

Logical Operators in SQL

Logical operators such as AND, OR, and NOT allow you to combine conditions.

• AND:

SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000;        

• OR:

SELECT * FROM employees WHERE department = 'Sales' OR department = 'Marketing';        

• NOT:

SELECT * FROM employees WHERE NOT department = 'Sales';        

• IN:

SELECT * FROM employees WHERE department IN ('Sales', 'Marketing');        

Sorting in SQL

The ORDER BY clause helps sort results in ascending or descending order. You can also sort by multiple columns.

• Sort by Salary in Ascending Order:

SELECT * FROM employees ORDER BY salary ASC;        

• Sort by Salary in Descending Order:

SELECT * FROM employees ORDER BY salary ASC;        

LIMIT and OFFSET in SQL

• LIMIT: Limits the number of rows returned by a query.

SELECT * FROM employees LIMIT 10;        

• OFFSET: Skips a certain number of rows before returning the result set.

SELECT * FROM employees LIMIT 10;        

DISTINCT in SQL

The DISTINCT keyword ensures that duplicate rows are eliminated from the result set.

Examples:

1. Unique Brands of Mobile Sales:

SELECT DISTINCT Brand FROM flipkart_mobiles;        

2. Unique Directors in Netflix Movies:

SELECT DISTINCT director FROM netflix;        

Putting It All Together

Let’s apply these concepts in a practical example:

• Fetching discounted products from BigBasket:

SELECT Product, (market_price - sale_price) AS Discount_price 
FROM bigbasketproducts 
WHERE (market_price - sale_price) > 50;        

• Sorting mobile data with conditions:

SELECT *, (`Original Price` - `Selling Price`) 
FROM flipkart_mobiles 
WHERE Brand = "OPPO" AND `Original Price` > 15000 
AND (Rating > 4.5 OR (`Original Price` - `Selling Price`) > 5000)
ORDER BY `Selling Price` ASC LIMIT 5;        

Conclusion:

Mastering these SQL command types, arithmetic, comparison, and logical operators is essential for anyone dealing with databases. Whether it's structuring the database, managing data, or retrieving specific information, SQL offers a powerful set of tools that, when used correctly, can streamline data operations and enhance performance.