SQL SELECT AS Alias

The AS keyword is used to give columns or tables a

temporary name that can be used to identify that
column or table later.

SELECT first_name AS name FROM Customers;

Here, the SQL command selects the first_name column

from the Customers table. However, the column name
is changed to name in the result set.

1
 SQL SELECT AS Alias
 Examples

 SELECT first_name AS name FROM Customer;

 SELECT customer_id AS cid, first_name AS name

FROM Customer;

2
SQL SELECT AS Alias
 We can combine data from multiple columns and
represent it in a single column

 SELECT first_name &" " & last_name AS full_name

FROM Customer;

3
SQL Table Alias
 The AS keyword can also be used to give temporary
names to tables. For example,

 SELECT cu.first_name, cu.last_name

FROM Customer AS cu;

4
SQL IN and NOT IN Operators

5
SQL IN Operator
 We use the IN operator with the WHERE clause to
match values in a list.
 SQL IN Syntax
 SELECT column1, column2, ... FROM table WHERE
column IN (value1, value2, ...);

6
SQL IN Operator
Example
-- select customers from the USA
 SELECT first_name, country FROM Customer
WHERE country IN ('USA');
-- select customers from the USA & UK

SELECT first_name, country

FROM Customer
WHERE country IN ('USA', 'UK');
7
SQL NOT IN Operator

 The NOT IN operator excludes the rows that match

values in the list. It returns all the rows except the
excluded rows.

 -- select rows where country is not in UK or UAE

 SELECT first_name, country

FROM Customer
WHERE country NOT IN ('UK', 'UAE');

8
SQL LIKE and NOT LIKE Operators

9
SQL LIKE

 We use the SQL LIKE operator with

the WHERE clause to get a result set that matches the
given string pattern.

 SQL LIKE Syntax

 SELECT column1, column2, ... FROM table WHERE
column LIKE value;

10
SQL LIKE

 Example:

 -- select customers who live in the UK

 SELECT *
FROM Customer
WHERE country LIKE 'UK';

11
SQL LIKE

The SQL LIKE query is often used with the *

to match a pattern of a string.
For example,
-- select customers whose -- last name starts with R

 SELECT *
FROM Customer
WHERE last_name LIKE "*R*";

12
SQL NOT LIKE Operator

 Syntax
 SELECT column1, column2, ... FROM table_name
WHERE column NOT LIKE value;

 For example,
 -- select customers who don't live in the USA

 SELECT *
 FROM Customer
 WHERE country NOT LIKE 'USA';
13
Aggregate functions

 SQL MAX() and MIN()

 The MAX() function returns the maximum value of a

column.
 The MIN() function returns the minimum value of a
column.

 SELECT MAX(age) as max_age FROM Customer;

 SELECT MIN(age) as min_age FROM Customer;

14
Aggregate functions
 SQL COUNT()
 COUNT() is the function to count the number of rows
in a table

 Example:
 --returns the number of rows in the Customers table
 SELECT COUNT(*)
FROM Customer;

15
Aggregate functions
Example: Specify Column to Count
--returns the count of non-null values in the age column
SELECT COUNT (age) FROM Customer;

 COUNT() With WHERE

 SELECT COUNT(country)
 FROM Customer
 WHERE country = 'UK';

16
Aggregate functions
 SQL SUM() AND AVG()
 In SQL, the SUM() and AVG() functions are used to
calculate total and average values in numeric columns.

SELECT SUM (age) AS total_age
FROM customer;

17
Aggregate functions
Here, the SQL command returns the total amount to be
paid by the customer having id 4.

 SELECT SUM (age) AS total

 FROM customer
 WHERE cid='4';

18
Aggregate functions
 -- get average age of customers

 SELECT AVG(age) AS average_age

 FROM Customer;

19
SQL ORDER BY Clause

 The ORDER BY clause in SQL is used to sort the result

set in ascending or descending order.

 -- orders all rows from Customers in ascending order

 by country
 SELECT * FROM Customer ORDER BY age;

 SELECT *
FROM Customer
ORDER BY last_name;
20
SQL ORDER BY Clause
 ORDER BY ASC (Ascending Order)
 -- orders all rows from Customers in ascending order
by age
 SELECT * FROM Customer ORDER BY age ASC;

 ORDER BY DESC (Descending Order)

 -- order all rows from Customers in descending order
 by age
 SELECT * FROM Customer ORDER BY age DESC;

21
SQL GROUP BY

 In SQL, we use the GROUP BY clause to group rows

based on the value of columns.
 Example:
/** count the number of customers in each country**/

SELECT country, COUNT(country) AS NO

FROM Customer
GROUP BY country;

22
GROUP BY With Multiple Columns
 GROUP BY can also be used to group rows based on
multiple columns.
 For example,
 /** count the number of customers in each country
 count the number of customers in each age**/

SELECT age, COUNT(age) AS diffage, country,

COUNT(country) AS NO
FROM Customer
GROUP BY country, age;

23
GROUP BY With HAVING Clause
 We can use the GROUP BY clause with the HAVING
clause to filter the result set based on aggregate functions.
 For example,
 select the customer_id count and country column from
Customers group by country if the count is greater than 1

SELECT count(cid), country

FROM customer
GROUP BY country
HAVING count(cid) > 1;

24
GROUP BY & ORDER BY
 The following SQL statement lists the number of
customers in each country, sorted high to low:

SELECT COUNT(cid) AS CN, Country

FROM customer
GROUP BY Country
ORDER BY COUNT(cid) DESC;

25
SQL UNION

 In SQL, the UNION operator selects fields from two or

more tables.
 To use UNION in SQL, we must always remember,
 The column count in all tables must be the same. For
example, both the Teachers and Students tables have three
columns.
 The data type of columns must be the same. For example,
the age column in both the Teachers and Students table is
integer.
 The columns must be in the same order in each table. For
example, the order of columns is id-name-age in
both Teachers and Students tables.
26
Example: SQL UNION

 -- select the union of age columns from two tables

Teachers and Students
 SELECT age
FROM teacher
UNION/ UNION ALL
SELECT age
FROM student;
 UNION: only keeps unique records.
 UNION ALL: keeps all records, including duplicates.

27