SQL Cheat Sheet

SQL stands for Structured Query Language. It’s a powerful and widely-used language specifically designed for managing data stored in relational databases.

There are several situations where using SQL directly might be preferable to using an Object-Relational Mapper (ORM)

Performance

• Complex Queries: While ORMs simplify complex queries, they often translate them into multiple underlying SQL statements. For very intricate queries, writing raw SQL can give you more control and potentially improve performance.
• Database-Specific Features: ORMs sometimes have limitations when dealing with functionalities specific to a particular database engine. If you need to leverage advanced features offered by your database (like stored procedures or custom functions), using raw SQL allows you to interact with them directly.

Customization and Control

• Fine-Tuning Queries: SQL grants you complete control over the query structure. You can optimize queries for specific use cases and databases, something that might be limited with an ORM’s abstraction layer.
• Database Schema Changes: If your database schema undergoes frequent modifications, relying solely on an ORM might require frequent updates to your object mappings. With SQL, you can directly adapt your queries to the evolving schema.

Troubleshooting

• Error Diagnostics: When errors occur with ORM-generated queries, it can be challenging to pinpoint the exact cause. With raw SQL, you can directly see the query being executed, making it easier to diagnose and fix issues.
• Understanding Database Behavior: For developers who want a deeper understanding of how the database interprets and executes queries, using SQL provides greater transparency into the underlying processes.

Basics

Here are the basic SQL commands that form the foundation for interacting with relational databases:

1. SELECT: This is the most fundamental command used to retrieve data from a database table. You can specify the columns (fields) you want to retrieve and optionally use a WHERE clause to filter the results based on certain conditions. Here’s an example:

SELECT name, email FROM users WHERE country = 'GR';

This query selects the name and email columns from the users table, but only for users where the country is ‘GR’.

2. INSERT: This command allows you to insert new data into a table. You specify the table name and the values you want to insert for each column.

Here’s an example:

INSERT INTO products (name, price, category) VALUES ('shirt', 9.99, 'clothing');

This query inserts a new row into the products table with the following details:

• name: shirt
• price: 9.99
• category: clothing

3. UPDATE: This command is used to modify existing data in a table. You specify the table name, the changes you want to make, and a WHERE clause to target specific rows for the update.

Here’s an example:

UPDATE customers SET email = 'new_email@example.com' WHERE id = 10;

This query updates the email address for the customer with id equal to 10 in the customers table.

4. DELETE: This command allows you to remove rows from a table. Similar to UPDATE, you can use a WHERE clause to filter the rows you want to delete.

Here’s an example:

DELETE FROM orders WHERE status = 'completed' AND order_date < '2024-01-01';

This query deletes all orders from the orders table that are marked as “completed” and have an order date before January 1st, 2024.

These are just a few basic SQL commands to get you started. As you learn more, you’ll discover commands for more complex tasks like joining data from multiple tables, grouping results, and using aggregate functions.

Check these official documentation resources for more in-depth information on SQL:

PostgreSQL Documentation

MySQL 8.0 Reference Manual

SQLite Documentation

JOINs

JOINs are a fundamental concept in relational databases that allow you to combine data from multiple tables based on a shared field. They are essential for retrieving related information across different tables.

Here’s a breakdown of the basics about JOINs:

• Purpose: JOINs enable you to retrieve data from multiple tables in a single query, establishing relationships between them. This eliminates the need to write separate queries and then manually combine the results.
• Types of JOINs: There are several types of JOINs, each serving a specific purpose:
  • Inner JOIN: This retrieves records that exist in both tables based on the matching criteria. It’s the most common type of JOIN.
  • Left JOIN: This includes all records from the left table (the table mentioned first in the JOIN clause) and matching records from the right table. Records from the right table with no match will have NULL values in the corresponding columns.
  • Right JOIN: Similar to Left JOIN, but includes all records from the right table and matching records from the left table.
  • Full JOIN: This combines all records from both tables, including those with no match in the other table (represented by NULL values).
• Example:

Imagine you have two tables:

• Customers: Contains customer information like ID, name, and email.
• Orders: Stores order details like order ID, customer ID (foreign key referencing Customers.ID), and order amount.

To retrieve a list of customer names along with their total order amount, you can use an Inner JOIN:

SELECT c.name, SUM(o.order_amount) AS total_order_amount
FROM Customers c
INNER JOIN Orders o ON c.id = o.customer_id
GROUP BY c.name;

Explanation:

• SELECT: This specifies the columns you want to retrieve.
• c.name: Selects the customer name from the Customers table (aliased as c).
• SUM(o.order_amount) AS total_order_amount: Calculates the total order amount for each customer by summing the order_amount from the Orders table (aliased as o).
• FROM Customers c: Specifies the Customers table as the source for customer data.
• INNER JOIN Orders o ON c.id = o.customer_id: Defines the JOIN operation.
  • INNER JOIN: This indicates an Inner JOIN.
  • Orders o: The Orders table is joined.
  • ON c.id = o.customer_id: This is the JOIN condition. It matches rows where the customer ID (c.id) in the Customers table is equal to the customer ID (o.customer_id) in the Orders table.
• GROUP BY c.name: Groups the results by customer name for accurate total order amount calculation.

This query will return a list of customer names and their corresponding total order amounts, effectively combining data from both tables using the JOIN.

TIPS

• Use Comments: SQL supports comments to document your queries. Use -- for single-line comments and /* */ for multi-line comments.

• Avoid SELECT * : When writing queries, avoid using SELECT * to retrieve all columns from a table. Instead, explicitly list the columns you need. This practice improves query performance and makes your code more maintainable.

• Use Transactions: When performing multiple operations that need to be executed together (like inserting data into multiple tables), wrap them in a transaction. This ensures that either all operations succeed or none of them are applied.

• Backup Your Data: Regularly back up your database to prevent data loss. Most database management systems provide tools or utilities to create backups.

• Optimize Queries: Use database tools to analyze query performance and optimize slow queries. Techniques like indexing, query caching, and query tuning can significantly improve database performance.

• Practice Safe SQL: Protect your database from SQL injection attacks by using parameterized queries or prepared statements. These techniques help prevent malicious code injection through user input.

Snippets

Check this SQL query that generates statements to convert tables in a database schema to the character set utf8mb4 with the collation utf8mb4_unicode_ci.

SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_statement
FROM information_schema.tables
WHERE table_schema = 'SCHEMA';

Here’s a SQL query that retrieves information about tables in your database and displays their estimated sizes using informational tables:

SELECT TABLE_SCHEMA AS `Database`,
       TABLE_NAME AS `Table`,
       ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS `Size (MB)`
FROM information_schema.TABLES;

Backing Up and Restoring Databases

Backing up your database is crucial to prevent data loss in case of hardware failures, accidental deletions, or other disasters. Here’s how you can back up and restore databases using SQL commands:

Backing Up a Database

To back up a database, you can use the mysqldump utility. Here’s an example command to back up a MySQL database:

mysqldump -u username -p database_name > backup.sql

This command exports the database database_name to a file named backup.sql. You’ll be prompted to enter the password for the specified user (username).

Restoring a Database

To restore a database from a backup file, you can use the mysql command. Here’s an example command to restore a MySQL database:

mysql -u username -p database_name < backup.sql

or you can source the backup file directly in the MySQL shell:

source backup.sql

PostgreSQL Backup and Restore Options:

PostgreSQL offers several methods for backing up and restoring databases. Here’s an overview of the key tools:

• pg_dump and pg_restore: These are the primary command-line utilities for managing backups.
  • pg_dump creates consistent backups of your database, including the schema and data. It’s suitable for both full and partial backups.
  • pg_restore is used to restore databases from backups created with pg_dump. It allows for restoring entire databases or specific objects.
• psql: While not the primary tool for full database restores, psql can be used to execute individual SQL statements from a backup script containing CREATE TABLE and INSERT statements, offering some restore flexibility.
• pg_basebackup: This is a powerful utility specifically designed for creating online backups of PostgreSQL databases. It creates a consistent physical copy of the database cluster’s data files while the server remains operational. This allows for minimal downtime during backup processes and is often used for replication setups.

Choosing the Right Tool:

The best backup approach depends on your specific needs. Here’s a general guideline:

• Full Backups: Use pg_dump for scheduled full backups or pg_basebackup for online backups with minimal downtime.
• Partial Backups: Use pg_dump to target specific schemas or tables.
• Restores: Use pg_restore for full database restores or psql for limited restores involving specific SQL statements.