Using Mysql write a script that will do the following: Also show the output sepa

Question

Using Mysql write a script that will do the following: Also show the output separately.

1. drop/delete all of the tables/views you will be creating below

2. create a table owner with an owner id, a first name, and a last name. Owner id should be an auto-increment primary key.

3. put at least 5 records in this table

4. do a select * on this table to show all the records

5. create a table called pet with a pet id (auto-increment primary key), pet name, pet dob (as character), and ownerid

(which is a foreign key into the owner table)

6. put at least 5 records in this table, with at least two pets owned by the same owner

7. do a select * on this table to show all the records

8. add a column to the pet table for type of pet (for example: dog, cat, or fish)

9. update several rows to add the pet type

10. change the pet dob column to a date type (from

character)

11. do a select * on this table to show all the records

12. define a view that will list each owner with their pet, just the names (first and last of owner) and the pet name

13. do a select * on this view to show all the records

Once you have debugged and corrected the above sql statements, put them in the script (with comments), run the script using the T command to put the results in a text file

Explanation / Answer

2.CREATE TABLE owner (
ownerID Integer PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255)
  
);

3.INSERT INTO owner (FirstName,LastName)
VALUES ('Lars','Monsen');

//execute statement then again execute with different values

INSERT INTO owner (FirstName,LastName)
VALUES ('ram','sita');

//execute statement then again execute with different values

INSERT INTO owner (FirstName,LastName)
VALUES ('ra','ray');

//execute statement then again execute with different values

INSERT INTO owner (FirstName,LastName)
VALUES ('rum','polo');

//execute statement then again execute with different values

INSERT INTO owner (FirstName,LastName)
VALUES ('beena','heena');

//execute statement then again execute with different values

INSERT INTO owner (FirstName,LastName)
VALUES ('uy','lolo');

4.select * from owner;

//this will show all 5 records inserted

5.CREATE TABLE pet (
petID Integer PRIMARY KEY AUTOINCREMENT,
petName varchar(255) ,
petDOB char(255),
ownerID Integer,
FOREIGN KEY (ownerID) REFERENCES owner(ownerID)
);

6.

INSERT INTO pet (petName , petDOB , ownerID )
VALUES ('tommy', 'oct', '1');

//execute statement then again execute with different values
INSERT INTO pet (petName , petDOB , ownerID )
VALUES ('tutu', 'jan', '2');

//execute statement then again execute with different values
INSERT INTO pet (petName , petDOB , ownerID )
VALUES ('piku', 'sept', '1');

//execute statement then again execute with different values
INSERT INTO pet (petName , petDOB , ownerID )
VALUES ('tiger', 'april', '3');

//execute statement then again execute with different values
INSERT INTO pet (petName , petDOB , ownerID )
VALUES ('pigi', 'jan', '4');

7.select * from pet;

8.ALTER TABLE pet
ADD pet_type char;

9.

UPDATE pet
SET pet_type = dog,
WHERE petID=1;

//execute statement then again execute with different values

UPDATE pet
SET pet_type = cat,
WHERE petID=2;

//execute statement then again execute with different values

UPDATE pet
SET pet_type = mouse,
WHERE petID=3;

//execute statement then again execute with different values

UPDATE pet
SET pet_type = dog,
WHERE petID=4;

//execute statement then again execute with different values

UPDATE pet
SET pet_type = mouse,
WHERE petID=5;

10.

PLEASE REPOST

11.select * from pet;

12. & 13 please repost so that others can answer

i hope above code will be helpful for you . its 100 % correct . i hope you like it .

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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