MIS 4340/5340 SQL Assignment
Purpose:
As IS professionals, you should be comfortable with writing SQL to create, maintain, and query tables. For this assignment, you will
use SQL Server Management Studio to execute SQL commands.
Assignment:
Create SQL commands that address the requirements on the next page. The commands should access the tables shown in the ERD
in this assignment. Only access columns shown on the ERD. Be sure to review the “What to turn in” and “Additional
Requirements” sections for more requirements related to the assignment. Only ONE SQL command is required per question.
Download and restore the “FlightsDB.bak” backup file to your copy of SQL Server (your instance of SQL Server). The tables in this
database originate from the OpenFlights.org/data.html website. The zip file containing the backup is in Canvas, SQL module.
Instructions for restoring a backup file to SQL Server are also in Canvas, SQL module.
Note that for problem #6, you will provide the required SQL command as with the other problems, AND inside of comments you will
also provide information about the location you chose for the query. For problem #7, you will enclose BOTH the required SQL
command AND your justification inside of comments. DO NOT EXECUTE the command for however if you wish, you may test
the command by creating a test table in your personal database that you created in class (FirstNameLastName), and then executing
a similar command using your test table (after your test be sure to delete the test table).
When you are done writing (and testing) each query, save your script. file. Turn on the SSMS feature to send query results to file,
then execute your script. with all the SQL statements addressing the problems. Ensure all your results have been saved in the file you
specified. Queries are worth different amounts but average to approximately 6 points each.
What to turn in through Canvas:
(1) A source script. file with your SQL statements (*.sql). I should be able to take your script. file and run it without a hitch. Part
of your grade is to ensure that the script. file will run completely without errors.
a. Copy/paste the command descriptions into a script. file. Each description should be followed by the SQL command
that addresses the problem description. For example:
/* 1. List the phone number and first name of all customers with a LastName of 'Miller'. */
Select …
From …
/* 2. List the phone number and first name of all customers whose last name includes the characters 'cat' */
Select …..
From …
…..
Additional Requirements for turning in your assignment:
➢ At the TOP of the script. file of your submitted assignment, put the following as comments:
o Assignment name ("SQL Assignment")
o Your name
o Class time
➢ Use ONLY the SQL clauses and commands learned in class.
➢ Access ONLY the fields shown on the ERD(s).
➢ Use only ONE SQL command per problem.
➢ Use aliasing to create user-friendly labels for all expressions/functions.
➢ If you made assumptions for a problem, type the Assumptions as comments in your script. file.
➢ Include all information requested above. Points are deducted if anything is missing, handwritten, or inconsistent with the
assignment requirements.
➢ Turn in your own work. Solutions violating the honor code and/or assignment pairing guidelines WILL receive a grade of
zero and the potential for further consequences as described on the syllabus.
Assignment Problems:
/* 1. Find available routes from the Waco area to the Tampa area.
Display the Waco Airline, Waco Airport Name, Dallas Airline, and Dallas Airport Name
HINT1: All routes out of "Waco" must go through a "Dallas" airport first.
HINT2: Consider including subqueries in the query
HINT3: Consider including a self-join in the query */
/* 2. Find airports in the country of Zambia that do not have any recorded routes.
Show results in order of the airport's city. */
/* 3. Show the number of different airlines and number of routes using E75 equipment */
/* 4. Show the name, city and number of routes of the 3 busiest airports (i.e., airports
with the most number of flights departing from that airport. Account for ties. */
/* 5. Show Timezones (i.e., TZ's) that have more than 100 airports.
Display TZ's with the largest number of airports first. */
/* 6. Write a query to find the ONE closest airport to a location of interest to you.
Your query should display meaningful information about the closest airport.
Before writing your query, find the GPS coordinates (latitude/longitude) of a specific
location of interest to you (you can google this). Then in your query, the following
calculation can be used in an Order By to determine the distance in km between a
location of interest and the latitude/longitude of an airport:
111.045 * DEGREES(ACOS(COS(RADIANS(@latitude)) * COS(RADIANS(latitude))
* COS(RADIANS(longitude) - RADIANS(@longitude))
+ SIN(RADIANS(@latitude)) * SIN(RADIANS(latitude))))
Replace ONLY the 3 variables in the above calculation with the longitude & latitude values
of your location of interest; leave all else the same. Variables begin with an @ symbol.
There should be no @ symbols left in the query once you supply your coordinates.
ALSO, inside of comments, tell me what location is represented by the coordinates
you included in the query. */
/* 7. Based on the queries you have written for this assignment, write the SQL
command that would create a secondary index that could improve performance of
one or more of your queries. DO NOT EXECUTE THIS COMMAND—include it inside of comments.
ALSO inside of comments, justify why you chose the field(s) for the index. */
From OpenFlights.org:
Airports Table
Airport ID Unique OpenFlights identifier for this airport.
Name Name of airport. May or may not contain the City name.
City Main city served by airport. May be spelled differently from Name.
Country Country or territory where airport is located. See countries.dat to cross-reference to ISO 3166-1 codes.
IATA 3-letter IATA code. Null if not assigned/unknown.
ICAO 4-letter ICAO code. Null if not assigned.
Latitude Decimal degrees, usually to six significant digits. Negative is South, positive is North.
Longitude Decimal degrees, usually to six significant digits. Negative is West, positive is East.
Altitude In feet.
Timezone Hours offset from UTC. Fractional hours are expressed as decimals, eg. India is 5.5.
DST Daylight savings time. One of E (Europe), A (US/Canada), S (South America), O (Australia), Z (New Zealand), N (None) or U (Unknown). See also: Help: Time
Tz database
time zone Timezone in "tz" (Olson) format, eg. "America/Los_Angeles".
Type Type of the airport. Value "airport" for air terminals, "station" for train stations, "port" for ferry terminals and "unknown" if not known. In airports.csv, only type=airport is included.
Source
Source of this data. "OurAirports" for data sourced from OurAirports, "Legacy" for old data not matched
to OurAirports (mostly DAFIF), "User" for unverified user contributions. In airports.csv, only
source=OurAirports is included.
Airlines Table
Airline ID Unique OpenFlights identifier for this airline.
Name Name of the airline.
Alias Alias of the airline. For example, All Nippon Airways is commonly known as "ANA".
IATA 2-letter IATA code, if available.
ICAO 3-letter ICAO code, if available.
Callsign Airline callsign.
Country Country or territory where airline is incorporated.
Active
"Y" if the airline is or has until recently been operational, "N" if it is defunct. This field is not reliable: in
particular, major airlines that stopped flying long ago, but have not had their IATA code reassigned (eg.
Ansett/AN), will incorrectly show as "Y".
Routes Table
Airline 2-letter (IATA) or 3-letter (ICAO) code of the airline.
Airline ID Unique OpenFlights identifier for airline (see Airline).
Source airport 3-letter (IATA) or 4-letter (ICAO) code of the source airport.
Source airport ID Unique OpenFlights identifier for source airport (see Airport)
Destination airport 3-letter (IATA) or 4-letter (ICAO) code of the destination airport.
Destination airport ID Unique OpenFlights identifier for destination airport (see Airport)
Codeshare "Y" if this flight is a codeshare (that is, not operated by Airline, but another carrier), empty otherwise.
Stops Number of stops on this flight ("0" for direct)
Equipment 3-letter codes for plane type(s) generally used on this flight, separated by spaces