Sakila 데이터베이스로 익히는 MySQL 연습 실제

5. Sakila 데이터베이스로 익히는 MySQL 연습 실제¶

• sakila 테이블 정보 참고
  (상세한 테이블 컬럼, 어떻게 수집되는지, 누구도 꼼꼼하게 설명해주지 않을 수 있습니다. 가능한 문서 또는 실제 컬럼값 보면서 직접 확인해보셔야 합니다.)

• https://downloads.mysql.com/docs/sakila-en.pdf

5.1 Tables
The following sections describe the tables that make up the Sakila sample database, in alphabetic order.

5.1.1 The actor Table
The actor table lists information for all actors.
The actor table is joined to the film table by means of the film_actor table.

Columns
• actor_id: A surrogate primary key used to uniquely identify each actor in the table.
• first_name: The actor's first name.
• last_name: The actor's last name.
• last_update: The time that the row was created or most recently updated.

5.1.2 The address Table
The address table contains address information for customers, staff, and stores.
The address table primary key appears as a foreign key in the customer, staff, and store tables.

Columns
• address_id: A surrogate primary key used to uniquely identify each address in the table.
• address: The first line of an address.
• address2: An optional second line of an address.
• district: The region of an address, this may be a state, province, prefecture, etc.
• city_id: A foreign key pointing to the city table.
• postal_code: The postal code or ZIP code of the address (where applicable).
• phone: The telephone number for the address.
• last_update: The time that the row was created or most recently updated.
• location: A Geometry column with a spatial index on it.

5.1.3 The category Table
The category table lists the categories that can be assigned to a film.
The category table is joined to the film table by means of the film_category table. 

Columns
• category_id: A surrogate primary key used to uniquely identify each category in the table.
• name: The name of the category.
• last_update: The time that the row was created or most recently updated.

5.1.4 The city Table
The city table contains a list of cities.
The city table is referred to by a foreign key in the address table and refers to the country table using
a foreign key.

Columns
• city_id: A surrogate primary key used to uniquely identify each city in the table.
• city: The name of the city.
• country_id: A foreign key identifying the country that the city belongs to.
• last_update: The time that the row was created or most recently updated.

5.1.5 The country Table
The country table contains a list of countries.
The country table is referred to by a foreign key in the city table.

Columns
• country_id: A surrogate primary key used to uniquely identify each country in the table.
• country: The name of the country.
• last_update: The time that the row was created or most recently updated.

5.1.6 The customer Table
The customer table contains a list of all customers.
The customer table is referred to in the payment and rental tables and refers to the address and
store tables using foreign keys.

Columns
• customer_id: A surrogate primary key used to uniquely identify each customer in the table.
• store_id: A foreign key identifying the customer's “home store.” Customers are not limited to renting
only from this store, but this is the store they generally shop at.
• first_name: The customer's first name.
• last_name: The customer's last name.
• email: The customer's email address.
• address_id: A foreign key identifying the customer's address in the address table.
• active: Indicates whether the customer is an active customer. Setting this to FALSE serves as an
alternative to deleting a customer outright. Most queries should have a WHERE active = TRUE clause.
• create_date: The date the customer was added to the system. This date is automatically set using a
trigger during an INSERT.
• last_update: The time that the row was created or most recently updated.

5.1.7 The film Table
The film table is a list of all films potentially in stock in the stores. The actual in-stock copies of each film
are represented in the inventory table.
The film table refers to the language table and is referred to by the film_category, film_actor,
and inventory tables.

Columns
• film_id: A surrogate primary key used to uniquely identify each film in the table.
• title: The title of the film.
• description: A short description or plot summary of the film.
• release_year: The year in which the movie was released.
• language_id: A foreign key pointing at the language table; identifies the language of the film.
• original_language_id: A foreign key pointing at the language table; identifies the original
language of the film. Used when a film has been dubbed into a new language.
• rental_duration: The length of the rental period, in days.
• rental_rate: The cost to rent the film for the period specified in the rental_duration column.
• length: The duration of the film, in minutes.
• replacement_cost: The amount charged to the customer if the film is not returned or is returned in a
damaged state.
• rating: The rating assigned to the film. Can be one of: G, PG, PG-13, R, or NC-17.
• special_features: Lists which common special features are included on the DVD. Can be zero or
more of: Trailers, Commentaries, Deleted Scenes, Behind the Scenes.
• last_update: The time that the row was created or most recently updated.

5.1.8 The film_actor Table
The film_actor table is used to support a many-to-many relationship between films and actors. For each
actor in a given film, there will be one row in the film_actor table listing the actor and film.
The film_actor table refers to the film and actor tables using foreign keys.

Columns:
• actor_id: A foreign key identifying the actor.
• film_id: A foreign key identifying the film.
• last_update: The time that the row was created or most recently updated.

5.1.9 The film_category Table
The film_category table is used to support a many-to-many relationship between films and categories.
For each category applied to a film, there will be one row in the film_category table listing the category
and film.
The film_category table refers to the film and category tables using foreign keys.

Columns:
• film_id: A foreign key identifying the film.
• category_id: A foreign key identifying the category.
• last_update: The time that the row was created or most recently updated.

5.1.10 The film_text Table
The film_text table contains the film_id, title and description columns of the film table, with
the contents of the table kept in synchrony with the film table by means of triggers on the film table's
INSERT, UPDATE and DELETE operations (see Section 5.5, “Triggers”).
Before MySQL server 5.6.10, the film_text table was the only table in the Sakila sample database that
used the MyISAM storage engine. This is because full-text search is used for titles and descriptions of films
listed in the film table. MyISAM was used because full-text search support with InnoDB was not available
until MySQL server 5.6.10.

Columns
• film_id: A surrogate primary key used to uniquely identify each film in the table.
• title: The title of the film.
• description: A short description or plot summary of the film.
The contents of the film_text table should never be modified directly. All changes should be made to
the film table instead.

5.1.11 The inventory Table
The inventory table contains one row for each copy of a given film in a given store.
The inventory table refers to the film and store tables using foreign keys and is referred to by the
rental table.

Columns
• inventory_id: A surrogate primary key used to uniquely identify each item in inventory.
• film_id: A foreign key pointing to the film this item represents.
• store_id: A foreign key pointing to the store stocking this item.
last_update: The time that the row was created or most recently updated.

5.1.12 The language Table
The language table is a lookup table listing the possible languages that films can have for their language
and original language values.
The language table is referred to by the film table.

Columns
• language_id: A surrogate primary key used to uniquely identify each language.
• name: The English name of the language.
• last_update: The time that the row was created or most recently updated.

5.1.13 The payment Table
The payment table records each payment made by a customer, with information such as the amount and
the rental being paid for (when applicable).
The payment table refers to the customer, rental, and staff tables.

Columns
• payment_id: A surrogate primary key used to uniquely identify each payment.
• customer_id: The customer whose balance the payment is being applied to. This is a foreign key
reference to the customer table.
• staff_id: The staff member who processed the payment. This is a foreign key reference to the staff
table.
• rental_id: The rental that the payment is being applied to. This is optional because some payments
are for outstanding fees and may not be directly related to a rental.
• amount: The amount of the payment.
• payment_date: The date the payment was processed.
• last_update: The time that the row was created or most recently updated.

5.1.14 The rental Table
The rental table contains one row for each rental of each inventory item with information about who
rented what item, when it was rented, and when it was returned.
The rental table refers to the inventory, customer, and staff tables and is referred to by the
payment table.

Columns
• rental_id: A surrogate primary key that uniquely identifies the rental.
• rental_date: The date and time that the item was rented.
• inventory_id: The item being rented.
customer_id: The customer renting the item.
• return_date: The date and time the item was returned.
• staff_id: The staff member who processed the rental.
• last_update: The time that the row was created or most recently updated.

5.1.15 The staff Table
The staff table lists all staff members, including information on email address, login information, and
picture.
The staff table refers to the store and address tables using foreign keys, and is referred to by the
rental, payment, and store tables.

Columns
• staff_id: A surrogate primary key that uniquely identifies the staff member.
• first_name: The first name of the staff member.
• last_name: The last name of the staff member.
• address_id: A foreign key to the staff member's address in the address table.
• picture: A BLOB containing a photograph of the employee.
• email: The staff member's email address.
• store_id: The staff member's “home store”. The employee can work at other stores but is generally
assigned to the store listed.
• active: Whether this is an active employee. If employees leave their rows are not deleted from this
table, instead this column is set to FALSE.
• username: The user name used by the staff member to access the rental system.
• password: The password used by the staff member to access the rental system. The password should
be stored as a hash using the SHA1() function.
• last_update: The time that the row was created or most recently updated.

5.1.16 The store Table
The store table lists all stores in the system. All inventory is assigned to specific stores, and staff and
customers are assigned a “home store”.
The store table refers to the staff and address tables using foreign keys and is referred to by the
staff, customer, and inventory tables.

Columns
• store_id: A surrogate primary key that uniquely identifies the store.
• manager_staff_id: A foreign key identifying the manager of this store.
• address_id: A foreign key identifying the address of this store.
• last_update: The time that the row was created or most recently updated.

5.1 실습 환경 맞추기¶

• Mysql Workbench (도커 환경이 아닌 분들은 다음 SQL로 sakila 데이터베이스를 만들고, 데이터를 넣어야 합니다.)
  • File -> Open SQL Script -> 03_data/sakila/sakila-schema.sql
  • File -> Open SQL Script -> 03_data/sakila/sakila-data.sql

5.2. SELECT 문 실습¶

연습문제1: 영화 테이블(film) 에서 총 영화 수 알아내기¶

• 컬럼과 실제 데이터로 테이블 유추하기

• film 테이블 구조 확인해보기

연습문제2: 등급 종류 확인해보기¶

연습문제3: 영화 테이블(film) 에서 영화 release 연도 종류 알아내기¶

연습문제4: 영화 렌탈 테이블(rental) 에서 10개 데이터만 출력하기¶

• 컬럼과 실제 데이터로 테이블 유추하기

연습문제5: 영화 렌탈 테이블(rental) 에서 inventory_id 가 367 인 로우(Row) 전체 출력하기¶

연습문제6: 영화 렌탈 테이블(rental) 에서 inventory_id 가 367 이고, staff_id가 1인 로우(Row) 전체 출력하기¶