1.求1号课成绩大于80分的学生的学号及成绩,并按成绩由高到低列出.(表名:成绩表.字段名:课号,学号,成绩.) SELECT 学号,成绩 FROM 成绩表 WHERE 课号=1 AND 成绩>80 ORDER BY 成绩 DESC 2.列出非软件专业学生的名单.(表名:学生表.字段名:学号,性别,姓名,专业.) SELECT 姓名 FROM 学生表 WHERE 专业 not in ('软件') 3.查询成绩在70~80分之间的学生选课得分情况.(表名:选课表.字段名:课号,学号,成绩
;with test(CID,money,NewAmount) as ( SELECT Row_Number() over ( order by money ) as CID ,money ,money *0.1 as NewAmount FROM test107 ) select b.CID , SUM(a.NewAmount ) sum_money, b.NewAmount --distinct b.CID ,case when SUM(a.NewAmount ) > 50000 then
You are given a data structure of employee information, which includes the employee's unique id, his importance value and his direct subordinates' id. For example, employee 1 is the leader of employee 2, and employee 2 is the leader of employee 3. Th
ORACLE笔试题一.单选题1.在Oracle中,以下不属于集合操作符的是( ). A. UNION B. SUM C. MINUS D. INTERSECT2.在Oracle中,执行下面的语句:SELECT ceil(-97.342),floor(-97.342),round(-97.342),trunc(-97.342)FROM dual; 哪个函数的返回值不等于-97().A.ceil() B. floor() C. round(0) D. trunc() 3.以下哪个命令可以被用来从表
基于olacle自带的表 第一篇 -----1.列出至少有一个员工的所有部门. oracle 一些经典sql第一篇 --------1.列出至少有一个员工的所有部门.--------- SQL> select dname from dept where deptno in(select deptno from emp); SQL> select dname from dept where deptno in(select deptno from emp group by deptno hav
1. 各个部门平均.最大.最小工资.人数,按照部门号升序排列.SELECT deptno AS 部门号,AVG(sal) AS 平均工资 ,MAX(sal) AS 最高工资,MIN(sal) AS 最低工资 ,COUNT(*) AS 人数 FROM emp GROUP BY deptno ORDER BY deptno ASC; 2. 各个部门中工资大于5000的员工人数.SELECT deptno,COUNT(*) FROM emp WHERE sal > 5000 GROUP BY