SQL: Inserting Data

SQL: Inserting Data

To insert data into a SQL table, you can use the INSERT statement. The INSERT statement allows you to specify the table name and the values you want to insert into the corresponding columns. Here’s an example:

Consider a table named “Employees” with columns “ID,” “Name,” and “Department.” We want to insert a new employee into this table with the following details: ID = 1, Name = “John Doe,” Department = “Sales.”

INSERT INTO Employees (ID, Name, Department)
VALUES (1, 'John Doe', 'Sales');

After executing this INSERT statement, the data will be inserted into the “Employees” table as follows:

Employees table:

+----+-----------+------------+
| ID |   Name    | Department |
+----+-----------+------------+
| 1  | John Doe  | Sales      |
+----+-----------+------------+

Here’s a breakdown of the INSERT statement components:

• INSERT INTO: Specifies the table name where the data will be inserted.
• Employees: The name of the table.
• (ID, Name, Department): The column names in which data will be inserted.
• VALUES: Specifies the values to be inserted into the columns.
• (1, ‘John Doe’, ‘Sales’): The actual values to be inserted, matching the respective column order.

You can also insert multiple rows in a single INSERT statement by separating the value sets with commas. Here’s an example:

INSERT INTO Employees (ID, Name, Department)
VALUES 
  (2, 'Jane Smith', 'Marketing'),
  (3, 'Mike Johnson', 'IT');

After executing this INSERT statement, the “Employees” table will look like this:

Employees table:

+----+--------------+------------+
| ID |     Name     | Department |
+----+--------------+------------+
| 1  | John Doe     | Sales      |
| 2  | Jane Smith   | Marketing  |
| 3  | Mike Johnson | IT         |
+----+--------------+------------+

Remember to ensure that the data you insert matches the column types and constraints defined in the table structure.

SQL: Delete

In SQL, a delete query is used to remove one or more records from a table in a database. It allows you to delete specific rows that match certain conditions or delete all rows from a table. The syntax for a delete query typically follows this structure:

DELETE FROM table_name
WHERE condition;

Let’s break down the components of the delete query:

• DELETE FROM: This phrase indicates that you want to delete records from a specific table.
• table_name: Specifies the name of the table from which you want to delete records.
• WHERE: This clause is optional but commonly used to specify the conditions that must be met for a record to be deleted. It allows you to target specific rows based on certain criteria.
• condition: Specifies the criteria that must be satisfied for a row to be deleted. It can consist of one or more conditions linked with logical operators such as AND, OR, and NOT.

Here’s an example to illustrate how a delete query works. Suppose we have a table called “employees” with the following structure and data:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  department VARCHAR(50)
);

INSERT INTO employees (id, name, age, department)
VALUES
  (1, 'John Doe', 30, 'HR'),
  (2, 'Jane Smith', 28, 'IT'),
  (3, 'Michael Johnson', 35, 'Sales'),
  (4, 'Emily Davis', 32, 'HR');

Now, let’s say we want to delete the employee with an ID of 2 from the “employees” table. We can use the following delete query:

DELETE FROM employees
WHERE id = 2;

After executing this query, the “employees” table will be updated, and the row with ID 2 will be deleted. The resulting table will look like this:

id	name	age	department
1	John Doe	30	HR
3	Michael Johnson	35	Sales
4	Emily Davis	32	HR

Note that if you omit the WHERE clause in the delete query, it will delete all rows from the specified table. Therefore, it’s crucial to be cautious while using delete queries without conditions to avoid unintended data loss.

SQL: Delete multiple rows and columns

To delete multiple rows and columns in SQL, you can use the DELETE statement with a WHERE clause to specify the conditions for the rows you want to delete. Here’s an example to illustrate how to delete multiple rows and columns:

Suppose we have a table called “employees” with the following structure and data:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  department VARCHAR(50)
);

INSERT INTO employees (id, name, age, department)
VALUES
  (1, 'John Doe', 30, 'HR'),
  (2, 'Jane Smith', 28, 'IT'),
  (3, 'Michael Johnson', 35, 'Sales'),
  (4, 'Emily Davis', 32, 'HR');

Now, let’s say we want to delete all employees whose department is ‘HR’ and age is less than 31. We can use the following delete query:

DELETE FROM employees
WHERE department = 'HR' AND age < 31;

After executing this query, the “employees” table will be updated, and all employees who satisfy the specified conditions will be deleted. The resulting table will look like this:

id	name	age	department
2	Jane Smith	28	IT
3	Michael Johnson	35	Sales

The rows with ID 1 and ID 4, which have a department of ‘HR’ and an age less than 31, have been deleted.

Note that the WHERE clause in the DELETE statement is crucial to specify the condition for deleting the rows. Without it, all rows in the table would be deleted.

SQL: Update

In SQL, an update query is used to modify existing records in a table. It allows you to update one or more columns of one or multiple rows based on specified conditions. The syntax for an update query typically follows this structure:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Let’s break down the components of the update query:

• UPDATE: This keyword indicates that you want to update records in a specific table.
• table_name: Specifies the name of the table in which you want to update records.
• SET: This phrase is used to specify the columns that you want to update and the new values you want to assign to them.
• column1 = value1, column2 = value2, ...: Specifies the columns you want to update and the new values you want to assign to them. Separate each column-value pair with commas.
• WHERE: This clause is optional but commonly used to specify the conditions that must be met for the rows to be updated. It allows you to target specific rows based on certain criteria.
• condition: Specifies the criteria that must be satisfied for a row to be updated. It can consist of one or more conditions linked with logical operators such as AND, OR, and NOT.

Here’s an example to illustrate how an update query works. Suppose we have a table called “employees” with the following structure and data:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  department VARCHAR(50)
);

INSERT INTO employees (id, name, age, department)
VALUES
  (1, 'John Doe', 30, 'HR'),
  (2, 'Jane Smith', 28, 'IT'),
  (3, 'Michael Johnson', 35, 'Sales'),
  (4, 'Emily Davis', 32, 'HR');

Now, let’s say we want to update the department and age of an employee with an ID of 2. We can use the following update query:

UPDATE employees
SET department = 'Finance', age = 30
WHERE id = 2;

After executing this query, the “employees” table will be updated, and the department of the employee with ID 2 will be changed to ‘Finance’, and their age will be set to 30. The resulting table will look like this:

id	name	age	department
1	John Doe	30	HR
2	Jane Smith	30	Finance
3	Michael Johnson	35	Sales
4	Emily Davis	32	HR

Note that if you omit the WHERE clause in the update query, all rows in the specified table will be updated with the new values. Therefore, it’s important to be cautious while using update queries without conditions to avoid unintended modifications to data.

SQL: Updating multiples rows and columns

To update multiple rows and columns in SQL, you can use the UPDATE statement with a WHERE clause to specify the conditions for the rows you want to update. Here’s an example to illustrate how to update multiple rows and columns:

Suppose we have a table called “employees” with the following structure and data:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  department VARCHAR(50)
);

INSERT INTO employees (id, name, age, department)
VALUES
  (1, 'John Doe', 30, 'HR'),
  (2, 'Jane Smith', 28, 'IT'),
  (3, 'Michael Johnson', 35, 'Sales'),
  (4, 'Emily Davis', 32, 'HR');

Now, let’s say we want to update the department and age of all employees whose age is greater than 30. We can use the following update query:

UPDATE employees
SET department = 'Management', age = 31
WHERE age > 30;

After executing this query, the “employees” table will be updated, and all employees with an age greater than 30 will have their department changed to “Management” and their age set to 31. The resulting table will look like this:

id	name	age	department
1	John Doe	30	HR
2	Jane Smith	28	IT
3	Michael Johnson	31	Management
4	Emily Davis	31	Management

Note that the WHERE clause in the UPDATE statement is crucial to specify the condition for updating the rows. Without it, all rows in the table would be updated.