Create a database to track information for retrieval later.

Question

Create a database to track information for retrieval later.
Directions
Let's build a simple database. You will create a database with information about products in a catalog. Plan the database first. As you plan, keep in mind that later you will want to sort this data by product number and retrieve a list of products with a quantity of less than one hundred. You will also need to look up the price and product description.

Create the database and populate the database with ten records about any type of products. Save your database.

GPT-4o mini · Answer

To create a simple database to track product information, we can design a relational database. Below is the plan for the database structure, the SQL commands to create it, and how to populate it with records.

Database Schema

Products Table

ProductID (Primary Key, Integer)
ProductName (String, Length 100)
Quantity (Integer)
Price (Decimal)
Description (String, Length 255)

SQL Commands to Create the Database and Table

Assuming you are using a SQL database such as MySQL, SQLite, or PostgreSQL, the commands below will help you create the database and the products table:

-- Create the database
CREATE DATABASE ProductCatalog;

-- Use the created database
USE ProductCatalog;

-- Create the Products table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Quantity INT,
    Price DECIMAL(10, 2),
    Description VARCHAR(255)
);

Inserting Records into the Database

Next, we will populate the database with ten product records. Here’s the SQL command to insert the records:

INSERT INTO Products (ProductID, ProductName, Quantity, Price, Description) VALUES
(1, 'Wireless Mouse', 150, 25.99, 'A high precision wireless mouse with ergonomic design.'),
(2, 'USB-C Charger', 75, 19.99, 'Fast charging USB-C charger for all compatible devices.'),
(3, 'Bluetooth Headphones', 50, 59.99, 'Noise-cancelling over-ear Bluetooth headphones.'),
(4, 'Portable SSD', 30, 89.99, 'Compact and lightweight portable SSD for data storage.'),
(5, 'Mechanical Keyboard', 120, 99.99, 'RGB mechanical keyboard with customizable keys.'),
(6, 'Webcam', 40, 49.99, '1080p HD webcam for video conferencing.'),
(7, 'Gaming Monitor', 20, 299.99, '27-inch 144Hz gaming monitor with vibrant colors.'),
(8, 'Laptop Stand', 200, 29.99, 'Adjustable laptop stand for improved ergonomics.'),
(9, 'Wireless Charger', 95, 15.99, 'Fast wireless charger compatible with Qi-enabled devices.'),
(10, 'HDMI Cable', 60, 9.99, 'High-speed HDMI cable for audio and video connection.');

Querying the Data

Now, you can perform queries to revisit the data you've inserted. Below are examples of how to sort and filter the data:

Retrieve all product records sorted by ProductID:

SELECT * FROM Products ORDER BY ProductID;

Retrieve products with a quantity of less than 100:

SELECT * FROM Products WHERE Quantity < 100;

Look up a product's price and description:

SELECT ProductName, Price, Description FROM Products WHERE ProductID = 5; -- Change 5 to any ProductID you wish to look up

Saving the Database

Once you've entered all the records, your database will be saved in the database management system you are using (MySQL, SQLite, etc.). You can export it or back it up depending on the system’s functionalities available.

Conclusion

You now have a basic database for keeping track of product information with the ability to query product details, sort them, and filter based on quantity! You can extend this database with additional tables or features as needed.