Bài tập chương 4 & 5

--1--
--Ðã tAo Database CuocDT voi pass là cdt
--2--
--Tao Tablespace không quan tâm den datafile, thuoc small file
--Extent: tu dong tang dung luong
--DICTIONARY: Chi ra tablespace duocc quan tri boi Data Dictionary.
--LOCAL: Chi ra tabespace duocc quan tri Locally voi 1 bitmap. --> chon local
alter system set
db_create_file_dest = 'E:\Ki7\Oracle\Projects\';
create tablespace TBSPcuocdienthoai
logging
datafile 'userdata01.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;
--3--
CREATE TABLE DSKH(
MAKH NVARCHAR2(5) CONSTRAINT MAKH_PK PRIMARY KEY,
HOTEN NVARCHAR2(50),
DIACHI NVARCHAR2(100)
);
CREATE TABLE CUOCGOI(
MAKH NVARCHAR2(5),
MATINH NVARCHAR2(5),
SOPHUT NUMBER,
NGAYGOI DATE,
CONSTRAINT MAKH_FK FOREIGN KEY (MAKH)REFERENCES DSKH(MAKH),
CONSTRAINT MATINH_FK FOREIGN KEY (MATINH) REFERENCES TINH(MATINH)
);
CREATE TABLE TINH(
MATINH NVARCHAR2(5) CONSTRAINT MATINH_PK PRIMARY KEY,
TENTINH NVARCHAR2(20),
DONGIA NUMBER
);
--NHAP DU LIEU--
INSERT INTO DSKH
VALUES('KH001','THACH QUANG THAI','HA NOI');
INSERT INTO DSKH
VALUES('KH002','THACH QUANG A','HAI DUONG');
INSERT INTO DSKH
VALUES('KH003','THACH QUANG B','HA GIANG');
INSERT INTO DSKH
VALUES('KH004','THACH QUANG C','HA NAM');
INSERT INTO DSKH
VALUES('KH005','THACH QUANG D','HA TAY');


INSERT INTO TINH
VALUES('T0001','BAC NINH',100);
INSERT INTO TINH
VALUES('T0002','BAC GIANG',150);
INSERT INTO TINH
VALUES('T0003','HAI DUONG',200);
INSERT INTO TINH
VALUES('T0004','NAM DINH',250);

INSERT INTO CUOCGOI
VALUES('KH001','T0002',100,TO_DATE('26/10/2014','DD/MM/YYYY'));
INSERT INTO CUOCGOI
VALUES('KH001','T0003',100,TO_DATE('26/10/2014','DD/MM/YYYY'));
INSERT INTO CUOCGOI
VALUES('KH001','T0004',100,TO_DATE('26/10/2014','DD/MM/YYYY'));
INSERT INTO CUOCGOI
VALUES('KH001','T0003',100,TO_DATE('26/10/2014','DD/MM/YYYY'));
INSERT INTO CUOCGOI
VALUES('KH001','T0001',100,TO_DATE('26/10/2014','DD/MM/YYYY'));
INSERT INTO CUOCGOI
VALUES('KH002','T0002',100,TO_DATE('26/10/2014','DD/MM/YYYY'));
INSERT INTO CUOCGOI
VALUES('KH003','T0002',100,TO_DATE('26/09/2014','DD/MM/YYYY'));
INSERT INTO CUOCGOI
VALUES('KH005','T0002',100,TO_DATE('26/11/2014','DD/MM/YYYY'));
INSERT INTO CUOCGOI
VALUES('KH004','T0002',100,TO_DATE('26/01/2014','DD/MM/YYYY'));
INSERT INTO CUOCGOI
VALUES('KH003','T0002',100,TO_DATE('26/09/2014','DD/MM/YYYY'));
INSERT INTO CUOCGOI
VALUES('KH003','T0002',100,TO_DATE('26/09/2014','DD/MM/YYYY'));
INSERT INTO CUOCGOI
VALUES('KH003','T0001',100,TO_DATE('26/09/2014','DD/MM/YYYY'));
INSERT INTO CUOCGOI
VALUES('KH003','T0003',100,TO_DATE('20/10/2014','DD/MM/YYYY'));
INSERT INTO CUOCGOI
VALUES('KH003','T0004',100,TO_DATE('26/10/2014','DD/MM/YYYY'));


--4--
--TRONG SLIDE CHUONG 4 - TRANG 15
--5--
--TABLESPACE: SYSTEM,SYSAUX
--6--
--TABLESPACE: SYSTEM,SYSAUX
--XEM THONG TIN SGA VA PGA O EM
--TRONG VUNG NHO SGA, DATA DICTIONARY CACHE CHIEM NHIEU NHAT, SHARED POOL CHIEM IT NHAT
--NAM O ORADATA
--CAC FILE NAY TU SINH RA
--7--
--KHONG NEN NHAP THU CONG BANG TAY
--CO THE DUNG LENH NHAP TU DONG CHO COT MAKH
--8--
--A--
SELECT *
FROM DSKH
WHERE MAKH='KH001';
--B--
SELECT *
FROM (SELECT CUOCGOI.MATINH,TINH.TENTINH,COUNT(CUOCGOI.MATINH) AS SL
FROM TINH JOIN CUOCGOI ON TINH.MATINH=CUOCGOI.MATINH
GROUP BY CUOCGOI.MATINH,TINH.TENTINH)
ORDER BY SL DESC;
--C--
SELECT CUOCGOI.MAKH,SUM(CUOCGOI.SOPHUT*TINH.DONGIA) AS THANHTIEN
FROM CUOCGOI JOIN TINH ON CUOCGOI.MATINH = TINH.MATINH
WHERE EXTRACT(MONTH FROM CUOCGOI.NGAYGOI)=10
GROUP BY CUOCGOI.MAKH;
--D--
SELECT CUOCGOI.MAKH,DSKH.HOTEN
FROM DSKH JOIN CUOCGOI ON DSKH.MAKH=CUOCGOI.MAKH
WHERE EXTRACT(MONTH FROM CUOCGOI.NGAYGOI)=10
GROUP BY CUOCGOI.MAKH,DSKH.HOTEN
HAVING SUM(CUOCGOI.SOPHUT)>100;
--9--
CREATE TEMPORARY TABLESPACE TBSPTAM
TEMPFILE 'E:\OLP\ORADATA\CUOCDT\TEMP001.DBF' SIZE 2G;
CREATE GLOBAL TEMPORARY TABLE TBTAM(
MAKH NVARCHAR2(5),
TENKH NVARCHAR2(50)
) ON COMMIT DELETE ROWS;
--DU LIEU O BANG TAM TBTAM SE MAT KHI KET THUC MOT GIAO DICH. CO THE THAO TAC DU LIEU TREN TBTAM NHU CAC BANG BINH THUONG
--CHE DO PRIVATE