求解答:oracle数据库的题目.

a 找出最贵的商品(item)的名称和价格

SELECT

name, price

FROM

item

WHERE

price = ( SELECT MAX(itprice) FROM item it);

b 找出每个月每个商品的销售总金额；

SELECT

TO_CHAR(transactiondate, 'MM') AS 月,

itemname AS 商品名,

SUM( itemSalequantity itemprice ) AS 销售额

FROM

transaction, itemSale, item

WHERE

transactiontransid = itemSaletransid

AND itemSaleitemid = itemitemid

GROUP BY

TO_CHAR(transactiondate, 'MM') ,

itemname

c 找出从来也没有销售过的商品；

SELECT

FROM

item

WHERE

itemid NOT IN ( SELECT DISTINCT itemid FROM itemSale)

-- 上面这个估计执行效率不高

d找出从来也没有买过“酒”的所有客户。

SELECT

FROM

customer

WHERE

customercustid NOT IN

( SELECT transactioncustid

FROM

transaction, itemSale, item

WHERE

transactiontransid = itemSaletransid

AND itemSaleitemid = itemitemid

AND itemname LIKE '%酒%'

)

-- 上面这个估计执行效率也是不高

1、查询姓“李”的老师的个数；

SELECT COUNT(Tno) FROM Teacher WHERE Tname LIKE '李%'

2、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；

SELECT Sno, Sname

FROM Student

WHERE sno IN(SELECT sno

FROM (SELECT Sno FROM SC WHERE Cno = 'c001') t1

(SELECT Sno FROM SC WHERE Cno = 'c002') t2

WHERE t1sno = t2sno)

3、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

SELECT Sno, Sname

FROM student

WHERE Sno IN(SELECT SC

FROM SC

WHERE CNO IN (SELECT CNO FROM Course WHERE Cname = '叶平')

GROUP BY SC HAVING COUNT(CNO) = (SELECT COUNT(CNO) FROM Course WHERE Cname = '叶平'))

4、查询“c001”课程比“c002”课程成绩高的所有学生的学号

SELECT C1SC

FROM (SELECT SC, Score FROM SC WHERE Cno = 'c001') c1

(SELECT SC, Score FROM SC WHERE Cno = 'c002') c2

WHERE c1SC = c2SC

AND c1Score > c2Score

如果只有C1,而没有C2成绩，用这个好一点

SELECT SC

FROM (SELECT SC, Score FROM SC WHERE Cno = 'c001') c1

left join (SELECT SC, Score FROM SC WHERE Cno = 'c002') c2

ON c1SC = c2SC AND c1Score > c2Score

5、查询平均成绩大于60分的同学的学号和平均成绩；

SELECT Sno, AVG(score)

FROM SC GROUP BY Sno Having AVG(score) > 60

6、查询所有同学的学号、姓名、选课数、总成绩；

SELECT stuSno, stuSname, COUNT(Cno), SUM(score)

FROM Student stu, SC

WHERE stuSno = Scsno

GROUP BY stusno, stuSname

7、查询没有学全所有课的同学的学号、姓名；

SELECT Sno, Sname

FROM SC

WHERE Sno NOT IN (SELECT Sno

FROM SC

GROUP BY SC

HAVING COUNT(CNO) < (SELECT COUNT(DISTINCT CNO) FROM SC))

1\SELECT TO_CHAR(REGISTER, 'YYYYMM') AS MONTH, COUNT() FROM A GROUP BY TO_CHAR(REGISTER, 'YYYYMM');

2\SELECT COUNT() FROM (SELECT NAME FROM A GROUP BY NAME HAVING COUNT() > 1);

3\INSERT INTO C

SELECT AID, ANAME

FROM A

INNER JOIN (SELECT NAME FROM A GROUP BY NAME HAVING COUNT() > 1) B ON ANAME = BNAME;

4\SELECT MAX(REGISTER), ID, NAME

FROM A

GROUP BY ID, NAME;

2按年

select Fname,to_char(Fdate,'yyyy') as y,sum(Fsalary) from table1 where to_char(Fdate,'yyyy') = (select to_char(Fdate'yyyy') from table1) group by Fname, y;

按月

select Fname,to_char(Fdate,'yyyymm') as mn,sum(Fsalary) from table1 where to_char(Fdate,'yyyymm') = (select to_char(Fdate'yyyymm') from table1) group by mn;

3

--从月薪表中取符合条件的数据

select Fname,avg(total) as sal_avg from

(select Fname,to_char(Fdate,'yyyymm') as mn,sum(Fsalary) as total from table1 where to_char(Fdate,'yyyymm') = (select to_char(Fdate'yyyymm') from table1) group by mn) group by Fname having avg(total) >=500 and avg(total) <=800;

第一题 更改用户密码的代码就是你写的BY后面写密码就行

第二题 直接用insert into emp values('员工1','IT support department');剩下的一样就部门改了就行。

第三题 用更新 update jobs set 职位 = IT Support Manager where name=Valli Pataballa；剩下的人一样。

3-1题 我没看明白。一个职位等于一个字段？？

3-2如上一样

3-3 update 表1 set 位置=IT Manager where name = Alexander Hunold；

3-4 update 表1 set jobid=（select jobid from 表1 where name=IT Support Manager），sal = sal+2000 where name = Valli Pataballa；

3-5 是题？？

我看不到你的表结构目前只能根据你的题这样写。都是简单的曾改，还有一些看不懂的题。

补齐函数lpad()或者rpad(),一个是左边补齐lpad（），一个是后边补齐rpad（），举例：

lpad('aa',5,‘0’)这个意思是在左边用0补齐5位，结果是000aa。

截取函数：substr（‘1234567’，1,5）意思是把1234567这个字符串从1个位置开始截取，截取到第五个

第一题：select substr(lpad('1234567',5,'0'),1,5) from dual; 结果12345

select substr(lpad('123',5,'0'),1,5) from dual; 结果00123

第二题：select case when length('1234567')>5 then '1234567' else lpad('1234567',5,'0') end from dual; 结果1234567

select case when length('1234')>5 then '1234' else lpad('1234,5,'0') end from dual; 结果01234

实验通过了，望采纳

1select 学号，姓名， 专业名称， 总学分

from xsb, zyb;

2 alter table xsb add 身高 int;

3insert into xsb values ('001', 姓名, null, null, null);

4 create view XX (学号， 姓名， 专业)

as

select 学号， 姓名， 专业

from xsb, zyb;

Q : This symbol When you put infront of a line in the parameter file signifies a ment $ @ # ! Q : When you change a parameter value in the parameter file when will that change takes affect Immediately after saving the parameter file At the first CHECKPOINT after saving the paramter file When the DBWR finishes writing all the dirty buffers to the disk At the next instance startup Q : ALTER SYSTEM DEFFERED mand modifies the global parameters for existing sessions after a certain amount of time new sessions only existing and new sessions depends on the SPIN_COUNT initialization parameter Q : The location where debugging trace files for back ground processes are written is specified by LOGFILE_DEST ORACLE_HOME BACKGROUND_DUMP_DEST CORE_DUMP_DEST Q : In case of heavy contention for latches set the LOG_SIMULTANEOUS_COPIES initialization parameter to Twice the number of CPUs Same as the DB Block Buffers Same as the Shared Pool Size None of the above Q : What is the first step in manually creating a new database Startup an instance Start SQLPlus and connect to Oracle as SYSDBA Check the instance identifier for your system Create a parameter file Q : Which of the following is true regarding control files Oracle remeds atleast o control files stored on o separate disks Oracle remeds atleast o control files stored on one disk Oracle remeds to store one control file One control file is not enough to run a database Q : Tom created a database with a DB_BLOCK_SIZE of k he wants to increase this to k what is his next step Issue ALTER SYSTEM SET DB_BLOCK_SIZE= k mand recreate the database with the new setting It can be done in both the ways the DB_BLOCK_SIZE cannot be k Q : Howmany rollback segments are required for Oracle to startup apart from SYSTEM rollback segment Oracle can start with just the system rollback segment Oracle Needs atleast rollback segments before it can start Oracle Needs a Temp Rollback Segment before it can start None of the above Q : The unit of measurement for DB_BLOCK_SIZE intialization parameter is BLOCKS BYTE PAGE ROW Q : This tablespace is a must before you run the database instance ROLLBACK TOOLS TEMP SYSTEM Q : Which initialization parameter determines the rollback segments that can be used by Oracle ROLLBACKS LOGFILE GROUP ROLLBACK_SEGMENTS DBA_ROLLBACK_SEGS Q : Which of the following is a valid but undocumented parameter in Oracle _CORRUPT_RBS _CORRUPT_REDO _CORRUPT_ROLLBACK_SEGMENTS None of the above lishixinzhi/Article/program/Oracle/201311/17773

以上就是关于求解答:oracle数据库的题目.全部的内容，包括:求解答:oracle数据库的题目.、关于Oracle数据库编程题的解答。谢谢各位！、oracle 数据库中以下几个题的sql语句等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！