CS403 Assignment # 4 Idea Solution June 2012

Create table instructor syntax:

Create table INSTUCTOR(INSTUCTOR_ID char(50),FISRT_NAME char(50),LAST_NAME)

Char(50),LAST_DEGREE char(50),SALARY char(50),CITY char(50), HIRE_DATE detetime);

Insert into INSTRUCTOR:

Insert into INSTRUCTOR values(‘50’,‘AHMAD’,‘ALEEM’,‘MS’,‘40000’,‘ISLD’,’15-JAN-2011’);

Insert into INSTRUCTOR values(‘55’,‘MUHMMAD’,‘HASAN’,‘MS’,‘42000’,‘ISLD’,’16-FEB-2010’);

Insert into INSTRUCTOR values(‘60’,‘TALHA’,‘ALI’,‘phd’,‘80000’,‘LHR’,’18-JUN-2008’);

Insert into INSTRUCTOR values(‘70’,‘HANNAN’,‘NAWAZ’,‘phd’,‘76000’,‘LHR’,’20-DEC-2008’);

Insert into INSTRUCTOR values(‘80’,‘WAJID’,‘ALI’,‘MS’,‘45000’,‘LHR’,’09-AUG-2008’);

Insert into INSTRUCTOR values(‘85’,‘ASIF’,‘AHMAD’,‘MIT’,‘50000’,‘MUL’,’19-MAY-2010’);

Insert into INSTRUCTOR values(‘90’,‘TAHIR’,‘MOHSIN’,‘MCS’,‘38000’,‘MUL’,’27-FEB-2009’);

Insert into INSTRUCTOR values(‘95’,‘BAKIR’,‘NAWAZ’,‘MS’,‘40000’,‘ISLD’,’12-AUG-2011’);

Insert into INSTRUCTOR values(‘100’,‘FAHAD’,‘AHMAD’,‘Phd’,‘90000’,‘ISLD’,’07-APR-2008’);

Create table student syntax:

Create table STUDENT (STUDENT_ID char(50),STU_NAME char(50),SEMESTER char(50),SPECILIZATION char(50).INSTRUCTOR_ID char(50));

Insert into student syntax:

Insert into STUDENT values(‘1’,‘AHMAD’,‘2’,‘NETWORKS’,‘50’);

Insert into STUDENT values(‘2’,‘ASIM’,‘2’,‘NETWORKS’,‘55’);

Insert into STUDENT values(‘3’,‘SAJID’,‘3’,‘DATABASES’,‘60’);

Insert into STUDENT values(‘4’,‘SHUJA’,‘4’,‘AL’,‘70’);

Insert into STUDENT values(‘5’,‘ZEESHAN’,‘1’,‘DATABASES’,‘80’);

Insert into STUDENT values(‘6’,‘HASEEB’,‘2’,‘SOFTWARE ENGINEERING’,‘85’);

Insert into STUDENT values(‘7’,‘MUDASSAR’,‘3’,‘SOFTWARE ENGINEERING’,‘90’);

Insert into STUDENT values(‘8’,‘QASIM’,‘3’,‘DATABASES’,‘95’);

Insert into STUDENT values(‘9’,‘Fahad’,‘4’,‘NETWORKS’,‘100’);

Select Query syntax:

Select STUDENT.STUDENT_ID, INSTRUCTOR.LAST_NAME, INSTRUCTOR.LAST_DEGREE INSTRUCTOR.HIRE_DATE, INSTRUCTOR.INSTRUCTOR_ID, STUDENT.INSTRUCTOR_ID, STUDENT.STU_NAME FROM STUDENT INNER JOIN INSTRUCTOR ON STUDENT.INSTRUCTOR_ID = STUDENT.INSTRUCTOR_ID,