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

KTU DBMS LAB CSL 333 Syllabus and Experiments

 Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11
Read more

Creating a Database and Table ,executing queries - MySQL Lab Exercise

Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&
Read more