SQL Mastery: Unleashing the Power of Queries
Tags: sql, database, query, development
Published: 2024-06-05 14:47:51

## **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.

## **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;
```