SQL cheat sheet

SQL Cheat Sheet [Basic DDL and DML commands]

MySQL, Snowflake, SQL

DATA DEFINITION LANGUAGES (DDL) COMMANDS

A Data Definition Language (DDL) statement is used to define the database structure or schema.

Aim:

To study and execute the DDL commands in RDBMS.

DDL commands:

  • CREATE
  • ALTER
  • DROP
  • RENAME
  • TRUNCATE

SYNTAX

CREATE:

To make a new database, table, index, or stored query. A create statement in SQL creates an object inside of a relational database management system (RDBMS).

To create database:

CREATE DATABASE  <database_name>

To create table:

CREATE TABLE <table_name> 
(
Column_name1 data_type ([size]), 
Column_name2 data_type ([size]),
.
.
Column_name-n data_type ([size])
);

ALTER A TABLE:

To modify an existing database object. Alter the structure of the database.

To add a column in a table

ALTER TABLE table_name ADD column_name datatype;

To delete a column in a table

ALTER TABLE table_name DROP column column_name;

DROP TABLE:

Delete Objects from the Database

DROP TABLE table_name;

TRUNCATE TABLE:

Remove all records from a table, including all spaces allocated for the records are removed.

TRUNCATE TABLE table_name;

DATA MANIPULATION LANGUAGE (DML) OF BASE TABLES AND VIEWS:

Data manipulation language allows the users to query and manipulate data in existing schema in object. It allows following data to insert, delete, update and recovery data in schema object.

Aim:

To study DML commands in RDBMS.

DML COMMANDS:

  • INSERT
  • UPDATE
  • DELETE
  • SELECT

QUERY:

Query is a statement in the DML that request the retrieval of data from database.

  • The portion of the DML used in a Query is called Query language. The SELECT statement is used to query a database

SYNTAX:

INSERT:

Values can be inserted into table using insert commands. There are two types of insert commands. They are multiple value insert commands (using ‘&’ symbol) single value insert command (without using ‘&’symbol)

Syntax:

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

UPDATE:

This allows the user to update the particular column value using the where clause condition.

Syntax:

UPDATE <table_name> SET <col1=value> WHERE <column=value>;

DELETE:

This allows you to delete the particular column values using where clause condition.

Syntax:

DELETE FROM <table_name> WHERE <condition>;

SELECT:

The select statement is used to query a database. This statement is used to retrieve the information from the database. The SELECT statement can be used in many ways. They are:

1. Selecting some columns:

To select specified number of columns from the table the Following command is used.

Syntax:

SELECT column_name FROM table_name;

2. Query All Columns:

To select all columns from the table * is used instead of column names.

Syntax:

SELECT * FROM table_name;

3. Select using DISTINCT:

The DISTINCT keyword is used to return only different values. This command does not select the duplicate values from the table.

Syntax:

SELECT DISTINCT column name(s) FROM table_name;

4. Select using IN:

If you want to get the rows which contain certain values, the best way to do it is to use the IN conditional expression.

Syntax:

SELECT column_name1, column_name2 FROM table_name WHERE column_name  IN (value1, value2,……,value-n);

5. Select using BETWEEN:

BETWEEN can be used to get those items that fall within a range.

Syntax:

SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2;

6. Renaming (Alias):

The select statement can be used to rename either a column or the entire table.

Syntax:

Renaming a column:

SELECT column_name AS new name FROM table_name;

Renaming a table:

SELECT column_name FROM table_name AS newname;

7. Sorting:

The select statement with the order by Clause is used to sort the contents Table either in ascending or descending order.

Syntax:

SELECT column_name FROM table_name WHERE Condition ORDER BY column_name ASC/DESC;

8. To select by matching some patterns:

The select statement along with like clause I is used to match strings. The like condition is used to specify a search pattern in a column.

Syntax:

SELECT column_name FROM table_name WHERE column_name LIKE "% or_";

%: Matches any sub string.

_ : Matches a single character.

9. To Select NULL values:

We can use the SELECT statement to select the ‘null’ values also. For retrieving rows where some of the columns have been defined as NULLs there is a special comparison operator of the form IS [NOT]NULL.

Syntax:

SELECT column_name FROM table_name WHERE column_name IS NULL;

10. Select using AND, OR, NOT:

We can combine one or more conditions in a SELECT statement using the logical operators AND, OR, NOT.

Syntax:

SELECT column_name FROM table_name WHERE Condition1 LOGICAL OPERATOR condition2;

Wildcard Characters in SQL Server:

SymbolDescriptionExample
%Represents zero or more charactersbl% finds bl, black, blue, and blob
_Represents a single characterh_t finds hot, hat, and hit
[]Represents any single character within the bracketsh[oa]t finds hot and hat, but not hit
^Represents any character not in the bracketsh[^oa]t finds hit, but not hot and hat
–Represents any single character within the specified rangec[a-b]t finds cat and cbt
  • SQL statement selects all customers with a City starting with “b”, “s”, or “p”:
    • SELECT * FROM Customers WHERE City LIKE ‘[bsp]%’;
  • The following SQL statement selects all customers with a City starting with “a”, “b”, or “c”:
    • SELECT * FROM Customers WHERE City LIKE ‘[a-c]%’;