MySQL: Solution Practical 2
Bài tập Practical 2: https://v1study.com/sql-practical-2-a484.html
SOLUTION
#a. SELECT CONVERT(mark,decimal(4,2)) FROM studenttest
#b. SELECT*FROM student WHERE age>25
#c. SELECT*FROM student WHERE age in(20,30)
#d. SELECT*FROM test WHERE name LIKE'%s%'
#e. SELECT*FROM studenttest WHERE mark>5
#f. SELECT*FROM student WHERE name LIKE'% ____'
#g. SELECT*FROM student WHERE name LIKE'______ %'
#h. SELECT*FROM student WHERE name REGEXP '^[^r]{6} '
#i. ALTER TABLE student ADD status varchar(10) DEFAULT 'Young'
# Extra:
#1. SELECT AVG(age) FROM student
#2. SELECT*FROM student WHERE age=(SELECT MAX(age) FROM student)
#3. SELECT*FROM student WHERE age=(SELECT MIN(age) FROM student)
#4. SELECT name,mark FROM test a JOIN studenttest b ON a.testid=b.testid WHERE mark=(SELECT MAX(mark) FROM studenttest)
#5. SELECT name,mark FROM test a JOIN studenttest b ON a.testid=b.testid WHERE mark=(SELECT MIN(mark) FROM studenttest)
#6. SELECT name,date FROM student a JOIN studenttest b ON a.rn=b.rn WHERE date=(SELECT MAX(date) FROM studenttest)
#7. SELECT name,date FROM student a JOIN studenttest b ON a.rn=b.rn WHERE date=(SELECT Min(date) FROM studenttest)
#8. SELECT SUM(age) FROM student
#9. SELECT name, datediff(now(),date) as 'Số ngày đã thi' FROM test a JOIN studenttest b on a.testid=b.testid
#10. SELECT*FROM student a JOIN studenttest b ON a.rn=b.rn WHERE mark=(SELECT MAX(mark) FROM studenttest)
#11. SELECT*FROM student a JOIN studenttest b ON a.rn=b.rn WHERE mark=(SELECT MIN(mark) FROM studenttest)
#12. SELECT name,CONVERT(AVG(mark),decimal(4,2)) FROM student a JOIN studenttest b ON a.rn=b.rn GROUP BY a.rn,name ORDER BY AVG(mark) DESC
#13. SELECT a.name as 'Tên học viên',c.name as 'Tên môn học' FROM student a JOIN studenttest b ON a.rn=b.rn JOIN test c ON b.testid=c.testid
#14. SELECT*FROM student WHERE rn NOT IN(SELECT rn FROM studenttest)
#15. SELECT a.name as 'Tên học viên',c.name as 'Tên môn học',mark FROM student a JOIN studenttest b ON a.rn=b.rn JOIN test c ON b.testid=c.testid WHERE mark<5
#16. SELECT name,CONVERT(AVG(mark),decimal(4,2)) FROM student a JOIN studenttest b ON a.rn=b.rn GROUP BY a.rn,name HAVING AVG(mark)=(SELECT AVG(mark) FROM studenttest GROUP BY rn ORDER BY AVG(mark) DESC LIMIT 0,1)
#17. SELECT name,CONVERT(AVG(mark),decimal(4,2)) FROM student a JOIN studenttest b ON a.rn=b.rn GROUP BY a.rn,name HAVING AVG(mark)=(SELECT AVG(mark) FROM studenttest GROUP BY rn ORDER BY AVG(mark) ASC LIMIT 0,1)
#18. SELECT name,MAX(mark) as 'Điểm thi cao nhất' FROM test a JOIN studenttest b ON a.testid=b.testid GROUP BY a.testid,name
#19. SELECT a.name as 'Tên học viên',c.name as 'Tên môn học' FROM student a LEFT JOIN studenttest b ON a.rn=b.rn LEFT JOIN test c ON b.testid=c.testid