Exercise No.1

MySQL

Insert the following data into the student table

Enroll_noStudent_NameGenderCourseTotal_fees
12014RAM KUMAR MALEDCA8400
12015DAISYFEMALEHDCA14800
12016KEERTHIFEMALEDCA9000
12017MUKESHMALEHDCA14500
12018ANANYAFEMALEHDCA14800
12019KARANMALEDCA9000
12020MARTIN THOMASMALEDCA8000
CREATE TABLE STUDENT ( Enroll_no int(06),
                      Student_Name varchar(30),
                      Gender Varchar(6),
                      Course Varchar(10),
                      Total_fees int(7) )
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 the following data into the student table

Product_idDescription QuantityPrice
10044GB DDR4 RAM52100
1005Asus M5A78L-M Motherboard29852
1006Gigabyte N3050M Motherboard54890
1007Gigabyte 78LMT Motherboard26800
1008Dell 21.5 Inch LED Monitor56200
1009Acer 24 Inch LED Monitor58000
1010SanDisk Ultra 32GB USB 10550
create table products( Product_id int auto_increment unique, 
                     Description varchar(20) ,
                     Quantity int,
                     Price int )
INSERT INTO `products`(`Product_id`, `Description`, `Quantity`, `Price`) VALUES (1004,'4GB DDR4 RAM',5,2100),(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)

Now Update the tables with following situations,

  1. Add all quantity with 2 for all the products.
  2. Subtract 2nos. 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.
UPDATE `products` SET `quantity`= `quantity`+2 
UPDATE `products` SET `quantity`= `quantity`-2 WHERE `price`>3000
UPDATE `products` SET `price`=`price` + 0.05*`price` WHERE `quantity`<5
UPDATE `student` SET `Course`='ADJP',`Total_fees`=9500 WHERE `Student_Name`='MARTIN THOMAS'
UPDATE `student` SET `Student_Name`='SNEKHA'  WHERE `Enroll_no`=12015