SQL Cheat Sheet | Database Management System (DBMS) - Software Development PDF Download

Introduction to SQL

SQL (Structured Query Language) is a programming language used for managing and manipulating relational databases. It allows you to interact with databases to perform tasks like querying, inserting, updating, and deleting data. Here are a few important points to remember:

• SQL is a declarative language, meaning you describe what you want the database to do, and the database management system figures out how to do it.
• SQL is not case-sensitive, so "SELECT" and "select" are the same.
• SQL statements are terminated with a semicolon (;).

Retrieving Data with SELECT

The SELECT statement is used to retrieve data from a database. Here's a basic syntax example:

SELECT column1, column2, ...

FROM table_name;

• SELECT: Keyword to indicate the retrieval of data.
• column1, column2, ...: Columns you want to select from the table.
• FROM: Keyword to specify the table you want to retrieve data from.
• table_name: Name of the table.

Example: Consider a table named employees with columns id, name, age, and salary. To retrieve all columns from this table, you would use:

SELECT * FROM employees;

Output

id | name   | age | salary

---|--------|-----|-------

1  | John   | 30  | 50000

2  | Emma   | 28  | 45000

3  | Michael| 32  | 60000

Filtering Data with WHERE

The WHERE clause is used to filter data based on certain conditions. Here's the syntax:

SELECT column1, column2, ...

FROM table_name

WHERE condition;

• WHERE: Keyword to specify the condition for filtering.
• condition: The condition to filter the data.

Example: To retrieve only the employees with an age greater than 30, you would use:

SELECT * FROM employees

WHERE age > 30;

Output

id | name    | age | salary

---|---------|-----|-------

3  | Michael | 32  | 60000

Sorting Data with ORDER BY

The ORDER BY clause is used to sort the result set in ascending or descending order. Here's the syntax:

SELECT column1, column2, ...

FROM table_name

ORDER BY column1 [ASC|DESC];

• ORDER BY: Keyword to specify the sorting order.
• column1: Column to sort the result by.
• ASC: Keyword for ascending order (default if not specified).
• DESC: Keyword for descending order.

Example: To retrieve all employees sorted by their salary in descending order, you would use:

SELECT * FROM employees

ORDER BY salary DESC;

Output

id | name    | age | salary

---|---------|-----|-------

3  | Michael | 32  | 60000

1  | John    | 30  | 50000

2  | Emma    | 28  | 45000

Aggregating Data with GROUP BY

The GROUP BY clause is used to group rows that have the same values in specified columns. It is often used with aggregate functions like COUNT, SUM, AVG, etc. Here's the syntax:

SELECT column1, aggregate_function(column2)

FROM table_name

GROUP BY column1;

• GROUP BY: Keyword to group rows.
• aggregate_function: Function used to perform calculations on grouped values.

Example: Consider a table named orders with columns order_id, customer_id, and total_amount. To find the total amount spent by each customer, you would use:

SELECT customer_id, SUM(total_amount)

FROM orders

GROUP BY customer_id;

Output

customer_id | sum

------------|-----

1           | 500

2           | 700

3           | 300

Joining Tables with JOIN

The JOIN operation is used to combine rows from two or more tables based on related columns between them. Here's an example:

SELECT column1, column2, ...

FROM table1

JOIN table2

  ON table1.column = table2.column;

• JOIN: Keyword to join tables.
• table1, table2: Tables to be joined.
• ON: Keyword to specify the join condition.

Example: Consider two tables: employees and departments, with a common column department_id. To retrieve the name of employees along with their department names, you would use:

SELECT employees.name, departments.department_name

FROM employees

JOIN departments

  ON employees.department_id = departments.department_id;

Output

name    | department_name

--------|----------------

John    | HR

Emma    | Sales

Michael | IT

Modifying Data with INSERT, UPDATE, and DELETE

SQL also allows you to modify data in a database. Here are the basic syntax examples:

INSERT:

INSERT INTO table_name (column1, column2, ...)

VALUES (value1, value2, ...);

UPDATE

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

DELETE

DELETE FROM table_name

WHERE condition;

Example: Consider a table named students with columns id and name. To insert a new student, update an existing student's name, and delete a student, you would use:

-- Insert

INSERT INTO students (id, name)

VALUES (1, 'John');

-- Update

UPDATE students

SET name = 'Emma'

WHERE id = 1;

-- Delete

DELETE FROM students

WHERE id = 1;

Sample Problems and Solutions

Here are a few sample problems along with their solutions:

Problem: Retrieve the names of all employees who earn more than $50,000.

SELECT name FROM employees WHERE salary > 50000;

Problem: Find the average salary of employees in the IT department.

SELECT AVG(salary) FROM employees WHERE department = 'IT';

Problem: Count the number of orders placed by each customer.

SELECT customer_id, COUNT(order_id) FROM orders GROUP BY customer_id;