Assignments Archive - Skills Academy

30 Jan 2024

MySQL Point of Sale (POS) project

Creating a MySQL Point of Sale (POS) project involves designing a database schema to manage products, customers, sales transactions, and inventory. Below is an assignment outline along with examples:

Assignment:

Design a MySQL database schema for a Point of Sale (POS) system. Your system should be capable of managing products, customers, sales transactions, and inventory. Additionally, implement SQL queries to perform various operations such as adding new products, recording sales, managing inventory, and generating sales reports.

Example Solution:

Here’s an example solution for the given assignment:

1. Database Schema:

sql

CREATE TABLE products (

    product_id INT AUTO_INCREMENT PRIMARY KEY,

    product_name VARCHAR(255) NOT NULL,

    unit_price DECIMAL(10, 2) NOT NULL,

    tax_rate DECIMAL(5, 2) NOT NULL,
    stock_quantity INT NOT NULL

);
CREATE TABLE customers (

    customer_id INT AUTO_INCREMENT PRIMARY KEY,

    customer_name VARCHAR(255) NOT NULL,

    email VARCHAR(255)

);
CREATE TABLE sales (

    sale_id INT AUTO_INCREMENT PRIMARY KEY,

    customer_id INT,

    sale_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    total_amount DECIMAL(10, 2) NOT NULL,

    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

);

CREATE TABLE sale_items ( sale_item_id INT AUTO_INCREMENT PRIMARY KEY, sale_id INT, product_id INT, quantity INT NOT NULL, unit_price DECIMAL(10, 2) NOT NULL, total_price DECIMAL(10, 2) NOT NULL, FOREIGN KEY (sale_id) REFERENCES sales(sale_id), FOREIGN KEY (product_id) REFERENCES products(product_id) );

2. Sample Data Insertion:

sql

-- Insert products

INSERT INTO products (product_name, unit_price, tax_rate, stock_quantity)

VALUES ('Product A', 10.00, 0.00, 100),

       ('Product B', 20.00, 0.00, 150),

       ('Product C', 15.00, 0.00, 200);
-- Insert customers

INSERT INTO customers (customer_name, email)

VALUES ('John Doe', 'john@example.com'),

       ('Jane Smith', 'jane@example.com');
-- Sample sales transaction

INSERT INTO sales (customer_id, total_amount)

VALUES (1, 50.00);

-- Insert sale items INSERT INTO sale_items (sale_id, product_id, quantity, unit_price, total_price) VALUES (1, 1, 2, 10.00, quantity * unit_price), (1, 2, 1, 20.00, quantity * unit_price);

3. Sample Queries:

Retrieve all products:

sql

SELECT * FROM products;

Record a new sale transaction:

sql

INSERT INTO sales (customer_id, total_amount)

VALUES (1, 35.00);

Update product stock quantity after a sale:

sql

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

Generate a sales report for a specific period:

sql

SELECT s.sale_id, c.customer_name, s.sale_date, s.total_amount

FROM sales s

JOIN customers c ON s.customer_id = c.customer_id

WHERE s.sale_date BETWEEN '2024-01-01' AND '2024-01-31';

Generate Invoice with Tax and Total Amount:

To generate a product invoice receipt with total amount plus taxes in MySQL, you would typically retrieve the necessary information from the database and perform calculations to generate the receipt. Here’s a basic example of how you can do this using SQL:

Assuming you have tables for products, sales, and sale_items, here’s how you might generate a receipt:

To generate an invoice with tax and total amount, you can use the following query:

sql

SELECT s.sale_id, s.sale_date, p.product_name, si.quantity, si.unit_price, si.total_price,

       ROUND(si.quantity * p.tax_rate, 2) AS tax_amount,

       ROUND(si.total_price + (si.quantity * p.tax_rate), 2) AS total_with_tax

FROM sales s

JOIN sale_items si ON s.sale_id = si.sale_id

JOIN products p ON si.product_id = p.product_id;

This query retrieves the sale details along with product information and calculates the tax amount and total amount with tax included for each item in the sale. Adjust the tax calculation logic based on your specific tax rules and regulations.

In this query:

We join the products, sale_items, and sales tables to retrieve the necessary information for the invoice.
We calculate the tax amount by multiplying the total amount by the tax rate (assuming 10% tax rate here).
We calculate the total amount including taxes by adding the tax amount to the total amount.

This query will provide you with a result set containing the details of the products sold in the specific sale, along with the sale date, total amount, tax amount, and total amount with taxes included.

This example provides a basic structure for a MySQL Point of Sale (POS) system, including tables for products, customers, sales, and sale items. Adjustments and enhancements can be made based on specific requirements and business logic.

23 Nov 2023

Creating a To-Do List

Exercise: Creating a To-Do List

Objective: Create a basic to-do list where users can add tasks, mark them as completed, and remove tasks.

Steps:

HTML Structure: Set up the HTML structure for the to-do list. Create an input field to add tasks, a button to submit tasks, and an area to display the tasks.

html

<!DOCTYPE html> <html> <head> <title>To-Do List Exercise</title> </head> <body> <h1>My To-Do List</h1> <input type="text" id="taskInput" placeholder="Enter task"> <button onclick="addTask()">Add Task</button> <ul id="taskList"></ul>
<script src="todo.js"></script> </body> </html>
JavaScript Logic: Create a JavaScript file (todo.js) and implement the logic for adding tasks, marking them as completed, and removing tasks.

javascript
// todo.js function addTask() { var taskInput = document.getElementById("taskInput"); var taskList = document.getElementById("taskList"); if (taskInput.value !== "") { // Create a new list item var newTask = document.createElement("li"); newTask.innerHTML = taskInput.value; // Mark task as completed on click newTask.onclick = function() { this.classList.toggle("completed"); }; // Remove task on double click newTask.ondblclick = function() { this.remove(); }; // Append the new task to the list taskList.appendChild(newTask);
// Clear the input field taskInput.value = ""; } else { alert("Please enter a task!"); } }
Styling (Optional): Add CSS styles to enhance the appearance of the to-do list.

css

/* Style the completed tasks */ .completed { text-decoration: line-through; color: #888; }
Testing: Open the HTML file in a web browser. Try adding tasks by typing in the input field and clicking the “Add Task” button. Click on tasks to mark them as completed or double-click to remove them.

Skills Academy