The create_engine function requires a connection string in order to work properly. The connection string for SQLite is very simple, and is just the location of the database, which is located in the data directory. Other relational database management systems have more complex connection strings. You will need to provide a username, password, hostname, port, and optionally, a database. You will also need to supply the SQL dialect and the driver. The general form for the connection string is as follows: dialect+driver://username:password@host:port/database. The driver for your particular relational database might need to be installed separately.

Once we have created the engine, selecting entire tables into DataFrames is very easy with the read_sql_table function in step 2. Each of the tables in the database has a primary key uniquely identifying each row. It is identified graphically with a key symbol in the diagram. In step 3, we link genres to tracks through GenreId. As we only care about the track length, we trim the tracks DataFrame down to just the columns we need before performing the merge. Once the tables have merged, we can answer the query with a basic groupby operation.

We go one step further and convert the integer milliseconds into a Timedelta object that is far easier to read. The key is passing in the correct unit of measurement as a string. Now that we have a Timedelta Series, we can use the dt attribute to access the floor method, which rounds the time down to the nearest second.

The query required to answer step 5 involves three tables. We can trim the tables down significantly to only the columns we need by passing them to the columns parameter. When using merge, the joining columns are not kept when they have the same name. In step 6, we could have assigned a column for the price times quantity with the following:

cust_inv['Total'] = cust_inv['Quantity'] * cust_inv['UnitPrice']

There is nothing wrong with assigning columns in this manner. We chose to dynamically create a new column with the assign method to allow a continuous chain of methods.