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