SQL Cheatsheet Cheatsheet

4928 2 years ago

+ 20

Creation & Insertion

Creating a new table :

CREATE TABLE <table name> (

<column name> <data type>,
<column name> <data type>,
.
.
.
<column name> <data type>
);

Inserting values into a table :

INSERT INTO <table name> VALUES (<val1>,<val2>,....,<valn>);

Inserting values in some columns :

INSERT INTO <table name>(<column name>,<column name>)

VALUES (<val1>,<val2>);

Data type is important i.e for strings use quotes (ex : ‘python’ instead of python)

Create table from existing table :

CREATE TABLE <table name> AS(

SELECT <column name>,<column name>
FROM <table name>
);

Visualization
To get description of a table :

DESCRIBE <table name>

or (DESC command runs on only the software system// doesn’t run on editors)

DESC <table name>

Selection
To get all values :

SELECT * FROM <table name> ;

To get specific columns :

SELECT <column name>, <column name> FROM <table name> ;

This can also be achieved by :

SELECT ALL <column name> FROM <table name>

To get used to specify the number of records in return:

SELECT <column_name>
FROM <table name>
WHERE condition
LIMIT number;

To get all unique values from a column :

SELECT DISTINCT <column name> FROM <table name> ;

Where Conditionals
To select specific rows with a condition :

SELECT <column name>

FROM <table name>
WHERE <condition>

Example :
Fetching rows with score greater than 85.

SELECT marks
FROM student_marks
WHERE score > 85 ;

Relational Operators
 =,>,<,>=,<=,<>

Example :
Fetching rows where city is not New York.

SELECT * FROM citydata

WHERE city <> 'New York' ;

Logical Operators

||, &&, !

|| -> Or
&& -> And
! -> Not
The default order is → 1)NOT, 2)AND and 3)OR use parenthesis to override.

Example :

1. Fetch students who failed in math and english.

SELECT studentname FROM studentdata

WHERE math = 'Fail' && english = 'Fail';

2. Fetch boys who failed in math and english.

SELECT studentname FROM studentdata

WHERE (math = 'Fail' && english = 'Fail') && gender = 'Male';

Iterating over a list

SELECT <column name>

FROM <table name>
WHERE <column name> <condition> IN (<val1>,<val2>,...<val3>)

Example :
Fetch names from student in class 8,9 & 10.

SELECT studentname
FROM studentdata
WHERE class IN (8,9,10);

Pattern Matching

1. Starting with 'x' :

SELECT <column name>

FROM <table name>
WHERE <column name> LIKE 'x%';
 2. Ending with 'x' :

SELECT <column name>

FROM <table name>
WHERE <column name> LIKE '%x';

Can also use NOT LIKE instead of LIKE.

Examples :

1. Fetch names of students starting with ‘A’.

SELECT studentname
FROM studentdata
WHERE name LIKE "A%";

2. Fetch names which have 3 letters.

SELECT studentname
FROM studentdata
WHERE name like "___";

-> 3 underscores.

Manipulation
Delete all records :

DELETE FROM <table name>

Delete with a condition :

DELETE FROM <table name> WHERE <condition>

Example :

1. Delete ‘Yash’ student data.

DELETE FROM studentdata WHERE studentname= "Yash";

Update values in a column :

UPDATE <table name>

SET <column name> = <val1>, <column name> = <val2>
WHERE <condition>;

Example :

1. Update student grade if passed math (scored more than 80).

 UPDATE studentdata
SET maths = "Pass"
WHERE maths > 80;

Add a new column by using ALTER :

ALTER TABLE <table name>

ADD <column name> <data type>;

Dropping a table :
Gets rid of the table.

DROP TABLE <table name>;

Truncating a table :
Gets rid of the data inside the table, preserving its structure.

TRUNCATE TABLE <table name>;

Constraints

UNIQUE, PRIMARY KEY, DEFAULT, CHECK, FOREIGN KEY, NOT NULL, CREATE INDE

Unique → Repetition of values forbidden

Primary Key → same as unique, cannot take NULL value. Only 1 Primary key in a table

Default → sets a pre defined value if value not entered.

Check → Limits value.

Foreign Key → Is a unique/primary key in a different table, but referenced in another.

Not Null -> It ensures that a column does not have a null value.

Create Index → It is used to create and retrieve data from the database very quickly.

Group By Clause

Used with different functions like COUNT, AVG, etc..

SELECT <column name> FROM <table name>

WHERE <condition>
GROUP BY <column name>

Example :
Get count of all students in each stream

SELECT COUNT(fname)
FROM studentdata
 GROUP BY stream;

Order By Clause
ASC → Ascending order (Default)

DESC → Descending order

SELECT <column name>, <column name>

FROM <table name>
ORDER BY <column name>, <column name> ASC <|DESC>;

Example :
Fetch all students and arrange them according to their age, in a descending order.

SELECT fname,lname
FROM studentdata
ORDER BY age DESC;

Can use both simultaneously like :

SELECT fname,lname
FROM studentdata
ORDER BY marks ASC, age DESC;

Can also order categorical columns in an alphabetical order.

Searching for NULL values

SELECT <column name>, <column name>

FROM <table name>
WHERE <column name> IS NULL;

Example :
Getting the first & last names where the age is a NA value.

SELECT fname, lname

FROM studentdata
WHERE age IS NULL;

Can also use the IS NOT NULL instead of IS NULL keyword, like this :

SELECT <column name>, <column name>

FROM <table name>
WHERE <column name> IS NOT NULL;

Joins in SQL
INNER JOIN
This query will return all of the records in the left table (table A) that have a matching
record in the right table (table B)
Example :

SELECT [select_list]
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key

-- shortcut for inner join using , operator

SELECT [select_list]
FROM Table_A A,Table_B B
ON A.Key = B.Key
-- , basically means cross product and if condition given as per condit

LEFT JOIN
This query will return all of the records in the left table (table A) regardless if any of
those records have a match in the right table (table B). It will also return any matching
records from the right table.

Example:

SELECT [select_list]
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key

RIGHT JOIN
This query will return all of the records in the right table (table B) regardless if any of
those records have a match in the left table (table A). It will also return any matching
records from the left table

Example

SELECT [select_list]
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key

OUTER JOIN
This Join can also be referred to as a FULL OUTER JOIN or a FULL JOIN. This query will
return all of the records from both tables, joining records from the left table (table A)
that match records from the right table (table B)
Example:

SELECT [select_list]
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
LEFT OUTER JOIN
This query will return all of the records in the left table (table A) that do not match any
records in the right table (table B).
Example:

SELECT [select_list]
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL

RIGHT OUTER JOIN

This query will return all of the records in the right table (table B) that do not match any
records in the left table (table A).
Example:

SELECT [select_list]
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL

Sql Data Control Language

DCL Commands are used to control access to the database and the data associated to
it. And it is highly used inforcing Data Security.

We have commands for managing user like :

Create User

CREATE USER <Username> IDENTIFIED BY <password>

Delete User

DROP USER <Username>

GRANT

GRANT SELECT / INSERT / DELETE / UPDATE / All privileges on <Table_Name

REVOKE

REVOKE SELECT / INSERT / DELETE / UPDATE / All privileges on <Table_Nam

OneCompiler.com

About
Contact

Users
Status
Pricing

GitHub
LinkedIn
Facebook
Instagram
Twitter

Languages

Java Python C
C++ NodeJS JavaScript
Groovy JShell Haskell
Tcl Lua Ada
CommonLisp D Elixir
Erlang F# Fortran
Assembly Scala PHP
Python2 C# Perl
Ruby Go R
Racket OCaml Visual Basic (VB.NET)
Basic HTML Materialize
Bootstrap JQuery Foundation
Bulma Uikit Semantic UI
Skeleton Milligram PaperCSS
BackboneJS React (Beta) Angular (Beta)
Vue (Beta) Vue3 (Beta) Bash
Clojure TypeScript Cobol
Kotlin Pascal Prolog
Rust Swift Objective-C
Octave Text BrainFK
CoffeeScript EJS MySQL
Oracle Database PostgreSQL MongoDB
SQLite Redis MariaDB
Cassandra Oracle PL/SQL Microsoft SQL Server

More
Orgs
API
Pricing

Cheatsheets
Tutorials
Tools
Stats

© Copyright 2024 One Compiler Pvt. Ltd. | Privacy Policy | Terms & Conditions