SQL Login Credentials & Recommended Tools

Below are the SQL login credentials for the gtareader user, which has read-only access to the GTA schema within the Courses database.

• Server Name: skills4it.database.windows.net
• Database Name: Courses
• Login (Username): gtareader
• Password: StrongPass!2025

Recommended Tools for Accessing the Database

To connect to your Azure SQL Database and execute the queries from this guide, you can use several excellent tools. Here are a few popular choices:

1. Azure Data Studio:
   • Description: A free, cross-platform (Windows, macOS, Linux) database tool for data professionals. It's lightweight and offers a modern editor experience with IntelliSense, code snippets, source control integration, and an integrated terminal.
   • Pros: Excellent for Azure SQL Database, modern interface, great for query writing and result visualization, built-in Jupyter Notebooks support.
   • How to Connect:
     1. Open Azure Data Studio.
     2. Click "New Connection" or the plug icon in the SERVERS view.
     3. Connection type: Microsoft SQL Server.
     4. Server: skills4it.database.windows.net
     5. Authentication type: SQL Login.
     6. User name: gtareader
     7. Password: StrongPass!2025
     8. Database: Courses (or leave as and select later).
     9. Enable "Encrypt connection" (usually default and recommended).
     10. Consider enabling "Trust server certificate" if you encounter SSL issues for initial local connections, but for production, properly configured certificates are better.
     11. Click "Connect".
2. SQL Server Management Studio (SSMS):
   • Description: A more comprehensive, Windows-only integrated environment for managing any SQL infrastructure.
   • Pros: Very powerful, extensive administrative features, familiar to many SQL Server DBAs.
   • How to Connect:
     1. Open SSMS.
     2. Server type: Database Engine.
     3. Server name: skills4it.database.windows.net
     4. Authentication: SQL Server Authentication.
     5. Login: gtareader
     6. Password: StrongPass!2025
     7. Click "Connect".
     8. You might need to click "Options >>" and specify "Connect to database:" as Courses on the "Connection Properties" tab. Ensure "Encrypt connection" is checked.
3. Command-Line Tools:
   • sqlcmd: A command-line utility that comes with SQL Server. Useful for scripting.

     sqlcmd -S skills4it.database.windows.net -U gtareader -P "StrongPass!2025" -d Courses -N -C
     # Type your query and then GO

   • mssql-cli: A modern, cross-platform command-line tool with auto-completion.

     mssql-cli -S skills4it.database.windows.net -U gtareader -P "StrongPass!2025" -d Courses --encrypt verify-full

4. VS Code with SQL Server (mssql) Extension:
   • Description: If you use Visual Studio Code, the "SQL Server (mssql)" extension by Microsoft provides a rich experience.
   • Pros: Convenient if VS Code is your primary editor; good IntelliSense and query execution.
   • How to Connect: Install the extension, then use the command palette (Ctrl+Shift+P or Cmd+Shift+P) to "MS SQL: Connect" and follow the prompts similar to Azure Data Studio.

General Connection Steps (varies slightly by tool):

1. Open your chosen tool.
2. Find "New Connection" / "Connect to Server".
3. Server Name/Host: skills4it.database.windows.net
4. Authentication Type: "SQL Server Authentication".
5. Login/User name: gtareader
6. Password: StrongPass!2025
7. Database (Optional but Recommended): Courses.
8. Encryption: Ensure it's enabled. For Azure SQL, this is typically required.
9. Click "Connect".

Once connected, you can open a new query window/editor and start running the SQL statements from the guide.

Important Security & Permissions Note:

• The gtareader user is configured with SELECT permissions only on the GTA schema. This is a security best practice.
• With these credentials, you cannot perform operations that modify the database structure or data (e.g., CREATE TABLE, INSERT, UPDATE, DELETE).
• To execute DDL or DML statements (like setting up the "Bay City Blues" tables and initial data), you will require credentials for a user with higher privileges (e.g., db_owner). The provided GTA_SQL_Academy_with_Bootcamp_met data.sql script should be run by such a user.

GTA: SQL Bay City Hustle – Bay City SQL Bootcamp

💻 SYSTEM MESSAGE: Incoming Transmission... Decrypting... Welcome, Agent.

The neon glow of Bay City reflects in your eyes. It's a city of a million stories, and even more data points. Before you dive headfirst into the criminal underworld or the high-stakes corporate espionage that defines this town, you need to become fluent in its digital language: SQL. This isn't just about queries, rookie; it's about survival, outsmarting your rivals, and carving out your legend. This SQL Bootcamp is your first step. Each directive here sharpens your skills, preparing you for the real hustle.

DATABASE SCHEMA OVERVIEW (Your Primary Intel Feed)

Before you query, you must understand your sources. This is a simplified manifest of your key data feeds:

(For the "Bay City Blues" detective challenge, additional BCPD_ tables will be introduced later.)

SELECT & FROM – Scanning the Grid

Handler's Briefing: "Alright, Agent, eyes open. In Bay City, information isn't just power; it's ammunition. SELECT is your targeting reticle – it tells the system what data you're after. FROM is your GPS – it points to the data silo, the TableName, where that intel is stashed."

Syntax:

SELECT column1, column2 -- Specify the intel fields you need
FROM GTA.TableName;      -- Specify the data source

Example: City-Wide Persons of Interest Scan
"When tall buildings block your line of sight, you fire up the city’s master cameras. Let's get a quick thermal scan of the city. Show me names and current heat levels from the Citizens databank."

SELECT Name, WantedLevel
FROM GTA.Citizens;

⭐ Quick Mission: Asset Roster Compilation (Difficulty: ⭐)

• Objective: Central command needs a basic roster. List all known citizen Names and their registered Professions. This could be your first lead, or your first target.
• Directive: Query the GTA.Citizens table.

SELECT Name, Profession
FROM GTA.Citizens;

WHERE – Filtering Suspects

Handler's Briefing: "The city's data stream is a firehose, Agent. You can't drink it all. The WHERE clause is your precision filter. You don't need the census report; you need the troublemakers, the vulnerabilities, the opportunities. This is how you cut through the noise."

Syntax:

SELECT * -- Or specific columns
FROM GTA.TableName
WHERE condition; -- Your filter criteria (e.g., Age < 25, WantedLevel > 3, Profession = 'Fixer')

Example: High Threat Individuals
"Filter the Citizens databank. I only want eyes on individuals with a WantedLevel greater than 1. These are the ones making waves."

SELECT *
FROM GTA.Citizens
WHERE WantedLevel > 1;

⭐ Quick Mission: Youth Division Profiling (Difficulty: ⭐)

• Objective: Intel chatter suggests an uptick in activity among the city's younger elements. Retrieve full dossiers (*) for all citizens under 25.
• Directive: Query GTA.Citizens.

SELECT *
FROM GTA.Citizens
WHERE Age < 25;

ORDER BY – Ranking the Danger

Handler's Briefing: "Data dumps are messy. To make strategic decisions, you need intel sorted. Fastest escape routes? Most lucrative targets? Highest operational risks? ORDER BY is your analyst, bringing method to the madness. ASC for ascending, DESC for descending."

Syntax:

SELECT * -- Or specific columns
FROM GTA.TableName
ORDER BY column ASC|DESC; -- Sort by this column, either A-Z/0-9 (ASC) or Z-A/9-0 (DESC)

Example: Hot Wheels Inventory
"I need a list of all registered Vehicles. Show me their Brand, Type, and Speed. Fastest ones at the top."

SELECT Brand, Type, Speed
FROM GTA.Vehicles
ORDER BY Speed DESC;

⭐ Quick Mission: Vehicle Performance Tiers (Difficulty: ⭐⭐)

• Objective: Your getaway driver needs options. List all vehicle Types, Brands, and their Speed, sorted from the slowest clunker to the fastest street demon.
• Directive: Query GTA.Vehicles.

SELECT Type, Brand, Speed
FROM GTA.Vehicles
ORDER BY Speed ASC; -- ASC for slowest to fastest

Pro Tip: ORDER BY is often one of the last clauses processed in a query. If you're sorting a very large dataset, it can impact performance. Use it wisely!

GROUP BY – Squad-Level Insights

Handler's Briefing: "Sometimes the individual brushstrokes don't matter as much as the whole painting. How many ops has each cell completed? What's the average take per type of job? GROUP BY rolls up your raw intel into actionable summaries. This is how you see the bigger picture, Agent."

Syntax:

SELECT column_to_group_by, AGGREGATE_FUNCTION(column_to_analyze) AS summary_value
FROM GTA.TableName
GROUP BY column_to_group_by;

(Remember, AGGREGATE_FUNCTION could be COUNT(), SUM(), AVG(), MIN(), MAX().)

Example: Operative Workload Assessment
"Let's see the operational tempo. COUNT how many Assignments each CitizenID has on their docket."

SELECT CitizenID, COUNT(*) AS TotalAssignments
FROM GTA.Assignments
GROUP BY CitizenID;

⭐ Quick Mission: Vehicle Fleet Analysis (Difficulty: ⭐⭐)

• Objective: Intel suggests some citizens are more... acquisitive than others. How many vehicles are registered to each citizen?
• Directive: You'll need to link GTA.Citizens (for names) to GTA.Vehicles. Show citizen Name and their vehicle COUNT.

SELECT
    C.Name,
    COUNT(V.VehicleID) AS NumberOfVehiclesOwned
FROM
    GTA.Citizens C
LEFT JOIN -- Important to see citizens even if they own 0 vehicles
    GTA.Vehicles V ON C.CitizenID = V.OwnerID
GROUP BY
    C.CitizenID, C.Name -- Group by primary key and displayed name
ORDER BY
    NumberOfVehiclesOwned DESC;

BONUS: Aggregates – The City at a Glance

Handler's Briefing: "These are your quick-scan tools, Agent. Need a headcount? COUNT(). Total cash flow? SUM(). Average operational speed? AVG(). Lowest price for an asset? MIN(). Highest threat level? MAX(). Master these, and you'll have the city's pulse at your fingertips."

• COUNT() - Number of records
• SUM() - Total of a numeric field
• AVG() - Average of a numeric field
• MIN() - Smallest value
• MAX() - Largest value

Example: City-Wide Vehicle Performance Benchmark
"What's the average top Speed of all Vehicles in our database?"

SELECT AVG(Speed) AS AverageCityVehicleSpeed
FROM GTA.Vehicles;

⭐ Quick Mission: Peak Threat Assessment (Difficulty: ⭐)

• Objective: The brass wants a sitrep. What's the absolute highest WantedLevel currently active in Bay City?
• Directive: Query GTA.Citizens.

SELECT MAX(WantedLevel) AS PeakWantedLevel
FROM GTA.Citizens;

SQL Operations: Full Training Missions 🎯

Handler's Briefing: "Bootcamp's a walk in the park, Agent. Now the real work begins. These next scenarios aren't drills; they're simulations of the kind of high-stakes data dives you'll be running daily on the gritty streets of Bay City. Each mission will push your SQL combat skills further. Failure is not an option when lives – or millions in untraceable crypto – are on the line."

MISSION 1: Suspect Scanner

• Objective (Difficulty: ⭐⭐): Sharpen your WHERE clause skills for targeted data acquisition.
• Incoming Encrypted Transmission (Story): "Handler here. Our top-tier netrunner, Claudia “Codewitch” Trejo, just punched a hole in the BCPD's archaic suspect database. She's streaming us a raw feed, but it's unfiltered – the entire city's watch list. Codewitch has a script auto-flagging any individual with a WantedLevel of 2 or higher. These aren't just petty criminals, agent; these are individuals who've made significant noise. Your first task: isolate these high-priority targets from the data stream. We need their full Name, known Alias, and confirmed WantedLevel for immediate tactical assessment. They could be potential assets, immediate threats, or simply pawns in a much larger game. Move fast; data this fresh doesn't stay exclusive for long."
• Your Assignment: Write a SQL query to extract all suspects with a WantedLevel of 2 or more. The dossier must include the suspect's Name, Alias, and current WantedLevel.

SELECT Name, Alias, WantedLevel
FROM GTA.Citizens
WHERE WantedLevel >= 2;

MISSION 2: Vehicle Watchlist

• Objective (Difficulty: ⭐⭐⭐): Master JOIN operations to correlate data across tables and refine with WHERE.
• Intel Transmission (Story): "Jakeyl “Beardstorm” Millan, one of our... 'asset recovery specialists,' is on the prowl. He's tracking a shipment of high-end stolen vehicles. The manifest is a mess, but he needs a clean list matching each boosted ride to its last known 'owner' – likely the thief. Time is critical; these cars will be on a freighter by dawn if he doesn't move fast."
• Your Assignment: Construct a query that returns the Name of the suspect (from GTA.Citizens), the Type of vehicle, and the Brand of all vehicles marked as stolen (IsStolen = 1 in GTA.Vehicles).

SELECT
    C.Name AS SuspectName,
    V.Type AS VehicleType,
    V.Brand AS VehicleBrand
FROM
    GTA.Citizens C
JOIN -- Inner join implies the vehicle must have an owner in the Citizens table
    GTA.Vehicles V ON C.CitizenID = V.OwnerID
WHERE
    V.IsStolen = 1;

Pro Tip: When joining tables, always specify which table a column belongs to if the column name exists in multiple tables (e.g., C.CitizenID, V.OwnerID). Using table aliases (C, V) keeps your queries concise.

MISSION 3: Reward Tracker

• Objective (Difficulty: ⭐⭐⭐): Utilize GROUP BY in conjunction with SUM and ORDER BY for financial intelligence.
• Intel Transmission (Story): "Frederick “Freddie Finesse” is a man who appreciates the bottom line. He's reviewing his crew's recent performance – specifically, the payout from all completed missions. He needs to know who the top earners are. Bonuses are on the line, and so is Freddie's patience with underperformers."
• Your Assignment: Write a SQL query that displays:
  • The Name of each operative (GTA.Citizens).
  • The total SUM of Reward they've earned from completed missions (linking through GTA.Assignments to GTA.Missions).
  Group the results by operative name and ORDER BY their total earnings from highest to lowest.

SELECT
    C.Name AS OperativeName,
    SUM(M.Reward) AS TotalEarningsFromCompletedMissions
FROM
    GTA.Citizens C
JOIN
    GTA.Assignments A ON C.CitizenID = A.CitizenID
JOIN
    GTA.Missions M ON A.MissionID = M.MissionID
WHERE
    A.Completed = 1 -- Critical filter for only completed missions
GROUP BY
    C.CitizenID, C.Name -- Group by primary key and displayed name
ORDER BY
    TotalEarningsFromCompletedMissions DESC;

MISSION 4: Mission Speed Metrics

• Objective (Difficulty: ⭐⭐⭐⭐): Employ GROUP BY with multiple aggregate functions (MIN, MAX, COUNT) for operational analysis.
• Intel Transmission (Story): "Kevin “Sunset Sniper” Gagante lives by the clock. Efficiency is everything. He needs a performance review of his agents. Some move like ghosts, swift and silent; others... well, they get the job done, eventually. He needs to know who's the quickest, who's the most methodical (read: slowest), and the total number of completed ops for each agent."
• Your Assignment: For each operative who has successfully completed missions, your report must return:
  • Their Name (GTA.Citizens).
  • The COUNT of missions they've Completed.
  • Their fastest mission completion time (MIN(TimeSpent) from GTA.Assignments).
  • Their slowest mission completion time (MAX(TimeSpent) from GTA.Assignments).
  Ensure you only consider records where TimeSpent is not null.

SELECT
    C.Name AS OperativeName,
    COUNT(A.MissionID) AS NumberOfMissionsCompleted,
    MIN(A.TimeSpent) AS FastestCompletionTime_Minutes,
    MAX(A.TimeSpent) AS SlowestCompletionTime_Minutes
FROM
    GTA.Citizens C
JOIN
    GTA.Assignments A ON C.CitizenID = A.CitizenID
WHERE
    A.Completed = 1 AND A.TimeSpent IS NOT NULL -- Filter for completed and timed missions
GROUP BY
    C.CitizenID, C.Name
ORDER BY
    NumberOfMissionsCompleted DESC, C.Name; -- Optional: Order by most active

MISSION 5: Elite Agent Filter

• Objective (Difficulty: ⭐⭐⭐⭐): Master the HAVING clause to filter grouped results based on aggregate conditions.
• Intel Transmission (Story): "A whisper on the dark net, Agent. Emilya “E-Mage” Illeeva, a legendary figure who pulls strings from the deepest shadows, is forming a new black-ops unit. Her recruitment standards are... exacting. She's only interested in operatives who have (1) successfully Completed at least 2 missions, AND (2) raked in a SUM of $4000 or more in total rewards. No rookies, no small-timers."
• Your Assignment: Write a query that returns only the Names of operatives (GTA.Citizens) who meet both of E-Mage's stringent conditions.

SELECT
    C.Name AS EliteOperativeCandidate
FROM
    GTA.Citizens C
JOIN
    GTA.Assignments A ON C.CitizenID = A.CitizenID
JOIN
    GTA.Missions M ON A.MissionID = M.MissionID
WHERE
    A.Completed = 1 -- Consider only completed missions for earning and count
GROUP BY
    C.CitizenID, C.Name
HAVING
    COUNT(A.MissionID) >= 2        -- Condition 1: At least 2 completed missions
    AND SUM(M.Reward) >= 4000.00;  -- Condition 2: At least $4000 earned

Solutions (Bootcamp & Full Training Missions)

(This section contains the expected results for Missions 1-5 of the "Full Training Missions" based on the original OCR'd data. Your results may vary with the expanded dataset.)

MISSION 1: Suspect Scanner

Expected Result:
A list of suspects who have a WantedLevel of 2 or higher. Example values:

• Chi-neme-rem Agana (Ghostface) – 2
• Marq Alejandro (Firewall) – 2
• Frederick Canning (Freddie Finesse) – 3
• Jakeyl Millan (Beardstorm) – 2
• J Torres (Midnight Mapper) – 2

Total rows expected: 5

MISSION 2: Vehicle Watchlist

Expected Result:
A list showing which suspects have a stolen vehicle, including:

• Remsey Mailjard – Motorcycle, ShadowRider
• Jakeyl Millan – Scooter, Zoomie
• J Torres – Van, StealthWagon

Total rows expected: 3

MISSION 3: Reward Tracker

Expected Result:
Names and total earnings of suspects who completed at least one mission, ordered from highest to lowest earnings. Top examples:

• Claudia Trejo – $6000
• Marq Alejandro – $5000
• Remsey Mailjard – $3000
• Cricelia Prado – $1500
• Chi-neme-rem Agana – $1500

Total rows expected: 5

MISSION 4: Mission Speed Metrics

Expected Result:
Each agent's name, how many missions they completed, and the fastest and slowest time. Example rows:

• Remsey Mailjard – 1 mission – Fastest: 40 min – Slowest: 40 min
• Marq Alejandro – 1 mission – Fastest/Slowest: 60 min
• Cricelia Prado – 1 mission – Fastest/Slowest: 25 min
• Chi-neme-rem Agana – 1 mission – Fastest/Slowest: 20 min
• Claudia Trejo – 1 mission – Fastest/Slowest: 55 min

Total rows expected: 5

MISSION 5: Elite Agent Filter

Expected Result:
Only the suspects who completed at least 2 missions and earned $4000 or more total.
Expected output:

• Claudia Trejo

Total rows expected: 1

(Note: With the "super uitgebreide" dataset and additional assignments previously discussed, more agents like Emilya Illeeva, Lena Petrova, and Remsey Mailjard might also qualify for Mission 5 in your actual database.)

SQL Bay City Blues - Pursuit & Justice (Detective Mega-Challenge)

🚓 SYSTEM MESSAGE: Badge #, a priority call just came in. High alert! This is not a drill.

Handler's Briefing (Detective Challenge)

Detective (as defined by the @PlayerOfficerName variable in the setup SQL, e.g., 'Alex Mercer'), listen up! We've got a Grand Theft Auto, Code 3. Isabelle “Izzy” Moreau – yes, the tech billionaire, that Izzy Moreau – just had her prized custom Pegassi Vacca (Vehicle ID 121) jacked. This isn't some street punk boosting a sedan; this is a professional hit on a high-value target. The Commissioner's already calling every five minutes.

The theft went down last night from her 'impenetrable' penthouse garage in the heart of Rockford Hills. Your case file is live (Incident ID – this ID was generated when you ran the GTA_SQL_Academy_with_Bootcamp_met data.sql script; you might need to query BCPD_Incidents to find the exact ID for Moreau's case if you didn't note it. Look for IncidentType 'Grand Theft Auto' reported by CitizenID 25).

Your orders are clear: retrieve that vehicle, identify the crew responsible, and bring them down. Bay City PD's reputation – and yours – is on the line.

DATABASE SCHEMA OVERVIEW (Relevant for this challenge)

PHASE 1: Initial Investigation & Intel Gathering (Detective Challenge)

Your Objective: Gather crucial information to build a picture of the case and potential suspects. Every query is a step closer to cracking this.

⭐ Challenge 1.1 — Confirm Your Identity & Case Assignment (Difficulty: ⭐)

"First things first, Detective. Let's make sure your credentials are in the system and you're officially assigned. We need to know who's running point on this high-stakes operation."

• Task: Write a SQL query to view your official BCPD profile.
• Intel Required: Display all information for your officer from the GTA.BCPD_Officers table.
• Hint: Use SELECT * and filter by your OfficerBadgeID (e.g., 778, or the ID you chose/was assigned in the setup script).

SELECT *
FROM GTA.BCPD_Officers
WHERE OfficerBadgeID = 778; -- Replace 778 with your badge ID

⭐ Challenge 1.2 — Review the Incident Report Details (Difficulty: ⭐)

"The initial report from Moreau (Citizen ID: 25) just hit your terminal, hot off the press. Every detail matters. Time of occurrence, exact location, initial observations... Scour it for anything out of place."

• Task: Retrieve all details from the incident report concerning the stolen Pegassi Vacca.
• Intel Required: Display all columns for the specific IncidentID you noted (or queried) from the briefing.

SELECT *
FROM GTA.BCPD_Incidents
WHERE IncidentID = ; -- Replace with the actual ID (e.g., 1001)

⭐ Challenge 1.3 — Profile Potential Auto Thieves (Difficulty: ⭐⭐)

"This wasn't some joyriding kid, Detective. Moreau's security is state-of-the-art. We need a list of individuals with the skills or the audacity for this kind of job. Pull up records of citizens known for vehicle-related expertise or those already flagged with a significant WantedLevel. Our thief is likely in this pool."

• Task: Identify citizens who have a Profession indicating expertise in car-theft or who have a WantedLevel ≥ 2.
• Intel Required: CitizenID, Name, Alias, Profession, WantedLevel — sorted by WantedLevel DESC then Profession.
• Hint: Possible "car theft" professions: 'Escape Vehicle Pro', 'Pro Driver', 'Hustler', 'System Breaker', 'Wheelwoman', 'Gadgeteer'.

SELECT CitizenID, Name, Alias, Profession, WantedLevel
FROM GTA.Citizens
WHERE Profession IN ('Escape Vehicle Pro', 'Pro Driver', 'Hustler', 'System Breaker', 'Wheelwoman', 'Gadgeteer')
   OR WantedLevel >= 2
ORDER BY WantedLevel DESC, Profession;

⭐ Challenge 1.4 — Check for a Pattern • Recent High-End Thefts (Difficulty: ⭐⭐)

"One high-end car theft is a headline. Two is a trend. Three is a goddamn epidemic. Is this an isolated score, or are we looking at a crew specializing in boosting luxury rides? Check the BCPD stolen vehicle database for any other 'Sports Car' or 'Superbike' types recently reported."

• Task: Display all 'Sports Car' and 'Superbike' vehicles currently listed as stolen (IsStolen = 1). Include the owner's name.
• Intel Required: V.VehicleID, C.Name AS OwnerName, V.Brand, V.Type.

SELECT
    V.VehicleID,
    C.Name AS OwnerName,
    V.Brand,
    V.Type,
    V.IsStolen
FROM GTA.Vehicles V
LEFT JOIN GTA.Citizens C ON V.OwnerID = C.CitizenID
WHERE V.IsStolen = 1
  AND V.Type IN ('Sports Car', 'Superbike');

PHASE 2: Evidence Collection & Witness Intel (Detective Challenge)

Your Objective: Analyze available evidence and witness information to narrow down your suspect list. The devil is in the details.

⭐ Challenge 2.1 — Review CCTV Footage Log (Difficulty: ⭐)

"Forensics just sent over their initial analysis of the CCTV footage from Moreau's garage. It's grainy, as always, but there might be something. Pull up the official evidence log for this case."

• Task: Display the evidence record with EvidenceType = 'CCTV Footage' linked to the stolen-Vacca incident.
• Intel Required: All columns from the relevant GTA.BCPD_Evidence record.

SELECT *
FROM GTA.BCPD_Evidence
WHERE IncidentID =  -- Replace with your ID
  AND EvidenceType = 'CCTV Footage';

⭐ Challenge 2.2 — Identify “Persons of Interest” (Difficulty: ⭐⭐)

"Moreau wasn't entirely unhelpful. She dropped a couple of names – individuals who've been sniffing around her assets, showing a little too much interest in her Vacca. Check the case file (BCPD_Suspects_In_Incidents) to see who your reporting officer officially flagged as 'Persons of Interest' based on her statement."

• Task: Display all suspects linked to the stolen-Vacca IncidentID.
• Intel Required: SuspectName, SuspectAlias, InvolvementLevel, SII.Notes.
• Hint: The setup script should have added Frederick "Freddie Finesse" Canning (ID 7) and Sofia "Sol" Ramirez (ID 23).

SELECT
    S.Name  AS SuspectName,
    S.Alias AS SuspectAlias,
    SII.InvolvementLevel,
    SII.Notes
FROM GTA.BCPD_Suspects_In_Incidents SII
JOIN GTA.Citizens S
  ON SII.SuspectCitizenID = S.CitizenID
WHERE SII.IncidentID = ; -- Replace with your ID

⭐ Challenge 2.3 — Background Check • Vehicles & Hideouts of Key Suspects (Difficulty: ⭐⭐⭐)

"Alright, Detective, our 'Persons of Interest' are Freddie Finesse (CitizenID 7) and Sol Ramirez (CitizenID 23). Let's run a background check on their known assets. What kind of wheels are they rolling in? Could they have used their own rides for reconnaissance or as a chase car? And more importantly, do they have properties with enough GarageSlots to make a Pegassi Vacca disappear?"

Part A – Vehicles

• Task: List all vehicles owned by Sofia “Sol” Ramirez and Frederick “Freddie Finesse” Canning.
• Intel: OwnerName, Type, Brand, Speed.

Part B – Properties

• Task: List all properties owned by the same two suspects, paying special attention to GarageSlots.
• Intel: OwnerName, PropertyName, PropertyType, Location, GarageSlots.

SELECT
    C.Name AS OwnerName,
    V.Type,
    V.Brand,
    V.Speed
FROM GTA.Citizens C
JOIN GTA.Vehicles V
  ON C.CitizenID = V.OwnerID
WHERE C.CitizenID IN (7, 23) -- For Freddie and Sol
ORDER BY C.Name, V.Speed DESC;

SELECT
    C.Name AS OwnerName,
    P.PropertyName,
    P.PropertyType,
    P.Location,
    P.GarageSlots
FROM GTA.Citizens C
JOIN GTA.Properties P
  ON C.CitizenID = P.OwnerID
WHERE C.CitizenID IN (7, 23)
ORDER BY C.Name, P.GarageSlots DESC;

Pro Tip: Using IN (value1, value2, ...) is a concise way to filter a column against multiple possible values, often more readable than multiple OR conditions.

PHASE 3: Closing In — The Takedown (Detective Challenge)

Handler's Update (Encrypted Channel): "Detective, solid gold from one of my CIs! The chatter on the street points directly to Sofia “Sol” Ramirez (Citizen 23). Word is she's the wheelwoman and the brains behind this. My source says she's got the Vacca cooled off in a warehouse controlled by her crew, the Street Racers Syndicate (Faction ID: 510), down at the Port of Bay City. This is our best lead. Verify it, locate that warehouse, and let's bring this car – and Ramirez – home."

⭐ Challenge 3.1 — Locate Faction Hideouts & Suspect Lairs (Difficulty: ⭐⭐⭐⭐)

"The informant was clear: Port of Bay City. We need to pinpoint that warehouse. Check properties directly owned by Sol Ramirez in that area. Also, run a broader search for any properties in the port linked to active members of the Street Racers Syndicate. One of them has to be our spot."

Part 1 – Sol’s direct properties in the port
Find properties owned by Sol Ramirez located near “Port of Bay City”.

Part 2 – Properties of active Street Racers Syndicate members in the port
Find properties owned by active members of Faction 510 located near the port. This is a tougher query!

SELECT
    C.Name        AS OwnerName,
    P.PropertyName,
    P.PropertyType,
    P.Location,
    P.GarageSlots
FROM GTA.Properties P
JOIN GTA.Citizens  C
  ON P.OwnerID = C.CitizenID
WHERE P.OwnerID = 23 -- Sol Ramirez
  AND P.Location LIKE '%Port of Bay City%';

SELECT DISTINCT -- Avoids listing the same property multiple times if multiple members are linked to it
    C_Owner.Name            AS PropertyOwner,
    P.PropertyName,
    P.PropertyType,
    P.Location,
    P.GarageSlots,
    C_Member_Alias.Alias    AS FactionMemberAssociated, -- Shows who in the faction is linked to the owner
    F.Name                  AS FactionName
FROM GTA.Properties P
JOIN GTA.Citizens               C_Owner         ON P.OwnerID = C_Owner.CitizenID             -- Get property owner details
JOIN GTA.CitizenFactionMembership CFM            ON C_Owner.CitizenID = CFM.CitizenID         -- Link owner to their faction memberships
JOIN GTA.Factions               F               ON CFM.FactionID    = F.FactionID            -- Get faction details
JOIN GTA.Citizens               C_Member_Alias  ON CFM.CitizenID    = C_Member_Alias.CitizenID -- Get details of the faction member (who is the owner here)
WHERE F.FactionID = 510 -- Street Racers Syndicate
  AND CFM.IsActive = 1
  AND P.Location LIKE '%Port of Bay City%';

Handler's Update (Priority Channel): "Units on standby near Pier 7. We have eyes on a warehouse matching the description: 'Sol’s Waterfront Hideout (SRS Used)' (should be PropertyID 407 from your setup). Thermal imaging confirms a vehicle matching the Vacca's profile inside. This is it, Detective. Go time. But first, paperwork – log that car as recovered evidence before the tac team breaches."

⭐ Challenge 3.2 — Log the Recovered Vehicle as Evidence (Difficulty: ⭐⭐)

"The SWAT team is ready to breach, but protocol is protocol. The recovered Pegassi Vacca (VehicleID: 121) needs to be officially logged as evidence before it's moved. Update the BCPD evidence database now."

• Task: Insert a new record into GTA.BCPD_Evidence.
• Details for INSERT: IncidentID = , CollectedByOfficerID = , EvidenceType = 'Vehicle', Description = "Recovered stolen red Pegassi Vacca, VIN matches victim's vehicle. Found inside warehouse at Pier 7. Minor scratches on passenger side door.", DateTimeCollected = GETDATE(), LocationFound = "Sol's Waterfront Hideout (SRS Used), Pier 7, Port of Bay City", LinkedVehicleID = 121.

INSERT INTO GTA.BCPD_Evidence
    (IncidentID, CollectedByOfficerID, EvidenceType, Description,
     DateTimeCollected, LocationFound, LinkedVehicleID)
VALUES
    (, -- Replace with your ID
     ,            -- Replace with your ID
     'Vehicle',
     'Recovered stolen red Pegassi Vacca, VIN matches victim''s vehicle. Found inside warehouse at Pier 7. Minor scratches on passenger side door.',
     GETDATE(),
     'Sol''s Waterfront Hideout (SRS Used), Pier 7, Port of Bay City',
     121);

⭐ Challenge 3.3 — Make the Arrest & Close the Case (Difficulty: ⭐⭐⭐)

"SWAT breached. Suspect Sofia 'Sol' Ramirez (CitizenID: 23) apprehended inside the warehouse, practically sitting on the hood of the Vacca. She didn't go quietly, but she's in cuffs. It's time to process her, update the case file, and inform the Commissioner we got our car back."

Part A – Arrest

• Task: Insert an arrest record for Sofia Ramirez (Citizen 23).
• Details for INSERT: IncidentID = , ArrestedCitizenID = 23, ArrestingOfficerID = , DateTimeArrested = GETDATE(), LocationOfArrest = "Sol's Waterfront Hideout...", ChargesFiled = "Grand Theft Auto (Sec 487 PC), Possession of Stolen Property (Sec 496 PC)", BookingNotes = "Suspect apprehended at scene with stolen vehicle. Resisted initially but subdued without further incident. Vehicle recovered.".

Part B – Close Incident

• Task: Update GTA.BCPD_Incidents.CurrentStatus to 'Closed – Arrest'.

Part C – (Bonus) Update Wanted Level

• Task: Set Sofia’s WantedLevel in GTA.Citizens to 4.

-- A: Arrest record
INSERT INTO GTA.BCPD_Arrests
    (IncidentID, ArrestedCitizenID, ArrestingOfficerID,
     DateTimeArrested, LocationOfArrest, ChargesFiled, BookingNotes)
VALUES
    (, -- Replace with your ID
     23,                       -- Sofia "Sol" Ramirez
     ,            -- Replace with your ID
     GETDATE(),
     'Sol''s Waterfront Hideout (SRS Used), Pier 7, Port of Bay City',
     'Grand Theft Auto (Sec 487 PC), Possession of Stolen Property (Sec 496 PC)',
     'Suspect apprehended at scene with stolen vehicle. Resisted initially but subdued without further incident. Vehicle recovered.');

-- B: Close the incident
UPDATE GTA.BCPD_Incidents
SET CurrentStatus = 'Closed - Arrest'
WHERE IncidentID = ; -- Replace with your ID

-- C: Bonus – increase wanted level
UPDATE GTA.Citizens
SET WantedLevel = 4
WHERE CitizenID = 23; -- Sofia "Sol" Ramirez

Handler's Commendation

Excellent work, Detective ! The Pegassi Vacca is back with its rightful owner, Ms. Moreau is ecstatic, and Sol Ramirez is facing serious time. The Commissioner sends his regards – said you handled this like a seasoned pro. Bay City is a little safer tonight thanks to you. Case closed!

SYSTEM MESSAGE: Case File # – Status: CLOSED. Commendation Awarded. Reputation Increased.

FINAL SYSTEM MESSAGE

Agent, you've been equipped with the fundamental and advanced SQL directives necessary to operate effectively in Bay City. The preceding bootcamp and training missions were designed to forge your data extraction skills. The "Bay City Blues" mega-challenge is your final exam. Remember, in this city, the ones who can read the data control the narrative. Don't just query the data; interrogate it. Make it talk. Your success, and perhaps your survival, depends on it.

End of Transmission.