--
-- Table structure for table borrower
--
DROP TABLE IF EXISTS borrower;
CREATE TABLE borrower (
brw_num int(11) NOT NULL default '0',
brw_lname varchar(15) default NULL,
brw_fname varchar(15) default NULL,
brw_initial varchar(1) default NULL,
brw_areacode varchar(3) default NULL,
brw_phone varchar(8) default NULL,
PRIMARY KEY (brw_num)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table borrower
--
INSERT INTO borrower VALUES
(1001,'Ramas',' Alfred','A','61 5','844-2573'),
(1002,'Dunne',' Leona','K','713 ','894-1238'),
(1003,'Smith',' Kathy','W','615 ','894-2285'),
(1004,'Olowski' ,'Paul','F','61 5','894-2180'),
(1005,'Orlando' ,'Myron',NULL,' 615','222-1672'),
(1006,'Brian',' Amy','B','713', '442-3381'),
(1007,'Brown',' James','G','615 ','297-1228'),
(1008,'Williams ','George',NULL ,'615','290-2556'),
(1009,'Farriss' ,'Anne','G','71 3','382-7185'),
(1010,'Smith',' Olette','K','61 5','297-3809');
--
-- Table structure for table movie
--
DROP TABLE IF EXISTS movie;
CREATE TABLE movie (
movie_code varchar(10) NOT NULL,
movie_copies int(11) default '0',
movie_name varchar(50) default NULL,
movie_charge decimal(8,2) default '0.00',
movie_late_chg_ day decimal(8,2) default '0.00',
PRIMARY KEY (movie_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table movie
--
INSERT INTO movie VALUES
('M3456',3,'Ram blin Tulip','3.50',' 0.25'),
('R2345',2,'Onc e Upon a Midnight Breezy','4.0000 ','0.25'),
('S4567',3,'Tul ips and Threelips','3.0 0','0.25'),
('W1234',5,'Bri ght Stars and Doodle Berries','4.00' ,'0.50');
--
-- Table structure for table copy
--
DROP TABLE IF EXISTS copy;
CREATE TABLE copy (
copy_code varchar(10) NOT NULL,
copy_num int(11) default '0' ,
movie_code varchar(10) default NULL,
PRIMARY KEY (copy_code),
FOREIGN KEY (movie_code) REFERENCES movie(movie_cod e)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table copy
--
INSERT INTO copy VALUES
('M3456-1',1,'M3456'),( 'M3456-2',2,'M3456'),( 'M3456-3',3,'M3456'),
('R2345-1',1,'R2345'),( 'R2345-2',2,'R2345'),( 'S4567-1',1,'S4567'),
('S4567-2',2,'S4567'),( 'S4567-3',3,'S4567'),( 'W1234-1',1,'W1234'),
('W1234-2',2,'W1234'),( 'W1234-3',3,'W1234'),( 'W1234-4',4,'W1234'),
('W1234-5',5,'W1234');
--
-- Table structure for table rental
--
DROP TABLE IF EXISTS rental;
CREATE TABLE rental (
rent_num int(11) NOT NULL auto_increment,
brw_num int(11) default '0',
rent_charge decimal(8,2) default '0.00',
rent_date datetime default NULL,
PRIMARY KEY (rent_num),
FOREIGN KEY (brw_num) REFERENCES borrower(brw_nu m)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table rental
--
INSERT INTO rental VALUES
(1,1002,'7.50', '2004-03-15 00:00:00'),
(2,1003,'4.00', '2004-03-15 00:00:00'),
(3,1001,'4.00', '2004-03-15 00:00:00'),
(4,1004,'4.00', '2004-03-15 00:00:00'),
(5,1004,'4.00', '2004-03-16 00:00:00'),
(6,1001,'7.00', '2004-03-16 00:00:00'),
(7,1003,'4.00', '2004-03-16 00:00:00'),
(8,1002,'4.00', '2004-03-17 00:00:00'),
(9,1001,'4.00', '2004-03-17 00:00:00'),
(10,1004,'4.00' ,'2004-03-17 00:00:00'),
(11,1002,'4.00' ,'2004-03-18 00:00:00'),
(12,1003,'3.50' ,'2004-03-18 00:00:00'),
(13,1001,'4.00' ,'2004-03-18 00:00:00'),
(14,1004,'11.00 ','2004-03-18 00:00:00'),
(15,1004,'4.00' ,'2004-03-19 00:00:00');
--
-- Table structure for table rent_line
--
DROP TABLE IF EXISTS rent_line;
CREATE TABLE rent_line (
rent_num int(11) NOT NULL default '0',
rentline_num int(11) NOT NULL default '0',
copy_code varchar(10) default NULL,
rentline_charge decimal(8,2) default '0.00',
rentline_late_c hg_day decimal(8,2) default '0.00',
rentline_date_o ut datetime default NULL,
rentline_date_d ue datetime default NULL,
rentline_date_i n datetime default NULL,
rentline_days_l ate int(11) default '0',
rentline_overdu e_chg decimal(8,2) default '0.00',
PRIMARY KEY (rent_num,rentl ine_num),
FOREIGN KEY (copy_code) REFERENCES copy(copy_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table rent_line
--
INSERT INTO rent_line VALUES
(1,1,'M3456-3','3.50','0.25 ','2004-03-15 00:00:00','2004-03-17 00:00:00','2004-03-17 00:00:00',0,'0. 00'),
(1,2,'W1234-2','4.00','0.50 ','2004-03-15 00:00:00','2004-03-17 00:00:00','2004-03-17 00:00:00',0,'0. 00'),
(2,1,'M3456-2','3.50','0.25 ','2004-03-15 00:00:00','2004-03-17 00:00:00','2004-03-17 00:00:00',0,'0. 00'),
(3,1,'R2345-1','4.00','0.25 ','2004-03-15 00:00:00','2004-03-17 00:00:00',NULL, 0,'0.00'),
(4,1,'R2345-2','4.00','0.25 ','2004-03-15 00:00:00','2004-03-17 00:00:00','2004-03-17 00:00:00',0,'0. 00'),
(5,1,'W1234-1','4.00','0.50 ','2004-03-16 00:00:00','2004-03-18 00:00:00','2004-03-18 00:00:00',0,'0. 00'),
(6,1,'S4567-2','3.00','0.25 ','2004-03-16 00:00:00','2004-03-18 00:00:00',NULL, 0,'0.00'),
(6,2,'W1234-4','4.00','0.50 ','2004-03-16 00:00:00','2004-03-18 00:00:00','2004-03-18 00:00:00',0,'0. 00'),
(7,1,'W1234-5','4.00','0.50 ','2004-03-16 00:00:00','2004-03-18 00:00:00','2004-03-18 00:00:00',0,'0. 00'),
(8,1,'R2345-1','4.00','0.25 ','2004-03-17 00:00:00','2004-03-19 00:00:00','2004-03-19 00:00:00',0,'0. 00'),
(9,1,'W1234-1','4.00','0.50 ','2004-03-17 00:00:00','2004-03-19 00:00:00','2004-03-19 00:00:00',0,'0. 00'),
(10,1,'R2345-2','4.00','0.25 ','2004-03-17 00:00:00','2004-03-19 00:00:00','2004-03-19 00:00:00',0,'0. 00'),
(11,1,'W1234-2','4.00','0.50 ','2004-03-18 00:00:00','2004-03-20 00:00:00',NULL, 0,'0.00'),
(12,1,'M3456-2','3.50','0.25 ','2004-03-18 00:00:00','2004-03-20 00:00:00',NULL, 0,'0.00'),
(13,1,'W1234-3','4.00','0.50 ','2004-03-18 00:00:00','2004-03-20 00:00:00','2004-03-22 00:00:00',2,'1. 00'),
(14,1,'S4567-2','3.00','0.25 ','2004-03-18 00:00:00','2004-03-20 00:00:00','2004-03-21 00:00:00',1,'0. 25'),
(14,2,'W1234-4','4.00','0.50 ','2004-03-18 00:00:00','2004-03-20 00:00:00',NULL, 0,'0.00'),
(14,3,'R2345-2','4.00','0.25 ','2004-03-18 00:00:00','2004-03-20 00:00:00','2004-03-22 00:00:00',2,'0. 50'),
(15,1,'W1234-2','4.00','0.50 ','2004-03-19 00:00:00','2004-03-21 00:00:00','2004-03-22 00:00:00',1,'0. 50');
how can i create a query to compute and update the number of days a movie has been overdue in the rental_line table.[ Hint: use an UPDATE query to update the rentline_days_l ate column. You can use the date out and date in columns to do this, and you should not compute the overdue days for movies that have been rented out but not yert returned!]