Assignment Chef icon Assignment Chef
All English tutorials

Programming lesson

Mastering SQL Joins and Pattern Matching: A CIS4301 Homework 3 Guide

Learn how to tackle CIS4301 Homework 3 with confidence. This tutorial covers table creation, data insertion, SQL queries with WHERE, BETWEEN, LIKE patterns, and joins, using a travel booking database. Perfect for UF students studying SQL 1.

CIS4301 homework 3 SQL tutorial MariaDB query examples SQL pattern matching LIKE BETWEEN date SQL travel booking database UF SQL 1 assignment SQL WHERE clause SQL INSERT statement database table creation SQL for beginners 2026 learn SQL step by step SQL query exercises travel agent database SQL foreign key example CIS4301 Spring 2025

Introduction: Why SQL Skills Matter in 2026

Structured Query Language (SQL) remains the backbone of data management, powering everything from social media apps to AI-driven travel platforms. As of May 2026, databases like MariaDB are used by startups and tech giants alike. In this tutorial, we'll break down the key concepts from CIS4301 Homework 3, focusing on table creation, data insertion, and writing SQL queries using WHERE, BETWEEN, and pattern matching (LIKE). You'll learn by working with a travel booking database—a perfect example of how SQL organizes real-world data.

Step 1: Setting Up MariaDB and Creating Tables

Before writing queries, you need a database. In MariaDB, you first create a database, then define tables. The schema for this homework includes four tables: TravelAgent, Traveler, Trip, and Booking. Here's the SQL for creating them:

CREATE TABLE TravelAgent (
    name VARCHAR(50),
    years_experience INT,
    phone VARCHAR(15)
);

CREATE TABLE Traveler (
    name VARCHAR(50),
    ssn INT PRIMARY KEY,
    dob DATE
);

CREATE TABLE Trip (
    trip_id INT PRIMARY KEY,
    start_location VARCHAR(50),
    end_location VARCHAR(50),
    start_date DATE,
    end_date DATE
);

CREATE TABLE Booking (
    agent VARCHAR(50),
    traveler_ssn INT,
    trip_id INT,
    FOREIGN KEY (traveler_ssn) REFERENCES Traveler(ssn),
    FOREIGN KEY (trip_id) REFERENCES Trip(trip_id)
);

Notice that Traveler and Trip have primary keys (ssn and trip_id), and Booking uses foreign keys to link them. This is a classic relational database design, similar to how a travel app like Kayak connects users, flights, and bookings.

Step 2: Inserting Sample Data

After creating tables, insert the provided records. Use INSERT statements like this:

INSERT INTO TravelAgent VALUES
('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');

INSERT INTO Traveler VALUES
('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');

INSERT INTO Trip VALUES
(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');

INSERT INTO Booking VALUES
('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);

Always double-check that your data matches the assignment. A common mistake is forgetting to insert all rows or mismatching foreign key values.

Step 3: Writing SQL Queries

Now for the core part: answering the 10 query exercises. Let's go through each with explanations and SQL code.

Query 1: Travel Agents with More Than 10 Years Experience

This is a simple filter using WHERE. Add a computed column showing a bonus if they have 15+ years, for example.

SELECT name, phone, 
       CASE WHEN years_experience >= 15 THEN 'Eligible for Bonus' ELSE 'Standard' END AS bonus_status
FROM TravelAgent
WHERE years_experience > 10;

Trend tie-in: In 2026, many travel agencies use AI to rank agents by experience. This query mirrors how a dashboard might filter top performers.

Query 2: Trips Starting in 'New York' and Ending in 'Paris'

Use AND to combine conditions.

SELECT *
FROM Trip
WHERE start_location = 'New York' AND end_location = 'Paris';

This returns trip 201. Simple but essential for filtering.

Query 3: Trips Starting in 'New York' or 'Miami'

Use OR.

SELECT *
FROM Trip
WHERE start_location = 'New York' OR start_location = 'Miami';

Returns trips 201, 204, 208. Note that 208 starts in New York but ends in Dubai.

Query 4: Trips Starting After '2025-08-01'

Use > with a date literal.

SELECT *
FROM Trip
WHERE start_date > '2025-08-01';

Returns trips 202, 203, 205, 206, 207, 208. Dates are compared as strings in YYYY-MM-DD format.

Query 5: Traveler SSN and Trip ID for Bookings by 'Alice Brown'

Filter Booking table by agent name.

SELECT traveler_ssn, trip_id
FROM Booking
WHERE agent = 'Alice Brown';

Returns three rows: (101,201), (106,206), (101,208).

Query 6: Travelers with 'a' as Second Letter (Pattern Matching)

Use LIKE with underscore wildcard.

SELECT *
FROM Traveler
WHERE name LIKE '_a%';

The underscore matches exactly one character, then 'a' as second letter, then % for any following. This returns David Harris, Sarah Connor, Laura White, James Miller, Emma Watson, etc. (Check: 'David' has 'a' as second? D-a-vid -> yes. 'Sarah' -> S-a-rah -> yes. 'Mike' -> M-i-ke -> no. 'Laura' -> L-a-ura -> yes. 'James' -> J-a-mes -> yes. 'Emma' -> E-m-ma -> no. 'Chris' -> C-h-ris -> no. 'Sophia' -> S-o-phia -> no. So result: David Harris, Sarah Connor, Laura White, James Miller.)

Query 7: Travelers Not Named 'David Harris' (Using Pattern)

Use NOT LIKE with a pattern that matches exactly 'David Harris'.

SELECT *
FROM Traveler
WHERE name NOT LIKE 'David Harris';

Returns all except David Harris. This is a pattern-based approach as required.

Query 8: Travel Agents with Phone Starting with '456'

Use LIKE with '456%'.

SELECT *
FROM TravelAgent
WHERE phone LIKE '456%';

Returns Sarah Williams (456-789-0123).

Query 9: Trips Starting Between '2025-07-01' and '2025-09-30' (Using BETWEEN)

BETWEEN is inclusive.

SELECT *
FROM Trip
WHERE start_date BETWEEN '2025-07-01' AND '2025-09-30';

Returns trips 201, 202, 203, 204, 206, 207, 208. Note: 205 starts on 2025-10-01, which is outside.

Common Pitfalls and Tips

  • Case sensitivity: In MariaDB, string comparisons are case-insensitive by default, but it's good to match exactly as given.
  • Date format: Always use 'YYYY-MM-DD' for date literals.
  • Wildcards: % matches any sequence, _ matches a single character.
  • Foreign keys: Ensure inserted data respects referential integrity.

Conclusion

By mastering these SQL basics—CREATE, INSERT, SELECT with WHERE, BETWEEN, and LIKE—you're building a foundation for advanced database work. In 2026, these skills are more relevant than ever, whether you're querying a travel booking system or analyzing data for an AI startup. Practice with your MariaDB, and you'll ace CIS4301 Homework 3.