NAME:SANVI CLASS:2 BCA D ROLL NO:CA23522
[Link] THE FOLLOWING TABLES IDENTIFYING PRIMARY KEY AND FOREIGN
[Link] THE NOT NULL PROPERTY FOR MANDATORY
KEYS.EMPLOYEE_MASTER(EMP_ID,EMP_NAME,EMAIL_ID,EMP_ADDRESS,PHONE)
ATTENDANCE(EMP_ID,MONTH,WOM,MHRS,THRS,WHRS,TRHRS,FHRS,SHRS,SUHRS
).(VALID VALUES FOR WOM<=5,MONTH CAN BE 1-12).APPLY APPROPRIATE
CONSTRAINTS CONSIDER 3 [Link] ATTENDANCE RECORDS FOR AT
LEAST TWO MONTHS.
CREATING A TABLE
CREATE TABLE EMPLOYEE_MASTER522
(E_ID NUMBER(5)PRIMARY KEY,
E_NAME VARCHAR2(10)NOT NULL,
EMAIL_ID VARCHAR2(20)NOT NULL,
E_ADDRESS VARCHAR2(20)NOT NULL,
PHONE NUMBER(10)NOT NULL);
TABLE CREATED.
INSERING INTO A TABLE :
INSERT INTO EMPLOYEE_MASTER522
VALUES(1301,'YADAV','YADAV13@[Link]','KINNIGOLI',9876703216);
1 ROW CREATED.
INSERT INTO EMPLOYEE_MASTER522
VALUES(1302,'UDAY','UDAY10@[Link]','MANGALORE',9678943266);
1 ROW CREATED.
INSERT INTO EMPLOYEE_MASTER522
VALUES(1303,'RAMANATH','RAMANATH90@[Link]','UDUPI',7865765161);
1 ROW CREATED.
PAGE NO:
NAME:SANVI CLASS:2 BCA D ROLL NO:CA23522
DISPLAYING THE VALUES IN THE TABLE:
CREATING ANOTHER TABLE:
CREATE TABLE ATTENDENCE522
(E_ID NUMBER(5)REFERENCES EMPLOYEE_MASTER522 (E_ID),
MONTH NUMBER(2)CHECK(MONTH BETWEEN 1 AND 12),
WOM NUMBER(1)CHECK(WOM<=5),
MHRS NUMBER(2),
THRS NUMBER(2),
WHRS NUMBER(2),
TRHRS NUMBER(2),
FHRS NUMBER(2),
SHRS NUMBER(2),
SUHRS NUMBER(2));
TABLE CREATED.
INSERTING VALUES TO THE TABLE:
INSERT INTO ATTENDENCE522
VALUES(1301,7,3,8,6,9,7,8,8,7);
1 ROW CREATED.
PAGE NO:
NAME:SANVI CLASS:2 BCA D ROLL NO:CA23522
INSERT INTO ATTENDENCE522
VALUES(1302,6,2,9,5,10,7,8,9,10);
1 ROW CREATED.
INSERT INTO ATTENDENCE522
VALUES(1301,10,3,6,7,8,8,7,6,9);
1 ROW CREATED.
INSERT INTO ATTENDENCE522
VALUES(1302,2,4,9,6,9,0,8,7,9);
1 ROW CREATED.
DISPLAYING THE VALUES IN THE TABLE:
WRITE THE SQL QUERIES FOR THE FOLLOWING:
[Link] EMP_ID,EMP_NAME AND EMAIL_ID OF ALL EMPLOYEES WHO
ARE WORKING ON EVERY SUNDAY OF 2ND AND 4TH WEEK IN A MONTH.
[Link] TOTAL HOURS WORKED BY EACH EMPLOYEE IN EACH MONTH
WITH EMP_ID.
PAGE NO:
NAME:SANVI CLASS:2 BCA D ROLL NO:CA23522
[Link] THE NAMES OF THE EMPLOYEES WHO NEVER ATTENDED THE
DUTY SO FAR(ATTENDANCE NOT GIVEN SO FAR).
[Link] THE EMPLOYEE NAME,MONTH,WEEK,TOTAL,HOURS WORKED
FOR EMPLOYEES WHO HAVE TOTAL NO. OF HOURS MORE THAN 20 HR. A
WEEK.
PAGE NO: