CS403 Assignment No 4 Spring 2012 solution

Use the following tables to solve this assignment.

 

INSTRUCTOR_ID

FIRST_NAME

LAST_NAME

LAST_ DEGREE

SALARY

CITY

HIRE_DATE

50

AHMAD

ALEEM

MS

40000

ISLD

15-JAN-2011

55

MUHAMMAD

HASAN

MS

42000

ISLD

16-FEB-2010

60

TALHA

ALI

Phd

80000

LHR

18-JUN-2008

70

HANNAN

NAWAZ

Phd

76000

LHR

20-DEC-2008

80

WAJID

ALI

MS

45000

LHR

09-AUG-2008

85

ASIF

AHMAD

MIT

50000

MUL

19-MAY-2010

90

TAHIR

MOHSIN

MCS

38000

MUL

27-FEB-2009

95

BAKIR

NAWAZ

MS

40000

ISLD

12-AUG-2011

100

SUMAIR

AHMAD

Phd

90000

ISLD

07-APR-2008

Table 1: INSTRUCTOR TABLE

                  Table 2: STUDENT TABLE

STUDENT_ID

STU_NAME

SEMESTER

SPECIALIZATION

INSTRUCTOR_ID

1

AHMAD

2

NETWORKS

50

2

ASIM

2

NETWORKS

55

3

SAJID

3

DATABASES

60

4

SHUJA

4

AI

70

5

ZEESHAN

1

DATABASES

80

6

HASEEB

2

SOFTWARE ENGINEERING

85

7

MUDASSAR

3

SOFTWARE ENGINEERING

90

8

QASIM

3

DATABASES

95

9

WAHEED

4

NETWORKS

100

                 You are required to write a SQL query to display the following output table:

 

STUDENT_ID

LAST_NAME

LAST DEGREE

HIRE_DATE

INSTRUCTOR_ID

INSTRUCTOR_ID

STU_NAME

1

ALEEM

MS

15-JAN-2011

50

50

AHMAD

2

HASAN

MS

16-FEB-2010

55

55

ASIM

3

ALI

Phd

18-JUN-2008

60

60

SAJID

4

NAWAZ

Phd

20-DEC-2008

70

70

SHUJA

5

ALI

MS

09-AUG-2008

80

80

ZEESHAN

6

AHMAD

MIT

19-MAY-2010

85

85

HASEEB

7

MOHSIN

MCS

27-FEB-2009

90

90

MUDASSAR

8

NAWAZ

MS

12-AUG-2011

95

95

QASIM

9

AHMAD

Phd

07-APR-2008

100

100

WAHEED


      Our Expectations: Hopefully, you all would be fine. Here in this practice assignment. You all are required to create two tables (Instructor, Student) using SQL CREATE TABLE statement in SQL Server. Example CREATE TABLE practice queries have also been uploaded in “Downloads” section under VULMS.

 

           We need screenshots of all your work, which will be pasted in your solution file (DOC File).

 

Marks Distribution will be as under:

 CREATE TABLE Commands          =   5 (2.5 for each table) marks

 INSERT INTO Table Commands    =   5 (2.5 for each table)  marks    // For time saving you can only insert three records in each table.

 SELECT Query                                  =  10 marks

 

Solution:
Create database STUDENT_DATA

create table INSTRUCTOR(INSTRUCTOR_ID char(50),FIRST_NAME char(50),LAST_NAME char(50),LAST_DEGREE char(50),SALARY char(50),CITY char(50),HIRE_DATE datetime);


INSERT DATA INTO INSTRUCTOR TABLE,

Record no 1

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

record no 2

insert into INSTRUCTOR values(’55’,’MUHAMMAD’,’HASAN’,’MS’,’42000′,’ISLD’,’16-FEB-2011′);

record no 3

insert into INSTRUCTOR values(’60’,’TALHA’,’ALI’,’PHD’,’80000′,’LHR’,’1-JUNE-2011′);

CREATE TABLE STUDENT

create table STUDENT(STUDENT_ID char(50),STU_NAME char(50),SEMESTER char(50),SPECIALIZATION char(50),INSTRUCTOR_ID char(50));

INSERT DATA INTO STUDENT TABLE

Record no 1

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

Record no 2

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

record no 3

insert into STUDENT values(‘3′,’SAJID’,’3′,’DATABASE’,’60’);

SELECT QUERY STATEMENT

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=INSTRUCTOR.INSTRUCTOR_ID;

DOWNLOAD SOLUTION HERE
loading...
  • Mc100401759

    very nice…………….brother……………