Question

Create three MySQL database tables and write SQL scripts to read, insert, and delete data. The first database table will contain the names of at least four movies. The second table will be a list of actors who appear in the movies. The third table will be an associative table that describes the relationship between the actors and their movies (which actors appear in which movies). Actors and movies have a “many-to-many relationship,” meaning an actor can be in multiple movies and a movie can have multiple actors.

• Use the MySQL Database Instructions to create the three tables.
• Once the three tables are completed, create a “join” statement that joins data from all three tables. Remember that field names used in more than one table (ASIN, ActorID) must be fully qualified (i.e., dvdtitle, ASIN).
• The previous SQL query returns a separate record for each actor. Provide a way to group results from several records into a single row.

General guidelines for this interactive assignment:

• String values in SQL statements are always enclosed within single quotes. ASIN and Title are both strings and require single quotes. For instance, your insert statement will look something like this:
  $sql = “Insert into dvdtitles (asin, title, price) VALUES (‘$asin’, ‘$title’, $price)”;
• SQL debugging tip: check your SQL statement before you execute it. Put the following line of code immediately before the mysql_query($strSQL) statement.
• When retrieving ASIN from the querystring, remember that ASIN is a string, not a number.

• Explain in detail the process on how to group results from several records into a single row.

Answer 1

First create Database

syntax: create database database name;

create database movies_data;

then use database

use movies_data;

now create actor table

syntax : create table table_name (

col1 datatype,

col2 datatype,

col3 datatype,

...

);

  

create table actors(

ActorsID int auto_increment,

ActorsName varchar(100),

primary key(ActorsID)

);

actors table

create movie table   

syntax : create table table_name (

col1 datatype,

col2 datatype,

col3 datatype,

...

);

  create table movies(

MoviesID int auto_increment,

MovieName varchar(100),

primary key(MoviesID)

);

movies table

create role table   

  syntax : create table table_name (

col1 datatype,

col2 datatype,

col3 datatype,

...

);

create table role (

MoviesID int,

ActorsID int,

rolename varchar(100)

);

add foreign key constraints in role table

alter table role add foreign key(MoviesID) references movies(MoviesID);

alter table role add foreign key(ActorsID) references actors(ActorsID);

role table

insert data in tables string values in SQL statements are always enclosed within single quotes

actors table

movies table

role table

create inner-join statement that joins data from all three tables

select * from actors a inner join role r on a.ActorsID = r.ActorsID inner join movies m on m.MoviesID = r.MoviesID;

create left-join statement that joins data from all three tables

select * from actors a left join role r on a.ActorsID = r.ActorsID left join movies m on m.MoviesID = r.MoviesID;

create right-join statement that joins data from all three tables

select * from actors a right join role r on a.ActorsID = r.ActorsID right join movies m on m.MoviesID = r.MoviesID;

==Please Upvote==