NAME:SANVI CLASS:2 BCA D ROLL NO:CA23522
2.CREATE THE FOLLOWING TABLES IDENTIFYING PRIMARY KEY AND FOREIGN
KEYS.SPECIFY 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 EMPLOYEES.AND 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','
[email protected]','KINNIGOLI',9876703216);
1 ROW CREATED.
INSERT INTO EMPLOYEE_MASTER522
VALUES(1302,'UDAY','
[email protected]','MANGALORE',9678943266);
1 ROW CREATED.
INSERT INTO EMPLOYEE_MASTER522
VALUES(1303,'RAMANATH','[email protected]','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:
A.DISPLAYING EMP_ID,EMP_NAME AND EMAIL_ID OF ALL EMPLOYEES WHO
ARE WORKING ON EVERY SUNDAY OF 2ND AND 4TH WEEK IN A MONTH.
B.DISPLAY TOTAL HOURS WORKED BY EACH EMPLOYEE IN EACH MONTH
WITH EMP_ID.
PAGE NO:
NAME:SANVI CLASS:2 BCA D ROLL NO:CA23522
C.DISPLAY THE NAMES OF THE EMPLOYEES WHO NEVER ATTENDED THE
DUTY SO FAR(ATTENDANCE NOT GIVEN SO FAR).
D.DISPLAY THE EMPLOYEE NAME,MONTH,WEEK,TOTAL,HOURS WORKED
FOR EMPLOYEES WHO HAVE TOTAL NO. OF HOURS MORE THAN 20 HR. A
WEEK.
PAGE NO:
