CC-104

Setting environment for using XAMPP for Windows.
CEDRIC@DESKTOP-0G7VTBI c:\xampp
# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.4.32-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE Angulo_C;
Query OK, 1 row affected (0.002 sec)

MariaDB [(none)]> use Angulo_C;
Database changed
MariaDB [Angulo_C]> CREATE TABLE TblOrders (
    -> ord_no INT(10) NOT NULL,
    -> purch_amt DECIMAL(10,2),
    -> ord_date DATE,
    -> customer_id SMALLINT,
    -> salesman_id SMALLINT);
Query OK, 0 rows affected (0.010 sec)

MariaDB [Angulo_C]> DESCRIBE TblOrders;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| ord_no      | int(10)       | NO   |     | NULL    |       |
| purch_amt   | decimal(10,2) | YES  |     | NULL    |       |
| ord_date    | date          | YES  |     | NULL    |       |
| customer_id | smallint(6)   | YES  |     | NULL    |       |
| salesman_id | smallint(6)   | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
5 rows in set (0.016 sec)

MariaDB [Angulo_C]> INSERT INTO TblOrders VALUES
    -> (70001, 150.50, '2012-10-05', 3005, 5002),
    -> (70009, 270.65, '2012-09-10', 3001, 5005),
    -> (70002, 65.26, '2012-10-05', 3002, 5001),
    -> (70004, 110.50, '2012-08-17', 3009, 5003),
    -> (70007, 948.50, '2012-09-10', 3005, 5002),
    -> (70005, 2400.60, '2012-07-27', 3007, 5001),
    -> (70008, 5760.00, '2012-09-10', 3002, 5001),
    -> (70010, 1983.43, '2012-10-10', 3004, 5006),
    -> (70003, 2480.40, '2012-10-10', 3009, 5003),
    -> (70012, 250.45, '2012-06-27', 3008, 5002),
    -> (70011, 75.29, '2012-08-17', 3003, 5007),
    -> (70013, 3045.60, '2012-04-25', 3002, 5001);
Query OK, 12 rows affected (0.009 sec)
Records: 12  Duplicates: 0  Warnings: 0

MariaDB [Angulo_C]> SELECT * FROM TblOrders;
+--------+-----------+------------+-------------+-------------+
| ord_no | purch_amt | ord_date   | customer_id | salesman_id |
+--------+-----------+------------+-------------+-------------+
|  70001 |    150.50 | 2012-10-05 |        3005 |        5002 |
|  70009 |    270.65 | 2012-09-10 |        3001 |        5005 |
|  70002 |     65.26 | 2012-10-05 |        3002 |        5001 |
|  70004 |    110.50 | 2012-08-17 |        3009 |        5003 |
|  70007 |    948.50 | 2012-09-10 |        3005 |        5002 |
|  70005 |   2400.60 | 2012-07-27 |        3007 |        5001 |
|  70008 |   5760.00 | 2012-09-10 |        3002 |        5001 |
|  70010 |   1983.43 | 2012-10-10 |        3004 |        5006 |
|  70003 |   2480.40 | 2012-10-10 |        3009 |        5003 |
|  70012 |    250.45 | 2012-06-27 |        3008 |        5002 |
|  70011 |     75.29 | 2012-08-17 |        3003 |        5007 |
|  70013 |   3045.60 | 2012-04-25 |        3002 |        5001 |
+--------+-----------+------------+-------------+-------------+
12 rows in set (0.000 sec)

MariaDB [Angulo_C]> SELECT SUM(purch_amt) AS total_purchases FROM TblOrders;
+-----------------+
| total_purchases |
+-----------------+
|        17541.18 |
+-----------------+
1 row in set (0.001 sec)

MariaDB [Angulo_C]> SELECT AVG(purch_amt) AS avg_purchase_amount FROM TblOrders;
+---------------------+
| avg_purchase_amount |
+---------------------+
|         1461.765000 |
+---------------------+
1 row in set (0.000 sec)

MariaDB [Angulo_C]> SELECT MAX(purch_amt) AS max_purchase, MIN(purch_amt) AS min_purchase FROM TblOrders;
+--------------+--------------+
| max_purchase | min_purchase |
+--------------+--------------+
|      5760.00 |        65.26 |
+--------------+--------------+
1 row in set (0.000 sec)

MariaDB [Angulo_C]> SELECT COUNT(*) AS total_orders FROM TblOrders;
+--------------+
| total_orders |
+--------------+
|           12 |
+--------------+
1 row in set (0.000 sec)

MariaDB [Angulo_C]> SELECT COUNT(DISTINCT customer_id) AS distinct_customers FROM TblOrders;
+--------------------+
| distinct_customers |
+--------------------+
|                  8 |
+--------------------+
1 row in set (0.000 sec)

MariaDB [Angulo_C]> SELECT customer_id, SUM(purch_amt) AS total_amount
    -> FROM TblOrders
    -> GROUP BY customer_id;
+-------------+--------------+
| customer_id | total_amount |
+-------------+--------------+
|        3001 |       270.65 |
|        3002 |      8870.86 |
|        3003 |        75.29 |
|        3004 |      1983.43 |
|        3005 |      1099.00 |
|        3007 |      2400.60 |
|        3008 |       250.45 |
|        3009 |      2590.90 |
+-------------+--------------+
8 rows in set (0.000 sec)

MariaDB [Angulo_C]> SELECT salesman_id, AVG(purch_amt) AS avg_amount
    -> FROM TblOrders
    -> GROUP BY salesman_id;
+-------------+-------------+
| salesman_id | avg_amount  |
+-------------+-------------+
|        5001 | 2817.865000 |
|        5002 |  449.816667 |
|        5003 | 1295.450000 |
|        5005 |  270.650000 |
|        5006 | 1983.430000 |
|        5007 |   75.290000 |
+-------------+-------------+
6 rows in set (0.000 sec)

MariaDB [Angulo_C]> SELECT ord_date, COUNT(*) AS total_orders
    -> FROM TblOrders
    -> GROUP BY ord_date;
+------------+--------------+
| ord_date   | total_orders |
+------------+--------------+
| 2012-04-25 |            1 |
| 2012-06-27 |            1 |
| 2012-07-27 |            1 |
| 2012-08-17 |            2 |
| 2012-09-10 |            3 |
| 2012-10-05 |            2 |
| 2012-10-10 |            2 |
+------------+--------------+
7 rows in set (0.000 sec)

MariaDB [Angulo_C]> bye

-- Create a new database for the orders
CREATE DATABASE Angulo_C;

-- Use the created database
USE Angulo_C;

-- Create the table with the specified structure
CREATE TABLE TblOrders (
    ord_no INT(10) NOT NULL,
    purch_amt DECIMAL(10,2),
    ord_date DATE,
    customer_id SMALLINT,
    salesman_id SMALLINT
);

-- Display the structure of the table
DESCRIBE TblOrders;

-- Insert records into the table
INSERT INTO TblOrders VALUES
(70001, 150.50, '2012-10-05', 3005, 5002),
(70009, 270.65, '2012-09-10', 3001, 5005),
(70002, 65.26, '2012-10-05', 3002, 5001),
(70004, 110.50, '2012-08-17', 3009, 5003),
(70007, 948.50, '2012-09-10', 3005, 5002),
(70005, 2400.60, '2012-07-27', 3007, 5001),
(70008, 5760.00, '2012-09-10', 3002, 5001),
(70010, 1983.43, '2012-10-10', 3004, 5006),
(70003, 2480.40, '2012-10-10', 3009, 5003),
(70012, 250.45, '2012-06-27', 3008, 5002),
(70011, 75.29, '2012-08-17', 3003, 5007),
(70013, 3045.60, '2012-04-25', 3002, 5001);

-- Query 1: Display the total amount of all purchases
SELECT SUM(purch_amt) AS total_purchases FROM TblOrders;

-- Query 2: Show the average purchase amount for all orders
SELECT AVG(purch_amt) AS avg_purchase_amount FROM TblOrders;

-- Query 3: Get the maximum and minimum purchase amounts recorded
SELECT MAX(purch_amt) AS max_purchase, MIN(purch_amt) AS min_purchase FROM TblOrders;

-- Query 4: Count the total number of orders made
SELECT COUNT(*) AS total_orders FROM TblOrders;

-- Query 5: Count the number of distinct customers who made purchases
SELECT COUNT(DISTINCT customer_id) AS distinct_customers FROM TblOrders;

-- Query 6: Display the total purchase amount per customer (Output: customer_id, total_amount)
SELECT customer_id, SUM(purch_amt) AS total_amount FROM TblOrders GROUP BY customer_id;

-- Query 7: Show the average order amount per salesman (Output: salesman_id, avg_amount)
SELECT salesman_id, AVG(purch_amt) AS avg_amount FROM TblOrders GROUP BY salesman_id;

-- Query 8: Count the number of orders per order date (Output: ord_date, total_orders)
SELECT ord_date, COUNT(*) AS total_orders FROM TblOrders GROUP BY ord_date;