SQL Mastery: Unleashing the Power of Queries

Introduction to SQL:

SQL, or Structured Query Language, serves as the primary means of communication with relational databases. It offers a standardized syntax for managing and querying data, facilitating efficient data retrieval, modification, and maintenance.

Basic SQL Queries:

1. Create a Database:

To initiate a new database, the following command is utilized:

CREATE DATABASE dbname;        

This command creates a new database with the specified name.

2. Delete a Database:

To remove an existing database from the system, the following command is executed:

DROP DATABASE dbname;        

This command permanently deletes the specified database and its associated data.

3. Create a Table:

Tables are fundamental structures for organizing data. Here's how to create one:

CREATE TABLE Person (
    id INT,
    name VARCHAR(255),
    address VARCHAR(255)
);        

This command creates a table named "Person" with columns for ID, name, and address.

4. Delete a Table:

If a table is no longer needed, it can be deleted using:

DROP TABLE tablename;        

This command removes the specified table from the database schema.

5. Insert Data into a Table:

To add records into a table, the following command is employed:

INSERT INTO Person (id, name, address)
VALUES (1, 'Tony Stark', 'New York');        

This command inserts a new record into the "Person" table with the provided values.

6. Retrieve All Data:

To fetch all records from a table, the SELECT statement is used:

SELECT * FROM Person;        

This command retrieves all rows and columns from the "Person" table.

7. Edit Table Data:

To modify existing data within a table, the UPDATE statement is utilized:

UPDATE Person SET name = 'Thor' WHERE id = 1;        

This command updates the name of the person with ID 1 to 'Thor'.

8. Delete Table Data:

To remove specific records from a table, the DELETE statement is employed:

DELETE FROM Person WHERE id = 1;        

This command deletes the record with ID 1 from the "Person" table.

9. Select Specific Columns:

Instead of retrieving all columns, you can specify which columns to retrieve using the SELECT statement:

SELECT name, address FROM Person;        

This command retrieves only the 'name' and 'address' columns from the "Person" table.

10. Filter Data with WHERE Clause:

You can apply conditions to filter the data using the WHERE clause:

SELECT * FROM Person WHERE address = 'New York';        

This command fetches all records from the "Person" table where the address is 'New York'.

11. Order Results with ORDER BY:

You can sort the retrieved data in ascending or descending order using the ORDER BY clause:

SELECT * FROM Person ORDER BY name ASC;        

This command sorts the records in the "Person" table alphabetically by name in ascending order.

12. Limit the Number of Results:

To limit the number of records returned, you can use the LIMIT clause:

SELECT * FROM Person LIMIT 5;        

This command restricts the output to the first 5 records from the "Person" table.

13. Group Data with GROUP BY:

You can group rows that have the same values into summary rows using the GROUP BY clause:

SELECT address, COUNT(*) FROM Person GROUP BY address;        

This command counts the number of people in each unique address from the "Person" table.

14. Calculate Aggregate Functions:

You can perform calculations on sets of values using aggregate functions like COUNT(), SUM(), AVG(), MIN(), MAX():

SELECT COUNT(*) FROM Person;        

This command counts the total number of records in the "Person" table.

15. Join Tables:

To combine rows from two or more tables based on a related column between them, you can use the JOIN clause:

SELECT * FROM Person INNER JOIN Orders ON Person.id = Orders.person_id;        

This command retrieves all records from the "Person" table that have matching records in the "Orders" table based on the common 'person_id' column.

16. Use Aliases for Tables and Columns:

You can use aliases to provide temporary names for tables and columns:

SELECT p.id AS person_id, p.name AS person_name, o.order_id
FROM Person p
JOIN Orders o ON p.id = o.person_id;        

This command uses aliases 'p' for 'Person' table and 'o' for 'Orders' table, providing clearer and more concise references.

17. Filter Results with HAVING Clause:

Similar to WHERE clause but used with GROUP BY for filtering group rows:

SELECT address, COUNT(*) as count
FROM Person
GROUP BY address
HAVING count > 1;        

This command filters addresses having more than one person residing.

18. Use Subqueries:

Subqueries allow embedding one query within another query:

SELECT name, address
FROM Person
WHERE id IN (SELECT person_id FROM Orders WHERE total_amount > 1000);        

This command retrieves names and addresses of people who have placed orders with a total amount greater than 1000.

19. Perform Joins with Different Types:

Besides INNER JOIN, you can use OUTER JOINs (LEFT JOIN, RIGHT JOIN, FULL JOIN) to include unmatched rows from one or both tables:

SELECT p.id, p.name, o.order_id
FROM Person p
LEFT JOIN Orders o ON p.id = o.person_id;        

This command retrieves all records from the "Person" table and matching records from the "Orders" table, if any.

20. Use CASE Statements for Conditional Logic:

CASE statements provide conditional logic within SQL queries:

SELECT id, name,
CASE
    WHEN address = 'New York' THEN 'East'
    WHEN address = 'Los Angeles' THEN 'West'
    ELSE 'Other'
END AS region
FROM Person;        

This command categorizes people based on their address into 'East', 'West', or 'Other' regions.

21. Perform Aggregate Functions with DISTINCT:

You can apply aggregate functions on distinct values:

SELECT COUNT(DISTINCT address) AS unique_addresses
FROM Person;        

22. Utilize Window Functions:

Window functions perform calculations across a set of rows:

SELECT name, address, SUM(total_amount) OVER (PARTITION BY address) AS total_spent
FROM Person
JOIN Orders ON Person.id = Orders.person_id;        

This command calculates the total amount spent by each person within their respective addresses.

23. Perform Cross Joins:

Cross join returns the Cartesian product of the sets of records from the two or more joined tables:

SELECT p.name, o.order_id
FROM Person p
CROSS JOIN Orders o;