-- Drop the tables, in reverse order of dependency
DROP TABLE parcel;
DROP TABLE customer;
DROP TABLE courier;
-- Create the tables
CREATE TABLE courier
  (
    cID       NUMBER(6),
    courName  VARCHAR2(50) NOT NULL,
    salary    NUMBER(7,2) NOT NULL,
    courEmail VARCHAR2(30) DEFAULT 'unknown@unknown.com',
    CONSTRAINT courier_pk PRIMARY KEY (cID),
    CONSTRAINT couremail_chk CHECK (courEmail LIKE '%@%') -- check constraint for email
  ) ;
CREATE TABLE customer
  (
    cNo       NUMBER (6),
    custName  VARCHAR2(50) NOT NULL,
    cAddress  VARCHAR2(50) NOT NULL,
    custEmail VARCHAR2(30) DEFAULT 'unknown@unknown.com',
    CONSTRAINT cust_pk PRIMARY KEY (cNo),
    CONSTRAINT custemail_chk CHECK(custEmail LIKE '%@%') -- check constraint for email
  ) ;
CREATE TABLE parcel
  (
    pNo       NUMBER(6),
    custNo    NUMBER(6),
    courierID NUMBER (6),
    pdate     DATE NOT NULL,
    pvalue    NUMBER(6,2) NOT NULL,
    CONSTRAINT parcel_pk PRIMARY KEY (pNo,custNo),
    CONSTRAINT parcel_courier_fk FOREIGN KEY (courierID) REFERENCES courier(cID), -- Foreign key to courier
    CONSTRAINT parcel_customer_fk FOREIGN KEY (custNo) REFERENCES customer(CNo),  -- Foreign key to customer
    CONSTRAINT pvalue_chk CHECK (pvalue <2500.00)                                 -- check constraint for parcel value
  ) ;
-- insert the data
-- the inserts are in the order of the lines included in the spec but could be grouped by table
-- need to insert data into courier and customer before inserting into parcel due to the foreign key constraints
INSERT
INTO customer
  (
    cno,
    custname,
    caddress,
    custemail
  )
  VALUES
  (
    1,
    'Marshall Mathers',
    ' 21 Malibu Drive',
    ' mm@gmail.com'
  );
INSERT
INTO courier
  (
    cid,
    courname,
    couremail,
    salary
  )
  VALUES
  (
    1,
    'Slim Shady',
    ' ss@gmail.com',
    20000
  );
INSERT
INTO parcel
  (
    pno,
    custno,
    courierid,
    pdate,
    pvalue
  )
  VALUES
  (
    1,1,1,
    '01 Jan 2016',
    20.00
  );
INSERT
INTO customer
  (
    cno,
    custname,
    caddress
  )
  VALUES
  (
    2,
    'Mick Jagger',
    '26 Sunset Boulevard'
  ); -- This is a partial insert because no email address was given, the default value will be used
INSERT
INTO courier
  (
    cid,
    courname,
    couremail,
    salary
  )
  VALUES
  (
    2,
    'Ruby Tuesday',
    'rt@gmail.com',
    20000
  );
INSERT
INTO parcel
  (
    pno,
    custno,
    courierid,
    pdate,
    pvalue
  )
  VALUES
  (
    1,2,2,
    '12 Apr 2016',
    560.00
  );
INSERT
INTO customer
  (
    cno,
    custname,
    caddress,
    custemail
  )
  VALUES
  (
    3,
    'Ronnie Van Sant',
    '56 Cliché Avenue',
    'rvs@gmail.com'
  );
INSERT
INTO courier
  (
    cid,
    courname,
    couremail,
    salary
  )
  VALUES
  (
    3,
    'Curtis Loew',
    'cl@gmail.com',
    18000
  );
INSERT
INTO parcel
  (
    pno,
    custno,
    courierid,
    pdate,
    pvalue
  )
  VALUES
  (
    1,3,3,
    '13 Jun 2016',
    490.00
  );
INSERT
INTO customer
  (
    cno,
    custname,
    caddress,
    custemail
  )
  VALUES
  (
    4,
    'Gene Pitney',
    '77 3rd Avenue, Nashville',
    'gp@gmail.com '
  );
INSERT
INTO courier
  (
    cid,
    courname,
    salary
  )
  VALUES
  (
    4,
    'Liberty Valance' ,
    17000
  );
INSERT
INTO parcel
  (
    pno,
    custno,
    courierid,
    pdate,
    pvalue
  )
  VALUES
  (
    1,
    4,
    4,
    '14 May 2016',
    67.00
  );
-- last two inserts are for customers that already exist using couriers that already exist
INSERT
INTO parcel
  (
    pno,
    custno,
    courierid,
    pdate,
    pvalue
  )
  VALUES
  (
    2,1,2,
    '01 Jul 2016 ',
    56.00
  );--Custno=1 Marshall Mathers, CourierID=2 Ruby Tuesday
INSERT
INTO parcel
  (
    pno,
    custno,
    courierid,
    pdate,
    pvalue
  )
  VALUES
  (
    2,3,4,
    '14 Jun 2016',
    490.00
  );-- Custno=3 Ronnie Van Sant, CourierID=4
COMMIT;
/*3. Write an SQL statement to return details of all parcels with a value 50 and 500 without using < and > in the comparison.
In your output you should:
• include the name of the customer to who the parcel was delivered
• include the name of the courier who delivered it
• include the parcel value
• sort the output in order of parcel value descending.
Hint: This requires an inner join.
*/
SELECT custname,
  courname,
  pvalue
FROM parcel
JOIN customer
ON custno=cno
JOIN courier
ON courierid=cid
WHERE pvalue BETWEEN 50 AND 500
ORDER BY pvalue DESC;
/*4. Modify the previous statement to include the date of the parcel (formatted as DD/MM/YYYY)
and left pad parcel value  with * to 5 digits.
*/
SELECT custname,
  courname,
  TO_CHAR(pdate, 'dd/mm/yyyy') ,
  lpad(pvalue, 5,'*')
FROM parcel
JOIN customer
ON custno=cno
JOIN courier
ON courierid=cid
WHERE pvalue BETWEEN 50 AND 500
ORDER BY pvalue DESC;
/*5. Write an SQL statement to return the names of all customers and their email addresses. Format the output so that:
• Customer name is uppercase
• Customer email is lowercase
• Include in your output the position of the @ symbol in the email address
*/
SELECT UPPER(custname),
  LOWER(custemail),
  INSTR(custemail,'@')
FROM customer;
/*6. Modify the previous SQL so that you also include a substring of 5 letters to the right of the position of the @ symbol
– this will require nesting. */
SELECT UPPER(custname),
  LOWER(custemail),
  SUBSTR(CUSTEMAIL,instr(custemail,'@')+1,5)
FROM customer;
/*7. Write an SQL statement that outputs for each parcel the name of the customer to whom the parcel was sent and using a CASE statement a comment on the parcel value
so that if the value is < 50 the output will read ‘Cheap’, if < 500 it will read ‘Mid Range’ and anything else will be ‘Expensive’, name the output of the case
statement pvalue comment and sort the output in descending order of comment.
*/
SELECT custname,
  CASE
    WHEN pvalue < 50
    THEN 'Cheap'
    WHEN pvalue <500
    THEN 'Mid Range'
    ELSE 'Expensive'
  END PvalueComment
FROM parcel
JOIN customer
ON custno=cno
ORDER BY pvaluecomment DESC ;
/*8. Write a statement to output the details of all parcels with a value less than any parcel delivered to customer 1.
Exclude customer 1 from the output.*/
SELECT *
FROM parcel
WHERE pvalue < ANY
  (SELECT pvalue FROM parcel WHERE custno=4
  )
AND custno<>4;
/*9. Amend the statement to consider just customer 3 and output details of parcels with a value
less than all parcels for customer 3. Exclude customer 3 from the output.*/
SELECT *
FROM parcel
WHERE pvalue < ALL
  (SELECT pvalue FROM parcel WHERE custno=3
  )
AND custno<>3;