1.选取表中所有的列：查所有学生的所有信息

select * from student

2.选取表中所指定的列：查询学生表中所有学生的学号、姓名、入学成绩

select sno,sname,sscore

from student

3.选取表中所指定的列和指定的行：查询学生表中‘’班的学生的学号、姓名、入学成绩

use student

select sno,sname,sscore,classno

from student

where classno='07010111'

4.查询学生表‘’班的男生信息

select * from student

where classno='07010111' and ssex='男'

5.查询出生日期在年到年间的教师信息

use student

select * from teacher

where tbirthday>='1975-1-1' and tbirthday<='1980-12-31'

6.查询职称为副教授的女教师信息

use student

select * from teacher

where tsex='女' and title='副教授'

7.查询年龄超过的职称为副教授的女教师信息

use student

select * from teacher

where (title='副教授' and (year(getdate())-year(tbirthday))>=40 and tsex='女')

8.查询选修了‘C001’课程的前位学生的选课信息

use student

select top 3 * from choice

where cno='c001'

9.查询学生表‘’班和‘’班的学生信息

use student

select * from student

where classno in ('07010111','07010111')

10.查询全体学生信息，查询结果按班级编号降序排列，同一个班级的学生按学号升序排列

select * from student

order by classno desc,sno asc

11.查询学生表中姓陈的学生信息

select * from student

where sname like '陈%'

12.查询学生表中姓陈的学生信息，查询结果按出生日期降序排序

use student

select * from student

where sname like '陈%'

order by sbirthday desc

2.查询orders表格中的订购日期，并且相同的订购日期只显示一次。

use exam

select distinct orderdate

from orders

3.查询供应商代码为HHT的供应商实际名称。

select * from suppliers

where suppcode='HHT'

4.查询供应商代码不为HHT的供应商实际名称。

select * from suppliers

where suppcode!='HHT'

查询的products表格中所有单价不超过元的产品的名称及其对应的价格。

select * from products

where unitprice<='20.00'

6.查询经销商号码为、、、和的经理名称以及其所在的公司名称

select Bossname,Company from distributors

where DistriNum in ('1006','1009','1013','1014','1017')

查询所有经理姓“王”的公司的名称、电话以及公司地址。

select Company,Phone,Address from distributors

where BossName like '王%'

查询orders表和distributors表所有的列的内容

select * from orders ,distributors

查询orders表格中的所有记录，查询结果要根据OrderNum由小至大的顺序进行排序

select *from orders

order by OrderNum asc

统计学生入学成绩的平均值

select AVG (sscore) as 平均值 from student

统计在校的学生人数

select COUNT(ssex)总人数 from student

统计在校的教师人数

select COUNT(*) 教师人数 from student

实现求出字符A的ASCII码

select ascii('A')

求出字符串“我要一步一步往上爬，在最高点乘着叶片往前飞”的长度

select LEN('我要一步一步往上爬，在最高点乘着叶片往前飞')as 字符串长度

实现将字符串“ abcdefg”前的空格去掉

select ltrim(' abcdefg')

实现将字符串“uvwxyz ”后的空格去掉

select rtrim('uvwxyz ')

计算出-2.5的绝对值

select abs(-2.5)

返回下面三个数的平方值、-6、.6

select square(4),square(-6),square(6.6)

求出学生入学成绩的最高分和最低分

select MAX(sscore)as 最高分,MIN(sscore)as 最低分 from student

查出所有姓陈的出生年月日

select sname as 姓名, ltrim(str(year(sbirthday)))+ '年'

+LTRIM(STR(MONTH(sbirthday)))+'月'

+LTRIM(STR(day(sbirthday)))+'日'

as 出生年月日 from student

where sname like'陈%'

5. 将字符串“I LOVE SQL SERVER”所有字母转换成小写字母

6. 将字符串“abcdefg”中小写字母转换成大写字母。

7. 将字符串“i love Microsoft”中小写字母转换成大写。

8. 实现将字符串“ abcdefg”前的空格去掉

9. 实现将字符串“uvwxyz ”后的空格去掉

10. 将字符串“我喜欢数据库”中的字符由左至右反转排列

select lower('I LOVE SQL SERVER')

select upper('abcdefg')

select ltrim(' abcdefg')

select rtrim('uvwxyz ')

select reverse('我喜欢数据库')

11. 实现求出字符A的ASCII码。

12. 实现求出字符m的ASCII码

13. 实现求出字符6的ASCII码。

14. 实现求出字符 + 的ASCII码

select ascii('A')

select ascii('m')

select ascii('6')

select ascii('+')

1. 计算出以下三个数的绝对值：-2.5，0.0，7.4

2. 返回分别大于或等于以下三个数字的最小整数：5.5，-7.2，0.0

3. 返回分别小于或等于以下三个数字的最小整数：5.8，-9.9，0.0

select abs(-2.5),abs(0.0),abs(7.4)

select ceiling(5.5),ceiling(-7.2),ceiling(0.0)

select floor(5.8),floor(-9.9),floor(0.0)

4. 返回π（PI）的值

5. 返回630.67的近似值，并将该数分别四舍五入到1、-2、-3、0长度的小数。

select pi()

select round(630.67,1),round(630.67,-2),round(630.67,-3),round(630.67,0)

select round(1024.33,1),round(1024.33,-1),round(1024.33,-2),round(1024.33,0)

6. 返回下面三个数的平方值4、-6、6.6。

7. 返回下面三个数的算数平方根4、0、50。

8. 计算Cos（3.1415926）的值

9. 计算Sin（3.1415926）的值。

10. 计算Ctg（3.14）的值

11. 计算Tg（3.1415926）的值

12. 计算2的自然对数。

select square(3),square(-6),square(6.6)

select sqrt(4),sqrt(0),sqrt(50)

select Cos(3.1415926)

select Sin(3.1415926)

select log(2)

13. 计算1000的以10为底的对数。

14. 计算2的10次方。

15. 计算1.25的-2次方。

16. 判断数字5的正负号，如果是正的返回1，如果是负的返回-1，如果是0则返回0。

select log10(1000)

select power(2,10)

select power(1.25,-2)

select sign(5)

统计各类职称的教师人数

select title as 职称,COUNT(*) as 人数

from teacher

group by title

查询班级编号和班级人数

select classno as 班级编号,count(*) as 班级人数

from student

group by classno

统计各课程的最高分、最低分和平均分（课程以课程号显示）。

select cno as 课程号,MAX(grade) as 最高分,MIN(grade) as 最低分,avg(grade) as 平均分

from choice

group by cno

统计各班级学生人数，班级以及班级名显示

select classname as 班级名称,COUNT(*) as 班级人数

from student inner join class

on student.classno=class.classno

group by classname

统计各班级学生人数，班级以及班级名显示,(班级没人耶显示为)

select classname as 班级名称,COUNT(sno) as 班级人数

from student full join class

on student.classno=class.classno

group by classname

根据需求将显示班级编号、班级名、班级人数都显示出来,怎么办？（学生作为拓展任务进行

select class.classno as 班级号, classname as 班级名称,COUNT(*) as 班级人数

from class inner join student

on student.classno=class.classno

group by classname,class.classno

任务5

select cname as 课程名称,MAX(grade) as 最高分,MIN(grade) as 最低分,avg(grade) as 平均分

from course inner join choice

on course.cno=choice.cno

group by cname

查询张丹同学的所有选修课的成绩

select sname as 张丹, cno as 姓名,grade as 成绩

from student inner join choice

on student.sno=choice.sno

where sname='张丹'

查询选修c001这门课程的学生的成绩

select sname as 姓名, cno as 课程, grade as 成绩

from student inner join choice

on student.sno=choice.sno

where cno='c001'

查询选修体育课程的学生的成绩

select sname as 姓名,cname as 课程, grade as 成绩

from student inner join choice

on student.sno=choice.sno

inner join course

on choice.cno=course.cno

where cname='体育'

查询选学“C001”课程的成绩高于“”号学生成绩的所有学生记录。

select * from choice

where grade>(select grade from choice where

sno='0901010103'and cno='c001') and cno='c001'

查询所有部门的员工情况

select *

from teacher right join department

on department.deptno=teacher.deptno

查询所有员工（教师）的所属部门情况

select * from teacher left join department

on department.deptno=teacher.deptno

查询出学生表中所有学生的选修课情况

select sname as 姓名,cname as 课程, grade as 成绩

from student left join choice

on student.sno=choice.sno

left join course

on choice.cno=course.cno

查询出学生表中选修过课程的学生的选修课情况

select sname as 姓名,cname as 课程, grade as 成绩

from student inner join choice

on student.sno=choice.sno

inner join course

on choice.cno=course.cno

insert into teacher values('11020001','01','肖海','3408','数据结构','45')

insert into teacher values('11020002','02','王岩盐','4390','数据结构','36')

insert into teacher values('11020003','01','刘星魂','2450','C语言','43')

insert into teacher values('11020004','03','钱青名','2987','数据库','37')

insert into teacher values('11020005','04','呼延军','3220','编译原理','29')

insert into teacher values('11020006','04','欧阳秀','3345','编译原理','55')

insert into department values('01','计算机系')

insert into department values('02','通信系')

insert into department values('03','信息管理系')

insert into department values('04','数学系')

查询教授“编译原理”这门课的教师人数

select COUNT(*) as 总人数 from teacher

where course='编译原理'

查询比“欧阳秀”工资高的教师的姓名

select * from teacher

where salary>(select salary from teacher where salary='3345')

查各个系教师的人数

select Dept_name as 系部名称,COUNT(*) as 教师人数

from department

group by Dept_name

查询教师-49这个年龄组的教师人数

select count(*) as 教师人数

from teacher

where age>=40 and age<=49

查询计算机系的所有教师信息

select * from teacher

where Dept_no='01'

用子查询

查询和“孙晓龙”在同一班级的学生信息

select * from student

where classno in (select classno from student where sname='孙晓龙')

查询和“郭玉霄”在同一部门的教师信息

select * from teacher

where deptno in(select teacher.deptno from teacher where tname='郭玉霄')

查询“软件与服务外包学院”所设置的班级情况

select *from class where pno in

(select pno from professional where deptno in(select deptno from department where deptname='软件与服务外包学院'))

查询“软件技术”专业学生的学号和名字

select sno,sname from student where classno in

(select classno from class where pno =

(select pno from professional where pname='软件技术'))

查询“软件技术”专业09级学生的学号和名字

select sno,sname from student where sno like'09%' and classno in

(select classno from class where pno =

(select pno from professional where pname='软件技术'))

查询非“软件技术”专业学生的学号和名字

select sno,sname from student where classno in

(select classno from class where pno in

(select pno from professional where pname !='软件技术'))