SQL Assessment Commands

Query Command Statement Examples

• SELECT• INSERT• AND• FROM• IF• WHERE• AS• OR• DELETE• DROP• ALTER

Datatype Examples

• CHAR

• VARCHAR

• INT

• MEDIUMINT

• TEXT

• BLOB

• MEDIUMBLOB

SELECT Command Statement

• The SELECT statement is used to select data from a database

Examples:

The following SQL statement selects the "CustomerName" and "City" columns from the "Customers" table:

SELECT CustomerName, City FROM Customers;

The following SQL statement selects all the columns from the "Customers" table:

SELECT * FROM Customers;

WHERE Command Statement

• The WHERE statement is used to filter records. The WHERE clause is used to extract records that fulfil a specified criteria only.

Examples:

The following SQL statement selects all the customers from the country "Mexico", in the "Customers" table:

SELECT * FROM CustomersWHERE Country='Mexico’;

However, numeric fields should not be enclosed in quotes:

SELECT * FROM CustomersWHERE CustomerID=1;

WHERE Clause Operators

Operator Description

= Equal

<> Not Equal

> Greater than

< Less than

>= Greater than or equal

<= Less than or equal

BETWEEN Between an inclusive range

LIKE Search for a pattern

IN To specify multiple possible values for a column

AND Command Statement

• The AND and OR operators are used to filter records based on more than one condition:• The AND operator displays a record if all the conditions separated by AND is TRUE:

Example:

The following SQL statement selects all fields from "Customers" where country is "Germany" AND city is "Berlin":

SELECT * FROM CustomersWHERE Country='Germany' AND City='Berlin';

OR Command Statement

• The AND and OR operators are used to filter records based on more than one condition:• The OR operator displays a record if any of the conditions separated by OR is TRUE.

Examples:

The following SQL statement selects all fields from "Customers" where city is "Berlin" OR "München":

SELECT * FROM CustomersWHERE City='Berlin' OR City='München';

NOT Command Statement

• The NOT operator displays a record if the condition(s) is NOT TRUE:

Examples:

The following SQL statement selects all fields from "Customers" where country is NOT "Germany":

SELECT * FROM CustomersWHERE NOT Country='Germany';

Combining AND, OR and NOT operators

• You can also combine the AND, OR and NOT operators:

Examples:

The following SQL statement selects all fields from "Customers" where country is "Germany" AND city must be "Berlin" OR "München":

SELECT * FROM CustomersWHERE Country='Germany' AND (City='Berlin' OR City='München’);

The following SQL statement selects all fields from "Customers" where country is NOT "Germany" and NOT "USA":

SELECT * FROM CustomersWHERE NOT Country='Germany' AND NOT Country='USA';

INSERT INTO Command Statement

• The INSERT INTO statement is used to insert new records in a table:

Syntax:

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

Examples:

The following SQL statement inserts a new record in the "Customers" table:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

INSERT INTO only specified table columns

• It is also possible to only insert data in specific columns.

Examples:

The following SQL statement will insert a new record, but only insert data in the "CustomerName", "City", and "Country" columns:

INSERT INTO Customers (CustomerName, City, Country)VALUES ('Cardinal', 'Stavanger', 'Norway’);

SQL NULL Values

• A field with a NULL value is a field with no value.

Note:

A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation.

UPDATE Command Statement

• The UPDATE statement is used to modify the existing records in a table.

Example:

The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city (Alfred and Frankfurt are new values):

UPDATE CustomersSET ContactName = 'Alfred Schmidt', City= 'Frankfurt'WHERE CustomerID = 1;

UPDATE Multiple Records

• It is the WHERE clause that determines how many records that will be updated:

Example:The following SQL statement will update the contactname to "Juan" for all records where country is "Mexico":

UPDATE CustomersSET ContactName='Juan'WHERE Country='Mexico’;

Note: Missing out the WHERE statement will alter every value under SET

DELETE Command Statement

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

Example:The following SQL statement deletes the customer “John Smith" from the "Customers" table:

DELETE FROM CustomersWHERE CustomerName=‘John Smith’;

Note: To delete all from a table, use:

DELETE * FROM table_name;

CREATE DATABASE Command Statement

• The CREATE DATABASE statement is used to create a new SQL database:

Syntax:

CREATE DATABASE databasename;

Example:

CREATE DATABASE testDB;

DROP DATABASE Command Statement

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

Syntax:

DROP DATABASE databasename;

Example:

DROP DATABASE testDB;

CREATE TABLE Command Statement

• The CREATE TABLE statement is used to create a new table in a database:

Syntax:

CREATE TABLE table_name (column1 datatype,column2 datatype,column3 datatype,

....);

CREATE TABLE continued…

Example:

CREATE TABLE testtable (PersonID int,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varchar(255)

);

DROP TABLE Command Statement

• The DROP TABLE statement is used to drop an existing table in a database.

Syntax:

DROP TABLE table_name;

Example:

DROP TABLE testtable;

TRUNCATE TABLE Command Statement

• The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE testtable;

ALTER TABLE Command Statement

• 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.

Syntax:

To add a column in a table, use the following syntax:

ALTER TABLE table_nameADD column_name datatype;

ALTER TABLE continued…

Syntax:

To delete a column in a table, use the following syntax:

ALTER TABLE table_nameDROP COLUMN column_name;

Examples:

ALTER TABLE testtableADD DateOfBirth date;

ALTER TABLE continued…

Examples:The following deletes the column named "DateOfBirth" in the “testtable" table:

ALTER TABLE PersonsDROP COLUMN DateOfBirth;

The following changes the data type of the column named "DateOfBirth" in the “testtable" table (date to year):

ALTER TABLE PersonsALTER COLUMN DateOfBirth year;

SQL Constraints Examples

• SQL constraints are used to specify rules for the data in a table:

• The following constraints are commonly used in SQL:• NOT NULL - Ensures that a column cannot have a NULL value• UNIQUE - Ensures that all values in a column are different• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely

identifies each row in a table• FOREIGN KEY - Uniquely identifies a row/record in another table• CHECK - Ensures that all values in a column satisfies a specific condition• DEFAULT - Sets a default value for a column when no value is specified• INDEX - Used to create and retrieve data from the database very quickly

PRIMARY KEY Constraint• The PRIMARY KEY constraint uniquely identifies each record in a database

table.

• Primary keys must contain UNIQUE values, and cannot contain NULL values.

PRIMARY KEY on CREATE TABLE:

CREATE TABLE testtable (ID int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Age int,PRIMARY KEY (ID)

);

PRIMARY KEY continued…

PRIMARY KEY on ALTER TABLE:

ALTER TABLE testtableADD PRIMARY KEY (ID);

DROP a PRIMARY KEY:

ALTER TABLE testtableDROP PRIMARY KEY;

FOREIGN KEY Constraint

• A FOREIGN KEY is a key used to link two tables together.• A FOREIGN KEY is a field (or multiple) in one table that refers to the

PRIMARY KEY in another table:

FOREIGN KEY on CREATE TABLE:CREATE TABLE Orders (

OrderID int NOT NULL,OrderNumber int NOT NULL,PersonID int,PRIMARY KEY (OrderID),FOREIGN KEY (PersonID) REFERENCES testtable(PersonID)

);

FOREIGN KEY continued…

FOREIGN KEY on ALTER TABLE:

ALTER TABLE OrdersADD FOREIGN KEY (PersonID) REFERENCES testtable(PersonID);

DROP a FOREIGN KEY:

ALTER TABLE OrdersDROP FOREIGN KEY FK_PersonOrder;