--creta database, grant permissions to it, connect to it[the created database].

 CREATE USER yourUserName IDENTIFIED by yourPassword;


  GRANT CONNECT, RESOURCE TO yourUserName;

 CONNECT yourUserName/yourPassword;
          
SHOW USER;

--to create the cars database and populate it run the dowloded script as follows:

@C:\db2\car;

--@ is short for start. Your path may be different.

--*******************************************************************************
--Select Example

DECLARE
   v_Cust_name VARCHAR2(35);
BEGIN
   SELECT Cust_name
     INTO v_Cust_name
     FROM Customers
    WHERE Cust_no = 701;
   DBMS_OUTPUT.PUT_LINE
      ('Customer name: ' ||v_Cust_name );
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('There is no customer with student ID 701');
END;

--****************************************************************************
-- Assingment to variable

SET SERVEROUTPUT ON;
DECLARE
   v_date DATE; 
BEGIN
DBMS_OUTPUT.ENABLE;
   /* variable initialization with the help of the assignment 
      operator */ 
   v_date := SYSDATE;
  
   /*  or you can initialize with the help of the SELECT INTO    
      statement */
   SELECT SYSDATE INTO v_date
     FROM DUAL;  
DBMS_OUTPUT.PUT_LINE('Today is :'|| v_date); 
END;
/

--*********************************************************************************
--input from key board
DECLARE
   v_num1 NUMBER := &sv_num1;
   v_num2 NUMBER := &sv_num2;
   v_result NUMBER;
BEGIN
   v_result = v_num1 / v_num2;
   DBMS_OUTPUT.PUT_LINE('v_result = '||v_result);
END;


--*************************************************************************************

--IfElse
--sort two nubers


SET SERVEROUTPUT ON;
DECLARE
    v_num1 NUMBER := 5;
    v_num2 NUMBER := 3;
    v_temp NUMBER;
 BEGIN
    IF v_num1 > v_num2 THEN
       v_temp := v_num1;
       v_num1 := v_num2;
       v_num2 := v_temp;
    END IF;
    DBMS_OUTPUT.PUT_LINE('v_num1 = '||v_num1);
    DBMS_OUTPUT.PUT_LINE('v_num2 = '||v_num2);
 END;
/
--*************************************************************************************

--input variable and IfElsif

DECLARE
   v_num NUMBER := &sv_num;
BEGIN
   IF v_num < 0 THEN
      DBMS_OUTPUT.PUT_LINE (v_num||' is a negative number');
   ELSIF v_num = 0 THEN
      DBMS_OUTPUT.PUT_LINE (v_num||' is equal to zero');
   ELSE
      DBMS_OUTPUT.PUT_LINE (v_num||' is a positive number');
   END IF;
END;
/

--**********************************************************************************
--nested IfElse
DECLARE
   v_num1 NUMBER := &sv_num1;
   v_num2 NUMBER := &sv_num2;
   v_total NUMBER;
BEGIN
   IF v_num1 > v_num2 THEN
      v_total := v_num1 - v_num2;
   ELSE
      v_total := v_num1 + v_num2;
      IF v_total < 0 THEN
         v_total := v_total * (-1);
      END IF;
   END IF;
   DBMS_OUTPUT.PUT_LINE ('v_total = '|| v_total);
END;

--*********************************************************************************

--nested blocks

SET SERVEROUTPUT ON;

<< outer_block >>

DECLARE
   v_test NUMBER := 123;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Outer Block, v_test: '||v_test);
   
 << inner_block >>

   DECLARE
      v_test NUMBER := 456;   
   BEGIN   
      DBMS_OUTPUT.PUT_LINE('Inner Block, v_test: '||v_test);
      DBMS_OUTPUT.PUT_LINE('Inner Block, outer_block.v_test: '||outer_block.v_test);
   END inner_block;

END outer_block;
/

--**************************************************************************************

/*Create the following script. Check if there is a record in the CUSTOMERS table for a 
given Customer ID. If there is no record for the given Customer ID, insert a record into 
the CUSTOMERS table for the given Customer ID.
Solution:
 */


SET SERVEROUTPUT ON;
DECLARE
   v_Cust_no NUMBER  := &sv_cust_no;
   v_cust_name VARCHAR2(30) := '&sv_cust_name';
   v_zip CHAR(5) := '&sv_zip';
   
BEGIN
   SELECT Cust_name
     INTO v_Cust_name
     FROM Customers
    WHERE Cust_no = v_Cust_no;
   DBMS_OUTPUT.PUT_LINE
      ('Customer '||v_cust_name||' is a valid customer');
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN 
      DBMS_OUTPUT.PUT_LINE
         ('This customer does not exist, and will be '||
          'added to the CUSTOMERS table');
	INSERT INTO CUSTOMERS (Cust_no,Cust_name,Address,Town,County, Post_code,Contact,PAY_METHOD)
      VALUES (v_Cust_no, v_Cust_name,NULL,NULL,NULL, v_zip,NULL,NULL);
      	
     COMMIT;	
END;

--**************************************************************************************
--sequence.sql is used to generate id numbers
 
CREATE TABLE test(col1 NUMBER);

CREATE SEQUENCE test_seq 
START WITH 1 
INCREMENT BY 1;

BEGIN
   INSERT INTO test 
   VALUES (test_seq.NEXTVAL);
END;
/

SELECT * FROM test;
-*************************************************************************************
--Change to character strin with given format
--select_into.sql and use of TO_CHAR()
SET SERVEROUTPUT ON;

DECLARE
   v_average_cost VARCHAR2(10);
BEGIN
   SELECT TO_CHAR(AVG(cost), '$9,999.99')
     INTO v_average_cost
     FROM course;
   DBMS_OUTPUT.PUT_LINE('The average cost of a course is '||
      v_average_cost);
END;
/

--*****************************************************************************************
--Date and time format
DECLARE
   v_day VARCHAR2(15);
   v_time VARCHAR(8); 
BEGIN
   v_day := TO_CHAR(SYSDATE-5, 'fmDAY'); --fm cops blanks
   v_time := TO_CHAR(SYSDATE-5, 'HH24:MI');  -- MI military format
   IF v_day IN ('SATURDAY', 'SUNDAY')
   THEN
      DBMS_OUTPUT.PUT_LINE(v_day||', '||v_time); 
      IF v_time BETWEEN '12:01' AND '24:00'
      THEN
         DBMS_OUTPUT.PUT_LINE('It''s afternoon'); 
      ELSE
         DBMS_OUTPUT.PUT_LINE('It''s morning'); 
      END IF;
   END IF; 
   -- control resumes here
   DBMS_OUTPUT.PUT_LINE('This a week day, keep working!');
END;
/

--*****************************************************************************************