SQL Insert

Question 1

How do you add a row to a SQL table?

Answer 1

By using the “insert” statement as a means of adding rows to a table. The name of the table you’re adding the row to goes after in double quotes, followed by a parenthesis filled with the names of the columns you want to insert to in double quotes.

To add a row to a table, we would likely execute the following command:

insert into “products” (“name”, “description”, “price”, “category”)
values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’);

-The statement begins with the insert keyword.
-The table to insert into is specified in “ double quotes.
-The list of columns being inserted is wrapped in () parenthesis.
-The values being inserted are also wrapped in () in parenthesis in the same order as the columns they belong to. In SQL, a list of values is referred to as a tuple.
-Text values are wrapped in ‘ single quotes.
-Numeric values are represented with literal numbers (or decimals if applicable).
In this particular statement, the “productId” was left out. This is because tables are often configured to auto-generate identifier attributes to avoid accidental duplicates.

Question 2

What is a tuple?

Answer 2

In SQL, a list of values is referred to as a tuple.

Question 3

How do you add multiple rows to a SQL table at once?

Answer 3

Data rows can be batch inserted into a database table by specifying more than one tuple of values, separated by commas.

insert into “products” (“name”, “description”, “price”, “category”)
values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’),
(‘Tater Mitts’, ‘Scrub some taters!’, 6, ‘cooking’)
returning *;

Another example: 
insert into "languages" ("name")
values ('HTML'),
       ('CSS'),
       ('JavaScript')
returning *;

Question 4

How to you get back the row being inserted into a table without a separate select statement?

Answer 4

In PostgreSQL it’s possible to get the full inserted row back from the database, including it’s auto-generated attribute(s), like a productId.
This is done with a returning clause.
Otherwise, it would be necessary to make a separate query to the database just to get the auto-generated attributes.