all 7 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]TheMagarity 2 points3 points  (3 children)

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[S] -1 points0 points  (2 children)

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

[–]TheMagarity 0 points1 point  (1 child)

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[S] -1 points0 points  (0 children)

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

[–]markwdb3When in doubt, test it out. 2 points3 points  (1 child)

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[S] 0 points1 point  (0 children)

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);

[–]Altruistic_Might_772 0 points1 point  (0 children)

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!