SQL EXERCISES

AIRPORT
Perform the following exercise in SQL Server, entering the instructions in
command line and showing captures of the instructions used
as well as the results obtained. Call the airport database.
plus the student's name.

1.- Create the database and tables.

1.-Create the database
2.-Create the tables: flights, bookings, airplanes
3.-Show database diagram

4.-Enter data into the tables.

 Take into account the order in which to create the tables
Relationships are created by creating tables and can be seen in the
database diagram

airplanes
capacity length wingspan cruising speed
320 187 42.15 32.6 853
72S 160 36.2 25.2 820
737 172 38.9 29 793
73S 185 44.1 30, 35 815
DS9 110 38.3 28.5 815
reservations
numero_de_vuelo fecha_de_salida free spaces
BA467 February 20, 1992 32
BA467 21-Feb-92 49
BA467 22-feb-92 79
IB0640 20-feb-92 15
IB0640 21-Feb-92 21
IB0640 22-feb-92 39
IB3709 February 20, 1992 60
IB3709 21-feb-92 72
IB3709 22-feb-92 85
IB510 20-feb-92 19
IB510 21-feb-92 31
IB510 22-feb-92 40
IB600 20-feb-92 46
IB600 21-Feb-92 80
IB600 22-feb-92 91
flights
numero_de_vuelo origin destiny flight_departure_time
AF577 BILBAO PARIS 10:10:0072S
BA467 MADRID LONDON 20:40:0073S
IB023 MADRID Tenerife 21:20:00320
IB0640 MADRID BARCELONA 6:45:00 DS9
IB318 SEVILLE MADRID 10:45:00320
IB327 MADRID SEVILLE 18:05:00320
IB368 MALAGA BARCELONA 22:25:00737
IB3709 DUBLIN BARCELONA 14:35:00737
IB3742 MADRID BARCELONA 9:15:00320
 airplanes
tpo capacity length wingspan cruising speed
IB510 SEVILLE MADRID 7:45:00320
IB600 MADRID LONDON 10:30:00DS9
IB610 MALAGA LONDON 15:05:00320
IB721 BARCELONA SEVILLE 16:40:00320
IB77B BARCELONA ROME 9:45:00320
LH1349 COPENHAGEN FRANKFURT 10:20:00 DS9

2.- Simple queries.

5.- Select from the flights table the columns origin, destination, and departure time for
all its rows:

6.-Similar to the previous one, but the order of the columns in the output list should be:
origen, hora de salida, destno:
8.- From the previous result, remove any redundant information:

With DISTINCT we remove redundancy, the problem is that in the field

DEPARTURE_TIME since there are no repeated values the values of the other two fields
they will continue with redundancy because the three fields are shown together in the same
table; if we separate the DEPARTURE_TIME from the two fields ORIGIN and DESTINATION
we would have something like this:

So in this case we would already have less redundancy in the data. And if
if we placed a single field (column), there would no longer be redundancy in the data.
9.- Place the correct order to view the content of the entire table in the list.
Perform it with two different commands:

3.- Queries with predicate

10.-Let's suppose that we want to retrieve from the flights table only those that depart
from Madrid:
11.-Retrieve the number of flights and the departure time of all flights that operate
the route Madrid-London:

12.- Retrieve the flights (complete row) that have London as the destination but do not
they leave from Madrid:

I will put it in two ways:

13.-Retrieve all flights that are between Madrid and Seville:

14.-Retrieve all flights departing from Madrid and arriving in Barcelona or Sevilla:

15.-Retrieve flights departing from Madrid, Barcelona or Seville:

16.-Retrieve all flights except those departing from Copenhagen or Dublin:

17.-Retrieve all flights departing from 6 to 12 in the morning:

18.-Recover all flights that are not Iberia:

19.-Retrieve all flights from the Iberia company:

 4.- Arithmetic expressions and functions.

20.-Visualize the airplanes table but with the length and wingspan expressed in feet.
(the table is in meters), and the cruising speed in miles/hour (in the table is
in kilometers/hour):

21. - Obtain the relationship between length and speed of all airplanes:

22.-Select the planes whose length exceeds their wingspan by more than 10%:
23.-Obtain the minimum and maximum values of the cruising speed:

24.- Get the first flight leaving from Madrid:

25.-It is desired to know how many reservations have more than 50 free spots:

26.-It is desired to know how many reservations exist in the table:

27.-Recover the number of distinct destinations that appear in the flights table:

28.-Number of seats available on all flights on February 20, 1992:

29.-Total number of available spots that exist for all days:

30.-Recover the average capacity of the planes:

31.-Recover the columns: type of plane, capacity, length, ratio between
capacity and length with decimal result, cruising speed, wingspan and
relationship between cruise speed and wingspan in integer format:

32.-Retrieve a list of cities of origin and the length of the name size
the city

33.-From the list of cities of origin, obtain another list of the 2nd, 3rd, and 4th characters, and
another list of the 3rd and 4th characters:
34.-Obtener una lista de ciudades de origen y otra con los nombres de la ciudad de
origin to which is added the first character of the name of the city and the three
last

35.-What does the statement do: SELECT DISTINCT FECHA_SALIDA, YEAR (FECHA_SALIDA),
MONTH (FECHA_SALIDA), DAY (FECHA_SALIDA) FROM RESERVAS.:

I took the bother of putting an AS to the different columns, YEAR, MONTH, and DAY.

Well, we are practically asking the query to give us the DEPARTURE_DATE.

from the reservations and we also request the year in one column, the month in another and the day in
another column and with DISTINCT we are telling it that we do not want values
redundant, that is to say repeated.
36.-What does the statement do: SELECT HORA_SALIDA, HOUR(HORA_SALIDA), MINUTE
(DEPARTURE_TIME), SECOND (DEPARTURE_TIME) FROM FLIGHTS.

Here we are asking the column to give us the departure time of the flights and
as in the previous exercise, that it also shows us in separate columns the
HOURS, MINUTES, AND SECONDS. In this case, we can see the tempo of each column.
in the same table.

37.-Find out how many days have passed for each reservation between the departure date and today.
1/3/92:
 5.- Queries with row grouping.

38.-Visualize the flights that depart the soonest from each of the origins:

39.-Visualize the flights that take off the soonest, but taking into account that the
Those that have Barcelona destiny will not be included:

40.-What does the statement do: SELECT ORIGEN, MIN(HORA_SALIDA), MAX

(DEPARTURE_TIME), COUNT (*) FROM FLIGHTS GROUP BY ORIGIN.
What it tells us is: Give me the origin, the flights that depart the earliest, those that
depart later or the last minute departures, and give me the number of flights that
They leave at that time, finally I want you to group them by origin.

41.-Similar to the previous one but the groups whose last hour is not to be displayed
departure is after 16:00 hours:

42.-Find for each origin, the last flight that departs and how many leave, not showing
the groups whose last flight is after 4:00 PM, that there is more than one
flight, and that the origin is neither Dublin nor Copenhagen:

43.-Get the earliest departure time for each origin and destination:
44.-Show the total number of available seats for each flight number:

45.-Show the total number of available seats for each flight number.
Iberia:

46.- They want to see those Iberia flights that have a total of more than 150 seats.
free:
 6.- Nested queries.

47.-Let's suppose that a traveler asks for a ticket for the Madrid-London flight of the
20:40 on February 21, 1992. Before giving the ticket, it must be checked that
there are free places (this information is only found in the reservations table, in which
flights are identified by their number, not by their route and departure time). It is necessary to
first find out the corresponding number for the requested flight:

48.-Recover the available seats on each of the Madrid-London flights for

February 20, 1992:

Second way to do it:

49.-Obtain the positions of planes and their capacities for those that remain.
less than 30 free spots:

50.-Retrieve the planes whose capacity is less than double that of any of the
average number of vacant spots per day:

51.-Recover the planes whose capacity is less than double that of any of the
average number of free spots per day:
52.-Suppose we want to recover the airplanes that make their route in
less than an hour and a quarter medium. Expand the flights table for this
column more in which the distance between origin and destination is included. The duration
approximately the journey will be: distance / cruising speed:

53.-Recover the bookings whose number of free spots is greater than the average for
that same flight:

54.-Select the flight number, origin, and destination of those flights originating from
Madrid for those who will have available spots:
55.-Retrieve the planes (with all their characteristics) that do not pass through Barcelona.
It is the same as recovering all the planes for which there is no flight with
origin or destination Barcelona:

56.-Recover the number of available seats on the Madrid-London flight at 20:40 for
February 21, 1992:

57.-Recover the available seats on each of the Madrid-London flights for

February 20, 1992:
58.-Obtain the positions of planes and their capacities for those that remain.
less than 30 available spots

59.-Get the number of free spots remaining (across all days) for each
flights and sort the result from most to least seats. For the same number of seats, it
will sort by flight number:

60.-Suppose you want a list of all the cities for which there are flights,
Whether they appear as origin or as destination, ordered. Do not make two queries.
do not list the same cities multiple times:
 7.- Update Statements.

61.-Insert a new row in the reservations table with the values: IB600 for the field
num_vuelo, 23-02-92 para el campo fecha_salida y 45 para el campo de plazas libres:

62.-Insert into the reservations table, the num_flight field for the records whose field
origin is Seville:

We have had to alter the table with the statement written below, as when executing
the insert of the PK of the Reservations table did not allow us to execute the insert correctly.

63.-Change the aircraft type of the flight Málaga-London at 15:05, putting D9S:
64.-Reduce the capacity of all aircraft by 10%:

65.-Remove from the reservations table the records with less than 50 seats.
free

66.-Delete all records from the reservations file:

8.- Create indexes.

67.-Create an index called IXVUELOS on the flights table, indexed by the field
origin and the destination field in ascending order:
68.-Create an index called IXRESERVAS on the reservations table, on the fields
num_vuelos y fecha_salida, en orden ascendente:

69.-Create an index on the airplanes table called IXAVIONES, on the field:

9.- Create views.

70.- What the command does: CREATE VIEW VISTA_VUELOS,

(V_ORIGEN,V_DESTINO,V_HORA_SALIDA) AS SELECT (ORIGEN,DESTINO,HORA_SALIDA)
FROM FLIGHTS.:
71.-Create a view with the fields flight_number and departure_date from the reservations table,
that includes Iberia flights:

72.- Create another view similar to the previous one, but that also includes the field:
plazas_libres:

73.-Visualize the content of the two previous views:

74.-Insert in view1 a new flight with the fields: flight_number: ib999,
fecha_salida: 29-02-1992, plazas_libres: 85. :

75.-Do the same in view2:

76.-Insert the same data into view2, but the flight number will be: ba999.

77.- Similar to the previous one, but do not allow insertion in the view that affects the table.
but not in sight:

I don't know how to do it no matter how much I search on the internet.

78.-Insert the values from exercise 76 into view2:

We already did this in exercise 75.

79.-Modify in view1 the number of available seats for flight ib510, setting it to 0 seats.
free

80.-Update flight number ba000 to flight ib510:

81.-Delete from view1 the records whose exit date is 20-02-92:

 10. Modify tables.

82.-Suppose you want to complete the reservation table specifying how many
Free seats are available in each class: first, preferential, and tourist.

83.-Delete the flight table:

84.-What does the command: DROP INDEX IXRESERVAS do?

What it does is delete the index ixreservas that is created in the RESERVAS table.
85.-Delete the IXVUELOS view:

11. Relational queries

86.-In the flight exercise, create a new table called DEPARTURES with the
following fields. The data will be taken from the FLIGHTS table.

NUM_VUELO ORIGIN HORA_SALIDA

87.-Create another table called incoming calls with the same structure as the table
previous and with the following data.

NUM_VUELO ORIGIN HORA_LLEGADA

IB222 GRAN CANARIA 22:00:00
IB432 LANZAROTE 15:05:00
IB212 THE PALM 12:00:00
IB410 LA GOMERA 09:15:00
IB610 SEVILLE 09:15:00
IB510 MADRID 18:08:00
88.-It is wanted to know all the hours where there is a flight operation.

89.-Obtain the origin cities from the ARRIVALS table that do not appear in the
output table.

90.-Obtain the Origin cities whether it is from the DEPARTURES table or from the
Arrivals table.
91. -In the previous case, prove what happens when it is introduced in the SELECT.
DISTINCT clause.

92.-Find the cities of origin where a DEPARTURE operation has taken place.
as well as the cities where LANDING operations have taken place (that
appear even if they are repeated.

93.-Find the origin cities in the DEPARTURES table that are not in the table
of ARRIVALS.
94.- Obtain the hours when there are ARRIVAL operations but no DEPARTURES.

95.-We wish to find the hours when an EXIT operation has occurred.
just as the hours where there has been an ARRIVAL operation (if they are different, they must
both appear and if they repeat as well.

96.-Obtain all the hours where there is an OUT and IN operation.

97.-Find the cities that appear as ORIGIN in both DEPARTURES and

Arrivals.
98.- It is desired to know the hours when there are EXIT operations but not of
ARRIVALS.

12. Create synonyms.

99.-Create a synonym to use as an abbreviation for the reservations table:

100.-Create another synonym for the reservations table:

101.-Delete the previously created synonyms:

13. Authorizations.

102.-What does the command: GRANT SELECT, UPDATE (FREE_SLOTS) ON TABLE do?
RESERVATIONS TO OPERATOR_RESERVATIONS;

Grant permissions to query the reservations table and update the available seats to
user OPERATOR_RESERVATIONS.

103.- The operator called HEAD_RESERVATIONS is to be granted all the

authorizations on the reservation table, in addition to being able to grant them to others.
users. SQL statement that performs this operation:

GRANT ALL ON RESERVAS TO JEFE_RESERVAS WITH GRANT OPTION

104.-They want to take privileges away from the reservations operator (user:
RESERVATION_OPERATOR), regarding the act

REVOKE SELECT, UPDATE (PLAZAS_LIBRES) ON RESERVAS TO

RESERVATION_OPERATOR