27
MOJZA
TICKETS
TicketNo FirstName LastName BusNo Price Destination
BS405 Lara Lee BS12 20.50 New York
QS564 James Benjamin QS11 40.00 Las Vegas
RT567 Louis Martin RT10 32.99 Queens
BS506 James Lee BS12 20.50 New York
QT234 Walter White QT09 15.50 Florida
SQL
➜
SQL stands for Standard Query Language
➜It is for writing scripts to obtain useful informationfrom a database and display it
➜
SELECT → selects the fields that information needsto be displayed from
➜SELECT * → means that all fields are to be displayed
➜FROM → specifies the table in which that specificfield is present
➜WHERE → selects all records with specific conditionor data type
➜ORDER BY → displays data either in ascending ordescending order➜SUM →
takes the sum of a specific field, if the field is of real or integer data type➜COUNT →
counts how many times the record and field contains the data according to the
conditions given
Operator Description
= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
28
MOJZA
BETWEEN Between a range of two values
LIKE Search for a pattern
IN Specify multiple values
AND Specify multiple conditions where all conditions must be true
OR Specify multiple conditions where one or more conditions must be true
NOT Specify a condition that could be false
➜
An SQL command starts with the SELECT function
➜This is followed by the FROM function
➜The script then has the commands needed to obtainthe information
➜The last command of the script is ended with a semicolon
Example 1:
he following SQL query displays the first name of all people going to New Yo
T
or Florida in alphabetical order, their last names, and their ticket number from
the table TICKETS.
ELECT FirstName, LastName, TicketNo
S
FROM TICKETS
WHERE Destination = ‘New York’ OR ‘Florida’
ORDER BY FirstName;
This returns:
James Lee BS506
Lara Lee BS405
Walter White QT234
ote that the order in which the script has asked for the information (in this case,
N
first name, last name, and then ticket number) is very important, and so, it must be
displayed according to it
29
MOJZA
Example 2:
he following SQL query displays the prices of tickets, in descending order,
T
for buses that are either going to New York, Queens or Las Vegas, their bus
numbers, and their respective destinations.
ELECT Price, BusNo, Destination
S
FROM TICKETS
WHERE Destination = ‘New York’ OR ‘Queens’ OR ‘Las
Vegas’ ORDER BY Price DESC;
This returns:
40.00 QS11 Las Vegas
32.99 RT10 Queens
20.50 BS12 New York
20.50 BS12 New York
30
MOJZA
Unit 10: Boolean Logic