CODEWITHSUNDEEP
sqldatabasems-access
Sam kh
Sam kh

Reputation: 127

trouble understand basic sql statements

Im stuck on a sql statements and need some help. Im new to database and access 2007 so i dont know all the syntax perfectly, any guidance would be great thanks

Some info

Teacher = [CourseN, Quarter,TeacherName]
Course = [CourseN,CourseName, Nunit)
Section = [CourseN, Quarter , DayTime,  RoomN]/ Examples of DayTime: M2:00AM, 
W4:50PM, and T8:00PM. Note that DayTime is represented as a string.
Student = [studentName, CourseN, Quarter]

The question

13. List the CourseN and Quarter of every course taught by two 
different instructors in the same quarter ordered by the CourseN in descending order.

The Teacher Table

CourseN Quarter TeacherName
1   Winter 2011 Karen Reed
1   Winter 2011 Sun     <--- cant add this one which i need to make it   work
2   Spring 2005 Salloum
3   Spring 2005 Karen Reed
4   Spring 2005 Salloum
5   Winter 2011 Sun

The thing about the second one is it wont let me put duplicate courseN in my database table because its either a index, primary key, or relationship so I dont know what to do since i cant input it in my table

Edit: I deleted the first problem since i solved, so there isnt to much in the post

Upvotes: 0

Views: 207

Answers (1)

Y.S
Y.S

Reputation: 1862

I think you need something like this.

For the first query you will need to Join the tables "Course" and "Section" by the course number, which is the key. Now, I don't think you need to do any string manipulations on the dayTime, since all you care is the number of records the aggregation will hold.

As for the second query, you will need to Join tables "Course" and "Teacher", again by course Number.

Both queries should use Group By and Having clauses, since you need aggregation in both which is conditioned in the number of teachers / Weekly meetings.

Hope this helps.

drop table #Teacher
SELECT  *
into #Teacher
FROM    
(
    select 1 as CourseN, 1 as Quarter, 'Dan' as TeacherName
        union
    select 1 as CourseN, 1 as Quarter, 'Alex' as TeacherName
        union
    select 2 as CourseN, 1 as Quarter, 'Rob' as TeacherName
        union
    select 3 as CourseN, 2 as Quarter, 'Jim' as TeacherName
        union
    select 4 as CourseN, 3 as Quarter, 'Bob' as TeacherName
        union
    select 4 as CourseN, 3 as Quarter, 'Alice' as TeacherName
) a

drop table #Course
SELECT  *
into #Course
FROM    
(
select 1 as CourseN, 'English' as CourseName, 1 as Nunit
    UNION
select 2 as CourseN, 'Algebra' as CourseName, 1 as Nunit
    UNION
select 3 as CourseN, 'Math' as CourseName, 1 as Nunit
    UNION
select 4 as CourseN, 'Science' as CourseName, 1 as Nunit
) a


drop table #Section
SELECT  *
into #Section
FROM    
(
    select 1 as CourseN, 1 as Quarter, 'M2:00AM' as DayTime , 1 as RoomN
        UNION
    select 1 as CourseN, 1 as Quarter, 'W2:00AM' as DayTime , 1 as RoomN
        UNION
    select 2 as CourseN, 1 as Quarter, 'W2:00AM' as DayTime , 1 as RoomN
        UNION
    select 3 as CourseN, 2 as Quarter, 'W2:00AM' as DayTime , 1 as RoomN
        UNION
    select 3 as CourseN, 2 as Quarter, 'T2:00AM' as DayTime , 1 as RoomN
        UNION
    select 4 as CourseN, 3 as Quarter, 'T2:00AM' as DayTime , 1 as RoomN
) a


--List the CourseN, CourseName, and Quarter which meets or met at least  two times a week.
select a.CourseN , a.CourseName , b.Quarter --, COUNT(b.CourseN) NumOfWeeklyMeetings
from #Course a
    inner join #Section b
        on a.CourseN = b.CourseN
Where 1=1
GROUP BY a.CourseN , a.CourseName , b.Quarter
having COUNT(b.CourseN) > 1


--List the CourseN and Quarter of every course taught by two 
--different instructors in the same quarter ordered by the CourseN in descending order.
SELECT  a.CourseN , b.Quarter --, count(b.TeacherName) NumOfTeachers
FROM    #Course a
    inner join #Teacher b 
        on a.CourseN = b.CourseN
WHERE 1=1
group by a.CourseN , b.Quarter
having count(b.TeacherName) > 1

Upvotes: 2

Related Questions