s****e 发帖数: 1180 | 1 借宝地问个面试中的sql的问题。
We have two tables, students (student ID, student name, student age, student
zip) and courses (course name, course time) .
The information in the parentheses are the schema.
We want to get how many students enroll all the classes starting at 11:00 AM?
the interviewer mentioned something like many to many join.
the interviewer mentioned something like "dimensionalize" in SQL. I am
wondering who knows any SQL book on this, and can introduce it me?
Thank you so much for your consideration! | d**e 发帖数: 6098 | 2 可能你是漏了一些条件吧?就纯粹靠这两个table来说,毫无关系,那么就根本找不到哪位
学生enroll了哪些课.
就比如另外有table student_course(student_id, course_name). 其实最好是course
最好是有个course_id,因为course_name可能会变.
其实这样已经是很简单的schema了,已经不需要再denormalization.
假如有上面这三个table,对你的问题来说应该是
select count(s.*)
from students s, courses c, student_course sc
where c.course_time = 11:00am
and c.course_name = sc.couse_name
and sc.student_id = s.student_id
不清楚什么书好,google一下应该也有简单教程.
student
AM?
【在 s****e 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 借宝地问个面试中的sql的问题。 : We have two tables, students (student ID, student name, student age, student : zip) and courses (course name, course time) . : The information in the parentheses are the schema. : We want to get how many students enroll all the classes starting at 11:00 AM? : the interviewer mentioned something like many to many join. : the interviewer mentioned something like "dimensionalize" in SQL. I am : wondering who knows any SQL book on this, and can introduce it me? : Thank you so much for your consideration!
| l*********8 发帖数: 4642 | 3 这个sql不对吧
course
【在 d**e 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 可能你是漏了一些条件吧?就纯粹靠这两个table来说,毫无关系,那么就根本找不到哪位 : 学生enroll了哪些课. : 就比如另外有table student_course(student_id, course_name). 其实最好是course : 最好是有个course_id,因为course_name可能会变. : 其实这样已经是很简单的schema了,已经不需要再denormalization. : 假如有上面这三个table,对你的问题来说应该是 : select count(s.*) : from students s, courses c, student_course sc : where c.course_time = 11:00am : and c.course_name = sc.couse_name
| l*********8 发帖数: 4642 | 4 我感觉, 题目要求的是: 注册了所有在十一点开始的课的学生的人数.
比如, 有三门课在十一点开始, 同时选了这三门课的学生人数,才是所求答案
【在 l*********8 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 这个sql不对吧 : : course
| d**e 发帖数: 6098 | 5 啊...你是对的.我没看清题目 :(
没有测试,但可能是这个?
不知道having能否接受变量,如果可以那用最后一个select count会简单一些.
才发现如果只找人数,students的表是多余的...
select count(*)
from (
select student_id, count(*) cnt
from student_course sc
where sc.course_id in (select course_id from course where c.course_time =
11:00am)
group by student_id
) where cnt = (select count(*) from course where c.course_time = 11:00am)
【在 l*********8 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 我感觉, 题目要求的是: 注册了所有在十一点开始的课的学生的人数. : 比如, 有三门课在十一点开始, 同时选了这三门课的学生人数,才是所求答案
| l*********8 发帖数: 4642 | 6 跟我想的差不多。
的确,students表是多余的啊。。。
我记得having可以跟sub-query的。
等我写个测测看。
=
【在 d**e 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 啊...你是对的.我没看清题目 :( : 没有测试,但可能是这个? : 不知道having能否接受变量,如果可以那用最后一个select count会简单一些. : 才发现如果只找人数,students的表是多余的... : select count(*) : from ( : select student_id, count(*) cnt : from student_course sc : where sc.course_id in (select course_id from course where c.course_time = : 11:00am)
| l*********8 发帖数: 4642 | 7 I just tried.
The SQL is almost like done's solution. But one sub-query is removed.
select count(*) from
(
select sc.student_id from courses c, students_courses sc
where c.start_time = '11:00:00'
and sc.course_id = c.id
group by sc.student_id
having count(sc.course_id) =
(select count(*) from courses where start_time = '11:00:00')
) t; | A***a 发帖数: 2211 | 8 missing at least one table.
you should have registration table (regID, studentID, course ID, )
this is a very basic sql joint. Be confident. Just tell the interviewer that
he/she did not provide enought info.
student
AM?
【在 s****e 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 借宝地问个面试中的sql的问题。 : We have two tables, students (student ID, student name, student age, student : zip) and courses (course name, course time) . : The information in the parentheses are the schema. : We want to get how many students enroll all the classes starting at 11:00 AM? : the interviewer mentioned something like many to many join. : the interviewer mentioned something like "dimensionalize" in SQL. I am : wondering who knows any SQL book on this, and can introduce it me? : Thank you so much for your consideration!
| l*********8 发帖数: 4642 | 9 我觉得考点之一,就是设计这个basic的multi-multi assoc table.
SQL算是中等难度吧。
that
【在 A***a 的大作中提到】![](/moin_static193/solenoid/img/up.png) : missing at least one table. : you should have registration table (regID, studentID, course ID, ) : this is a very basic sql joint. Be confident. Just tell the interviewer that : he/she did not provide enought info. : : student : AM?
| s****e 发帖数: 1180 | 10 http://www.mitbbs.com/article_t0/Database/31183043.html
确实,缺一个表,见如上链接,我想问的是把这三个表join起来的 sql codes该怎么写?
多谢!:)
course
【在 d**e 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 可能你是漏了一些条件吧?就纯粹靠这两个table来说,毫无关系,那么就根本找不到哪位 : 学生enroll了哪些课. : 就比如另外有table student_course(student_id, course_name). 其实最好是course : 最好是有个course_id,因为course_name可能会变. : 其实这样已经是很简单的schema了,已经不需要再denormalization. : 假如有上面这三个table,对你的问题来说应该是 : select count(s.*) : from students s, courses c, student_course sc : where c.course_time = 11:00am : and c.course_name = sc.couse_name
|
|