II PUC SQL Lab Program

PART B
SQL – Students Marks Report
Experiment B1

1. Create a table with the following fields and enter 4 records into the table.
Entity Name : Marks.

Attribute Type size Constrains

name
SQL 1
Rollno Int 5
Sname Varchar 25 Not null
L1 Int 5
L2 Int 5
Sub1 Int 5
Sub2 Int 5
Sub3 Int 5
Sub4 Int 5

2. List all the records.

3. Display the description of the table.
4. Add the new attributes total and percentage.
5. Calculate total and percentage of marks for all the student.
6. List the student whose percentage of marks is more than 60%
7. List the student whose percentage is between 80% and 85%.
8. Arrange the student based on percentage of marks from highest to
lowest.

Page 1
 II PUC SQL Lab Program

Solution :

1. Create a table with the following fields and enter 4 records into the
table.
Entity_Name : Marks.

1a) mysql> CREATE TABLE Marks

-> (
-> Rollno int(5),
-> Sname Varchar(25) NOT NULL,
-> L1 int(5),
-> L2 int(5),
-> Sub1 int(5),
-> Sub2 int(5),
-> Sub3 int(5),
-> Sub4 int(5)
- > );

Query OK, 0 rows affected (0.01 sec)

1b) mysql>INSERT INTO marks VALUES (68,'krishna',99,85,84,87,74,65);

mysql>INSERT INTO MarksVALUES(58,'Balarama',40,75,38,70,45,86);
mysql>INSERT INTO Marks VALUES(86,'Radha',88,75,75,85,95,75);
mysql>INSERT INTO Marks VALUES(74,'Rukmini',75,84,74,74,75,99);

2. List all the records.

mysql>SELECT * FROM Marks;

Rollno Sname L1 L2 Sub1 Sub2 Sub3 Sub4

68 Krishna 99 85 84 87 74 65
58 Balarama 40 75 38 70 45 86
86 Radha 88 75 75 85 95 75
74 Rukmini 75 84 74 74 75 99

Query OK, 4 row affected (0.00 sec)

Page 2
 II PUC SQL Lab Program

3. Display the description of the table.

mysql> DESC Marks;

Field Type Null Key Default

Rollno int(5) Yes Null
Sname Varchar(25) NO Null
L1 Int(5) Yes Null
L2 nt(5) Yes Null
Sub1 Int(5) Yes Null
Sub2 Int(5) Yes Null
Sub3 Int(5) Yes Null
Sub4 Int(5) Yes Null

8 rows in set (0.01 sec)

4. Add the new attributes total and percentage.

mysql> ALTER TABLE Marks ADD (Total int(6), Percentage float(8,3));

Query OK, 4 rows affected (0.03 sec)

5. Calculate total and percentage of marks for all the student.

mysql> UPDATE Marks SET Total=L1+L2+sub1+sub2+sub3+sub4;

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4 Changed: 4 Warnings: 0

mysql> UPDATE Marks SET Percentage=(Total/600)*100;

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4 Changed: 4 Warnings: 0

Page 3
 II PUC SQL Lab Program

6. List the student whose percentage of marks is more than 60%

mysql> SELECT Sname, Percentage FROM Marks WHERE

percentage>=60;

Sname Percentage
Krishna 82.333
Radha 82.167
Rukmini 80.167

4 rows in set (0.00 sec)

7. List the student whose percentage is between 80% and 85%.

mysql> SELECT Sname, Percentage FROM Marks WHERE Percentage

between 80 AND 85;

Sname Percentage
Krishna 82.333
Radha 82.167
Rukmini 80.167

3 rows in set (0.00 sec)

8. Arrange the student based on percentage of marks from highest to

lowest.

mysql> SELECT Sname, Percentage FROM Marks ORDER BY

Percentage DESC;

Sname Percentage
Krishna 82.333
Radha 82.167
Rukmini 80.167
Balarama 59.000

4 rows in set (0.00 sec)

************

Page 4
II PUC SQL Lab Program

PART B
SQL – CESCOM Report
Experiment B2

1. Create a table for house holder electricity bill with the following
fields and enter 4 records. Entity Name Cescom.

Attribute Type Size Constraint

name
RRno Varchar 10 Primary Key SQL 2
Cname Varchar 25 Not Null
Bdate Date
Units Int 5

2. View the structure of the table.

3. List all the records.
4. Add a new field bill amount in the name of Bamt.
5. Compute the bill amount for each consumer as per the following rules.
 Minimum amount Rs 100.
 For first 100 units Rs 7.50/Unit
 For the above 100 units Rs 8.50/Unit
6. Display the maximum, minimum, average and sum from Cescom.
7. List all the bill generated in a sorting order base on RRno.

Page 5
 II PUC SQL Lab Program

SOLUTION :
1. Create a table for house holder electricity bill with the following fields
and enter 4 records. Entity_Name Cescom.

1a) mysql> CREATE TABLE Cescom

-> (
-> RRno varchar(10) PRIMARY KEY,
-> Cname varchar(25) NOT NULL,
-> Bdate date,
-> Units int(5)
-> );

Query OK, 0 rows affected (0.00 sec)

1b)mysql>INSERT INTO Cescom VALUES('D13','Rama','2025-09-25', 125);

mysql>INSERT INTO Cescom VALUES ('A143', 'Raju', '2025-10-15', 98);
mysql>INSERT INTO Cescom VALUES('C65','Ramesh','2025-09-29',198);
mysql>INSERT INTO Cescom VALUES('B61','Balarama','2025-09-10',78);

2. View the structure of the table.

mysql>DESC Cescom;

Field Type Null Key Default

RRno varchar(10) NO PRI
Cname varchar(25) NO NULL
Bdate date YES NULL
Units int(5) YES NULL
4 rows in set (0.00 sec)

3. List all the records.

mysql>SELECT * FROM Cescom;

RRno Cname BDATE UNITS
D13 RAMA 2025-10-15 125
A143 RAJU 2025-09-25 98
C65 RAMESH 2025-09-29 198
B61 BALARAMA 2025-10-10 78

4 rows in set (0.00 sec)

Page 6
 II PUC SQL Lab Program

4. Add a new field bill amount in the name of Bamt.

mysql> ALTER TABLE Cescom ADD Bamt float (8,3);

Query OK, 4 rows affected (0.02 sec)

Records: 4 Duplicates: 0 Warnings: 0

5. Compute the bill amount for each consumer as per the following rules.
 Minimum amount Rs 100.
 For first 100 units Rs 7.50/Unit
 For the above 100 units Rs 8.50/Unit

5.1 mysql> UPDATE Cescom SET Bamt=100;

Query OK, 4 rows affected (0.00 sec)

Rows matched: 2 Changed: 2 Warnings: 0

5.2 mysql> UPDATE Cescom SET Bamt=Bamt + Units*7.50 WHERE

Units <=100;

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2 Changed: 2 Warnings: 0

5.3 mysql> UPDATE Cescom SET Bamt=Bamt + (100*7.50)+ ((Units-

100) * 8.50) WHERE Units >100;

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2 Changed: 2 Warnings: 0

6. Display the maximum, minimum, average and sum from Cescom.

mysql> SELECT MAX(Bamt), MIN(Bamt), AVG(Bamt),SUM(Bamt)

FROM Cescom;

MAX(Bamt) MIN(Bamt) AVG(Bamt) SUM(Bamt)

1683.000 685.000 1066.3750000 4265.500

1 row in set (0.00 sec)

Page 7
 II PUC SQL Lab Program

7. List all the bill generated in a sorting order based on RRno.

mysql> SELECT * FROM CescoM ORDER BY RRNO;

RRno Cname BDATE UNITS Bamt

A143 Raju 2025-09-25 98 835.000
B61 Balarama 2025-10-10 78 685.000
C65 Ramesh 2025-09-29 198 1683.000
D13 Rama 2025-10-15 125 1062.500

4 rows in set (0.00 sec)

********************

Page 8
II PUC SQL Lab Program

PART B
SQL – Students Report
Experiment B3
1. Create a table with the following details and enter 5 records into the
table. Entity Name: student

Attribute Type Size Constraint

name
Rollno Int 5 PRIMARY KEY SQL 3
Sname Varchar 25 NOT NULL
DOB Date
Gender Char 5
Comb Varchar 10
Class Varchar 10

2. List all the students.

3. List only those students who are in EBACs and PCMCs combination.

4. List only the combination by removing duplicate values.

5. List the students alphabetically.

6. List the students who born in the month of September of any year.

7. Count the number of students Gender-wise.

8. List the students alphabetically class-wise.

Page 9
II PUC SQL Lab Program

1. Create a table with the following details and enter 5 records into the
table. Entity Name: student

Attribute Type Size Constraint

name
Rollno Int 5 PRIMARY KEY
Sname Varchar 25 NOT NULL
DOB Date
Gender Char 5
Comb Varchar 10
Class Varchar 10

1a) mysql> CREATE TABLE Student

-> (
-> Rollno Int (8) PRIMARY KEY,
-> Sname Varchar (25) NOT NULL,
-> DOB Date,
-> Gender Character (1),
-> Comb Varchar (10),
- -> Class Varchar (10)
-> );

Query OK, 0 rows affected (0.00 sec)

1b)mysql> INSERT INTO Student VALUES (85, 'Krishna', '2007-09-17',

'M','EBACs', '2D');
mysql> INSERT INTO Student VALUES (150, 'Rukmini', '2007-06-28',
'F', 'PCMCs', '2C');
mysql> INSERT INTO Student VALUES (68, 'Balarama', '2008-11-19',
'M', 'EBACs', '2D');
mysql> INSERT INTO Student VALUES (169, 'Vasu', '2006-08-24',
'M', 'PCMB', '2A');
mysql> INSERT INTO Student VALUES (79, 'Radha', '2007-09-25',
'F', 'EBACs', '2D');

Query OK, 1 row affected (0.00 sec)

Page 10
 II PUC SQL Lab Program

2. List all the students.

mysql> SELECT * FROM Student;

Rollno Sname DOB Gender Comb Class
68 Balarama 2008-11-19 M EBACs 2D
79 Radha 2007-09-25 F EBACs 2D
85 Krishna 2007-09-17 M EBACs 2D
150 Rukmini 2007-06-28 F PCMCs 2C
169 Vasu 2006-08-24 M PCMB 2A
5 rows in set (0.00 sec)

3. List only those students who are in EBACs and PCMCs combination.

mysql> SELECT * FROM Student WHERE Comb='EBACs' OR

Comb = 'PCMCs';

Rollno Sname DOB Gender Comb Class

68 Balarama 2008-11-19 M EBACs 2D
79 Radha 2007-09-25 F EBACs 2D
85 Krishna 2007-09-17 M EBACs 2D
150 Rukmini 2027-09-28 F PCMCs 2C

4 rows in set (0.00 sec)

4. List only the combination by removing duplicate values.

mysql> SELECT DISTINCT(Comb) FROM Student;

Comb
EBACs
PCMCs
PCMB

3 rows in set (0.00 sec)

5. List the students alphabetically.

mysql> SELECT Sname FROM Student ORDER BY Sname;
Sname
Balarama
Krishna
Radha
Rukmini
Vasu
5 rows in set (0.00 sec)

Page 11
 II PUC SQL Lab Program

6. List the students who born in the month of June of any year.

mysql> SELECT * FROM Student WHERE Month (DOB)=09;

Rollno Sname DOB Gender Comb Class

79 Radha 2007-09-25 F EBACs 2D
85 Krishna 2007-09-17 M EBACs 2D

2 rows in set (0.00 sec)

7. Count the number of students Gender-wise.

mysql> SELECT Gender, Count(*) FROM Student GROUP BY Gender;

Gender Count(*)
F 2
M 3

2 rows in set (0.00 sec)

8. List the students alphabetically class-wise.

mysql> SELECT Sname, Class FROM Student GROUP BY Sname

ORDER BY Class;
Sname Class
Vasu 2A
Rukmini 2C
Balarama 2D
Krishna 2D
Radha 2D

5 rows in set (0.00 sec)

**********

Page 12
II PUC SQL Lab Program

PART B
SQL – Library Report
Experiment B4
1. Create a table with following fields and enter 4 records into the
table.
Entity Name: Library

Attribute name Type Size Constraint

SQL 4
Title Varchar 30 Not null
Author Varchar 30
Year Int 6
Category Varchar 30
Price Float 10,4
Qty Int 5

2. List all the books.

3. Calculate Amount by altering table by adding a new column ‘Amount’.
4. List the records of all those books price is between 650 and 750.
5. List those records with no value in the attribute year.
6. List the names of the authors whose name starts with letter ‘P’ or ‘G’.
7. List Title, year and category from the table library with category field has
word ‘reference’.
8. List all those records whose year of publication is 2010 onwards with
book price is less than Rs.750.

Page 13
 II PUC SQL Lab Program

SOLUTION :
1. Create a table with following fields and enter 4 records into the table.
Entity Name: Library

1a) mysql> CREATE TABLE Library

-> (
-> Title varchar(30),
-> Author varchar(30),
-> Year int(6),
-> Category varchar(30),
-> Price float(10,4),
-> Qty int(5)
-> );

Query OK, 0 rows affected (0.00 sec)

1b) mysql> INSERT INTO Library VALUES('Complete reference', 'Prasad',

2005, 'Python', 680, 10);

mysql> INSERT INTO Library VALUES('Data Base concept',

'Hemanth', 2012, 'DBMS', 720, 7);

mysql> INSERT INTO Library VALUES ('ANSI C', 'Bal Guruswamy',

2010, 'C', 520, 5);

mysql> INSERT INTO Library VALUES ('Complete reference', 'Prasad',

2005, 'Java', 680, 10);

mysql> INSERT INTO Library VALUES ('Complete reference', 'Prasad',

2005, 'C using Data structure', 580, 10);

mysql> INSERT INTO Library (Title, Author, Category, Price, Qty)

VALUES('Computer Science', 'Gundu Rao', ‘Text Book',
300,12,);

Page 14
 II PUC SQL Lab Program

2. List all the books.

mysql> SELECT * FROM Library;

Title Author Year Category Price Qty

Complete Prasad 2005 Python 680 10
Reference
Data Base Hemanth 2012 DBMS 720 7
Concept
ANSI C BalGuruswamy 2010 C 520 5
Complete Prasad 2005 Java 680 10
Reference
Complete Prasad 2005 C using Data 580 10
Reference Structure
Computer Gundu Rao NULL Text BooK 300 12
Science

6 rows in set (0.00 sec)

3. Calculate Amount by altering table by adding a new column ‘Amount’.

3a) mysql> ALTER TABLE Library ADD Amount float(8,3);

Query OK, 6 rows affected (0.01 sec)

Records: 6 Duplicates: 0 Warnings: 0

3b) mysql> UPDATE Library SET Amount= Price * Qty;

Query OK, 6 rows affected (0.00 sec)

Rows matched: 6 Changed: 6 Warnings: 0

4. List the records of all those books price is between 650 and 750.

mysql> SELECT * FROM Library WHERE Price between 650 AND 750;
Title Author Year Category Price Qty Amount
Complete Prasad 2005 Python 680 10 6800.000
Reference
Data Base Hemanth 2012 DBMS 720 7 5040.000
Concept
Complete Prasad 2005 Java 680 10 6800.000
Reference
3 rows in set (0.00 sec)

Page 15
 II PUC SQL Lab Program

5. List those records with no value in the attribute year.

mysql> SELECT Title, Year FROM Library WHERE Year IS Null;

Title Year
Computer Science NULL

6. List the names of the authors whose name starts with letter ‘P’ or ‘G’.
mysql> SELECT Author FROM Library WHERE Author like 'P%' or
Author like 'G%';

Author
Prasad
Prasad
Prasad
Gundu Rao

7. List Title, year and category from the table library with category field
has word ‘reference’.

mysql> SELECT Title, Year, Category FROM Library WHERE Title

like '%reference%';

Title Year Category

Complete Reference 2005 Python
Complete Reference 2005 Java
Complete Reference 2005 C using Data
Structure

3 rows in set (0.00 sec)

8. List all those records whose year of publication is 2010 onwards with
book price is less than Rs.750.

mysql> SELECT Year, Price FROM Library WHERE Year>=2010 AND

Price<650;

Year Price
2010 520.000
1 row in set (0.00 sec)

****************

Page 16