curriculum/challenges/english/blocks/lecture-working-with-relational-databases/687ea8a88c9e9419af7cd8c3.md
Let's see some of the fundamental commands that you can use to insert and view data in SQL.
First of all, to view data, you need to insert it. So we'll start with insertions.
The examples that we will cover in this lesson will be based on this table of dog records:
CREATE TABLE dogs(
id SERIAL,
name VARCHAR(100),
age INTEGER
);
The table will store the names and ages of various dogs.
If we assume that we are working with PostgreSQL, we can use the SERIAL data type for the id. Since id was created using SERIAL, its value will be an INTEGER, starting from 1, and incremented automatically when a new record is inserted. So you will not need to pass a value for it when you insert a record.
Let's start by inserting records to our dogs table with the INSERT INTO statement. There are many ways to do this. The first one would be inserting a single row and specifying the columns of the values that will be inserted.
You write INSERT INTO, followed by the name of the table (in this case, dogs), then the columns within parentheses, then VALUES, and their corresponding values within parentheses.
This is an example, we're inserting a dog record with the name of 'Gino', whose age is 3, remember that the id will be assigned automatically:
INSERT INTO dogs (name, age)
VALUES ('Gino', 3);
This is usually the safest option because you specify the columns explicitly, so the values will be assigned to those columns in order.
Notice that 'Gino' needs to be in single quotes because it is a VARCHAR type.
Another alternative is to insert the record without specifying the columns, like this:
INSERT INTO dogs
VALUES ('Gino', 3);
This syntax is valid SQL, but it is error-prone.
Since values are assigned based on column order, PostgreSQL will try to insert 'Gino' into the id column, which expects an integer. This results in an error such as invalid input syntax for type integer.
For this reason, it is safer to explicitly specify the column names when inserting data.
You can also insert multiple records in the same SQL command by separating them with a comma.
Here, we are inserting two dog records, one for 'Gino' and another one for 'Nora'.
INSERT INTO dogs (name, age)
VALUES
('Gino', 3),
('Nora', 2);
And just like you can insert records, you can query information from the database. In the context of databases, a query is a request for data.
In SQL, the SELECT statement is used for querying data from one or more tables. You can customize the query to get the exact information that you need.
If you need to query all the information from the dogs table, you just need to write SELECT, followed by an asterisk (*), then FROM, and the name of the table, dogs:
SELECT *
FROM dogs;
In this command, the asterisk is a wildcard character that represents "all columns."
You will get all the data of all the records in the table:
id | name | age
----+------+-----
1 | Gino | 3
2 | Nora | 2
To query specific columns, you can write the names of those columns in the command, right after SELECT, separated by a comma. In this example, we query the name and age columns of the dogs table:
SELECT name, age
FROM dogs;
The result will only include the name and age columns:
name | age
------+-----
Gino | 3
Nora | 2
Sometimes, you might need to query data based on a specific condition. For example, to get all dogs who are less than 3 years old you can use the WHERE keyword and the less than (<) comparison operator:
SELECT *
FROM dogs
WHERE age < 3;
The result will only include dogs whose age is less than 3:
id | name | age
----+------+-----
2 | Nora | 2
If you are just trying to find the age of 'Gino', you can use the equals (=) comparison operator:
SELECT age
FROM dogs
WHERE name = 'Gino';
And here's the result:
age
-----
3
These are common and simple ways to insert and view data in SQL, but there are many different options that you can choose from and some of them are more advanced. You'll learn about them in coming lessons.
What does the asterisk (*) represent in the SQL statement SELECT * FROM products;?
It selects the last column from the products table.
Think about what you would need to write explicitly if you didn't use the asterisk.
It indicates that there is an error in the SQL query.
Think about what you would need to write explicitly if you didn't use the asterisk.
It selects all columns from the products table.
It selects all rows where a column contains the asterisk character.
Think about what you would need to write explicitly if you didn't use the asterisk.
3
Which SQL command is used to add a new record to a table named products?
SELECT name, price FROM products;
Think about the basic actions you perform on data. Which SQL keyword corresponds to adding something new?
INSERT INTO products (name, price) VALUES ('New Product', 4.5);
UPDATE products SET name = 'New Product' WHERE price = 4.5;
Think about the basic actions you perform on data. Which SQL keyword corresponds to adding something new?
CREATE TABLE products (name VARCHAR(100), price DECIMAL);
Think about the basic actions you perform on data. Which SQL keyword corresponds to adding something new?
2
Given the following query:
SELECT price FROM products WHERE category = 'Laptops';
What is the main purpose of the WHERE category = 'Laptops' clause?
To specify that only the price column should be displayed.
Think about the effect that this part of the query will have on which rows are included in the final result.
To sort the resulting list of products based on their category.
Think about the effect that this part of the query will have on which rows are included in the final result.
To filter the rows, returning only the products whose category is 'Laptops'.
To update the category of all products to 'Laptops'.
Think about the effect that this part of the query will have on which rows are included in the final result.
3