SQL Exercise

1. Display all students whose ID are even numbers.
2. Display all students of Class Four in the order of mark from highest to lowest
3. Display all students of Class Four in the order of their Name
4. Display all students in the order of Class and then Mark.
5. Display all students who got mark more than 50
6. Display all students who got mark between 50 and 60
7. Display all students who’s name starts with A
8. Display all students who’s name ends with hn
9. Display all students who’s name has John at any location
10. Display all students who’s name start with A or start with B
11. Display all students who’s name ends with A or ends with B
12. Display all students who’s id is more than 10 and second digit is 3
13. Display all students who’s name starts with A and ends with n
14. Display all students who’s name starts with A but does not end with n
15. Display all details of the student having highest mark
16. Display the student having second highest mark
17. Display the 5 students who secured lowest mark
18. Display the students who’s name starts with A and got more than 60 mark
19. Display the students who’s ids are (within) 12, 14,13, 9 & 6
20. Display all the classes in student table
21. Display all 5 students who added recently ( last 5 records ) of student table

1. Find out the number of students.
2. Find out the number of students in Class Three
3. Find out the number of students who got more than 50 mark
4. Find out the number of students who got mark between 50 and 70
5. List the students who got more than 50 mark of class three
6. What is the highest mark
7. What is the highest mark of class Three
8. What is the average mark of class Four.
9. What is the minimum mark of class Five
10. What is the Total mark of students of class Three
11. List the full details of ( name, class, mark , sex ) of the student who got highest mark.
12. List details of students of top three rank holders based on mark.

UPDATE

1. Add 2 mark to each student
2. Add 5 mark to each student who got less than 50
3. Add -3 mark to each student who got more than 50
4. Change the last char for all the students ( name column ) by making it to y if it is x

DELETE

1. Delete all the records of Class file
2. Remove all the records who got mark less than 60
3. Remove all records who's name starts with Al
4. Remove all records who's name having John
5. Remove all records with odd number of ID
6. Remove all records not having John in its name

STRING

1. Display the student name and its number of Chars
2. Display the student name along with reverse of it
3. Display the student name along with name in Upper case
4. Display the first two chars of the student name
5. Display the last two chars of the student name

MORE on STRING

1. Display the rest of the email address starting from 3rd position from left.
2. Display the last four chars of the email address.
3. Display ( email address ) starting from 2nd position 4 chars (to be collected)
4. Display from right ( end ) starting from 6 position return 4 chars (length)
5. Display the position of @ inside email address along with the email address.
6. From the email address get the first (left) 3 chars along with the email address.
7. From the email address get the last (right) 3 chars along with the email address.
8. In one column email address are stored, create another two columns and store userid in one and domain part in other column of the table. ( use the above concepts )
9. Using the above update, reverse the userid and display
10. Along with the original userid, display userid by adding plus2net_ before
11. Along with the original userid, display userid by adding plus2net_ after
12. Remove .com from the domain name

LINKING MORE THAN ONE TABLE

1. Exercise on LEFT JOIN
2. How many records will be returnd for this query
   SELECT * FROM student,student_football
3. How many records will be returnd for this query
   SELECT * FROM student,student_football,student_baseball
4. How many reocrds will be returned for this query
   SELECT * FROM student,student_football WHERE id=f_id
5. How many reocrds will be returned for this query
   SELECT * FROM student,student_football WHERE id != f_id
6. Display full details of students who are in NOT selected for football team ( use LEFT JOIN )
   Try by not using LEFT Join and see the difference.
   SELECT id,name,class,mark,sex,f_id FROM `student` LEFT JOIN student_football on id=f_id WHERE f_id IS NULL
7. Display full details of students who are selected for football team
   SELECT id,name,class,mark,sex,f_id FROM `student`
   LEFT JOIN student_football on id=f_id
   WHERE f_id is not null
8. Display full details of students who are in baseball team
SELECT id, name, class, mark, sex, b_id FROM `student` LEFT JOIN student_baseball ON id = b_id WHERE b_id IS NOT NULL
9. Display full details of students who are not there in football or baseball team
   SELECT id,name,class,mark,sex,b_id,f_id FROM `student`
   LEFT JOIN student_baseball on id=b_id
   LEFT JOIN student_football on id=f_id
   WHERE b_id is null and f_id is null
10. Display full details of students who are there in any one team
    SELECT id,name,class,mark,sex,b_id,f_id FROM `student`
    LEFT JOIN student_baseball on id=b_id
    LEFT JOIN student_football on id=f_id
    WHERE b_id is not null or f_id is not null

1. List of products sold
2. List of quantity sold against each product.
3. List of quantity and total sales against each product
4. List of quantity sold against each product and against each store.
5. List of quantity sold against each Store with total turnover of the store.
6. List of products which are not sold
7. List of customers who have not purchased any product.

1. Average mark of all students in all exams
2. Maximum mark of all students in all exams
3. Maximum mark of each month for all students
4. Average mark of each month for all students
5. Consider mark of student for the exams conducted in later part of the month.