Write the following SQL commands in MySQL: (a) Create a table called Actor that

Question

Write the following SQL commands in MySQL:

(a)
Create a table called Actor that has attributes Id and Name. Set Id
to be the primary key. Also set the Name attribute to be not null.
Make sure you use Innodb as the database storage engine.

(b)
Insert the following rows into the table:
i. (1, `Anthony Perkins')
ii. (2, `Janet Leigh')
iii. (3, `Tom Hanks')
Display the content of the Actor table.

(c)
Create a table called Movie, which has attributes Id, Title, and Year.
Set Id to be the primary key. You can use integer to store the Year
attribute. Make sure you use Innodb as the database storage engine.

(d)
Insert the following rows into the Movie table:
i. (1, `Psycho', 1960)
ii. (2, `The Fog', 1980)
iii. (3, `Forrest Gump', 1994)
iv. (4, `The Green Mile', 1999)

(e)
Create a table called Acts, which has attributes ActorId and MovieId.
Set (ActorId, MovieId) to be the primary key. Make sure ActorId is a
foreign key to the Id attribute in Actor and MovieId is a foreign key
to the Id attribute in Movie. Make sure you use InnoDB as storage
engine and create the appropriate indices.

(f)
Insert the following rows (ActorId, MovieId) into the Acts table: (1,1),(2,1), (2,2), (3,3), (3,4).

Explanation / Answer

a) CREATE TABLE Actor ( id int,name char(20) NOT NULL,PRIMARY KEY (id) ) ENGINE=InnoDB;

b)INSERT INTO Actor(id,name) values(1, `Anthony Perkins'),(2, `Janet Leigh'),(3, `Tom Hanks');
SELECT * FROM Actor;

c)CREATE TABLE Movie(id int, title char(50), year int, PRIMARY KEY(id)) ENGINE=InnoDB;

d)INSERT INTO Movie(id,name,year) values (1, `Psycho', 1960),(2, `The Fog', 1980),(3, `Forrest Gump', 1994),(4, `The Green Mile', 1999);

e)CREATE TABLE Acts(ActorId int,MovieId int)FOREIGN KEY (ActorId) REFERENCES Actor(id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (MovieId) REFERENCES Movie(id) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

f)INSERT INTO Acts(ActorId,MovieId) values(1,1),(2,1), (2,2), (3,3), (3,4);

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.