SQL: Compelete sql cheatsheet

Introduction

what is database

Ans: Database is a place to store data, it is a organized collection of information.

what is database management system

Ans: Software which allow us to manage information. Management of data like manipulation create new entry deletion and creating new relations in can be achieve by DBMS

1. it allow to create and maintain DB
2. handle security so can be accessible with proper rights
3. Allows you to backup information
4. import/export data

CRUD

Ans: Accronym stands for Create Read Update Delete.

keys in relational databases

Ans:

1. Primary key: Unique identifier which is use to differentiate one from other. and it can't be null.
2. Foreign key: Use to establish relation between two table which refers to primary key of other table.
3. Composite key: Type of primary key and use 2 or more columns to create a new key.

SQL

Ans:

• Language used to intract with RDBMS,
• we write sql queries to create db, table, perform CRUD operations.
• Perform Administrative tasks like user management, import/export

Sql is combination of 4 languages

• DQL (Data Query Language): Used to query information
• DDL (Data Defination Language): Helps in defining Schemas
• DCL (Data Control Language): Used for controlling access such as user and permission management.
• DML (Data manipulation Language): Used for inserting, updating and deleting data from database.

Query

Ans: Set of instruction that you give to RDBMS to do something and query helps us in achiving goal / desired result based on those instructions

SELECT * FROM Employees WHERE CustomerId = 10;
 /* like this query is used to retrieve all the columns
 * from the employee table where customer id is 10

Different Types of DataTypes

• INT (INT): whole number without decimal
• FLOAT (FLOAT): Decimal number
• VARCHAR (VARCHAR(char_length)): Text
• DATE (DATE): Dates

---

SQL Queries

• SQL Introduction

• SQL Creating a DB

• SQL Use or Select a DB

• SQL CREATE TABLE

• SQL INSERT INTO

• SQL Insert Multiple Rows

• SQL SELECT

  • SQL LIMIT
  • SQL DISTINCT

• SQL WHERE

  • SQL BETWEEN
  • SQL LIKE
  • SQL NULL Values

• SQL AND, OR, NOT

• SQL ORDER BY

• SQL Aggregate Functions

  • SQL MIN and MAX
  • SQL AVG, SUM
  • SQL COUNT

• SQL IN

• SQL GROUP BY

• SQL HAVING

• SQL Aliases

• SQL UPDATE

• SQL UNION

• SQL VIEWS

• SQL JOINS

• SQL INNER JOIN

• SQL LEFT JOIN

• SQL RIGHT JOIN

• SQL CROSS JOIN

• SQL DELETE

• SQL EXISTS

• SQL DROP DATABASE

• SQL ALTER TABLE

Creating a DB

CREATE DATABASE DatabaseName

Use or Select a DB

USE DatabaseName

Create Table

The column parameters specify the names of the columns of the table. The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).

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

Insert Into

It is possible to write the INSERT INTO statement in two ways:

1. Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:

INSERT INTO table_name VALUES(
    value1, value2, value3
   ....
);

Insert Multiple Rows

INSERT INTO table_name (column1, column2, column3 ....)
VALUES
(value1, value2, value3, ...),
(value1, value2, value3, ...),
(value1, value2, value3, ...);

SELECT

It can be used in 2 ways.

1. select All column using * (Wildcards)

SELECT * FROM table_name;

2. select specific column using column_name

SELECT column1, column2, ... FROM table_name;

LIMIT

LIMIT used with select to limit the result.

SELECT * FROM table_name LIMIT limit_amount;

DISTINCT

The SELECT DISTINCT statement is used to return only distinct (different) values. Distinct returns unique value from that column

SELECT DISTINCT column1, column2, ... FROM table_name;

WHERE

The WHERE clause allows you to specify a condition/criteria.

available conditions

• '=' will find and return the exact macthing column result.
• '>' will find and return the data for column result greater than condition.
• '<' will find and return the data for column result smaller than condition.
• '>=' will find and return the data for column result greater than equal to condition.
• '<=' will find and return the data for column result smaller than equal to condition.
• '<>' will find and return the data for column result is not equal to condition.

SELECT * FROM table_name WHERE condition;
/* 
 * condition is combination of column name operator and operands
 * For ex:
 * SELECT * FROM students WHERE id = 6;
 * SELECT * FROM students WHERE rollNo > 20;
 * SELECT * FROM students WHERE marks < 35;
 */

BETWEEN

The BETWEEN statement is used to select record within a range

SELECT * FROM table_name WHERE column_name BETWEEN min AND max;
/*  min and max are values

LIKE

The LIKE statement is used with where clause. it is used to search for a pattern

• 'a%' will return records that start with a.

• '%a' will return records that ends with a.

• '%a%' will return records which contains 'a' character.

• 'a%e' will return records which starts with 'a' and ends with 'e' character.

• '%' Represents zero or more characters

• '_' Represents a single character

• '[]' Represents any single character within the brackets *

• '^' Represents any character not in the brackets *

• '-' Represents any single character within the specified range *

• '' Represents any escaped character **

* Not supported in PostgreSQL and MySQL databases.

** Supported only in Oracle databases.

SELECT * FROM table_name WHERE column_name LIKE pattern;
/*  pattern can be '%a%', '%a', 'a%', 'a%e' ... 

NULL VALUES

The NULL VALUES statement is used with where clause. it is used to search for null values. It is not possible to test for NULL values with comparison operators, such as =, >, < . We have IS NULL and IS NOT NULL operators instead.

SELECT * FROM table_name WHERE column_name LIKE pattern;
/*  pattern can be '%a%', '%a', 'a%', 'a%e' ... 

AND OR NOT

• AND: In AND all condition needs to be true.
• OR: where as in OR either of them need to be truthy.
• NOT: Use to specify condition not to be truthy.

SELECT * FROM table_name WHERE condition AND condition;

SELECT * FROM table_name WHERE condition OR condition;

SELECT * FROM table_name WHERE NOT condition;

ORDER BY

Use to sort data either in ascending or decending, Default sorting order will be ascending.

SELECT * FROM table_name ORDER BY column_name ASC;

SELECT * FROM table_name ORDER BY column_name DESC;

AGGREGATE FUNCTIONS

Sql gives some method to boost up query pereformance.

• MIN: to get minimum value among the whole column.
• MAX: to get maximum value among the whole column.
• AVG: to get average value from the whole column.
• SUM: to get total sum of whole column.
• COUNT: to get count of record the whole column.

MIN MAX

SELECT MIN(column_name) FROM table_name;

SELECT MAX(column_name) FROM table_name;

AVG SUM

SELECT AVG(column_name) FROM table_name;

SELECT SUM(column_name) FROM table_name;

COUNT

SELECT COUNT(*) FROM table_name;

with distinct column

SELECT COUNT(DISTINCT column1) FROM table_name;

IN

IN operator allows you to specify multiple values

SELECT * FROM table_name WHERE column_name IN(parameter1, parameter2, parameter3, ...);

GROUP BY

GROUP BY operator allows you to group rows, Oftenly used with aggregate functions.

SELECT column FROM table_name WHERE condition GROUP BY column2;
SELECT AGGREGATE_FUNC(column1), column2 FROM table_name GROUP BY column2;

/*
 * for ex: Show number of student in each branch
 * SELECT COUNT(id), branch FROM students GROUP BY branch
 * 

HAVING

HAVING clause used to refine/ add condition after the result of grouped record. It is similar to where and used with GROUP BY

SELECT column FROM table_name WHERE condition GROUP BY column2 HAVING condition;
SELECT AGGREGATE_FUNC(column1), column2 FROM table_name GROUP BY column2 HAVING condition;

/*
 * for ex: Show number of student in each branch where student count is more than 20
 * SELECT COUNT(id), branch FROM students GROUP BY branch HAVING COUNT(id) > 20;
 * 

ALIASES

ALIASES is used to give a table or column, a temporary name. Mostly used to make the column names readable, which makes it easy to understand. The alias exists only for the duration of that query

SELECT column AS newCol FROM table_name;

UPDATE

UPDATE is used to alter/update a record, mostly use with where condition to prevent complication.

UPDATE table SET column = "new value" where condition;

UNION

UNION is used to add rows to existing table. This operator will add distinct values but if you want default value then can use UNION ALL statement

SELECT column1 FROM table1 UNION SELECT column1 FROM table2 
SELECT column1 FROM table1 UNION ALL SELECT column1 FROM table2 

VIEWS

VIEW will create a separate table to view, which eventually hides the data from other access so become impossible to retrive secrate data.

• Security: views can be made accessible to users and show them only the data they need to see.
• Simplicity: Views are simple and can be used to hide complex queries.
• Show aggregated data where queries are generating sum, avg and presents the final output in the form of a view.

CREATE VIEW view_name AS <<SQL_QUERY>>

JOINS

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

INNER JOIN

INNER JOIN selects records that have matching values.

SELECT column1, column2, ... FROM table1 INNER JOIN table2 ON related_condition

/*
select * FROM students INNER JOIN address ON students.id = address.studentId

LEFT JOIN

LEFT JOIN selects All the records from left table and macthing record from ther right table.

SELECT column1, column2, ... FROM table1 LEFT JOIN table2 ON related_condition

/*
select * FROM students LEFT JOIN address ON students.id = address.studentId

RIGHT JOIN

RIGHT JOIN selects All the records from right table and macthing record from ther left table.

SELECT column1, column2, ... FROM table1 RIGHT JOIN table2 ON related_condition

/*
select * FROM students RIGHT JOIN address ON students.id = address.studentId

CROSS JOIN

CROSS JOIN selects records all records from both table.

SELECT column1, column2, ... FROM table1 CROSS JOIN table2;

/*
select * FROM students CROSS JOIN address;

DELETE

The DELETE statement is used to delete existing records in a table.

DELETE FROM table_name WHERE condition;

EXISTS

The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.

SELECT column_name FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

/*
* SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

DROP DATABASE

The DROP DATABASE statement is used to drop an existing SQL database.

DROP DATABASE databasename;

ALTER TABLE

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

/* ADD Column */
ALTER TABLE table_name ADD column_name datatype;

/*  DROP COLUMN */
ALTER TABLE table_name DROP COLUMN column_name;

/* RENAME COLUMN */
ALTER TABLE table_name RENAME COLUMN old_name to new_name;

/* ALTER/MODIFY DATATYPE */
ALTER TABLE table_name ALTER COLUMN column_name datatype;