-- Create customer table
CREATE TABLE customer (
  cust_id varchar(10) PRIMARY KEY,
  f_name varchar(20),
  l_name varchar(20),
  area varchar(10),
  ph_no varchar(10)
);

-- Insert data into customer table
INSERT INTO customer (cust_id, f_name, l_name, area, ph_no)
VALUES
  ('a01', 'Ivan', 'Bayross', 'sa', '6125467'),
  ('a02', 'Vandana', 'Ariel', 'mu', '5560379'),
  ('a03', 'Prarnada', 'Jauguste', 'da', '4560389'),
  ('a04', 'Basu', 'Navindgi', 'ba', '61125401'),
  ('a05', 'Ravi', 'Sreedharm', 'na', NULL),
  ('a06', 'Rukmini', NULL, 'gh', '5125274');

-- create movie table
CREATE TABLE movie (
  mv_no INT PRIMARY KEY,
  title VARCHAR(50),
  type VARCHAR(20),
  star VARCHAR(30),
  price DECIMAL(6,2)
);

INSERT INTO movie (mv_no, title, type, star, price)
VALUES
    (1, 'Bloody Action', 'Action', 'Jacki Chang', 181),
    (2, 'The Firm', 'Thriller', 'Tom Cruise', 200),
    (3, 'Pretty Woman', 'Romance', 'Richard Gere', 151),
    (4, 'Home Alone', 'Comedy', 'Macaulay Culkin', 150),
    (5, 'The Fugitive', 'Thriller', 'Marrison Ford', 200),
    (6, 'Coma', 'Suspense', 'Michal Doglous', 100),
    (7, 'Dracula', 'Horror', 'Gary Oldman', 150),
    (8, 'Quick Change', 'Comedy', 'Bill Mauray', 100),
    (9, 'Gone with the Wind', 'Action', 'Clark Gable', 200),
    (10, 'Carry on Doctor', 'Comedy', 'Leslie Philips', 100);
    

CREATE TABLE invoice (
    inv_no INT PRIMARY KEY,
    mv_no INT,
    cust_id VARCHAR(3),
    issue_date DATE,
    return_date DATE
);

INSERT INTO invoice (inv_no, mv_no, cust_id, issue_date, return_date) 
VALUES
    (101, 4, 'a01', '1993-07-23', '1993-06-25'),
    (102, 3, 'a02', '1993-08-12', '1993-08-15'),
    (103, 1, 'a02', '1993-08-15', '1993-08-18'),
    (104, 6, 'a03', '1993-09-10', '1993-09-13'),
    (105, 7, 'a04', '1993-08-05', '1993-08-08'),
    (106, 2, 'a06', '1993-09-18', '1993-09-21'),
    (107, 9, 'a05', '1993-07-07', '1993-07-10'),
    (108, 9, 'a01', '1993-08-11', '1993-08-14'),
    (109, 5, 'a03', '1993-07-06', '1993-07-09'),
    (110, 8, 'a06', '1993-09-03', '1993-09-06');
    
    
SELECT * FROM invoice;
SELECT f_name, l_name FROM customer;
SELECT f_name, area FROM customer;
SELECT type FROM movie;
SELECT 'The Invoice No. of Customer Id.' ||cust_id || 'is' ||inv_no|| 'and MOV_NO is' ||mv_no||'.' AS INFORAMTION FROM Invoice;
SELECT CUST_ID || ' has taken Movie No ' || MV_NO || ' on ' || ISSUE_DATE || ' and will return on ' || RETURN_DATE || '.' AS Information FROM invoice;
UPDATE Customer SET ph_no = '466389' WHERE f_name = 'Prarnada';
UPDATE invoice SET issue_date = '1993-07-24' WHERE cust_id = 'a01';
UPDATE Movie SET price = '250' WHERE title = 'Gone with the Wind';
DELETE FROM invoice WHERE inv_no = 108;
DELETE FROM INVOICE WHERE RETURN_DATE < '1993-07-10';
UPDATE Customer SET area = 'vs' WHERE cust_id = 'a05';
update invoice set RETURN_DATE = 1993-08-16 WHERE inv_no = 108;
SELECT f_name FROM Customer WHERE f_name like '_a%';
SELECT l_name from Customer WHERE l_name like 'a%' or l_name like 'j%';
SELECT * from Customer WHERE area like '_a%';
SELECT * from Customer WHERE area in ('gu', 'mu');
SELECT * from Movie WHERE type in ('Action', 'Comedy');
SELECT * from Customer WHERE ph_no > 55500;
SELECT * from invoice WHERE issue_date like '%-09-%';
SELECT * from Customer WHERE cust_id IN ('a01', 'a02');
SELECT * from Movie WHERE price > 150  and price <= 200