Introd to SQL

SQL Basics: Create Tables and Insert Data

If you're just getting started with databases, this is the right place to begin. In this post, we'll walk through the very basics of SQL — what it is, how you write your first table, and how to insert and query data from it. This is Assignment 1 from a hands-on SQL lab series, and everything here is beginner-friendly.

What is SQL?

SQL stands for Structured Query Language. Unlike most programming languages where you tell the computer how to do something step by step, SQL is different — you just tell it what you want, and it figures out how to get it. That's what makes it a non-procedural language.

It reads almost like plain English, which is why it's one of the easiest database languages to pick up.

SQL has four major components you'll use throughout your database journey:

• DDL (Data Definition Language) — for creating and modifying database structure. Think CREATE, ALTER, DROP.

• DML (Data Manipulation Language) — for working with the actual data: INSERT, UPDATE, DELETE, SELECT.

• Integrity — lets you set rules so your data always stays correct and consistent.

• Authorization — controls who can see or modify what in your database.

Data Definition Language (DDL)

DDL is what you use when you want to define your database — the structure, not the content. When you write a CREATE TABLE statement, you're using DDL.

With DDL, you can define:

• The schema (structure) of your tables

• Datatypes for each column

• Integrity constraints (like NOT NULL or PRIMARY KEY)

• Indexing and physical storage

• Authorization rules

Common SQL Datatypes

Before creating a table, you need to know what kind of data each column will hold. Here are the basic types:

These datatypes follow Oracle SQL conventions which is why you'll see VARCHAR2 and NUMBER instead of the standard VARCHAR and INT used in MySQL or PostgreSQL.

Creating a Table

The syntax is straightforward. Here's the basic structure:

CREATE TABLE table_name (
    column_name datatype(size),
    column_name datatype(size)
);

You can also create a table directly from another existing table:

CREATE TABLE new_table
AS SELECT column1, column2 FROM existing_table;

Inserting Data

Once your table exists, you populate it using INSERT INTO:

INSERT INTO table_name (col1, col2, ...)
VALUES (value1, value2, ...);

You can also copy data from another table:

INSERT INTO table_name
SELECT col1, col2 FROM another_table;

Or insert only rows that match a condition:

INSERT INTO table_name
SELECT col1, col2 FROM another_table
WHERE condition;

Retrieving Data with SELECT

The SELECT statement is how you read data back from a table. It's one of the most used SQL commands you'll ever write.

SELECT * FROM table_name;               -- All columns
SELECT col1, col2 FROM table_name;      -- Specific columns
SELECT DISTINCT col1 FROM table_name;   -- Remove duplicate values

Add a WHERE clause to filter results:

SELECT col1, col2 FROM table_name
WHERE condition;

Lab Assignment: client_master Table

Now let's put it all together. The goal of this assignment is to create a table called client_master, insert some records, and run a few queries against it.

Step 1: Create the Table

Here's the structure we need:

CREATE TABLE client_master (
    client_no VARCHAR2(6),
    name VARCHAR2(20),
    city VARCHAR2(15),
    state VARCHAR2(15),
    pincode NUMBER(6),
    bal_due NUMBER(10,2)
);

Step 2: Insert the Data

Here are the records we're inserting — notice that some values are NULL, which is totally valid in SQL when data is missing or unknown.

INSERT INTO client_master (client_no, name, city, pincode, state, bal_due) 
VALUES ('0001', 'Ivan', 'Bombay', 400054, 'Maharashtra', 15000);

INSERT INTO client_master (client_no, name, city, pincode, state, bal_due) 
VALUES ('0002', 'Vandana', 'Madras', 780001, 'Tamilnadu', 0);

INSERT INTO client_master (client_no, name, city, pincode, state, bal_due) 
VALUES ('0003', 'Pramada', 'Bombay', 400057, 'Maharashtra', 5000);

INSERT INTO client_master (client_no, name, city, pincode, state, bal_due) 
VALUES ('0004', 'Basu', 'Bombay', NULL, 'Maharashtra', 0);

INSERT INTO client_master (client_no, name, city, pincode, state, bal_due) 
VALUES ('0005', 'Ravi', 'Delhi', 100001, NULL, 2000);

INSERT INTO client_master (client_no, name, city, pincode, state, bal_due) 
VALUES ('0006', 'Rukmini', 'Bombay', 400050, 'Maharashtra', 0);

Step 3: Run These Queries

Now the fun part — querying the data. Try running each of these and verify the output yourself.

-- 1. Find names of all clients
SELECT name FROM client_master;

-- 2. List names and cities of all clients
SELECT name, city FROM client_master;

-- 3. Show all clients located in Bombay
SELECT * FROM client_master WHERE city = 'Bombay';

-- 4. Get info for client_no 0001 and 0002
SELECT * FROM client_master WHERE client_no IN ('0001', '0002');

-- 5. Clients with a balance due greater than 5000
SELECT * FROM client_master WHERE bal_due > 5000;

-- 6. Clients from Bombay, Delhi, or Madras
SELECT * FROM client_master WHERE city IN ('Bombay', 'Delhi', 'Madras');

-- 7. Clients NOT from Maharashtra
SELECT * FROM client_master WHERE state != 'Maharashtra' OR state IS NULL;

Note on Query 7: The OR state IS NULL part is important. In SQL, NULL doesn't behave like a regular value — a condition like state != 'Maharashtra' will not return rows where state is NULL. You have to explicitly include it.

Expected Outputs

Query 3 — Clients in Bombay:

CLIENT_NO  NAME     CITY    STATE        PINCODE  BAL_DUE
0001       Ivan     Bombay  Maharashtra  400054   15000
0003       Pramada  Bombay  Maharashtra  400057   5000
0004       Basu     Bombay  Maharashtra  NULL     0
0006       Rukmini  Bombay  Maharashtra  400050   0

Query 4 — Clients 0001 and 0002:

CLIENT_NO  NAME     CITY    STATE        PINCODE  BAL_DUE
0001       Ivan     Bombay  Maharashtra  400054   15000
0002       Vandana  Madras  Tamilnadu    780001   0

Query 7 — Not from Maharashtra:

CLIENT_NO  NAME     CITY    STATE      PINCODE  BAL_DUE
0002       Vandana  Madras  Tamilnadu  780001   0
0005       Ravi     Delhi   NULL       100001   2000

Want to practice more queries interactively? W3Schools SQL editor lets you run SQL right in your browser — no setup needed.

Also Explore These Topics

If you found this useful, here are some related notes worth checking out on Notehub:

• SQL Quick Recap — a fast revision of all core SQL concepts

• SQL Assignment 2 — next in the series, covering more DML and constraints

• SQL Assignment 3 — more complex queries and operations

• Triggers in SQL — learn how to automate actions in your database

• DBMS Overview — understand databases from the ground up before diving deeper into SQL

• Views in SQL — how to create virtual tables using views

• PL/SQL Assignment — take SQL further with procedural logic