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.

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.

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).

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

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

Copy
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

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

Copy
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

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.