Cheatsheets
SQL Basics for Back-End Development

SQL Basics for Back-End Development

Managing Data with SQL

Rules for Data Columns

Rules for columns define what kind of data each column can hold, such as making sure a value is unique or not empty.

                                
CREATE TABLE student (
  id INTEGER PRIMARY KEY,
  name TEXT UNIQUE,
  grade INTEGER NOT NULL,
  age INTEGER DEFAULT 10
);

Creating a New Table

You use the CREATE TABLE statement to make a new table in the database, specifying the columns and their types.

                                
CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  column3 datatype
);

Adding Data to a Table

The INSERT INTO statement adds new rows of data into a table. You can add values in the same order as columns or specify the columns you're adding data to.

                                
-- Add values in order:
INSERT INTO table_name
VALUES (value1, value2);
-- Add values by column names:
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

Changing Table Structure

Use ALTER TABLE to modify the structure of an existing table, such as adding a new column.

                                
ALTER TABLE table_name
ADD column_name datatype;

Removing Data from a Table

The DELETE FROM statement removes rows from a table based on a condition.

                                
DELETE FROM table_name
WHERE some_column = some_value;

Updating Existing Data

The UPDATE statement changes existing data in a table. You specify which rows to update and the new values.

                                
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE some_column = some_value;

Writing Basic SQL Queries

Combining Conditions with AND

Use the AND operator to combine multiple conditions in a query. The result will include records that meet all the conditions.

                                
SELECT model 
FROM cars 
WHERE color = 'blue' 
  AND year > 2014;

Renaming Columns with AS

The AS clause lets you rename columns or tables in the results. This is useful for making your output clearer.

                                
SELECT name AS 'movie_title'
FROM movies;

Using OR to Combine Conditions

The OR operator allows you to include records that meet any of the specified conditions.

                                
SELECT name
FROM customers 
WHERE state = 'CA' 
   OR state = 'NY';

Wildcard % for Matching Patterns

Use the % wildcard in the LIKE operator to match any number of characters in a pattern.

                                
SELECT name
FROM movies
WHERE name LIKE 'The%';

Selecting All Columns

The SELECT * statement retrieves all columns and rows from a table.

                                
SELECT *
FROM movies;

Wildcard _ for Single Characters

Use the _ wildcard to match exactly one character in a pattern.

                                
SELECT name
FROM movies
WHERE name LIKE '_ove';

Sorting Results with ORDER BY

The ORDER BY clause sorts your results by a specified column, either in ascending or descending order.

                                
SELECT *
FROM contacts
ORDER BY birth_date DESC;

Finding Patterns with LIKE

The LIKE operator helps you find records that match a specific pattern.

                                
SELECT name
FROM movies
WHERE name LIKE 'Star%';

Selecting Unique Values with DISTINCT

The DISTINCT clause filters out duplicate values, showing only unique values in your results.

                                
SELECT DISTINCT city
FROM contact_details;

Filtering by Range with BETWEEN

The BETWEEN operator helps you filter records by a range of values, such as dates or numbers.

                                
SELECT *
FROM movies
WHERE year BETWEEN 1980 AND 1990;

Limiting Results with LIMIT

The LIMIT clause restricts the number of rows returned by your query.

                                
SELECT *
FROM movies
LIMIT 5;

Handling NULL Values

Use IS NULL or IS NOT NULL to filter records with missing or present values in a column.

                                
SELECT address
FROM records
WHERE address IS NOT NULL;

Filtering Records with WHERE

The WHERE clause filters records based on a specified condition.

                                
SELECT title
FROM library
WHERE pub_year = 2017;

Performing Calculations with SQL

Referencing Columns in GROUP BY and ORDER BY

You can use column numbers to reference columns in GROUP BY and ORDER BY clauses. This allows you to group and sort data based on selected columns.

                                
SELECT COUNT(*) AS 'total_movies', 
   rating 
FROM movies 
GROUP BY 2 
ORDER BY 1;

Calculating Sum with SUM()

The SUM() function adds up all the values in a specified column.

                                
SELECT SUM(salary)
FROM salary_disbursement;

Finding Maximum Value with MAX()

The MAX() function returns the largest value from a specified column.

                                
SELECT MAX(amount) 
FROM transactions;

Counting Rows with COUNT()

The COUNT() function gives you the total number of rows that meet certain criteria.

                                
SELECT COUNT(*)
FROM employees
WHERE experience < 5;

Grouping Data with GROUP BY

The GROUP BY clause groups rows that have the same values into summary rows, often used with aggregate functions like COUNT or SUM.

                                
SELECT rating, 
   COUNT(*) 
FROM movies 
GROUP BY rating;

Finding Minimum Value with MIN()

The MIN() function returns the smallest value from a specified column.

                                
SELECT MIN(amount) 
FROM transactions;

Calculating Average with AVG()

The AVG() function calculates the average value from a column.

                                
SELECT AVG(salary)
FROM employees
WHERE experience < 5;

Filtering Groups with HAVING

The HAVING clause filters groups of rows created by GROUP BY, often used with aggregate functions to apply conditions.

                                
SELECT year, 
   COUNT(*) 
FROM movies 
GROUP BY year
HAVING COUNT(*) > 5;

Rounding Numbers with ROUND()

The ROUND() function rounds a numeric value to a specified number of decimal places.

                                
SELECT ROUND(AVG(rating), 2) 
FROM movies 
WHERE year = 2015;

Working with Multiple Tables

Combining Tables with Outer Join

An outer join combines rows from two tables even if there is no matching row in one of the tables. LEFT JOIN includes all rows from the left table and matches rows from the right table, filling with NULL where no match is found.

                                
SELECT column_name(s)
FROM table1
LEFT JOIN table2
  ON table1.column_name = table2.column_name;

Using WITH to Create Temporary Tables

The WITH clause lets you create a temporary table to store the results of a query for later use in the same query.

                                
WITH temporary_movies AS (
   SELECT *
   FROM movies
)
SELECT *
FROM temporary_movies
WHERE year BETWEEN 2000 AND 2020;

Combining Results with UNION

The UNION clause combines results from multiple SELECT statements into one result set, removing duplicate rows.

                                
SELECT name
FROM first_names
UNION
SELECT name
FROM last_names;

Creating Combinations with CROSS JOIN

The CROSS JOIN clause creates a Cartesian product of two tables, combining each row from the first table with each row from the second table.

                                
SELECT shirts.shirt_color,
   pants.pants_color
FROM shirts
CROSS JOIN pants;

Linking Tables with Foreign Key

A foreign key is a column that creates a relationship between two tables. For example, matching authors to books using their IDs.

                                
SELECT * 
FROM books
JOIN authors
  ON books.author_id = authors.id;

Identifying Unique Rows with Primary Key

A primary key is a unique identifier for each row in a table, ensuring no two rows have the same value in this column.

Joining Tables with Inner Join

An inner join returns only the rows where there is a match between the tables. If you have shirt and pants tables, an inner join would show only the rows with matching data from both tables.

Programming Cheatsheets: Quick Reference for Productivity

Welcome to our comprehensive collection of programming language cheatsheets! Whether you're a seasoned developer or a beginner, these quick reference guides provide essential tips and key information for all major languages. They focus on core concepts, commands, and functions—designed to enhance your efficiency and productivity.

ManageEngine Site24x7, a leading IT monitoring and observability platform, is committed to equipping developers and IT professionals with the tools and insights needed to excel in their fields.