{%hackmd BJrTq20hE %}
# 5月13日 sql
Q1 :瀏覽所有類別加子類別


-- 只顯示出類別的
SELECT `category_name` FROM `category`

-- 顯示出類別的+對應之子類別
SELECT category_name,sub_category 
FROM category
LEFT JOIN  sub_category 
ON sub_category.category_id = category.category_id

Q2:瀏覽某一類別之所有類別之所有課程


--將每個類別之課程排出
SELECT `course_name`,course.course_id,`category_name`
FROM course
INNER JOIN course_category on course_category.course_id = course.course_id
LEFT JOIN category on category.category_id = course_category.category_id
order BY category.category_name


--選出類別為'電子商務'之所有課程
SELECT course.course_name,course.total_number_of_lecture,course.total_time_of_lecture,course.level,course.price,course.final_rating,course.review_count
FROM course
LEFT JOIN course_category ON course.course_id = course_category.course_id
LEFT JOIN category ON course_category.category_id = category.category_id
WHERE category.category_name = '電子商務'

Q3:課程資訊所有資訊


--跑出課程代碼為1的課程資訊(未加入will_learn)
SELECT * FROM course
LEFT JOIN course_included on course.course_id = course_included.course_id
WHERE course.course_id = 1

-- 跑出課程代碼為1的課程資訊(加入will_learn)
SELECT * FROM course
LEFT JOIN course_included on course.course_id = course_included.course_id
LEFT JOIN
(SELECT `will_learn`.`course_id`,
GROUP_CONCAT(`will_learn`.`will_learn`) as 'will_learn'
FROM `will_learn`
GROUP BY `will_learn`.`course_id`)b
ON course.course_id = b.course_id
LEFT JOIN
(SELECT `course_request`.course_id,
GROUP_CONCAT(`course_request`.request) as 'request'
FROM `course_request`
GROUP BY `course_request`.`course_id`)c
ON course.course_id = c.course_id
WHERE course.course_id = 1

-- 課程內容
SELECT course_content.content_name,lecture_content.lecture_name,lecture_content.time_of_lecture_name,lecture_content.預覽
FROM course_content
LEFT JOIN lecture_content ON course_content.course_content_id = lecture_content.course_content_id
WHERE lecture_content.course_id = 1

-- will_learn+request
SELECT b.will_learn,c.request,course.total_number_of_lecture,course.total_time_of_lecture
FROM course
LEFT JOIN course_included on course.course_id = course_included.course_id
LEFT JOIN
(SELECT `will_learn`.`course_id`,
GROUP_CONCAT(`will_learn`.`will_learn`) as 'will_learn'
FROM will_learn
GROUP BY `will_learn`.`course_id`)b
ON course.course_id = b.course_id
LEFT JOIN
(SELECT `course_request`.course_id,
GROUP_CONCAT(`course_request`.request) as 'request'
FROM course_request
GROUP BY `course_request`.`course_id`)c
ON course.course_id = c.course_id
WHERE course.course_id = 1

Q4:屬於我的課程的列表


-- 跑出學生編號1的所有課程，
SELECT `course`.course_name,`user_course`.`all_process(%)`,review.rating
FROM course
LEFT JOIN user_course ON `course`.`course_id` = `user_course`.`course_id`
LEFT JOIN review ON `user_course`.`course_id` = `review`.`course_id`
AND `user_course`.`student_id` = `review`.`student_id`
WHERE user_course.student_id = 1

Q5:我上過這門課的進度列表


-- 跑出課程代碼為1的目前進度 學生編號為1
SELECT course_content.content_name,lecture_content.lecture_name,lecture_content.time_of_lecture_name,user_course_looked.is_looked,user_course_looked.process
FROM user_course_looked
LEFT JOIN course ON user_course_looked.course_id = course.course_id
LEFT JOIN course_content ON user_course_looked.course_content_id = course_content.course_content_id
LEFT JOIN lecture_content ON user_course_looked.lecture_content_id = lecture_content.lecture_content_id
WHERE user_course_looked.student_id = 1 AND user_course_looked.course_id = 1

Q6:加到我的願望清單


-- 學生編號2的願望清單
SELECT course.course_name ,course.price
FROM wishlist_course
LEFT JOIN course ON wishlist_course.course_id = course.course_id
WHERE wishlist_course.student_id = 2