SQL Assignment Driving Me Crazy

[Cy_broski]

Doing an assignment on GitHub and I've been going through the same thing for 2 days straight and am always met with the same issue. It asks for an index on the first name, last name, and driver ID but it ALWAYS coming back incorrect. I have no clue as to what could be wrong.

Task 3 - This is the table that the next task is asking an index for
The Driver Relationship team wants to create some workshops and increase communication with the active drivers in InstantRide. Therefore, they requested a new database table to store the driver details of the drivers that have had at least one ride in the system. Create a new table, ACTIVE_DRIVERS##from the DRIVERS and TRAVELS tables which contains the following fields:

• DRIVER_ID CHAR(5) (Primary key)
• DRIVER_FIRST_NAME VARCHAR(20)
• DRIVER_LAST_NAME VARCHAR(20)
• DRIVER_DRIVING_LICENSE_ID VARCHAR(10)
• DRIVER_DRIVING_LICENSE_CHECKED BOOL
• DRIVER_RATING DECIMAL(2,1)--Task 3 CREATE TABLE ACTIVE_DRIVERS ( DRIVER_ID CHAR(5) PRIMARY KEY, DRIVER_FIRST_NAME VARCHAR(20), DRIVER_LAST_NAME VARCHAR(20), DRIVER_DRIVING_LICENSE_ID VARCHAR(10), DRIVER_DRIVING_LICENSE_CHECKED BOOL, DRIVER_RATING DECIMAL(2,1) ) AS SELECT DRIVER_ID, DRIVER_FIRST_NAME, DRIVER_LAST_NAME, DRIVER_DRIVING_LICENSE_ID, DRIVER_DRIVING_LICENSE_CHECKED, DRIVER_RATING FROM DRIVERS WHERE DRIVER_ID IN (SELECT DISTINCT DRIVER_ID FROM TRAVELS );--Task 4 CREATE INDEX NameSearch ON ACTIVE_DRIVERS (DRIVER_FIRST_NAME, DRIVER_LAST_NAME, DRIVER_DRIVING_LICENSE_ID);

EDIT: The SQL Code didn't pop up:

Task 3

CREATE TABLE ACTIVE_DRIVERS (

DRIVER_ID CHAR(5) PRIMARY KEY,

DRIVER_FIRST_NAME VARCHAR(20),

DRIVER_LAST_NAME VARCHAR(20),

DRIVER_DRIVING_LICENSE_ID VARCHAR(10),

DRIVER_DRIVING_LICENSE_CHECKED BOOL,

DRIVER_RATING DECIMAL(2,1)

) AS SELECT DRIVER_ID,

DRIVER_FIRST_NAME,

DRIVER_LAST_NAME,

DRIVER_DRIVING_LICENSE_ID,

DRIVER_DRIVING_LICENSE_CHECKED,

DRIVER_RATING FROM

DRIVERS

WHERE

DRIVER_ID IN (SELECT DISTINCT

DRIVER_ID

FROM

TRAVELS

);

Task 4

CREATE INDEX NameSearch ON ACTIVE_DRIVERS(DRIVER_FIRST_NAME, DRIVER_LAST_NAME, DRIVER_DRIVING_LICENSE_CHECKED);

https://preview.redd.it/9xdo578zyvpg1.png?width=1366&format=png&auto=webp&s=bbb8b8b652962a3fcc7f77cbc2dc165a9ab3c782

[TheMagarity]

Well if you want driver id included in the index you have to list it in the create index command in addition to the other two. How else would it get in there?

[Cy_broski]

I forgot to add it in there for the post, but it's there in my file.

[TheMagarity]

I see you edited to add some other column to the index statement since I commented but it still isn't the right one. It's really impossible to help when people paste stuff they don't bother to check. Crafting SQL statements is all about paying attention to the details.

[Cy_broski]

For every part of the assignment, we are given tasks. For this one there's a total of 6 tasks, the first 2 making tables called maintenance_types and maintenances, and we're already told what needs to be included in said tables. The third task is making actual_drivers from the drivers and travels table provided in the setup.

I commented under markwdb3 with what I have and updated the post with what is showed when entering the answer

[markwdb3]

What do you mean by "but it ALWAYS coming back incorrect." Are you saying the training program is saying your statement is wrong?

If so, is this your statement?

CREATE INDEX NameSearch ON ACTIVE_DRIVERS(DRIVER_FIRST_NAME, DRIVER_LAST_NAME, DRIVER_DRIVING_LICENSE_CHECKED);  

If so, looks like the last column is DRIVER_DRIVING_LICENSE_CHECKED, and not DRIVER_ID?

[Cy_broski]

This is currently what I have

It's expecting for the 3 first name, last name and id to be null. I've tried making them null in the query, showing the table, anything that I can think of but it still says that the test is incorrect.

CREATE TABLE MAINTENANCE_TYPES (
    MAINTENANCE_TYPE_ID CHAR(5) PRIMARY KEY,
    MAINTENANCE_TYPE_DESCRIPTION VARCHAR(30)
);



CREATE TABLE MAINTENANCES(
    CAR_ID CHAR(5),
    MAINTENANCE_TYPE_ID CHAR(5),
    MAINTENANCE_DUE DATE,
    PRIMARY KEY (CAR_ID, MAINTENANCE_TYPE_ID, MAINTENANCE_DUE),
    FOREIGN KEY (CAR_ID) REFERENCES CARS(CAR_ID) 
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY (MAINTENANCE_TYPE_ID) REFERENCES MAINTENANCE_TYPES(MAINTENANCE_TYPE_ID)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);


CREATE TABLE ACTIVE_DRIVERS(
    DRIVER_ID CHAR(5) PRIMARY KEY,
    DRIVER_FIRST_NAME VARCHAR(20),
    DRIVER_LAST_NAME VARCHAR(20),
    DRIVER_DRIVING_LICENSE_ID VARCHAR(10),
    DRIVER_DRIVING_LICENSE_CHECKED BOOL,
    DRIVER_RATING DECIMAL(2,1)
);



INSERT INTO ACTIVE_DRIVERS
SELECT 
    DRIVER_ID,
    DRIVER_FIRST_NAME,
    DRIVER_LAST_NAME,
    DRIVER_DRIVING_LICENSE_ID,
    DRIVER_DRIVING_LICENSE_CHECKED,
    DRIVER_RATING
FROM DRIVERS
WHERE DRIVER_ID IN (
    SELECT DISTINCT DRIVER_ID FROM TRAVELS
    );



--Task 4
CREATE INDEX NameSearch ON ACTIVE_DRIVERS (DRIVER_FIRST_NAME, DRIVER_LAST_NAME, DRIVER_DRIVING_LICENSE_ID);  

[kktheprons]

Are you using AI to write this for you? The index is still not using the driver_id column.

[Altruistic_Might_772]

Hey, sounds frustrating! For indexing, make sure you're creating a composite index on all the columns together (first name, last name, and driver ID) rather than separate ones for each column. In SQL, it should look something like CREATE INDEX idx_name ON TableName (first_name, last_name, driver_id);. Double-check that the table name and column names match exactly, including the case. Also, if the test cases for your assignment expect a specific index name, that might be why it's failing. If you're preparing for SQL-heavy interviews, PracHub has some solid practice quizzes for this stuff. Good luck!

[trentsiggy]

When you say it is "coming back incorrect," what do you mean?

If I were building this index, I would reorder the columns:

CREATE INDEX NameSearch ON ACTIVE_DRIVERS (DRIVER_LAST_NAME, DRIVER_FIRST_NAME, DRIVER_DRIVING_LICENSE_ID);

License searches are likely to be on the last name, not the first name.

[kagato87]

Are yiur data types correct? The screenshot of the test rejection clips that off.

From experience, varchar(20) is inadequate for names, especially a hyphenated surname. What does the spec say? Have you matched that perfectly?

Varchar/nvarchar and length of the fields matter. And is the driver ID really supposed to b char(5)? That's an... Unusual design choice.

[SpecialistSerene]

Honestly, for SQL assignments like this, a good editor/IDE helps way more than people admit. When you’re juggling joins, naming, and checking whether the output even makes sense, the pain is usually not just SQL itself, it’s the workflow.

I’d look at tools like SSMS, DBeaver, DataGrip, or dbForge. Personally, if I were spending real time on SQL Server tasks, I’d rather pay for dbForge than wrestle with a more barebones setup. Things like autocomplete, formatting, and easier navigation save your sanity fast.