For section 1 and 3, give the syntax of each SQL statement only. The execution results are not required.  For section 2, write only SQL*Plus commands. 

||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||
			SECTION I
			Complete the queries. 
||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||

Log in your database as scott/tiger.  Write queries and execute them.  First eight questions are to write query statements and the last two are to write update statements.
  
Q1.  Write a query statement to query all the records in EMP table.
	SELECT * FROM EMP;
Q2.  Write a query statement to join DEPT and EMP tables.  Select a name from emp table and select dname from dept.
	SELECT DISTINCT D.DNAME, E.ENAME 
	FROM DEPT D, EMP E;
Q3.  Write a query statement that only return the employee records for sale department.
	SELECT DISTINCT D.DNAME, E.ENAME 
	FROM DEPT D, EMP E 
	WHERE D.DNAME = 'SALES';
Q4.  Write a query statement that group data by department.
	SELECT DISTINCT D.DNAME, COUNT(E.ENAME)
	FROM DEPT D, EMP E
	GROUP BY DNAME;
Q5.  Write a query that tell you who is Martin's manager (mgr is equal to and referencing empno).
	?????????????????????????????????????
Q6.  Write a query that returns ename order by empno.
	SELECT ENAME FROM EMP
	 ORDER BY EMPNO;
Q7.  How many employees are there in department research (write query).
	SELECT COUNT(ENAME) AS "RESEARCH NAME COUNT"
	FROM EMP, DEPT
	WHERE DEPT.DNAME = 'RESEARCH';
Q8.  How many employees are there in each department.  Display only dname and employee counts for each department.
	 SELECT DNAME, COUNT(EMPNO)
	 FROM DEPT, EMP
	 GROUP BY DNAME;
Q9.  Give every employee a 10% raise in salary(sal).  This is an update not a query. 
	UPDATE EMP
	SET SAL = SAL + SAL * 10/100;
Q10.  Give the employees  $100  commission whose manager is  BLAKE.
	??????????????????????????????????????????

||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||
			SECTION II
		Answer the following questions.
||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||

1. You used SQL*Plus to login your database as system to perform some system tasks.  Then you are asked to create a table under scott schema.  
What is the command that you should use to reconnect to your database as user scott without exiting SQL*Plus.
	connect scott/tiger@center
2. What command "get c:\cmit2610\my_file.sql" does for you in SQL*Plus?
	@c:\cmit2610\my_file.sql
3. In SQL*Plus, you would like to have 1000 rows of the page size and 120 charact
ers of line size.  What commands do you use to set them up?
	SET PAGESIZE 1000
	SET LINESIZE 120
4. How to find out current user name in SQL*Plus?
	SHOW USER
5. You run a script file with following command in SQL*Plus:
SQL>@c:\cmit2610\myfile.sql
Then command failed and the error is " Can not open the file" .  What is the possible cause of the error?
	THE FILE LOCATION IS INCORRECT.
	THE NAME OF THE FILE IS INCORRECT.

||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||
			SECTION III
	Use SQL built-in function to convert the followings.
||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||~~~~~||

1. String, 'both the UPDATE commands and the ALTER TABLE command'
* replace 'A' with 'b' in the string.
	 SELECT REPLACE ('both the UPDATE commands and the ALTER TABLE command','A','b')
  	 FROM DUAL;
* Remove 'UPDATE' and 'ALTER' from the string.
	SELECT REPLACE ('both the UPDATE commands and the ALTER TABLE command','UPDATE',' ')
  	 FROM DUAL;
	 SELECT REPLACE ('both the UPDATE commands and the ALTER TABLE command','ALTER',' ')
  	 FROM DUAL;
* Find out the position of 'a' of 'and' in the string.
	SELECT INSTR('both the UPDATE commands and the ALTER TABLE command','a') from dual;
	 SELECT INSTR('both the UPDATE commands and the ALTER TABLE command','and') from dual;
* Convert the string to upper case and lower case.
	SELECT UPPER('both the UPDATE commands and the ALTER TABLE command') FROM DUAL;
	SELECT LOWER('both the UPDATE commands and the ALTER TABLE command') FROM DUAL;
2. SYSDATE, returns both date and time. Display the output of SYSDATE as the following format:  July 20th, 2002 12:03:55.
Tip: first to do: "select sysdate from dual;" 
	SELECT TO_CHAR(SYSDATE, 'MON DD, YYY HH24:MI:SS') FROM DUAL;
3. Convert 'abcd' to $9999 (hint: replace the characters with numbers and then do formating).
	SELECT REPLACE('abcd', 'abcd', '9999') from dual;
	SELECT LPAD('9999',5,'$') FROM DUAL;