SQL_学生
第二部分
1.所有男生的姓名、年龄:
Select Sname，Sage
from student;
2.所有年龄大于20，计算机科学系学生名单:
SELECT Sname
from Student
WHERE Sage>20 AND Sdept='CS';
3.成绩大于60的学生学号:
SELECT Sno
from SC
WHERE Grade>60;
4.成绩在70到80之间的学生学号:
SELECT Sno
from SC
WHERE Grade>70 AND Grade<80;
SELECT Sno 学号
from SC
WHERE Grade BETWEEN 70 AND 80;
5.1985到1989年出生的学生姓名:
SELECT Sname 姓名
from Student
WHERE 2009-Sage BETWEEN 1985 AND 1989;
SELECT Sname
from Student
WHERE 2009-Sage>1985 AND 2009-Sage<1989;
6.查询成绩不在70到85的及格学生名单:
SELECT DISTINCT Sname
from Student,SC
WHERE Student.Sno=SC.Sno AND Grade NOT BETWEEN 70 AND 80 AND Grade>60;
7.查询成绩是70、80、90的学生学号:
SELECT Sno
from SC
WHERE Grade IN(70,80,90);
8.查询1985、1989年的学生学号和姓名:
SELECT Sno,Sname
from Student
WHERE 2009-Sage IN('1985','1989');
9.查询学分不是3、4、5的课号:
SELECT Cno
from Course
WHERE Ccredit NOT IN(3,4,5);
10.查询2002级所有学生情况:
SELECT *
from Student
WHERE Sno LIKE '2002%';
SELECT *
from Student
WHERE Sno='2002%';
11.查询姓刘'CS'系的姓名和性别:
SELECT Sname,Ssex
from Student
WHERE Sdept='CS' AND Sname LIKE '刘%';
12.查询姓刘、张、李的学生名单:
SELECT Sname
from Student
WHERE Sname LIKE '[刘张李]%';
13.查询课程名含有数据的课程名称和学分:
SELECT Cname,Ccredit
from Course
WHERE Cname LIKE '%数据%';
14.查询没有参加考试,课程号为001的学生的学号:
SELECT Sno
from SC
WHERE Cno LIKE '001' AND Grade IS NULL;
15.所有参加了课程号为002的学生学号，成绩。
SELECT Sno,Grade
from SC
WHERE Cno LIKE '%2';
16.查询所有2002级男生和MA系的学生学号和姓名:
SELECT Sno,Sname
from Student
WHERE Sno LIKE '2002%' AND Ssex LIKE '男' OR Sdept LIKE 'MA';
17.查询所有4学分和先行课为6的课程号和课程名:
SELECT Cno,Cname
from Course
WHERE Ccredit LIKE '4' AND Cpno LIKE '6';
18.查询'CS'系学生名单,并按年龄降序,按性别升序:
SELECT Sname,Sage,Ssex
from Student
WHERE Sdept LIKE 'CS'
ORDER BY
相关文档：
General Overview
FeatureSQL Server 2008 (RC0)MySQL 5.1/6PostgreSQL 8.3/PostGIS 1.3/1.4
OS
Windows XP, Windows Vista, Windows 2003, Windows 2008
Windows XP, Windows Vista, (haven't tested on 2008), Linux, Unix, Mac
Windows 2000+ (including Vista and 2003, haven't tested on 2008), Linux, Unix, Ma ......
select case when b.name is null and c.name is null then '合计' when b.name is null and c.name is not null then '小计' else b.name end as mtrname,
sum(a.number),c.name as cname from x_sell a join x_material b on a.mtr=b.fid join p_organi c on c.fid=a.customer
where a.stime>'2009-10-01'
GROUP B ......
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--每页显示记录条数
@currentpage int output,--第几页
@orderid nvarchar(50),--主键排序
@sort int,--排序方式，1表示升序，0表示降序排列
......
SQL中的时间函数非常有用，特别是在我们进行初始赋值、复杂查询的时候，就显得特别方便。
1、获得系统当前时间
select getdate()
2、DateName ( datepart , date )返回表示指定日期的指定日期部分的字符串。
--今天是2009-2-24--星期二
SELECT DATENAME(year, getdate()) AS 'Year&nbs ......
SQL SERVER 和EXCEL的数据导入导出
1、在SQL SERVER里查询Excel数据:
-- ======================================================
SELECT *
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
下面是个查� ......
