We are here with you hands in hands to facilitate your learning & don't appreciate the idea of copying or replicating solutions. Read More>>


Looking For Something at vustudents.ning.com? Click Here to Search

Assignment No. 04 Semester: Fall 2016
CS403 Database Management Systems


Total Marks: 20

Due Date: 07/02/2017
Uploading instructions:

Your assignment should be in .doc /.docx format.
Save your assignment with your ID (e.g. bc000000000.doc).
No assignment will be accepted through email.

Rules for Marking:

It should be clear that your assignment will not get any credit if:

The assignment is submitted after due date.
The submitted assignment does not open, execute or file is corrupted.
Your assignment is copied from internet, handouts or from any other student
(Strict disciplinary action will be taken in this case).

Assignment


Question: (20 Marks)


Two relations of a Railway Management System are given below:

Train (Train_ID, Train_Name, Source_Location, Destination_Location, DepartureDate, DepartureTime)

Passenger ( Train_ID, Passenger_ID, PName, Seat_No, Ticket_No, Fare)


Create a database with your VUID as you created in previous assignment (The name of database must be your own VU ID like BC12345678 otherwise zero marks will awarded) and add above mentioned tables in the database. Now add at least 10 to 20 proper dummy records in each of the table by keeping the constraints of Primary keys. Take screen shots of both tables along with dummy data and then paste to the MS Word file.

Now you have to perform the following tasks using SQL queries:

Select all train names whose departure time is in between 3:00 PM 6:00 PM and date between is 24 Jan 2017 26 Jan 2017.

Select all the Train_IDs with fares which have fare less than 1000 and greater than 400 for destination location Karachi.

Show the summary of each Train_ID and tickets sold against each Train_ID.

Show Train_ID, Train name alongwith fares collected against each item.

Show the list of Train_ID and Train name where least profitable train will be displayed first and most profitable train will be displayed at the end.

You are required to write the queries in MS Word. After that, execute each of the queries in SQL Server and take the screen shot of every query alongwith its result and then paste it to MS Word file (As given Below).


Table 1 Screenshot



Query 1 Screenshot:





NOTE: You are required to submit the MS Word file containing the solution of both parts. Do not put any query on MDB about this assignment, if you have any query then email at cs403@vu.edu.pk. Furthermore, if any student found cheating from any other student or from online forums then he/she will be awarded ZERO right away and strict disciplinary action will be taken against the student.


Deadline: Your assignment must be uploaded/submitted on or before 7th February 2017.

+ Click Here To Join also Our facebook study Group.

..How to Join Subject Study Groups & Get Helping Material?..


See Your Saved Posts Timeline

Views: 6005

.

+ http://bit.ly/vucodes (Link for Assignments, GDBs & Online Quizzes Solution)

+ http://bit.ly/papersvu (Link for Past Papers, Solved MCQs, Short Notes & More)

Attachments:

Replies to This Discussion

ap na SQL ka konsa version download kiya ha.

ma na bhi kiya ha but install nae ho raha ha.

https://www.youtube.com/watch?v=Usp9QYZwUAY
Sql server download and installation

agar solution ha tu send ker do? last date

bro mery sql server error ker raha ha agar apny assignment bana ly ha to mujy bhi bana do last date.

agar assignment bana li to hamy bhi dy do? email or vu id do mery sql error ker raha ha help last date.

cs403 assignment complete solution 2017

Attachments:
CS403 assignment no 4 video example

https://www.youtube.com/watch?v=q1h8b4qJ_80&t=72s

1. Trian Table 

create Table Train (Train_Id int primary key, 
Train_Name varchar (55), 
Source_Location varchar(55),
Destination_Location varchar(55), 
DepartureDate Date,
DepartureTime Time)

insert into Train Values( 1 ,' AWAM EXPRESS ',' KARACHI CANTT ',' SIALKOT ',' 01-19-2017 ',' 1:15:00 PM ')
insert into Train Values( 2 ,' ALLAMA IQBAL EXPRESS ',' KARACHI CANTT ',' PESHAWAR CANT ',' 01-22-2017 ',' 7:45:00 AM ')
insert into Train Values( 3 ,' BAHAUDDIN ZAKRIA EXPRESS ',' KARACHI CITY ',' MULTAN ',' 01-23-2017 ',' 6:35:00 PM ')
insert into Train Values( 4 ,' BURAQ EXPRESS ',' KARACHI ',' RAWALPINDI ',' 01-24-2017 ',' 3:15:00 PM ')
insert into Train Values( 5 ,' FAREED EXPRESS ',' KARACHI CITY ',' LAHORE ',' 01-24-2017 ',' 7:45:00 PM ')
insert into Train Values( 6 ,' KARACHI EXPRESS ',' KARACHI CANTT ',' LAHORE ',' 01-24-2017 ',' 4:30:00 PM ')
insert into Train Values( 7 ,' KARAKORAM EXPRESS ',' KARACHI CANTT ',' LAHORE ',' 01-25-2017 ',' 3:30:00 PM ')
insert into Train Values( 8 ,' KHYBER MAIL ',' KARACHI CANTT ',' PESHAWAR CANT ',' 01-25-2017 ',' 9:30:00 PM ')
insert into Train Values( 9 ,' MILAT EXP ',' KARACHI CANTT ',' FAISALABAD ',' 01-26-2017 ',' 4:15:00 PM ')
insert into Train Values( 10 ,' PAKISTAN EXPRESS ',' KARACHI ',' RAWALPINDI ',' 01-27-2017 ',' 2:30:00 PM ')
insert into Train Values( 11 ,' SHALIMAR EXPRESS ',' KARACHI CANTT ',' LAHORE ',' 01-28-2017 ',' 4:00:00 AM ')
insert into Train Values( 12 ,' SUKKUR EXPRESS ',' KARACHI CANTT ',' JACOBABAD ',' 01-29-2017 ',' 5:15:00 PM ')






2.Passenger Table:
Create Table Passenger (Train_Id int Foreign key references Train(Train_Id),
Passenger_ID Varchar(55) primary key , 
PName varchar(55), 
Seat_No varchar(55),
Ticket_No varchar(20),
Fare int)
GO

insert into Passenger values( 1 ,' Pass#11 ',' Ali ',' Seat#1 ',' Ticket#1 ', 800 )
insert into Passenger values( 2 ,' Pass#12 ',' Kamran ',' Seat#2 ',' Ticket#2 ', 200 )
insert into Passenger values( 3 ,' Pass#13 ',' Ali ',' Seat#3 ',' Ticket#3 ', 150 )
insert into Passenger values( 4 ,' Pass#14 ',' Noureen ',' Seat#4 ',' Ticket#4 ', 600 )
insert into Passenger values( 5 ,' Pass#15 ',' Saima ',' Seat#5 ',' Ticket#5 ', 999 )
insert into Passenger values( 6 ,' Pass#16 ',' Tahira ',' Seat#6 ',' Ticket#6 ', 1100 )
insert into Passenger values( 7 ,' Pass#17 ',' Maryam ',' Seat#7 ',' Ticket#7 ', 1100 )
insert into Passenger values( 8 ,' Pass#18 ',' Waqas ',' Seat#8 ',' Ticket#8 ', 1200 )
insert into Passenger values( 9 ,' Pass#19 ',' Javed ',' Seat#9 ',' Ticket#9 ', 200 )
insert into Passenger values( 10 ,' Pass#110 ',' Nouraiz ',' Seat#10 ',' Ticket#10 ', 600 )
insert into Passenger values( 11 ,' Pass#111 ',' Shakeel ',' Seat#11 ',' Ticket#11 ', 400 )
insert into Passenger values( 4 ,' Pass#112 ',' Saima ',' Seat#12 ',' Ticket#12 ', 200 )
insert into Passenger values( 5 ,' Pass#113 ',' Ali ',' Seat#13 ',' Ticket#13 ', 800 )
insert into Passenger values( 14 ,' Pass#114 ',' Saleem ',' Seat#14 ',' Ticket#14 ', 296 )
insert into Passenger values( 15 ,' Pass#115 ',' Kamran ',' Seat#15 ',' Ticket#15 ', 296 )
insert into Passenger values( 16 ,' Pass#116 ',' Amjad ',' Seat#16 ',' Ticket#16 ', 1200 )
insert into Passenger values( 17 ,' Pass#117 ',' Iqra ',' Seat#17 ',' Ticket#17 ', 900 )
insert into Passenger values( 18 ,' Pass#118 ',' Kamal ',' Seat#18 ',' Ticket#18 ', 211 )
insert into Passenger values( 19 ,' Pass#119 ',' Aqsa ',' Seat#19 ',' Ticket#19 ', 150 )
insert into Passenger values( 11 ,' Pass#120 ',' Tahira ',' Seat#20 ',' Ticket#20 ', 395 )
insert into Passenger values( 12 ,' Pass#121 ',' Tariq ',' Seat#21 ',' Ticket#21 ', 400 )
insert into Passenger values( 14 ,' Pass#122 ',' Mahum ',' Seat#22 ',' Ticket#22 ', 200 )
insert into Passenger values( 15 ,' Pass#123 ',' Irum ',' Seat#23 ',' Ticket#23 ', 150 )
insert into Passenger values( 16 ,' Pass#124 ',' Javed ',' Seat#24 ',' Ticket#24 ', 600 )
insert into Passenger values( 19 ,' Pass#125 ',' Kaleem ',' Seat#25 ',' Ticket#25 ', 1250 )
insert into Passenger values( 18 ,' Pass#126 ',' Zahid ',' Seat#26 ',' Ticket#26 ', 600 )
insert into Passenger values( 17 ,' Pass#127 ',' Saleem ',' Seat#27 ',' Ticket#27 ', 400 )
insert into Passenger values( 16 ,' Pass#128 ',' Naeem ',' Seat#28 ',' Ticket#28 ', 300 )
insert into Passenger values( 15 ,' Pass#129 ',' Akmal ',' Seat#29 ',' Ticket#29 ', 250 )
insert into Passenger values( 14 ,' Pass#130 ',' Nishat ',' Seat#30 ',' Ticket#30 ', 695 )
insert into Passenger values( 4 ,' Pass#131 ',' Shafique ',' Seat#31 ',' Ticket#31 ', 1100 )
insert into Passenger values( 5 ,' Pass#132 ',' Siddique ',' Seat#32 ',' Ticket#32 ', 1200 )

Select *From Passenger


B.1:
select Train_Name from Train Where DepartureTime between '3:00:00 PM' and '6:00:00 PM' and DepartureDate 
between '01-24-2017' and '01-26-2017' 

B.2:
select Train.Train_Id,Destination_Location,Passenger.Fare 
From Train,Passenger 
Where Fare >400 and Fare <1000 and Destination_Location='karachi' order by Fare asc?

B.3:

select Train.Train_Id ,Passenger.Ticket_No
From Train,Passenger
Where Train.Train_Id=Passenger.Train_Id order by Train_id

B.4:
select Train.Train_Id,Train_Name,Passenger.Fare
From Train,Passenger
Where Train.Train_Id=Passenger.Train_Id

B.5:
Select Train_Id ,Sum(Fare) as 'Fare Earned by Eeach Train' from Passenger Group By Train_Id order by sum(Fare) ASC

My assignment is completed. But how to mention am and pm in time. Because MSSQL Studio 2014 mai time(7) ka option hai bus.

agare assignment ban gy to hamy bhi solution dy do last date ha

hey if you have done assignment please take a screnshot by my id i am having error in downloaading sql server.

CS403 

Attachments:

RSS

© 2019   Created by + M.Tariq Malik.   Powered by

Promote Us  |  Report an Issue  |  Privacy Policy  |  Terms of Service