[SQL] Day 2: INSERT INTO & SELECT WHERE

Today, we learned about:

• Row inserting
• SELECT

 

---

Row Insert

 

Continued from yesterday's database, our employee table in myDB database has following columns

columns of employees table

 

We want to add a row of data into the table. 

In such situation, we will be using INSERT command. 

INSERT INTO employees
VALUES (1, "Eugene", "Krabs", 25.50, "2024-10-22");

When insrting a row to a table, the new data have to follow the order of the column.

 

However, using a whole block of statements for a single data insert could be tedious. 

Obviously, MySQL supports multiple data input at one time as following:

INSERT INTO employees
VALUES 	(2, "Squidward", "Tentacles", 15.00, "2024-11-12"),
        (3, "Spongebob", "Squarepants", 12.50, "2024-12-01"),
        (4, "Patrick", "Star", 12.50, "2024-12-02"),
        (5, "Sandy", "CHeeks", 17.25, "2024-01-10");

SELECT * from employees;

 

Then, what would happen when we attempt to add a row with missing data?

INSERT INTO employees
VALUES (6, "Sheldon", "Plankton");

 

Any attempt of adding a data with missing column will fail as following.

 

 

However, there is still a way to add a data with missing columns by stating the new data as:

INSERT INTO employees (employee_id, first_name, last_name)
VALUES (6, "Sheldon", "Plankton");

Since the new row have insufficient data, we can explicitly state the existing data and save the new row with null on the missing spots. 

 

---

SELECT

 

We have to use SELECT statement when we want to select data from a table.

 

To query all of the data from a table, we have to use following familiar line of statement:

SELECT * FROM employees;

It will give you all columns and all rows of the table.

 

 

But sometimes, you don't need all of the data from a table. 

 

Say that our boss asked us to retrieve all the full name of the employees from our database. 

Then, we would not need to collect data from the columns other than first_name and last_name.

SELECT first_name, last_name FROM employees;

We can change the order by simply changing it in the statement.

SELECT last_name, first_name FROM employees;

 

 

We can also use WHERE statement together with SELECT to filter select specific data.

Following code will select only the employee with the data employee_id = 1

SELECT *
FROM employees
WHERE employee_id = 1;

 

 

One more example of WHERE statement. 

We can find an employee with his first name Spongebob by using following script.

SELECT *
FROM employees
WHERE first_name = "Spongebob";

 

 

Another example where we want to select the employees with over than $15.00 hourly wage. 

We can easily obtain the result with WHERE statement again. 

SELECT *
FROM employees
WHERE hourly_pay >= 15;

 

 

As you've seen in the example, we can use the relational operators (=, >, <, >=, <=, etc) with WHERE to SELECT desired data from a table. 

Another important relational operator is not-equal-to operator (!=). 

 

Let's find all the employees except the employee with his employee_id = 1

SELECT *
FROM employees
WHERE employee_id != 1;

 

 

Now, suppose we are HR manager and we want to require a document for the employees that have no data for their hire data (hire_data). 

SELECT *
FROM employees
WHERE hire_date IS NULL;

Here, we used IS NULL statement to filter the rows with their hire_data = NULL.

BUT, it doesn't work as expected if we use 'WHERE hire_date = NULL;'.

We MUST use IS for NULL, so take that into account.

 

 

Finally, we can also use IS NOT NULL to check every people with a valid data.

SELECT *
FROM employees
WHERE hire_date IS NOT NULL;