Page 109 - PowerPoint Presentation

CAVITE STATE UNIVERSITY
T3 CAMPUS
Department of Information Technology DCIT 55 – Advance Database System

COUNT Function example:
- SELECT COUNT(ID) AS “Total_Male” FROM Persons WHERE Gender=’Male’;

COUNT(*) Function example:
- SELECT COUNT(*) AS “Total_Records” FROM Persons;

Online Assessment 2: Create your own database, tables and apply different queries.
Write/Type everything on a piece of paper.
1. Drop all the existing Database in MySQL server.

2. Create a database and name it Quiz_2, then use it.

3. Create 3 tables namely: Orders, OrderItem & Product. Use proper data types.
o Orders Table must have 4 fields namely ID, OrderDate, OrderNumber, CustomerID
o OrderItem Table must have 5 fields namely: ID, OrderID, ProductID, Price,
Quantity
o Product Table must have 3 fields namely: ID, ProducName, Price

4. Add at least 5 records on Orders Table and 3 records on OrderItem Table and Product
Table. (check table at number 7 for records)
NOTE: Records on OrderItem Table and Product Table must be the same in ID and
Price.

5. Using INNER JOIN, join all the 3 tables. Output must be:

ID OrderNumber OrderDate CustomerID ProductID Quantity ProductName Price
1 1 2020-12-01 1 1 21 Soap 150
2 2 2020-11-11 2 2 14 Alcohol 121
3 3 2020-09-14 3 3 6 Tissue 78

6. Using RIGHT JOIN, join all the 3 tables. Output must be:

ID OrderNumber OrderDate CustomerID ProductID Quantity ProductName Price
1 1 2020-12-01 1 1 21 Soap 150
2 2 2020-11-11 2 2 14 Alcohol 121
3 3 2020-09-14 3 3 6 Tissue 78

7. Using LEFT JOIN, join all the 3 tables. Output must be:
ID OrderNumber OrderDate CustomerID ProductID Quantity ProductName Price
1 1 2020-12-01 1 1 21 Soap 150
2 2 2020-11-11 2 2 14 Alcohol 121
3 3 2020-09-14 3 3 6 Tissue 78
4 3 2020-16-21 4 NULL NULL NULL NULL
5 3 2020-01-27 5 NULL NULL NULL NULL

8. DROP all the table.

9. DROP the database.

Page | 25