Exercises using SQL

In this repository are listed some of the SQL exercises used in preparation for the Database 1 exam at the University of Padova.

Airports

The first database contains three tables: Cities, Airplane_Types and Flights and it is structured as follows:

Cities (CityName, Country)

Airplane_Types(AircraftType, Capacity)

Flights(FlightID, DayOfWeek, DepartureCityName, ArrivalCityName, AircraftType)

The creation of the database can be achieved by running on your DBMS the script shown in airports-db.sql file, where the tables are created and the inserts are randomly generated.

Queries

The following queries are implemented. It is strongly recommended to analyze the query code, this list's purpose is to specify the contract utilized in the query but it does not explain the query functioning.

query-1

List the scheduled flights using a known type of aircraft. This is a simple query example with the three basic statements, SELECT FROM WHERE

query-2

List the types of aircraft used in flights departing from Singapore. This is a simple query with the introduction of the boolean operator AND

query-3

List the countries from which flights depart that can carry at least 200 passengers. In this query, the INNER JOIN operation is introduced

query-4

For the flight with ID 56, list the day of the week, the departure country, and the arrival country. Here, the INNER JOIN operation is accomplished two times on the same table

query-5

List the cities from which international flights depart. Like in the 4th, the JOIN operation is done 2 times in the same table

query-6

For each flight departing from Rome, list the arrival city and, if available, the type and capacity of the aircraft. In this case, the LEFT JOIN is utilized instead

query-7

List the cities from which there are direct flights to Osaka, ordered alphabetically. In this query, the GROUP BY and ORDER BY statements are combined

query-8

Indicate the number of international flights departing on Monday from Manchester. The mathematical function COUNT() is introduced

query-9

For each Italian city, indicate the number of international flights departing. The list should only contain cities where this number is > 0. Here we can find a combination of COUNT() and GROUP BY statements

query-10

List the French cities from which more than two flights depart weekly directly to Italy. In this query, it is used the HAVING statement in combination with COUNT() function

query-11

List every city connected to Melbourne by a flight (either departing from or arriving at Melbourne). The set operation UNION is used between two queries

query-12

List the cities connected EXCLUSIVELY by Tuesday flights, meaning flights departing from or arriving at these cities only on Tuesdays. The list should have a single column. Avoid using JOIN. Here we can find a combination of UNION and EXCEPT set operations

query-13

List the cities served by aircraft types with the maximum capacity. The list should have a single column. Here, the nested queries are introduced

query-14

List the Spanish cities with the highest "reception." The reception of a city is obtained by summing the capacity of the flights arriving in that city. The list should have a single column.

query-15

List the USA cities from which there are no flights departing for abroad. The list should have a single column. In this query, it can be used both NOT IN and NOT EXISTS, operators

query-16

For each city, list the types of aircraft with the minimum capacity among all flights departing from that city. The first case of intra-query join.

Music

The second database contains all the tables needed to manage music albums of different singers. Its structure is the following:

People(PersonName, BirthYear)

Songs(SongID, SongTitle, SongYear)

Song_Authors(PersonName, SongID)

Song_Singers(PersonName, SongID)

Albums(AlbumID, AlbumTile, AlbumYear)

Album_Songs(AlbumID, TrackID, SongID)

The creation of the database can be achieved by running on your DBMS the script shown in music-db.sql file.

Queries

The following queries are implemented. It is strongly recommended to analyze the query code, this list's purpose is to specify the contract utilized in the query but it does not explain the query functioning.

query-1

The titles of the albums that contain at least one popular song. A popular song is a song whose publication date is unknown. In this query, a nested query is utilized-

query-2

The titles and singers of the songs from the album with identifier 3, sorted by track identifier. This query has been implemented by the ORDER BY construct.

query-3

The names of individuals whose names start with "T" and who have sung a song written by them. In this query the LIKE construct is used for the first time.

query-4

The names of the authors who have never sung a song and the names of the singers who have never written a song, listed in a single column. The query is implemented by the UNION set operator and using the construct NOT IN and DISTINCT.

query-5

The identifier of the album that contains the highest number of songs. This query calculates the maximum through 2 nested queries. The first one is used in the HAVING statement while the second is used in the FROM statement of the first nested query. Note that this query could be implemented by using a view.

query-6

The identifiers of the albums containing songs that are all by a single singer and containing at least three songs from years preceding the album's year. To implement this query it is used the INTERSECT operator between two other queries in which there is the COUNT operation in the HAVING statement.

query-7

The names of the singers who have never sung an entire album alone. In this query, a `VIEW`` is implemented to improve the readability of the query.

query-8

The names of the singers who have never sung a song as soloists. This query uses a VIEW construct and then intersects two other queries.

query-9

The names of the singers who have only sung as soloists. A similar approach is used here as well, relying on a VIEW and the `EXCEPT`` set operator.

query-10

For each song identifier, find the identifier of the album in which the song was first published and find the year of this publication.

Troubleshooting

Some of the queries in this repository may be wrong or unfinished. If you want to help improve the queries, I strongly suggest you follow these instructions.

Important

1. Use the fork function to clone the repository to your GitHub account

2. Create a new branch and make the modifications and additions to the project

3. Submit a pull request and wait for the changes to be reviewed

Additionally, GitHub issues can be used to report a minor error.

Collaborators

• Alessandro Trigolo (GitHub: @imAlessas)