Introduction to Databases and Tables

Understanding Databases and Tables

A database is like a digital filing cabinet where you can store, organize, and manage large amounts of information. Instead of keeping paper records in folders, you use a database to keep data safe, easy to find, and ready for analysis. Databases are used everywhere — from online shopping sites to banking apps — to keep track of customers, products, transactions, and more.

Inside a database, information is organized into tables. Think of a table as a spreadsheet: each table has rows and columns. Each row represents a single record (like one customer or one product), and each column represents a specific piece of information about that record (such as a customer's name or a product's price).

Why are databases and tables important for data analysis?

• They allow you to store data in a structured, consistent way;
• They make it easy to find, filter, and sort information quickly;
• They support combining data from different tables to answer complex questions;
• They help keep data accurate and up to date.

Example:

Imagine you run an online store. You might have one table for customers, storing details like names and cities, and another table for products, listing items for sale and their prices. With these tables, you can easily find out things like which customers signed up this month or which products are the most expensive. This structure is the foundation for all kinds of data analysis and business decisions.

Selecting All Customers

You can use the SELECT statement in SQL to view all the information stored in a table. Think of it like looking at a spreadsheet where you want to see every row and column. This is useful when you want to get a complete list of all customers and their details, such as their names, cities, and signup dates. The result will show every customer in the customers table, with each row representing one customer.

SELECT * FROM customers;

Selecting Specific Columns

Sometimes you only need certain details, not everything. If you only want to see the names and cities of the customers, you can list those columns in your SELECT statement. This is like telling a librarian you only want the titles and authors from a list of books, not every detail about each book. The result will be a table with just the name and city columns for every customer.

SELECT name, city FROM customers;

Filtering Data with WHERE

If you want to find customers from a specific city, you can use the WHERE clause. This works like a filter on a spreadsheet, showing only the rows that match your condition. For example, to see all customers from 'New York', you use WHERE city = 'New York'. The result will list only those customers whose city is New York.

SELECT * FROM customers WHERE city = 'New York';

Sorting Results with ORDER BY

You can organize your results using ORDER BY. This is like sorting a list of names alphabetically or by date. If you want to see all customers sorted by their signup date, you use ORDER BY signup_date. The result will show customers from the earliest to the latest signup.

SELECT * FROM customers ORDER BY signup_date;

Limiting Results with LIMIT

If you only want to see a certain number of results, you can use LIMIT. This is like asking for just the first five names from a long list. For example, to see only the first three customers in the table, you use LIMIT 3. The result will show just three rows from the top of your query.

SELECT * FROM customers LIMIT 3;

Chapter Summary

• You learned how to work with the customers and products tables, including their structure and sample data;
• You practiced writing SQL queries to retrieve, filter, and manipulate data from these tables;
• You explored key SQL concepts such as selecting specific columns, filtering results with WHERE, and understanding data types like VARCHAR, DATE, and DECIMAL;
• You saw how real-world data, such as customer information and product details, can be organized and queried using SQL;
• You gained hands-on experience with practical SQL examples that reflect common business scenarios.

Review these points to reinforce your understanding before moving on to more advanced SQL topics.

Selecting All Customers from the Database

You often need to view all records in a table to understand the available data or to perform further analysis. The following SQL statement retrieves every row and column from the customers table. This is useful for getting a complete list of all customers currently stored in the database. The expected result is a table containing all customer details, including their id, name, city, and signup_date.

SELECT * FROM customers;

Retrieving Product Names and Prices

Sometimes you only need specific columns from a table. This query selects the name and price columns from the products table. This is helpful when you want to display or analyze just the product names and their prices, without extra details like category or id. The expected result is a list of product names alongside their prices.

SELECT name, price FROM products;

Selecting All Customers

You can retrieve all records from the customers table using a simple SELECT statement:

SELECT * FROM customers;

This query returns every column and every row from the customers table. You will see a list of all customers, including their id, name, city, and signup_date. This is useful for getting a complete view of the data in the table.

Selecting Specific Columns

If you only want to see certain columns, specify them in your SELECT statement:

SELECT name, city FROM customers;

This query displays only the name and city columns for each customer. The result will be a list of customer names alongside their cities, making it easier to focus on just the information you need.

Filtering Results with WHERE

You can filter the results to show only customers from a specific city using the WHERE clause:

SELECT name, city FROM customers WHERE city = 'Chicago';

This query returns only the customers whose city is 'Chicago'. You will see a list of customer names and their city, but only for those who live in Chicago. This helps you target specific subsets of your data.

Imagine you are managing a large library. Each book has a title, an author, a genre, and a date it was added to the collection. To keep track of everything, you might use a spreadsheet where each row represents a book and each column represents a detail about the book. This is similar to how data is organized in a relational database. In databases, tables store information in a structured way, much like a spreadsheet or a library catalog. Each table is made up of rows (also called records) and columns (also called fields). This structure allows you to store, organize, and retrieve information quickly and efficiently. For example, a business might use a table to keep track of its customers, with each row representing a different customer and each column storing information such as the customer's name, city, and signup date.

This SQL statement creates a table called customers. The CREATE TABLE command tells the database that you want to make a new table. Inside the parentheses, you define each column:

• The id column is of type SERIAL, which means it will automatically generate a unique number for each customer; it is also set as the PRIMARY KEY, ensuring each row is uniquely identified.
• The name column uses the VARCHAR(100) data type, which stores text up to 100 characters, and NOT NULL means this field cannot be left empty.
• The city column is also a VARCHAR(100) and cannot be empty.
• The signup_date column uses the DATE data type to store the date each customer signed up, and must also be filled in for every row.

Each column definition specifies the type of data it can hold and any rules about whether it can be empty or must be unique. This structure helps keep your data organized and ensures you always have the information you need for each customer.