MySQL Assignment on Select Statement


MySQL assignment on the select statement. The sample database table student –

+-------+---------+--------+-------+-------+---------+--------+--------+
| admno | name    | stream | marks | grade | fees    | gender | gameID |
+-------+---------+--------+-------+-------+---------+--------+--------+
| 101   | rakesh  | sci    | 96.56 | A     | 1850.35 | F      |      2 |
| 102   | udit    | sci    | 56.50 | B     | 1850.35 | M      |      1 |
| 103   | mohit   | bio    | 46.50 | C     | 1980.90 | M      |      3 |
| 104   | mannat  | human  | 67.50 | A     | 1420.15 | F      |      4 |
| 105   | unnati  | comm   | 77.50 | B     | 1650.75 | F      |      1 |
| 106   | nikunj  | sci    | 79.50 | A     | 1850.35 | M      |      3 |
| 107   | pushkar | comm   | 54.20 | C     | 1650.75 | M      |      1 |
| 108   | pratham | comm   | 84.80 | A     | 1650.75 | M      |      4 |
| 109   | mohit   | comm   | 64.80 | B     | 1650.75 | M      |      2 |
| 110   | urshavi | bio    | 96.80 | A     | 1980.90 | M      |      3 |
| 111   | punya   | comm   | 86.56 | B     | 1650.75 | F      |      1 |
| 112   | vipul   | comm   |  NULL | A     | 1650.75 | M      |      2 |
+-------+---------+--------+-------+-------+---------+--------+--------+

Q1. Display all the contents of the above table student.

Q2. Display admno, name, stream of all the student from student tables

Q3. Create a report of student showing name, admno, fees from the above table student

Q4. Create a report of students showing admno, name, fees , annual fees from the above table student ( consider fees listed in the above table as monthly fees)

Q5. Display admno, ‘student name’, stream ,gender from the above table student ( where student name is an alias of name column )

Q6. Display different streams available in the student table.

Where clause

Q7. Display the records of all the students whose gameID is 3

Q8.  Create a list of students whose marks is greater than 70%

Q9.  Display admno, name and stream of all the female students.

Q10. Create a list of student’s whose stream is science and grade is ‘A’

Q11. Display the records of all the student’s whose stream is either ‘bio’ or ‘human’

Q12. Make a list of students whose stream is not commerce.

BETWEEN  AND Clause

Q13. Display all the records of students who is able to score marks between 80% and 90%

Q14. Create a list of student whose marks are not between 50%  and  60%.

Q15. Create a report of all the female students having fields admno, name, fees and annual fees of  whose  grade is between A and C.

IN Clause

Q16.  Display the details of all the students whose game is either 1, 2 or 4.

Q17.  Create alist of students whose stream is either ‘bio’, science’ or ‘ human’ from the table student.

Like Clause

Q18.  Display the details of all the students whose names start with the alphabet ‘p’ from the table student.

Q19.  Display admno, name, stream, and gender of all those students whose name have 5 characters in their name and end character is ‘t’.

Q20.  create a list of students whose names end with the alphabet ‘l’ and start with any character.

Q21.  List all the students from the table student whose name contains ‘nn’.

Order By Clause

Q22.  Display the contents of the student table in ascending order ( by name).

Q23. Display the contents of the student table in descending order ( by marks )

Q24. Create a report of students having fields admno, name, marks, stream in descending order. when students have the same marks then give preference to the student name ( ascending order).

Grouping Functions

Q25. Display the maximum marks, minimum marks, and average marks from the above table student.

Q26. find out the total number of unique streams available in the above table student.

Group By Clause

Q27. find out stream-wise students’ strength.

Q28. find out stream-wise student strength where marks obtained by the student is greater than 70.

Q29.  Display the list of streams and the total number of students in that stream if the counting of students in that stream is less than 3.

Q30.  find out the stream wise total collection of fees from the student table.

Create command with select command.

Q31. Create a table ‘Female’ that contains all the records of student table as well as its data structure.

Q32.  Create a table  ‘Male’ that contains admno, name, stream, marks from the student table.

Hope this MySQL Assignment on Select Statement will help to brush up your skills on MySQL select statement.

Print Friendly, PDF & Email

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.