SYBSC-SEM-III
Assignment Date-16/7/15
Submission date-10/7/15
Practical Assignment-1(RDBMS)
practicle-1 Create the following tables
1) Write a query to create table student_info.(with minimum 7 fields).
2)
Write
a
query
to
(studid,sub1mark,sub2mark,sub3mark...)
create
student_marks.
practicle-2 Insert data into following tables
3) Write a query to insert data in student_info table.(min 5 record)
4) Write a query to insert data in student_marks table.(min 5 record)
Practicle-3 Constraints(validation)
5) Add primary key constraint in student_info and foreign key constraint into
student_marks.
6)Insert check constraint for data values being inserted into column
stud_name into of table student_info table should be in lower case only.
7) Insert check constraint for data values being inserted into column gender
must be 'm' and 'f' only in student_info table.
8) set default value for all subject as zero in student_marks table.
9) Insert check constraint in student_marks table that marks of all subject
must be in between 1 to 100.
10) Add new column hobby and enrollno into student_info table and enrollno
must start with 'e'.
11) Set default value of city as 'valod'.
12) Add constraint in student info that enrollnumber must be unique.
Practicle-4 Perform the following queries(single table)
13)Write a query to display structure of a table.(e.g. structure of
student_info).
14)create table try(tryid,tryname).
15)rename table try to success.
16)Display current user name.
17)Add a new column stud_state in student_info table.
18)Add a new column stud_country in student_info table.
19)change the size of coumn stud_state.
20) Display only student name and contact number from student_info.
21) Display only distinct row from student_info table.
22) Display distinct city from student_info.
23) Display all record of student who passed in sub1 from student_marks
table.
(e.g. student who have secured marks more than 35 in sub1 will be
displayed).
24) Display student id and marks of all subject whose id is 105(or any one id
you entered in
student info table(e.g. id 1).
25) Display all information of student whose name start from d.
26) Display all information of student whose name start from a or b.
27) Display city name have second character u or a.(e.g valod or bardoli)
28) Display all information of student who born between january to april.
29) Display student id,city and name of student who live in bardoli or valod.
30) Display student id,city and name of student who do no live in valod.
31) Display student id,city and name of student whose gender is male and
not live in vallod
32) Display all record from student_marks table and sub3mark in descending
order.
33) Display all record from student_info order by city.
Practicle-5 Perform the following queries(multiple table)
34) Display student name,sub1mark,sub2mark,sub3mark.
35) Display student name, student birth date and student marks of all
subject.
36) Display student id , name, marks of all subject who passed in all subject.
37) Display student id, marks of all subject, total and percentage.
38) Display student id ,marks of all subject and percentage.(percentage
must be in decimal point).
39) Display stud birth date and marks of sub1.
40)Display all information of student_info table who secured marks more
than 60.
41)Display student id,student name,city,marks of subject1 who have secured
less than 50 marks in subject1.
