Create a Table - Learn SQL

I just completed this exercise. One question I had about it. How do you re-index the rows after you remove one? Step #8 wants you to Wait, Jane Smith is not a real person. Remove her from friends.. What this does is cause this in the picture below.


/*Create a table named friends with three columns: 

id that stores INTEGER

name that stores TEXT

birthday that stores DATE*/

CREATE TABLE friends ( id INTEGER, name TEXT, birthday DATE);

/*Beneath your current code, add Jane Doe to friends.Her birthday is May 30th, 1990.*/

INSERT INTO friends ( id, name, birthday) VALUES ( 1, 'Jane Doe', '1990-05-30');

/*Let’s make sure that Jane has been added to the database: SELECT * FROM friends;*/

/*Add two of your friends to the table.  Insert an id, name, and birthday for each of them.*/

INSERT INTO friends ( id, name, birthday) VALUES ( 2, 'Philip', '1980-05-30');

INSERT INTO friends ( id, name, birthday) VALUES ( 3, 'Scott', '1970-05-30');

/*Jane Doe just got married! Her new last name is “Smith”.Update her record in friends.*/

UPDATE friends SET name = 'Jane Smith' WHERE id = 1;

/*Add a new column named email.*/

ALTER TABLE friends ADD email TEXT;

/*Update the email address for everyone in your table.*/

UPDATE friends SET email = '' WHERE id = 1;

UPDATE friends SET email = '' WHERE id = 2;

UPDATE friends SET email = '' WHERE id = 3;

/*Wait, Jane Smith is not a real person.  Remove her from friends.*/

DELETE FROM friends WHERE id = 1;

/*Great job! Let’s take a look at the result one last time:*/

SELECT * FROM friends;
1 Like

What index?
Maybe what you really mean is that you don’t want an id column

Well, the row they wanted you to remove was 1, as in id 1. So, when you do so, it left row 2, 3, 4, in place. It did not re-index the table. So, how could I re-index the table so that 2 = 1, 3 =2, 4 =3?

1 Like

Kay, but, why do you say id is index rather than for example the name?
What is id an abbreviation of? (not index)
What is the general purpose of identity, and is change part of that?

Old/bad habits :stuck_out_tongue: But I think I just realized how this would be done. I would have to change id in all instances by -1. How? Not sure, but I assume that is what the answer is.

In C style language I would do this with a loop. I’m unsure if there is a loop in SQL, or something that mimics it to change the id by -1 in all rows.

You could modify values with UPDATE and I still don’t think it’s something you want

OK, so lets say we have a table. id is used here to enumerate the row. We delete a row. How is that to be handled in a real world situation? Is this normal to have NULL rows in a database? I’m just having a hard time seeing how you would just delete a row and forget about it? I can understand if it was sorted by like a name, so it didn’t matter, but when its numerical like this, I would think it would matter?

what says identities need to be consecutive numbers starting from 1
identity and index are two separate concepts, an identity is a unique value for something, an index is a way of finding something

your social security number is an identity. it does not change when somebody dies/is born

Think I see your point now and how I am overthinking this or thinking in the wrong direction. In this exercise id is simply that. It’s not an index.

This question came to mind because of past use of SQLite on a video game server I have developed for many years. It has SQLite, and one feature was a PvP system. Basically fighting. So you would log win/loss/ratio in short among other things. Well, lets say you ban a player, and you want to purge all there records. OK so we remove those rows. Well, in this case, we need every row sorted without any skipping in the index column. Sure, I could use an app to repack the database. But what if we wanted to do so by script after the ban was in place.

Hope that all makes sense and to give you an idea where I am coming from with this line of thought.

makes me wonder both why they’d need to have some consecutive numbering (I get the feeling it really shouldn’t matter, what would that be used for, is that a bug/bad idea) and also makes me wonder why they’d need to get removed if they’re banned, overkill

but yeah if you needed to change things … update

Toxic players exist in gaming. Sometimes no matter what you do, the only solution is to ban them. I try my best to script around such behavior to prevent unwanted behavior, but sometimes either its not possible, or requires immediate attention.

As for the consecutive numbering, imagine a scoreboard/ranking system. You wouldn’t want your ranking to be non-sequential or have gaps in it.

So you’d filter by not banned, sort by score, take 10

That’s sounds like a better idea. That is, to have a new column IsBanned and omit all results that return true in that column when you query the table. That way, if you wish to un-ban someone, the data’s integrity remains.

If you did not have such a column, sure, removing works. Still not an issue with id’s though. You’d take as many results as you wanted to show. There’s no gap in that.