易截截图软件、单文件、免安装、纯绿色、仅160KB

Sql语句使用 50个示例

Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
问题:
1、查询“001”课程比“002”课程成绩高的所有学生的学号;
  select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
  from SC where C#='002') b
  where a.score>b.score and a.s#=b.s#;
2、查询平均成绩大于60分的同学的学号和平均成绩;
    select S#,avg(score)
    from sc
    group by S# having avg(score) >60;
3、查询所有同学的学号、姓名、选课数、总成绩;
  select Student.S#,Student.Sname,count(SC.C#),sum(score)
  from Student left Outer join SC on Student.S#=SC.S#
  group by Student.S#,Sname
4、查询姓“李”的老师的个数;
  select count(distinct(Tname))
  from Teacher
  where Tname like '李%';
5、查询没学过“叶平”老师课的同学的学号、姓名;
    select Student.S#,Student.Sname
    from Student 
    where S# not in (select distinct( SC.S#) from SC,Course,Teacher where  SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平');
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
  select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
  select S#,Sname
  from Student
  where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher  where Teacher.T#=Course.T# and Tname='叶平'));
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
  Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC


相关文档:

240个国外SQL Server高手的博客集萃

Aaron Bertrand
Adam Machanic
All Things SQL Server
Allen Kinsel - SQL DBA
Allen White
Amit Bansal writes...
Andrew Fryer's Blog
Andrew Kelly
Andy Leonard
Anything and Everything IT
Arcane Code
Arnie Rowland: Ramblings of a Harried Technogeek
B.I. for the SQL Guy
Bart Duncan's SQL Weblog ......

ORACLE SQL性能优化(内部培训资料)

ORACLE SQL性能优化
ORACLE SQL性能优化系列 (一) 
1. 选用适合的ORACLE优化器
 ORACLE的优化器共有3种:
a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性)
 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQ ......

MS SQL 递归函数

 declare @ID varchar(10)
set @ID=9 --根节点
declare @i int --级数
declare @t table(ID varchar(10),ParentID varchar(10),Level int)
set @i = 1
insert into @t select @ID,0,0 --当前级,本级,如果不要的话可以注释掉或再加个参数来选择操作
insert into @t select ID,ParentID,@i from t_ ......

Sql Server 自動還原解決方案

根據前一篇關於自動備份的文章,對應的自動還原方案
create procedure [dbo].[sp_RestoreDatabase]
@SourceDirBackupFiles NVARCHAR(200)
as
declare @DatabaseName sysname
--Table to hold the result from RESTORE HEADERONLY. Needed to get the database name o ......

sql中having 与group by详解

GROUP BY 实例
表 "Sales":
Company Amount
W3Course 6500
IBM 5500
W3Course 7300
SQL:
SELECT Company, SUM(Amount) from Sales
结果:
Company SUM(Amount)
W3Course 19300
IBM 19300
W3Course 19300
上面的代码是无效的,这是由于被返回的列没有进行部分合计。GROUP BY 子句能解决这个问题:
SELE ......
© 2009 ej38.com All Rights Reserved. 关于E健网联系我们 | 站点地图 | 赣ICP备09004571号