CvLTAELG1AAv7GMjp8RSaPGsvS8 CS403 Assignments # 3 Solution Spring 2012 |
Sunday , 17 February 2019
You are here: Home - CS403 - CS403 Assignments # 3 Solution Spring 2012

# CS403 Assignments # 3 Solution Spring 2012

1(a). Consider the table below:                                          Marks: 5

Documentary Table

 Doc_No Title Ranking YearReleased 1 Hoop Dreams A 1994 2 The Thin Blue Line B 2000 3 Surprise Me A 2004 4 Trouble the Water C 2008 5 Dark Days B 2000 6 ParisIn Burning B 1991

You are required to write an SQL statement using SELECT operator, which will give the following resultant table.

 Title YearReleased The Thin Blue Line 2000 Dark Days 2000 ParisIn Burning 1991

Solution can be found on next page…

Solution(a):

ALL SQL Statements…

Create table Documentary (

Doc_No int,

Title  Varchar(50),

Ranking varchar(2),

YearReleased smalldatetime );

Select * from Documentary;

Insert into documentary (Doc_No,Title,Ranking,YearReleased) values (1,’Hoop Dreams’,’A’,’1994′);

Insert into documentary (Doc_No,Title,Ranking,YearReleased) values (2,’The Thin Blue Line’,’B’,’2000′);

Insert into documentary (Doc_No,Title,Ranking,YearReleased) values (3,’Surprise Me ‘,’A’,’2004′);

Insert into documentary (Doc_No,Title,Ranking,YearReleased) values (4,’Trouble the Water ‘,’C’,’2008′);

Insert into documentary (Doc_No,Title,Ranking,YearReleased) values (5,’Dark Days’,’B’,’2000′);

Insert into documentary (Doc_No,Title,Ranking,YearReleased) values (6,’Paris In Burning’,’B’,’1991′);

Insert into documentary (Doc_No,Title,Ranking,YearReleased) values (7,’Man on Wire’,’A’,’2008′);

Select * from Documentary;

Select Title, Year Released from documentary where Ranking = ‘B’;

Note: This Query will written to find the resultant table.

1(b). Consider the table below:                                     Marks: 5

Documentary Table

 Doc_No Title Ranking YearReleased 1 Hoop Dreams A 1994 2 The Thin Blue Line B 2000 3 Surprise Me A 2004 4 Trouble the Water C 2008 5 Dark Days B 2000 6 Parisof Burning B 1991 7 Man on Wire A 2008

You are required to show the resultant relation that will be produced by the following SQL SELECT statement.

SELECT Doc_No, Title, YearReleased

FROM

Documentary

WHERE  YearReleased <>‘2000’;

Solution(b): Resultant table would be:

 Doc_No Title YearReleased 1 Hoop Dreams 1994 3 Surprise Me 2004 4 Trouble the Water 2008 6 Parisof Burning 1991 7 Man on Wire 2008