Function COUNT, SUM and AVG

MySQL have some predefined functions, which can be called using SQL statements. This are some basic mathematical functions such as COUNT, SUM, AVG, MAX and MIN functions.

Function COUNT

COUNT function returned the total number of data retrieved by a given SQL statement. The syntax to count the data is:

The example of the SQL statement is shown in Figure 1:

Figure 1: Using COUNT() to calculate the number of data in table "students".

In Figure 1, the first SQL statement shows the all the data in the table students, while the second statement shows output of COUNT(*) functions, which is the total number of data in the table.

Function SUM

SUM is a summation function, which is a mathematical formula to add all the value together. In SQL statement. The syntax to perform SUM function for a column is:

The SUM function can only perform for numerical column. Column that is text or varchar cannot perform such function. Figure 2 shows database administrator sum the intake years for the students table.

Figure 2: Summation for intake year in students table.

When user attempts to sum the value of a column that consists varchar or text, the SQL statements will returns value "0" and warnings for each of the data containing none numerical data. Figure 3 below shows the attempts of summing column that content none-numerical value.

Figure 3: Output for Summing none numerical data in MySQL / Maria DB.

Function AVG

AVG is the acronyms for "AVERAGE". The AVG function is used to calculate the average value for each of the value in the column. The operation add all numerical data from the column and divide by the number of data to output the average value for a particular column in the table. Figure 4 below shows the SQL statements to calculate the average intake year for the students in the table students.

Figure 4: Calculating average year for each student in table students.

Similar with summation function, the AVG function cannot process none-numerical data in the column. If there exist one or many none-numerical data in the column, the SQL statement will skip these column and only sum the column with numerical data. However, the AVG function still divide the summed value with the total row of data available.

In Figure 5 below, the student email for student with stdid "123456" is changed to value 12. After that the database administrator attempts to calculate the average value for the column "stdemail". The results show value "3", which is the value of "12" divided by 4 column of data.

Figure 5: Performing AVG function with none-numerical column.

Excluding data value in filtering

NOT operator is used to exclude certain value from the retrieved data. This operator is in syntax as below:

The example of how the operator can be used is shown in Figure 1 below:

Figure 1: Select data from student table where intake year is not 2018

Retrieve, update and delete data from table with multiple conditions.

In Lab 2, we learnt to use WHERE clause to specify which data to retrieve, update or delete. This is usually done through the use of primary key. However, sometimes database administrator or user need to perform to some data in the table based on several criteria. The keyword AND and OR is used so that the WHERE clause filtering can be done with multiple conditions.

The AND operator displays a record if all the conditions separated by AND is TRUE. Figure 1 shows the example of using AND to retrieve data that matches two condition given. 

Figure 1: SQL statements with AND keyword in WHERE clause.

Noticed in the first SQL statement returned a data where both of the condition in the WHERE clause are met. (stdname = "Enson Liu" AND intakeyear = 2018). While the second SQL statement return zero data because only one of the condition are true.

The OR operator displays a record if any of the conditions separated by OR is TRUE. Figure 2 shows the example using OR to retrieve data with either one or both condition is matches.

Figure 2: SQL statements with OR Keyword in WHERE clauses.

Noted the first SQL statement return directly the data regarding Enson because both of the condition matches. Second SQL statements returned the data for Enson Liu and also John Sena because the data for "Enson Liu" is TRUE for the first condition, even though the second condition is wrong, the statement still return TRUE. For the data of John Sena, the first condition is FALSE, but the second condition is TRUE. Therefore under the OR selection, the data for John Sena is still TRUE for the SQL statement.

The third SQL statements did not return any data because there are no data in the table that matches neither one of the condition given.

The use of OR and AND is applicable for all the WHERE clauses in SQL statements, includes UPDATE and DELETE statements.

Sorting Retrieved Data from Table

The data retrieved from a selected table is based on the sequence of the data entered into the table. Therefore, the data may not be in any order based on the context. Figure 1 below shows the retrieval of data from the table students.

Figure 1: Table students

Noted that the data is not sorted by name or by year of intake and only based on the primary key (stdid). Database administrator or user can choose to order the data based on the column desired. The syntax to perform the operation is:

Database administrator or user can choose which column to sort and also how to sort the data. ASC is the keyword to sort the data in an ascending order (small to big), while DESC is the keyword to sort the data in a descending order. The example of sorting using ASC keyword is shown in Figure 2, while the example of sorting data using DESC keyword is shown in Figure 3.

Figure 2: Sorting the data from students table based on column stdname in ascending order.

Figure 3: Sorting the data from students table based on column stdname in descending order.

Retrieve Distinct Data from Table

Data can be retrieved from table using SELECT [COLUMN] FROM [TABLE]. Sometime, the statement can retrieve multiple data that are the same. Figure 1 below shows the example of the duplication data in a SELECT statements.

Figure 1: Retrieve year of intake from table students.

Noted in Figure 1, where the SQL statement returned 2 2018 figures in the result. This happen because there are two data with the same intake year in the table. SQL had a pre-defined function to remove duplicate data in a column. The SQL syntax is shown below:

The keyword DISTINCT will allow the SQL command to display a set of unique data only in a column. The example of using the particular command is shown in Figure 2 below:

Update Data in Table using SQL

Beside insert and display data from table, database administrator can edit the information in a table using SQL statement. The syntax to update an information in a table is:

The Figure 1 below show the command updating the information in the "students" table.

Figure 1: Using SQL to update the student email and yea of intake using SQL statements

One thing need to be careful is to set the WHERE clause for the UPDATE statement. Same like SELECT statements, WHERE clause is used to identify the specific data in the table to perform the operation. Usually the condition for UPDATE statements in the WHERE clause is based on PRIMARY KEY. 

However, if WHERE clause is not define, the SQL will change all the data in the table. The result of such mistake is shown in Figure 2 below:

Figure 2: Result of UPDATE statements without WHERE clause

Notice in Figure 2 where all the stdemail changed into "ahmad@gmail.com", this is the result where no particular reference point is provided for the SQL to update the information. Therefore, the the statements update all the data in the column as defined by the SQL statement.

Beside update one particular data in the table using UPDATE statement with WHERE clause, database administrator can also update a set of data that through defining the correct WHERE statement. For example, updating all the intakeyear from 2017 to 2018. The operation is shown in Figure 3 below.

Figure 3: Update a set of data using SELECT and WHERE clause.

Lab 3: Insert, Retrieve and Delete data from Database using SQL

Objectives:
after this lab, students are able to

• insert data to selected table using SQL command.
• select and display data using SQL command.
• update information in table using SQL command.
• delete data from table

Instructions:

• follow the instructions from the links in the objectives. after that, complete the exercise below:
1. insert three (3) data to each of the table in the tables created in lab 2. 
• Fill in the lecturer table using the details of your favorite lecturers.
• Fill in the course table using the details of your favorite courses.
• Fill in the students table using your own details and two of your course mates beside you in the lab.
• Fill in the appropriate information in the enrollment table using your data and your course mates data.
2. Select and display all the data in table course.
3. Select and display your own data from students table using your student ID.
4. Update your own year of intake in the students table to 2010.
5. delete one lecturer from your lecturer table.
6. delete all lecturers from your lecturer table.

*Use snipping tools (in windows) or screencapture software to print screen the show database result before and after the task. Save the task title and also the screencapture in a Microsoft Document named "lab3-[matric no]".