Create a MySQL query (using stored procedures?)
$30-520 NZD
Πληρώθηκε κατά την παράδοση
We have a MySQL database of all flight routes and schedules in the world, which is updated weekly. We require a freelancer to:
1) Create the proper indexes (hash indexes?) for maximum efficiency
2) Write an SQL query (perhaps using stored procedures?) that recursively searches for a specific route, given inputs of origin, destination, departure date, minimum connection time, and maximum number of connections.
For example, say I wanted to find a route that took me from Auckland, New Zealand (AKL) to London, United Kingdom (LHR), with a minimum of 2 hours between flights and a maximum of 2 connections.
A. The query would first look at Auckland Airport (AKL), and find every possible destination and flight time available from there.
B. The query would then look at all destinations available from each airport returned from the previous result (such as SYD, MEL, BNE, HKG, etc), that have flights leaving at least 2 hours after the arrival from AKL.
C. The query would then look at all destinations available from *those* airports returned from the previous result that have flights leaving at least 2 hours after the arrival from the previous airports.
D. Because the last airport was teh final connection, this step would look only for flights leaving at least 2 hours after the arrival which have their destination as LHR.
Note that flights with less connections can (and should) be returned also. For example, if there was a direct flight between AKL and LHR, or a flight with one connection (AKL-HKG-LHR).
Some flights do not operate on certain days, so day of the week is also important, and should be deciphered from departure date provided. You also need to confirm the effective_from and effective_to dates to ensure you are using the right schedule for the date.
The query should be able to return a result for 2 connections in less than 10 seconds.
Example data is attached and is a result of running the following query:
select * from schedules WHERE (origin='AKL' OR origin='SYD' OR origin='MEL' OR origin='SIN' OR origin='HKG') AND (destination='AKL' OR destination='SYD' OR destination='MEL' OR destination='SIN' OR destination='HKG' OR destination='LHR') AND codeshare IS NULL
Ταυτότητα Εργασίας: #9752863
Σχετικά με την εργασία
Ανατέθηκε στον:
11 freelancers κάνουν προσφορές κατά μέσο όρο $170 για αυτή τη δουλειά
Hi! I'm senior lecturer on databases and I've more 10 years experience in MySQL. I can create indexes and effective query on your task.
Dear Sir/Madam, I have over 8 years of work experience in PHP, MySQL, Database Development, Database Programming, HTML, CSS, Linux, Javascript, jQuery, .NET, Bootstrap, C, C++, C# and so on... Please visit the Περισσότερα
I am oracle certified professional and expert in databases and query design. I can do the task as per your requirements.
Hi Hope you are fine I am qualified webdeveloper with experience in C#,Asp.net ,mvc,SqlServer 2008r2.. fully understand your query. I have a logic to implement it which will display result according to your des Περισσότερα
hello sir plz let me know if u want help from myself. ------------------------------------------------------------------------------------------ Regards Ravi Sharma
the best case who require me seeing the structure of the tables in question even with dummy data in order to assess and write the best way possible. ITS basically more of a mathematically challenge (Permutations) wri Περισσότερα