Starting from:
$35
cis4301 Homework #3 Solution
CIS 4301 - Spring 2025
Submission Format
You will submit a soft copy of your solution using e-Learning (http://elearning.ufl.edu) by the end of the day (23:59 / 11:59 PM) on the assigned date (February 21st). Save your solution as a PDF file and name the file hw3.pdf. At the top of every solution file you submit this semester, include your name, the assignment number, and the due date. Before submitting, carefully review the ”What to Submit” section at the end of this assignment to ensure that you have included all required components. Failure to follow the submission guidelines can result in point deductions.
Important Instructions
Before starting this homework, **students must study the SQL 1 slides and recorded lectures** found in the **SQL 1 module** on Canvas: https://ufl.instructure.com/courses/
527757/pages/sql-i?module_item_id=11692841
After studying the slides, **watch the SQL 1 session video** in the same module. Follow along with the video using your MariaDB to practice writing SQL queries. This will help you complete the homework successfully.
Step 1: Start MariaDB (0 pts)
Ensure MariaDB is running on your system before executing the SQL queries.
Step 2: Create Tables and Insert Data (20 pts)
Schema
Use the following schema to create the necessary tables:
• Booking(agent: String, traveler ssn: integer, trip id: integer)
• TravelAgent(name: String, years experience: integer, phone: String)
Insert Sample Data
After creating the tables, insert the following records:
-- TravelAgent
('Alice Brown', 12, '123-456-7890'),
('John Smith', 8, '234-567-8901'),
('Michael Johnson', 5, '345-678-9012'),
('Sarah Williams', 15, '456-789-0123'),
('Daniel Lee', 20, '567-890-1234'),
('Rachel Green', 3, '678-901-2345');
-- Traveler
('David Harris', 101, '1985-06-12'),
('Sarah Connor', 102, '1992-03-05'),
('Mike Johnson', 103, '1998-09-17'),
('Laura White', 104, '1995-04-23'),
('James Miller', 105, '2000-08-14'),
('Emma Watson', 106, '1997-11-30'), ('Chris Evans', 107, '1993-06-21'),
('Sophia Brown', 108, '1999-02-25');
-- Trip
(201, 'New York', 'Paris', '2025-07-10', '2025-07-20'),
(202, 'Tokyo', 'Sydney', '2025-08-01', '2025-08-15'),
(203, 'London', 'Rome', '2025-09-05', '2025-09-15'),
(204, 'Miami', 'New York', '2025-06-15', '2025-06-20'),
(205, 'San Francisco', 'Berlin', '2025-10-01', '2025-10-10'),
(206, 'Chicago', 'Los Angeles', '2025-09-10', '2025-09-12'),
(207, 'Boston', 'Dubai', '2025-07-05', '2025-07-18'),
(208, 'New York', 'Dubai', '2025-09-10', '2025-09-15');
-- Booking
('Alice Brown', 101, 201),
('John Smith', 102, 202),
('Michael Johnson', 103, 203),
('Sarah Williams', 104, 204),
('Daniel Lee', 105, 205),
('Alice Brown', 106, 206),
('Rachel Green', 107, 207),
('John Smith', 108, 201),
('Alice Brown', 101, 208);
Step 3: SQL Query Exercises (80 pts)
1. [8 pts] Retrieve the names and phone numbers of all travel agents who have more than 10 years of experience. Additionally, include a new column in the output labeled
3. [8 pts] Find all trips that start in ’New York’ and end in ’Paris’.
4. [8 pts] Retrieve all information about trips that start in either ’New York’ or ’Miami’.
5. [8 pts] Retrieve all information about trips that start after ’2025-08-01’.
6. [8 pts] Retrieve the traveler SSN and trip ID for all trips booked by the travel agent ”Alice Brown”.
7. [8 pts] Find all travelers whose names have ’a’ as the second letter. You must use Patterns to solve this question.
8. [8 pts] Find all travelers who are NOT named ’David Harris’. You must use Patterns to solve this question.
9. [8 pts] Find all travel agents whose phone number starts with ’456’. You must use Patterns to solve this question.
10. [8 pts] Find all trips that start between ’2025-07-01’ and ’2025-09-30’. You must use the Keyword BETWEEN for this question. Check the SQL 1 session video to see how to use this keyword.
What to Submit
For this assignment, submit a single PDF file named hw3.pdf containing: • Step 2: Table Creation and Data Insertion
– The SQL statements used to create tables.
– The SQL statements used to insert data.
– A screenshot showing successful table creation.
– A screenshot showing successful data insertion.
• Step 3: SQL Query Exercises. For each questions submit the following:
– The SQL query in text format.
– A screenshot of the SQL query in MariaDB.
– A screenshot of the query output.
Submission Format
You will submit a soft copy of your solution using e-Learning (http://elearning.ufl.edu) by the end of the day (23:59 / 11:59 PM) on the assigned date (February 21st). Save your solution as a PDF file and name the file hw3.pdf. At the top of every solution file you submit this semester, include your name, the assignment number, and the due date. Before submitting, carefully review the ”What to Submit” section at the end of this assignment to ensure that you have included all required components. Failure to follow the submission guidelines can result in point deductions.
Important Instructions
Before starting this homework, **students must study the SQL 1 slides and recorded lectures** found in the **SQL 1 module** on Canvas: https://ufl.instructure.com/courses/
527757/pages/sql-i?module_item_id=11692841
After studying the slides, **watch the SQL 1 session video** in the same module. Follow along with the video using your MariaDB to practice writing SQL queries. This will help you complete the homework successfully.
Step 1: Start MariaDB (0 pts)
Ensure MariaDB is running on your system before executing the SQL queries.
Step 2: Create Tables and Insert Data (20 pts)
Schema
Use the following schema to create the necessary tables:
• Booking(agent: String, traveler ssn: integer, trip id: integer)
• TravelAgent(name: String, years experience: integer, phone: String)
Insert Sample Data
After creating the tables, insert the following records:
-- TravelAgent
('Alice Brown', 12, '123-456-7890'),
('John Smith', 8, '234-567-8901'),
('Michael Johnson', 5, '345-678-9012'),
('Sarah Williams', 15, '456-789-0123'),
('Daniel Lee', 20, '567-890-1234'),
('Rachel Green', 3, '678-901-2345');
-- Traveler
('David Harris', 101, '1985-06-12'),
('Sarah Connor', 102, '1992-03-05'),
('Mike Johnson', 103, '1998-09-17'),
('Laura White', 104, '1995-04-23'),
('James Miller', 105, '2000-08-14'),
('Emma Watson', 106, '1997-11-30'), ('Chris Evans', 107, '1993-06-21'),
('Sophia Brown', 108, '1999-02-25');
-- Trip
(201, 'New York', 'Paris', '2025-07-10', '2025-07-20'),
(202, 'Tokyo', 'Sydney', '2025-08-01', '2025-08-15'),
(203, 'London', 'Rome', '2025-09-05', '2025-09-15'),
(204, 'Miami', 'New York', '2025-06-15', '2025-06-20'),
(205, 'San Francisco', 'Berlin', '2025-10-01', '2025-10-10'),
(206, 'Chicago', 'Los Angeles', '2025-09-10', '2025-09-12'),
(207, 'Boston', 'Dubai', '2025-07-05', '2025-07-18'),
(208, 'New York', 'Dubai', '2025-09-10', '2025-09-15');
-- Booking
('Alice Brown', 101, 201),
('John Smith', 102, 202),
('Michael Johnson', 103, 203),
('Sarah Williams', 104, 204),
('Daniel Lee', 105, 205),
('Alice Brown', 106, 206),
('Rachel Green', 107, 207),
('John Smith', 108, 201),
('Alice Brown', 101, 208);
Step 3: SQL Query Exercises (80 pts)
1. [8 pts] Retrieve the names and phone numbers of all travel agents who have more than 10 years of experience. Additionally, include a new column in the output labeled
3. [8 pts] Find all trips that start in ’New York’ and end in ’Paris’.
4. [8 pts] Retrieve all information about trips that start in either ’New York’ or ’Miami’.
5. [8 pts] Retrieve all information about trips that start after ’2025-08-01’.
6. [8 pts] Retrieve the traveler SSN and trip ID for all trips booked by the travel agent ”Alice Brown”.
7. [8 pts] Find all travelers whose names have ’a’ as the second letter. You must use Patterns to solve this question.
8. [8 pts] Find all travelers who are NOT named ’David Harris’. You must use Patterns to solve this question.
9. [8 pts] Find all travel agents whose phone number starts with ’456’. You must use Patterns to solve this question.
10. [8 pts] Find all trips that start between ’2025-07-01’ and ’2025-09-30’. You must use the Keyword BETWEEN for this question. Check the SQL 1 session video to see how to use this keyword.
What to Submit
For this assignment, submit a single PDF file named hw3.pdf containing: • Step 2: Table Creation and Data Insertion
– The SQL statements used to create tables.
– The SQL statements used to insert data.
– A screenshot showing successful table creation.
– A screenshot showing successful data insertion.
• Step 3: SQL Query Exercises. For each questions submit the following:
– The SQL query in text format.
– A screenshot of the SQL query in MariaDB.
– A screenshot of the query output.
1 file (105.1KB)
