SQL Tutorial 3 INSERT INTO, Update & Etc

SQL Tutorial 3 INSERT INTO, Update & Etc.

SQL INSERT INTO with example

In SQL, the INSERT INTO statement is used to add new records (rows) to a database table. The general syntax for the INSERT INTO statement is as follows:

INSERT INTO table_name (column1, column2, column3, …)

VALUES (value1, value2, value3, …);

Here’s an example of how to use the INSERT INTO statement in SQL:

Suppose we have a table named customers with the following structure:

CREATE TABLE customers (

    customer_id INT PRIMARY KEY,

    customer_name VARCHAR(50),

    email VARCHAR(50)

);

We can insert a new record into the customers table using the INSERT INTO statement as follows:

INSERT INTO customers (customer_id, customer_name, email)

VALUES (1, ‘John Doe’, ‘john@example.com’);

This statement will insert a new record with the customer_id of 1, the customer_name of ‘John Doe’, and the email of ‘john@example.com’ into the customers table.

You can also insert multiple records at once by providing multiple sets of values within the VALUES clause, like this:

INSERT INTO customers (customer_id, customer_name, email)

VALUES (2, ‘Jane Smith’, ‘jane@example.com’),

       (3, ‘Michael Johnson’, ‘michael@example.com’),

       (4, ‘Emily Williams’, ’emily@example.com’);

This statement will insert three new records into the customers table with the specified values.

Make sure that the values provided match the data types and constraints of the columns in the table to avoid any errors during insertion.

SQL Update with example

In SQL, the UPDATE statement is used to modify existing records in a table. The general syntax for the UPDATE statement is as follows:

UPDATE table_name

SET column1 = value1, column2 = value2, …

WHERE condition;

Here’s an example of how to use the UPDATE statement in SQL:

Suppose we have a table named customers with the following data:

+————-+—————+——————-+

| customer_id | customer_name | email             |

+————-+—————+——————-+

| 1           | John Doe      | john@example.com  |

| 2           | Jane Smith    | jane@example.com  |

| 3           | Michael Johnson | michael@example.com |

+————-+—————+——————-+

We can use the UPDATE statement to modify the email of the customer with customer_id 3 as follows:

UPDATE customers

SET email = ‘michael.johnson@example.com’

WHERE customer_id = 3;

This statement will update the email of the customer with customer_id 3 to ‘michael.johnson@example.com’.

You can also update multiple columns simultaneously:

UPDATE customers

SET customer_name = ‘Michael J. Johnson’, email = ‘michael.j@example.com’

WHERE customer_id = 3;

This statement will update both the customer_name and email of the customer with customer_id 3.

Make sure to include a WHERE clause to specify the condition that identifies the record(s) you want to update. Omitting the WHERE clause will result in updating all records in the table.

SQL Not where use and example

In SQL, the NOT operator is used to negate the logical state of a condition. When combined with the WHERE clause, it allows you to retrieve rows that do not meet a certain condition. Here’s an example of how to use the NOT operator with the WHERE clause:

Suppose we have a table named students with the following data:

+————+———–+——–+

| student_id | full_name | grade  |

+————+———–+——–+

| 1          | John Doe  | A      |

| 2          | Jane Smith| B      |

| 3          | Michael Johnson | A |

| 4          | Emily Williams | C  |

+————+———–+——–+

To retrieve all the students who do not have an ‘A’ grade, you can use the NOT operator in combination with the WHERE clause:

SELECT *

FROM students

WHERE grade <> ‘A’;

+————+—————+——–+

| student_id | full_name     | grade  |

+————+—————+——–+

| 2          | Jane Smith    | B      |

| 4          | Emily Williams| C      |

+————+—————+——–+

The <> operator in the WHERE clause is used to check for values that are not equal to ‘A’. Therefore, the query returns all rows where the ‘grade’ column is not ‘A’.

You can also use the NOT operator with other logical conditions, such as NOT LIKE, NOT IN, and NOT BETWEEN, to further filter and retrieve specific rows from your table.

what is SQL Null Values and example

In SQL, a NULL value represents a missing or unknown data value. It is not the same as zero or an empty string (”). A column in a table can contain a NULL value if no value has been provided for that column or if the value is unknown or not applicable.

Here is an example of how NULL values can be used in SQL:

Suppose we have a table named employees with the following data:

In this example, the ‘manager’ column represents the ID of the employee’s manager. The NULL value for the ‘manager’ column indicates that the corresponding employee does not have a manager (e.g., the CEO or a top-level executive).

You can use the IS NULL and IS NOT NULL operators to check for NULL values in SQL. For example, to select all employees without a manager, you can use the following query:

SELECT *

FROM employees

WHERE manager IS NULL;

This query will return the row for John Doe from the employees table because he does not have a manager.

Similarly, to select all employees who have a manager assigned, you can use the following query:

SELECT *

FROM employees

WHERE manager IS NOT NULL;

This query will return the rows for Jane Smith, Michael Johnson, and Emily Williams from the employees table.