Notes

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 2 13:36:50 2022

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

SQL> connect
Enter user-name: system
Enter password:
Connected.
SQL> create user scott identified by tiger;

User created.

SQL> grant connect,resources to scott;
grant connect,resources to scott
*
ERROR at line 1:
ORA-01919: role 'RESOURCES' does not exist


SQL> grant connect, resource to scott;

Grant succeeded.

SQL> grant create table to scott;

Grant succeeded.

SQL> connect
Enter user-name: scott
Enter password:
Connected.
SQL> select * from tab;

no rows selected

SQL> create table employee(emp_no number(3),e_name varchar2(20),e_address varchar2(20),e_phone_no number(10),dept_no varchar2(5),dept_name varchar2(10),job_id number(5),email varchar2(20),salary varchar2(7));

Table created.
============================================================
============================================================

SQL> update employee set e_address='Nagpur' where emp_no=12;

1 row updated.

SQL> update employee set email=' ' where e_name='james';

1 row updated.

SQL> select * from employee;

EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
1 Anurag Bhopal 9988776655 CS123 computer S
12345 [email protected] 600000

1 Aman indore 9988664411 CS2 civil
1222 [email protected] 1000000

1 Ankit Lambakheda 9982333331 CE2 EX
1112 [email protected] 100000


EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
5 Salman kolua 988888331 ME2 ME
3312 [email protected] 1000

6 Asim Jahangirabad 9192939495 CS1 Computer s
5555 [email protected] 120000

7 Hardik Bhopal 98765432 CS4 Computer
7777 [email protected] 123456


EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
8 Ayan Bhopal 87654321 CS5 Civil
8888 [email protected] 98765

12 Astha Nagpur 987654234 CS1 Automobile
12122 [email protected] 111111

13 Bhumi mumbai 8888888 ME1 Mech
13131 [email protected] 12312


EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
14 Muskan Indore 99999 SL1 Sales
14141 [email protected] 121212

15 james Indore 88776655 D24 Marketing
15151 22222

16 Pankaj Betul 989898989 D10 Devlopment
16161 [email protected] 40000



SQL> insert into employee values(1,'Anurag','Bhopal',9988776655,'CS123','computer science',12345,'[email protected]','600000');
insert into employee values(1,'Anurag','Bhopal',9988776655,'CS123','computer science',12345,'[email protected]','600000')
*
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."EMPLOYEE"."DEPT_NAME" (actual:
16, maximum: 10)


SQL> insert into employee values(1, 'Anurag', 'Bhopal', 9988776655, 'CS123', 'computer S', 12345, '[email protected]', '600000');

1 row created.

SQL> select * from employee;

EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
1 Anurag Bhopal 9988776655 CS123 computer S
12345 [email protected] 600000


SQL> insert into employee values(1, 'Aman', 'indore', 9988664411, 'CS2', 'civil', 1222, '[email protected]', '1000000');

1 row created.

SQL> insert into employee values(1, 'Ankit', 'Lambakheda', 9982333331, 'CE2', 'EX', 1112, '[email protected]', '100000');

1 row created.

SQL> select * from employee;

EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
1 Anurag Bhopal 9988776655 CS123 computer S
12345 [email protected] 600000

1 Aman indore 9988664411 CS2 civil
1222 [email protected] 1000000

1 Ankit Lambakheda 9982333331 CE2 EX
1112 [email protected] 100000


SQL> insert into employee values(5, 'Salman', 'kolua', 988888331, 'ME2', 'ME', 3312, '[email protected]', '1000');

1 row created.

SQL> select * from employee;

EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
1 Anurag Bhopal 9988776655 CS123 computer S
12345 [email protected] 600000

1 Aman indore 9988664411 CS2 civil
1222 [email protected] 1000000

1 Ankit Lambakheda 9982333331 CE2 EX
1112 [email protected] 100000


EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
5 Salman kolua 988888331 ME2 ME
3312 [email protected] 1000


SQL> insert into employee values(&emp_no,'&e_name','&e_address',&e_phone_no,'&dept_no','&dept_name',&job_id,'&email','&salary');
Enter value for emp_no: 6
Enter value for e_name: Asim
Enter value for e_address: jahangirabad
Enter value for e_phone_no: 9192939495
Enter value for dept_no: CS45
Enter value for dept_name: Computer S
Enter value for job_id: SE12
Enter value for email: [email protected]
Enter value for salary: 1200000
old 1: insert into employee values(&emp_no,'&e_name','&e_address',&e_phone_no,'&dept_no','&dept_name',&job_id,'&email','&salary')
new 1: insert into employee values(6,'Asim','jahangirabad',9192939495,'CS45','Computer S',SE12,'[email protected]','1200000')
insert into employee values(6,'Asim','jahangirabad',9192939495,'CS45','Computer S',SE12,'[email protected]','1200000')
*
ERROR at line 1:
ORA-00984: column not allowed here


SQL> /
Enter value for emp_no: 6
Enter value for e_name: Asim
Enter value for e_address: Jahangirabad
Enter value for e_phone_no: 9192939495
Enter value for dept_no: CS1
Enter value for dept_name: Computer s
Enter value for job_id: 5555
Enter value for email: [email protected]
Enter value for salary: 120000
old 1: insert into employee values(&emp_no,'&e_name','&e_address',&e_phone_no,'&dept_no','&dept_name',&job_id,'&email','&salary')
new 1: insert into employee values(6,'Asim','Jahangirabad',9192939495,'CS1','Computer s',5555,'[email protected]','120000')

1 row created.

SQL> /
Enter value for emp_no: Hardik
Enter value for e_name:
Enter value for e_address:
Enter value for e_phone_no:
Enter value for dept_no:
Enter value for dept_name:
Enter value for job_id:
Enter value for email:
Enter value for salary:
old 1: insert into employee values(&emp_no,'&e_name','&e_address',&e_phone_no,'&dept_no','&dept_name',&job_id,'&email','&salary')
new 1: insert into employee values(Hardik,'','',,'','',,'','')
insert into employee values(Hardik,'','',,'','',,'','')
*
ERROR at line 1:
ORA-00936: missing expression


SQL> /
Enter value for emp_no: 7
Enter value for e_name: Hardik
Enter value for e_address: Bhopal
Enter value for e_phone_no: 098765432
Enter value for dept_no: CS4
Enter value for dept_name: Computer
Enter value for job_id: 7777
Enter value for email: [email protected]
Enter value for salary: 123456
old 1: insert into employee values(&emp_no,'&e_name','&e_address',&e_phone_no,'&dept_no','&dept_name',&job_id,'&email','&salary')
new 1: insert into employee values(7,'Hardik','Bhopal',098765432,'CS4','Computer',7777,'[email protected]','123456')

1 row created.

SQL> /
Enter value for emp_no: 8
Enter value for e_name: Ayan
Enter value for e_address: Bhopal
Enter value for e_phone_no: 87654321
Enter value for dept_no: CS5
Enter value for dept_name: Civil
Enter value for job_id: 8888
Enter value for email: [email protected]
Enter value for salary: 98765
old 1: insert into employee values(&emp_no,'&e_name','&e_address',&e_phone_no,'&dept_no','&dept_name',&job_id,'&email','&salary')
new 1: insert into employee values(8,'Ayan','Bhopal',87654321,'CS5','Civil',8888,'[email protected]','98765')

1 row created.

SQL> select * from employee;

EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
1 Anurag Bhopal 9988776655 CS123 computer S
12345 [email protected] 600000

1 Aman indore 9988664411 CS2 civil
1222 [email protected] 1000000

1 Ankit Lambakheda 9982333331 CE2 EX
1112 [email protected] 100000


EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
5 Salman kolua 988888331 ME2 ME
3312 [email protected] 1000

6 Asim Jahangirabad 9192939495 CS1 Computer s
5555 [email protected] 120000

7 Hardik Bhopal 98765432 CS4 Computer
7777 [email protected] 123456


EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
8 Ayan Bhopal 87654321 CS5 Civil
8888 [email protected] 98765


7 rows selected.

SQL> /

EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
1 Anurag Bhopal 9988776655 CS123 computer S
12345 [email protected] 600000

1 Aman indore 9988664411 CS2 civil
1222 [email protected] 1000000

1 Ankit Lambakheda 9982333331 CE2 EX
1112 [email protected] 100000


EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
5 Salman kolua 988888331 ME2 ME
3312 [email protected] 1000

6 Asim Jahangirabad 9192939495 CS1 Computer s
5555 [email protected] 120000

7 Hardik Bhopal 98765432 CS4 Computer
7777 [email protected] 123456


EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
8 Ayan Bhopal 87654321 CS5 Civil
8888 [email protected] 98765


7 rows selected.

SQL> insert into employee values(&emp_no,'&e_name','&e_address',&e_phone_no,'&dept_no','&dept_name',&job_id,'&email','&salary');
Enter value for emp_no: 12
Enter value for e_name: Astha
Enter value for e_address: indore
Enter value for e_phone_no: 987654234
Enter value for dept_no: CS1
Enter value for dept_name: Automobile
Enter value for job_id: 12122
Enter value for email: [email protected]
Enter value for salary: 111111
old 1: insert into employee values(&emp_no,'&e_name','&e_address',&e_phone_no,'&dept_no','&dept_name',&job_id,'&email','&salary')
new 1: insert into employee values(12,'Astha','indore',987654234,'CS1','Automobile',12122,'[email protected]','111111')

1 row created.

SQL> /
Enter value for emp_no: 13
Enter value for e_name: Bhumi
Enter value for e_address: mumbai
Enter value for e_phone_no: 8888888
Enter value for dept_no: ME1
Enter value for dept_name: Mech
Enter value for job_id: 13131
Enter value for email: [email protected]
Enter value for salary: 12312
old 1: insert into employee values(&emp_no,'&e_name','&e_address',&e_phone_no,'&dept_no','&dept_name',&job_id,'&email','&salary')
new 1: insert into employee values(13,'Bhumi','mumbai',8888888,'ME1','Mech',13131,'[email protected]','12312')

1 row created.

SQL> /
Enter value for emp_no: 14
Enter value for e_name: Muskan
Enter value for e_address: Indore
Enter value for e_phone_no: 99999
Enter value for dept_no: SL1
Enter value for dept_name: Sales
Enter value for job_id: 14141
Enter value for email: [email protected]
Enter value for salary: 121212
old 1: insert into employee values(&emp_no,'&e_name','&e_address',&e_phone_no,'&dept_no','&dept_name',&job_id,'&email','&salary')
new 1: insert into employee values(14,'Muskan','Indore',99999,'SL1','Sales',14141,'[email protected]','121212')

1 row created.

SQL> /
Enter value for emp_no: 15
Enter value for e_name: james
Enter value for e_address: Indore
Enter value for e_phone_no: 88776655
Enter value for dept_no: D24
Enter value for dept_name: Marketing
Enter value for job_id: 15151
Enter value for email: [email protected]
Enter value for salary: 22222
old 1: insert into employee values(&emp_no,'&e_name','&e_address',&e_phone_no,'&dept_no','&dept_name',&job_id,'&email','&salary')
new 1: insert into employee values(15,'james','Indore',88776655,'D24','Marketing',15151,'[email protected]','22222')

1 row created.

SQL> /
Enter value for emp_no: 16
Enter value for e_name: Pankaj
Enter value for e_address: Betul
Enter value for e_phone_no: 989898989
Enter value for dept_no: D10
Enter value for dept_name: Devlopment
Enter value for job_id: 16161
Enter value for email: [email protected]
Enter value for salary: 40000
old 1: insert into employee values(&emp_no,'&e_name','&e_address',&e_phone_no,'&dept_no','&dept_name',&job_id,'&email','&salary')
new 1: insert into employee values(16,'Pankaj','Betul',989898989,'D10','Devlopment',16161,'[email protected]','40000')

1 row created.

SQL> select * from employee;

EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
1 Anurag Bhopal 9988776655 CS123 computer S
12345 [email protected] 600000

1 Aman indore 9988664411 CS2 civil
1222 [email protected] 1000000

1 Ankit Lambakheda 9982333331 CE2 EX
1112 [email protected] 100000


EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
5 Salman kolua 988888331 ME2 ME
3312 [email protected] 1000

6 Asim Jahangirabad 9192939495 CS1 Computer s
5555 [email protected] 120000

7 Hardik Bhopal 98765432 CS4 Computer
7777 [email protected] 123456


EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
8 Ayan Bhopal 87654321 CS5 Civil
8888 [email protected] 98765

12 Astha indore 987654234 CS1 Automobile
12122 [email protected] 111111

13 Bhumi mumbai 8888888 ME1 Mech
13131 [email protected] 12312


EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
14 Muskan Indore 99999 SL1 Sales
14141 [email protected] 121212

15 james Indore 88776655 D24 Marketing
15151 [email protected] 22222

16 Pankaj Betul 989898989 D10 Devlopment

=============================================================================================
============================================================================================


SQL> select avg(salary) from employee group by (dept_name);

AVG(SALARY)
-----------
98765
40000
111111
600000
100000
1000000
12312
1000
120000
121212
22222

AVG(SALARY)
-----------
123456

12 rows selected.

SQL> select min(salary) from employee group by (dept_name);

MIN(SAL
-------
98765
40000
111111
600000
100000
1000000
12312
1000
120000
121212
22222

MIN(SAL
-------
123456

12 rows selected.

SQL> select min(salary) from employee group by (dept_name);

MIN(SAL
-------
98765
40000
111111
600000
100000
1000000
12312
1000
120000
121212
22222

MIN(SAL
-------
123456

12 rows selected.

SQL> select min(salary) from employee where emp_id=1;
select min(salary) from employee where emp_id=1
*
ERROR at line 1:
ORA-00904: "EMP_ID": invalid identifier


SQL> select min(salary) from employee;

MIN(SAL
-------
1000

SQL> select max(salary) from employee;

MAX(SAL
-------
98765

SQL> select min(salary),dept_name from employee group by (dept_name);

MIN(SAL DEPT_NAME
------- ----------
98765 Civil
40000 Devlopment
111111 Automobile
600000 computer S
100000 EX
1000000 civil
12312 Mech
1000 ME
120000 Computer s
121212 Sales
22222 Marketing

MIN(SAL DEPT_NAME
------- ----------
123456 Computer

12 rows selected.

SQL> select * from employee;

EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
1 Anurag Bhopal 9988776655 CS123 computer S
12345 [email protected] 600000

1 Aman indore 9988664411 CS2 civil
1222 [email protected] 1000000

1 Ankit Lambakheda 9982333331 CE2 EX
1112 [email protected] 100000


EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
5 Salman kolua 988888331 ME2 ME
3312 [email protected] 1000

6 Asim Jahangirabad 9192939495 CS1 Computer s
5555 [email protected] 120000

7 Hardik Bhopal 98765432 CS4 Computer
7777 [email protected] 123456


EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
8 Ayan Bhopal 87654321 CS5 Civil
8888 [email protected] 98765

12 Astha Nagpur 987654234 CS1 Automobile
12122 [email protected] 111111

13 Bhumi mumbai 8888888 ME1 Mech
13131 [email protected] 12312


EMP_NO E_NAME E_ADDRESS E_PHONE_NO DEPT_ DEPT_NAME
---------- -------------------- -------------------- ---------- ----- ----------
JOB_ID EMAIL SALARY
---------- -------------------- -------
14 Muskan Indore 99999 SL1 Sales
14141 [email protected] 121212

15 james Indore 88776655 D24 Marketing
15151 22222

16 Pankaj Betul 989898989 D10 Devlopment
16161 [email protected] 40000


12 rows selected.

SQL> select min(salary),dept_name from employee group by (dept_name);

MIN(SAL DEPT_NAME
------- ----------
98765 Civil
40000 Devlopment
111111 Automobile
600000 computer S
100000 EX
1000000 civil
12312 Mech
1000 ME
120000 Computer s
121212 Sales
22222 Marketing

MIN(SAL DEPT_NAME
------- ----------
123456 Computer

12 rows selected.

SQL> select max(salary),dept_name from employee group by (dept_name);

MAX(SAL DEPT_NAME
------- ----------
98765 Civil
40000 Devlopment
111111 Automobile
600000 computer S
100000 EX
1000000 civil
12312 Mech
1000 ME
120000 Computer s
121212 Sales
22222 Marketing

MAX(SAL DEPT_NAME
------- ----------
123456 Computer

12 rows selected.

SQL> select max(salary) from employee ;

MAX(SAL
-------
98765

SQL> select avg(salary) from employee ;

AVG(SALARY)
-----------
195839.833

SQL> select min(salary),dept_name from employee order by (dept_name);
select min(salary),dept_name from employee order by (dept_name)
*
ERROR at line 1:
ORA-00937: not a single-group group function
=================================================================
===================================================================

SQL> select emp_no,e_name,dept_name,salary from employee where salary>50000;

EMP_NO E_NAME DEPT_NAME SALARY
---------- -------------------- ---------- -------
1 Anurag computer S 600000
1 Aman civil 1000000
1 Ankit EX 100000
6 Asim Computer s 120000
7 Hardik Computer 123456
8 Ayan Civil 98765
12 Astha Automobile 111111
14 Muskan Sales 121212

8 rows selected.

SQL>