previousnext
Lesson 24
Courses / Software School Cuts
Populating a SQL Database Table with New Data

Learn how to use Structured Query Language (SQL) to insert new rows into a table with a set of given column values.

You can also use SQL to add new data to a table. The lecture goes over the insert statement so you can populate a table with new rows.

You first learn the implicit way, then you learn the explicit way that requires you specify every column name in the table.

Along the way you are also exposed to the UNIQUE constraint that can be set on a specific column to prevent duplicate rows with the same column value. Primary key columns usually have that unique constraint.

Summary

Summary of Data Manipulation in SQL

In this transcript, the discussion revolves around manipulating data within a database, focusing on inserting new records into a "customers" table. Here’s a breakdown of key points:

Inserting New Records

  • Basic Syntax:

    INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

  • Example Insertion: To add a new customer:

    INSERT INTO customers (customerID, firstName, lastName, age, country) VALUES (6, 'Anna', 'Long', 21, 'Canada');

  • Auto Increment ID: Databases typically auto-increment primary keys, but in this environment, the ID must be specified manually.

Specifying Column Order

  • If the order of columns is unknown, you can specify column names explicitly:

    INSERT INTO customers (customerID, lastName, firstName, age, country) VALUES (7, 'Long', 'Anna', 21, 'Canada');

  • Error Handling: Attempting to insert a duplicate customerID results in an error due to a unique constraint. It's crucial that each ID remains unique to avoid conflicts.

Flexibility with Data

  • Little restriction is placed on most other fields (like first names or surnames), allowing for duplicates.
  • Correct order of values corresponding to specified column names is necessary for successful insertion.

Formatting and Readability

  • SQL queries can span multiple lines for better readability, and breaking lines does not affect the execution of the query.

This summary captures the fundamental concepts of inserting and managing data entries within a SQL database as outlined in the transcript.

Scroll down above for more

Video Transcript

Nice, we've talked a lot about querying data, like finding out information, but can we manipulate the data? I mean, can we add more records? Can we update them? Can we delete them? Let's talk about inserting new records. So when you talk about adding a new row, we talk about insert. Okay, so we're gonna say insert into customers, values, and then we're gonna put some parentheses, it's not me calling here. Now the syntax is all like this, insert space into, and then table name, space values, and some parentheses here, and within the parentheses, you gotta specify the value for each column in order, okay? In this case, you have to have the order. So the first, you look up in the left-hand side, you can see the customer ID is the first one. So as you can see, there's already a five, so I'm gonna add six. And then if I wanna separate these column values, I need a column. I like to add a space of a column, but that's not necessary. Okay, now I need, what's the next column here? After customer ID, there's first name, right? So I gotta write the first name within quotes. Let's say, I don't know, let's say somebody Anna. And then column, now I need the third, that's the last name, so last name, I'm say long. And then the fourth column here is age, I'm gonna say 21, Alma. Now the next column is country, let's say Canada. Okay, now let's click run. See what happened there? If you notice, we now have a new row in the column customers. So typically when you have databases, the ID column, usually auto increments for you, so you don't have to specify it, but here it's not really, I don't think it works that way in this environment. So I have to specify the ID there. Okay, you might be able to see Okay, you might be asked, okay, I have to always follow the same order, but if I don't know, for some reason, I don't know the order of the columns. Obviously if you have access to database, you're gonna know the order, but let's suppose you don't know. You can always go into here before values, you can specify the column names here. Let's say I say customer ID is the first, but then let's, for some reason, use last name before the first name. And then Asian country. So make sure it's specified all the column names separated by a comma. Now you might notice there's no quotes around them because they're a special thing, they're column names, so no need. Now if you notice I put last name before first, now if I do this, I gotta swap these two because this one is for the first one here, so they're all like corresponding in order. The last name in this case would be Anna, which is wrong, so I would have to swap this one with that one there. Make sure the syntax is correct. And I put in a new row so you can see better. The line break doesn't matter, okay? I can break it in a new line and that won't change your thing. I just made it so you can see it better. You might have noticed I'm typing everything in a single line, but you don't have to. Usually the SQL queries will be very long so you can break them down multiple lines. And there's usually a name, a convention for that if you want to follow. Okay, what happens if I do that? Oh no, if you read the error, unique constraint failed on the customer ID. So it says table name dot column name. You're gonna see this a lot. So unique constraint. So the column customer ID has a constraint called unique meaning I cannot have a same row of different rows with the same customer ID. So that's important because it has to be something unique so that every row has something unique you can query for. So since I cannot have that there, I'm gonna put seven here. Now if you notice, there's no protection about first name and last name. It can't be the same. You know, probably in the world there are people with the same name, right? So I wouldn't add a unique constraint to the names. And probably they could have the same age and same country. That's totally possible to have these kind of things. They could be the same different people by the same name. Okay, and if you notice the last name is the same even though the order here was changed. So if you need to order any way you want, just specify the column names here and order any way you want, but make sure that the values right hand side here is corresponding in order to the names that you wrote. So that's the way to do it. So if you want to have a different name, you can have a different name. So if you want to have a different name, you can have a different name. So if you want to have a different name, you can have a different name. So if you want to have a different name, you can have a different name. So if you want to have a different name, you can have a different name. So if you want to have a different name, you can have a different name. So if you want to have a different name,
See more
No comments yet (loading...)
No comments yet (loading...)
Did you like the lesson? 😆👍
Consider a donation to support our work:
See donation links