Q1.

Consider the following Relational Schema:

CUSTOMER (cust_id, cust_name, annual_revenue)
SHIPMENT (shipment_id, cust_id, weight, truck_id, destination)
Foreign Key Destination references CITY
TRUCK (truck_id, driver_name)
CITY (city_name, population)
Create the database and insert sufficient no of records Write SQL commands to do the
following:
A. i) Delete only those cities from the database whose population fewer than 5000 and at the
same time delete them form SHIPMENT table.
ii) List the cities in the database having largest and smallest populations.
iii) List the names and populations of the cities that have received shipments weights over
10pounds.
B. i) Convert the weight of every shipment in kilograms by dividing the weight by 2.2.
ii) List all the customers having over Rs. 2,50,000 in annual revenue who have sent
Shipments weighing less than hundred pounds.
iii) List all minimum weight of packets sent by the cities with population over 5,000.

Solution:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.4.32-MariaDB [Link] 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 shipments_db;

Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use shipments_db;

Database changed
MariaDB [shipments_db]> CREATE TABLE CITY (
-> city_name VARCHAR(255) PRIMARY KEY,
-> population INTEGER
-> );
Query OK, 0 rows affected (0.009 sec)

MariaDB [shipments_db]>
MariaDB [shipments_db]> CREATE TABLE TRUCK (
-> truck_id INTEGER PRIMARY KEY,
-> driver_name VARCHAR(255)
-> );
Query OK, 0 rows affected (0.008 sec)
MariaDB [shipments_db]> CREATE TABLE CUSTOMER (
-> cust_id INTEGER PRIMARY KEY,
-> cust_name VARCHAR(255),
-> annual_revenue REAL
-> );
Query OK, 0 rows affected (0.012 sec)

MariaDB [shipments_db]>
MariaDB [shipments_db]> CREATE TABLE SHIPMENT (
-> shipment_id INTEGER PRIMARY KEY,
-> cust_id INTEGER,
-> weight REAL,
-> truck_id INTEGER,
-> destination VARCHAR(255),
-> FOREIGN KEY (cust_id) REFERENCES CUSTOMER(cust_id),
-> FOREIGN KEY (truck_id) REFERENCES TRUCK(truck_id),
-> FOREIGN KEY (destination) REFERENCES CITY(city_name)
-> );
Query OK, 0 rows affected (0.025 sec)

MariaDB [shipments_db]> INSERT INTO CITY (city_name, population) VALUES

-> ('Kolkata', 4496694),
-> ('Howrah', 1072161),
-> ('Bally', 293373),
-> ('Belur', 87295),
-> ('Domjur', 182701),
-> ('Shibpur', 153028),
-> ('Lilua', 120539),
-> ('Pilkhana', 110000),
-> ('TinyTown', 2000);
Query OK, 9 rows affected (0.003 sec)
Records: 9 Duplicates: 0 Warnings: 0

MariaDB [shipments_db]>
MariaDB [shipments_db]> INSERT INTO TRUCK (truck_id, driver_name) VALUES
-> (1, 'Tata Motors Truck 1'),
-> (2, 'Ashok Leyland Truck 2'),
-> (3, 'Mahindra Trucks 3');
Query OK, 3 rows affected (0.003 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [shipments_db]>
MariaDB [shipments_db]> INSERT INTO CUSTOMER (cust_id, cust_name, annual_revenue)
VALUES
-> (1, 'Debmalya', 1000000),
-> (2, 'Ayush', 5000000),
-> (3, 'Arnab', 100000),
-> (4, 'Reliance Industries', 3000000);
Query OK, 4 rows affected (0.004 sec)
Records: 4 Duplicates: 0 Warnings: 0

MariaDB [shipments_db]>
MariaDB [shipments_db]> INSERT INTO SHIPMENT (shipment_id, cust_id, weight,
truck_id, destination) VALUES
-> (1, 1, 50, 1, 'Howrah'),
-> (2, 2, 150, 2, 'Kolkata'),
-> (3, 3, 25, 3, 'Bally'),
-> (4, 1, 75, 1, 'Belur'),
-> (5, 4, 90, 2, 'Domjur'),
-> (6, 1, 10, 1, 'Shibpur'),
-> (7, 2, 5, 2, 'Lilua'),
-> (8, 3, 120, 3, 'Kolkata'),
-> (9,1,10,1,'TinyTown');
Query OK, 9 rows affected (0.003 sec)
Records: 9 Duplicates: 0 Warnings: 0
MariaDB [shipments_db]> SELECT * FROM CITY;
+-----------+------------+
| city_name | population |
+-----------+------------+
| Bally | 293373 |
| Belur | 87295 |
| Domjur | 182701 |
| Howrah | 1072161 |
| Kolkata | 4496694 |
| Lilua | 120539 |
| Pilkhana | 110000 |
| Shibpur | 153028 |
| TinyTown | 2000 |
+-----------+------------+
9 rows in set (0.000 sec)

MariaDB [shipments_db]> SELECT cust_name, annual_revenue FROM CUSTOMER;

+---------------------+----------------+
| cust_name | annual_revenue |
+---------------------+----------------+
| Debmalya | 1000000 |
| Ayush | 5000000 |
| Arnab | 100000 |
| Reliance Industries | 3000000 |
+---------------------+----------------+
4 rows in set (0.000 sec)

MariaDB [shipments_db]> SELECT * FROM TRUCK;

+----------+-----------------------+
| truck_id | driver_name |
+----------+-----------------------+
| 1 | Tata Motors Truck 1 |
| 2 | Ashok Leyland Truck 2 |
| 3 | Mahindra Trucks 3 |
+----------+-----------------------+
3 rows in set (0.015 sec)

MariaDB [shipments_db]> SELECT * FROM SHIPMENT;

+-------------+---------+--------+----------+-------------+
| shipment_id | cust_id | weight | truck_id | destination |
+-------------+---------+--------+----------+-------------+
| 1 | 1 | 50 | 1 | Howrah |
| 2 | 2 | 150 | 2 | Kolkata |
| 3 | 3 | 25 | 3 | Bally |
| 4 | 1 | 75 | 1 | Belur |
| 5 | 4 | 90 | 2 | Domjur |
| 6 | 1 | 10 | 1 | Shibpur |
| 7 | 2 | 5 | 2 | Lilua |
| 8 | 3 | 120 | 3 | Kolkata |
| 9 | 1 | 10 | 1 | TinyTown |
+-------------+---------+--------+----------+-------------+
9 rows in set (0.002 sec)
A. i) Delete only those cities from the database whose population fewer than 5000 and at the
same time delete them form SHIPMENT table.

MariaDB [shipments_db]> DELETE FROM SHIPMENT WHERE destination IN (SELECT city_name

FROM CITY WHERE population < 5000);
Query OK, 1 row affected (0.013 sec)

MariaDB [shipments_db]> DELETE FROM CITY WHERE population < 5000;

Query OK, 1 row affected (0.003 sec)

ii) List the cities in the database having largest and smallest populations.

MariaDB [shipments_db]> SELECT city_name, population FROM CITY ORDER BY population

DESC LIMIT 1; -- Largest
+-----------+------------+
| city_name | population |
+-----------+------------+
| Kolkata | 4496694 |
+-----------+------------+
1 row in set (0.004 sec)

MariaDB [shipments_db]> SELECT city_name, population FROM CITY ORDER BY population

ASC LIMIT 1; -- Smallest
+-----------+------------+
| city_name | population |
+-----------+------------+
| Belur | 87295 |
+-----------+------------+
1 row in set (0.000 sec)

iii) List the names and populations of the cities that have received shipments weights over
10 pounds.

MariaDB [shipments_db]> SELECT c.city_name, [Link]

-> FROM CITY c
-> JOIN SHIPMENT s ON c.city_name = [Link]
-> WHERE [Link] > 10;
+-----------+------------+
| city_name | population |
+-----------+------------+
| Howrah | 1072161 |
| Kolkata | 4496694 |
| Bally | 293373 |
| Belur | 87295 |
| Domjur | 182701 |
| Kolkata | 4496694 |
+-----------+------------+
6 rows in set (0.002 sec)

B. i) Convert the weight of every shipment in kilograms by dividing the weight by 2.2.

MariaDB [shipments_db]> UPDATE SHIPMENT SET weight = weight / 2.2;

Query OK, 8 rows affected (0.007 sec)
Rows matched: 8 Changed: 8 Warnings: 0
MariaDB [shipments_db]> SELECT * FROM SHIPMENT; --To view the updated table
+-------------+---------+--------------------+----------+-------------+
| shipment_id | cust_id | weight | truck_id | destination |
+-------------+---------+--------------------+----------+-------------+
| 1 | 1 | 22.727272727272727 | 1 | Howrah |
| 2 | 2 | 68.18181818181817 | 2 | Kolkata |
| 3 | 3 | 11.363636363636363 | 3 | Bally |
| 4 | 1 | 34.090909090909086 | 1 | Belur |
| 5 | 4 | 40.90909090909091 | 2 | Domjur |
| 6 | 1 | 4.545454545454545 | 1 | Shibpur |
| 7 | 2 | 2.2727272727272725 | 2 | Lilua |
| 8 | 3 | 54.54545454545454 | 3 | Kolkata |
+-------------+---------+--------------------+----------+-------------+
8 rows in set (0.000 sec)

ii) List all the customers having over Rs. 2,50,000 in annual revenue who have sent
Shipments weighing less than hundred pounds.

MariaDB [shipments_db]> SELECT c.cust_name

-> FROM CUSTOMER c
-> JOIN SHIPMENT s ON c.cust_id = s.cust_id
-> WHERE c.annual_revenue > 250000 AND [Link] < 100;
+---------------------+
| cust_name |
+---------------------+
| Debmalya |
| Ayush |
| Debmalya |
| Reliance Industries |
| Debmalya |
| Ayush |
+---------------------+
6 rows in set (0.001 sec)

iii) List all minimum weight of packets sent by the cities with population over 5,000.

MariaDB [shipments_db]> SELECT MIN([Link])

-> FROM SHIPMENT s
-> JOIN CITY c ON [Link] = c.city_name
-> WHERE [Link] > 5000;
+--------------------+
| MIN([Link]) |
+--------------------+
| 2.2727272727272725 |
+--------------------+
1 row in set (0.002 sec)