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 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 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]>

1
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 |

2
| 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.

3
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, c.population
-> FROM CITY c
-> JOIN SHIPMENT s ON c.city_name = s.destination
-> WHERE s.weight > 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 |
+-------------+---------+--------------------+----------+-------------+

4
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 s.weight < 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(s.weight)
-> FROM SHIPMENT s
-> JOIN CITY c ON s.destination = c.city_name
-> WHERE c.population > 5000;
+--------------------+
| MIN(s.weight) |
+--------------------+
| 2.2727272727272725 |
+--------------------+
1 row in set (0.002 sec)

Q2. Consider the following Relational schema:

Borrower (b_name, b_id);
Book (book_id, title, author, subject);
Borrows (book_id, b_id, date_of_issue, date_of_return);
Create table through appropriate sql commands. Define all integrity constraints
and enter sufficient data. Write SQL commands for the following queries:
i) Give all the books on physics and mathematics.
ii) Find out all the title and author of all the books issued by the borrower where
b_id is B001.
iii) Find out the names of the borrower who have issued books written by
C.J.Date

Solution:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9

5
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 library_db;

Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use library_db;

Database changed
MariaDB [library_db]> CREATE TABLE Borrower (
-> b_name VARCHAR(255) NOT NULL,
-> b_id VARCHAR(10) PRIMARY KEY,
-> CONSTRAINT borrower_pk PRIMARY KEY (b_id)
-> );
ERROR 1068 (42000): Multiple primary key defined
MariaDB [library_db]> CREATE TABLE Borrower (
-> b_name VARCHAR(255) NOT NULL,
-> b_id VARCHAR(10) PRIMARY KEY
-> );
Query OK, 0 rows affected (0.011 sec)

MariaDB [library_db]> CREATE TABLE Book (

-> book_id VARCHAR(10) PRIMARY KEY,
-> title VARCHAR(255) NOT NULL,
-> author VARCHAR(255) NOT NULL,
-> subject VARCHAR(255) NOT NULL
-> );
Query OK, 0 rows affected (0.010 sec)

MariaDB [library_db]> CREATE TABLE Borrows (

-> book_id VARCHAR(10) NOT NULL,
-> b_id VARCHAR(10) NOT NULL,
-> date_of_issue DATE NOT NULL,
-> date_of_return DATE,
-> FOREIGN KEY (book_id) REFERENCES Book(book_id),
-> FOREIGN KEY (b_id) REFERENCES Borrower(b_id),
-> CONSTRAINT borrows_pk PRIMARY KEY (book_id, b_id)
-> );
Query OK, 0 rows affected, 1 warning (0.025 sec)

MariaDB [library_db]> INSERT INTO Borrower (b_name, b_id) VALUES

-> ('Debmalya', 'B001'),
-> ('Ayush', 'B002'),
-> ('Arnab', 'B003');
Query OK, 3 rows affected (0.004 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [library_db]> INSERT INTO Book (book_id, title, author, subject) VALUES
-> ('BOOK001', 'Intro to Physics', 'Isaac Asimov', 'Physics'),
-> ('BOOK002', 'Calculus for Dummies', 'Milton Friedman', 'Mathematics'),
-> ('BOOK003', 'SQL in 10 Minutes', 'Ben Forta', 'Computer Science'),
-> ('BOOK004', 'Data Structures and Algorithms', 'Alfred T. Hoenig', 'Computer
Science'),
-> ('BOOK005', 'C Programming Language', 'Brian Kernighan', 'Computer
Science'),
-> ('BOOK006', 'Introduction to Database Systems', 'C.J. Date', 'Database');
Query OK, 6 rows affected (0.004 sec)
Records: 6 Duplicates: 0 Warnings: 0

MariaDB [library_db]> INSERT INTO Borrows (book_id, b_id, date_of_issue,

date_of_return) VALUES

6
 -> ('BOOK001', 'B001', '2023-01-10', '2023-01-20'),
-> ('BOOK002', 'B002', '2023-01-15', NULL),
-> ('BOOK003', 'B001', '2023-01-18', '2023-01-25'),
-> ('BOOK004', 'B002', '2023-01-20', NULL),
-> ('BOOK006', 'B002', '2023-01-22', NULL);
Query OK, 5 rows affected (0.003 sec)
Records: 5 Duplicates: 0 Warnings: 0

MariaDB [library_db]> SELECT * FROM Borrower;

+----------+------+
| b_name | b_id |
+----------+------+
| Debmalya | B001 |
| Ayush | B002 |
| Arnab | B003 |
+----------+------+
3 rows in set (0.000 sec)

MariaDB [library_db]> SELECT * FROM Book;

+---------+----------------------------------+------------------+-----------------+
| book_id | title | author | subject |
+---------+----------------------------------+------------------+-----------------+
| BOOK001 | Intro to Physics | Isaac Asimov | Physics |
| BOOK002 | Calculus for Dummies | Milton Friedman | Mathematics |
| BOOK003 | SQL in 10 Minutes | Ben Forta | Computer Science|
| BOOK004 | Data Structures and Algorithms | Alfred T. Hoenig | Computer Science|
| BOOK005 | C Programming Language | Brian Kernighan | Computer Science|
| BOOK006 | Introduction to Database Systems | C.J. Date | Database |
+---------+----------------------------------+------------------+-----------------+
6 rows in set (0.000 sec)

MariaDB [library_db]> SELECT * FROM Borrows;

+---------+------+---------------+----------------+
| book_id | b_id | date_of_issue | date_of_return |
+---------+------+---------------+----------------+
| BOOK001 | B001 | 2023-01-10 | 2023-01-20 |
| BOOK002 | B002 | 2023-01-15 | NULL |
| BOOK003 | B001 | 2023-01-18 | 2023-01-25 |
| BOOK004 | B002 | 2023-01-20 | NULL |
| BOOK006 | B002 | 2023-01-22 | NULL |
+---------+------+---------------+----------------+
5 rows in set (0.000 sec)

i) Give all the books on physics and mathematics.

MariaDB [library_db]> SELECT * FROM Book WHERE subject IN ('Physics',

'Mathematics');
+---------+----------------------+-----------------+-------------+
| book_id | title | author | subject |
+---------+----------------------+-----------------+-------------+
| BOOK001 | Intro to Physics | Isaac Asimov | Physics |
| BOOK002 | Calculus for Dummies | Milton Friedman | Mathematics |
+---------+----------------------+-----------------+-------------+
2 rows in set (0.001 sec)

7
ii) Find out all the title and author of all the books issued by the borrower where
b_id is B001.

MariaDB [library_db]> SELECT b.title, b.author

-> FROM Book b
-> JOIN Borrows br ON b.book_id = br.book_id
-> WHERE br.b_id = 'B001';
+-------------------+--------------+
| title | author |
+-------------------+--------------+
| Intro to Physics | Isaac Asimov |
| SQL in 10 Minutes | Ben Forta |
+-------------------+--------------+
2 rows in set (0.072 sec)

iii) Find out the names of the borrower who have issued books written by
C.J.Date.

MariaDB [library_db]> INSERT INTO Borrows (book_id, b_id, date_of_issue,

date_of_return) VALUES
-> ('BOOK006', 'B001', '2024-01-05', NULL); -- Debmalya borrows the C.J. Date
book
Query OK, 1 row affected (0.005 sec)

MariaDB [library_db]> SELECT br.b_name

-> FROM Borrower br
-> JOIN Borrows brw ON br.b_id = brw.b_id
-> JOIN Book bk ON brw.book_id = bk.book_id
-> WHERE bk.author = 'C.J. Date';
+----------+
| b_name |
+----------+
| Debmalya |
| Ayush |
+----------+
2 rows in set (0.000 sec)

8
Q3. Consider the following Relational Schema:
STUDENT (student_id, student_name)
IS_QUALIFIED (faculty_id, course_id, date_qualified)
FACULTY (faculty_id, faculty_name)
SECTION (section_id, course_id)
COURSE (course_id, course_name)
IS_REGISTERED (student_id, section_id, semester)
Create the database and insert records. Define all integrity constraints Write SQL
commands to do the following:
i) Display the course_id and course_name for all the courses with an ISM prefix.
ii) Display the student name for all students enrolled in section 2714 of ISM
4212.
iii) List all the students who weren’t enrolled in any course during semester I-
2008.

Solution:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
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 university_db;

Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use university_db;

Database changed
MariaDB [university_db]> CREATE TABLE STUDENT (
-> student_id INT PRIMARY KEY,
-> student_name VARCHAR(255)
-> );
Query OK, 0 rows affected (0.012 sec)

MariaDB [university_db]> CREATE TABLE FACULTY (

-> faculty_id INT PRIMARY KEY,
-> faculty_name VARCHAR(255)
-> );
Query OK, 0 rows affected (0.009 sec)

MariaDB [university_db]> CREATE TABLE COURSE (

-> course_id VARCHAR(10) PRIMARY KEY,
-> course_name VARCHAR(255)
-> );
Query OK, 0 rows affected (0.027 sec)

MariaDB [university_db]> CREATE TABLE SECTION (

-> section_id INT PRIMARY KEY,
-> course_id VARCHAR(10),
-> FOREIGN KEY (course_id) REFERENCES COURSE(course_id)
-> );

9
Query OK, 0 rows affected (0.027 sec)

MariaDB [university_db]> CREATE TABLE IS_QUALIFIED (

-> faculty_id INT,
-> course_id VARCHAR(10),
-> date_qualified DATE,
-> FOREIGN KEY (faculty_id) REFERENCES FACULTY(faculty_id),
-> FOREIGN KEY (course_id) REFERENCES COURSE(course_id),
-> PRIMARY KEY (faculty_id, course_id)
-> );
Query OK, 0 rows affected (0.026 sec)

MariaDB [university_db]> CREATE TABLE IS_REGISTERED (

-> student_id INT,
-> section_id INT,
-> semester VARCHAR(10),
-> FOREIGN KEY (student_id) REFERENCES STUDENT(student_id),
-> FOREIGN KEY (section_id) REFERENCES SECTION(section_id),
-> PRIMARY KEY (student_id, section_id, semester)
-> );
Query OK, 0 rows affected (0.018 sec)

MariaDB [university_db]> INSERT INTO STUDENT (student_id, student_name) VALUES

-> (1, 'Debmalya'),
-> (2, 'Ayush'),
-> (3, 'Arnab'),
-> (4, 'Priya');
Query OK, 4 rows affected (0.008 sec)
Records: 4 Duplicates: 0 Warnings: 0

MariaDB [university_db]> INSERT INTO FACULTY (faculty_id, faculty_name) VALUES

-> (101, 'Dr. Roy'),
-> (102, 'Prof. Sen');
Query OK, 2 rows affected (0.004 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [university_db]> INSERT INTO COURSE (course_id, course_name) VALUES

-> ('ISM4212', 'Database Management'),
-> ('ISM3113', 'Web Development'),
-> ('CS101', 'Intro to Programming'),
-> ('MTH202', 'Calculus II');
Query OK, 4 rows affected (0.004 sec)
Records: 4 Duplicates: 0 Warnings: 0

MariaDB [university_db]> INSERT INTO SECTION (section_id, course_id) VALUES

-> (2714, 'ISM4212'),
-> (2715, 'ISM3113'),
-> (1001, 'CS101');
Query OK, 3 rows affected (0.004 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [university_db]> INSERT INTO IS_QUALIFIED (faculty_id, course_id,

date_qualified) VALUES
-> (101, 'ISM4212', '2020-01-15'),
-> (102, 'ISM3113', '2019-05-10');
Query OK, 2 rows affected (0.003 sec)
Records: 2 Duplicates: 0 Warnings: 0

i) Display course_id and course_name for courses with an ISM prefix.

MariaDB [university_db]> SELECT course_id, course_name

-> FROM COURSE
-> WHERE course_id LIKE 'ISM%';

10
+-----------+---------------------+
| course_id | course_name |
+-----------+---------------------+
| ISM3113 | Web Development |
| ISM4212 | Database Management |
+-----------+---------------------+
2 rows in set (0.007 sec)

ii) Display student name for students enrolled in section 2714 of ISM 4212.

MariaDB [university_db]> SELECT s.student_name

-> FROM STUDENT s
-> JOIN IS_REGISTERED ir ON s.student_id = ir.student_id
-> JOIN SECTION sec ON ir.section_id = sec.section_id
-> WHERE sec.section_id = 2714 AND sec.course_id = 'ISM4212';
+--------------+
| student_name |
+--------------+
| Debmalya |
| Ayush |
+--------------+
2 rows in set (0.005 sec)

iii) List all the students who weren’t enrolled in any course during semester I-
2008.

MariaDB [university_db]> INSERT INTO STUDENT (student_id, student_name) VALUES

-> (6, 'Gopal'),
-> (7, 'Madhu'),
-> (8, 'Shreya'),
-> (9, 'Rohan'); -- Use 9 instead of 8 because 8 might already exist
Query OK, 4 rows affected (0.002 sec)
Records: 4 Duplicates: 0 Warnings: 0

MariaDB [university_db]> INSERT INTO IS_REGISTERED (student_id, section_id,

semester) VALUES
-> (6, 2714, 'I-2008'), -- Gopal is registered
-> (8, 2715, 'I-2008'); -- Shreya is registered
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [university_db]> SELECT s.student_name

-> FROM STUDENT s
-> LEFT JOIN IS_REGISTERED ir ON s.student_id = ir.student_id AND ir.semester =
'I-2008'
-> WHERE ir.student_id IS NULL;
+--------------+
| student_name |
+--------------+
| New Student |
| Madhu |
| Rohan |
+--------------+
3 rows in set (0.002 sec)

Q4. Consider the following Relational schema:

Sailors (s_id, s_name, rating)
Boats (b_id, b_name, color)
Reserves (s_id, b_id, day)

11
Bid must be between 10and 30 and color must be RED, GREEN, BLUE, YELLOW
Create the database and insert records. Define all integrity constraints Write SQL
commands to do the following:
i) Find the name of the sailors who have reserved both a red and green boats.
ii) Find the colors of boats reserved by Rahim.

Solution:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
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 sailing_db;

Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE sailing_db;

Database changed
MariaDB [sailing_db]> CREATE TABLE Sailors (
-> s_id INT PRIMARY KEY,
-> s_name VARCHAR(255),
-> rating INT
-> );
Query OK, 0 rows affected (0.013 sec)

MariaDB [sailing_db]> CREATE TABLE Boats (

-> b_id INT PRIMARY KEY CHECK (b_id BETWEEN 10 AND 30),
-> b_name VARCHAR(255),
-> color VARCHAR(20) CHECK (color IN ('RED', 'GREEN', 'BLUE', 'YELLOW'))
-> );
Query OK, 0 rows affected (0.008 sec)

MariaDB [sailing_db]> CREATE TABLE Reserves (

-> s_id INT,
-> b_id INT,
-> day DATE,
-> FOREIGN KEY (s_id) REFERENCES Sailors(s_id),
-> FOREIGN KEY (b_id) REFERENCES Boats(b_id),
-> PRIMARY KEY (s_id, b_id, day) -- Composite key
-> );
Query OK, 0 rows affected (0.027 sec)

MariaDB [sailing_db]> INSERT INTO Sailors (s_id, s_name, rating) VALUES

-> (1, 'Rahim', 7),
-> (2, 'Karim', 8),
-> (3, 'Salman', 9),
-> (4, 'Debmalya', 10);
Query OK, 4 rows affected (0.004 sec)
Records: 4 Duplicates: 0 Warnings: 0

MariaDB [sailing_db]>
MariaDB [sailing_db]> INSERT INTO Boats (b_id, b_name, color) VALUES
-> (10, 'Sea Queen', 'RED'),
-> (12, 'Ocean Breeze', 'GREEN'),
-> (14, 'Rapid Current', 'BLUE'),
-> (16, 'Yellow Duck', 'YELLOW'),
-> (20, 'Red Baron', 'RED'),
-> (25, 'Green Peace', 'GREEN');

12
Query OK, 6 rows affected (0.002 sec)
Records: 6 Duplicates: 0 Warnings: 0

MariaDB [sailing_db]> INSERT INTO Reserves (s_id, b_id, day) VALUES

-> (1, 10, '2024-07-20'), -- Rahim reserved a RED boat
-> (1, 12, '2024-07-21'), -- Rahim reserved a GREEN boat
-> (2, 14, '2024-07-20'), -- Karim reserved a BLUE boat
-> (3, 10, '2024-07-22'), -- Salman reserved a RED boat
-> (4,20,'2024-07-24'), -- Debmalya reserved a RED boat
-> (4,12,'2024-07-25'); -- Debmalya reserved a GREEN boat
Query OK, 6 rows affected (0.004 sec)
Records: 6 Duplicates: 0 Warnings: 0

i) Find the name of the sailors who have reserved both a red and green boats.

MariaDB [sailing_db]> SELECT s.s_name

-> FROM Sailors s
-> WHERE EXISTS (
-> SELECT 1
-> FROM Reserves r1
-> JOIN Boats b1 ON r1.b_id = b1.b_id
-> WHERE r1.s_id = s.s_id AND b1.color = 'RED'
-> )
-> AND EXISTS (
-> SELECT 1
-> FROM Reserves r2
-> JOIN Boats b2 ON r2.b_id = b2.b_id
-> WHERE r2.s_id = s.s_id AND b2.color = 'GREEN'
-> );
+----------+
| s_name |
+----------+
| Rahim |
| Debmalya |
+----------+
2 rows in set (0.001 sec)
ii) Find the colors of boats reserved by Rahim.

MariaDB [sailing_db]> SELECT b.color

-> FROM Boats b
-> JOIN Reserves r ON b.b_id = r.b_id
-> JOIN Sailors s ON r.s_id = s.s_id
-> WHERE s.s_name = 'Rahim';
+-------+
| color |
+-------+
| RED |
| GREEN |
+-------+
2 rows in set (0.000 sec)

Q5. Consider the following Relational Schema:

EMPLOYEE (emp_id, emp_name, street, city)
WORKS (emp_id, company_name, salary)
[Salary range- 10,00to 1, 00,000]
COMPANY (company_name, city)
[Company_name must be ABC, PQR or XYZ]
MANAGER (mgremp_id, manager_name)
Create the database and insert records . Define all integrity constraints Write
SQL commands to do the following:

13
i)Find the names, street address and cities of residence of all employees who
works for ABC company and earn more than Rs10,000/-
ii)Find all employees who do not work for ABC company.

Solution:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
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 company_db;

Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE company_db;

Database changed
MariaDB [company_db]> CREATE TABLE EMPLOYEE (
-> emp_id INT PRIMARY KEY,
-> emp_name VARCHAR(255),
-> street VARCHAR(255),
-> city VARCHAR(255)
-> );
Query OK, 0 rows affected (0.007 sec)

MariaDB [company_db]> CREATE TABLE COMPANY (

-> company_name VARCHAR(255) PRIMARY KEY CHECK (company_name IN ('ABC',
'PQR', 'XYZ')),
-> city VARCHAR(255)
-> );
Query OK, 0 rows affected (0.007 sec)

MariaDB [company_db]> CREATE TABLE WORKS (

-> emp_id INT,
-> company_name VARCHAR(255),
-> salary DECIMAL(10, 2) CHECK (salary BETWEEN 10000 AND 100000),
-> FOREIGN KEY (emp_id) REFERENCES EMPLOYEE(emp_id),
-> FOREIGN KEY (company_name) REFERENCES COMPANY(company_name),
-> PRIMARY KEY (emp_id, company_name) -- Composite key
-> );
Query OK, 0 rows affected (0.028 sec)

MariaDB [company_db]> CREATE TABLE MANAGER (

-> mgremp_id INT PRIMARY KEY,
-> manager_name VARCHAR(255),
-> FOREIGN KEY (mgremp_id) REFERENCES EMPLOYEE(emp_id)
-> );
Query OK, 0 rows affected (0.008 sec)

MariaDB [company_db]> -- Insert Sample Data

MariaDB [company_db]> INSERT INTO EMPLOYEE (emp_id, emp_name, street, city) VALUES
-> (1, 'Debmalya', '123 B.T. Road', 'Howrah'),
-> (2, 'Ayush', '456 G.T. Road', 'Kolkata'),
-> (3, 'Arnab', '789 N.S. Road', 'Howrah'),
-> (4, 'Shreya', '101 M.G. Road', 'Kolkata'),
-> (5, 'Gopal', '202 Belilious Road', 'Howrah');
Query OK, 5 rows affected (0.002 sec)
Records: 5 Duplicates: 0 Warnings: 0

MariaDB [company_db]> INSERT INTO COMPANY (company_name, city) VALUES

-> ('ABC', 'Kolkata'),

14
 -> ('PQR', 'Mumbai'),
-> ('XYZ', 'Delhi');
Query OK, 3 rows affected (0.002 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [company_db]> INSERT INTO WORKS (emp_id, company_name, salary) VALUES

-> (1, 'ABC', 50000),
-> (2, 'PQR', 75000),
-> (3, 'ABC', 15000),
-> (4, 'XYZ', 90000),
-> (5, 'PQR', 20000);
Query OK, 5 rows affected (0.002 sec)
Records: 5 Duplicates: 0 Warnings: 0

MariaDB [company_db]> INSERT INTO MANAGER (mgremp_id, manager_name) VALUES

-> (1, 'Rohan');
Query OK, 1 row affected (0.004 sec)

i) Find the names, street address and cities of residence of all employees who
works for ABC company and earn more than Rs10,000/-

MariaDB [company_db]> SELECT e.emp_name, e.street, e.city

-> FROM EMPLOYEE e
-> JOIN WORKS w ON e.emp_id = w.emp_id
-> WHERE w.company_name = 'ABC' AND w.salary > 10000;
+----------+---------------+--------+
| emp_name | street | city |
+----------+---------------+--------+
| Debmalya | 123 B.T. Road | Howrah |
| Arnab | 789 N.S. Road | Howrah |
+----------+---------------+--------+
2 rows in set (0.001 sec)

ii) Find all employees who do not work for ABC company.

MariaDB [company_db]> SELECT e.emp_name

-> FROM EMPLOYEE e
-> WHERE NOT EXISTS (
-> SELECT 1
-> FROM WORKS w
-> WHERE w.emp_id = e.emp_id AND w.company_name = 'ABC'
-> );
+----------+
| emp_name |
+----------+
| Ayush |
| Shreya |
| Gopal |
+----------+
3 rows in set (0.001 sec)

15
Q6. Consider the following Relational schema:
Salesperson (ssn, name, startyear, dept_no)
Trip (ssn, fromcity, tocity, departure_date, return_date, trip_id)
Expense (trip_id, account, amount)
Create the database through appropriate SQL command. Define all integrity
constraints Insert sufficient number of records. Write SQL commands for the
following queries:
i) Give the details of trips that exited rupees 2000 in expenses.
ii) Print the ssn of the salesman who trip from howrah.
iii) Print the total trip expenses in earned by the salesman with ssn 145268547

Solution:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
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 SalesTripDB;

Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE SalesTripDB;

Database changed

MariaDB [SalesTripDB]> CREATE TABLE Salesperson (

-> ssn CHAR(9) PRIMARY KEY,
-> name VARCHAR(50) NOT NULL,
-> startyear INT NOT NULL,
-> dept_no INT NOT NULL
-> );
Query OK, 0 rows affected (0.009 sec)

MariaDB [SalesTripDB]> CREATE TABLE Trip (

-> ssn CHAR(9) NOT NULL,
-> fromcity VARCHAR(50) NOT NULL,
-> tocity VARCHAR(50) NOT NULL,
-> departure_date DATE NOT NULL,
-> return_date DATE NOT NULL,
-> trip_id INT PRIMARY KEY,
-> FOREIGN KEY (ssn) REFERENCES Salesperson(ssn)
-> );
Query OK, 0 rows affected (0.025 sec)

MariaDB [SalesTripDB]> CREATE TABLE Expense (

-> trip_id INT NOT NULL,
-> account VARCHAR(50) NOT NULL,
-> amount DECIMAL(10, 2) NOT NULL,
-> FOREIGN KEY (trip_id) REFERENCES Trip(trip_id)
-> );
Query OK, 0 rows affected (0.026 sec)

MariaDB [SalesTripDB]> INSERT INTO Salesperson (ssn, name, startyear, dept_no)

VALUES
-> ('145268547', 'John Doe', 2015, 101),
-> ('123456789', 'Jane Smith', 2018, 102),

16
 -> ('987654321', 'Alice Brown', 2020, 103);
Query OK, 3 rows affected (0.003 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [SalesTripDB]> INSERT INTO Trip (ssn, fromcity, tocity, departure_date,

return_date, trip_id) VALUES
-> ('145268547', 'Kolkata', 'Mumbai', '2025-01-01', '2025-01-05', 1),
-> ('123456789', 'Howrah', 'Delhi', '2025-01-10', '2025-01-15', 2),
-> ('987654321', 'Chennai', 'Bangalore', '2025-01-20', '2025-01-25', 3);
Query OK, 3 rows affected (0.002 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [SalesTripDB]> INSERT INTO Expense (trip_id, account, amount) VALUES

-> (1, 'Travel', 1500.00),
-> (1, 'Food', 700.00),
-> (2, 'Accommodation', 2500.00),
-> (3, 'Miscellaneous', 1200.00);
Query OK, 4 rows affected (0.002 sec)
Records: 4 Duplicates: 0 Warnings: 0

i) Give the details of trips that exited rupees 2000 in expenses.

MariaDB [SalesTripDB]> SELECT T.*

-> FROM Trip T
-> JOIN Expense E ON T.trip_id = E.trip_id
-> GROUP BY T.trip_id, T.ssn, T.fromcity, T.tocity, T.departure_date,
T.return_date
-> HAVING SUM(E.amount) > 2000;
+-----------+----------+--------+----------------+-------------+---------+
| ssn | fromcity | tocity | departure_date | return_date | trip_id |
+-----------+----------+--------+----------------+-------------+---------+
| 145268547 | Kolkata | Mumbai | 2025-01-01 | 2025-01-05 | 1 |
| 123456789 | Howrah | Delhi | 2025-01-10 | 2025-01-15 | 2 |
+-----------+----------+--------+----------------+-------------+---------+
2 rows in set (0.001 sec)

ii) Print the ssn of the salesman who trip from howrah.

MariaDB [SalesTripDB]> SELECT DISTINCT ssn

-> FROM Trip
-> WHERE fromcity = 'Howrah';
+-----------+
| ssn |
+-----------+
| 123456789 |
+-----------+
1 row in set (0.001 sec)

iii) Print the total trip expenses in earned by the salesman with ssn 145268547

MariaDB [SalesTripDB]> SELECT SUM(E.amount) AS total_expense

-> FROM Trip T
-> JOIN Expense E ON T.trip_id = E.trip_id
-> WHERE T.ssn = '145268547';
+---------------+
| total_expense |

17
+---------------+
| 2200.00 |
+---------------+
1 row in set

18
Q7. Consider the following Relations about a computer firm. The primary key of
each relation is underlined.
PRODUCT (maker, model, type)
PC (code, model, speed, ram, hd, cd, price)
LAPTOP (code, model, speed, ram, hd, screen, price)
PRINTER (code, model, color, type, price)
Create the database and insert sufficient records. Define all integrity constraints.
Write SQL commands to do the following
Find the model number, speed and hard drive capacity for all the PCs with prices
below rs.500.
Find the makers of the PCs that have speed not less than 450MHz.
Find the printers having the highest price.

Solution:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.41 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database computerdb;
Query OK, 1 row affected (0.01 sec)
mysql> use computerdb;
Database changed
mysql> CREATE TABLE PRODUCT (
-> maker VARCHAR(50),
-> model INT PRIMARY KEY,
-> type VARCHAR(50)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE PC (
-> code INT PRIMARY KEY,
-> model INT,
-> speed INT,
-> ram INT,
-> hd INT,
26
-> cd VARCHAR(10),
-> price DECIMAL(10, 2),
-> FOREIGN KEY (model) REFERENCES PRODUCT(model)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE TABLE LAPTOP (
-> code INT PRIMARY KEY,
-> model INT,
-> speed INT, -- Speed in MHz
-> ram INT, -- RAM in MB
-> hd INT, -- Hard drive in GB
-> screen DECIMAL(5, 2), -- Screen size in inches
-> price DECIMAL(10, 2),
-> FOREIGN KEY (model) REFERENCES PRODUCT(model)
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> CREATE TABLE PRINTER (
-> code INT PRIMARY KEY,

19
-> model INT,
-> color BOOLEAN,
-> type VARCHAR(50),
-> price DECIMAL(10, 2),
-> FOREIGN KEY (model) REFERENCES PRODUCT(model)
-> );
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO PRODUCT (maker, model, type) VALUES

-> ('Dell', 101, 'PC'),
-> ('HP', 102, 'Laptop'),
-> ('Canon', 103, 'Printer'),
-> ('Lenovo', 104, 'PC'),
-> ('Epson', 105, 'Printer');
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> INSERT INTO PC (code, model, speed, ram, hd, cd, price) VALUES
-> (1, 101, 500, 8, 500, '24x', 450.00),
-> (2, 101, 600, 16, 1000, '32x', 600.00),
-> (3, 104, 450, 4, 250, '16x', 300.00);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO LAPTOP (code, model, speed, ram, hd, screen, price) VALUES
-> (4, 102, 1000, 8, 512, 15.6, 700.00),
-> (5, 102, 1200, 16, 1024, 14.0, 850.00);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO PRINTER (code, model, color, type, price) VALUES
-> (6, 103, TRUE, 'Laser', 200.00),
-> (7, 103, FALSE, 'Inkjet', 150.00),
-> (8, 105, TRUE, 'Laser', 300.00);
Query OK, 3 rows affected (0.01 sec)
27
Records: 3 Duplicates: 0 Warnings: 0

a. Find the model number, speed and hard drive capacity for all the PCs with
prices
below rs.500.

mysql> SELECT model, speed, hd

-> FROM PC
-> WHERE price < 500;
+-------+-------+------+
| model | speed | hd |
+-------+-------+------+
| 101 | 500 | 500 |
| 104 | 450 | 250 |
+-------+-------+------+
2 rows in set (0.00 sec)

b. Find the makers of the PCs that have speed not less than 450MHz.

mysql> SELECT DISTINCT p.maker

-> FROM PRODUCT p
-> JOIN PC pc ON p.model = pc.model
-> WHERE pc.speed >= 450;
+--------+

20
| maker |
+--------+
| Dell |
| Lenovo |
+--------+
2 rows in set (0.00 sec)

c. Find the printers having the highest price.

mysql> SELECT code, model, price

-> FROM PRINTER
-> WHERE price = (SELECT MAX(price) FROM PRINTER);
+------+-------+--------+
| code | model | price |
+------+-------+--------+
| 8 | 105 | 300.00 |
+------+-------+--------+
1 row in set (0.00 sec)

21
Q8. Consider the following Relational schema:
Student (s_num, s_name, major, level, age)
Class (c_name, time_schedule, room, faculty_id)
Enrolled (s_num, c_name)
Faculty (faculty_id, ename, dept_id)
Create tables through appropriate sql commands. Define all integrity constraints
and enter sufficient data.
Write SQL commands for the following queries:
a. Find the name of faculty members who teach in every room in which some
class is taught.
b. Print the level and the average age for that level.

Solution:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.41 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database schooldb;
Query OK, 1 row affected (0.01 sec)
mysql> use schooldb;
Database changed
mysql> CREATE TABLE Student (
-> s_num INT PRIMARY KEY,
-> s_name VARCHAR(100),
-> major VARCHAR(50),
-> level VARCHAR(20),
-> age INT CHECK(age > 0)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE TABLE Faculty (
-> faculty_id INT PRIMARY KEY,
-> ename VARCHAR(100),
-> dept_id INT
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE Class (
-> c_name VARCHAR(100) PRIMARY KEY,
-> time_schedule VARCHAR(50),
20
-> room VARCHAR(50),
-> faculty_id INT,
-> FOREIGN KEY (faculty_id) REFERENCES Faculty(faculty_id)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE TABLE Enrolled (
-> s_num INT,
-> c_name VARCHAR(100),
-> PRIMARY KEY (s_num, c_name),
-> FOREIGN KEY (s_num) REFERENCES Student(s_num),
-> FOREIGN KEY (c_name) REFERENCES Class(c_name)
-> );
Query OK, 0 rows affected (0.09 sec)

22
mysql> INSERT INTO Student (s_num, s_name, major, level, age) VALUES
-> (1, 'Alice', 'Computer Science', 'Undergraduate', 20),
-> (2, 'Bob', 'Mathematics', 'Undergraduate', 21),
-> (3, 'Charlie', 'Physics', 'Postgraduate', 24),
-> (4, 'David', 'Computer Science', 'Undergraduate', 22);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO Faculty (faculty_id, ename, dept_id) VALUES
-> (1, 'Dr. Smith', 101),
-> (2, 'Prof. Johnson', 102),
-> (3, 'Dr. Brown', 101);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO Class (c_name, time_schedule, room, faculty_id) VALUES
-> ('CS101', 'Mon 9-11 AM', 'Room 101', 1),
-> ('MATH101', 'Mon 1-3 PM', 'Room 102', 2),
-> ('PHYS101', 'Tue 10-12 AM', 'Room 101', 3),
-> ('CS102', 'Wed 2-4 PM', 'Room 103', 1);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO Enrolled (s_num, c_name) VALUES
-> (1, 'CS101'),
-> (2, 'MATH101'),
-> (3, 'PHYS101'),
-> (4, 'CS102'),
-> (1, 'CS102');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

a. Find the name of faculty members who teach in every room in which some
class is taught.

mysql> SELECT f.ename

-> FROM Faculty f
-> JOIN Class c ON f.faculty_id = c.faculty_id
-> GROUP BY f.faculty_id
-> HAVING COUNT(DISTINCT c.room) = (SELECT COUNT(DISTINCT room) FROM Class);
Empty set (0.00 sec)

b. Print the level and the average age for that level

mysql> SELECT level, AVG(age) AS avg_age

-> FROM Student
-> GROUP BY level;
+---------------+---------+
| level | avg_age |
+---------------+---------+
| Undergraduate | 21.0000 |
| Postgraduate | 24.0000 |
+---------------+---------+
2 rows in set (0.00 sec)

23