SQL Cheat Sheet

By | May 16, 2020
SQL Cheat Sheet
  • A database is a collection of data
  • In the database, we collect data in an organized way.
  • Database that store data in tables known as Relational DataBase.
  • SQL stands for Structured Query Language.
  • SQL is used to create, update, retrieve and delete a database.

Some popular SQL Programs

There are many popular SQL programs and each one has their advantages and disadvantages.

Vamware
  • MySQL
  • SQLite
  • PostgreSQL
  • Oracle

SQL Commands

In SQL we use 3 types of Commands:

  • Data Definition Language(DDL): DDL commands used to define the frame of a database and its major commands are CREATE, ALTER, and DROP.
  • Data Manipulation Language(DML): DML commands used to modify or edit the existing database and its major commands are INSERT, UPDATE and DELETE.
  • Data Control Language(DCL): DCL commands deal with the permission of the database and its major commands are GRANT and REVOKE.
  • Data Query Language(DQL)
  • Data Transfer Language(DTL)

SQL Datatypes

Data types are the most important factor, we should know data types before we store data in that format.

Data Type Description
int To store integer values
decimal To store numeric value with decimal points.
numeric To store a numeric value
float To store numeric value or real numbers
DateTime To store data and time in a specific format
char(n) To store characters with n length
varchar(n) To store character data
text To store characters or string
bit 0 or 1 integer
image To store images
real To store real numbers
binary To store binary values
timestamp Timestamp (time in seconds)
table To store a temporary table
XML XML values

Creating Table

To create a new table in the database we use CREATE TABLE command.

Example:

CREATE TABLE STUDENT
(  roll_no int,
name varchar(30),
phone varchar(11)
);

SQL Constraints

In SQL there are some rules that we need to follow to store data in an organized way and SQL commands can work on it. We use constrains when we create a table and pass it along with the data types.

Here are the SQL Constraints we use while creating a table.

  • Primary key: It makes sure that all the data of primary key constraint have unique values and No null Value.
  • Not Null: There would be no Null value in that column
  • Check: It makes sure that all the values of the column satisfy the condition
  • Foreign Key: Uniquely Identifies a record in another table
  • Default: It gives a default value to the column if the user does not give a value.
  • Unique: It makes sure that each value of the column is different.

Syntax to Write a constraint:

CREATE TABLE table_name (
    column_name datatype constraint,
    column2_name datatype constraint,
    column3_name datatype constraint,
);

Insert Data into the table

INSERT…..VALUES:

Using the INSERT and VALUES command we can enter data into a table.

Example:

INSERT INTO Student VALUES (1,'Ash’,’9999991119’);

Multiple Insertion of data using single INSERT command:

INSERT INTO Student
VALUES (2,’Kakashi’,’2020202020’),
        (03,‘Naruto’,’3030303030’),
        (4,‘sam,’4040404040’),
        (5,‘Goku’,’5050505050’),
        (6,‘Gohan’,’6060606060’),
        (7,‘sam,’1010101010’);

Copy using Insert Command:

INSERT INTO Student
SELECT * FROM Student_backup_Table
WHERE roll_no < 30; 

SELECT

SELECT command is used to retrieve the attributes (columns with their values), of the Table.

SELECT roll_no, name
From Student;

From

From tell, from which table select will retrieve the attributes and values, there could be more than one table associated with FROM statement.

Example:

SELECT roll_no, name
From Student ,  Student_backup_Table;

DISTINCT

DISTINCT is used along with SELECT command, and it is used to eliminate the duplicate values.

Example:

SELECT DISTINCT name
FROM Student;

ALTER

With ALTER command we can perform various operations such as adding new columns, modify data type and add or remove constraints.

Example:

To add a new column in the table

ALTER TABLE Student,
ADD rank;

To delete a column from the table

ALTER TABLE Student
DROP COLUMN rank;

To change the data type of table:

ALTER TABLE Student
ALTER COLUMN Name varchar(100);

Rename the Table

With the help of RENAME command, we can rename the table

Example:

RENAME TABLE Student TO Students;

Drop-Table:

DROP TABLE command used to delete the complete table.

Example:

DROP TABLE Students;

Truncate Table

Truncate table command is used to delete the table content, if we use the truncate table command it will not delete the table, it just deletes the content or data of the table.

Example:

TRUNCATE TABLE Students;

Update Data of the Table

With the help of SET command, we can update the data we have saved previously. We can also put come conditions to SET update for specific data.

Example:

SELECT Student
SET  phone = ‘7070707070’
WHERE roll_no = 7;

Delete

With the help of DELETE command, we can delete rows of the table.

Example:

DELETE FROM Student
WHERE roll_no = 7;

Strings Functions

In SQL we have a special function which we can apply on our string data to customize it.

Sting Functions Example
left SELECT left

(‘SAMO TARLI’, 5)

len SELECT len

(‘length’)

lower SELECT lower (‘HELLO’)
reverse SELECT reverse (‘esrever’)
right SELECT right (‘RIGHT’ ,4)
space SELECT ‘Well’ + space(2) + ‘Done’
str SELECT str (2389,6,2)
substring SELECT substring (‘Hello’ ,2,2)
upper SELECT upper (‘hello’)

Aggregate Functions

Aggregate functions used to summarize the attribute in one value.

Aggregate Functions Description
avg Provide the average of all values
count Count the total number of values present in the attribute
min Give the minimum value present in an attribute
max Give the maximum value present in the attribute
sum Sum all the values of the attribute.

Comparison Operators

In SQL we have some standard comparison operators which we use along with WHERE statement to retrieve or update some specific row or data.

Comparison Operator Name
> Greater than
< Smaller than
>= Greater than equal to
<= Less than equal to
!= Not Equal to
= Equal to

 

Examples:

SELECT * FROM Student
WHERE roll_no = 4;

SELECT * FROM Student
WHERE roll_no > 10;

SELECT * FROM Student
WHERE roll_no != 4;

SELECT * FROM Student
WHERE roll_no >= 5;

Logical Operators in SQL

In SQL we have 3 Logical operators, we use logical operators between two conditional expressions.

  • AND
  • OR
  • NOT

OR Operator

SELECT * FROM  Student
WHERE roll_no= 40 OR roll_no=56;

AND Operator

SELECT * FROM  Student
WHERE roll_no > 10 AND roll_no <56;

NOT Operator

SELECT * FROM  Student
WHERE NOT name = “Naruto”;

BETWEEN

With the help of BETWEEN command, we can obtain or retrieve a set of range.

Example:

SELECT * FROM Student
WHERE roll_no BETWEEN 5 AND 10;

LIKE ……..%

With LIKE command we can match string patterns, we use % symbol along with LIKE command to specify which pattern we want to search.

Example:

SELECT * FROM Student
WHERE name LIKE ‘s%’ ;

It will show all those records where the name starts from s.

SELECT * FROM Student
WHERE name LIKE ‘%s’ ;

It will show all those records where the name ends with s.

SQL Sequence ORDER BY

With ORDERED BY command we can retrieve data in Ascending or Descending order. By default, SQL retrieves data in Ascending order.

Example:

SELECT * FROM Student
ORDER BY roll_no DESC;

JOIN

JOINs command is used to join two tables together and provide a single table as a result.

Join Types:

  • INNER JOIN
  • OUTER JOIN
  • CROSS JOIN

INNER JOIN

INNER JOIN is similar to a simple JOIN, it returns rows when there is a match between the tables.

SELECT * FROM s1.Student INNER JOIN s2.Student_backup_table
ON  s1.Student = s2.Student_backup_table;

OUTER JOIN

OUTER ROW shows all the rows of the first table and the matching rows of another table.

SELECT * FROM s1.Student OUTER JOIN s2.Student_backup_table
ON  s1.Student = s2.Student_backup_table;

CROSS JOIN

It generates a table by the cartesian product of one table with another table. The total number of rows after the cartesian product would be rows of first table * columns of 2nd table.

SELECT * FROM Students CROSS JOIN Students_backup_table;

Subqueries

With SQL we can query inside another query statement, with this we can perform multiple queries statements at once.

IN

With IN command we can check whether the data is present in the table or not.

Example:

SELECT * FROM Students
WHERE name IN
(SELECT name FROM Students
WHERE name = ”Sam” );

EXISTS

The EXISTS command is used to check whether the record exists or not.

SELECT * FROM Students
WHERE EXISTS
(SELECT name FROM Students
WHERE name = ”Sam” );

GROUP BY

The GROUP BY statement groups rows that have the same values into summary rows.

Example:

SELECT *
FROM Customers
GROUP BY name;

Having:

The having is similar to WHERE statement, HAVING used along GROUP BY statement.

SELECT *
FROM Customers
GROUP BY name
HAVING COUNT(name) > 5;

You might be interested in:

Leave a Reply

Your email address will not be published. Required fields are marked *