Write a script that will do the following: 1. drop/delete all of the tables/view

Question

Write a script that will do the following:

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. Turn in both the script and the output file

Note: For 10. if you cannot change the column data type, delete the column and add a new one. Put a note in your script that you did, including the error message you got

Explanation / Answer

CREATE TABLE IF NOT EXISTS `pet` (
`id` int(2) NOT NULL,
`name` varchar(40) NOT NULL,
`dob` date NOT NULL,
`ownerid` int(2) NOT NULL,
`type` varchar(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `pet`
--

INSERT INTO `pet` (`id`, `name`, `dob`, `ownerid`, `type`) VALUES
(1, 'vb', '0000-00-00', 1, 'Dog'),
(2, 'abc', '0000-00-00', 1, 'Dog'),
(3, 'xfd', '0000-00-00', 1, 'Cat'),
(4, 'ert', '0000-00-00', 2, 'Fish'),
(5, 'sasd', '0000-00-00', 3, 'Cat');

-- --------------------------------------------------------

--
-- Table structure for table `tblowner`
--

CREATE TABLE IF NOT EXISTS `tblowner` (
`ownerid` int(3) NOT NULL,
`fname` varchar(50) NOT NULL,
`lname` varchar(50) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tblowner`
--

INSERT INTO `tblowner` (`ownerid`, `fname`, `lname`) VALUES
(1, 'Veerendra', 'Patel'),
(2, 'Lasya', 'Patel'),
(3, 'Sanvi', 'Patel'),
(4, 'Chanakya', 'Patel'),
(5, 'Vinay', 'Gowda');

-- --------------------------------------------------------

--
-- Stand-in structure for view `viewexaple`
--
CREATE TABLE IF NOT EXISTS `viewexaple` (
`fname` varchar(50)
,`lname` varchar(50)
,`name` varchar(40)
);

-- --------------------------------------------------------

--
-- Structure for view `viewexaple`
--
DROP TABLE IF EXISTS `viewexaple`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `viewexaple` AS select `tblowner`.`fname` AS `fname`,`tblowner`.`lname` AS `lname`,`pet`.`name` AS `name` from (`tblowner` join `pet` on((`tblowner`.`ownerid` = `pet`.`ownerid`))) where (`tblowner`.`ownerid` = `pet`.`ownerid`);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `pet`
--
ALTER TABLE `pet`
ADD PRIMARY KEY (`id`);

--
-- Indexes for table `tblowner`
--
ALTER TABLE `tblowner`
ADD PRIMARY KEY (`ownerid`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `pet`
--
ALTER TABLE `pet`
MODIFY `id` int(2) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=11;
--
-- AUTO_INCREMENT for table `tblowner`
--
ALTER TABLE `tblowner`
MODIFY `ownerid` int(3) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `pet`
--
ALTER TABLE `pet`
ADD CONSTRAINT `pet_ibfk_1` FOREIGN KEY (`id`) REFERENCES `tblowner` (`ownerid`);

sql.text

1. Table owner creation:
CREATE TABLE `tblowner` ( `ownerid` INT(3) NOT NULL AUTO_INCREMENT , `fname` VARCHAR(50) NOT NULL , `lname` VARCHAR(50) NOT NULL , PRIMARY KEY (`ownerid`) );

2. insert Five rows of data into tblowner table
INSERT INTO `tblowner` (`ownerid`, `fname`, `lname`) VALUES (NULL, 'Veerendra', 'Patel'), (NULL, 'Lasya', 'Patel'), (NULL, 'Sanvi', 'Patel'), (NULL, 'Chanakya', 'Patel'), (NULL, 'Vinay', 'Gowda');

3. query from tbl owner to display all records

SELECT * FROM `tblowner`

4. create pet table

CREATE TABLE `pet` ( `id` INT(3) NOT NULL AUTO_INCREMENT , `name` VARCHAR(40) NOT NULL , `dob` VARCHAR(15) NOT NULL , `ownerid` INT(3) NOT NULL , PRIMARY KEY (`id`) ) ;

5. Making Foreign key
ALTER TABLE `pet` ADD FOREIGN KEY (`id`) REFERENCES `chegg`.`tblowner`(`ownerid`) ON DELETE RESTRICT ON UPDATE RESTRICT;

6. Insert five rows of data

INSERT INTO `pet` (`id`, `name`, `dob`, `ownerid`) VALUES ('', 'vfb', 'sept 2012', '5'), ('', 'abc', 'marc 2012', '1'), ('', 'xfd', 'april 2015', '1'), ('', 'ert', 'may 2012', '2'), ('', 'sasd', 'june 2011', '3');

7. select all rows of pet table
SELECT * FROM `pet`

8. adding pet table column type

ALTER TABLE `pet` ADD `type` VARCHAR(10) NOT NULL ;

9. update pet table
UPDATE `pet` SET `type` = 'Dog' WHERE `pet`.`id` = 1

UPDATE `pet` SET `type` = 'Dog' WHERE `pet`.`id` = 2
UPDATE `pet` SET `type` = 'Cat' WHERE `pet`.`id` = 3;

UPDATE `pet` SET `type` = 'Fish' WHERE `pet`.`id` = 4;

UPDATE `pet` SET `type` = 'Cat' WHERE `pet`.`id` = 5;

10. change type of column from varchar to DATE
ALTER TABLE `pet` CHANGE `dob` `dob` DATE NOT NULL;

11. select all records from pet
SELECT * FROM `pet

12. create view
CREATE VIEW `viewexaple` as SELECT tblowner.fname, tblowner.lname, pet.name FROM tblowner INNER JOIN pet ON tblowner.ownerid = pet.ownerid WHERE tblowner.ownerid = pet.ownerid

13. select all records from view

SELECT * FROM `viewexaple`

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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