Relationship between tables?

I have seen开发者_开发百科 ER diagrams and conceptual schema of many databases, but I am still not very clear how do you create table out of it and query them?

For example, below is a schema database and how do create tables out of it and query it?

Say I need perform a query to find all films that include the keyword “America” within the genre “Action” (genre ID = 126). any ideas?

Most tools that can create an ER diagram can also generate the SQL statements necessary to implement the database. Otherwise, you have to do it manually, by reading the diagram and translating it yourself into SQL.

So, looking at the ER diagram, you might write

CREATE TABLE Film (
  Id INTEGER PRIMARY KEY,
  Title VARCHAR(35) NOT NULL,
  Description VARCHAR(256) NOT NULL,
  Year INTEGER NOT NULL CHECK (Year > 1900),
  Rating INTEGER NOT NULL DEFAULT 3 CHECK (Rating BETWEEN 1 AND 5)
);

This image doesn't show data types (like VARCHAR(35)) and constraints (like Year > 1900), so I just guessed.

Now you just have to insert data into the tables, and you're good to go.

select Film.*
  from Film
 inner join FilmGenre on FilmGenre.FilmId = Film.Id
 inner join Genre on Genre.Id = FilmGenre.GenreId
 where Film.Title like '%America%'
   and Genre.Name = 'Action'