-- 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 data into movie table
INSERT INTO movie 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 Movie No. is ' || MV_NO || '.' AS Information 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 CUST_ID = 'a03';
UPDATE invoice SET ISSUE_DATE = '24-JUL-93' WHERE CUST_ID = 'a01';
UPDATE movie SET PRICE = 250.00 WHERE TITLE = 'Gone with the Wind';
DELETE FROM invoice WHERE INV_NO = '108';
DELETE FROM invoice WHERE RETURN_DATE < '10-JUL-93';
UPDATE customer SET AREA = 'vs' WHERE CUST_ID = 'a05';
UPDATE invoice SET RETURN_DATE = '16-AUG-93' WHERE INV_NO = '108';
SELECT F_NAME FROM customer WHERE F_NAME LIKE '_a%';
SELECT L_NAME FROM customer WHERE L_NAME LIKE 'S%' OR L_NAME LIKE 'J%';
SELECT * FROM customer WHERE AREA LIKE '_a%';
SELECT * FROM customer WHERE AREA IN ('mu', 'gu');
SELECT * FROM customer WHERE PH_NO > '55500';
SELECT * FROM invoice WHERE ISSUE_DATE LIKE '%-SEP-%';
SELECT * FROM invoice WHERE CUST_ID IN ('a01', 'a02');
SELECT MV_NO, TITLE FROM movie WHERE TYPE IN ('action', 'comedy');
SELECT * FROM movie WHERE PRICE > 150 AND PRICE <= 200;
SELECT MV_NO, TITLE, PRICE*15 AS new_price FROM movie WHERE PRICE > 150;
SELECT MV_NO, TITLE, PRICE*15 AS new_price FROM movie WHERE PRICE > 150;
SELECT MV_NO, TITLE FROM movie ORDER BY TITLE ASC;
SELECT TITLE, TYPE FROM movie WHERE TYPE != 'horror';
SELECT (PRICE/(PRICE-100)) AS Result FROM movie WHERE TITLE = 'Home Alone';
SELECT F_NAME, L_NAME, CUST_ID FROM customer WHERE PH_NO IS NULL;
SELECT F_NAME FROM customer WHERE L_NAME IS NOT NULL;
SELECT MV_NO, TITLE FROM movie WHERE TITLE LIKE 'm%';
SELECT MV_NO, INV_NO FROM invoice WHERE INV_NO < '105';