Frequently used SQL Queries with Example

SQL Stands for “Structured Query Language”. This is a popular programming language to manage Data. SQL consists of DDL (Data Definition Language), DML (Data Manipulation Language) & DCL (Data Control Language). SQL was first appeared in 1974. That time It was developed by Donald D. Chamberlin & Raymond F. Boyce in IBM.

In 1986 the SQL was initially released. SQL is approved by both ANSI & ISO. SQL is derived from the word “SEQUEL”. In this session for absolute beginners let us share the list of frequently used SQL Queries... https://goo.gl/mGbCmE

SQL Basics: A Comprehensive Guide

Structured Query Language (SQL) is the standard language used to interact with relational databases. It allows users to create, read, retrieve, update, and delete data efficiently. SQL is essential for database management, data analysis, and backend development. This guide covers the fundamental concepts of SQL, including its syntax, key commands, and practical applications.

What is SQL?

SQL is a domain-specific language designed for managing and manipulating relational databases. Developed in the 1970s, it has become the standard for database communication. SQL enables users to define database structures, insert and modify data, and retrieve information through queries.

Key Components of SQL

1. Databases and Tables A database is a structured collection of data stored electronically. Within a database, data is organized into tables, which consist of rows (records) and columns (fields). Each table represents a specific entity (e.g., customers, orders), while columns define attributes (e.g., name, age, order date).

2. SQL Commands SQL commands are categorized into four main types:

- Data Definition Language (DDL): Commands that define database structures. 

- `CREATE`: Builds new tables or databases. 

- `ALTER`: Modifies existing tables. - `DROP`: Deletes tables or databases.

- Data Manipulation Language (DML): Commands for managing data within tables. 

- `SELECT`: Retrieves data. 

- `INSERT`: Adds new records. 

- `UPDATE`: Modifies existing records. 

- `DELETE`: Removes records.

- Data Control Language (DCL): Commands for access control. 

- `GRANT`: Provides user privileges. 

- `REVOKE`: Removes privileges.

- Transaction Control Language (TCL): Commands for transaction management. 

- `COMMIT`: Saves transactions. 

- `ROLLBACK`: Reverts changes.

Basic SQL Syntax

Creating a Table ```sql CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), hire_date DATE ); ```

Inserting Data ```sql INSERT INTO employees (employee_id, first_name, last_name, hire_date) VALUES (1, 'John', 'Doe', '2023-01-15'); ```

Retrieving Data ```sql SELECT first_name, last_name FROM employees WHERE employee_id = 1; ```

Updating Data ```sql UPDATE employees SET hire_date = '2023-02-01' WHERE employee_id = 1; ```

Deleting Data ```sql DELETE FROM employees WHERE employee_id = 1; ```

Querying Data with SELECT

The `SELECT` statement retrieves data from one or more tables. Key clauses include:

- `WHERE`: Filters records based on conditions. - `ORDER BY`: Sorts results. - `GROUP BY`: Groups rows by column values. - `HAVING`: Filters grouped data. - `JOIN`: Combines data from multiple tables.

Example: ```sql SELECT first_name, last_name, department FROM employees WHERE department = 'Sales' ORDER BY last_name; ```

Joins in SQL

Joins combine rows from two or more tables based on related columns. Common types include:

- INNER JOIN: Returns matching rows from both tables. - LEFT JOIN: Returns all rows from the left table and matched rows from the right. - RIGHT JOIN: Returns all rows from the right table and matched rows from the left. - FULL JOIN: Returns all rows when there is a match in either table.

Example: ```sql SELECT e.first_name, e.last_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id; ```

Indexes and Performance

Indexes improve query performance by speeding up data retrieval. They function like a book’s index, allowing the database to find data without scanning the entire table.

Creating an Index: ```sql CREATE INDEX idx_last_name ON employees(last_name); ```

Constraints

Constraints enforce data integrity rules:

- `PRIMARY KEY`: Uniquely identifies each record. - `FOREIGN KEY`: Ensures referential integrity. - `NOT NULL`: Prevents null values. - `UNIQUE`: Ensures all values are distinct. - `CHECK`: Validates data against a condition.

Example: ```sql CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE NOT NULL, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); ```

Transactions

Transactions ensure data consistency by grouping SQL operations into a single unit. They follow the ACID properties:

- Atomicity: All operations succeed or fail together. - Consistency: Data remains valid before and after the transaction. - Isolation: Transactions operate independently. - Durability: Completed transactions persist even after system failure.

Example: ```sql BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; UPDATE accounts SET balance = balance + 100 WHERE account_id = 2; COMMIT; ```

Views

Views are virtual tables created from SQL queries. They simplify complex queries and restrict data access for security.

Creating a View: ```sql CREATE VIEW sales_employees AS SELECT first_name, last_name FROM employees WHERE department = 'Sales'; ```

Stored Procedures

Stored procedures are precompiled SQL statements stored in the database for reuse. They improve performance and security.

Example: ```sql CREATE PROCEDURE get_employee(IN emp_id INT) BEGIN SELECT * FROM employees WHERE employee_id = emp_id; END; ```

Best Practices

1. Use meaningful table and column names. 2. Normalize databases to reduce redundancy. 3. Avoid using `SELECT *`; specify columns instead. 4. Use transactions for critical operations. 5. Optimize queries with indexes.

Conclusion

SQL is a powerful tool for managing relational databases, offering robust capabilities for data manipulation and retrieval. By mastering basic commands, joins, constraints, and transactions, users can efficiently interact with databases to support applications and data analysis. Understanding these fundamentals is the first step toward becoming proficient in SQL and leveraging its full potential in real-world scenarios.