Little help needed with SQL

[thenameDS]

Hey guys, so I'm pretty stumped on two queries I have to create just can't figure out how to do them for the life of me!

This is pretty basic SQL for someone that knows what they are doing, but I'm new

These are the tables I have:

Spoiler

Now I need help with:

Spoiler

List the rooms which have no activities scheduled in them on a Monday(day = 1).

And

Spoiler

List the number of activities that take place in rooms which have a Whiteboard in them.

 

[tom_e]

If no one's answered before tomorrow morning I'll give you a hand when I get to the office because I'm lazy and can't be arsed typing it out on my phone.

 

[thenameDS]

Cheers that's much appreciated, I'm in no rush anyways.

 

[kkelly]

In Oracle something like this (Not got a db installed so might be slighty off)

Post your build scripts and il do it properly if this is rubbish

Select to_char(day, "DDD"), roomid
from tblActivity
where to_char(day, "DDD") != "MON"
group by to_char(day, "DDD"), roomid
order by 1;


Select a.roomid, count(*)
from tblActivity a, tblRoomRquipment b
where a.roomid = b.roomid
and b.EquipmentType = 'Whiteboard'
group by a.roomid
order by 2,1

 

[planty]

Select to_char(day, "DDD"), roomid
from tblActivity
where to_char(day, "DDD") != "MON"
group by to_char(day, "DDD"), roomid
order by 1;

This doesn't take into account any rooms that are not referenced in the activity table. The question doesn't specify whether that is relevant tho, so meh!

 

[thenameDS]

In Oracle something like this (Not got a db installed so might be slighty off)

Post your build scripts and il do it properly if this is rubbish

Select to_char(day, "DDD"), roomid
from tblActivity
where to_char(day, "DDD") != "MON"
group by to_char(day, "DDD"), roomid
order by 1;


Select a.roomid, count(*)
from tblActivity a, tblRoomRquipment b
where a.roomid = b.roomid
and b.EquipmentType = 'Whiteboard'
group by a.roomid
order by 2,1

Will have a go tomorrow morning, haven't got MS SQL on my mac. It looks good though, my attempts were lacking some stuff you have so might just work

 

[kkelly]

Do you have build scripts, ie create table xyz I can do it properly tomorrow.

 

[thenameDS]

Sure, hope this is what you're looking for:

Spoiler

CREATE TABLE tblRoomType(
Type NVARCHAR(2) PRIMARY KEY,
Description NVARCHAR(20),
Responsibility NVARCHAR(20)
)

CREATE TABLE tblRoom(
ID NVARCHAR(8) PRIMARY KEY,
RoomType NVARCHAR(2),
Capacity NUMERIC(3),
CONSTRAINT assess_FkRoomType FOREIGN KEY (RoomType) REFERENCES tblRoomType
)

CREATE TABLE tblEquipment(
Type NVARCHAR(1) PRIMARY KEY,
Description NVARCHAR(10)
)

CREATE TABLE tblRoomEquipment(
RoomID NVARCHAR(8),
EquipmentType NVARCHAR(1),
CONSTRAINT assess_fkRoomID FOREIGN KEY (RoomID) REFERENCES tblRoom,
CONSTRAINT assess_fkEquipmentType FOREIGN KEY (EquipmentType) REFERENCES tblEquipment
)

CREATE TABLE tblModule(
ID NVARCHAR(6) PRIMARY KEY,
Title NVARCHAR(60)
)

CREATE TABLE tblActivity(
ID NVARCHAR(12) PRIMARY KEY,
Name NVARCHAR(12),
Type NVARCHAR(3),
ModuleID NVARCHAR(6),
Day NUMERIC(1),
Time NUMERIC(2),
RoomID NVARCHAR(8),
CONSTRAINT fk_Module FOREIGN KEY (ModuleID) REFERENCES tblModule(ID),
CONSTRAINT fk_Room FOREIGN KEY (RoomID) REFERENCES tblRoom(ID)
)

 

[SimonCHere]

Not really difficult, you just need all the rooms where the room id isn't in tblActivity with a day of 1. You don't even need any date conversions.

select ID
from tblRoom
where ID not in (select RoomID from tblActivity where day = 1)

Oh and your table diagram has a wrong link between tblRoomEquipment and tblActivity, the one you have got should be pointing to tblRoom. Actually it should have both!

 

[SimonCHere]

For the other question. Link tblActivity to tblRoomEquipment.
Find out the Equipment type for whiteboard as I hate using text descriptions in queries which by definition are there to be changed.
Then count() the activities where the room has that equipment.

 

[SimonCHere]

Select to_char(day, "DDD"), roomid
from tblActivity
where to_char(day, "DDD") != "MON"
group by to_char(day, "DDD"), roomid
order by 1;

to_char() can't be used like this as "day" is not a date field and "DDD" is the number of days since the beginning of the year, if it was a date field you could use "DY" but there is no point as we know Monday is defined as 1.

This will quite happily show you one room that has an activity on Monday, and Tuesday, but will only list the Tuesday activity. Not what you want.

 

[kkelly]

SELECT a.id, count(*)
FROM `tblRoom` a, `tblactivity` b
WHERE a.id = b.roomid
and b.Day != 1
Group by a.id
order by 1


results :-

id count(*)
ROOM1 4
ROOM10 8
ROOM2 4
ROOM3 5
ROOM4 12
ROOM5 13
ROOM6 15
ROOM7 9
ROOM8 7
ROOM9 8

Test it's correct :-

update `tblactivity` set Day = '1'
where roomid = 'Room1';

SELECT a.id, count(*)
FROM `tblRoom` a, `tblactivity` b
WHERE a.id = b.roomid
and b.Day != 1
Group by a.id
order by 1

results :-

id count(*)
ROOM10 8
ROOM2 4
ROOM3 5
ROOM4 12
ROOM5 13
ROOM6 15
ROOM7 9
ROOM8 7
ROOM9 8

(room 1 is gone)


@SimonCHere, I done it without a computer in front on me , also his Day is decimal not datetime.

 

[SimonCHere]

How about if room 10 didn't have any activities yet?
It's still free on Mondays.

How about your update statement reading:

update `tblactivity` set Day = '1'
where roomid = 'Room1'
and id = 1

instead as what you have done is update all the activities for room1 to happen on Monday.

edit, And it think I said that "day" wasn't a date field?

 

[thenameDS]

select ID
from tblRoom
where ID not in (select RoomID from tblActivity where day = 1)

That worked great for the first one, thanks!!