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;
Top comments (0)