Class XI
MySQL Practical
Q1. Consider the following Table and write the answers and queries:
Consider the following table STUDENT
Table: Student
No NAME STIPEND STREAM AVGMARK GRADE CLASS
1 KARAN 400.00 MEDICAL 78.5 B 12B
2 DIVAKAR 450.00 COMMERCE 89.2 A 11C
3 DIVYA COMMERCE 68.6 C 12C
4 ARUN 350.00 HUMANITIES 73.1 B 12C
5 SABINA 500.00 NONMEDICAL 90.6 A 11A
6 JOHN 400.00 MEDICAL 75.4 B 12B
7 ROBERT 250.00 HUMANITIES 64.4 C 11A
8 RUBINA NONMEDICAL 88.5 A 12A
1. Which column can be considered as primary key and why?
Ans: Column No can be considered as a primary key as the values in the
No column are unique (non redundant) and it contains no blank record.
2. What is the degree and cardinality of above table?
Ans. Degree: 7
Cardinality: 8
3. Mention the attributes of the above Table
Ans:
No NAME STIPEND STREAM AVGMARK GRADE CLASS
4. Which column(s) can be considered as candidate key.
Ans. Column No, Name and Avgmark can be considered as candidate key
as these columns have non redundant (no duplicate records) values.
5. Which column(s) can be considered as Alternate key.
Ans. Name and Avgmark can be considered as alternate key
6. Which command can be used to add a new column in the table student.
Ans. Alter
Write the Queries for the following:
i. Display records of MEDICAL stream students.
Select * from student where stream=”MEDICAL”;
ii. Display the records of student who have obtained A and B grade
Select * from student where grade=’A’ or grade=’B’
iii. Display the records of students in ascending order of their AVGMARK.
Select * from student order by AVGMARK asc;
(Note : asc is for ascending order and desc is for descending order)
iv. Display names, stipend and class of 12 class students.
Select name, stipend, class from student where class like ‘12%’;
v. Delete the records of COMMERCE students.
Delete from student where stream=”commerce”;
vi. Show the names of students whose name start with D.
Select name from student where name like ‘D%’;
Show the names of students whose name end with D.
Select name from student where name like ‘%D’;
vii. Increase the stipend of all students of medical stream by 100
Update student set stipend=stipend+100 where stream=’MEDICAL’;
viii. Display the records of student whose stipend value is available.
Select * from student where stipend is not NULL;
ix. Display the records of student whose stipend value is not available.
Select * from student where stipend is NULL;
x. Add a new record in the table as
9 KANIKA 600 MEDICAL 89.9 A 12 B
Insert into student values(9, ‘KANIKA’, 600, ‘ MEDICAL’ 89.9, ‘ A’,
‘12 B’)
xi. Delete all records from the Table
Delete from student;
(Note: delete command will delete all the records but the
structure(Attributes/ headings) will remain as it is
xii. Remove the existence of table from the database.
Drop table student;
(Note: drop command will remove the existence of the table along
with structure)
Q2. Give the output of the following on the basis of student Table
i. Select sum(stipend) from student;
Ans. 2350.00
ii. Select max(AVGMARK) from student;
Ans. 90.6
iii. Select count(stipend) from student;
Ans. 6
iv. Select count(*) from student;
Ans. 8
v. Select name from student where grade=’A’;
Divakar
Sabina
Rubina
