SQL Assessment Commands

Query Command StatementExamples

• SELECT
• INSERT
• AND
• FROM
• IF
• WHERE
• OR
• DELETE
• DROP
• ALTER
 SELECT Command Statement
• The SELECTstatement is used to select data from adatabase

Examples:
The following SQLstatement selects the "CustomerName" and "City" columns from
the "Customers" table:

SELECTCustomerName, City FROMCustomers;

The following SQLstatement selects all the columns from the "Customers"table:

SELECT* FROM Customers;

 WHERE Command Statement
• The WHEREstatement is used to filter records. The WHEREclause is used to extract
records that fulfil a specified criteriaonly.

Examples:
The following SQLstatement selects all the customers from the country "Mexico", in the
"Customers" table:

SELECT* FROMCustomers
WHERECountry='Mexico’;

However, numeric fields should not be enclosed in quotes:

SELECT* FROMCustomers
WHERE CustomerID=1;
 WHERE Clause Operators
Operator Description

= Equal
<> Not Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than orequal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN Tospecify multiple possible values for a column
 AND CommandStatement
• The AND and ORoperators are used to filter records based on more than
one condition:
• The AND operator displays a record if all the conditions separated by AND isTRUE:

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

SELECT* FROM Customers

WHERECountry='Germany' ANDCity='Berlin';
 OR Command Statement
• The AND and ORoperators are used to filter records based on more than
one condition:
• The ORoperator displays a record if any of the conditions separated by ORis TRUE.

Examples:
The following SQLstatement selects all fields from "Customers" where cityis
"Berlin" OR"München":

SELECT* FROM Customers

WHERECity='Berlin' ORCity='München';
 NOT Command Statement
• The NOToperator displays a record if the condition(s) is NOTTRUE:

Examples:
The following SQLstatement selects all fields from "Customers" where
country is NOT"Germany":

SELECT* FROMCustomers
WHERENOTCountry='Germany';
 Combining AND, OR and NOT operators
• You can also combine the AND, ORand NOToperators:

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

SELECT* FROM Customers

WHERECountry='Germany' AND (City='Berlin' ORCity='München’);

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

SELECT* FROM Customers

WHERENOTCountry='Germany' AND NOTCountry='USA';
 INSERT INTO CommandStatement
• The INSERTINTO statement is used to insert new records in a table:

Syntax:
INSERTINTO table_name (column1, column2, column3, ...)
VALUES(value1, value2, value3, ...);

Examples:
The following SQLstatement inserts a new record in the "Customers" table:

INSERTINTO Customers (CustomerName, ContactName, Address, City, PostalCode,

Country)
VALUES('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
 INSERT INTO only specified tablecolumns
• It is also possible toonly insert data in specific columns.

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

INSERTINTO Customers (CustomerName , City, Country)

VALUES('Cardinal', 'Stavanger', 'Norway’);
 SQL NULL Values
• A field with a NULLvalue 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 UPDATEstatement is used to modify the existing records in a table.

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

UPDATECustomers
SETContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERECustomerID =1;
 UPDATE Multiple Records
• It is the WHEREclause that determines how many records that will be
updated:

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

UPDATECustomers
SETContactName='Juan'
WHERECountry='Mexico’;

Note: Missing out the WHERE statement will alter every value under SET
 DELETE Command Statement
• The DELETEstatement is used to delete existing records in atable.

Example:
The following SQLstatement deletes the customer “John Smith" from the
"Customers" table:
DELETE FROM
DCustomers
WWHERECustomerName=‘John
Smith’;
Note: To delete all from a table, use:

DELETE* FROMtable_name;
 CREATE DATABASE Command Statement
• The CREATEDATABASEstatement is used to create a new SQL
database:

Syntax:
CREATEDATABASE databasename;

Example:
CREATEDATABASEtestDB;
 DROP DATABASE CommandStatement
• The DROPDATABASEstatement is used to drop an existing SQLdatabase:

Syntax:

DROPDATABASEdatabasename;

Example:

DROPDATABASEtestDB;
 CREATE TABLE Command Statement
• The CREATETABLEstatement is used to create a new table in a
database:

Syntax:
CREATETABLEtable_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
 CREATE TABLEcontinued…
Example:
CREATETABLEtesttable (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
 DROP TABLE CommandStatement
• The DROPTABLEstatement is used to drop an existing table in a
database.

Syntax:
DROPTABLEtable_name;

Example:
DROPTABLEtesttable;
 TRUNCATE TABLE Command Statement
• The TRUNCATETABLEstatement is used to delete the data inside a
table, but not the tableitself.

Syntax:
TRUNCATETABLEtable_name;

Example:
TRUNCATETABLEtesttable;
 ALTER TABLE Command Statement
• The ALTERTABLEstatement is used to add, delete, or modify columns
in an existing table.
• The ALTERTABLEstatement is also used to add and drop various
constraints on an existing table.

Syntax:
To add a column in a table, use the followingsyntax:

ALTERTABLEtable_name
ADD column_namedatatype;
 ALTER TABLEcontinued…
Syntax:
To delete a column in a table, use the following syntax:

ALTERTABLEtable_name
DROPCOLUMNcolumn_name;

Examples:

ALTERTABLEtesttable
ADD DateOfBirth date;
 ALTER TABLEcontinued…
Examples:
The following deletes the column named "DateOfBirth" in the “testtable"
table:

ALTERTABLEPersons
DROPCOLUMNDateOfBirth;

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

ALTERTABLEPersons
ALTERCOLUMN DateOfBirth year;
 SQL Constraints Examples
• SQLconstraints are used to specify rules for the data in atable:

• The following constraints are commonly used in SQL:

• NOT NULL - Ensures that a column cannot have a NULLvalue
• UNIQUE - Ensures that all values ina column are different
• PRIMARY KEY - A combination of a NOTNULL and UNIQUE. Uniquely
identifies each row in atable
• FOREIGN KEY - Uniquely identifies a row/record in anothertable
• CHECK - Ensures that all values in a column satisfies a specificcondition
• DEFAULT - Sets a default value for a column when no value isspecified
• INDEX - Used to create and retrieve data from the database very quickly
 PRIMARY KEYConstraint
• The PRIMARY KEYconstraint uniquely identifies each record in adatabase
table.
• Primary keys must contain UNIQUE values, and cannot contain NULL
values.

PRIMARY KEY on CREATETABLE:

CREATETABLEtesttable (
ID int NOTNULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY(ID)
);
 PRIMARY KEYcontinued…
PRIMARY KEY on ALTERTABLE:
ALTERTABLEtesttable
ADD PRIMARY KEY(ID);

DROP a PRIMARY KEY:

ALTERTABLEtesttable
DROPPRIMARYKEY;
 FOREIGN KEYConstraint
• A FOREIGN KEYis a key used to link two tables together.
• A FOREIGNKEYis a field (or multiple) in one table that refers to the
PRIMARY KEYin another table:

FOREIGN KEY on CREATETABLE:

CREATETABLEOrders (
OrderID int NOTNULL,
OrderNumber int NOTNULL,
PersonID int,
PRIMARY KEY(OrderID),
FOREIGNKEY(PersonID) REFERENCEStesttable(PersonID)
);
 FOREIGN KEYcontinued…
FOREIGN KEY on ALTERTABLE:
ALTERTABLEOrders
ADD FOREIGNKEY(PersonID) REFERENCEStesttable(PersonID);

DROP a FOREIGN KEY:

ALTERTABLEOrders
DROPFOREIGNKEYFK_PersonOrder;