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 的大作中提到】
: 借宝地问个面试中的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 的大作中提到】
: 可能你是漏了一些条件吧?就纯粹靠这两个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 的大作中提到】
: 这个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 的大作中提到】
: 我感觉, 题目要求的是: 注册了所有在十一点开始的课的学生的人数.
: 比如, 有三门课在十一点开始, 同时选了这三门课的学生人数,才是所求答案
| l*********8
发帖数: 4642 | 6 跟我想的差不多。
的确，students表是多余的啊。。。
我记得having可以跟sub-query的。
等我写个测测看。
=
【在 d**e 的大作中提到】
: 啊...你是对的.我没看清题目 :(
: 没有测试,但可能是这个?
: 不知道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 的大作中提到】
: 借宝地问个面试中的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 的大作中提到】
: 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 的大作中提到】
: 可能你是漏了一些条件吧?就纯粹靠这两个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
|
|
