C#】华南理工大学计算机考研复试题目 csdn

 

创建表

create table Student(
Sno char(4) not null primary key,
Sname char(40),
Ssex char(2) check([Ssex]='男'or[Ssex]='女')
)
create table Course(
Cno char(4) not null primary key,
Cname char(40)
)
create table SC(
Sno char(4),
Cno char(4),
grade int,
primary key(Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key(Cno) references Course(Cno)
)



create table [WORKS](
[EmpNo] varchar(8) references[EMPLOYEE]([EmpNo]) on delete cascade,
[CmpNo] varchar(8) references[COMPANY]([CmpNo]) on delete cascade,
[Saraly] int check([Saraly]>0)
)

时间要加引号：
insert into List values('S01','D01','电脑','2017/1/6',100)
唯一约束：
create unique index Stusno on Student(Sno)


select count(distinct Sno) from CS
select AVG(grade) from CS where Cno='1'
select max(grade) from CS where Cno='1'

C#窗体中sql语句的应用

table = db.getBySql(@"select [EMPLOYEE].[EmpNo],[EMPLOYEE].[EmpName],[EMPLOYEE].[EmpSex],
             [EMPLOYEE].[EmpAge],sum([WORKS].[Salary]) as '总工资' " +
                " from [EMPLOYEE],[WORKS]" +
                " where [EMPLOYEE].[EmpAge]>=40" +
                " and [EMPLOYEE].[EmpNo]=[WORKS].[EmpNo]" +
                " group by [EMPLOYEE].[EmpNo],[EMPLOYEE].[EmpName],[EMPLOYEE].[EmpSex],[EMPLOYEE].[EmpAge]" +
                " order by '总工资' desc");

table = db.getBySql(@"select [EMPLOYEE].[EmpName],[COMPANY].[CmpName] from [EMPLOYEE],[COMPANY],[WORKS]"+
                " where [EMPLOYEE].[EmpNo]=[WORKS].[EmpNo]" +
                 " and [COMPANY].[CmpNo]=[WORKS].[CmpNo]" +
                 "and [EMPLOYEE].[EmpNo] in" +
                " (select [WORKS].[EmpNo] from [WORKS] group by [EmpNo] having count([CmpNo])>1)");
            
able = db.getBySql(@"select [Employee].[Ename],sum([Works].[salary]) as '总工资'" +
                "from [Employee],[Works]" +  
                "where [Works].[Eno]=[Employee].[Eno]" +
                "group by [Employee].[Ename] having count(Cno)>=2");

table = db.getBySql(@"select [Company].[Cname],sum([Works].[salary]) as '总工资' " +
                " from [Company],[Works]" +
                " where [Company].[Cno]=[Works].[Cno]" +
                " group by [Company].[Cname]" +
                " order by '总工资' desc");

 table = db.getBySql(@"select [TEACHERS].[TName],[COURSES].[CName]"+
                       "from [TEACHERS],[COURSES],[WORKS]"+
                        "where [WORKS].[CNo]=[COURSES].[CNo]"+
                        "and [WORKS].[TNo]=[TEACHERS].[TNo]"+
                      "and [TEACHERS].[TNo] in"+
                      "(select [WORKS].[TNo] from [WORKS] group by [WORKS].[TNo] having count([WORKS].[CNo])>=2)");


table = db.getBySql(@"select [Department].[DeptName], sum([Group].[Number]) as '病人人数' " +
                " from [Department],[Group]" +
                " where [Department].[DeptNo]=[Group].[DeptNo]" +
                " group by [Department].[DeptNo],[Department].[DeptName] " +
                " order by '病人人数' desc");

/只找出最多和最少病人人数的记录
 table = db.getBySql(@"select [Group].[GroupName],  [Group].[Month],[Group].[Number] " +
                " from [Group]" +
                " where [Group].[Number] in(select max([Group].[Number])from [Group])"+
            " or [Group].[Number]in(select min([Group].[Number])from [Group])");

//模糊查找
 DataTable table = db.getBySql(@"select [Department].[DeptName],[Group].[GroupName],[Patient].[PName] " +
                " from [Department],[Group],[Patient]" +
                " where [Department].[DeptNo]=[Group].[DeptNo]" +
                " and [Group].[GroupNo]=[Patient].[GroupNo]" +
                " and [Patient].[PName]like'%" + textBox1.Text + "%'" );

//平均分数
 DataTable table = db.getBySql(@"select [Course].[Cname],count([SC].[Sno])as'选课人数',avg([SC].[grade])as'平均分数' " +
                " from [Course],[SC],[Student]" +
                " where [Course].[Cno]=[SC].[cno]" +
                " and [Student].[Sname]like'%" + textBox2.Text + "%'"+
            " group by [Course].[Cname]  ");