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.

 

[thenameDS]

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

 

[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

 

[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)
)

 

[thenameDS]

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

That worked great for the first one, thanks!!