一道面试题【sql吧】_百度贴吧
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&签到排名：今日本吧第个签到，本吧因你更精彩，明天继续来努力！
本吧签到人数：0成为超级会员，使用一键签到本月漏签0次！成为超级会员，赠送8张补签卡连续签到：天&&累计签到：天超级会员单次开通12个月以上，赠送连续签到卡3张
关注：37,531贴子：
一道面试题收藏
有个表a a中有三个字段n n1 n2
n1 n2 为整形，求写sql语句取出四列 a n1 n2 n3 ，n3 是n1与n2的商
Select n1,n2,(n1/n2) as n3 from a
登录百度帐号推荐应用发现和了解你未来的雇主一道SQL面试题求最大值
来源：博客园
哎,这题目既然没做来
题目大概为:一个表t1存储了用户登陆时间和登户用户名,登陆时间为主键,求出每一位用户最后登陆的时间,表字段为UserName,LoginTime
答案:select UserName,Max(LoginTime) from t1 group by UserName
免责声明：本站部分内容、图片、文字、视频等来自于互联网，仅供大家学习与交流。相关内容如涉嫌侵犯您的知识产权或其他合法权益，请向本站发送有效通知，我们会及时处理。反馈邮箱&&&&。
学生服务号
在线咨询，奖学金返现，名师点评，等你来互动&& &文章主题:
交流经验：
总积分：345
级别：普通会员
1.用一条SQL语句 查询出每门课都大于80分的学生姓名&
name&& kecheng&& fenshu& 张三&&&&&语文&&&&&& 81 张三&&&&&数学&&&&&& 75 李四&&&&&语文&&&&&& 76 李四&&&&&数学&&&&&& 90 王五&&&&&语文&&&&&& 81 王五&&&&&数学&&&&&& 100 王五&&&&&英语&&&&&&&90
A: select distinct name from table& where& name not in (select distinct name from table where fenshu&=80)
2.学生表 如下: 自动编号&&&学号&&&姓名 课程编号 课程名称 分数 1&&&&&&& 2005001&&张三& 0001&&&&&&数学&&& 69 2&&&&&&& 2005002&&李四& 0001&&&&&&数学&&& 89 3&&&&&&& 2005001&&张三& 0001&&&&&&数学&&& 69 删除除了自动编号不同,其他都相同的学生冗余信息
A: delete tablename where&自动编号&not in(select min(自动编号) from tablename group by&学号,姓名,课程编号,课程名称,分数)
一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合. 你先按你自己的想法做一下，看结果有我的这个简单吗？
答：select a.name, b.name& from team a, team b& where a.name & b.name
请用SQL语句实现：从TestDB数据表中查询出所有月份的发生额都比101科目相应月份的发生额高的科目。请注意：TestDB中有很多科目，都有1－12月份的发生额。 AccID：科目代码，Occmonth：发生额月份，DebitOccur：发生额。 数据库名：JcyAudit，数据集：Select * from TestDB
答：select a.* from TestDB a& ,(select Occmonth,max(DebitOccur) Debit101ccur from TestDB where AccID='101' group by Occmonth) b where a.Occmonth=b.Occmonth and a.DebitOccur&b.Debit101ccur
************************************************************************************
面试题：怎么把这样一个表儿 year&&month amount 1991&& 1&&&& 1.1 1991&& 2&&&& 1.2 1991&& 3&&&& 1.3 1991&& 4&&&& 1.4 1992&& 1&&&& 2.1 1992&& 2&&&& 2.2 1992&& 3&&&& 2.3 1992&& 4&&&& 2.4 查成这样一个结果 year m1&&m2&&m3&&m4
1.2 1.3 1.4
2.2 2.3 2.4&
答案一、 select year,& (select amount from&&aaa m where month=1&&and m.year=aaa.year) as m1, (select amount from&&aaa m where month=2&&and m.year=aaa.year) as m2, (select amount from&&aaa m where month=3&&and m.year=aaa.year) as m3, (select amount from&&aaa m where month=4&&and m.year=aaa.year) as m4 from aaa&&group by year
这个是ORACLE&&中做的： select * from (select name, year b1, lead(year) over (partition by name order by year) b2, lead(m,2) over(partition by name order by year) b3,rank()over( partition by name order by year) rk from t) where rk=1;
************************************************************************************
精妙的SQL语句！ 精妙SQL语句&& 作者：不详 发文时间： 10:55:05&
说明：复制表(只复制结构,源表名：a&新表名：b)&
SQL: select * into b from a where 1&&1&
说明：拷贝表(拷贝数据,源表名：a&目标表名：b)&
SQL: insert into b(a, b, c) select d,e,&
说明：显示文章、提交人和最后回复时间&
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b&
说明：外连接查询(表名1：a&表名2：b)&
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c&
说明：日程安排提前五分钟提醒&
SQL: select * from&日程安排&where datediff('minute',f开始时间,getdate())&5&
说明：两张关联表，删除主表中已经在副表中没有的信息&
delete from info where not exists ( select * from infobz where info.infid=infobz.infid )&
SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE&
FROM TABLE1,&
(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE&
FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND&
FROM TABLE2&
WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,&
(SELECT NUM, UPD_DATE, STOCK_ONHAND&
FROM TABLE2&
WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =&
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') && '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y,&
WHERE X.NUM = Y.NUM&（+）&
AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) && X.STOCK_ONHAND ) B&
WHERE A.NUM = B.NUM&
select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and&系名称='&&strdepartmentname&&' and&专业名称='&&strprofessionname&&' order by&性别,生源地,高考总成绩&
从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）&
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear,&
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN,&
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI,&
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR,&
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR,&
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY,&
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,&
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL,&
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU,&
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP,&
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT,&
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,&
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC&
FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration&
FROM TELFEESTAND a, TELFEE b&
WHERE a.tel = b.telfax) a&
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')&
说明：四表联查问题：&
SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....&
说明：得到表中最小的未使用的ID号&
SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID&
FROM Handle&
WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)
*******************************************************************************
有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value换为A中对应的value 这道题的SQL语句怎么写？
update & b & set & b.value=(select & a.value & from & a & where & a.key=b.key) & where & b.id & in(select & b.id & from & b,a & where & b.key=a.key);
***************************************************************************
高级sql面试题
原表:& courseid coursename score& -------------------------------------& 1 java 70& 2 oracle 90& 3 xml 40& 4 jsp 30& 5 servlet 80& -------------------------------------& 为了便于阅读,查询此表后的结果显式如下(及格分数为60):& courseid coursename score mark& ---------------------------------------------------& 1 java 70 pass& 2 oracle 90 pass& 3 xml 40 fail& 4 jsp 30 fail& 5 servlet 80 pass& ---------------------------------------------------& 写出此查询语句
没有装ＯＲＡＣＬＥ，没试过& select courseid, coursename ,score ,decode（sign(score-60),-1,'fail','pass') as mark from course
SQL& desc course_v& Name Null? Type& ----------------------------------------- -------- ----------------------------& COURSEID NUMBER& COURSENAME VARCHAR2(10)& SCORE NUMBER&
SQL& select * from course_v;&
COURSEID COURSENAME SCORE& ---------- ---------- ----------& 1 java 70& 2 oracle 90& 3 xml 40& 4 jsp 30& 5 servlet 80&
SQL& select courseid, coursename ,score ,decode(sign(score-60),-1,'fail','pass') as mark from course_v;&
COURSEID COURSENAME SCORE MARK& ---------- ---------- ---------- ----& 1 java 70 pass& 2 oracle 90 pass& 3 xml 40 fail& 4 jsp 30 fail& 5 servlet 80 pass
*******************************************************************************
id proid proname& 1 1 M& 1 2 F& 2 1 N& 2 2 G& 3 1 B& 3 2 A& 查询后的表:&
id pro1 pro2& 1 M&F& 2 N G& 3 B A& 写出查询语句
sql求解& 表a& 列&a1 a2& 记录&1 a& 1 b& 2 x& 2 y& 2 z& 用select能选成以下结果吗？& 1 ab& 2 xyz& 使用pl/sql代码实现，但要求你组合后的长度不能超出oracle varchar2长度的限制。& 下面是一个例子& create or replace type strings_table is table of varchar2(20);& /& create or replace function merge (pv in strings_table) return varchar2& is& ls varchar2(4000);& begin& for i in 1..pv.count loop& ls := ls || pv(i);&&&& /& create table t (id number,name varchar2(10));& insert into t values(1,'Joan');& insert into t values(1,'Jack');& insert into t values(1,'Tom');& insert into t values(2,'Rose');& insert into t values(2,'Jenny');&
column names format a80;& select t0.id,merge(cast(multiset(select name from t where t.id = t0.id) as strings_table)) names& from (select distinct id from t) t0;&
drop type strings_&&&
Well if you have a thoretical maximum, which I would assume you would given the legibility of listing hundreds of employees in the way you describe then yes. But the SQL needs to use the LAG function for each employee, hence a hundred emps a hundred LAGs, so kind of bulky.&
This example uses a max of 6, and would need more cut n pasting to do more than that.&
SQL& select deptno, dname, emps& 2 from (& 3 select d.deptno, d.dname, rtrim(e.ename ||', '||& 4 lead(e.ename,1) over (partition by d.deptno& 5 order by e.ename) ||', '||& 6 lead(e.ename,2) over (partition by d.deptno& 7 order by e.ename) ||', '||& 8 lead(e.ename,3) over (partition by d.deptno& 9 order by e.ename) ||', '||& 10 lead(e.ename,4) over (partition by d.deptno& 11 order by e.ename) ||', '||& 12 lead(e.ename,5) over (partition by d.deptno& 13 order by e.ename),', ') emps,& 14 row_number () over (partition by d.deptno& 15 order by e.ename) x& 16 from emp e, dept d& 17 where d.deptno = e.deptno& 18 )& 19 where x = 1& 20 /&
DEPTNO DNAME EMPS& ------- ----------- ------------------------------------------& 10 ACCOUNTING CLARK, KING, MILLER& 20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH& 30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD&
also& 先create function get_a2;& create or replace function get_a2( tmp_a1 number)& return varchar2& is& Col_a2 varchar2(4000);& begin& Col_a2:='';& for cur in (select a2 from unite_a where a1=tmp_a1)& loop& Col_a2=Col_a2||cur.a2;&& return Col_a2;& end get_a2;&
select distinct a1 ,get_a2(a1) from unite_a& 1 ABC& 2 EFG& 3 KMN
*******************************************************************************
一个SQL&面试题
去年应聘一个职位未果,其间被考了一个看似简单的题,但我没有找到好的大案. 不知各位大虾有无好的解法?
题为: 有两个表, t1, t2, Table t1:
SELLER | NON_SELLER ----- -----
A B A C A D B A B C B D C A C B C D D A D B D C
SELLER | COUPON | BAL ----- --------- --------- A 9 100 B 9&200 C 9 300 D 9&400 A 9.5 100 B 9.5&20 A 10 80
要求用SELECT&语句列出如下结果:------如A的SUM(BAL)为B,C,D的和,B的SUM(BAL)为A,C,D的和....... 且用的方法不要增加数据库负担,如用临时表等.
NON-SELLER| COUPON | SUM(BAL) ------- -------- A 9 900 B 9&800 C 9 700 D 9&600& A 9.5 20 B 9.5&100 C 9.5 120 D 9.5&120 A 10 0 B 10&80 C 10 80 D 10 80
关于论坛上那个SQL微软面试题
一百个账户各有100$，某个账户某天如有支出则添加一条新记录，记录其余额。一百天后，请输出每天所有账户的余额信息 &
这个问题的难点在于每个用户在某天可能有多条纪录，也可能一条纪录也没有（不包括第一天）
返回的记录集是一个100天*100个用户的纪录集
下面是我的思路：
1.创建表并插入测试数据：我们要求username从1-100 CREATE TABLE [dbo].[TABLE2] ( [username] [varchar] (50) NOT NULL , --用户名 [outdate] [datetime] NOT NULL , --日期 [cash] [float] NOT NULL --余额 ) ON [PRIMARY
declare @i int set @i=1 while @i&=100 & begin & & insert table2 values(convert(varchar(50),@i),'',100) & & insert table2 values(convert(varchar(50),@i),'',50) & & set @i=@i+1 & end insert table2 values(convert(varchar(50),@i),'',90)
select * from table2 order by outdate,convert(int,username)
2.组合查询语句： a.我们必须返回一个从第一天开始到100天的纪录集： 如：（这个日期是任意的）到& 由于第一天是任意一天，所以我们需要下面的SQL语句： select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate from table2 group by username order by convert(int,username) 这里的奥妙在于： convert(int,username)-1（记得我们指定用户名从1-100 :-)) group by username,min(outdate):第一天就可能每个用户有多个纪录。 返回的结果： outdate& & & & & & & & & & & & & & & & & & & & & & & && ------------------------------------------------------&
00:00:00.000 .........
00:00:00.000
b.返回一个所有用户名的纪录集： select distinct username from table2& 返回结果： username& & & & & & & & & & & & & & & & & & & & && --------------------------------------------------& 1 10 100 ...... 99
c.返回一个100天记录集和100个用户记录集的笛卡尔集合： select * from ( select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate from table2 group by username order by convert(int,username)
) as A CROSS join& ( select distinct username from table2& ) as B order by outdate,convert(int,username) 返回结果100*100条纪录： outdate& & & & & & & & & & & & & & username
00:00:00.000& & & & & & 1 ......
00:00:00.000& & & & & & 100
d.返回当前所有用户在数据库的有的纪录： select outdate,username,min(cash) as cash from table2 group by outdate,username
order by outdate,convert(int,username) 返回纪录： outdate& & & & & & & & & & & & & & username& & cash
00:00:00.000& & & & & & 1& & & & & 90 ......
00:00:00.000& & & & & & 100& & & & 50
e.将c中返回的笛卡尔集和d中返回的纪录做left join: select C.outdate,C.username, D.cash from ( select * from ( select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate from table2 group by username order by convert(int,username) ) as A CROSS join& ( select distinct username from table2& ) as B ) as C left join ( select outdate,username,min(cash) as cash from table2 group by outdate,username ) as D on(C.username=D.username and datediff(d,C.outdate,D.outdate)=0)
order by C.outdate,convert(int,C.username) 注意：用户在当天如果没有纪录，cash字段返回NULL，否则cash返回每个用户当天的余额 outdate& & & & & & & & & & & & & & username& & cash
00:00:00.000& & & & & & 1& & & & & 90
00:00:00.000& & & & & & 2& & & & & 100 ......
00:00:00.000& & & & & & 1& & & & & 90
00:00:00.000& & & & & & 2& & & & & NULL& &--注意这里 ......
00:00:00.000& & & & & & 100& & & &&50
f.好了，现在我们最后要做的就是，如果cash为NULL，我们要返回小于当前纪录日期的第一个用户余额(由于我们使用order by cash,所以返回top 1纪录即可，使用min应该也可以)，这个余额即为当前的余额： case isnull(D.cash,0) when 0 then& ( select top 1 cash from table2 where table2.username=C.username and datediff(d,C.outdate,table2.outdate)&0& order by table2.cash ) else D.cash end as cash
g.最后组合的完整语句就是 select C.outdate,C.username, case isnull(D.cash,0) when 0 then& ( select top 1 cash from table2 where table2.username=C.username and datediff(d,C.outdate,table2.outdate)&0& order by table2.cash ) else D.cash end as cash from ( select * from ( select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate from table2 group by username order by convert(int,username) ) as A CROSS join& ( select distinct username from table2& ) as B ) as C left join ( select outdate,username,min(cash) as cash from table2 group by outdate,username ) as D on(C.username=D.username and datediff(d,C.outdate,D.outdate)=0)
order by C.outdate,convert(int,C.username)
返回结果： outdate& & & &&&&&&&&&&&&&&&&&&&&&&&&&& username& & & & cash
00:00:00.000&&& 1& & & & & & &&&&&&& 90
00:00:00.000&&& 2& & & & & & &&&&&& 100 ......
00:00:00.000&&& 100&&&&&&&&&&&&&&& 50
***********************************************************************************
取出sql表中第31到40的记录（以自动增长ID为主键）
*从数据表中取出第n条到第m条的记录*/&
declare @m int& declare @n int& declare @sql varchar(800)& set @m=40& set @n=31& set @sql='select top '+str(@m-@n+1) + '* from idetail where autoid not in(& select top '+ str(@n-1) + 'autoid from idetail)'& exec(@sql)
select top 10 * from t where id not in (select top 30 id from t order by id ) orde by id
--------------------------------------------------------------------------------
select top 10 * from t where id in (select top 40 id from t order by id) order by id desc
*******************************************************************************
一道面试题,写sql语句
有表a存储二叉树的节点,要用一条sql语句查出所有节点及节点所在的层. 表a& c1 c2 A ----------1& ---- ---- / \ A B B C --------2 A C / / \ B D D N E ------3 C E / \ \ D F F K I ---4 E I D K C N
所要得到的结果如下
jd cs ----- ---- A 1 B&2 C 2 D 3 N 3 E&3 F 4 K 4 I 4 有高手指导一下,我只能用pl/sql写出来,请教用一条sql语句的写法
SQL& select c2, level + 1 lv 2 from test start 3 with c1 = 'A' 4 connect by c1 = prior c2 5 union 6 select 'A', 1 from dual 7
C2 LV -- ---------- A 1 B&2 C 2 D 3 E 3 N&3 F 4 I 4 K 4
已选择9行。
为管理岗位业务培训信息，建立3个表:
S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
C (C#,CN ) C#,CN 分别代表课程编号、课程名称
SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩
1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
--实现代码:
SELECT S#,SN FROM S
WHERE [S#] IN(
SELECT [S#] FROM C,SC
WHERE C.[C#]=SC.[C#]
AND CN='税收基础')
可以用三表连接做:
SELECT S.S#,SN&
FROM S,C,SC
WHERE S.S#=SC.S# AND SC.C#=C.C# AND CN='税收基础'
2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
--实现代码:
SELECT S.SN,S.SD FROM S,SC
WHERE S.[S#]=SC.[S#]
AND SC.[C#]='C2'
SELECT SN,SD
WHERE S# IN (SELECT S# FROM SC WHERE C#='C2')
3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
--实现代码:
SELECT SN,SD FROM S
WHERE [S#] NOT IN(
SELECT [S#] FROM SC
WHERE [C#]='C5')
注解:通常问不包括什么的查询用NOT解决.
4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
--实现代码:
SELECT SN,SD FROM S
WHERE [S#] IN(
SELECT [S#] FROM SC
RIGHT JOIN
C ON SC.[C#]=C.[C#] GROUP BY [S#]
HAVING COUNT(*)=COUNT([S#]))
注释:右连接后,若没有选全部课程,则存在s#为NULL,故COUNT(*)=COUNT(S#)是有无选全部课程的标志
5. 查询选修了课程的学员人数
--实现代码:
SELECT 学员人数=COUNT(DISTINCT [S#]) FROM SC
注释:要强调不重复,要在属性前加DISTINCT关键字
6. 查询选修课程超过5门的学员学号和所属单位
--实现代码:
SELECT SN,SD FROM S
WHERE [S#] IN(
SELECT [S#] FROM SC
GROUP BY [S#]
HAVING COUNT(DISTINCT [C#])&5)
注释:此题应该不存在同一学生选同一门课程的情况,DISTINCT关键字应该可以省去.
已知关系模式:
S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
1. 找出没有选修过“李明”老师讲授课程的所有学生姓名
--实现代码:
SELECT SNAME FROM S
WHERE NOT EXISTS(
SELECT * FROM SC,C
AND CNAME='李明'
AND SC.SNO=S.SNO)
注释:使用EXISTS逐一比较排除选李明老师课的学生.
SELECT SNAME&
WHERE S.SNO=SC.SNO AND CNO NOT IN (SELECT CNO FROM C WHERE CTEACHER='李明')
2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
--实现代码:
SELECT S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)
FROM S,SC,(
SELECT SNO
WHERE SCGRADE&60
GROUP BY SNO
HAVING COUNT(DISTINCT CNO)&=2
)A WHERE S.SNO=A.SNO AND SC.SNO=A.SNO
GROUP BY S.SNO,S.SNAME
注释:此题重点在于先将SC表用WHERE条件筛选出不及格的条目,再用GROUP BY按SNO分组统计出count(SNO)&=2的条目
SELECT SNAME,AVG(SCGRADE)
WHERE S.SNO=SC.SNO AND SC.SNO NOT IN (SELECT SNO FROM SC WHERE SCGRADE&60 GROUP BY SNO HAVING COUNT(CNO)&=2)
GROUP BY SNAME
3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
--实现代码:
SELECT S.SNO,S.SNAME
SELECT SC.SNO
AME IN('1','2')
GROUP BY SNO
HAVING COUNT(DISTINCT CNO)=2
)SC WHERE S.SNO=SC.SNO
注释:上面给出的解法应该是只选过'1'号和'2'号课程的学生姓名,题目要求的解法应该如下:
SELECT SNAME
FROM S,SC,C
WHERE S.SNO=SC.SNO O AME IN('1','2')
4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
--实现代码:
SELECT S.SNO,S.SNAME
SELECT SC1.SNO
FROM SC SC1,C C1,SC SC2,C C2
O AND C1.NAME='1'
O AND C2.NAME='2'
AND SC1.SCGRADE&SC2.SCGRADE
)SC WHERE S.SNO=SC.SNO
注释:此题重点在于SC表的数据要和自己的数据比较,故要采用自连接,同时,需要C表确定SC表的比较条目,上面给出的解答好像少了条件SC1.SNO=SC2.SNO
SELECT SNO,SNAME
WHERE SNO IN (SELECT SC1.SNO FROM SC SC1,SC SC2,C C1,C C2 WHERE SC1.SNO=SC2.SNO O AME='1' O AME='2' AND SC1,SCGRADE&SC2.SCGRADE)
5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
--实现代码:
SELECT S.SNO,S.SNAME,SC.[1号课成绩],SC.[2号课成绩]
SELECT SC1.SNO,[1号课成绩]=SC1.SCGRADE,[2号课成绩]=SC2.SCGRADE
FROM SC SC1,C C1,SC SC2,C C2
O AND C1.NAME='1'
O AND C2.NAME='2'
AND SC1.SCGRADE&SC2.SCGRADE
)SC WHERE S.SNO=SC.SNO
注释:此题和上题类似,如果采用解法1，将子查询定义为临时表则比较简单,直接在查询项中给出即可,如果是解法二则比较麻烦
SELECT SNO,SNAME,SC1.GRADE,SC2.GRADE
FROM S,SC SC1,SC SC2,C C1,C C2
WHERE SNO IN (SELECT SC1.SNO FROM SC SC1,SC SC2,C C1,C C2 WHERE SC1.SNO=SC2.SNO O AME='1' O AME='2' AND SC1,SCGRADE&SC2.SCGRADE) AND SNO=SC1 AND SNO=SC2 O AME='1' O AME='2'
说明：有三个表，项目表、合同表、付款表
--下面是建立表的语句 create table 项目(项目编号 int,项目名称 varchar(50)) insert into 项目 select 1, '项目1' from dual union all select 2, '项目2' from dual union all select 3, '项目3'
create table 合同(合同编号 int,项目编号 int,合同金额 number(7,3))
insert into 合同 select 1,1,1000 from dual union all select 2,1,1500 from dual union all select 3,2,2000
create table 付款(付款编号 int,合同编号 int,付款金额 number(7,3))
insert into 付款 select 1,1,100 from dual union all select 2,2,200 from dual union all select 3,2,800
一个项目可能会有签署多个合同，每个合同会分几次付款，
问题(一) 设计一个查询，要求返回结果如下： 项目编号&&&&&&& 项目名称&&&&&&&&&&&& 项目所有合同的金额&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& ----------- ----------------------- ------------------------ 1&&&&&&&&&& 项目1&&&&&&&&&&&&&&&&&&& 2500 2&&&&&&&&&& 项目2&&&&&&&&&&&&&&&&&&& 2000 3&&&&&&&&&& 项目3&&&&&&&&&&&&&&&&&&& NULL
解答： SELECT L.项目编号,MAX(L.项目名称),SUM(R.合同金额) AS 合同金额 FROM 项目 L LEFT OUTER JOIN 合同 R ON L.项目编号=R.项目编号 GROUP BY L.项目编号
注释:出现NULL值,应该采用外连接,连接后按项目编号分组对付款金额求和,上面给出解法未排序以及聚合函数使用错误,应该用SUM而非MAX
SELECT X.项目编号,项目名称,SUM(付款金额)
FROM 项目 X LEFT JOIN 合同 H ON X.项目编号=H.项目编号
GROUP BY X.项目编号
ORDER BY X.项目编号 ASC
问题(二) 设计一个查询，要求返回结果如下： 项目编号&&&&&&& 项目所有合同已付款金额&&&&&&&&&&&&&&&&&&&&&&&&&&&&& ----------- ----------------------------- 1&&&&&&&&&& &1100 2&&&&&&&&&& &NULL -----------------------------------------
解答： SELECT T2.项目编号,SUM(T3.付款金额) AS 项目所有合同已付款金额 FROM 合同 T2 LEFT OUTER JOIN 付款 T3 ON& T2.合同编号=T3.合同编号 GROUP BY T2.项目编号
解析:同上题,有NULL,先外连接再分组.
SELECT H.项目编号,SUM(F.付款金额)
FROM 合同 H LEFT JOIN 付款 F ON H.合同编号=F.合同编号
GROUP BY H.项目编号
ORDER BY H.项目编号 ASC
问题(三) 设计一个查询，要求返回结果如下： 项目编号&&& 项目名称&&&&&&& 项目所有合同已付款金额&&&&&&&&&&&&&&&&&&&&&&&&&&&&& ----------- ---------------------------------------- 1&&&&&&&&&& 项目1&&&1100 2&&&&&&&&&& 项目2&&&NULL ----------------------------------------------------
解答： SELECT T1.项目编号 ,MAX(T1.项目名称) ,SUM(T3.付款金额) AS 项目所有合同已付款金额 FROM 项目 T1 JOIN 合同 T2 ON T1.项目编号=T2.项目编号 LEFT OUTER JOIN 付款 T3 ON& T2.合同编号=T3.合同编号 GROUP BY T1.项目编号
解析:在上题解法二的基础上建立临时表,再连接项目表即可
SELECT X.项目编号,X.项目名称,HF.合同已付款
FROM 项目 X,(
SELECT H.项目编号 编号,SUM(F.付款金额) 合同已付款
FROM 合同 H LEFT JOIN 付款 F ON H.合同编号=F.合同编号
GROUP BY H.项目编号
WHERE X.项目编号=HF.编号
ORDER BY X.项目编号
问题(四) 请您设计一个查询语句，检索的格式如下
------------------------------------------------------------------------ 项目编号&&& 项目名称&&& 项目所有合同的金额&&& 项目所有合同已付款金额&&&& 1&&&&&&&&&& 项目1&&&&&&& &&&&&&&&&&&&&
2&&&&&&&&&& 项目2&&&&&&& &&&&&&&&&&&&& NULL ------------------------------------------------------------------------
解答： select L.项目编号,L.项目名称,L.项目所有合同的金额,R.项目所有合同已付款金额 from (select A.项目编号,A.项目名称,sum(B.合同金额) 项目所有合同的金额 && from 项目 A LEFT JOIN 合同 B ON A.项目编号=B.项目编号 &&&& group by A.项目编号,A.项目名称) L JOIN (select B.项目编号, sum(付款金额) 项目所有合同已付款金额 && from 合同 B LEFT JOIN& 付款 C ON B.合同编号=C.合同编号 &&&& group by B.项目编号) R ON L.项目编号=R.项目编号
解析:在前两题的基础上进行内联
SQL查询的基本结构
SELECT [列名1],[列名2]
FROM [表名]
WHERE [条件]
GROUP BY [列名] HAVING [分组条件]
ORDER BY [列名] ASC/DESC
精品视频课程推荐
本课程专注于数据结构和算法的内容，使用Java来进行代码示例，不空洞的讲解概念和理论，重点放在代码的实现和示例上。
从零开始、全面系统、成体系的讲解数据结构和基本算法，循序渐进的讲述构建软件系统所常见的数据结构和算法。
本视频课程是北京Java私塾原创精品书籍《研磨设计模式》一书的配套学习视频，由《研磨设计模式》的第一作者CC录制
课程目标：全面、系统的掌握GoF设计模式的知识，达到可以在实际项目开发中运用的能力
技术要点：如何实现可配置、如何实现缓存以及缓存的管理、如何实现用缓存来控制多实例的创建、如何实现参数化工厂、 如何实现可扩展工厂、如何实现原型管理器、如何实现Java的静态代理和动态代理、如何实现多线程处理队列请求、 如何实现命令的参数化配置、可撤销的操作、宏命令、队列请求和日志请求、如何实现翻页迭代、如何检测环状结构、 如何实现通用的增删改查、如何模拟工作流来处理流程、如何实现简单又通用的XML读取、如何实现模拟AOP的功能......
JavaScript的内置对象--Array、String、Date、Math等，可以通过DOM对象进行对象控制，创建控制菜单及复选框的控制，创建二级联动列表框及列表框选项的移动，JavaScript项目，创建基于JS的商品管理系统。
创建规范的XML文档，DTD的作用，并且可以根据要求创建私用的DTD，通过JavaScript解析XML DOM
系统、完整的学习Spring Web MVC开发的知识。包括：Spring Web MVC入门；理解DispatcherServlet；注解式控制器开发详解；数据类型转换；数据格式化；数据验证； 拦截器；对Ajax的支持；文件上传下载；表单标签等内容；最后以一个综合的CRUD带翻页的应用示例来综合所学的知识
&& &文章主题:
交流经验：
总积分：340
级别：普通会员
谢谢！收藏！
个性签名：这里没有末路，你从不曾寂寞！
&& &文章主题:
交流经验：
总积分：7244
级别：VIP2
对于面试来说，可以看看！
个性签名：好好学习，天天向上！
&& &文章主题:
交流经验：
总积分：7102
级别：VIP2
是些基本的知识点哈
个性签名：谢谢java私塾的分享资料
&& &文章主题:
交流经验：
总积分：558
级别：普通会员
谢谢！收藏！
&& &文章主题:
交流经验：
总积分：16
级别：普通会员
谢谢！收藏！
个性签名：十四岁asd
&& &文章主题:
交流经验：
级别：普通会员
个性签名：加油
&& &文章主题:
交流经验：
总积分：360
级别：普通会员
个性签名：把握现在！
&& &文章主题:
交流经验：
总积分：110
级别：普通会员
很全面实用
&& &文章主题:
交流经验：
总积分：110
级别：普通会员
不错，值得学习
选择一个版面
软件设计专版
Web前端技术
学习问题讨论
面试、就业
版权所有 Copyright(C) 私塾在线学习网