a2ans - Model Answers to Assignment 2 Each exercise is...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
Model Answers to Assignment 2 Each exercise is worth 10 points. In your personal database, write and execute MySQL commands for the following actions. 1. Create a table called 'actor' with the following fields: guild_id, an integer last_name, a variable-length string of 20 characters first_name, a variable-length string of 20 characters nickname, a fixed-length string of 10 characters age, an integer years_acting, an integer Make guild_id the primary key and check that the age of an actor be greater than years_acting plus 13. (In other words, no child actors allowed, even after they grow up.) Also, insert the following data: 8904 Superstar Bigname Bigsy 36 10 3256 Twinkletoes Rosemary Rose 21 3 6984 Comedienne Ima NULL 46 27 Use a simple SELECT query to display the table. Oops, Ima lied about her age! Update her data so that her age is 52 and display the table again. SOLUTION Here are the MySQL commands, applied to my database. USE ches /* exercise 1 */ CREATE TABLE actor (guild_id INT, last_name VARCHAR(20), first_name VARCHAR(20), nickname CHAR(10), age INT, years_acting INT, PRIMARY KEY (guild_id), CHECK (age > years_acting + 13)); INSERT INTO actor VALUES (8904, 'Superstar', 'Bigname', 'Bigsy', 36, 10); INSERT INTO actor VALUES (3256, 'Twinkletoes', 'Rosemary', 'Rose', 21, 3); INSERT INTO actor VALUES (6984, 'Comedienne', 'Ima', NULL, 46, 27); SELECT * FROM actor;
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
UPDATE actor SET age = 52 WHERE guild_id = 6984; SELECT * FROM actor; Here is the output produced by these commands. Database changed Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) +----------+-------------+------------+----------+------+--------------+ | guild_id | last_name | first_name | nickname | age | years_acting | +----------+-------------+------------+----------+------+--------------+ | 8904 | Superstar | Bigname | Bigsy | 36 | 10 | | 3256 | Twinkletoes | Rosemary | Rose | 21 | 3 | | 6984 | Comedienne | Ima | NULL | 46 | 27 | +----------+-------------+------------+----------+------+--------------+ 3 rows in set (0.00 sec) Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 +----------+-------------+------------+----------+------+--------------+ | guild_id | last_name | first_name | nickname | age | years_acting | +----------+-------------+------------+----------+------+--------------+ | 8904 | Superstar | Bigname | Bigsy
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 11/03/2009 for the course CISC 437 taught by Professor Schwartz,l during the Fall '08 term at University of Delaware.

Page1 / 7

a2ans - Model Answers to Assignment 2 Each exercise is...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online