Pages

Sunday, 24 June 2012

-CS403 Assignment no 3 Spring 2012



Assignment No. 04
Semester Fall 2012
Database Management Systems-CS403

Objective:
To learn and understand basic concepts of SQL.
Instructions:
Please read the following instructions carefully before solving & submitting assignment:
Assignment should be in your own wordings not copied from net, handouts or books.

It should be clear that your assignment will not get any credit (zero marks) if:

  • The assignment is submitted after due date.
  • The submitted assignment does not open or file corrupt.
  •  Student ID is not mentioned in the assignment File or name of file is other than student ID.
  • Assignment is not submitted in DOC file.

For any query about the assignment, contact at cs403@vu.edu.pk

GOOD LUCK

 

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

Use the following tables to solve this assignment.
                  
       
                    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