sql 数据库习题谢谢

1. select '类别为：'+tushuleibie 图书分类 from T_Book

2. select shuming 书名,zuozhe 作者,jiage*0.7 价格 from T_Book where chubanshe ='机械工业出版社'

3. select shuming 书名,zuozhe 作者,jiage 价格, chubanshe 出版社 from T_Book where jiage between 30 and 60

4. select top 3 shuming 书名,zuozhe 作者,chubanshe 出版社, jiage 价格 from T_Book order by jiage desc

6.select chubanshe 出版社,AVG(jaige) 平均价,MAX(jiage) 最高价 ,MIN(jaige) 最低价 from T_Book group by chubanshe order by SUM(jiage) desc

10.select top 1 chubanshe 出版社, count(*) 出版图书个数 from T_Book group by chubanshe order by COUNT(*) desc

book表的做好了 reader的自己做吧 字段名我用拼音做代替的 你自己替换成你表中的字段

1. 查询雇员（employee）的姓和名

Select substring(username,1,1) as 姓 from employee

Select substring(username,2,2) as 名 from employee

2. 查询雇员的姓名

Select username from employee

3. 查询雇员数

Select count(*) from employee

4. 查询雇员的姓名和职务

Select username,,duty from employee

5. 查询雇员的工龄

Select year(getdate())-开始工作日期 as 工龄 from employee

任务2：条件查询

1. 查询雇员（employee）从事"Sales Representative"职务的有哪些人

Select * from employee where duty=’ Sales Representative’

2. 查询工龄超过15年的雇员

Select * from employee where cast( (year(getdate())-开始工作日期) as int)>=15

3. 查询姓以a开头的雇员

Select * from employee where username like ‘a%’

4. 查询姓的开头字母在m以后的雇员

Select * from employee where cast((substring(username,1,1) as varchar)>=’m’

5. 认为hire_date是雇员生日，查询巨蟹座的雇员

Select * from employee where birthday between ‘6-22 ‘ and ‘7-22’

任务3：联合查询

1. 查询雇员和雇员职位

Select a.id,b.duty from employee, as a,jobs as b

2. 查询雇员、雇员职位和雇员所在出版社

Select a.id,b.duty, b.publishing from employee as a,jobs as b on a.id=b.id

3. 查询雇员、雇员工资、雇员离本职位最高工资的差值

select a. ID,a.username,a.[雇员工资],b.[最高工资]-a.[雇员工资] as [差值] from employee a,jobs b where a.[职位]=b.[职位]

(1) 试说明R不是2NF模式的理由。

存在部分依赖，所以R不是2NF

 (2) 试把R分解成2NF模式集。

   R1(ABC),R2(AD)  说明：消除部分依赖关系

2.(1)R存在传递依赖。

（2）R1(CB),R2(BA)  说明：消除传递依赖关系

---