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: 6004

.

+ 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

Please Discuss here about this assignment.Thanks

Our main purpose here discussion not just Solution

We are here with you hands in hands to facilitate your learning and do not appreciate the idea of copying or replicating solutions. Read More>>

 

Note:-

For Important Helping Material related to this subject (Solved MCQs, Short Notes, Solved past Papers, E-Books, FAQ,Short Questions Answers & more). You must view all the featured Discussion in this subject group.

For how you can view all the Featured discussions click on the Back to Subject Name Discussions link below the title of this Discussion & then under featured Discussion corner click on the view all link.

Or visit this link 

Click Here For Detail.

&

.•°How to Download past papers from study groups°•.

 

Please Click on the below link to see…

.... How to Find Your Subject Study Group & Join .... 

 

Dear all don't wait the solution just share your ideas. As we know about SQL and it's queries so please share what you know and what you have problems and clear your concepts, discuss each other about assignment

Dears, mujhe tables ko connect karne k bad os mein values add karna nahi arahi hai... can anyone help me to understand.

Q_01 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.

Ans_01
select Train_Name from train
where
(departureTime between '15:00' and '18:00')
and
(DepartureDate between '2017-01-24' and '2017-01-26')

Kindly try it and tell me that, is it wrong or right?

1 SQL installation

Create Database ,Table and Insert and Select Command in SQL

Dear Students Don’t wait for solution post your problems here and discuss ... after discussion a perfect solution will come in a result. So, Start it now, replies here give your comments according to your knowledge and understandings....

Question: (20 Marks)
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.
1.Database Screen Shot

2. Table_1 Screen Shot

3. Table_2 Screen Shot
b) Now you have to perform the following tasks using SQL queries:
1. 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 * From Train
Where DepartureTime Between '15:00:00' AND '18:00:00' AND DepartureDate Between '2017-01-24' AND '2017-01-26'
2. Select all the Train_IDs with fares which have fare less than 1000 and greater than 400 for destination location Karachi.

SELECT Distinct(Train.Train_ID), Passenger.Fare
FROM Train INNER JOIN
Passenger ON Train.Train_ID = Passenger.Train_ID
Where Fare<'1000.00' AND Fare>'400.00' AND DestinationLocation='Karachi'

3. Show the summary of each Train_ID and tickets sold against each Train_ID.
SELECT Train.Train_ID, Count(Passenger.TicketNo) AS TicketSold
FROM Train INNER JOIN
Passenger ON Train.Train_ID = Passenger.Train_ID
Group By
Train.Train_ID
4. Show Train_ID, Train name along with fares collected against each item.
SELECT Train.Train_ID, Train.TrainName,SUM(Passenger.Fare) AS FareCollected
FROM Train INNER JOIN
Passenger ON Train.Train_ID = Passenger.Train_ID
Group By
Train.Train_ID, Train.TrainName

5. 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.
SELECT Train.Train_ID, Train.TrainName
FROM Train INNER JOIN
Passenger ON Train.Train_ID = Passenger.Train_ID
Group By

Train.Train_ID, Train.TrainName
Order BY
SUM(Passenger.Fare) ASC

In Q 3  use left join instead of inner join due to, if any train sales ticket zero then  this train also show in summary 

PLEASE HELP IN THE 4TH CONDITION I HAVE TWO ROWS WHICH HAVE SAME TRAIN ANME BUT DIFFERENT FARES HOW TO ADD THESE FARES AND SHOW AS A SINGLE RECORD I.E
TRAINID TRAINNAME FARE
1 AKABAR XPRESS 200
2 AKABAR XPRESS 300
----------------------------- AFTER MERGE THESE TWO
1 AKBAR XPRESS 500 SO IT CAN COUNT TICKET & SALES AGAINST EACH TRAIN CORRECTLY

RSS

Latest Activity

Today Top Members 

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

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