Using Mysql write a script that will do the following: Also show the output sepa
ID: 3699195 • Letter: U
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
Please help
Explanation / Answer
ANSWER:
THE ABOVE GIVEN MYSQL SCRPIT USING THE OUTPUT SHOWN BELOW:
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;
11.select * from pet;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.