أ.انس قصار
05-16-2008, 11:30 AM
إلى طلاب السنة الثانية هندسة فيما يلي حل أسئلة دورة الأوركل للفصل الأول للعام الدراسي 2007 - 2008
First Question:
1-
Select V.ptno
From Patients P, Visitors V
Where (v.ptno = V.ptno) And (to_char (vdate, ‘yyyy’) = ‘2007’) And (p.gender = ‘ ذكر’) And (name Like ‘أ%’ Or Name Like ‘ا%’ Or Name Like ‘آ%’ Or Name Like ‘إ%’);
2-
Select C.cno, C.name, Count (d.dno)
From Clinic C, Doctors D
Where C.cno = D.cno
Group By C.cno, C.name;
3-
Select D.dno, D.name
From Doctors D
Where (d.dno, D.sal) = (select Dno, Max (sal) From Doctors Where Dno = D.dno Group By Dno);
4-
Select D.cno, Sum (decode (upper (p.gender), ‘male’, 1, 0)), Sum (decode (upper (p.gender), ‘female’, 1, 0))
From Doctors D, Patients P, Visitors V
Where (p.ptno = V.ptno) And (d.dno = V.dno) And (to_char (v.vdate, ‘yyyy’) = ‘2007’)
Group By D.cno;
5-
Select D.cno, Count (v.ptno) * 500
From Doctors D, Visitors V
Where D.dno = V.dno
Group By D.cno;
SECOND QUESTION:
1-
CREATE VIEW VIEW_1 AS
SELECT SUM (SUBTOTAL) TOTAL
FROM (
SELECT SUM (COUNT (V.PTNO) * C.TPRICE) SUBTOTAL
FROM CLINIC C, VISITORS V, DOCTORS D, PATIENTS P
WHERE P.PTNO = V.PTNO AND V.DNO = D.DNO AND C.CNO = D.CNO AND
(V.VDATE - P.BDATE) / 365 >= 10
GROUP BY C.TPRICE
UNION ALL
SELECT SUM (COUNT (V.PTNO) * C.TPRICE / 2)
FROM CLINIC C, VISITORS V, DOCTORS D, PATIENTS P
WHERE P.PATNO = V.PTNO AND V.DNO = D.DNO AND C.CNO = D.CNO AND
(V.VDATE - P.BDATE) /365 < 10
GROUP BY C.TPRICE);
2-
CREATE VIEW VIEW_2 AS
SELECT C.CNO, C.NAME, COUNT (V.PTNO) * C.TPRICE – SUM (D.SAL) NET_TOTAL
FROM VISITORS V, DOCTORS D, CLINIC C
WHERE V.DNO = D.DNO AND D.CNO = C.CNO AND
TO_CHAR (V.VDATE, 'MM') = DECODE (TO_CHAR (SYSDATE, 'MM') – 1, 0, 12, TO_CHAR (SYSDATE, 'MM') – 1)
GROUP BY C.CNO, C.NAME, C.TPRICE;
3-
CREATE VIEW VIEW_3 AS
SELECT DPNAME
FROM (
SELECT DNO, NAME DPNAME, 1
FROM DOCTORS
UNION ALL
SELECT DNO, NAME, 2
FROM VISITORS, PATIENTS
WHERE VISITORS.PTNO (+) = PATIENTS.PTNO
ORDER BY 1, 3, 2);
THIRD QUESTION:
CREATE TABLE DOCTORS (
DNO NUMBER (4) PRIMARY KEY,
NAME VARCHAR2 (20),
LAST VARCHAR2 (20),
SAL NUMBER (7, 2) DEFAULT (20000),
CNO NUMBER (4) REFERENCES CLINIC (CNO) NOT NULL,
UNIQUE (NAME, LAST),
CHECK (SAL BETWEEN 20000 AND 40000));
FOURTH QUESTION:
CREATE USER USER4 INDENTIFIED BY USER4;
GRANT CREATE SESSION TO USER4 WITH ADMIN OPTION;
GRANT CREATE TABLE, CREATE USER TO USER4;
ALTER USER USER4 QUOTA 100M ON USERS;
GRANT INSERT, DELETE, UPDATE (DNO, DIAGNOSIS) ON VISITORS TO USER4;
FIFTH QUESTION:
CREATE OR REPLACE PROCEDURE PRINT_CLINIC_INFO (P_CNO INOUT NUMBER)
IS
CURSOR CLINIC_CUR IS SELECT P.NAME, P.LAST FROM PATIENTS P, DOCTORS D, VISITORS V WHERE D.CNO = P_CNO AND D.DNO = V.DNO AND P.PTNO = V.PTNO;
COUNTER NUMBER (5);
V_NAME CLINIC.NAME%TYPE;
V_TPRICE CLINIC.TPRICE%TYPE;
BEGIN
SELECT CLINIC.NAME, CLINIC.TPRICE INTO V_NAME, V_TPRICE FROM CLINIC WHERE CLINIC.CNO = P_CNO;
COUNTER := 0;
FOR CLINIC_REC IN CLINIC_CUR LOOP
COUNTER := COUNTER + 1;
DBMS_OUTPUT.PUT_LINE (CLINIC_REC.NAME || CLINIC_REC.LAST);
END LOOP;
P_CNO := COUNTER * V_TPRICE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE (“CLINIC NOT EXISTS”);
P_CNO := 0;
END EXCEPTION;
END PROCEDURE;
أرجو الاستفادة للجميع وفي حال وجود أي أخطاء يرجى ابلاغي عنها
مع تمنياتي للجميع بالنجاح والتخرج
First Question:
1-
Select V.ptno
From Patients P, Visitors V
Where (v.ptno = V.ptno) And (to_char (vdate, ‘yyyy’) = ‘2007’) And (p.gender = ‘ ذكر’) And (name Like ‘أ%’ Or Name Like ‘ا%’ Or Name Like ‘آ%’ Or Name Like ‘إ%’);
2-
Select C.cno, C.name, Count (d.dno)
From Clinic C, Doctors D
Where C.cno = D.cno
Group By C.cno, C.name;
3-
Select D.dno, D.name
From Doctors D
Where (d.dno, D.sal) = (select Dno, Max (sal) From Doctors Where Dno = D.dno Group By Dno);
4-
Select D.cno, Sum (decode (upper (p.gender), ‘male’, 1, 0)), Sum (decode (upper (p.gender), ‘female’, 1, 0))
From Doctors D, Patients P, Visitors V
Where (p.ptno = V.ptno) And (d.dno = V.dno) And (to_char (v.vdate, ‘yyyy’) = ‘2007’)
Group By D.cno;
5-
Select D.cno, Count (v.ptno) * 500
From Doctors D, Visitors V
Where D.dno = V.dno
Group By D.cno;
SECOND QUESTION:
1-
CREATE VIEW VIEW_1 AS
SELECT SUM (SUBTOTAL) TOTAL
FROM (
SELECT SUM (COUNT (V.PTNO) * C.TPRICE) SUBTOTAL
FROM CLINIC C, VISITORS V, DOCTORS D, PATIENTS P
WHERE P.PTNO = V.PTNO AND V.DNO = D.DNO AND C.CNO = D.CNO AND
(V.VDATE - P.BDATE) / 365 >= 10
GROUP BY C.TPRICE
UNION ALL
SELECT SUM (COUNT (V.PTNO) * C.TPRICE / 2)
FROM CLINIC C, VISITORS V, DOCTORS D, PATIENTS P
WHERE P.PATNO = V.PTNO AND V.DNO = D.DNO AND C.CNO = D.CNO AND
(V.VDATE - P.BDATE) /365 < 10
GROUP BY C.TPRICE);
2-
CREATE VIEW VIEW_2 AS
SELECT C.CNO, C.NAME, COUNT (V.PTNO) * C.TPRICE – SUM (D.SAL) NET_TOTAL
FROM VISITORS V, DOCTORS D, CLINIC C
WHERE V.DNO = D.DNO AND D.CNO = C.CNO AND
TO_CHAR (V.VDATE, 'MM') = DECODE (TO_CHAR (SYSDATE, 'MM') – 1, 0, 12, TO_CHAR (SYSDATE, 'MM') – 1)
GROUP BY C.CNO, C.NAME, C.TPRICE;
3-
CREATE VIEW VIEW_3 AS
SELECT DPNAME
FROM (
SELECT DNO, NAME DPNAME, 1
FROM DOCTORS
UNION ALL
SELECT DNO, NAME, 2
FROM VISITORS, PATIENTS
WHERE VISITORS.PTNO (+) = PATIENTS.PTNO
ORDER BY 1, 3, 2);
THIRD QUESTION:
CREATE TABLE DOCTORS (
DNO NUMBER (4) PRIMARY KEY,
NAME VARCHAR2 (20),
LAST VARCHAR2 (20),
SAL NUMBER (7, 2) DEFAULT (20000),
CNO NUMBER (4) REFERENCES CLINIC (CNO) NOT NULL,
UNIQUE (NAME, LAST),
CHECK (SAL BETWEEN 20000 AND 40000));
FOURTH QUESTION:
CREATE USER USER4 INDENTIFIED BY USER4;
GRANT CREATE SESSION TO USER4 WITH ADMIN OPTION;
GRANT CREATE TABLE, CREATE USER TO USER4;
ALTER USER USER4 QUOTA 100M ON USERS;
GRANT INSERT, DELETE, UPDATE (DNO, DIAGNOSIS) ON VISITORS TO USER4;
FIFTH QUESTION:
CREATE OR REPLACE PROCEDURE PRINT_CLINIC_INFO (P_CNO INOUT NUMBER)
IS
CURSOR CLINIC_CUR IS SELECT P.NAME, P.LAST FROM PATIENTS P, DOCTORS D, VISITORS V WHERE D.CNO = P_CNO AND D.DNO = V.DNO AND P.PTNO = V.PTNO;
COUNTER NUMBER (5);
V_NAME CLINIC.NAME%TYPE;
V_TPRICE CLINIC.TPRICE%TYPE;
BEGIN
SELECT CLINIC.NAME, CLINIC.TPRICE INTO V_NAME, V_TPRICE FROM CLINIC WHERE CLINIC.CNO = P_CNO;
COUNTER := 0;
FOR CLINIC_REC IN CLINIC_CUR LOOP
COUNTER := COUNTER + 1;
DBMS_OUTPUT.PUT_LINE (CLINIC_REC.NAME || CLINIC_REC.LAST);
END LOOP;
P_CNO := COUNTER * V_TPRICE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE (“CLINIC NOT EXISTS”);
P_CNO := 0;
END EXCEPTION;
END PROCEDURE;
أرجو الاستفادة للجميع وفي حال وجود أي أخطاء يرجى ابلاغي عنها
مع تمنياتي للجميع بالنجاح والتخرج