Basics of SQL: Understanding Functions, Operations, and Data Types

SQL, or Structured Query Language, is essential for managing and manipulating relational databases. This article covers its basic functions, operations, and data types, illustrated with a cake shop sales data example.

Functions of SQL

SQL provides various built-in functions that can be categorized into two main types:

1. Aggregate Functions:
These functions perform calculations on a set of values and return a single value. Common aggregate functions include:

• SUM(): Adds up values.
• AVG(): Calculates the average.
• COUNT(): Counts the number of entries.

Example:
For our cake shop sales data, to find the total sales, you might use:

SELECT SUM(price) FROM sales;

2. Scalar Functions:
These operate on a single value and return a single value. Examples include:

• UPPER(): Converts text to uppercase.
• ROUND(): Rounds a number to a specified number of decimal places.

Example:
To convert customer names to uppercase:

SELECT UPPER(customer_name) FROM sales;

SQL Operations

SQL operations can be broadly classified into four categories:

1. Data Query Language (DQL):
Used for querying data using the SELECT statement.

2. Data Definition Language (DDL):
Used for defining database structures. Common commands include:

• CREATE TABLE: Creates a new table.
• ALTER TABLE: Modifies an existing table.

Example:
Creating a sales table:

CREATE TABLE sales (
    id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    cake_type VARCHAR(50),
    price DECIMAL(10, 2),
    sale_date DATE
);

3. Data Manipulation Language (DML):
Used for manipulating data within tables. Key commands include:

• INSERT INTO: Adds new records.
• UPDATE: Modifies existing records.
• DELETE FROM: Removes records.

Example:
Inserting a new sale record:

INSERT INTO sales (id, customer_name, cake_type, price, sale_date)
VALUES (1, 'Alice', 'Chocolate', 25.00, '2024-10-01');

4. Data Control Language (DCL):
Used for controlling access to data in the database. Commands include:

• GRANT: Gives user access privileges.
• REVOKE: Removes user access privileges.

Data Types in SQL

Understanding SQL data types is crucial for defining how data is stored and processed. Here are the primary categories:

1. Numeric Data Types

These are used to store numbers:

• INT: Integer values.
• DECIMAL(p,s): Fixed-point numbers where p is precision and s is scale.

2. Character Data Types

Used for storing text:

• CHAR(n): Fixed-length strings.
• VARCHAR(n): Variable-length strings.

3. Date and Time Data Types

Store date and time values:

• DATE: Stores dates.
• DATETIME: Stores both date and time.

4. Binary Data Types

Used for storing binary data like images:

• BLOB: Binary Large Objects.

Example Sales Data

Consider the following 15-row example of sales data from our cake shop:

ID	Customer Name	Cake Type	Price	Sale Date
1	Alice	Chocolate	25.00	2024-10-01
2	Bob	Vanilla	20.00	2024-10-02
3	Charlie	Red Velvet	30.00	2024-10-03
4	Diana	Cheesecake	35.00	2024-10-04
5	Eve	Chocolate	25.00	2024-10-05
6	Frank	Vanilla	20.00	2024-10-06
7	Grace	Red Velvet	30.00	2024-10-07
8	Henry	Cheesecake	35.00	2024-10-08
9	Ivy	Chocolate	25.00	2024-10-09
10	Jack	Vanilla	20.00	2024-10-10
11	Kelly	Red Velvet	30.00	2024-10-11
12	Leo	Cheesecake	35.00	2024-10-12
13	Mia	Chocolate	25.00	2024-10-13
14	Noah	Vanilla	20.00	2024-10-14
15	Olivia	Red Velvet	30.00	2024-10-15

Using SQL with Sales Data

To analyze this data, you can run various SQL queries:

1. Total Sales Calculation

   SELECT SUM(price) AS Total_Sales FROM sales;
   

2. Average Price of Cakes

   SELECT AVG(price) AS Average_Price FROM sales;
   

3. Count of Sales by Cake Type

   SELECT cake_type, COUNT(*) AS Number_of_Sales FROM sales GROUP BY cake_type;
   

By understanding these basics of SQL—its functions, operations, and data types—you can effectively manage and analyze data in relational databases like our cake shop's sales records.