SQL #8: Modifying Tables, Rows & Columns

@grchab

#sql #learnsql #sqltips #sqltutorial #learnwithtiktok #learntocode #learninpublic

♬ original sound – Curiosity Corner 🙇🏽‍♂️

To add a row into a table:

INSERT INTO table VALUES (column1, column2, column3, column4, column5);

This code is all general, there’s no example table. We use the INSERT INTO statement and follow that by the table name (where it says ‘table’). The VALUES keyword is what lets us select the columns we’re inserting these values into. Again, these are placeholder texts, you wouldn’t actually type these. The first value where it says ‘column1’ means it would put that value under the first column for that new row, and so on for the other columns. Keep in mind that most datatables will have ‘ID’ as their first column since it’s unique to whatever instance it represents. Just how 2 people can have the same exact name, but their social security number is what sets them apart.

For that, IDs make it easier to select an instance to modify it.

UPDATE table
SET selected_column = updated value
WHERE id = 2;

This is the code if I wanted to update a row. Use the UPDATE keyword followed by the table name. Then use the SET statement to whatever column you want to update, and equal that to the new value you want to update it to. Remember if it’s text, you have to wrap it in double quotes. And use the WHERE clause to select the instance by its ID number.

UPDATE table
SET selected_column, selected_column2 = updated value1, updated value2
WHERE id = 2;

UPDATE table
SET selected_column = updated value, selected_column2 = updated value
WHERE id = 2;

If you want to update multiple columns in a row, do the full first change, then separate it with a comma and do the other one. Don’t SET column1, column2 and do both changes at once, like in the first code. Do column 1’s update all the way through, then separate with a comma and do your other columns still separately, the second code is the correct way.

To delete a row:

DELETE FROM table
WHERE column = ;

Use a DELETE FROM statement followed by the table number. Then a WHERE clause with the selected column and whatever you want to do with it. It’ll usually be an equal sign but it can be others too.

Moving on from rows, now we’re creating tables.

CREATE TABLE name (
    Column1 TEXT,
    Column2 FLOAT,
    Column3 INTEGER
);

CREATE TABLE is what prompts it, ‘name’ is the placeholder, you can name it whatever you choose. The statements within the parentheses are the columns. We first name the column, then follow that by the type of values the column will take. TEXT and INTEGER speak for themselves. FLOAT is for floating point values which are basically decimals. INTEGER only takes whole numbers. Thing is, FLOAT can take whole numbers, so you might be wondering why not just make all numerical columns FLOAT? Simple answer, INTEGER takes up less space for the computer, so there’s no point in making it a FLOAT if you know for a fact there will only be whole numbers in that column.

To add a column we use an ALTER TABLE statement and return with an indented ADD COLUMN.

ALTER TABLE name
  ADD COLUMN Column_name FLOAT DEFAULT 0.00; 

In this case we made it take FLOAT values, and we can set a default number to that value, which would be the place of 0.00.

And lastly, if we want to delete a table, just write a DROP TABLE statement along with the table’s name. Nothing more than that.