SQL语句实例

insert into t_student
values
('g0911','李超','网路传媒',default,'1990-3-4','党员')
--(1) 列出T_score表中c001或c002两科成绩中大于60分的同学
select * from T_score where (c_number='c001' or c_number='c002')
and score>60
--(2) 查询成绩高于90分并且是党员的男学生的信息。
select * from t_student,t_score where t_student.s_id=t_score.s_number and polity='党员' and sex='女' and score>90
--(3) 从表T_score中查询各科成绩在80~90分的学生
select * from t_score where score>=80 and score<=90 --score between 80 and 90
--(4) 从表T_student表中查询1981~1984年出生的同学
select * from t_student where year(birthday)>=1981 and year(birthday)<=1984 --year(birthday) between 1981 and 1984
--(5) 在T_student表中找到姓“张X”的同学
select * from t_student where s_name like '张%'
--(6) 在T_student表中找到所有姓名中带红字的同学
select * from t_student where s_name like '%红'
--(7) 按出生日期先后显示T_student表
select * from t_student order by birthday desc　-- asc
--(8) 按年龄由大到小显示T_student表
select * from t_student order by year(getdate())-year(birthday) asc
--(9) 将T_score表按课程名和成绩排序
select * from t_score order by c_number,score desc
--(10) 将T_student表按性别和出生日期排序（性别相同按出生日期先后顺序显示）
select * from t_student order by sex,birthday
--(11) 显示tblStudent表一共有多少条数据
select count(*) from t_student
--(12) 显示tblStudent表共有多少1984年出生的同学
select count(*) from t_student where year(birthday)=1984
--(13) 查询T_student所有记录的sex字段，并去掉重复值
select distinct sex from t_student
--(14) 查询T_student所有记录的polity字段，并去掉重复值
select distinct polity from t_student