SQL Commands Guide(Cheat-sheet)

SQL, or Structured Query Language, is a programming language used to manage data stored in relational databases. A relational database is a database that organizes information into one or more tables, which are collections of data organized into rows and columns. SQL statements are used to manage and manipulate data in these tables.

1. CREATE TABLE

One of the most basic SQL statements is the CREATE TABLE statement, which is used to create a new table. For example:

CREATE TABLE users (
   id INTEGER PRIMARY KEY,
   name TEXT,
   age INTEGER
);

2. INSERT

The INSERT statement is used to insert a new row into a table. For example:

INSERT INTO users (name, age)
VALUES 
     ('John', 30),
     ('Jane', 25);

3. ALTER TABLE

The ALTER TABLE statement is used to add a new column to a table. For example:

ALTER TABLE users 
ADD COLUMN email TEXT;

4. UPDATE

The UPDATE statement is used to edit a row in a table. For example:

UPDATE users 
SET age = 31 
WHERE name = 'John';

5. DELETE FROM

The DELETE FROM statement is used to delete one or more rows from a table. For example:

DELETE FROM users 
WHERE age < 25;

6. AS

The AS keyword allows you to rename a column or table using an alias. For example:

SELECT name AS 'Full Name', age AS 'Age'
FROM users;

7. Constraints

Constraints are used to add information about how a column can be used, such as UNIQUE, NOT NULL, and DEFAULT. For example:

CREATE TABLE employees (
   id INTEGER PRIMARY KEY,
   name TEXT UNIQUE,
   age INTEGER NOT NULL,
   salary INTEGER DEFAULT 50000
);

8. DISTINCT

The DISTINCT keyword is used to return unique values in the output. For example:

SELECT DISTINCT age
FROM employees;

9. WHERE

The WHERE clause is used to restrict query results to only the information we want. For example:

SELECT *
FROM employees
WHERE age > 30;

10.LIKE

The LIKE operator is used to search for a specific pattern in a column. The percentage sign (%) is a wildcard character that matches zero or more missing letters in the pattern. For example:

SELECT *
FROM employees
WHERE name LIKE 'J%';

11. 'IS NULL' AND 'IS NOT NULL'

The IS NULL and IS NOT NULL operators are used to filter the result set to only include rows with null or non-null values in a column, respectively. For example:

SELECT name
FROM employees 
WHERE salary IS NOT NULL;

12. BETWEEN

The BETWEEN operator is used to filter the result set within a certain range. For example:

SELECT *
FROM employees
WHERE age BETWEEN 25 AND 35;

13. AND and OR

The AND and OR operators are used to combine multiple conditions in a WHERE clause to make the result set more specific and useful. For example:

SELECT *
FROM employees
WHERE age BETWEEN 25 AND 35
   AND salary > 60000
   OR name LIKE '%Smith%';

14. ORDER BY

The ORDER BY statement is used to sort the result set in ascending or descending order. For example:

SELECT *
FROM employees
ORDER BY age DESC;

15. LIMIT

The LIMIT statement is used to specify the maximum number of rows the result set will have. For example:

SELECT *
FROM employees
LIMIT 10;

16. CASE

The CASE statement is used to create different outputs based on a condition. For example:

SELECT name,
 CASE
  WHEN age > 30 THEN 'Senior Employee'
  WHEN age > 25 THEN 'Mid-Level Employee'
  ELSE 'Junior Employee'
 END
FROM employees;

In conclusion,SQL is an extremely powerful tool for managing and manipulating data stored in relational databases.