DEV Community

Cover image for SQL Mastery: Unleashing the Power of Queries
Mahabubur Rahman
Mahabubur Rahman

Posted on

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.

SQL Queries:

1. Create a Database:
To initiate a new database, the following command is utilized:

CREATE DATABASE dbname;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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)
);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Top comments (0)