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>

INSERT DATA:

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> insert into students values(6,'Test','Test2','Test3');

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 Test Test2

6 rows selected.

SQL>

DELETE DATA from table record:

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 Test Test2

6 rows selected.

SQL> delete from students where studentid = 6;

1 row deleted.

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>

SELECT (QUERY) SPECIFIC DATA FROM TABLE:

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>

SQL> select studentid, firstname, lastname from students
2 where firstname like '%ff%';

Employee Salary statistics
STUDENTID FIRSTNAME LASTNAME
---------- ------------------------------ --------------------
4 Jeff King

1 row selected.

SQL>