Working with two tables - MySQL

 Let’s create two tables connected by a foreign key and then explore some sample queries.


First, we’ll create two tables: Customers and Products. The Customers table will have an id (as the primary key), name, and age. The Products table will have a customer_id (as a foreign key referencing the id column in the Customers table), and a name.

CREATE TABLE Customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL
);

CREATE TABLE Products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    name VARCHAR(255) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES Customers(id)
);


In this example, the customer_id column in the Products table references the id column in the Customers table. This establishes a relationship between the two tables.

INSERT INTO Customers (name, age) VALUES
    ('John Doe', 30),
    ('Jane Smith', 25),
    ('Mike Johnson', 35),
    ('Alice Brown', 28);

INSERT INTO Products (customer_id, name) VALUES
    (1, 'Product A'),
    (1, 'Product B'),
    (2, 'Product C'),
    (3, 'Product D'),
    (4, 'Product E');

1.Retrieve all customers:
SELECT * FROM Customers;

2.Retrieve all products:
SELECT * FROM Products;

3.Retrieve all customers and their products:
SELECT c.name AS customer_name, p.name AS product_name
FROM Customers c
JOIN Products p ON c.id = p.customer_id;

4.Count the number of products for each customer:
SELECT c.name AS customer_name, COUNT(p.id) AS num_products
FROM Customers c
LEFT JOIN Products p ON c.id = p.customer_id
GROUP BY c.id, c.name;

5.Find customers who have not purchased any products:
SELECT c.name AS customer_name
FROM Customers c
LEFT JOIN Products p ON c.id = p.customer_id
WHERE p.id IS NULL;

6.Find all products for a specific customer (e.g., customer_id = 1):
SELECT p.name AS product_name
FROM Products p
WHERE p.customer_id = 1;

7.Get customer details along with the count of products they have purchased:
SELECT c.id, c.name, c.age, COUNT(p.id) AS product_count
FROM Customers c
LEFT JOIN Products p ON c.id = p.customer_id
GROUP BY c.id, c.name, c.age;

8.List all customers who are older than 30 and their products:
SELECT c.name AS customer_name, p.name AS product_name
FROM Customers c
JOIN Products p ON c.id = p.customer_id
WHERE c.age > 30;

9.List all customers who are older than 30 and their products:
SELECT c.name AS customer_name, p.name AS product_name
FROM Customers c
JOIN Products p ON c.id = p.customer_id
WHERE c.age > 30;

10.Find the average age of customers who purchased products
SELECT AVG(age) AS avg_customer_age
FROM Customers
WHERE id IN (SELECT DISTINCT customer_id FROM Products);

11.Update a customer's name
UPDATE Customers
SET name = 'Jonathan Doe'
WHERE id = 1;

12.Delete a product by its id:
DELETE FROM Products
WHERE id = 2;


Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

Procedures in PL/SQL

Procedures in PL/SQL (Procedural Language/Structured Query Language) are a way to encapsulate a set of SQL and PL/SQL statements that perform a specific task. They are stored in the database and can be executed as needed, providing a modular and reusable approach to database programming. Here’s an overview of creating and using procedures in PL/SQL: Creating a Procedure To create a procedure, you use the CREATE PROCEDURE statement followed by the procedure's name, parameters (if any), and the body of the procedure, which contains the executable code. Here’s a basic syntax: CREATE [OR REPLACE] PROCEDURE procedure_name     [ (parameter_name [IN | OUT | IN OUT] datatype [, ...]) ] IS    -- Declaration of variables BEGIN    -- Executable statements EXCEPTION    -- Exception handling END procedure_name; Example Here’s a simple example of a PL/SQL procedure that inserts a new employee record into an employees table: CREATE OR REPLACE PROCEDURE add_employee (     p_emp_id IN employees.emp
Read more

Cursors in PL/SQL

In PL/SQL, cursors are a mechanism for accessing and manipulating query result sets. A cursor allows you to fetch and process rows returned by a query one at a time. There are two main types of cursors in PL/SQL: implicit cursors and explicit cursors. Implicit Cursors PL/SQL automatically creates implicit cursors for SELECT INTO statements, and for INSERT, UPDATE, DELETE, and MERGE statements. Example of Implicit Cursor DECLARE     v_employee_name employees.first_name%TYPE; BEGIN     -- This SELECT INTO statement uses an implicit cursor     SELECT first_name     INTO v_employee_name     FROM employees     WHERE employee_id = 100;     DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); END; In this example, PL/SQL creates an implicit cursor for the SELECT INTO statement and automatically fetches the result into the v_employee_name variable. Explicit Cursors Explicit cursors give you more control over the context area. You can open a cursor, fetch rows from the result s
Read more