STUDENT DETAILS IN SQL

create a student table with the fields namely roll number ,name,date of birth,sex,father name,address,phone number.

SQL> create table studentdetails(rno int, name varchar2(20),fname varchar2(20),dob varchar2(20),sex varchar2(10),address varchar2(20),pno int, sslc number(5),hsc number(5),ug number(5));

Table created.

SQL> insert into studentdetails values('&rno','& name ','& fname ','& dob ','& sex ', '& address '& pno’, '& ,sslc ','& hsc ', '& ug');

Enter value for rno: 1

Enter value for name:john

Enter value for fname:jony

Enter value for dob:10/12/91

Enter value for sex:male

Enter value for address:north korea

Enter value for pno:2345678267

Enter value for sslc:78

Enter value for hsc:69

Enter value for ug:88

1 row created.

ii.Create a course table fields namely course id,course applied,eligibility,fees/semester

SQL> create table course(cid int,courseapplied varchar2(20),eligibility varchar2(10),fees varchar2(10));

Table created.

SQL> insert into course values('&cid','& courseapplied ','& eligibility ', ’& fees’);

Enter value for cid: 1

Enter value for courseapplied:Mca-tech

Enter value for eligibility:ug

Enter value for fees:25000

1 row created.

iii.Write a query to display the SSLC,HSC and UG mark attained by a specific student.

SQL>select sslc,hsc,ug from studentdetails where  rno=1;

   SSLC   HSC   UG

   -------   ------   -----

     78       69       88

iv.Write a query to display the available course of the instituation.

SQL>select courseapplied from  course;

courseapplied

---------------------

Mca-tech

Mca-non tech

Mba

v.Write a query to display the details Course wise.

SQL>select *  from course where  courseapplied=’mba’;

CID   COURSEAPPLIED   ELIGIBILITY   FEES

-----   -------------------------   ------------------  -------

3             mba                            ug                  40000

vi.Find the topper in UG marks.

SQL>select max(ug)as “topper” from studentdetails;

TOPPER

-----------

88

vii.Find the least mark score in HSC

SQL>select min(hsc)as “least” from studentdetails;

LEAST

-----------

69

viii.Write a query to update the SSLC mark of any one student.

SQL>update studentdetails set hsc=80 where rno=1;

1 row updated.

 x.Course wise find the rank holder-using group by.

SQL>select ug from studentdetailas group by rno;

UG

----

88

77

65