SQL TUTORIALS

Inserting Data

Importing Data Using the INSERT INTO Statement

Importing Data from a CSV File into an Existing Table

Summary:

• The INSERT INTO statement is used to insert one or more rows into a table. This is commonly used when filling in a test table to make sure your constraints and data types are working correctly. This is used with the following syntax:
  INSERT INTO table_name VALUES (column1_value, column2_value, etc.);
• If you want to insert multiple rows in the same statement, use the following syntax:
  INSERT INTO table_name VALUES (column1_value, column2_value, etc.), (column1_value, column2_value, etc.);
• Using PgAdmin, you can import CSV data into a table using the GUI (see detailed instructions in the video above).
• If you want to customize your CSV import using SQL, use the following syntax (PostgreSQL specific):
  COPY table_name(column1, column2, column3, column4, column5)
FROM 'file_path'
DELIMITER ","
CSV HEADER;
• NOTE: To avoid any errors, you need to ensure that the data types and column orders align when importing data from a CSV.

Updating Data

Summary:

• The UPDATE statement is used to change the values in a column. This is used with the following syntax:
  UPDATE table_name
SET column = new_value;
• It's a best practice to ALWAYS use the WHERE clause when updating column values. This ensures you don't accidentally update every value in the column. When updating a table based on a condition, use the following syntax:
  UPDATE table_name
SET column = new_value
WHERE [condition];
• If you want to update multiple columns in the same statement, use the following syntax:
  UPDATE table_name
SET column1 = new_value, column2 = new_value
WHERE [condition];

Deleting Data

Summary:

• The DELETE statement is used to remove data from a table based on a condition. This is used with the following syntax:
  DELETE FROM table_name
WHERE [condition];
• To delete specific rows in a table, use the following syntax:
  DELETE FROM [table_name] WHERE [condition];
• NOTE: It's a best practice to ALWAYS run a SELECT statement with the same conditions mentioned in the WHERE clause of your proposed DELETE statement to ensure you're only removing data that is no longer needed.

Exercise

Congratulations! You just completed the DML Tutorial! To help test your knowledge, let's complete the exercise outlined below. This exercise is based on the exercise of the previous tutorial.
**It's highly recommended that you complete the exercise outlined in the previous tutorial before beginning this exercise.**

Instructions:

1. Open your RDBMS Client.
2. Open the "Sales" Database created in the previous tutorial.
3. Create a New Table named "Orders" with the following schema:
   • id (INT) [PRIMARY KEY]
   • order_date (DATE) [NOT NULL]
   • num_items (INT) [NOT NULL]
   • subtotal_amount (FLOAT) [NOT NULL]
   • tax_amount (FLOAT) [NOT NULL]
   • customer_id (INT) [FOREIGN KEY REFERENCES customers(id)
   • employee_id (INT) [FOREIGN KEY REFERENCES employees(id)
4. Download the following files:
   • Customers Table Mock Data CSV File
   • Employees Table Mock Data CSV File
   • Orders Table Mock Data CSV File
5. Insert the data from the "customers.csv" file into the "Customer" table.
6. Insert the data from the "employees.csv" file into the "Employees" table.
7. Insert the data from the "orders.csv" file into the "Orders" table.
8. Insert the following rows into the "Categories" table:
   • 1, "Furniture", "This includes all items that furnish a home or backyard. Examples inlude couches, coffe tables, and ottomans."
   • 2, "Small Appliances", "This includes all items that are used to assist customers in their daily activities around the home. Examples include small fridges, soda can dispensers, and coffee makers."
   • 3, "Bedding", "This includes all items that are related to sleeping. Examples include mattresses, fitted sheets, loose sheets, and pillow cases."
9. Update the "wage" column in the "Employees" table by multiplying the current value by 1.5 for all employees hired before 3/1/2020.
10. Update the "tax_amount" column in the "Orders" table by dividing the current value by 2 for all orders that had more than 10 items in it.
11. Delete all rows from the "Customers" table where the "city" column is not null and the "state" column is null.
12. Exercise Completed! Click here to view the answers.

Have any issues with the above exercise? Post your question on Discord!