compute and update query

[islandgal]

--
-- 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','615','844-2573'),
(1002,'Dunne','Leona','K','713','894-1238'),
(1003,'Smith','Kathy','W','615','894-2285'),
(1004,'Olowski','Paul','F','615','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','713','382-7185'),
(1010,'Smith','Olette','K','615','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,'Ramblin Tulip','3.50','0.25'),
('R2345',2,'Once Upon a Midnight Breezy','4.0000','0.25'),
('S4567',3,'Tulips and Threelips','3.00','0.25'),
('W1234',5,'Bright 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_code)
) 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_num)
) 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_chg_day decimal(8,2) default '0.00',
rentline_date_out datetime default NULL,
rentline_date_due datetime default NULL,
rentline_date_in datetime default NULL,
rentline_days_late int(11) default '0',
rentline_overdue_chg decimal(8,2) default '0.00',
PRIMARY KEY (rent_num,rentline_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_late 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!]

[tlhintoq]

Bytes has a policy regarding assisting students with their homework.

The short version is that the volunteers here can't help you with schoolwork.
A) We don't know what material you have and have not learned in class.
B) We don't know the guidelines you must follow.
C) In the long run giving you the answers actually short changes your education.

Hint 1: Try hitting Google with terms of your programming language and primary terms of what you want to do. For example "C# custom events" or "VB datagrid Excel". I've found this to be a very effective tool.
Hint 2: Your text book
Hint 3: Your instructor
Hint 4: Posting guidelines regarding homework assignments.

TIP: Read through the MSDN Library about each new control/method/area you are learning.