数据库实验

实验网站

http://211.87.227.203:7088/#/ （主站）
http://211.87.227.202:7088/#/
http://211.87.227.201:7088/#/

数据库实验4

CREATE TABLE test4_01 AS
SELECT * FROM bk.deptor3;

UPDATE test4_01
SET pid = SUBSTR(pid,1,6)
          || TO_CHAR(birthday,'YYYYMMDD')
          || SUBSTR(pid,15);

UPDATE test4_01
SET pname = REGEXP_REPLACE(pname,'[A-Za-z]','');

UPDATE test4_01
SET pname = REGEXP_REPLACE(pname,' ','');
 
UPDATE test4_01
SET pname = REGEXP_REPLACE(pname,'[0-9]','');

UPDATE test4_01
SET pname = REGEXP_REPLACE(pname,'-','');

UPDATE test4_01
SET pname = REPLACE(pname,'+','');

UPDATE test4_01
SET sex = REGEXP_REPLACE(sex,'.*女.*','女');

UPDATE test4_01
SET sex = REGEXP_REPLACE(sex,'.*男.*','男');

UPDATE test4_01
SET sex = NULL
WHERE REGEXP_LIKE(sex,'^[ ]*$');

CREATE TABLE test4_02 AS
SELECT *
FROM bk.deptor3;

UPDATE test4_02
SET age = 2019 - EXTRACT(YEAR FROM birthday)
WHERE 2019 - EXTRACT(YEAR FROM birthday) < age;

CREATE TABLE test4_03 AS
SELECT *
FROM bk.deptor3;

ALTER TABLE test4_03 ADD (
  total_count   NUMBER(6),
  total_amount  NUMBER(10,2),
  avg_amount    NUMBER(10,2)
);

UPDATE test4_03 t
SET (total_count, total_amount, avg_amount) = (
  SELECT NVL(COUNT(*), 0),
         NVL(SUM(d.amount), 0),
         NVL(AVG(d.amount), 0)
  FROM bk.deposit d
  WHERE d.pid = t.pid
);

CREATE TABLE test4_04 AS
SELECT *
FROM bk.deptor3;

ALTER TABLE test4_04 ADD (
  total_count   NUMBER(6),
  total_amount  NUMBER(10,2),
  avg_amount    NUMBER(10,2)
);

UPDATE test4_04 t
SET (total_count, total_amount, avg_amount) = (
  SELECT NVL(COUNT(*), 0),
         NVL(SUM(d.amount), 0),
         NVL(AVG(d.amount), 0)
  FROM bk.deposit d
  WHERE d.pid = t.pid
)
WHERE EXISTS (
  SELECT 1
  FROM bk.deposit d
  WHERE d.pid = t.pid
);

CREATE TABLE test4_05 AS
SELECT * FROM bk.deptor3;

ALTER TABLE test4_05 ADD (
  total_count   NUMERIC(6),
  total_amount  NUMERIC(10,2),
  avg_amount    NUMERIC(10,2)
);

UPDATE test4_05 t
SET total_amount = CASE 
    WHEN t.parentpid IN (
      SELECT d.pid 
      FROM bk.deposit d
      WHERE d.pid = t.parentpid
    )
    THEN NVL((SELECT SUM(c.amount) FROM bk.deposit c WHERE c.pid = t.parentpid),0)
       + NVL((SELECT SUM(b.amount) FROM bk.deposit b WHERE b.pid = t.pid),0)
    ELSE NVL((SELECT SUM(b.amount) FROM bk.deposit b WHERE b.pid = t.pid),0)
  END;

UPDATE test4_05 t
SET total_count = 
    NVL((SELECT COUNT(*) FROM bk.deposit d WHERE d.pid = t.pid),0)
  + NVL((SELECT COUNT(*) FROM bk.deposit p WHERE p.pid = t.parentpid),0);

UPDATE test4_05
SET avg_amount = ROUND(total_amount / NULLIF(total_count,0), 2);


CREATE TABLE test4_06 AS
SELECT * FROM bk.deptor3;

ALTER TABLE test4_06 ADD (
  total_count   NUMERIC(6),
  total_amount  NUMERIC(10,2),
  avg_amount    NUMERIC(10,2)
);

UPDATE test4_06 t
SET total_count = 
    (SELECT COUNT(*) FROM bk.deposit d WHERE d.pid = t.pid)
  + (SELECT COUNT(*) FROM bk.deposit p WHERE p.pid = t.parentpid)
WHERE EXISTS (
  SELECT 1 FROM bk.deposit p WHERE p.pid = t.parentpid
);

UPDATE test4_06 t
SET total_amount =
    NVL((SELECT SUM(c.amount)  FROM bk.deposit c WHERE c.pid = t.parentpid),0)
  + NVL((SELECT SUM(b.amount)  FROM bk.deposit b WHERE b.pid = t.pid),0)
WHERE EXISTS (
  SELECT 1 FROM bk.deposit p WHERE p.pid = t.parentpid
);

UPDATE test4_06
SET avg_amount = ROUND(total_amount / NULLIF(total_count,0), 2)
WHERE total_count > 0;


CREATE TABLE test4_07 AS
SELECT * FROM bk.deptor3;

ALTER TABLE test4_07 ADD (
  total_interest  NUMERIC(10,2)
);

UPDATE test4_07 t
SET total_interest = (
  SELECT ROUND(
           SUM(
             ROUND(d.amount * b.dailyrate
                   * (TRUNC(SYSDATE) - TRUNC(d.dtime)),2)
           ),2)
  FROM bk.deposit d
  JOIN bk.bank b ON b.bid = d.bid
  WHERE d.pid = t.pid
);


CREATE TABLE test4_08 AS
SELECT * FROM bk.deptor3
WHERE sex IN ('男','女');

ALTER TABLE test4_08 ADD (
  birthday1  DATE
);

UPDATE test4_08 t1
SET birthday1 = (
  SELECT MAX(t2.birthday)
  FROM test4_08 t2
  WHERE TO_CHAR(t1.birthday,'MMDD') = TO_CHAR(t2.birthday,'MMDD')
);


CREATE TABLE test4_09 AS
SELECT * FROM bk.deptor3
WHERE sex IN ('男','女');

ALTER TABLE test4_09 ADD (
  pid1  CHAR(18)
);

UPDATE test4_09 t1
SET pid1 = (
  SELECT MAX(t2.pid)
  FROM test4_09 t2
  WHERE t2.birthday = (
    SELECT MAX(t3.birthday)
    FROM test4_09 t3
    WHERE TO_CHAR(t1.birthday,'MMDD') = TO_CHAR(t3.birthday,'MMDD')
  )
);


CREATE TABLE test4_10 AS
SELECT * FROM bk.deptor3
WHERE sex IN ('男','女');

ALTER TABLE test4_10 ADD (
  pid1  CHAR(18)
);

UPDATE test4_10 t1
SET pid1 = (
  SELECT MAX(t2.pid)
  FROM test4_10 t2
  WHERE t2.birthday = (
    SELECT MAX(t3.birthday)
    FRO