CS403 Database Management Systems Assignment 4 Solution Spring 2014

Task Description: 

In this assignment you will focus only on the tasks given below:

Node_Info

Node_id Node_role Protocol_id
01 Source 11
02 Source 12
03 Destination 13
04 Destination 14

 

Protocol_Info

Protocol_id Protocol_name Protocol_version
11  AODV 2.1
12 DSR 1.1
13 DSDV 1.5
14 AODV 1.1
15 DSR 1.2

 

  1. Open the Query Analyzer/SQL server management studio, and perform the below tasks in already created database (Assignment#03). If you did not create a database for assignment#03 then creates a database along with all tables and entity integrity constraints as mentioned in assignment#03 file.
  1. Insert above rows in each table i.e. Node_info and Protocol_info.

After inserting rows, Perform the below tasks:

  1. Write an SQL query to apply Right outer join between table Node_Info and Protocol_Info and show the result.
  2. Write an SQL query to apply inner join between table Node_Info and Protocol_Info and show the result.
  3. Write an SQL query to apply subquery (nested query) between table Node_Info and Protocol_Info , which shows the following results;
Protocol_id Protocol_name Protocol_version
11  AODV 2.1

 

  1. Take screenshots of your work, and paste them in your solution file (.DOC file).

Hint: You can use aggregate function i.e. max function to perform the 5th task.

DOWNLOAD SOLUTION HERE
loading...