TECHONESPOT

All Tech at one spot

How to Create a table
Stream and Student
to perform certain queries on the table.

Start with Database 

-> How to create database Student_Stream.

Solution :- 

create database Student_steam;

Output :- 

-> Access the database Student_steam.

Solution :- 

use Student_steam;

Output :-

-> Create table Student.

 

Solution : –

create table Student(
admno int not null,
sname varchar(100)not null,
class varchar(5)not null,
sec varchar(1)not null,
fee int not null,
mobile varchar(10) not null,
area varchar(50),
s_id int,
primary key (admno),
foreign key (s_id) references Stream(s_id));

Output :- 

 

-> Create table Stream.

Solution 

create table Stream
(
s_id int primary key,
stream_name varchar(100)
);

Output :- 

->Insert value in Stream;

 

Solution :- 

insert into Stream values(10,”Medical”);
insert into Stream values(20,”Non Medical”);
insert into Stream values(30,”Commerce with Math”);
insert into Stream values(40,”Commerce with IP”);
insert into Stream values(50,”Humanities”);

Output:-

 

-> Insert value in Student table.

Solution :- 

insert into Student values(1001,’Ramesh’,’XII’,’A’,2500,’5566332255′,’Madipur’,10);
insert into Student values(1078,’Krishna’,’XII’,’B’,2400,’5632536211′,’Jawala Heri’,30);
insert into Student values(1006,’Jack’,’XII’,’C’,2600,’5566377756′,’Paschim Puri’,40);
insert into Student values(1004,’Shubham’,’XII’,’A’,2500,’9554433221′,’Madipur’,20);
insert into Student values(1029,’Harish’,’XI’,’A’,2500,’5566332255′,’Madipur’,10);
insert into Student values(1008,’Rishi’,’XII’,’A’,2450,’7766332255′,’Puri’,20);
insert into Student values(1025,’Radhika’,’XII’,’B’,2580,’6566332255′,’Madipur’,30);
insert into Student values(1036,’Yash’,’XII’,’B’,2600,’6566332255′,’Madipur’,30);
insert into Student values(1037,’Aman’,’XII’,’C’,2650,’7799332255′,’Puri’,40);
insert into Student values(1023,’Nishant’,’XII’,’A’,2750,’9966332255′,’Heri’,10);

Output :- 


 

-> Display Stream id and Stream wise total fee collected.

Solution :-

Select s_id,sum(fee) from Student group by s_id;

Output :- 

->Count no of Students for each area.

Solution :- 

Select area , count(area) from student group by area;

Output :- 

->Display all the Student details those who belong to madipur area.

Solution :-

Select * from Student where area = “madipur”;

Output :- 

->Increase the fees of all the Student by 10%.

Solution :-

update Student set fee = fee +(fee*10)/100; 

Output :- 

-> Display unique area from the Student table.

Solution :- 

select distinct(area) from Student;

Output :-

-> Display details of those students whose area contains ‘Puri’.

Solution :-

Select * from Student where area =’puri’; 

Output :- 

-> Display the information of those students who are in class XII and section is either B or C.

Solution :- 

Select * from Student where class =’XII’ and sec in(‘B’,’C’);

Output :- 

-> Dispaly class and total fee collected from each class.

Solution :-

Select class , sum(class) from Student group by class; 

Output :- 

-> Display admission no , student name and Stream name.

Solution :- 

Select admno , sname , stream_name from Student,stream where student.s_id = stream.s_id;

Output :- 

-> Display all the student detail who have taken Commerce Stream.

Soution :- 

Select * from Student, stream where student.s_id = stream.s_id and stream_name like’COMMERCE%’;

Output :- 

->Count number of Students who have opted for Humanities stream.

Solution :- 

Select count(stream_name) from student,stream where student.s_id = stream.s_id and stream_name =’Humanities’;

Output :- 

->Display information of commerce with ip Student whose name start with ‘S’.Arrange the record by admission number.

Solution :- 

Select admno , sname,class , sec , fee, mobile,area,Student.s_id, stream_name from Student, stream where Student.s_id = Stream.s_id and sname like ‘S%’ order by admno asc;

Output :- 

->Display details of all the Students who are in the Medical Stream.

Solution :-

Select * from Student ,stream where student.s_id = stream.s_id and stream_name =”Medical”; 

Output :- 

->Display total fee of “Non-Meidcal” Student.

Solution :-

Select sum(fee) from Student , stream where Student.s_id = Stream.s_id and Stream_name =”Non Medical”; 

Output :- 

-> Change the name of the Column Sname to Student name.

Solution :- 

Alter table student change sname student_name varchar(20);

Output :- 

Fun & Easy to follow
Works on all devices
Your own Pace
Super Affordable

The Best Collection place for gaining power of knowledge, skills and new opportunities.