CS MySQL practical questions with Key Answers
STEP 1 – Open MySQL Client and ask for Password.
STEP 2 – Create database using syntax CREATE DATABASE mydb; then press enter
STEP 3 – Type USE mydb; then press enter
B1)
1. Create a table with the following fields.
Entity Name: Marks
Attribute name Type Size Constraints
Rollno Int 5
Sname Varchar 15 Not null
Lang1 Int 3 Between 0 and 100
Lang2 Int 3 Between 0 and 100
Sub1 Int 3 Between 0 and 100
Sub2 Int 3 Between 0 and 100
Sub3 Int 3 Between 0 and 100
Sub4 Int 3 Between 0 and 100
CREATE TABLE marks (
Rollno INT(5),
Sname VARCHAR(15) NOT NULL,
Lang1 INT(3) CHECK (Lang1 BETWEEN 0 AND 100),
Lang2 INT(3) CHECK (Lang2 BETWEEN 0 AND 100),
Sub1 INT(3) CHECK (Sub1 BETWEEN 0 AND 100),
Sub2 INT(3) CHECK (Sub2 BETWEEN 0 AND 100),
Sub3 INT(3) CHECK (Sub3 BETWEEN 0 AND 100),
Sub4 INT(3) CHECK (Sub4 BETWEEN 0 AND 100)
);
2. Insert 6 records.
3. Display the description of the table.
DESCRIBE Marks;
4. Add the new attributes Total and Percentage
ALTER TABLE marks ADD(total INT(3)), ADD(percent FLOAT(5,3));
1
5. Calculate total and percentage of marks for all the students
UPDATE marks SET total = Lang1+Lang2+Sub1+Sub2+Sub3+Sub4;
UPDATE marks SET percentage = total/600*100;
6. Display all the students whose percentage of marks is more than 60%.
SELECT * FROM marks WHERE percentage >=60%
B2)
1 . Create a table for house hold Electricity bill with the following fields
Entity Name: BESCOM
Attribute Name Type Size Constraint
RRNO Varchar 10 Primary key
CUSTNAME Varchar 25 Not null
BILLDATE DATE
UNITS INT 4
2. Insert 6 records.
3. Display the description of the table or view the structure of table.
DESCRIBE BESCOM;
4. Add a new field for bill amount in the name of BillAmt.
ALTER TABLE BESCOM ADD BillAmt FLOAT(7,2);
5. Compute the bill amount for each consumer as per the following rules.
a. MINIMUM Amount Rs. 100
b. For first 100 units Rs 7.50/Unit
c. For the above 100 units Rs. 8.50/Unit
UPDATE BESCOM SET BillAmt = 100 + UNITS * 7.50 WHERE UNITS <=
100;
UPDATE BESCOM SET BillAmt = 100 + (100 * 7.50) + (UNITS – 100) *
8.50 WHERE UNITS > 100;
6. List all the bills generated in a sorted order based on RRNO.
2
SELECT * FROM BESCOM ORDER BY RRNO;
B3)
1. Create a table with the following details
Entity Name: Student
Attribute name Type Size Constraint
Rollno Int 5 Primary key
Sname Varchar 15 Not null
DOB Date
Gender Char 1
Combn Varchar 5
Class Char 6
2. Insert 6 records ( with combination BASC, CEBA, PCMC).
3. List all the student records.
SELECT * FROM student;
4. List only the combination by removing duplicate values.
SELECT DISTINCT(combn) FROM student;
5. List the students(sname) alphabetically.
SELECT sname FROM student ORDER BY sname;
6. List the students who born in the month of June of any year.
SELECT * FROM student WHERE MONTH(dob) = 6;
B4)
3
1. Create a table with following fields
Entity Name: Library
Attribute Name Type Size Constraint
Title Varchar 75 Not null
Author Varchar 60
Year Int 4
Category Varchar 25
Price Float 7,2
Qty Int 4
2. Enter 6 records into the table.
3. List all the records of the table.
SELECT * FROM Library;
4. Alter the table by adding a new column Amount.
ALTER TABLE Library ADD (Amount FLOAT(7,2));
5. Calculate Amount as per Qty and price.
UPDATE Library SET Amount = Price * Qty;
6. List the records of all those books priced between 400 and 900.
SELECT * FROM Library WHERE price BETWEEN 400 AND 900;