SQL Fundamentals: Creating and Modifying Data

SQL: The Universal Language

The Bridge to Data

To bring our ER designs to life, we use SQL (Structured Query Language). It is the industry-standard language used across all major platforms like MySQL, PostgreSQL, and Oracle.

We categorize SQL into two main types:

Welcome back! Now that we have our design, it's time to communicate with the database using SQL. SQL is an ANSI standard, which means the skills you learn here apply whether you're using Oracle or MySQL. We'll use DDL to build our tables and DML to handle the actual data inside them. Think of DDL as the blueprints for a house. It defines the rooms and walls, but doesn't include the furniture yet. DML is like moving the furniture in. It’s how we add, change, or remove the actual items stored in those rooms.

SQL: The Universal Language

The Language of Data

SQL (Structured Query Language) is the standard ANSI language for communicating with relational databases. Think of it like a universal translator—whether you are using PostgreSQL, MySQL, or SQL Server, the core commands remain the same.

Welcome to the world of SQL. Just as people use different dialects to speak the same language, database systems like MySQL and PostgreSQL use SQL as their foundation. Because it follows ANSI standards, the skills you learn here are portable across almost any platform you'll encounter in your career.

Building the Skeleton: CREATE TABLE

Defining the Products Table

Using CREATE TABLE, we define the columns and data types for our e-commerce store. We also designate a Primary Key to ensure every product is unique.

Let's build our Products table. First, we name the table. Then, we define the product_id as our Primary Key—this ensures no two products ever share the same ID. We also specify data types: VARCHAR for names, DECIMAL for prices, and INT for our stock count.

DDL vs. DML: Building and Filling

Defining vs. Manipulating

SQL commands are categorized into two main groups: DDL for structure and DML for content.

Think of your database as a digital filing system. Data Definition Language, or DDL, is like the carpenter building the cabinet and labeling the drawers. Data Manipulation Language, or DML, is the clerk who puts folders inside, updates their contents, or removes them when they are no longer needed.

Defining the Blueprint: CREATE TABLE

The TechTrend Schema

To start our TechTrend store, we need a Products table. We must define the column names and their Data Types.

Let's bring our TechTrend store to life. Before we can sell anything, we need to create the 'Products' table. We start with a Product ID, marked as the Primary Key to ensure every item is unique. Then, we define our name as text, price as a decimal, and stock as a whole number.

Adding Data with INSERT

Stocking the Shelves

Now that the table exists, we use INSERT INTO to add products. Remember: text values like 'Mechanical Keyboard' must be wrapped in single quotes.

It's time to stock our store. Drag the product details into the correct slots in the SQL statement to add our first item. Great job! Notice how the name is wrapped in single quotes. Once you execute this, the Mechanical Keyboard is officially in our database.

Data Types Matching

Drag the correct Data Type to the column it best represents for our TechTrend store.

It's your turn to design the table columns. Drag the data types from the bottom tray to the matching product attribute. Not quite. Think about whether that attribute is a whole number, a decimal, or text. Perfect! That data type ensures the database handles that specific information correctly.

The Power (and Risk) of UPDATE

Modifying Existing Data

The UPDATE command changes existing records. However, it is dangerous without a WHERE clause.

Prices change often. To update them, we use the UPDATE command. By using a WHERE clause, we can target just the Keyboard. But watch what happens if we forget it... Every single product in the database becomes the same price! Always double-check your WHERE clause.

Removing Data with DELETE

Deleting Records

When a product is discontinued, we use DELETE FROM. Like UPDATE, this requires a WHERE clause to avoid wiping the entire table.

A product has been discontinued. Write a SQL statement to delete the product with an ID of 1. Be careful!

Adding Your First Product

Write an INSERT statement to add a 'Pro Gaming Mouse' to the Products table. Use ID 1, price 49.99, and stock 50.

We have our table structure. Now, let's add our first inventory item. Use the standard INSERT syntax to add the Pro Gaming Mouse.

Database Safety Check

Avoiding Costly Mistakes

Explain in your own words why the WHERE clause is considered the most critical part of an UPDATE or DELETE statement for a beginner.

Before we move on, let's make sure you understand the risks. Explain why the WHERE clause is so important in SQL.

The Power of the WHERE Clause

Modifying and Deleting

The UPDATE and DELETE commands allow you to change existing data. However, they are dangerous without the WHERE clause.

Imagine our store now has dozens of products. When we want to put the mouse on sale, we use the UPDATE command. Pay close attention to the WHERE clause. Without it, you aren't just changing the mouse—you're changing every single price in the store!