Assignment
1
Table STUDENT
|
Name
|
Class
|
Age
|
DateofJoin
|
City
|
|
RAHUL KUMAR
|
XII
|
16
|
20-Jan-10
|
JAMSHEDPUR
|
|
SACHIN
|
BCA
|
20
|
12-May-16
|
RANCHI
|
|
ROSHAN SINGH
|
XI
|
15
|
13-Jun-15
|
JAMSHEDPUR
|
|
TAPAS
|
MCA
|
21
|
14-Jul-14
|
PATNA
|
|
RAJU PRASAD
|
VIII
|
12
|
15-Mar-13
|
KOLKATA
|
|
ANIL SINGH
|
XII
|
17
|
16-Aug-12
|
PATNA
|
|
ARUN KUMAR
|
BCA
|
19
|
17-May-15
|
RANCHI
|
|
RAJESH PRASAD
|
XI
|
15
|
12-May-14
|
KOLKATA
|
|
RAHUL SINGH
|
MCA
|
22
|
11-May-14
|
RANCHI
|
|
MOHAN
|
VIII
|
13
|
20-May-16
|
JAMSHEDPUR
|
1. Display
all the records with all the columns
2. Display
the Name column for all the records
3. Display
the Name and Class column for all the records
4. Display
the City, Name and Age column for all the records
5. Display
all the records with all the columns for age >16
6. Display
all the records with all the columns for students studying in BCA
7. Display
all the records with all the columns for age<=16
8. Display
all the records with all the columns for students staying in PATNA
9. Display
all the records with all the columns for students staying in PATNA or RANCHI
10.
Display the Name column for all the records who are
staying in JAMSHEDPUR
11.
Display all the records with Name and Class columns
for students staying in PATNA or RANCHI
12.
Display the Name column for all the records who are
staying in JAMSHEDPUR and age<=15
13.
Display all the records with all the columns who
are not staying in JAMSHEDPUR
14.
Display all the records with all the columns who
are not staying in JAMSHEDPUR and age<=15
15.
Display all the records with all the columns whose
age is between 15 to 20 both inclusive
16.
Display all the records with all the columns whose
Dateofjoin <= 31-Dec-2015
17.
Display all the records with all the columns whose
Dateofjoin >= 01-Jan-2010 to Dateofjoin <= 31-Dec-2014
18.
Display all the records with all the columns whose
Dateofjoin >= 01-Jan-2010 to Dateofjoin <= 31-Dec-2014 and City=JAMSHEDPUR
19.
Display all the records with all the columns whose
Dateofjoin >= 01-Jan-2010 to Dateofjoin <= 31-Dec-2014 and City not equal
to JAMSHEDPUR
20.
Display all the records with all the columns whose name
starts with “R”
21.
Display all the records with all the columns whose
name ends with “R”
22.
Display all the records with all the columns whose
name have the second character as “R”
23.
Display all the records with all the columns who
studies in BCA or MCA and stays in JAMSHEDPUR
24.
Display all the records with all the columns who
studies in BCA or MCA and stays in JAMSHEDPUR or RANCHI
25.
Display all the records with all the columns who
does not study in BCA or MCA and stays in JAMSHEDPUR or RANCHI
26.
Change the name City from JAMSHEDPUR to TATANAGAR
27.
Increment the age by 1 for all records present in
the table
28.
Change the name from TAPAS to TAPSHI DAS
29.
Delete the record of SACHIN from Table
30.
Display all the records with all columns after the change
Assignment
2
1. Create the
table and insert the records as given in the table below
Table name
: Markregister
|
Roll
|
Name
|
Mark1
|
Mark2
|
|
1
|
Ashok
|
65
|
45
|
|
2
|
Ravi
|
32
|
23
|
|
3
|
Jatin
|
45
|
67
|
|
4
|
Robin
|
61
|
20
|
|
5
|
Sachin
|
75
|
95
|
2. Display
all the records present in the table with all columns
3. Display
all the records present in the table with Roll, Name and Mark1 columns
4. Display
all the records present in the table with all columns whose mark1>=40
5. Display
all the records present in the table with all columns whose mark2>=80
6. Display
all the records present in the table with all columns whose mark1+mark2>=100
7. Display
all the records present in the table with all columns whose mark1>mark2
8. Display
all the records present in the table with all columns whose mark1<mark2 and
mark1+mark2>=80
9. Display
all the records present in the table with all columns whose mark1 is between 40
to 60 and mark2 is between 80 to 100
10.
Display all the records present in the table with
all columns whose mark1+mark2 is between 100 to 120
11.
Display all the records present in the table with
all columns in the ascending order of mark1
12.
Increment the mark1 by 2 for all the records
13.
Decrement the mark2 by 5 for the students named
Jatin, Robin,Ravi
14.
Display all the records present in the table with
Roll, Name, Total
[Total is
the sum of Mark1 +Mark2 also use the concept of Alias name]
15.
ADD a column TOTAL to the Markregister Table
16.
Update the Total for all the records
17.
Display all the records present in the table with
all columns
18.
ADD a column STATUS to the Markregister Table
19.
Update the STATUS as “PASS” for all the students
who has got more than or equal to 40 in each subject separately
20.
Update the STATUS as “FAIL” for all the students
who has got less than 40 in either subject .
21.
Display all the records present in the table with
all columns
22.
Display the Average of Mark1 and Average of Mark2
for the class
23.
Display the Maximum of Mark1 and Minimum of Mark2
for the class
24.
Display the Minimum of Mark1 and Minimum of Mark2
for the class
25.
Display the Sum of Mark1 and Average of Mark2 for
the class
26.
Display the difference of the Average of Mark1 and
Average of Mark2
27.
Count the number of records present in the Table
28.
Count the number of records who have got more than
50 in both subjects
29.
Count the number of records who have got more than
80 in either of the subjects
30.
Display all the records present in the table with
all columns who have passed the examination
31.
Count the number of students failed in the
examination
32.
Display the Second Maximum marks of Mark1 from the
Table
33.
Display the Third minimum marks of Mark1 from the
Table
34.
Count the number of students who have got more than
average of mark1 in mark2
35.
Delete the records who have got less than 40 in
both subjects
36.
Display all the rows whose mark1 or mark2 is greater than the average of mark1+mark2
37.
Display all the rows whose mark1 and mark2 is greater than the average of mark1+mark2
38.
Display all the rows whose roll number is between 2
and 4 and is greater than the average of mark1+mark2
39.
Drop the column status from the Table
40.
Drop the table Markregister
