Computer Science Paper 2: SQL: Structured Query Language *** Miss Nafeeza COAN
SQL: Structured Query Language
SQL is a standard language for accessing and manipulating databases.
What is SQL?
SQL stands for Structured Query Language
SQL lets you access and manipulate databases
What Can SQL do?
SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
NCOAN 59253887 Di pl oma Infor mation Systems / Bsc (H ons) Busi ness
Infor mati cs / Post Gr aduate C erti fi cate C omputer Sci ence/ Post Graduate
C er ti fi cate C omputer Educati on / MSc Pr oject Manag ement
1
Computer Science Paper 2: SQL: Structured Query Language *** Miss Nafeeza COAN
Database Tables
A database most often contains one or more tables. Each table is identified by a
name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.
Below is a selection from the "Customers" table
The above table consists of 7 Fields.
Primary Key: Customer ID
What is a Primary Key? It is a unique record identifier.
The above table consists of 5 Records.
NCOAN 59253887 Di pl oma Infor mation Systems / Bsc (H ons) Busi ness
Infor mati cs / Post Gr aduate C erti fi cate C omputer Sci ence/ Post Graduate
C er ti fi cate C omputer Educati on / MSc Pr oject Manag ement
2
Computer Science Paper 2: SQL: Structured Query Language *** Miss Nafeeza COAN
SQL Script … SQL Language
The following SQL statement selects all the records in the "Customers" table
SELECT * FROM Customers;
The SQL SELECT Statement
SELECT Column Example
The following SQL statement selects the "CustomerName" and "City" columns
from the "Customers" table. Output both columns only.
SELECT CustomerName, City FROM Customers;
The following SQL statement selects all the customers from the country
"Mexico", in the "Customers" table
SELECT * FROM Customers
WHERE Country='Mexico';
NCOAN 59253887 Di pl oma Infor mation Systems / Bsc (H ons) Busi ness
Infor mati cs / Post Gr aduate C erti fi cate C omputer Sci ence/ Post Graduate
C er ti fi cate C omputer Educati on / MSc Pr oject Manag ement
3
Computer Science Paper 2: SQL: Structured Query Language *** Miss Nafeeza COAN
NCOAN 59253887 Di pl oma Infor mation Systems / Bsc (H ons) Busi ness
Infor mati cs / Post Gr aduate C erti fi cate C omputer Sci ence/ Post Graduate
C er ti fi cate C omputer Educati on / MSc Pr oject Manag ement
4
Computer Science Paper 2: SQL: Structured Query Language *** Miss Nafeeza COAN
The SQL AND & OR Operators
AND Operator Example
The following SQL statement selects all customers from the country "Germany"
AND the city "Berlin", in the "Customers" table
SELECT * FROM Customers
WHERE Country='Germany'
AND City='Berlin';
OUTPUT:
NCOAN 59253887 Di pl oma Infor mation Systems / Bsc (H ons) Busi ness
Infor mati cs / Post Gr aduate C erti fi cate C omputer Sci ence/ Post Graduate
C er ti fi cate C omputer Educati on / MSc Pr oject Manag ement
5
Computer Science Paper 2: SQL: Structured Query Language *** Miss Nafeeza COAN
OR Operator Example
The following SQL statement selects all customers from the city "Berlin" OR in
the Country "Mexico", in the "Customers" table
SELECT * FROM Customers
WHERE City='Berlin'
OR Country ='Mexico';
OUTPUT
Combining AND & OR
You can also combine AND and OR (use parenthesis to form complex
expressions).
The following SQL statement selects all customers from the country "Germany"
AND the city must be equal to "Berlin" OR "München", in the "Customers" table
NCOAN 59253887 Di pl oma Infor mation Systems / Bsc (H ons) Busi ness
Infor mati cs / Post Gr aduate C erti fi cate C omputer Sci ence/ Post Graduate
C er ti fi cate C omputer Educati on / MSc Pr oject Manag ement
6
Computer Science Paper 2: SQL: Structured Query Language *** Miss Nafeeza COAN
SELECT * FROM Customers
WHERE Country='Germany'
AND (City='Berlin' OR City='München');
NCOAN 59253887 Di pl oma Infor mation Systems / Bsc (H ons) Busi ness
Infor mati cs / Post Gr aduate C erti fi cate C omputer Sci ence/ Post Graduate
C er ti fi cate C omputer Educati on / MSc Pr oject Manag ement
7
Computer Science Paper 2: SQL: Structured Query Language *** Miss Nafeeza COAN
The SQL ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set by one or more columns.
The ORDER BY keyword sorts the records in ascending order by default. To sort
the records in a descending order, you can use the DESC keyword
The following SQL statement selects all customers from the "Customers" table,
sorted by the "Country" column
SELECT * FROM Customers
ORDER BY Country;
ORDER BY DESC Example
The following SQL statement selects all customers from the "Customers" table,
sorted DESCENDING by the "Country" column
SELECT * FROM Customers
ORDER BY Country DESC;
NCOAN 59253887 Di pl oma Infor mation Systems / Bsc (H ons) Busi ness
Infor mati cs / Post Gr aduate C erti fi cate C omputer Sci ence/ Post Graduate
C er ti fi cate C omputer Educati on / MSc Pr oject Manag ement
8
Computer Science Paper 2: SQL: Structured Query Language *** Miss Nafeeza COAN
ORDER BY Several Columns Example
The following SQL statement selects all customers from the "Customers" table,
sorted by the "Country" and the "CustomerName" column:
SELECT * FROM Customers
ORDER BY Country, CustomerName;
*Sorting will be done in ascending order. (default
sort)
NCOAN 59253887 Di pl oma Infor mation Systems / Bsc (H ons) Busi ness
Infor mati cs / Post Gr aduate C erti fi cate C omputer Sci ence/ Post Graduate
C er ti fi cate C omputer Educati on / MSc Pr oject Manag ement
9
Computer Science Paper 2: SQL: Structured Query Language *** Miss Nafeeza COAN
ORDER BY Several Columns Example 2
The following SQL statement selects all customers from the "Customers" table,
sorted ascending by the "Country" and descending by the "CustomerName"
column:
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
ASC: Ascending order
DESC: Descending order
NCOAN 59253887 Di pl oma Infor mation Systems / Bsc (H ons) Busi ness
Infor mati cs / Post Gr aduate C erti fi cate C omputer Sci ence/ Post Graduate
C er ti fi cate C omputer Educati on / MSc Pr oject Manag ement
10
Computer Science Paper 2: SQL: Structured Query Language *** Miss Nafeeza COAN
SUM
Table Name: product
If you want to sum values stored in one column, use SUM() with that column’s name as the
argument. Look at the example below:
SELECT SUM(quantity) AS sum_quantity
FROM product;
In this query, we use SUM() alone in the SELECT statement. The SUM() function adds all
values from the quantity column and returns the total as the result of the function. The name of
the new result column (i.e. the alias) is sum_quantity.
Here’s the result (output):
sum_quantity
NCOAN 59253887 Di pl oma Infor mation Systems / Bsc (H ons) Busi ness
Infor mati cs / Post Gr aduate C erti fi cate C omputer Sci ence/ Post Graduate
C er ti fi cate C omputer Educati on / MSc Pr oject Manag ement
11
Computer Science Paper 2: SQL: Structured Query Language *** Miss Nafeeza COAN
As you see, the sum of values in the quantity column in the table product is 7. If one of the rows
has a NULL in this column, SUM() omits this row from the calculation. Note that there is only
one row in the result: all the individual rows were “collapsed” by SUM() – their details are not
available in the result.
COUNT
The COUNT() function returns the number of rows that matches a specified criterion.
Table Name: customers
Enter the following SELECT statement that uses the COUNT function:
Try It
SELECT COUNT(customer_id)
FROM customers;
COUNT(customer_id)
NCOAN 59253887 Di pl oma Infor mation Systems / Bsc (H ons) Busi ness
Infor mati cs / Post Gr aduate C erti fi cate C omputer Sci ence/ Post Graduate
C er ti fi cate C omputer Educati on / MSc Pr oject Manag ement
12
Computer Science Paper 2: SQL: Structured Query Language *** Miss Nafeeza COAN
Example 2: COUNT
Table Name: employees
Try It
SELECT COUNT(*) AS total
FROM employees
WHERE salary > 50000;
OUTPUT
total
In this example, we will return the number of employees who have a salary above $50,000.
We've aliased the COUNT(*) as total to make our query results more readable. Now, total will
display as the column heading when the result set is returned.
NCOAN 59253887 Di pl oma Infor mation Systems / Bsc (H ons) Busi ness
Infor mati cs / Post Gr aduate C erti fi cate C omputer Sci ence/ Post Graduate
C er ti fi cate C omputer Educati on / MSc Pr oject Manag ement
13
