SQL QUERY FOR AGGRIGATION - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL QUERY FOR AGGRIGATION

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-11-08 : 08:10:21

ANOOB writes "A LIST OF SERVICE TRANSACTIONS(WITH A FULL DESCRIPTION OF THE SERVICE) ON A PARTICULAR DAY ALONG WITH THE NAMES OF THE CUSTOMER AND THEIR ROOM NUMBERS.THE TOTAL AMOUNT TRANSACTED
NEEDS TO BE DIDPLYED AT THE BOTTOM OF THE LIST
TABLES ARE
CREATE TABLE ROOM_TYPE
(RTYPE VARCHAR(5)NOT NULL CONSTRAINT PK_RTYPE PRIMARY KEY(RTYPE),
RDESC VARCHAR(100),
RCHARGE MONEY)

CREATE TABLE ROOM
(RNO INT NOT NULL CONSTRAINT PK_ROOMNO PRIMARY KEY(RNO),
R_TYPE VARCHAR(10) DEFAULT(DX) CONSTRAINT FK_RTYPE REFERENCES ROOM_TYPE(RTYPE),
RSTATUS VARCHAR(5) DEFAULT(VC))

CREATE TABLE RESERVATION
(CUSTID INT NOT NULL CONSTRAINT PK_CUSTID PRIMARY KEY(CUSTID),
CNAME VARCHAR(10),
CADDR VARCHAR(50),
CITY VARCHAR(10),
STATE VARCHAR(10),
PHONE VARCHAR(15),
ZIP VARCHAR(10),
INDATE DATETIME DEFAULT GETDATE(),
OUTDATE DATETIME,
R_NO INT CONSTRAINT FK_RNO REFERENCES ROOM(RNO),
SERCHAR MONEY CONSTRAINT CH_SRCHAR CHECK(SRCHAR>0),
RCHAR MONEY CONSTRAINT CH_RCHAR CHECK(RCHAR>119),
TOTCHAR MONEY)

CREATE TABLE SERVICE
(SERNO INT NOT NULL CONSTRAINT PK_SERNO PRIMARY KEY(SERNO),
SERDESC VARCHAR(200),
SERVICECHAR SMALLMONEY CONSTRAINT CH_SERVICECHARGE CHECK(SERVICECHARGE BETWEEN 8 AND 15))

CREATE TABLE TRANSACTION
(TRANID INT NOT NULL CONSTRAINT PK_TRANID PRIMARY KEY(TRANID),
CUST_ID INT CONSTRAINT FK_CUSTID REFERENCES RESERVATION(CUSTID),
SER_NO INT CONSTRAINT FK_SERNO REFERENCES SERVICE(SERNO),
SCHAR MONEY,
TRANDATE DATETIME)"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 08:17:25

Use INNER JOIN to bind the different tables together.
Use SERNO and CUSTID as binding columns between most tables.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-08 : 08:45:54

And please do me a favor and post only necessary text in UPPER case, not all, next time. Help us help you.

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources