Database Laboratory

This page provides the summary content for this Database Laboratory Tutorial.

• Lab 1: Accessing MySQL/MariaDB Database in Local Machine
• Lab 2 : Manipulating Database Table Structure using SQL
• Lab 3: Insert, Retrieve and Delete data from Database using SQL
• Lab 4: Functions in SQL part 1
• Lab 5: SELECT, JOIN and UNION
• Lab 6: phpMyAdmin and Web Technologies
• Lab 7: Web-based Database Manipulation
• Lab 8: Web-Based database manipulation (part 2)

Lab 8: Web-Based database manipulation (part 2)

Objectives:

• Learn how to delete a data from database using PHP 
• Learn how to update a data in database through PHP and HTML form.
• Learn the use of different input elements in HTML form.

Instructions:

1. Download Lab 8 and codes from links.
2. Follows the instruction of Lab demo and tries all the codes in the lab.

* Slides and codes are updated on 4th Dec 2017, courtesy of Mr Kuan Huiggy.

Lab 7: Web-based Database Manipulation

Objectives:

1. Connect MySQL using PHP.
2. Retrieve and display data from MySQL using PHP and HTML.
3. Input Data to MySQL using PHP and HTML form.

Instructions:

1. This lab is based-on the students table from the previous lab.
2. Download the slides from the link(updated).
3. Perform all the exercises in the slides

* The previous slides have some issue on the database connection slides. Please update your slide to the latest one.
*Slides 23 updated on 28 Nov 2017, the method for the form is changed from "post" to "get". This will overcome the error for later lab exercise. 

Lab 6

Since some lab have completed Lab 5. This week lab will be on preparation for next week.

Objectives:

• Explore the use of GUI Web-based Database Management System (PHPMyAdmin).
• Explore The WebPage Scripting (HTML).
• Explore The fundamental of PHP Scripting.

Instructions:

1. Access the PHPMyAdmin module in your XAMPP through http://localhost/phpmyadmin 
• explore and see how the database in previous lab looks like in PHPMyAdmin.
• Explore the structure tab and SQL tab.
• Try some SQL statements using the SQL module.
2. Explore HTML scripts in building simple webpages from links.
3. Explore PHP scripts for database management. explore it at links.

* the slides for this lab is downloadable through link. The slides are provided by Mr Kuan Huiggy.

Lab 5

Objectives:

• Retrieves data from two tables using:
• Select Query
• JOINS
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• UNION
• Retrieve Data from Table by groups
• Using LIKE keyword and WILDCARD Operator to search database content

Instructions:

• Download the lab slides from links.
• Update the database content based on the code and data in slide 4 and slide 5.
• Execute all the SQL statements in Lab Exercise section and documents the results.
• Screenshot all the operation exercise above and save it in a Microsoft Documents named "lab5-[matric no]". *each lab document will be different because your matric number is included in your table name. For example: 56788_students for student with matric number 56788.
• Group 06/07 are required to submit in eleap after the lab sessions by 30 Nov 2017.
• The other group depends on your lecturer’s instruction.

Lab 4: Functions in SQL part 1

Objectives:

• Retrieve distinct data from table.
• Sorting the retrieved data using ORDERED BY function.
• Retrieve, update and delete data from table with multiple conditions.
• Excluding some data value in filtering.
• Using the functions AVG, COUNT and SUM.
• Using the functions MAX and MIN.

Instructions:

• This labs uses the students table with the data shown in Figure 1 below:

Figure 1: Table Students.

• Update the information in Figure 1 into your students table using the SQL command you learnt previously.
• Follow the instructions from the links in the lab objectives to understands the different functions in SQL commands.
• Perform the operation below:
• Retrieve the distinct list of intake year from the table students.
• Sort the distinct list of intake year from the table in descending order.
• retrieve a data from table students where the name of the student is Enson Liu and the year of intake is 2016.
• Retrieve the data from table students where the name of the student is Enson Liu or the the year of intake is 2012.
• List all the student in table students that are not from the year 2018.
• Calculate the sum and average value for the student id in the table students.
• show the largest year in the table students.
• Show the smallest student id in the table students.
• Screenshot all the operation exercise above and save it in a Microsoft Documents named "lab4-[matric no]". *each lab document will be different because your matric number is included in your table name. For example: 56788_students for student with matric number 56788.

Using MAX and MIN Functions

Beside Average, Count and Sum functions, SQL statement also have MIN and MAX functions.
MIN function returns the data with the minimum value in a column. This to obtain the minimum value in a column is:

Figure 1 below shows how the above SQL statement is used to obtain the smallest year number in the table students.

Figure 1: Output the minimum year number from table students using the SQL MIN function.

MIN Function outputted the minimum value in the column, Then MAX function will output the maximum value from the particular column for SQL statement. The syntax for MAX function in SQL statement is:

Figure 2 below shows the SQL statement obtain the largest (Maximum value) student ID in the table students using MAX function.

Figure 2: Output the largest (Maximum Value) student ID in table students