Oracle SQL 数据库简单代码题

第一题 更改用户密码的代码就是你写的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 是题？？

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

答案如下,

如果不想与另一个表产关联可以直接decode

with a as(

select distinct hci_id, regexp_substr(hstu_ids, '[^,]+', 1, level) as cl1

from pm_ci h

connect by level <=

length(hstu_ids) - length(replace(hstu_ids, ',', '')) + 1), b as (select qstu_id,

max(qstu_name) name

from pm_stu q

group by qstu_id)

select distinct aci_id,

wmsyswm_concat(decode(acl1,

'1',

(select bname

from b

where bstu_id = '1'),

'2',

(select bname

from b

where bstu_id = '2'),

'3',

(select bname

from b

where bstu_id = '3'),

'4',

(select bname

from b

where bstu_id = '4'))) over(partition by ci_id) tt

from a

第二种方法

select a, c

from (with test as (select tci_id a, tstu_ids c from pm_ci t)

select a,

substr(tca,

instr(tca, ',', 1, clv) + 1,

instr(tca, ',', 1, clv + 1) -

(instr(tca, ',', 1, clv) + 1)) AS c

from (select a,

',' || c || ',' AS ca,

length(c || ',') - nvl(length(REPLACE(c, ',')), 0) AS cnt

FROM test) t,

(select LEVEL lv from dual CONNECT BY LEVEL <= 100) c

where clv <= tcnt)

其它的同上面一样,level<=100表示循环次数,这个可以取C的长度除以2

=========================================================================

第三种方法

create or replace function getNote(v_content in varchar2)

return varchar2 is

result varchar2(8000);

cursor res is select from pm_stu;

begin

result:=v_content;

for i in res loop

result:= replace(result,istu_id,istu_name);

end loop;

return result;

end getNote;

调用

select aci_id,getNote(astu_ids) from pm_ci a

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;

那他提的这些要求，就是你实现数据库对应表的信息了。比如，职工信息，那肯定是职工信息表了。字段就包括，编号，姓名，性别，年龄，职称等。以此类推。出勤记录，肯定是要实现员工上下班的，那么出勤表，肯定有一个职工信息表的引用，不用说了，肯定是主键ID。一次类推，那么出差信息也是一样的。另外我想说的是，如果这么设计的话，可能有点不满足数据库范式要求。需要自己再揣摩下，祝你成功

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: delet from cw_dw_pz where A003='000000';

2: select from gz_gr_zz where A044<0;

3: delete from cw_dw_pz where p002<to_date('2015-01-01','yyyy-mm-dd');

4

--建立表 和删除表;

DROP TABLE watest；

CREATE TABLE watest

(

A001 VARCHAR2(10),

A002 VARCHAR2(10),

A003 VARCHAR2(10),

A008 VARCHAR2(10)

);

---插入watest语句块;

INSERT INTO DEPT VALUES

('test','test','test','test');

--增加A004字段

alter table watest add A004 VARCHAR2(10); #给表watest增加一名为A004的列。

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

既然求各部门总雇员数，应该要分组。你的语句没有分组选项。

各部门的雇员数：

select deptno,count(deptno) from emp group by deptno;

部门雇员大于1：

select deptno,count(deptno) from emp having count()>1 group by deptno;

以上就是关于Oracle SQL 数据库简单代码题全部的内容，包括:Oracle SQL 数据库简单代码题、oracle数据库一道面试题、Oracle的一道练习题：假定有一个XSCJ数据库，其中有一个学生基本数据表XSB和一张专业表ZYB等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！