1
00:00:00,000 --> 00:00:06,849
[MUSIC]

2
00:00:06,849 --> 00:00:08,692
Hello and welcome.

3
00:00:08,692 --> 00:00:13,254
In this video, we'll briefly present a few useful expressions that

4
00:00:13,254 --> 00:00:15,625
are used with select statements.

5
00:00:15,625 --> 00:00:20,496
The first one is COUNT, COUNT is a built-in database function that

6
00:00:20,496 --> 00:00:24,869
retrieves the number of rows that match the query criteria.

7
00:00:24,869 --> 00:00:29,740
For example, get the total number of rows in a given table,

8
00:00:29,740 --> 00:00:32,865
select COUNT(*) from tablename.

9
00:00:32,865 --> 00:00:38,083
Let's say you create a table called MEDALS which has a column called COUNTRY, and

10
00:00:38,083 --> 00:00:43,234
you want to retrieve the number of rows where the medal recipient is from Canada.

11
00:00:43,234 --> 00:00:46,330
You can issue a query like this:

12
00:00:46,330 --> 00:00:52,325
Select COUNT(COUNTRY) from MEDALS where COUNTRY='CANADA.'

13
00:00:52,325 --> 00:00:55,890
The second expression is DISTINCT.

14
00:00:55,890 --> 00:01:00,397
DISTINCT is used to remove duplicate values from a result set.

15
00:01:00,397 --> 00:01:04,797
Example, to retrieve unique values in a column,

16
00:01:04,797 --> 00:01:08,900
select DISTINCT columnname from tablename.

17
00:01:08,900 --> 00:01:11,635
In the MEDALS table mentioned earlier,

18
00:01:11,635 --> 00:01:15,501
a country may have received a gold medal multiple times.

19
00:01:15,501 --> 00:01:20,954
Example, retrieve the list of unique countries that received gold medals.

20
00:01:20,954 --> 00:01:25,888
That is, removing all duplicate values of the same country.

21
00:01:25,888 --> 00:01:32,512
Select DISTINCT COUNTRY from MEDALS where MEDALTYPE = 'GOLD'.

22
00:01:32,512 --> 00:01:35,822
The third expression is LIMIT, LIMIT is used for

23
00:01:35,822 --> 00:01:40,034
restricting the number of rows retrieved from the database.

24
00:01:40,034 --> 00:01:44,725
Example, retrieve just the first 10 rows in a table.

25
00:01:44,725 --> 00:01:48,741
Select * from tablename LIMIT 10.

26
00:01:48,741 --> 00:01:54,028
This can be very useful to examine the results set by looking at just a few

27
00:01:54,028 --> 00:01:59,508
rows instead of retrieving the entire result set which may be very large.

28
00:01:59,508 --> 00:02:05,942
Example, retrieve just a few rows in the MEDALS table for a particular year.

29
00:02:05,942 --> 00:02:12,128
Select * from MEDALS where YEAR = 2018 LIMIT 5.

30
00:02:12,128 --> 00:02:17,406
In this video we looked at some useful expressions that are used with select

31
00:02:17,406 --> 00:02:22,960
statements, namely the COUNT, DISTINCT, and LIMIT built-in functions.

32
00:02:22,960 --> 00:02:25,130
Thanks for watching this video.

33
00:02:25,130 --> 00:02:30,325
[MUSIC]