Notes

SQL*Plus: Release 11.2.0.2.0 Production on Sat Jan 18 09:29:42 2020

Copyright (c) 1982, 2014, Oracle. All rights reserved.

SQL> connect
Enter user-name: system/system
ERROR:
ORA-28002: the password will expire within 7 days


Connected.
SQL> create user VENOM identified by eleven;
create user VENOM identified by eleven
*
ERROR at line 1:
ORA-01920: user name 'VENOM' conflicts with another user or role name


SQL> create user Venom identified by eleven;
create user Venom identified by eleven
*
ERROR at line 1:
ORA-01920: user name 'VENOM' conflicts with another user or role name


SQL> create user eleven identified by venom;

User created.

SQL> grant all privilages to eleven;
grant all privilages to eleven
*
ERROR at line 1:
ORA-00990: missing or invalid privilege


SQL> grant all privileges to eleven;

Grant succeeded.

SQL> connect eleven;
Enter password:
Connected.
SQL> Show database
SP2-0158: unknown SHOW option "database"
SQL> show user
USER is "ELEVEN"
SQL> spool C:UserslabDesktopel.txt
SQL> spool C:\UserslabDesktopel.txt
SQL> spool D:el.txt
SQL> spool D:\el.txt
SQL> spool D:\El.txt
SQL> spool D:\a.txt
SQL> spool D:\El.txt
SQL> spool on
SQL> Select 'Hellon world.' from dual;

'HELLONWORLD.
-------------
Hellon world.

SQL> Select 20+20 from dual;

20+20
----------
40

SQL> Select 20*20*30 from dual;

20*20*30
----------
12000

SQL> Select 10^2 from dual;
Select 10^2 from dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL> desc dual;
Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)

SQL> Select sysdate from dual;

SYSDATE
---------
18-JAN-20

SQL> Create table St1
2 {
3 Name Varchar2(10),
4 Roll Number(10),
5 marks Number(3),
6 CGPA Number(6,2),
7 }
8 ;
{
*
ERROR at line 2:
ORA-00911: invalid character


SQL> Create table St1
2 (
3 Name Varchar2(10),
4 Roll Number(10),
5 marks Number(3),
6 CGPA Number(6,2),
7 );
)
*
ERROR at line 7:
ORA-00904: : invalid identifier


SQL> Create table st1
2 Name Varchar2(10),
3 Roll Number(10),
4 marks Number(3),
5 CGPA Number(6,2),
6 );
Name Varchar2(10),
*
ERROR at line 2:
ORA-00922: missing or invalid option


SQL> Create table st1
2 (
3 Name Varchar2(10),
4 Roll Number(10),
5 marks Number(3),
6 CGPA Number(6,2),
7 );
)
*
ERROR at line 7:
ORA-00904: : invalid identifier


SQL> Create table st1
2 (
3 Name Varchar2(10),
4 Roll Number(10),
5 marks Number(3),
6 CGPA Number(6,2)
7 );

Table created.

SQL> spool off
SQL> spool d://el.txt
SQL> spool on
SQL> Create table st1
2 (
3 Name Varchar2(10),
4 Roll Number(10),
5 marks Number(3),
6 CGPA Number(6,2)
7 );
Create table st1
*
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> Create table st1
2 2 (
3 3 Name Varchar2(10),
4 4 Roll Number(10),
5 5 marks Number(3),
6 6 CGPA Number(6,2)
7
SQL> ed
Wrote file afiedt.buf

1 Create table st1
2 2 (
3 3 Name Varchar2(10),
4 4 Roll Number(10),
5 5 marks Number(3),
6* 6 CGPA Number(6,2)
SQL> Select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ST1 TABLE

SQL> desc st1;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
ROLL NUMBER(10)
MARKS NUMBER(3)
CGPA NUMBER(6,2)

SQL> Insert into st1 values('Shiv',26,77,8);

1 row created.

SQL> desc st1;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
ROLL NUMBER(10)
MARKS NUMBER(3)
CGPA NUMBER(6,2)

SQL> Select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ST1 TABLE

SQL> Select * from st1;

NAME ROLL MARKS CGPA
---------- ---------- ---------- ----------
Shiv 26 77 8

SQL> Insert into st1 values('Shiv',26,77,8.01);

1 row created.

SQL> Select * from st1;

NAME ROLL MARKS CGPA
---------- ---------- ---------- ----------
Shiv 26 77 8
Shiv 26 77 8.01

SQL> Insert into st1 values('will',27,74,8.31);

1 row created.

SQL> Insert into st1 values('lucas',29,71,8.91);

1 row created.

SQL> Insert into st1 values('mike',30,61,7.91);

1 row created.

SQL> Insert into st1 values('dustin',39,91,9.21);

1 row created.

SQL> Insert into st1 values('max',30,81,8.21);

1 row created.

SQL> Insert into st1 values('suzie',40,91,8.91);

1 row created.

SQL> Insert into st1 values('steve',20,71,8.11);

1 row created.

SQL> Insert into st1 values('nancy',21,69,7.86);

1 row created.

SQL> Insert into st1 values('billy',42,42,4.20);

1 row created.

SQL> Select * from st1;

NAME ROLL MARKS CGPA
---------- ---------- ---------- ----------
Shiv 26 77 8
Shiv 26 77 8.01
will 27 74 8.31
lucas 29 71 8.91
mike 30 61 7.91
dustin 39 91 9.21
max 30 81 8.21
suzie 40 91 8.91
steve 20 71 8.11
nancy 21 69 7.86
billy 42 42 4.2

11 rows selected.

SQL> Select count(*) from st1;

COUNT(*)
----------
11

SQL> Select name from st1;

NAME
----------
Shiv
Shiv
will
lucas
mike
dustin
max
suzie
steve
nancy
billy

11 rows selected.

SQL> Select name "FULL NAME" from st1;

FULL NAME
----------
Shiv
Shiv
will
lucas
mike
dustin
max
suzie
steve
nancy
billy

11 rows selected.

SQL> Select * from st1 whose name = 'shiv';
Select * from st1 whose name = 'shiv'
*
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> Select * from st1
2 whose name = 'Shiv';
whose name = 'Shiv'
*
ERROR at line 2:
ORA-00933: SQL command not properly ended


SQL> Select * from st1
2 where name = 'Shiv';

NAME ROLL MARKS CGPA
---------- ---------- ---------- ----------
Shiv 26 77 8
Shiv 26 77 8.01

SQL> Select * from st1
2 where roll between 30 and 40;

NAME ROLL MARKS CGPA
---------- ---------- ---------- ----------
mike 30 61 7.91
dustin 39 91 9.21
max 30 81 8.21
suzie 40 91 8.91

SQL> Select roll,marks from st1 where cgpa is between 8.5 and 10;
Select roll,marks from st1 where cgpa is between 8.5 and 10
*
ERROR at line 1:
ORA-00908: missing NULL keyword


SQL> Select roll,marks from st1 where cgpa between 8.5 and 10;

ROLL MARKS
---------- ----------
29 71
39 91
40 91

SQL> max(cgpa from st1;)
SP2-0734: unknown command beginning "max(cgpa f..." - rest of line ignored.
SQL> max(cgpa) from st1;
SP2-0734: unknown command beginning "max(cgpa) ..." - rest of line ignored.
SQL> Select max(cgpa) from st1;

MAX(CGPA)
----------
9.21

SQL> Select max(name) from st1;

MAX(NAME)
----------
will

SQL> Select name from st1 where max(cgpa);
Select name from st1 where max(cgpa)
*
ERROR at line 1:
ORA-00934: group function is not allowed here


SQL> Select name from st1 where cgpa=max(cgpa);
Select name from st1 where cgpa=max(cgpa)
*
ERROR at line 1:
ORA-00934: group function is not allowed here


SQL> Select name from st1 where cgpa = max(cgpa);
Select name from st1 where cgpa = max(cgpa)
*
ERROR at line 1:
ORA-00934: group function is not allowed here


SQL> Select name from st1 where cgpa = max(cgpa) from st1;
Select name from st1 where cgpa = max(cgpa) from st1
*
ERROR at line 1:
ORA-00934: group function is not allowed here


SQL> ed
Wrote file afiedt.buf

1* Select name from st1 where cgpa = max(cgpa) from st1
SQL> spool off
SQL> spool on
SQL> Select name from st1 where cgpa = (Select max(cgpa) from st1);

NAME
----------
dustin

SQL>