SQL> set markup html on table " "
SQL> COLUMN empno HEADING "ID"
<br>
SQL&gt; COLUMN ename HEADING "Name"
<br>
SQL&gt; COLUMN hiredate HEADING "Hire On"
<br>
SQL&gt; set termout off
<br>
SQL&gt; set feedback off
<br>
SQL&gt; spool c:\emp.html

bfdhfhkjlfs ..............

/* more text missing */

23-JAN-1982 00:00:00
</td>
</tr>
</table>
<p>
SQL&gt; spool off
<br>
SQL&gt;

SQL&gt; set markup html off
<br>
SQL>

SQL> select studentid, firstname, lastname from students;

Employee Salary statistics
STUDENTID FIRSTNAME LASTNAME
---------- ------------------------------ ----------------
1 michael Green
2 biLLy Hill
3 Jessica Harris
4 Jeff King
5 Jenny Allen

5 rows selected.

SQL>

CREATE PROGRAM TO MAKE INTERFACE TO ADD DATA TO TABLE:

SQL> edit newstu

prompt =========================================
prompt
prompt INSERT NEW STUDENT
prompt
prompt =========================================

prompt Please provide the following information:

accept stuid NUMBER prompt "ID :"
accept fname prompt "First Name:"
accept lname prompt "Last Name:"

INSERT INTO students(studentid, firstname, lastname)
VALUES ('&stuid', '&fname', '&lname');

SQL> start newstu
=========================================

INSERT NEW STUDENT

=========================================
Please provide the following information:
ID :6
First Name:joe
Last Name:eugine
old 2: VALUES ('&stuid', '&fname', '&lname')
new 2: VALUES (' 6', 'joe', 'eugine')

1 row created.

SQL> select studentid, firstname, lastname from students;

Employee Salary statistics
STUDENTID FIRSTNAME LASTNAME
---------- ------------------------------ -----------------
1 michael Green
2 biLLy Hill
3 Jessica Harris
4 Jeff King
5 Jenny Allen
6 joe eugine

6 rows selected.

SQL>

ad sequence number:

SQL>
SQL>
SQL> create sequence stuid minvalue 1 start with 1 increment by 1 cache 20;
SQL>

prompt =========================================
prompt
prompt INSERT NEW STUDENT
prompt
prompt =========================================

prompt Please provide the following information:

accept stuid NUMBER prompt "ID :"
accept fname prompt "First Name:"
accept lname prompt "Last Name:"

INSERT INTO students(studentid, firstname, lastname)
VALUES ('&stuid.nextval', '&fname', '&lname');

SQL> create table students(StudentID number(6),Firstname varchar2(30),LastName varchar2(30),Address
varchar2(50));

Table created.

SQL> start stuin /* populate data in the table students */

1 row created.


1 row created.


1 row created.


1 row created.


1 row created.

SQL> select * from students;

Employee Salary statistics
STUDENTID FIRSTNAME LASTNAME
---------- ------------------------------ ------------------------------
ADDRESS
--------------------------------------------------
1 michael GreeN


2 biLLy Hill


3 Jessica Harris


4 Jeff King


5 Jenny Allen



5 rows selected.

SQL> select studentid, firstname, lastname from students;

Employee Salary statistics
STUDENTID FIRSTNAME LASTNAME
---------- ------------------------------ ------------------------------
1 michael GreeN
2 biLLy Hill
3 Jessica Harris
4 Jeff King
5 Jenny Allen

5 rows selected.

SQL> UPDATE students SET lastname = initcap(lastname);

5 rows updated.

SQL> select studentid, firstname, lastname from students;

Employee Salary statistics
STUDENTID FIRSTNAME LASTNAME
---------- ------------------------------ ------------------------------
1 michael Green
2 biLLy Hill
3 Jessica Harris
4 Jeff King
5 Jenny Allen

5 rows selected.

SQL>

CHECK TABLE ENTRY:

SQL> select table_name from user_tables;

Employee Salary statistics
TABLE_NAME
------------------------------
BONUS
DEPT
EMP
SALGRADE
SQL>

CREATE TABLE:

SQL> select table_name from user_tables;

Employee Salary statistics
TABLE_NAME
------------------------------
BONUS
DEPT
EMP
SALGRADE
SQL>
SQL>
SQL> create table students
2 (
3 StidentID number(6),
4 Firstname varchar2(30),
5 LastNmae varchar2(30),
6 Address varchar2(50)
7 );

Table created.

SQL> select table_name from user_tables;

Employee Salary statistics
TABLE_NAME
------------------------------
BONUS
DEPT
EMP
SALGRADE
STUDENTS
SQL> describe students
Name Null? Type
----------------------------------------- -------- ----------------
STIDENTID NUMBER(6)
FIRSTNAME VARCHAR2(30)
LASTNMAE VARCHAR2(30)
ADDRESS VARCHAR2(50)

SQL>

EXAMPLE 2:

SQL> edit sql_explicit01

SELECT
e.empno,
e.ename,
d.dname,
d.loc
FROM
emp e INNER JOIN dept d
ON
e.deptno = d.deptno
ORDER BY
d.loc;

SQL> start sql_explicit01
DOC>select
DOC> e.empno as "ID",
DOC> INITCAP(e.ename) as "NAME",
DOC> INITCAP(d.dname) as "DEPT.",
DOC> INITCAP(d.loc) as "LOCATION"
DOC>from
DOC> emp e, dept d
DOC>where
DOC> e.deptno = d.deptno
DOC>order by
DOC> d.loc;
DOC>*/

Employee Salary statistics
EMPNO ENAME DNAME LOC
---------- ---------- -------------- -------------
7499 ALLEN SALES CHICAGO
7521 WARD SALES CHICAGO
7654 MARTIN SALES CHICAGO
7900 JAMES SALES CHICAGO
7844 TURNER SALES CHICAGO
7698 BLAKE SALES CHICAGO
7369 SMITH RESEARCH DALLAS
7902 FORD RESEARCH DALLAS
7876 ADAMS RESEARCH DALLAS
7566 JONES RESEARCH DALLAS
7788 SCOTT RESEARCH DALLAS
7782 CLARK ACCOUNTING NEW YORK
7839 KING ACCOUNTING NEW YORK
7934 MILLER ACCOUNTING NEW YORK
SQL>

Joint data from 2 table:

SQL> desc dept
Name Null? Type
----------------------------------------- -------- -------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

SQL> desc emp
Name Null? Type
----------------------------------------- -------- -------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> select * from dept;

Employee Salary statistics
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> select ename, sal from emp;

Employee Salary statistics
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
SQL>

SQL> select ename, sal from emp
2 where sal = (select MIN(sal) from emp);

Employee Salary statistics
ENAME SAL
---------- ----------
SMITH 800
SQL>

SQL> select avg(sal)
2 from emp
3 group by sal;

AVG(SAL)
----------
800
950
1100
1250
1300
1500
1600
2450
2850
2975
3000
5000
SQL> select count(sal)from emp;

COUNT(SAL)
----------
14
SQL> select avg(sal) from emp;

AVG(SAL)
----------
2073.21429
SQL>

SQL> save salstats
Created file salstats.sql
SQL> edit salstats

select count(sal) from emp;
select avg(sal) from emp;
select min(sal) from emp;
select max(sal) from emp;

SQL> start salstats

COUNT(SAL)
----------
14

AVG(SAL)
----------
2073.21429

MIN(SAL)
----------
800

MAX(SAL)
----------
5000
SQL>

EXAMPLE2:

SQL> edit salstats2

Using sysdate, systimestamp to get the current date and time

SQL> select SYSDATE, SYSTIMESTAMP from dual;

SYSDATE
---------
SYSTIMESTAMP
--------------------------------------------------
20-DEC-06
20-DEC-06 10.00.21.921000 AM +08:00

SQL>

SQL> select sysdate, ADD_MONTHS(SYSDATE, 3) as plus_3 from dual;

SYSDATE PLUS_3
--------- ---------
20-DEC-06 20-MAR-07

SQL> select sysdate, ADD_MONTHS(SYSDATE, 3) as plus_3Month, ADD_MONTHS(SYSDATE, -2) as Minus2_month
from dual;

SYSDATE PLUS_3MON MINUS2_MO
--------- --------- ---------
20-DEC-06 20-MAR-07 20-OCT-06
SQL>

SQL> select sysdate
2 ,extract(year from sysdate) as year
3 ,extract(month from systimestamp) as month
4 ,extract(timezone_hour from timestamp) as tzh
5 from dual
6
SQL> save extractsysdate
Created file extractsysdate.sql
SQL> edit extractsysdate

select sysdate
,extract(year from sysdate) as year
,extract(month from systimestamp) as month
,extract(timezone_hour from systimestamp) as tzh
from dual
/

SQL> select empno, ename
2 from emp
3 where ename LIKE '%ll%';
SQL> save like01
Created file like01.sql
SQL> edit like01

select empno, ename
from emp
where ename LIKE '%LL%'

/

SQL> start like01

EMPNO ENAME
---------- ----------
7499 ALLEN
7934 MILLER

SQL>

SQL> edit like02

select empno, ename
from emp
where upper(ename) LIKE '%LL%'
/


SQL> start like02

EMPNO ENAME
---------- ----------
7499 ALLEN
7934 MILLER