MySQL Exercises - Student and Product tables

Here is the extracted content from your images, organized clearly and neatly.

---

STUDENTS TABLE – CREATE QUERY

CREATE TABLE IF NOT EXISTS student (
    enroll_no INT(5) NOT NULL,
    student_name VARCHAR(25) DEFAULT NULL,
    gender VARCHAR(6) DEFAULT NULL,
    course VARCHAR(12) DEFAULT NULL,
    total_fees INT(5) DEFAULT NULL
);

---

PRODUCTS TABLE – CREATE QUERY

CREATE TABLE IF NOT EXISTS products (
    productid INT UNSIGNED AUTO_INCREMENT,
    description VARCHAR(30) DEFAULT '',
    quantity INT UNSIGNED DEFAULT 0,
    price DECIMAL(7,2) DEFAULT 99999.99,
    PRIMARY KEY (productid)
);

STUDENTS TABLE DATA

Enroll_no	Student_Name	Gender	Course	Total_fees
12014	RAM KUMAR	MALE	DCA	8400
12015	DAISY	FEMALE	HDCA	14800
12016	KEERTHI	FEMALE	DCA	9000
12017	MUKESH	MALE	HDCA	14500
12018	ANANYA	FEMALE	HDCA	14800
12019	KARAN	MALE	DCA	9000
12020	MARTIN THOMAS	MALE	DCA	8000

PRODUCTS TABLE DATA

Product_id	Description	Quantity	Price
1004	4GB DDR4 RAM	5	1100
1005	ASUS M5A78L-M Motherboard	2	9852
1006	Gigabyte N3050M Motherboard	5	4890
1007	Gigabyte 78LMT Motherboard	2	6800
1008	Dell 21.5 Inch LED Monitor	5	6200
1009	Acer 24 Inch LED Monitor	5	8000
1010	SanDisk Ultra 32GB USB	10	550

EXERCISES

1. Add all quantity with 2 for all the products.
2. Subtract 200 from quantity for products greater than 3000.
3. Increase the price by 5% for all the products whose quantity less than 5.
4. Change the Course by ADJP and Fees by 9500 for the student MARTIN THOMAS.
5. Change name of the student to SNEKHA for the student id 12015.

---

1️⃣ INSERT QUERIES

✅ Insert into STUDENT Table

INSERT INTO student (enroll_no, student_name, gender, course, total_fees) VALUES
(12014, 'RAM KUMAR', 'MALE', 'DCA', 8400),
(12015, 'DAISY', 'FEMALE', 'HDCA', 14800),
(12016, 'KEERTHI', 'FEMALE', 'DCA', 9000),
(12017, 'MUKESH', 'MALE', 'HDCA', 14500),
(12018, 'ANANYA', 'FEMALE', 'HDCA', 14800),
(12019, 'KARAN', 'MALE', 'DCA', 9000),
(12020, 'MARTIN THOMAS', 'MALE', 'DCA', 8000);

---

✅ Insert into PRODUCTS Table

(Productid is AUTO_INCREMENT, so no need to insert it)

INSERT INTO products (description, quantity, price) VALUES
('4GB DDR4 RAM', 5, 1100),
('ASUS M5A78L-M Motherboard', 2, 9852),
('Gigabyte N3050M Motherboard', 5, 4890),
('Gigabyte 78LMT Motherboard', 2, 6800),
('Dell 21.5 Inch LED Monitor', 5, 6200),
('Acer 24 Inch LED Monitor', 5, 8000),
('SanDisk Ultra 32GB USB', 10, 550);

---

2️⃣ SELECT QUERIES

🔹 Select all students

SELECT * FROM student;

🔹 Select only student name and course

SELECT student_name, course FROM student;

🔹 Select students whose course is 'DCA'

SELECT * FROM student
WHERE course = 'DCA';

🔹 Select all products

SELECT * FROM products;

🔹 Select products where price > 3000

SELECT * FROM products
WHERE price > 3000;

🔹 Select products where quantity < 5

SELECT * FROM products
WHERE quantity < 5;

---

3️⃣ SOLUTIONS

✅ Exercise 1

Add 2 to quantity for all products.

UPDATE products
SET quantity = quantity + 2;

---

✅ Exercise 2

Subtract 200 from quantity for products greater than 3000. (Here logically it should be price > 3000.)

UPDATE products
SET quantity = quantity - 200
WHERE price > 3000;

---

✅ Exercise 3

Increase price by 5% for products whose quantity < 5.

UPDATE products
SET price = price + (price * 0.05)
WHERE quantity < 5;

OR

UPDATE products
SET price = price * 1.05
WHERE quantity < 5;

---

✅ Exercise 4

Change course to ADJP and fees to 9500 for MARTIN THOMAS.

UPDATE student
SET course = 'ADJP',
    total_fees = 9500
WHERE student_name = 'MARTIN THOMAS';

---

✅ Exercise 5

Change name to SNEKHA for student id 12015.

UPDATE student
SET student_name = 'SNEKHA'
WHERE enroll_no = 12015;

---