A.1.8 Describe the Two Functions Databases Require to Be Performed on Them

Databases primarily require two key functions to operate effectively: query functions and update functions. These functions enable data retrieval and modification, forming the backbone of database management.

---

1. Query Functions

Definition:

Query functions are operations used to retrieve data from a database based on specific criteria. They allow users to search, filter, and organize data to extract meaningful information.

Key Features:

• Data Retrieval:
  • Extract specific data from the database using queries.
  • Example: Retrieve all customer names from a database.
• Sorting and Filtering:
  • Arrange data in a specific order or filter it based on conditions.
  • Example: Get all sales records where the amount exceeds $1000.
• Analysis and Reporting:
  • Summarize or aggregate data for reporting purposes.
  • Example: Calculate the average salary of employees in a department.

Examples of Query Functions:

• SQL Queries:
  • SELECT * FROM customers WHERE city = 'New York';
  • Retrieves all customers located in New York.

---

2. Update Functions

Definition:

Update functions are operations used to modify the data in a database. This includes adding new records, changing existing data, or deleting obsolete data.

Key Features:

• Data Modification:
  • Modify existing records to keep data up to date.
  • Example: Update a customer’s phone number in the database.
• Data Insertion:
  • Add new records to the database.
  • Example: Insert a new employee’s details into the system.
• Data Deletion:
  • Remove obsolete or incorrect data to maintain accuracy.
  • Example: Delete a product that is no longer available.

Examples of Update Functions:

• SQL Statements:
  • Insert: INSERT INTO customers (name, city) VALUES ('John Doe', 'Los Angeles');
  • Update: UPDATE customers SET phone = '123-456-7890' WHERE name = 'John Doe';
  • Delete: DELETE FROM customers WHERE name = 'John Doe';

---

Importance of Query and Update Functions

1. Data Retrieval for Decision-Making:

   • Query functions enable businesses to access critical insights, such as sales trends or customer demographics, to make informed decisions.

2. Maintaining Data Accuracy:

   • Update functions ensure that the database reflects the most current and accurate information.

3. Facilitating Collaboration:

   • Both functions support data sharing and collaboration among multiple users or systems.

4. Optimizing Database Performance:

   • Well-designed queries and updates ensure efficient database performance, especially for large datasets.

---

Example: E-commerce Database

• Query Function:
  • Retrieve all orders placed in the last 30 days:
    • SELECT * FROM orders WHERE order_date >= '2023-11-01';
• Update Function:
  • Mark an order as shipped:
    • UPDATE orders SET status = 'Shipped' WHERE order_id = 1234;

Database Example: Online Store

Tables

We will use three tables as examples: customers, orders, and products.

1. customers Table

customer_id	name	email	city	phone
1	John Doe	john.doe@example.com	New York	123-456-7890
2	Jane Smith	jane.smith@example.com	Los Angeles	987-654-3210
3	Alice Brown	alice.brown@example.com	Chicago	555-555-5555

2. orders Table

order_id	customer_id	order_date	status	total_amount
101	1	2023-12-01	Pending	150.00
102	2	2023-12-02	Shipped	250.00
103	3	2023-12-03	Pending	75.00

3. products Table

product_id	name	category	price	stock_quantity
201	Laptop	Electronics	1000.00	20
202	Headphones	Electronics	50.00	100
203	Coffee Maker	Home Appliances	75.00	50

---

1. Query Functions

Query functions retrieve specific data from the database using SQL SELECT statements or equivalent commands in NoSQL.

Examples of Query Functions

1. Retrieve all customers in New York:

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


    

   customer_id	name	email	city	phone
   1	John Doe	john.doe@example.com	New York	123-456-7890

2. Get all orders with a total amount greater than $100:

   SELECT * FROM orders WHERE total_amount > 100;


    

   order_id	customer_id	order_date	status	total_amount
   101	1	2023-12-01	Pending	150.00
   102	2	2023-12-02	Shipped	250.00

3. Find all products in the “Electronics” category:

   SELECT * FROM products WHERE category = 'Electronics';


    

   product_id	name	category	price	stock_quantity
   201	Laptop	Electronics	1000.00	20
   202	Headphones	Electronics	50.00	100

---

2. Update Functions

Update functions modify, add, or delete data using SQL commands like INSERT, UPDATE, and DELETE.

Examples of Update Functions

1. Insert a new customer:

   INSERT INTO customers (customer_id, name, email, city, phone)
VALUES (4, 'Mark Johnson', 'mark.johnson@example.com', 'Miami', '444-444-4444');

    

   Updated customers Table:

    

   customer_id	name	email	city	phone
   1	John Doe	john.doe@example.com	New York	123-456-7890
   2	Jane Smith	jane.smith@example.com	Los Angeles	987-654-3210
   3	Alice Brown	alice.brown@example.com	Chicago	555-555-5555
   4	Mark Johnson	mark.johnson@example.com	Miami	444-444-4444

2. Update the stock quantity of a product:

   UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 201;

    

   Updated products Table:

    

   product_id	name	category	price	stock_quantity
   201	Laptop	Electronics	1000.00	19

3. Delete a customer:

   DELETE FROM customers WHERE customer_id = 3;

    

   Updated customers Table:

    

   customer_id	name	email	city	phone
   1	John Doe	john.doe@example.com	New York	123-456-7890
   2	Jane Smith	jane.smith@example.com	Los Angeles	987-654-3210
   4	Mark Johnson	mark.johnson@example.com	Miami	444-444-4444

---

Importance of Query and Update Functions

1. Query Functions:

   • Allow efficient data retrieval for decision-making and reporting.
   • Example: Generating a sales report for the last month.

2. Update Functions:

   • Ensure data remains current and accurate by allowing modifications.
   • Example: Adding new products or updating customer details.

These two functions form the core of database operations, enabling efficient data management and utilization. Let me know if you need further details or additional examples!