Databases and Tables

Before writing SQL commands, you need to understand what SQL works with: databases and tables.

Notes: At CodeFriends, we use SQLite as the database engine. While SQL syntax can vary slightly between different database systems, the underlying concepts remain the same.

What is a Database?

A database functions as a structured digital repository that stores and organizes data, allowing for efficient retrieval, modification, and management.

A single database can contain multiple tables, each used to store a specific kind of information, like users, products, orders, or grades.

What is a Table?

A table is a structured arrangement of data, organized into rows and columns, similar in format to a spreadsheet.

• Each row is a single record (e.g., one student).
• Each column is a field (e.g., name, exam score, passed status).

Example Table: students

id	name	exam_score	passed
1	John Miller	92	Yes
2	Emily Davis	88	Yes
3	Michael Johnson	75	Yes
4	Sophia Wilson	58	No
5	Ethan Brown	81	Yes

This is how most data is stored in SQL — clearly structured in rows and columns.

Creating a Table with SQL

The following is an example of how you can create a simple students_info table using SQL:

Create Table Example

CREATE TABLE students_info (
  id INTEGER,
  name TEXT,
  exam_score INTEGER,
  passed TEXT
);

This command:

• Creates a table called students_info
• Defines 4 columns: id, name, exam_score, and passed
• Sets the data types as INTEGER or TEXT

Inserting Data into a Table

The following is an example of how you can insert data into the students_info table using SQL:

Insert data into the students_info table

INSERT INTO students_info (id, name, grade, passed) VALUES
(1, 'John Miller', 92, 'yes'),
(2, 'Emily Davis', 88, 'yes'),
(3, 'Michael Johnson', 59, 'no');

This command:

• Inserts 3 new records into the students_info table
• Defines the values for each column in the new records

What's Next?

Now that you know what a table looks like, the next step is learning how to retrieve data using the most important SQL command: SELECT.

Run SELECT * FROM students_info; to try out the SELECT statement, and we will cover it in more detail in the next lesson.