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

	Assignment No. 04 Semester Fall 2012 Database Management Systems-CS403		Total Marks: 20 Due Date: 27-06-2012
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: o        The assignment is submitted after due date. o        The submitted assignment does not open or file corrupt. o         Student ID is not mentioned in the assignment File or name of file is other than student ID. o        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