#!/bin/bash

DBUSER='chyuen'
DBPASSWORD='12242466'
DB='oracle.scs.ryerson.ca'

num = 0
table_name = ""
data = ""
echo "Enter 1. to create SQL tables"
echo "Enter 2. to drop SQL tables"
echo "Enter 3. to add data into a SQL table"
echo "Enter 4. to view SQL tables"
echo "Enter 5. exit"
read num


while [ $num -ne 5 ]
do
	if [ $num -eq 1 ]
	then
		echo "You entered $num"
		sqlplus64 "chyuen/12242466@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=oracle.scs.ryerson.ca)(Port=1521))(CONNECT_DATA=(SID=orcl)))" << EOF
        	CREATE TABLE julian(
			Name VARCHAR2(25));
		exit;
EOF
	fi
	if [ $num -eq 2 ]
	then
		echo "Your entered $num"
        	sqlplus64 "chyuen/12242466@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=oracle.scs.ryerson.ca)(Port=1521))(CONNECT_DATA=(SID=orcl)))" << EOF2
		DROP TABLE julian;
		exit;
EOF2
	fi
	if [ $num -eq 3 ]
	then
		echo "Insert the table you want to add data to"
		read table_name
		echo "What do you want to add in $table_name?"
		read data
                sqlplus64 "chyuen/12242466@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=oracle.scs.ryerson.ca)(Port=1521))(CONNECT_DATA=(SID=orcl)))" << EOF
		INSERT INTO $table_name Values ('$data');
		exit;
EOF

	fi
	echo "Enter 1. to create SQL tables"
	echo "Enter 2. to drop SQL tables"
	echo "Enter 3. to add data into a SQL table"
	echo "Enter 4. to view SQL tables"
	echo "Enter 5. exit"
	read num

	if [ $num -eq 4 ]
	then
		echo "Which table do you want to view?"
		read table_name
                sqlplus64 "chyuen/12242466@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=oracle.scs.ryerson.ca)(Port=1521))(CONNECT_DATA=(SID=orcl)))" << EOF
		SELECT * FROM $table_name;
		exit;
EOF
	fi
done