SQL Lesson and Functions

Content of the lesson:

SQL and Functions

We are going to learn several new commands and functions which can be combined with the limited SELECT commands. All new functions will be explained using examples, you will need the renome database which was used in the previous lessons. The whole database can be downloaded in this file: databaze.txt.

Command LIMIT

The command LIMIT can be used in situation when you have a large table and you want to display only a limited number of results (for example the first ten results), or if you want to separate your results into multiple pages (for example 10 articles per each page) - in this case you want to display 10 records from the N-th item in the database.

     

general syntax:

LIMIT od, pocet

LIMIT pocet



output of the first 10 records:

LIMIT 10

lze zapsat i jako LIMIT 0, 10



output of the records 11 - 20:

LIMIT 10, 10

The first example which is listed below this text will display the 10 most often read articles with the number of views (the command ORDER BY viewedcount DESC will sort data according to the number of views (descending) and the command LIMIT 10 will output the first 10 records).

SELECT heading, viewedcount

FROM renome_tarticle

ORDER BY viewedcount DESC

LIMIT 10

The first 10 most read articles are displayed. Now we want to display the following 10 articles, that means the 11th - 20th most read article. We only have to adjust the command LIMIT and extend it by one numeric parameter (LIMIT from, count) thus LIMIT 10, 10.

SELECT heading, viewedcount

FROM renome_tarticle

ORDER BY viewedcount DESC

LIMIT 10, 10

Command LIKE

The command LIKE is most often used in the queries SELECT and with the command WHERE which is used to select records according to a condition. In case that you search for text data and use the equal operator, you have to specify a specific text. However, you might want to set the condition more generally (for example articles beginning with the letter P or articles which contain a particular link). You can use the command LIKE for this purpose - there is the name of field on the left side and the expression which defines the searched value on the right side. You can use all of the following special characters:

After the list of basic options of syntax you can see several examples. The first one can be used to choose articles which begin with the letter P.

SELECT heading

FROM renome_tarticle

WHERE heading LIKE 'P%'

The second example can be used to choose articles which contain any link in the text (that means HTML tag a with set parameter href) - we search for a text which contains the word href.

SELECT heading

FROM renome_tarticle

WHERE text LIKE '%href%'

Aggregation Functions and Command GROUP BY

Aggregation Functions

Aggregation Functions can be used to work with values in a particular set of records. The most often used functions are listed in the following table:

The following two examples demonstrate usage of the function AVG() to compute the arithmetic mean of views for displayed articles and the function SUM() to find out the total number of views for all displayed articles. Using any of the other functions is based on the same principle.

SELECT AVG(viewedcount)

FROM renome_tarticle

SELECT SUM(viewedcount)

FROM renome_tarticle

Command GROUP BY

The aggregation functions which were described in the previous paragraph are commonly used with the command GROUP BY. This command groups values according to a criterium. Then you can use the aggregation functions on every grouped value for example to find out the number of values in each category.

SELECT name, COUNT(*)

FROM renome_tarticle

INNER JOIN renome_ccathegory ON renome_tarticle.idccathegory = renome_ccathegory.idccathegory

GROUP BY renome_tarticle.idccathegory

Functions for working with date and time

The first example displays the list of articles and their age in days. To find out the age in days we used the function DATEDIFF which finds out the difference between the current date (function CURDATE) and the date listed inside the column createdate.

SELECT heading, createdate, 

DATEDIFF(CURDATE(), createdate)

FROM renome_tarticle

The second query selects articles from the September 2009 only. The functions for working with date and time are used inside the condition WHERE which uses the month and year from the column createdate using the functions MONTH and YEAR. The month is compared with the value 9 (September) and the year with the value 2009. To connect these two comparisons the AND logical operator is used.

SELECT heading, createdate 

FROM renome_tarticle 

WHERE (MONTH(createdate)=9 and YEAR (createdate)=2009)

Additional Texts

Questions

1. What is the purpose of the SQL command GROUP BY and which aggregation functions do you know?
2. How can the command LIMIT be used along with the command SELECT?
3. Which SQL functions for working with date and time do you know?