SQL Cheat Sheet - Scaler Topics

Overview

The SQL commands are used to retrieve data from the database. The DBMS processes the SQL request, retrieves the requested data from the database, and returns it. This process of requesting data from the database and receiving back the results is called a database query and hence the name Structured Query Language.

SQL is used to control all the functions that a DBMS provides for its users, including :

Data Definition :
SQL allows a user to define the structure and the organization of the data to be stored and the relationships among the stored data items
Data Retrieval :
SQL allows a user or an application program to retrieve the stored data from the database
Data Manipulation :
SQL lets a user or an application program update the database by allowing it to add new data, delete the existing data, and modify the existing data.
Access Control :
SOL can be used to restrict a user's ability to retrieve, add, and modify data, thus protecting the stored data against unauthorized access

Installation

To install MySQL in Windows :

Download MySQL from its official website. Click here

To install MySQL in MAcOS :

Use package managers such as Homebrew and use the following command. Write the version name in <version>. brew install mysql brew install mysql@<version>

SQL Keywords

Keyword	Description
1.CREATE DATABASE	To create a new database
2.CREATE TABLE	To create a new table
3.INSERT INTO	To add new rows to a table
4.SELECT	To select data from a database that is returned in a results set.
5.SELECT INTO	To Copy data from one table and inserts it into another table.
6.WHERE	To filter results to include data that meets the given condition.
7.VALUES	To add new values to a table.
8.UPDATE	To update existing data in a table.
9.FROM	To specify the table to delete or select data from.
10.EXISTS	To check for any record within the subquery.
11.CHECK	To add a constraint that limits the value to be added to a column.
12.SET	To update existing data in a table.
13.DESC	To return the data in descending order.
14.UNIQUE	To ensure all values in a column are unique.
15.UNION	To Combine the results and return distinct values.
16.DELETE	To Delete data from a table.
17.ALTER COLUMN	To Change the data type of a table’s column.
18.SELECT TOP	To return a set number of records to return from a table.
19.AND	To join separate conditions within a WHERE clause.
20.UNION ALL	To include duplicate values.
21.TRUNCATE TABLE	To delete the data without deleting the table.
22.ANY	To return if any of the subquery values meet the given condition.
23.ADD	To add a new column to an existing table
24.ORDER BY	To arrange data in ascending (default) or descending order.
25.ADD CONSTRAINT	To add a new constraint in the table.
26.ALTER TABLE	To add, delete or modify columns in a table.
27.LIKE	To return true if the operand value matches a pattern.
28.BETWEEN	To select values within a specific range.
29.ALL	To return true if the subquery values meet the given condition.
30. AS	To rename a table or column with an alias value.
31.DROP COLUMN	To delete a column from a table.
32.DROP DATABASE	To delete an entire database.
33.CASE	To change query output depending on conditions.
34.DEFAULT	To set a default value for a column.
35.DROP TABLE	To delete a table from a database.

To learn more about keywords in SQL, Click here.

SQL Commands

The five types of SQL commands are :

Data Definition Language(DDL)
- Create
- Drop
- Alter
- Truncate
Data Manipulation Language
- Insert
- Update
- Delete
Data Control Language
- Grant
- Revoke
Transaction Control Language
- Commit
- Rollback
- Save point
Data Query Language
- Select

Learn more here.

SQL Constraints

The kind of data stored in a table is put to certain limits by data types. Some kinds of constraints are defined while defining values for rows and columns in a table that raises an error when violated.

There are two types of constraints :

Table Constraint :
The constraint applicable to a column definition as well as to more than one column in a table.
Column Constraint :
The constraint applicable to only a specific column.

Crud Operations in SQL Commands

1. CREATE

C of CRUD in SQL commands is used to insert and add values to the table. To do so, First, we use the CREATE command to create a table and then INSERT INTO to add or insert values to the table.

CREATE

INSERT INTO

2. READ

R of CRUD in SQL is used to fetch data from the given table. Hence, we use the SELECT command to fetch data from the table.

SELECT

3. UPDATE

U of CRUD in SQL is used to update the existing records in the table. Hence, we use the UPDATE command.

4. DELETE

D of CRUD in SQL is used to delete the table or delete the records in the table. Hence, we use the DELETE command.

Clauses in SQL

GROUP BY CLAUSE

The GROUP BY clause is used with the SELECT statement to arrange similar data, into groups. It is used as an aggregation function following the WHEREclause and preceding the ORDER BYclause.

HAVING CLAUSE

HAVING is used with the GROUP BY clause to specify the search condition for a group.

ORDER BY CLAUSE

The ORDER BY clause is used to sort data in ascending (by default) or descending order(by using the DESC keyword).

Triggers in SQL

Whenever there's a special event, that occurs in the database due to which a stored procedure gets invoked, We call it to be a trigger.

Syntax :

Comments in SQL

To comment out code, SQL uses Single line comments and multiline comments.

1. Single Line Comment :

Starts with -.The characters after - are not considered to be a part of the code.

For example :

2. MultiLine Comment

Starts with /_ and ends with _/.

For example :

SQL Operators

SQL uses Arithmetic Operators, Bitwise operators, Comparison Operators, and Compound operators.

1. Arithmetic Operators

To Add- +
To Subtract- -
To Multiply- *
To divide-/
To get the remainder- %

2. Bitwise Operator

Bitwise AND - &
Bitwise OR - |
Bitwise exclusive OR - ^

3. Comparison Operators

Equal to - =
Greater than - >
Less than - <
Greater than or equal to - >=
Less than or equal to - <=
Not equal to - <>

4. Compound Operators

Add equals - +=
Subtract equals - -=
Multiply equals - *=
Divide equals - /=
Modulo equals - %=
Bitwise AND equals - &=
Bitwise exclusive equals - ^-=
Bitwise OR equals - |*=

Learn more here.

SQL Functions

String Functions

Function	Keyword
ASCII	Used to return the equivalent ASCII value for a given specific character.
CHAR_LENGTH	Used to Return the length of a character string.
CONCAT	Used to merge expressions.
CONCAT_WS	Used to merge expressions with a separator between each value.
FIELD	Used to return an index value relative to the position of a value provided by a list of values.
FIND IN SET	Used to return the position of a string in a list of strings.
FORMAT	Used to return the number formatted to include commas when a number is provided as input. (eg 3,405,600).
INSERT	Used to insert one string into another at a given point, for a required number of characters.
INSTR	Used to return the position of the first time one string appears within another.
LCASE	Used to convert a given string to lowercase.
LEFT	Used to extract the given number of characters from a string and return them as another, starting from the left.
LOCATE	Used to return the first occurrence of one string within another.
LPAD	Used for adding left pad one string with another of a specific length.
LENGTH	Used to return the length of a string in bytes.
MID	Used for extracting one string from another, starting from any position.
LTRIM	Used to remove leading spaces in a given string.
POSITION	Used to return the position of the first time one substring appears within another.
REPLACE	Used to replace any instances of a substring within a string, with a new substring.
REPEAT	Used for repetition of a string
RIGHT	Used to extract the given number of characters from a string and return them as another starting from the right.
REVERSE	Used to reverse a given string.
RTRIM	Used to remove any trailing spaces from the given string.
SPACE	Used to return a string full of spaces equal to the amount passed.
RPAD	Used for applying right pads one string with another, to a specific length.
SUBSTRING_INDEX	Used to return a substring from a string before the passed substring is found the number of times equals the passed number.
STRCMP	Used for comparing two strings.
SUBSTR	Used to extract a substring from another, starting from any position.
UCASE/UPPER	Used to convert a string to uppercase.
TRIM	Used to remove trailing and leading spaces from the given string.

Numeric Functions

Function	Description
ABS	Used to return the absolute value of the given number.
ASIN	Used to return the arc sine of a given number.
ATAN	Used to return the arc tangent of given numbers.
ATAN2	Used to return the arc tangent of given numbers.
ACOS	Used to return the arc cosine of a given number.
AVG	Used to return the average value of the given expression.
CEIL	Used to return the closest whole number (integer) upwards from a given decimal point number.

To learn more about SQL functions, Read here.

Filtering in SQL

In SQL, the FILTER clause is used for filtering the input data to an aggregation function. It is even more flexible than the WHERE clause.

Learn more about Filtering in SQL here.

Wildcard Characters in SQL

Wildcard characters	Description
%	Matches with any string followed by any number of characters. Eg : 'B%' will match with all the strings starting with B followed by any number of characters. %B will match all strings starting with any characters and ending with B. %B% will match with a string consisting of B at any position.
_	Matches string with two characters where the string must start with the specified character followed by any character of a single length. Eg : 'B\_' will match with the string starting with B followed by any other character. '\_B will match with any character followed by a B.'
[]	Matches a single character starting with a given character but lying in a given in specific range ([a-f]) or set ([abcdef]). Eg : 'S[a,r,t]'will match with strings like Star,Start etc.
[^]	Matches the character string starting with a given character but not lying in the specified range ([^a-f]) or set ([^abcdef]) Eg : 'S[^a,r,t]'will match all string starting with S but not strings like Star, Start, etc.

SQL Keys

Candidate Key :
The set of one or more attributes that uniquely identify a row in a table.
Primary Key :
An attribute that uniquely identifies a row from the table.
Alternate Key :
Candidate keys apart from the primary keys are known as Alternate Key.
Composite Primary Key :
A combination of more than one key attribute is known as Composite Primary Key.
Non-Key Attribute :
The keys apart from the candidate keys are known as Non-Key attributes.
Foreign Key :
The set of attributes whose values are matched with a column in the same table.

Indexes in SQL

To make data retrieval efficient, We use indexes. Indexes are the attributes assigned to columns to be searched against to make quick data retreival.

CREATE INDEX : Creates Indexes where duplicates are allowed.
CREATE UNIQUE INDEX : Creates indexes with no duplicate values.
DROP INDEX : Deletes an existing index.

To learn more, click here.

SQL Joins

Joins is used to combine data from multiple tables.

Inner Join (By Default) :
Used to return records having matching values from both tables.
Left Join :
Used to return all the records having macthing records from the second table from the first table.
Right Join :
Used to return all the records from the second table along with any matching records from the first.
Full Join :
Used to return records from both tables in case of a match.

To learn SQL Joins in detail, click here.

Views in SQL

Views in SQL are used to store the database under a label that prevents rerunning the query.

To Create a View :

To update a View :

To delete a view :

To learn more, Click here.

SQL Stored Procedures

SQL stored procedures are used to save a written code to prevent writing the code again and again.

It helps in code reusability.

SQL Injection

SQL injection is a technique in which malicious code is palced in SQL taking inputs from the webpage.

Conclusion

SQL commands are used to insert, update, retrieve, and perform operations in the database.
It helps in building complex websites dealing with a huge amount of data.
In this article, we skimmed through an entire overview of SQL concepts.
To learn more in detail, Follow this tutorial.
It's your turn now to play around with commands and explore the use of SQL commands considering sets of data and performing various operations.