Introduction to Databases
ITDR2106
Assigment-part-1
Hostel System
Introduction:
Hostel is a building for girls students in Rustaq college to stay in it and it is a
near from the college. It has 4 units and in each units it has 3 floors. In addition,
in each floor it has 10 room and one TV room. In the beginning in the hostel it
has a reception for students to have a promotion to go out and return to the
hostel. In the hostel there are many problems that girls suffer from them. The
one of them is late to get a promotion to go out the hostel. The objectives of this
system are:
1.To solve the crowded girls while they get a promotion paper to go home.
2. To exchange the promotion paper process by electronic process using
finger print.
3. The electronic process convert the information about the student from
supervisor to the guard in easy way.
Schema hostel system:
•
Hostel(SID,FULLNAME,FLOURNUM,ROOMNUM)
•
Student(SID,FNAME,LNAME,STUYEAR,STUADRESS,MROOMNUM,STUMAJOR)
•
ROOM(ROOMNUM , STUNUM )
•
•
Furniture(ROOMNUM,ITEM,FUNAME)
•
Registration(SID,SNAME,YEAR,PHONE,DATE,ROOM)
ERD:
Room
Pk
Registration
Pk
ROOMNUM
SNAME
YEAR
ROOM
PHONE
DAY
STUNUM
Hostel
Furniture
Pk
Student
ROOMNUM
FUNAME
ITEM
SID
Pk
Pk
No-Of-items
SID
SID
FNAME
LNAME
STYEAR
STUMAJOR
STUADDRESS
ROOMNUM
FULLNAME
FLOURNUM
ROOMNUM
Introduction to Databases
ITDR2106
Assigment-part-2
INSERT TABLE ROOM
CREATE TABLE ROOM(ROOMNUM NUMBER (4) PRIMARY KEY, STUNUM NUMBER (4));
INSERT INTO ROOM VALUES(1,3);
INSERT INTO ROOM VALUES(2,2);
INSERT INTO ROOM VALUES(3,2);
INSERT INTO ROOM VALUES(4,2);
INSERT INTO ROOM VALUES(5,2);
INSERT INTO ROOM VALUES(6,3);
INSERT INTO ROOM VALUES(7,2);
INSERT INTO ROOM VALUES(8,2);
INSERT INTO ROOM VALUES(9,3);
INSERT INTO ROOM VALUES(10,2);
SELECT*FROM ROOM;
INSERT TABLE STUDENT
CREATE TABLE STUDENT (SID NUMBER (10) PRIMARY KEY, FNAME VARCHAR (10),LNAME VARCHAR(10),
STYEAR NUMBER (4), STUMAJOR VARCHAR (10), STUADDRESS VARCHAR (10),ROOMNUM REFERENCES
ROOM);
INSERT INTO STUDENT VALUES(2014193021,'ASMA','SALEM',2014,'IT','ALRUSTAQ',1);
INSERT INTO STUDENT VALUES(2014193028,'AFRAH','HILAL',2014,'ENGLISH','ALRUSTAQ',1);
INSERT INTO STUDENT VALUES(2014193022,'NOOR','ALI',2014,'IT','ALRUSTAQ',1);
INSERT INTO STUDENT VALUES(2013193029,'NOUF','SALEM',2013,'IT','SOHAR',2);
INSERT INTO STUDENT VALUES(2013193023,'ASMA','ALI',2013,'IBA','SOHAR',2);
INSERT INTO STUDENT VALUES(2013193025,'YARA','NASER',2012,'IBA','SOHAR',3);
INSERT INTO STUDENT VALUES(2012193027,'SAMYA','MOHAMMED',2012,'IBA','IBRI',3);
INSERT INTO STUDENT VALUES(2014193113,'AMJAAD','DARWISH',2014,'IT','ALRUSTAQ',4);
INSERT INTO STUDENT VALUES(2014193345,'ABEER','SULIMAN',2014,'IT','SOUR',4);
INSERT INTO STUDENT VALUES(2011193002,'ASMA','SALEM',2011,'ENGLISH','IBRI',5);
INSERT INTO STUDENT VALUES(2011193003,'NOOR','NOOH',2011,'IT','MUSCAT',5);
INSERT INTO STUDENT VALUES(2011193009,'NOOF','HAMED',2011,'IBA','MUSCAT',6);
INSERT INTO STUDENT VALUES(2014193023,'SALWA','NASER',2014,'IT','NAKHAL',6);
INSERT INTO STUDENT VALUES(2012193056,'ANWAR','ALI',2012,'ENGLISH','NAKHAL',6);
INSERT INTO STUDENT VALUES(2013193654,'SARA','HAMED',2013,'IBA','SUR',7);
INSERT INTO STUDENT VALUES(2013193543,'BUTHAINA','MARHOON',2013,'ENGLISH','SUR',7);
INSERT INTO STUDENT VALUES(2013193123,'SALAMA','RASHID',2013,'IBA','MUSQAT',8);
INSERT INTO STUDENT VALUES(2013193123,'SUMAIA','ALI',2013,'IBA','SAHAM',8);
INSERT INTO STUDENT VALUES(2013193111,'REEM','AHMED',2013,'IT','SAHAM',9);
INSERT INTO STUDENT VALUES(2013193504,'MAHA','KHALID',2013,'ENGLISH','SAHAM',9);
INSERT INTO STUDENT VALUES(201319332,'NOOR','OMAR',2013,'IBA','NAKHAL',9);
INSERT INTO STUDENT VALUES(2014193650,'INTISAR','HAMED',2014,'ENGLISH','ALRUSTAQ',10);
INSERT INTO STUDENT VALUES(2014193123,'MARWA','SALEM',2014,'ENGLISH','IBRI',10);
SELECT*FROM STUDENT;
INSERT TABLE HOSTEL
CREATE TABLE HOSTEL(SID NUMBER (10) PRIMARY KEY, FULLNAME VARCHAR (20),
FLOURNUM NUMBER (3),ROOMNUM REFERENCES ROOM);
INSERT INTO HOSTEL VALUES (2013193654,'SARA HAMED', 2,7);
INSERT INTO HOSTEL VALUES (2013193543,'BUTHAINA MARHOON', 2,7);
INSERT INTO HOSTEL VALUES (2013193123,'SALAMA RASHID', 1,8);
INSERT INTO HOSTEL VALUES (201319332,'NOOR OMAR', 1,9);
INSERT INTO HOSTEL VALUES (2014193028,'AFRAH HILAL', 2,1);
INSERT INTO HOSTEL VALUES (2013193023,'ASMA ALI', 1,2);
INSERT INTO HOSTEL VALUES (2014193113,'AMJAAD DARWISH', 2,4);
INSERT INTO HOSTEL VALUES (2014193345,'ABEER SULIMAN', 2,4);
SELECT*FROM HOSTEL;
INSERT TABLE FURNITURE
CREATE TABLE FURNITURE(ROOMNUM NUMBER (4) PRIMARY KEY, FUNAME VARCHAR(10), ITEM
NUMBER(5));
INSERT INTO FURNITURE VALUES(10,'TABLE', 1);
INSERT INTO FURNITURE VALUES(9,'TABLE', 2);
INSERT INTO FURNITURE VALUES(8,'CHAIR', 1);
INSERT INTO FURNITURE VALUES(7,'TABLE', 2);
INSERT INTO FURNITURE VALUES(6,'CHAIR', 1);
INSERT INTO FURNITURE VALUES(5,'TABLE', 3);
INSERT INTO FURNITURE VALUES(4,'CHAIR', 2);
INSERT INTO FURNITURE VALUES(3,'TABLE', 3);
INSERT INTO FURNITURE VALUES(2,'CHAIR', 2);
INSERT INTO FURNITURE VALUES(1,'CHAIR', 2);
select *from FURNITURE;
INSERT TABLE REGISTRATION
CREATE TABLE REGISTRATION(
SID NUMBER(10)PRIMARY KEY,
SNAME VARCHAR2(15),
ROOM NUMBER(3),
YEAR NUMBER(4),
PHONE NUMBER(9),
DAY DATE
);
INSERT INTO REGISTRATION VALUES(2013193654,'SARA',7,2014,91827646,'25/october/2015');
INSERT INTO REGISTRATION VALUES(2014193021,'ASMAA',7,2014,97142525,'10/october/2015');
INSERT INTO REGISTRATION VALUES(2014193123,'MARWA',10,2011,92183774,'22/october/2015');
INSERT INTO REGISTRATION VALUES(2013193111,'REEM',9,2012,92838741,'1/october/2015');
INSERT INTO REGISTRATION VALUES(2011193009,'NOOF',6,2013,91773646,'1/november/2015');
INSERT INTO REGISTRATION VALUES(2013193025,'YARA',3,2013,97176366,'15/october/2016');
INSERT INTO REGISTRATION VALUES(2014193028,'AFRAH',1,2011,91887277,'5/november/2016');
SELECT*FROM REGISTRATION;
1. select a horizontal and vertical subset of a table
SELECT SID ,FULLNAME, FLOURNUM, ROOMNUM FROM HOSTEL WHERE SID=2013193123;
SELECT FULLNAME FROM HOSTEL;
2 ) join 2 tables and project a reasonable subset of the columns
SELECT * FROM STUDENT NATURAL JOIN REGISTRATIONN ;
3. a SELECT using LIKE on a text field
SELECT * FROM HOSTEL WHERE FULLNAME LIKE 'A%' ;
SELECT * FROM HOSTEL WHERE FULLNAME LIKE '%A%' ;
4. a query using GROUP BY
SELECT STUADDRESS FROM STUDENT GROUP BY STUADDRESS;
5. a query using HAVING
SELECT SID FROM STUDENT GROUP BY SID HAVING SID='2014193345';
6. a query using ORDER BY
SELECT FUNAME, ITEM FROM FURNITURE ORDER BY ITEM;
7. a query using a subquery
SELECT FNAME, STYEAR FROM STUDENT WHERE STYEAR IN (SELECT STYEAR FROM ROOM WHERE
STYEAR=2014);
8. a query using a correlated subquery
SELECT a.FNAME,a.LNAME,a.STYEAR,a.SID
FROM STUDENT a
WHERE a.SID=(
SELECT b.SID
FROM REGISTRATIONN b WHERE b.PHONE='91887277');
9. a query involving COUNT(*)
SELECT COUNT (*)FROM REGISTRATIONN;
10. a query involving another aggregate function
SELECT MAX(ITEM)FROM FURNITURE;
11. a selective DELETE
DELETE FROM STUDENT WHERE SID='2013193654';
12. a selective UPDATE
UPDTE FURNITURE SET ITEM= '2';
13. a SELECT on a DATE column (note: use the TO_DATE function)
SELECT SNAME,DAY FROM REGISTRATION WHERE DAY>=('01-NOV-15') ORDER BY DAY;
14. a SELECT using an inequality
SELECT SID,FULLNAME,ROOMNUM FROM HOSTEL WHERE FLOURNUM=1;
Introduction to Databases
ITDR2106
Assigment-part-3
*Trigger change the number of room when student change her room
CREATE TABLE CHANGEROOMMM AS SELECT * FROM ROOM WHERE
ROOMNUM=2;
CREATE OR REPLACE TRIGGER ROOM_CHANGE3
AFTER UPDATE OF ROOMNUM
ON ROOM
FOR EACH ROW
BEGIN
INSERT INTO CHANGEROOMMM VALUES(:OLD.ROOMNUM,:OLD.STUNUM);
END;
/
SELECT*FROM CHANGEROOM;
UPDATE CHANGEROOM SET ROOMNUM=3 WHERE STUNUM=2;
SELECT*FROM CHANGEROOM;
Trigger remove old (GRADUATE) STUDENT from student table to new table called
GRADUATE_STUDENTT
CREATE TABLE GRADUATE_STUDENTT(ID NUMBER (12) PRIMARY KEY, STUDENTNAME VARCHAR(20),
PHONE NUMBER (9));
CREATE or REPLACE TRIGGER OLD_STUDENT_trigger
AFTER INSERT
ON STUDENT
FOR EACH ROW
BEGIN
INSERT INTO GRADUATE_STUDENTT
VALUES
(:old.ID,
:old.STUDENTNAME,
:old.PHONE);
END;
insert into GRADUATE_STUDENTT values(2011193003,'muna',91122332);
select*from GRADUATE_STUDENTT;
*Cursor RETURN THE STUDENT ID, YEAR AND MAJOR
DECLARE
CURSOR STU IS SELECT * FROM STUDENT;
DREC STUDENT%ROWTYPE;
BEGIN
IF NOT STU%ISOPEN THEN
OPEN STU;
END IF;
FETCH STU INTO DREC ;
WHILE STU%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(DREC.SID||' ' || DREC.STYEAR||' '|| DREC.STUMAJOR);
FETCH STU INTO DREC ;
END LOOP;
END;
/
*cursor RETYRN FIRST NAME AND LAST NAME OF STUDENT WHEN SEND ROOM NUMBER
declare cursor Student_cursor is
select FNAME ,LNAME
from STUDENT
where ROOMNUM in (1);
begin for i in Student_cursor loop
DBMS_OUTPUT.PUT_line('FNAME: '||i.FNAME||' LNAME: '||i.LNAME);
end loop;
end;
FUNCTION RETURN THE ITEM IN EVERY ROOM WHEN SEND ROOM NUMBER
CREATE OR REPLACE FUNCTION FUN_ROOM(S FURNITURE.ROOMNUM%TYPE)
RETURN VARCHAR
IS
NS FURNITURE.ITEM%TYPE;
BEGIN
SELECT ITEM INTO NS FROM FURNITURE WHERE ROOMNUM=1;
RETURN NS;
END;
DECLARE
S FURNITURE.ITEM%TYPE;
BEGIN
S:=FUN_ROOM(4);
DBMS_OUTPUT.PUT_LINE('THE NUMBER OF ITEM IS:' ||S);
END;
/
Function RETURN NUMBER OF ROOM WHEN SEND STUDENT ID
CREATE OR REPLACE FUNCTION FUN_STUDENT(S STUDENT.SID%TYPE)
RETURN VARCHAR
IS
NS STUDENT.ROOMNUM%TYPE;
BEGIN
SELECT ROOMNUM INTO NS FROM STUDENT WHERE SID=2014193021;
RETURN NS;
END;
DECLARE
S STUDENT.ROOMNUM%TYPE;
BEGIN
S:=FUN_STUDENT(2014193021);
DBMS_OUTPUT.PUT_LINE('THIS STUDENT IS IN ROOM NUMBER :' ||S);
END;
/
PROCEDURE RETURN NAME OF STUDENT WHEN SEND STUDENT ID
CREATE OR REPLACE PROCEDURE PRC_DNAME IS
DN STUDENT.FNAME%TYPE;
BEGIN
SELECT FNAME INTO DN FROM STUDENT WHERE SID=2014193021;
DBMS_OUTPUT.PUT_LINE(DN);
END;
/
BEGIN
PRC_DNAME;
END;
/
PROCEDURE RETURN DATE OF BOOKING THE ROOM WHEN SEND STUDENT NAME
CREATE OR REPLACE PROCEDURE PRC_REG(A REGISTRATION.ROOM%TYPE) IS
RE REGISTRATION.DAY%TYPE;
BEGIN
SELECT DAY INTO RE FROM REGISTRATION WHERE SNAME='SARA';
DBMS_OUTPUT.PUT_LINE(RE);
END;
/
BEGIN
PRC_REG(7);
END;
/
Package CONTAIN 2 function
CREATE OR REPLACE PACKAGE package_stu AS
FUNCTION FUN_STUDENT(S STUDENT.SID%TYPE)
RETURN VARCHAR;
FUNCTION FUN_ROOM(S FURNITURE.ROOMNUM%TYPE)
RETURN VARCHAR;
END PACKAGE_STU;
/
CREATE OR REPLACE PACKAGE BODY package_stu AS
FUNCTION FUN_ROOM(S FURNITURE.ROOMNUM%TYPE)
RETURN VARCHAR
IS
NS FURNITURE.ITEM%TYPE;
BEGIN
SELECT ITEM INTO NS FROM FURNITURE WHERE ROOMNUM=1;
RETURN NS;
END;
FUNCTION FUN_STUDENT(S STUDENT.SID%TYPE)
RETURN VARCHAR
IS
NS STUDENT.ROOMNUM%TYPE;
BEGIN
SELECT ROOMNUM INTO NS FROM STUDENT WHERE SID=2014193021;
RETURN NS;
END;
End;
SELECT
package_stu.FUN_ROOM(1) FROM DUAL;
PACKAGE CONTAIN 2 PROCEDURE
CREATE OR REPLACE PACKAGE package_pr_HOSTEL AS
PROCEDURE PRC_DNAME ;
PROCEDURE PRC_REG(A REGISTRATION.ROOM%TYPE);
END package_pr_HOSTEL;
/
CREATE OR REPLACE PACKAGE BODY package_pr_HOSTEL AS
PROCEDURE PRC_REG(A REGISTRATION.ROOM%TYPE) IS
RE REGISTRATION.DAY%TYPE;
BEGIN
SELECT DAY INTO RE FROM REGISTRATION WHERE SNAME='SARA';
DBMS_OUTPUT.PUT_LINE(RE);
END;
PROCEDURE PRC_DNAME IS
DN STUDENT.FNAME%TYPE;
BEGIN
SELECT FNAME INTO DN FROM STUDENT WHERE SID=2014193021;
DBMS_OUTPUT.PUT_LINE(DN);
END;
End;
BEGIN
package_pr_HOSTEL.PRC_DNAME;
END;
Introduction to Databases
ITDR2106
Assigment-part-1
Hostel System
Introduction:
Hostel is a building for girls students in Rustaq college to stay in it and it is a
near from the college. It has 4 units and in each units it has 3 floors. In addition,
in each floor it has 10 room and one TV room. In the beginning in the hostel it
has a reception for students to have a promotion to go out and return to the
hostel. In the hostel there are many problems that girls suffer from them. The
one of them is late to get a promotion to go out the hostel. The objectives of this
system are:
1.To solve the crowded girls while they get a promotion paper to go home.
2. To exchange the promotion paper process by electronic process using
finger print.
3. The electronic process convert the information about the student from
supervisor to the guard in easy way.
Schema hostel system:
•
Hostel(SID,FULLNAME,FLOURNUM,ROOMNUM)
•
Student(SID,FNAME,LNAME,STUYEAR,STUADRESS,MROOMNUM,STUMAJOR)
•
ROOM(ROOMNUM , STUNUM )
•
•
Furniture(ROOMNUM,ITEM,FUNAME)
•
Registration(SID,SNAME,YEAR,PHONE,DATE,ROOM)
ERD:
Room
Pk
Registration
Pk
ROOMNUM
SNAME
YEAR
ROOM
PHONE
DAY
STUNUM
Hostel
Furniture
Pk
Student
ROOMNUM
FUNAME
ITEM
SID
Pk
Pk
No-Of-items
SID
SID
FNAME
LNAME
STYEAR
STUMAJOR
STUADDRESS
ROOMNUM
FULLNAME
FLOURNUM
ROOMNUM
Introduction to Databases
ITDR2106
Assigment-part-2
INSERT TABLE ROOM
CREATE TABLE ROOM(ROOMNUM NUMBER (4) PRIMARY KEY, STUNUM NUMBER (4));
INSERT INTO ROOM VALUES(1,3);
INSERT INTO ROOM VALUES(2,2);
INSERT INTO ROOM VALUES(3,2);
INSERT INTO ROOM VALUES(4,2);
INSERT INTO ROOM VALUES(5,2);
INSERT INTO ROOM VALUES(6,3);
INSERT INTO ROOM VALUES(7,2);
INSERT INTO ROOM VALUES(8,2);
INSERT INTO ROOM VALUES(9,3);
INSERT INTO ROOM VALUES(10,2);
SELECT*FROM ROOM;
INSERT TABLE STUDENT
CREATE TABLE STUDENT (SID NUMBER (10) PRIMARY KEY, FNAME VARCHAR (10),LNAME VARCHAR(10),
STYEAR NUMBER (4), STUMAJOR VARCHAR (10), STUADDRESS VARCHAR (10),ROOMNUM REFERENCES
ROOM);
INSERT INTO STUDENT VALUES(2014193021,'ASMA','SALEM',2014,'IT','ALRUSTAQ',1);
INSERT INTO STUDENT VALUES(2014193028,'AFRAH','HILAL',2014,'ENGLISH','ALRUSTAQ',1);
INSERT INTO STUDENT VALUES(2014193022,'NOOR','ALI',2014,'IT','ALRUSTAQ',1);
INSERT INTO STUDENT VALUES(2013193029,'NOUF','SALEM',2013,'IT','SOHAR',2);
INSERT INTO STUDENT VALUES(2013193023,'ASMA','ALI',2013,'IBA','SOHAR',2);
INSERT INTO STUDENT VALUES(2013193025,'YARA','NASER',2012,'IBA','SOHAR',3);
INSERT INTO STUDENT VALUES(2012193027,'SAMYA','MOHAMMED',2012,'IBA','IBRI',3);
INSERT INTO STUDENT VALUES(2014193113,'AMJAAD','DARWISH',2014,'IT','ALRUSTAQ',4);
INSERT INTO STUDENT VALUES(2014193345,'ABEER','SULIMAN',2014,'IT','SOUR',4);
INSERT INTO STUDENT VALUES(2011193002,'ASMA','SALEM',2011,'ENGLISH','IBRI',5);
INSERT INTO STUDENT VALUES(2011193003,'NOOR','NOOH',2011,'IT','MUSCAT',5);
INSERT INTO STUDENT VALUES(2011193009,'NOOF','HAMED',2011,'IBA','MUSCAT',6);
INSERT INTO STUDENT VALUES(2014193023,'SALWA','NASER',2014,'IT','NAKHAL',6);
INSERT INTO STUDENT VALUES(2012193056,'ANWAR','ALI',2012,'ENGLISH','NAKHAL',6);
INSERT INTO STUDENT VALUES(2013193654,'SARA','HAMED',2013,'IBA','SUR',7);
INSERT INTO STUDENT VALUES(2013193543,'BUTHAINA','MARHOON',2013,'ENGLISH','SUR',7);
INSERT INTO STUDENT VALUES(2013193123,'SALAMA','RASHID',2013,'IBA','MUSQAT',8);
INSERT INTO STUDENT VALUES(2013193123,'SUMAIA','ALI',2013,'IBA','SAHAM',8);
INSERT INTO STUDENT VALUES(2013193111,'REEM','AHMED',2013,'IT','SAHAM',9);
INSERT INTO STUDENT VALUES(2013193504,'MAHA','KHALID',2013,'ENGLISH','SAHAM',9);
INSERT INTO STUDENT VALUES(201319332,'NOOR','OMAR',2013,'IBA','NAKHAL',9);
INSERT INTO STUDENT VALUES(2014193650,'INTISAR','HAMED',2014,'ENGLISH','ALRUSTAQ',10);
INSERT INTO STUDENT VALUES(2014193123,'MARWA','SALEM',2014,'ENGLISH','IBRI',10);
SELECT*FROM STUDENT;
INSERT TABLE HOSTEL
CREATE TABLE HOSTEL(SID NUMBER (10) PRIMARY KEY, FULLNAME VARCHAR (20),
FLOURNUM NUMBER (3),ROOMNUM REFERENCES ROOM);
INSERT INTO HOSTEL VALUES (2013193654,'SARA HAMED', 2,7);
INSERT INTO HOSTEL VALUES (2013193543,'BUTHAINA MARHOON', 2,7);
INSERT INTO HOSTEL VALUES (2013193123,'SALAMA RASHID', 1,8);
INSERT INTO HOSTEL VALUES (201319332,'NOOR OMAR', 1,9);
INSERT INTO HOSTEL VALUES (2014193028,'AFRAH HILAL', 2,1);
INSERT INTO HOSTEL VALUES (2013193023,'ASMA ALI', 1,2);
INSERT INTO HOSTEL VALUES (2014193113,'AMJAAD DARWISH', 2,4);
INSERT INTO HOSTEL VALUES (2014193345,'ABEER SULIMAN', 2,4);
SELECT*FROM HOSTEL;
INSERT TABLE FURNITURE
CREATE TABLE FURNITURE(ROOMNUM NUMBER (4) PRIMARY KEY, FUNAME VARCHAR(10), ITEM
NUMBER(5));
INSERT INTO FURNITURE VALUES(10,'TABLE', 1);
INSERT INTO FURNITURE VALUES(9,'TABLE', 2);
INSERT INTO FURNITURE VALUES(8,'CHAIR', 1);
INSERT INTO FURNITURE VALUES(7,'TABLE', 2);
INSERT INTO FURNITURE VALUES(6,'CHAIR', 1);
INSERT INTO FURNITURE VALUES(5,'TABLE', 3);
INSERT INTO FURNITURE VALUES(4,'CHAIR', 2);
INSERT INTO FURNITURE VALUES(3,'TABLE', 3);
INSERT INTO FURNITURE VALUES(2,'CHAIR', 2);
INSERT INTO FURNITURE VALUES(1,'CHAIR', 2);
select *from FURNITURE;
INSERT TABLE REGISTRATION
CREATE TABLE REGISTRATION(
SID NUMBER(10)PRIMARY KEY,
SNAME VARCHAR2(15),
ROOM NUMBER(3),
YEAR NUMBER(4),
PHONE NUMBER(9),
DAY DATE
);
INSERT INTO REGISTRATION VALUES(2013193654,'SARA',7,2014,91827646,'25/october/2015');
INSERT INTO REGISTRATION VALUES(2014193021,'ASMAA',7,2014,97142525,'10/october/2015');
INSERT INTO REGISTRATION VALUES(2014193123,'MARWA',10,2011,92183774,'22/october/2015');
INSERT INTO REGISTRATION VALUES(2013193111,'REEM',9,2012,92838741,'1/october/2015');
INSERT INTO REGISTRATION VALUES(2011193009,'NOOF',6,2013,91773646,'1/november/2015');
INSERT INTO REGISTRATION VALUES(2013193025,'YARA',3,2013,97176366,'15/october/2016');
INSERT INTO REGISTRATION VALUES(2014193028,'AFRAH',1,2011,91887277,'5/november/2016');
SELECT*FROM REGISTRATION;
1. select a horizontal and vertical subset of a table
SELECT SID ,FULLNAME, FLOURNUM, ROOMNUM FROM HOSTEL WHERE SID=2013193123;
SELECT FULLNAME FROM HOSTEL;
2 ) join 2 tables and project a reasonable subset of the columns
SELECT * FROM STUDENT NATURAL JOIN REGISTRATIONN ;
3. a SELECT using LIKE on a text field
SELECT * FROM HOSTEL WHERE FULLNAME LIKE 'A%' ;
SELECT * FROM HOSTEL WHERE FULLNAME LIKE '%A%' ;
4. a query using GROUP BY
SELECT STUADDRESS FROM STUDENT GROUP BY STUADDRESS;
5. a query using HAVING
SELECT SID FROM STUDENT GROUP BY SID HAVING SID='2014193345';
6. a query using ORDER BY
SELECT FUNAME, ITEM FROM FURNITURE ORDER BY ITEM;
7. a query using a subquery
SELECT FNAME, STYEAR FROM STUDENT WHERE STYEAR IN (SELECT STYEAR FROM ROOM WHERE
STYEAR=2014);
8. a query using a correlated subquery
SELECT a.FNAME,a.LNAME,a.STYEAR,a.SID
FROM STUDENT a
WHERE a.SID=(
SELECT b.SID
FROM REGISTRATIONN b WHERE b.PHONE='91887277');
9. a query involving COUNT(*)
SELECT COUNT (*)FROM REGISTRATIONN;
10. a query involving another aggregate function
SELECT MAX(ITEM)FROM FURNITURE;
11. a selective DELETE
DELETE FROM STUDENT WHERE SID='2013193654';
12. a selective UPDATE
UPDTE FURNITURE SET ITEM= '2';
13. a SELECT on a DATE column (note: use the TO_DATE function)
SELECT SNAME,DAY FROM REGISTRATION WHERE DAY>=('01-NOV-15') ORDER BY DAY;
14. a SELECT using an inequality
SELECT SID,FULLNAME,ROOMNUM FROM HOSTEL WHERE FLOURNUM=1;
Introduction to Databases
ITDR2106
Assigment-part-3
*Trigger change the number of room when student change her room
CREATE TABLE CHANGEROOMMM AS SELECT * FROM ROOM WHERE
ROOMNUM=2;
CREATE OR REPLACE TRIGGER ROOM_CHANGE3
AFTER UPDATE OF ROOMNUM
ON ROOM
FOR EACH ROW
BEGIN
INSERT INTO CHANGEROOMMM VALUES(:OLD.ROOMNUM,:OLD.STUNUM);
END;
/
SELECT*FROM CHANGEROOM;
UPDATE CHANGEROOM SET ROOMNUM=3 WHERE STUNUM=2;
SELECT*FROM CHANGEROOM;
Trigger remove old (GRADUATE) STUDENT from student table to new table called
GRADUATE_STUDENTT
CREATE TABLE GRADUATE_STUDENTT(ID NUMBER (12) PRIMARY KEY, STUDENTNAME VARCHAR(20),
PHONE NUMBER (9));
CREATE or REPLACE TRIGGER OLD_STUDENT_trigger
AFTER INSERT
ON STUDENT
FOR EACH ROW
BEGIN
INSERT INTO GRADUATE_STUDENTT
VALUES
(:old.ID,
:old.STUDENTNAME,
:old.PHONE);
END;
insert into GRADUATE_STUDENTT values(2011193003,'muna',91122332);
select*from GRADUATE_STUDENTT;
*Cursor RETURN THE STUDENT ID, YEAR AND MAJOR
DECLARE
CURSOR STU IS SELECT * FROM STUDENT;
DREC STUDENT%ROWTYPE;
BEGIN
IF NOT STU%ISOPEN THEN
OPEN STU;
END IF;
FETCH STU INTO DREC ;
WHILE STU%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(DREC.SID||' ' || DREC.STYEAR||' '|| DREC.STUMAJOR);
FETCH STU INTO DREC ;
END LOOP;
END;
/
*cursor RETYRN FIRST NAME AND LAST NAME OF STUDENT WHEN SEND ROOM NUMBER
declare cursor Student_cursor is
select FNAME ,LNAME
from STUDENT
where ROOMNUM in (1);
begin for i in Student_cursor loop
DBMS_OUTPUT.PUT_line('FNAME: '||i.FNAME||' LNAME: '||i.LNAME);
end loop;
end;
FUNCTION RETURN THE ITEM IN EVERY ROOM WHEN SEND ROOM NUMBER
CREATE OR REPLACE FUNCTION FUN_ROOM(S FURNITURE.ROOMNUM%TYPE)
RETURN VARCHAR
IS
NS FURNITURE.ITEM%TYPE;
BEGIN
SELECT ITEM INTO NS FROM FURNITURE WHERE ROOMNUM=1;
RETURN NS;
END;
DECLARE
S FURNITURE.ITEM%TYPE;
BEGIN
S:=FUN_ROOM(4);
DBMS_OUTPUT.PUT_LINE('THE NUMBER OF ITEM IS:' ||S);
END;
/
Function RETURN NUMBER OF ROOM WHEN SEND STUDENT ID
CREATE OR REPLACE FUNCTION FUN_STUDENT(S STUDENT.SID%TYPE)
RETURN VARCHAR
IS
NS STUDENT.ROOMNUM%TYPE;
BEGIN
SELECT ROOMNUM INTO NS FROM STUDENT WHERE SID=2014193021;
RETURN NS;
END;
DECLARE
S STUDENT.ROOMNUM%TYPE;
BEGIN
S:=FUN_STUDENT(2014193021);
DBMS_OUTPUT.PUT_LINE('THIS STUDENT IS IN ROOM NUMBER :' ||S);
END;
/
PROCEDURE RETURN NAME OF STUDENT WHEN SEND STUDENT ID
CREATE OR REPLACE PROCEDURE PRC_DNAME IS
DN STUDENT.FNAME%TYPE;
BEGIN
SELECT FNAME INTO DN FROM STUDENT WHERE SID=2014193021;
DBMS_OUTPUT.PUT_LINE(DN);
END;
/
BEGIN
PRC_DNAME;
END;
/
PROCEDURE RETURN DATE OF BOOKING THE ROOM WHEN SEND STUDENT NAME
CREATE OR REPLACE PROCEDURE PRC_REG(A REGISTRATION.ROOM%TYPE) IS
RE REGISTRATION.DAY%TYPE;
BEGIN
SELECT DAY INTO RE FROM REGISTRATION WHERE SNAME='SARA';
DBMS_OUTPUT.PUT_LINE(RE);
END;
/
BEGIN
PRC_REG(7);
END;
/
Package CONTAIN 2 function
CREATE OR REPLACE PACKAGE package_stu AS
FUNCTION FUN_STUDENT(S STUDENT.SID%TYPE)
RETURN VARCHAR;
FUNCTION FUN_ROOM(S FURNITURE.ROOMNUM%TYPE)
RETURN VARCHAR;
END PACKAGE_STU;
/
CREATE OR REPLACE PACKAGE BODY package_stu AS
FUNCTION FUN_ROOM(S FURNITURE.ROOMNUM%TYPE)
RETURN VARCHAR
IS
NS FURNITURE.ITEM%TYPE;
BEGIN
SELECT ITEM INTO NS FROM FURNITURE WHERE ROOMNUM=1;
RETURN NS;
END;
FUNCTION FUN_STUDENT(S STUDENT.SID%TYPE)
RETURN VARCHAR
IS
NS STUDENT.ROOMNUM%TYPE;
BEGIN
SELECT ROOMNUM INTO NS FROM STUDENT WHERE SID=2014193021;
RETURN NS;
END;
End;
SELECT
package_stu.FUN_ROOM(1) FROM DUAL;
PACKAGE CONTAIN 2 PROCEDURE
CREATE OR REPLACE PACKAGE package_pr_HOSTEL AS
PROCEDURE PRC_DNAME ;
PROCEDURE PRC_REG(A REGISTRATION.ROOM%TYPE);
END package_pr_HOSTEL;
/
CREATE OR REPLACE PACKAGE BODY package_pr_HOSTEL AS
PROCEDURE PRC_REG(A REGISTRATION.ROOM%TYPE) IS
RE REGISTRATION.DAY%TYPE;
BEGIN
SELECT DAY INTO RE FROM REGISTRATION WHERE SNAME='SARA';
DBMS_OUTPUT.PUT_LINE(RE);
END;
PROCEDURE PRC_DNAME IS
DN STUDENT.FNAME%TYPE;
BEGIN
SELECT FNAME INTO DN FROM STUDENT WHERE SID=2014193021;
DBMS_OUTPUT.PUT_LINE(DN);
END;
End;
BEGIN
package_pr_HOSTEL.PRC_DNAME;
END;
Simple ette Alle
opel additional al
more than one
Composite identifier Peimary key composed of
Attributes
te bile the
Foot
at Entity: Chen and Crow's
4.1 The Attributes of the
Topic
has the clinic system
Database syster
Part (1)
1) Case Study
2)_E_R
Entity Relationship
a) blogical Schema
Part (2)
DDL Data definition language
SQL» DML = Data Manipulation
Structured "Puerg language SQL (10)
Part (3) PL Procedural Language
PLEASE SQC
Entities
@ Rutlets
Payment
Melicine
of Chesnas
Presexplon
Dector
2. Pharmacist
9 Medicine
4. Prescription
5. Patients
6.
paying
Purchase answer to see full
attachment