Question

1. Using the alter table command, add a new column named gender making sure that only values of M, F or O are allowed.

2. Modify the data in the customers table to change the state for customers with ID number 2, 4, and 5 to NV.

3. Create a sequence that starts with -100 and increments by 10. Show the first value of the sequence.

4. Create a table named MYDATA with the following columns: id (primary key) and name. Use your sequence created in question 28 to insert 2 rows for the table. Make up your own data for name.

5. Create an index on the CANDIDATE table on the upper-case values of the last name column.

Answer 1

#############################################################################

Answer to Question 1

Two step answer to this is

Step 1 : Create a sample table first, In this case we are creating a table named "alterexample" with id,firstname,lastname and birthday as keys

Step 2 : Alter the table "alterexample" to add the gender column, allowing valid values 'M','F' & 'O'

Code Snippet for Step 1 in Question 1

#*********MYSQL CODE SNIPPET STARTS HERE *************

CREATE TABLE alterexample
( id INT(11) NOT NULL AUTO_INCREMENT,
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(25),
birthday DATE,
CONSTRAINT id_pk PRIMARY KEY (id)
);

#*********MYSQL CODE SNIPPET ENDS HERE *************

Code Snippet for Step 2 in Question 1

#*********MYSQL CODE SNIPPET STARTS HERE *************

ALTER TABLE alterexample
ADD COLUMN gender enum('M','F','O') AFTER first_name;

#*********MYSQL CODE SNIPPET ENDS HERE *************

##############################################################################

Answer to Question 2

#The assumptions made are table name is "customer" the column name is "state"

#*********MYSQL CODE SNIPPET ENDS HERE *************

update customer
SET state = 'NV'
WHERE customer_id IN (2,4,5)

#*********MYSQL CODE SNIPPET ENDS HERE *************

##############################################################################

##############################################################################

Answer to Question 3

#Creating a sequence for the customer table created in the question 2

#*********MYSQL CODE SNIPPET BEGINS HERE *************

SELECT @mysequence:=-110;
SELECT *, @mysequence:=@mysequence+10 AS mysequence FROM customer ORDER BY state;

#*********MYSQL CODE SNIPPET ENDS HERE *************

##################################################################

Answer to Question 4

Step1 : Create the table

Step 2 : Use the sequence to load the table

Step 1 : Create the MYDATA table

#*********MYSQL CODE SNIPPET ENDS HERE *************
CREATE TABLE MYDATA
( id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
CONSTRAINT id_pk PRIMARY KEY (id)
);

#*********MYSQL CODE SNIPPET ENDS HERE *************

#*********MYSQL CODE SNIPPET STARTS HERE *************

INSERT INTO MYDATA
SELECT @mysequence:=@mysequence+10 AS id, last_name AS name FROM customer where state = 'AZ' ORDER BY state

#*********MYSQL CODE SNIPPET ENDS HERE *************

##################################################################

Answer to Question 5

#*********MYSQL CODE SNIPPET STARTS HERE *************
CREATE TABLE candidate
( candidate_id INT(11) NOT NULL AUTO_INCREMENT,
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(25),
birthday DATE,
CONSTRAINT candidate_pk PRIMARY KEY (candidate_id)
);

create index upper_col1 on candidate ( (upper(last_name)) )

#*********MYSQL CODE SNIPPET ENDS HERE *************

##################################################################