To establish the relationship between the following
? Departments information: department number, name of department, director of the institute department, address, the number of departments;
? Students basic information, department number, student id, name, gender, student type, admission date, birth place, id number, detailed address, date of birth, your major, national, phone (home), capture expends in total, the required rate, note;
? Dormitory information: the area code, store, room number, beds, rent (yuan/bed);
? Lodging information: student id, area code, store, room number, bed number, check-in date, note,

Query - the average monthly rent, if higher than 75 yuan, shows the average rent more than $75, or show 'average rent is less than 75 yuan (hint: average monthly rent=1 (dormitory beds &personnel x 1 per bed dormitory rent +... + n dorm beds by dormitory per bed rent n)/(dorm beds + 1... N + dormitory beds)),

Using any statement in your SQL2008 can do it

CodePudding user response:

The

refer to the original poster ss6441543 response:

to establish the relationship between the following
? Departments information: department number, name of department, director of the institute department, address, the number of departments;
? Students basic information, department number, student id, name, gender, student type, admission date, birth place, id number, detailed address, date of birth, your major, national, phone (home), capture expends in total, the required rate, note;
? Dormitory information: the area code, store, room number, beds, rent (yuan/bed);
? Lodging information: student id, area code, store, room number, bed number, check-in date, note,

Query - the average monthly rent, if higher than 75 yuan, shows the average rent more than $75, or show 'average rent is less than 75 yuan (hint: average monthly rent=1 (dormitory beds &personnel x 1 per bed dormitory rent +... + n dorm beds by dormitory per bed rent n)/(dorm beds + 1... N + dormitory beds)),

With what statement can make come in SQL2008

A SQL directly write not to come out, need to use the process to handle to an intermediate table, and then from the table to remove

Or at the front desk to use the data window, more convenient

CodePudding user response:

Can you help me to write it. I'm completely not ah

CodePudding user response:

- create table room (RNO nvarchar (10), Bed int, Money a decimal)
- insert into room values (' a ', 8100)
- insert into room values (' b ', 4 reached)
- insert into room values (' c ', 8 ')

WITH t1 as (select sum (bed * money)/sum (bed) as AVGMONEY from room)
The SELECT RNO, BED, MONEY, AVGMONEY, CASE WHEN MONEY & gt; AVGMONEY THEN 'is greater than the average rent' ELSE 'is less than the average rent END as the rent is
The FROM ROOM, t1
The ORDER BY RNO

RNO BED MONEY AVGMONEY rent situation
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
A 8 100 80.000000 is greater than the average rent
B 4 80 80.000000 less than the average rent
C 8 60 80.000000 less than the average rent

(3) affected