Scribd
Upload
666 views5 pages

Irfanali Dbs Test 02

The document describes a lab test on developing database tables for a university portal. It includes: 1) Creating tables for teachers, students, and courses with attributes like teacher ID, name, salary and student ID, name, semester. 2) Inserting sample data into the tables. 3) Additional questions about creating tables for a hotel management system and copying data between employee tables.

Uploaded by

DeadPool Pool
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
666 views5 pages

Irfanali Dbs Test 02

The document describes a lab test on developing database tables for a university portal. It includes: 1) Creating tables for teachers, students, and courses with attributes like teacher ID, name, salary and student ID, name, semester. 2) Inserting sample data into the tables. 3) Additional questions about creating tables for a hotel management system and copying data between employee tables.

Uploaded by

DeadPool Pool
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 5

Lab Test 02

Subject:
Database Systems lab
Name:
Irfan Ali
Sap id:
2392
Question.No1
Develop database of university portal create the following tables.
 Teacher (techear_id (primary key), teacher name (unique and not null),
designation, Salary)
 Student (Cms (primary_key), Student name, age , semester (not null))
 Course (course_id (primarykey), course-name(not null and unique) ,
cms(forignkey)
,credit hour)

Ans:
Create user unversity_portal
Identified by abc;
GRANT CREATE SESSION TO unversity_portal;
GRANT CREATE TABLE TO unversity_portal;
GRANT CREATE VIEW TO unversity_portal;
GRANT CREATE SEQUENCE TO unversity_portal;

CREATE TABLE teacher(teacher_id NUMBER(2),teacher_name


VARCHAR2(14),designation VARCHAR2(13) ,salary NUMBER(2));

CREATE TABLE student(cms NUMBER(2) ,student_name VARCHAR2(14),age


NUMBER(2),semester NUMBER(2) NOT NULL);

CREATE TABLE course( course_id NUMBER(6), course_name VARCHAR2(16),


CONSTRAINT cms_fk FOREIGN KEY (cms)REFERENCES student (cms),credit_hour
NUMBER(2));

INSERT INTO TEACHER(TEACHER_ID,TEACHER_NAME,DESIGNATION,SALARY)


VALUES(1,'ali',NULL,60000);

INSERT INTO TEACHER(TEACHER_ID,TEACHER_NAME,DESIGNATION,SALARY)


VALUES(2,'ahmed',NULL, 45000);

INSERT INTO TEACHER(TEACHER_ID,TEACHER_NAME,DESIGNATION,SALARY)


VALUES(3,'khaled',NULL, 45000);

INSERT INTO TEACHER(TEACHER_ID,TEACHER_NAME,DESIGNATION,SALARY)


VALUES(4,'abdullah',NULL, 45000);

INSERT INTO TEACHER(TEACHER_ID,TEACHER_NAME,DESIGNATION,SALARY)


VALUES(5,'abbas',NULL, 45000);
INSERT INTO STUDENT( CMS ,STUDENT_NAME,S_AGE,SEMESTER)
VALUES (10,'irfan',21 ,4);

INSERT INTO STUDENT( CMS ,STUDENT_NAME,S_AGE,SEMESTER)


VALUES (11,'almuzaini',20,4);

INSERT INTO STUDENT( CMS ,STUDENT_NAME,S_AGE,SEMESTER)


VALUES (12,'yaqub',21 ,4);

INSERT INTO STUDENT( CMS ,STUDENT_NAME,S_AGE,SEMESTER)


VALUES (13,'abdulrhman',20 ,4);

INSERT INTO STUDENT( CMS ,STUDENT_NAME,S_AGE,SEMESTER)


VALUES (14,'mustafa',20 ,4);
INSERT INTO COURSE( COURSE_ID ,COURSE_NAME , CREDIT_HOUR)
VALUES (122,'math' ,3);

INSERT INTO COURSE( COURSE_ID ,COURSE_NAME , CREDIT_HOUR)


VALUES (133,'db' ,4);

INSERT INTO COURSE( COURSE_ID ,COURSE_NAME , CREDIT_HOUR)


VALUES (144,'physics' ,3);

INSERT INTO COURSE( COURSE_ID ,COURSE_NAME , CREDIT_HOUR)


VALUES (155,'hci' ,3);

INSERT INTO COURSE( COURSE_ID ,COURSE_NAME , CREDIT_HOUR)


VALUES (166,'english' ,3);

CREATE TABLE teacher(teacher_id NUMBER(2) CONSTRAINT tid_pk PRIMARY


KEY(teacher_id),teacher_name VARCHAR2(14) CONSTRAINT tname_uk UNIQUE KEY
(teacher_name) NOT NULL,designation VARCHAR2(13) ,salary NUMBER(2)));

CREATE TABLE student(cms NUMBER(2) CONSTRAINT sid_pk PRIMARY KEY,student_name


VARCHAR2(14),age NUMBER(2),semester NUMBER(2) NOT NULL);

CREATE TABLE course( course_id NUMBER(6),CONSTRAINT c_pk PRIMARY KEY


(course_id), course_name VARCHAR2(16) CONSTRAINT cam_pk UNIQUE
KEY(course_name) NOT NULL, CONSTRAINT cms_fk FOREIGN KEY (cms)REFERENCES
student (cms),credit_hour NUMBER(2));

drop table course cascade constraints;

UPDATE teacher teacher_id = 88,teacher_name='fahad',designation = dec ,salary


= 19000;
UPDATE course course_id = 0098,course_name='dsa',credit_hour = 4;

TRUNCATE TABLE teacher;


Alter table student cnic NUMBER(14);

Question.no 2
Develop a system hotel management system create the following tables
 Hotel (hotel-id(Primary key), Hotel-name ,room-id (foreign-key))
 Room (Room_id(Primary key), Room-rent, check in(date) , check out (date) )
 Employees (Employee_id(primary key), employee_name (unique), Salary )
Apply the following operations
 Create the hotel table use default value of hotel-name which is (Usamania,
Tabaq, Envy continental and etc.)
 Apply table level constraint in each table.
 Insert at least 10 records in each table.
 Remove all records in hotel table also hotel entity itself

Ans)
Create user hotel_manage_system
Identified by 440;
GRANT CREATE SESSION TO hotel_manage_system ;
GRANT CREATE TABLE TO hotel_manage_system ;
GRANT CREATE VIEW TO hotel_manage_system ;
GRANT CREATE SEQUENCE TO hotel_manage_system ;

CREATE TABLE hotel(hotel_id NUMBER(2) CONSTRAINT hid_pk PRIMARY


KEY(hotel_id),hotel_name VARCHAR2(14),room_id NUMBER(4)CONSTRAINT rom_fk
FOREIGN KEY (room_id)REFERENCES room (room_id));

CREATE TABLE room(room_id NUMBER(2) CONSTRAINT rid_pk PRIMARY KEY


(room_id),room_rent NUMBER(5),check_in date , check_out date);

CREATE TABLE employees( employee_id NUMBER(6)CONSTRAINT wm_pk PRIMARY KEY


(employee_id), employee_name VARCHAR2(16) CONSTRAINT emn_pk UNIQUE KEY
(employee_name), salary);

CREATE TABLE hotel(hotel_id NUMBER(2) CONSTRAINT hid_pk PRIMARY


KEY(hotel_id),hotel_name ='Usamania',room_id NUMBER(4)CONSTRAINT rom_fk
FOREIGN KEY (room_id)REFERENCES room (room_id));

CREATE TABLE hotel(hotel_id NUMBER(2) CONSTRAINT hid_pk PRIMARY


KEY(hotel_id),hotel_name ='Tabaq',room_id NUMBER(4)CONSTRAINT rom_fk FOREIGN
KEY (room_id)REFERENCES room (room_id));

CREATE TABLE hotel(hotel_id NUMBER(2) CONSTRAINT hid_pk PRIMARY


KEY(hotel_id),hotel_name ='Envy',room_id NUMBER(4)CONSTRAINT rom_fk FOREIGN
KEY (room_id)REFERENCES room (room_id));

CREATE TABLE hotel(hotel_id NUMBER(2) CONSTRAINT hid_pk PRIMARY


KEY(hotel_id),hotel_name ='continental',room_id NUMBER(4)CONSTRAINT rom_fk
FOREIGN KEY (room_id)REFERENCES room (room_id));

CREATE TABLE hotel(hotel_id NUMBER(2),hotel_name VARCHAR2(14),room_id


NUMBER(4),CONSTRAINT hid_pk PRIMARY KEY(hotel_id),CONSTRAINT rom_fk FOREIGN
KEY (room_id)REFERENCES room (room_id));

CREATE TABLE room(room_id NUMBER(2),hotel_name VARCHAR2(14),room_id


NUMBER(4),room_rent NUMBER(5),check_in date , check_out date,CONSTRAINT
rid_pk PRIMARY KEY);

CREATE TABLE employees(employee_id NUMBER(6),employee_name


VARCHAR2(16),salary, CONSTRAINT wm_pk PRIMARY KEY (employee_id),CONSTRAINT
emn_pk UNIQUE KEY(employee_name);

INSERT INTO hotel(hotel_id,hotel_name,room_id)VALUES (333,'premium', 1850);


INSERT INTO
room(room_id,hotel_name,room_id,room_rent,check_in,check_out)VALUES
(1850,'down', 19098, sysdate, sysdate);
INSERT INTO employees(employee_id,employee_name,salary,)VALUES (134,'yen',
1900);

TRUNCATE TABLE hotel;


drop table hotel cascade constraints;
Question.no3
Create a table new_employees and new_departments the table structure should
be same as employees and department and copy all the records in new table
which is exist in employee and department table.
 After create new table update the salary and job_id of employee_id 133
where salary and job_id is equivalent to employee_id 150.

Ans)
CREATE TABLE NEW_EMPLOYEES(EMPLOYEE_ID NUMBER(4),FIRST_NAME
VARCHAR2(20),LAST_NAME VARCHAR2(20),JOB_ID VARCHAR(10),SALARY NUMBER 10,
CONSTRAINT EMP_PK PRIMARY KEY(EMPLOYEE_ID));

INSERT INTO NEW_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY)


SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY
FROM EMPLOYEES;

CREATE TABLE NEW_DEPARTMENTS(DEMPARTMENT_ID NUMBER(4),DEPARTMENT_NAME


VARCHAR2(40), CONSTRAINT DEP_PK PRIMARY KEY(DEPARTMENT_ID));

INSERT INTO NEW_DEPARTMENTS(DEPARTMENT_ID,DEPARTMENTS_NAME)


SELECT DEPARTMENT_ID,DEPARTMENT_NAME
FROM DEMPARTMENTS;

UPDATE NEW_EMPLOYEES SET JOB_ID=(SELECT JOB_ID FROM EMPLOYEES WHERE


EMPLOYEES_ID=150),SALARY=(SELECT FROM EMPLOYEES WHERE EMPLOYEE_ID=150)
WHERE EMPLOYEE_ID=133;

========================THE END========================

You might also like