1.

Create a table and perform the following basic mysql operations a) Set the
primary key b) Alter the structure of the table c) Insert values d) Delete values based on
constraints e) Display values using various forms of select clause f) Drop the table

~~ CREATE STUDENT TABLE

SQL> create table student

2 (rollno number(6) primary key,
3 name varchar2(15),
4 class char(8),
5 age number(3));

Table created.

~~ DESCRIBE STUDENT TABLE

SQL> desc student;

Name Null? Type

----------------------------------------- -------- ----------------------------
ROLLNO NOT NULL NUMBER(6)
NAME VARCHAR2(15)
CLASS CHAR(8)
AGE NUMBER(3)

~~ INSERT THE VALUES INTO STUDENT TABLE

SQL> insert into student values(22351, 'Anbukumar', 'iiiCS', 20);

1 row created.

SQL> insert into student values(22352, 'Anandhi', 'iiiCS', 20);

1 row created.

SQL> insert into student values(22353, 'Balaji', 'iiiCS', 20);

1 row created.

SQL> insert into student values(23363, 'Chandru', 'iiCS', 19);

1 row created.

SQL> insert into student values(23364, 'Dhivya', 'iiCS', 19);

1 row created.

SQL> insert into student values(23365, 'Elamathi', 'iiCS', 19);

1 row created.

~~ DISPLAY THE VALUES OF STUDENT TABLE

SQL> select * from student;

 ROLLNO NAME CLASS AGE
---------- --------------- -------- ----------
22351 Anbukumar iiiCS 20
22352 Anandhi iiiCS 20
22353 Balaji iiiCS 20
23363 Chandru iiCS 19
23364 Dhivya iiCS 19
23365 Elamathi iiCS 19

6 rows selected.

~~ ALTER THE STUDENT TABLE BY ADDING A COLUMN

SQL> alter table student add(result char(5));

Table altered.

SQL> desc student;

Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO NOT NULL NUMBER(6)
NAME VARCHAR2(15)
CLASS CHAR(8)
AGE NUMBER(3)
RESULT CHAR(5)

~~ ALTER THE STUDENT TABLE BY MODIFYING A COLUMN IN IT

SQL> alter table student modify(class varchar2(8));

Table altered.

SQL> desc student;

Name Null? Type

----------------------------------------- -------- ----------------------------
ROLLNO NOT NULL NUMBER(6)
NAME VARCHAR2(15)
CLASS VARCHAR2(8)
AGE NUMBER(3)
RESULT CHAR(5)

~~ DISPLAY THE VALUES OF ALTERED TABLE STUDENT

SQL> select * from student;

ROLLNO NAME CLASS AGE RESUL

---------- --------------- -------- ---------- -----
22351 Anbukumar iiiCS 20
22352 Anandhi iiiCS 20
22353 Balaji iiiCS 20
23363 Chandru iiCS 19
23364 Dhivya iiCS 19
 23365 Elamathi iiCS 19

6 rows selected.

~~ UPDATE THE VALUE OF STUDENT TABLE

SQL> update student set result='pass';

6 rows updated.

SQL> select * from student;

ROLLNO NAME CLASS AGE RESUL

---------- --------------- -------- ---------- -----
22351 Anbukumar iiiCS 20 pass
22352 Anandhi iiiCS 20 pass
22353 Balaji iiiCS 20 pass
23363 Chandru iiCS 19 pass
23364 Dhivya iiCS 19 pass
23365 Elamathi iiCS 19 pass

6 rows selected.

~~ DELETE A TUPLE OF STUDENT BY USING WHERE CLAUSE

SQL> delete from student where rollno=23365;

1 row deleted.

SQL> select * from student;

ROLLNO NAME CLASS AGE RESUL

---------- --------------- -------- ---------- -----
22351 Anbukumar iiiCS 20 pass
22352 Anandhi iiiCS 20 pass
22353 Balaji iiiCS 20 pass
23363 Chandru iiCS 19 pass
23364 Dhivya iiCS 19 pass
~~ UPDATE THE VALUES OF STUDENT BY USING WHERE CLAUSE

SQL> update student set age=21 where rollno=22351;

1 row updated.
SQL> update student set age=21 where rollno=22352;
1 row updated.
SQL> update student set age=21 where rollno=223533;
0 rows updated.
SQL> update student set age=21 where rollno=22353;
1 row updated.

SQL> select * from student;

ROLLNO NAME CLASS AGE RESUL

---------- --------------- -------- ---------- -----
 22351 Anbukumar iiiCS 21 pass
22352 Anandhi iiiCS 21 pass
22353 Balaji iiiCS 21 pass
23363 Chandru iiCS 19 pass
23364 Dhivya iiCS 19 pass

SQL> select * from student where age=21;

ROLLNO NAME CLASS AGE RESUL

---------- --------------- -------- ---------- -----
22351 Anbukumar iiiCS 21 pass
22352 Anandhi iiiCS 21 pass
22353 Balaji iiiCS 21 pass

SQL> update student set result='Fail' where age=19;

2 rows updated.

SQL> select * from student;

ROLLNO NAME CLASS AGE RESUL

---------- --------------- -------- ---------- -----
22351 Anbukumar iiiCS 21 pass
22352 Anandhi iiiCS 21 pass
22353 Balaji iiiCS 21 pass
23363 Chandru iiCS 19 Fail
23364 Dhivya iiCS 19 Fail

~~ DROP THE STUDENT TABLE

SQL> drop table student;

Table dropped.
SQL> desc student;
ERROR:
ORA-04043: object student does not exist
 2. Develop sql queries to implement the following set operations a) Union b) Union
all c) Intersect d) Intersect all.

~~ CREATE STD1 TABLE

SQL> create table std1(id number(3), name varchar2(20));

Table created.

~~ DESCRIBE STD1 TABLE

SQL> desc std1;

Name Null? Type

----------------------------------------- -------- ----------------------------
ID NUMBER(3)
NAME VARCHAR2(20)

~~ INSERT THE VALUES INTO STD1 TABLE

SQL> insert into std1 values(1, 'Akilan');

1 row created.
SQL> insert into std1 values(2, 'Babu');
1 row created.
SQL> insert into std1 values(3, 'Chithra');
1 row created.
SQL> insert into std1 values(4, 'David');
1 row created.

~~ DISPLAY THE VALUES OF STD1 TABLE

SQL> select * from std1;

ID NAME
---------- --------------------
1 Akilan
2 Babu
3 Chithra
4 David

~~ CREATE THE STD2 TABLE

SQL> create table std2(id number(3), name varchar2(20));

Table created

.
~~ DESCRIBE THE STD2 TABLE

SQL> desc std2;

Name Null? Type

----------------------------------------- -------- ----------------------------
 ID NUMBER(3)
NAME VARCHAR2(20)

~~ INSERT THE VALUES INTO STD2 TABLE

SQL> insert into std2 values(3, 'Chithra');

1 row created.
SQL> insert into std2 values(4, 'David');
1 row created.
SQL> insert into std2 values(5, 'Eniyan');
1 row created.
SQL> insert into std2 values(6, 'Harish');
1 row created.

~~ DISPLAY THE VALUES OF STD2 TABLE

SQL> select * from std2;

ID NAME
---------- --------------------
3 Chithra
4 David
5 Eniyan
6 Harish

~~ JOIN TWO TABLES STD1 AND STD2 USING UNION

SQL> select name from std1 union select name from std2;

NAME
--------------------
Akilan
Babu
Chithra
David
Eniyan
Harish
6 rows selected.

~~ JOIN TWO TABLES STD1 AND STD2 USING UNION ALL

SQL> select name from std1 union all select name from std2;

NAME
--------------------
Akilan
Babu
Chithra
David
Chithra
David
Eniyan
Harish
8 rows selected.

~~ SELECT COMMON VALUES FROM STD1 AND STD2 USING INTERSECT

SQL> select name from std1 intersect select name from std2;

NAME
--------------------
Chithra
David

~~ MINUS OPERATION

SQL> select name from std1 MINUS select name from std2;

NAME
--------------------
Akilan
Babu
3. Develop sql queries to implement the following aggregate functions a) Sum b) Count
c) Average d) Maximum e) Minimum f) Group by clause & having clause.

~~ CREATE THE TABLE STUDENT

SQL> create table student(rolno number(5), name varchar2(15), class char(10), mark number(3),
result char(5));
Table created.

~~ DESCRIBE THE TABLE STUDENT

SQL> desc student;

Name Null? Type

----------------------------------------- -------- ----------------------------
ROLNO NUMBER(5)
NAME VARCHAR2(15)
CLASS CHAR(10)
MARK NUMBER(3)
RESULT CHAR(5)

~~ INSERT THE VALUES INTO STUDENT TABLE

SQL> insert into student values(101, 'Anandh', 'iiics', 89, 'pass');

1 row created.
SQL> insert into student values(102, 'Chandru', 'iiics', 76, 'pass');
1 row created.
SQL> insert into student values(103, 'Dhivya', 'iics', 77, 'pass');
1 row created.
SQL> insert into student values(210, 'Fathima', 'iics', 37, 'fail');
1 row created.
SQL> insert into student values(210, 'Harish', 'iics', 23, 'fail');
1 row created.

~~ DISPLAY THE VALUES OF STUDENT TABLE

SQL> select * from student;

ROLNO NAME CLASS MARK RESUL

---------- --------------- ---------- ---------- -----
101 Anandh iiics 89 pass
102 Chandru iiics 76 pass
103 Dhivya iics 77 pass
210 Fathima iics 37 fail
210 Harish iics 23 fail
~~COUNT

SQL> select count(*) from student;

COUNT(*)
----------
 5
SQL> select count(*) from student where class='iiics';
COUNT(*)
----------
2

SQL> select count(*) from student where class='iics';

COUNT(*)
----------
3

~~MINIMUM

SQL> select min(mark) from student;

MIN(MARK)
----------
23

~~MAXIMUM

SQL> select max(mark) from student;

MAX(MARK)
----------
89

~~SUM

SQL> select sum(mark) from student;

SUM(MARK)
----------
302

~~AVERAGE

SQL> select avg(mark) from student;

AVG(MARK)
----------
60.4
~~GROUP BY CLAUSE

SQL> select count(*) from student group by class;

COUNT(*)
----------
3
2
SQL> select class, count(*) from student group by class;
CLASS COUNT(*)
---------- ----------
iics 3
iiics 2

SQL> select result, count(*) from student group by result;

RESUL COUNT(*)
----- ----------
fail 2
pass 3

SQL> insert into student values(321, 'Madhavan', 'ics', 57, 'pass');

1 row created.
SQL> insert into student values(301, 'Akilan', 'ics', 87, 'pass');
1 row created.
SQL> select result, count(*) from student group by result;

RESUL COUNT(*)
----- ----------
fail 2
pass 5

SQL> select class, count(*) from student group by class;

CLASS COUNT(*)
---------- ----------
ics 2
iics 3
iiics 2

SQL> select class, min(mark) as Low_Mark from student group by class;

CLASS LOW_MARK
---------- ----------
ics 57
iics 23
iiics 76

SQL> select class, max(mark) as Low_Mark from student group by class;

CLASS LOW_MARK
---------- ----------
ics 87
iics 77
iiics 89

SQL> select class, sum(mark) as Low_Mark from student group by class;

CLASS LOW_MARK
---------- ----------
ics 144
iics 137
iiics 165

~~HAVING CLAUSE

SQL> select class, count(*) from student group by class having count(class) >= 2;

CLASS COUNT(*)
---------- ----------
ics 2
iics 3
iiics 2

SQL> select class, count(*) from student group by class having count(class) = 2;

CLASS COUNT(*)
---------- ----------
ics 2
iiics 2
SQL> select result, count(*) from student group by result having count(result) = 2;
RESUL COUNT(*)
----- ----------
fail 2

SQL> select result, count(*) from student group by result having count(result) >= 2;
RESUL COUNT(*)
----- ----------
fail 2
pass 5
4. Develop sql queries to implement following join operations a) Natural join b) Inner
join c) Outer join-left outer, right outer, full outer d) Using join conditions.

~~ CREATE THE TABLE T1

SQL> create table t1(emp_no number(5) primary key, emp_name varchar2(15), dept char(10));

Table created.

~~ DESCRIBE THE TABLE T1

SQL> desc t1;

Name Null? Type

----------------------------------------- -------- ----------------------------
EMP_NO NOT NULL NUMBER(5)
EMP_NAME VARCHAR2(15)
DEPT CHAR(10)

~~ INSERT THE VALUES INTO T1 TABLE

SQL> insert into t1 values(101, 'Amirtha', 'Commerce');

1 row created.
SQL> insert into t1 values(102, 'John', 'Computer');
1 row created.
SQL> insert into t1 values(103, 'James', 'Production');
1 row created.
SQL> insert into t1 values(104, 'Antony', 'Customer');
1 row created.

~~ DISPLAY THE VALUES OF T1

SQL> select * from t1;

EMP_NO EMP_NAME DEPT

---------- --------------- ----------
101 Amirtha Commerce
102 John Computer
103 James Production
104 Antony Customer

SQL> create table t2(item_code varchar2(5), eid number(5), item_name varchar2(10));

Table created.

~~ DESCRIBE THE TABLE T2

SQL> desc t2;

Name Null? Type

----------------------------------------- -------- ----------------------------
ITEM_CODE VARCHAR2(5)
EID NUMBER(5)
ITEM_NAME VARCHAR2(10)

~~ INSERT THE VALUES INTO TABLE T2

SQL> insert into t2 values('I01', 101, 'Soap');

1 row created.
SQL> insert into t2 values('I02', 101, 'Shampoo');
1 row created.
SQL> insert into t2 values('I00', 102, 'Powder');
1 row created.
SQL> insert into t2 values('I03', 102, 'Face Wash');
1 row created.

~~ DISPLAY THE VALUES OF TABLE T2

SQL> select * from t2;

ITEM_ EID ITEM_NAME

----- ---------- ----------
I01 101 Soap
I02 101 Shampoo
I00 102 Powder
I03 102 Face Wash

~~JOIN (INNER JOIN)

SQL> select emp_no, emp_name from t1 inner join t2 on t1.emp_no=t2.eid;

EMP_NO EMP_NAME
---------- ---------------
101 Amirtha
101 Amirtha
102 John
102 John
SQL> select * from t1 inner join t2 on t1.emp_no=t2.eid;
EMP_NO EMP_NAME DEPT ITEM_ EID ITEM_NAME
---------- --------------- ---------- ----- ---------- ----------
101 Amirtha Commerce I01 101 Soap
101 Amirtha Commerce I02 101 Shampoo
102 John Computer I00 102 Powder
102 John Computer I03 102 Face Wash
~~LEFT (OUTER) JOIN

SQL> select emp_name, dept from t1 left join t2 on t1.emp_no=t2.eid;

EMP_NAME DEPT
--------------- ----------
Amirtha Commerce
Amirtha Commerce
John Computer
John Computer
James Production
Antony Customer
6 rows selected.

SQL> select * from t1 left join t2 on t1.emp_no=t2.eid;

EMP_NO EMP_NAME DEPT ITEM_ EID ITEM_NAME
---------- --------------- ---------- ----- ---------- ----------
101 Amirtha Commerce I01 101 Soap
101 Amirtha Commerce I02 101 Shampoo
102 John Computer I00 102 Powder
102 John Computer I03 102 Face Wash
103 James Production
104 Antony Customer
6 rows selected.

~~RIGHT (OUTER) JOIN

SQL> select emp_name, dept from t1 right join t2 on t1.emp_no=t2.eid;

EMP_NAME DEPT
--------------- ----------
Amirtha Commerce
Amirtha Commerce
John Computer
John Computer
SQL> select * from t1 right join t2 on t1.emp_no=t2.eid;
EMP_NO EMP_NAME DEPT ITEM_ EID ITEM_NAME
---------- --------------- ---------- ----- ---------- ----------
101 Amirtha Commerce I02 101 Shampoo
101 Amirtha Commerce I01 101 Soap
102 John Computer I03 102 Face Wash
102 John Computer I00 102 Powder

~~FULL (OUTER) JOIN

SQL> select emp_name, dept from t1 full join t2 on t1.emp_no=t2.eid;

EMP_NAME DEPT
--------------- ----------
Amirtha Commerce
Amirtha Commerce
John Computer
John Computer
James Production
Antony Customer
6 rows selected.

SQL> select * from t1 full join t2 on t1.emp_no=t2.eid;

 EMP_NO EMP_NAME DEPT ITEM_ EID ITEM_NAME
---------- --------------- ---------- ----- ---------- ----------
101 Amirtha Commerce I01 101 Soap
101 Amirtha Commerce I02 101 Shampoo
102 John Computer I00 102 Powder
102 John Computer I03 102 Face Wash
103 James Production
104 Antony Customer
6 rows selected.
 5. Develop sql queries to implement nested subqueries a) Set membership (int, not
int) b) Set comparison (some, all) c) Empty relation (exists, not exists) d) Check for
existence of Duplicate tuples(unique, not unique).

~~ CREATE THE TABLE EMP

SQL> create table emp(emp_id varchar2(6),

2 emp_name char(15),
3 salary number(6,2),
4 dept_id varchar2(6));

Table created.

~~ DESCRIBE THE TABLE EMP

SQL> desc emp;

Name Null? Type

----------------------------------------- -------- ----------------------------
EMP_ID VARCHAR2(6)
EMP_NAME CHAR(15)
SALARY NUMBER(6,2)
DEPT_ID VARCHAR2(6)

~~ INSERT THE VALUES INTO EMP TABLE

SQL> insert into emp values('&emp_id','&emp_name','&salary','&dept_id');

Enter value for emp_id: E001
Enter value for emp_name: Jinna
Enter value for salary: 2000.50
Enter value for dept_id: CS008
old 1: insert into emp values('&emp_id','&emp_name','&salary','&dept_id')
new 1: insert into emp values('E001','Jinna','2000.50','CS008')
1 row created.

SQL> alter table emp modify(emp_id varchar2(6) primary key,salary number(8,2));

Table altered.

SQL> desc emp;

Name Null? Type

----------------------------------------- -------- ----------------------------
EMP_ID NOT NULL VARCHAR2(6)
EMP_NAME CHAR(15)
SALARY NUMBER(8,2)
DEPT_ID VARCHAR2(6)

SQL> insert into emp values('&emp_id','&emp_name','&salary','&dept_id');

Enter value for emp_id: E002
Enter value for emp_name: James
Enter value for salary: 20000.00
Enter value for dept_id: TA001
old 1: insert into emp values('&emp_id','&emp_name','&salary','&dept_id')
new 1: insert into emp values('E002','James','20000.00','TA001')

1 row created.
SQL> /
Enter value for emp_id: E003
Enter value for emp_name: Anbu

Enter value for salary: 25000.20

Enter value for dept_id: CS008
old 1: insert into emp values('&emp_id','&emp_name','&salary','&dept_id')
new 1: insert into emp values('E003','Anbu','25000.20','CS008')

1 row created.

SQL> commit;
Commit complete.

~~ DISPLAY THE VALUES OF EMP TABLE

SQL> select * from emp;

EMP_ID EMP_NAME SALARY DEPT_I

------ --------------- ---------- ------
E001 Jinna 2000.5 CS008
E002 James 20000 TA001
E003 Anbu 25000.2 CS008

SQL> insert into emp values('&emp_id','&emp_name','&salary','&dept_id');

Enter value for emp_id: E004
Enter value for emp_name: Mani
Enter value for salary: 22000.00
Enter value for dept_id: EC003
old 1: insert into emp values('&emp_id','&emp_name','&salary','&dept_id')
new 1: insert into emp values('E004','Mani','22000.00','EC003')

1 row created.
SQL> /
Enter value for emp_id: E005
Enter value for emp_name: Kannan
Enter value for salary: 30000
Enter value for dept_id: ENG02
old 1: insert into emp values('&emp_id','&emp_name','&salary','&dept_id')
new 1: insert into emp values('E005','Kannan','30000','ENG02')
1 row created.

SQL> /
Enter value for emp_id: E006
Enter value for emp_name: Varun
Enter value for salary: 15000
Enter value for dept_id: COM03
old 1: insert into emp values('&emp_id','&emp_name','&salary','&dept_id')
new 1: insert into emp values('E006','Varun','15000','COM03')

1 row created.
SQL> select * from emp;
EMP_ID EMP_NAME SALARY DEPT_I
------ --------------- ---------- ------
E001 Jinna 2000.5 CS008
E002 James 20000 TA001
E003 Anbu 25000.2 CS008
E004 Mani 22000 EC003
E005 Kannan 30000 ENG02
E006 Varun 15000 COM03
6 rows selected.

~~ CREATE THE TABLE DEPT

SQL> create table dept(dept_id varchar2(6) primary key,

2 dept_name char(20),
3 location_id number(5));

Table created.

~~ DESCRIBE THE TABLE DEPT

SQL> desc dept;

Name Null? Type
----------------------------------------- -------- ----------------------------
DEPT_ID NOT NULL VARCHAR2(6)
DEPT_NAME CHAR20)
LOCATION_ID NUMBER(5)

~~ INSERT THE VALUES INTO TABLE DEPT

SQL> insert into dept values('&dept_id', '&dept_name',&location_id);

Enter value for dept_id: CS008
Enter value for dept_name: Computer Science
Enter value for location_id: 1500
old 1: insert into dept values('&dept_id', '&dept_name',&location_id)
new 1: insert into dept values('CS008', 'Computer Science',1500)
1 row created.

SQL> /
Enter value for dept_id: ENG02
Enter value for dept_name: English

Enter value for location_id: 1200

 old 1: insert into dept values('&dept_id', '&dept_name',&location_id)
new 1: insert into dept values('ENG02', 'English',1200)
1 row created.

SQL> /
Enter value for dept_id: COM03
Enter value for dept_name: Commerce
Enter value for location_id: 1100
old 1: insert into dept values('&dept_id', '&dept_name',&location_id)
new 1: insert into dept values('COM03', 'Commerce',1100)

1 row created.
SQL> /
Enter value for dept_id: EC003
Enter value for dept_name: Economics
Enter value for location_id: 1000
old 1: insert into dept values('&dept_id', '&dept_name',&location_id)
new 1: insert into dept values('EC003', 'Economics',1000)

1 row created.
SQL> /
Enter value for dept_id: TA001
Enter value for dept_name: Tamil
Enter value for location_id: 1400
old 1: insert into dept values('&dept_id', '&dept_name',&location_id)
new 1: insert into dept values('TA001', 'Tamil',1400)
1 row created.

~~ DISPLAY THE VALUES OF TABLE DEPT

SQL> select * from dept;

DEPT_I DEPT_NAME LOCATION_ID

------ -------------------- -----------
CS008 Computer Science 1500
ENG02 English 1200
COM03 Commerce 1100
EC003 Economics 1000
TA001 Tamil 1400

SQL> commit;
Commit complete.

~~ SUB QUERIES

~~SOME

SQL> select emp_name from emp where salary > some(select salary from emp where
dept_id='cs001');

EMP_NAME
--------------------
james
Mani
anbu
Kumaran

~~IN

SQL> select emp_id, emp_name from emp where dept_id in(select dept_id from dept where
location_id=1500);

EMP_ID EMP_NAME
------ ---------------
E001 Jinna
E003 Anbu

NOT IN

SQL> select emp_id, emp_name from emp where dept_id not in (select dept_id from dept where
location_id=1500);

EMP_ID EMP_NAME
------ ---------------
E002 James
E004 Mani
E005 Kannan
E006 Varun

SQL> select * from emp where salary=(select max(salary) from emp) order by emp_name;
EMP_ID EMP_NAME SALARY DEPT_I
------ --------------- ---------- ------
E005 Kannan 30000 ENG02
SQL> select dept_name from dept d where exists(select * from emp e where salary>20000 and
e.dept_id=d.dept_id) order by dept_name;

DEPT_NAME
--------------------
Computer Science
Economics
English
SQL> select dept_name from dept d where not exists(select * from emp e where salary>20000
and e.dept_id=d.dept_id) order by dept_name;

DEPT_NAME
--------------------
Commerce
Tamil

SQL> select * from emp where salary>= all(select (min(salary)) from emp group by dept_id);
EMP_ID EMP_NAME SALARY DEPT_I
------ --------------- ---------- ------
E005 Kannan 30000 ENG02
SQL> select emp_id, emp_name, salary from emp where salary>=some(select max(salary) from
emp group by dept_id);
EMP_ID EMP_NAME SALARY
------ --------------- ----------
E002 James 20000
E003 Anbu 25000.2
E004 Mani 22000
E005 Kannan 30000
E006 Varun 15000
SQL> select max(salary) from emp group by dept_id;
MAX(SALARY)
-----------
15000
22000
30000
25000.2
20000

SQL> select max(salary) from emp;

MAX(SALARY)
-----------
30000

SQL> select emp_id, emp_name, salary from emp where salary>=any(select max(salary) from
emp group by dept_id);

EMP_ID EMP_NAME SALARY

------ --------------- ----------
E002 James 20000
E003 Anbu 25000.2
E004 Mani 22000
E005 Kannan 30000
E006 Varun 15000

SQL> select round(avg(average_salary),0) from (select avg(salary)average_salary from emp

group by dept_id) dept_salary;

ROUND(AVG(AVERAGE_SALARY),0)
----------------------------
20100
SQL> SELECT COUNT (DISTINCT DEPT_NAME) FROM DEPT;

COUNT(DISTINCTDEPT_NAME)
------------------------
9

SQL> SELECT DISTINCT DEPT_NAME FROM DEPT;

DEPT_NAME
--------------------
zoology
maths
physics
economics
commerce
computer science
english
Tamil
tamil

9 rows selected.
 6. Develop sql queries to create a views and expand it

~~ CREATE THE TABLE STUD_DETAILS

SQL>create
tablestud_details(idint,namevarchar(10),addressvarchar(10));Tablecreated

~~ DESCRIBE THE TABLE STUD_DETAILS

SQL>descstud_details;

Name Null? Type

ID NUMBER(38)

NAME VARCHAR2(10)

ADDRESS VARCHAR2(10)

~~ INSER THE VALUES INTO THE TABLE STUD_DETAILS

SQL> insert into stud_details

values(1,'Rahul','Delhi');1rowcreated.
SQL>insertintostud_detailsvalues(2,'Sara','Pu
ne');1rowcreated.
SQL>insertintostud_detailsvalues(3,'Rohit','Mumb
ai');1rowcreated.

SQL>insertintostud_detailsvalues(4,'Preeti','Pu
ne');1rowcreated.

~~ DISPLAY THE VALUES OF TABLE STUD_DETAILS

SQL>select*fromstud_details;
ID NAME ADDRESS

1 Rahul Delhi

2 Sara Pune

3 Rohit Mumbai

4 Preethi Pune

~~ CREATE THE TABLE MARK_DETAILS

SQL>createtablemark_details(idint,namevarchar(10),marksint
);
Tablecreated.
~~ DISPLAY THE VALUES OF THE TABLE MARK_DETAILS

SQL>descmark_details;

Name Null? Type

ID NUMBER(38)

NAME VARCHAR2(10)

MARKS NUMBER(38)

~~ INSERT THE VALUES INTO THE TABLE MARK_DETAILS

SQL>insertintomark_detailsvalues(1,'Rahul',
88);1rowcreated.
SQL>insertintomark_detailsvalues(2,'Sara'
,83);1rowcreated.
SQL>insertintomark_detailsvalues(3,'Rohit'
,76);1rowcreated.
SQL>insertintomark_detailsvalues(4,'Preethi',
92);1rowcreated.

~~ DISPLAY THE THE VALUES OF TABLE MARK_DETAILS

SQL> select * from

mark_details;

ID NAME
MARKS

1 Rahul 88

2 Sara 83

3 Rohit 76

4 Preethi 92

~~CREATETHE VIEW STUDVIEW

SQL> create view studview as select name, address from stud_details where
id>1;
View created.

~~DISPLAYINGVIEWRECORD

SQL>select*fromstu
dview;
 NAME
ADDRES
S

RohitMumbai
Sara Pune
Preethi Pune

~~CREATETHE VIEW MARKVIEW

SQL>createviewmarkviewasselectname,marksfrom mark_detailswheremarks>85;View
created.

~~DISPLAYING VIEWRECORD
SQL>select*frommarkview;

NAME MARKS

Rahul 88

Preethi 92

~~UPDATE/REPLACEVIEW

SQL>createorreplaceviewstudviewasselectid,name,addressfrom stud_detailswhereid>=2;
View created.

~~DISPLAYING VIEWRECORD

SQL>selectid,name,addressfromstud_detailswhereid>=2;

ID NAME ADDRESS

2 Sara Pune

3 Rohit Mumbai

4 Preethi Pune

~~UPDATE/REPLACEVIEW

SQL> create or replace view markview as select id,name,marks from mark_details

wheremarks>80;

Viewcreated.

~~DISPLAYING VIEWRECORD
SQL>selectid,name,marksfromBmark_detailswheremarks>8
0;

ID NAMEMARKS

1 Rahul 88

2 Sara 83
4 Preethi 92

~~DELETE VIEW

SQL>dropviewstudview;
View dropped.

SQL>dropviewma
rkview;View
dropped.
 7. Develop sql queries to implement a) String operations using % b) String operations
using ‘_’ c) Sort the element using asc,desc [*create necessary reletions with requires
attribute]

~~ CREATE THE EMPLOYEE TABLE

SQL> CREATE TABLE employee( empid int primary key, name varchar2(20), position
varchar2(20), salary decimal(10,2));

Table created.

~~ DESCRIBE EMPLYEE TABLE

SQL> desc employee;

Name Null? Type

----------------------------------------- -------- ----------------------------
EMPID NOT NULL NUMBER(38)
NAME VARCHAR2(20)
POSITION VARCHAR2(20)
SALARY NUMBER(10,2)

~~ INSERT THE VALUES INTO EMPLOYEE TABLE

SQL> insert into employee(empid, name, position, salary) values(1,'Akilan','Manager',50000.00);

1 row created.
SQL> INSERT INTO employee(empid, name, position, salary)VALUES(2, 'Bob', 'Developer',
4500.00);
1 row created.
SQL> INSERT INTO employee(empid, name, position, salary)VALUES(3, 'Charlie', 'Data Analyst',
5500.00);
1 row created.
SQL> INSERT INTO employee (empid, name, position, salary)VALUES(4, 'David', 'Developer',
4000.00);
1 row created.
SQL> INSERT INTO employee(empid, name, position, salary)VALUES(5, 'Eve', 'HR Specialist',
3500.00);
1 row created.
SQL> INSERT INTO employee(empid, name, position, salary)VALUES(6, 'Frank', 'Data Scientist',
6000.00);
1 row created.
SQL> INSERT INTO employee(empid, name, position, salary)VALUES(7, 'Akilan', 'Data Scientist',
6000.00);
1 row created.
SQL> INSERT INTO employee(empid, name, position, salary)VALUES(8, 'Babu', 'Data Analyst',
5500.00);
1 row created.

~~ DISPLAY THE VALUES OF EMPLOYEE

SQL> select * from employee;

EMPID NAME POSITION SALARY

---------- -------------------- -------------------- ----------
1 Akilan Manager 50000
2 Bob Developer 4500
3 Charlie Data Analyst 5500
4 David Developer 4000
5 Eve HR Specialist 3500
6 Frank Data Scientist 6000
7 Akilan Data Scientist 6000
8 Babu Data Analyst 5500
8 rows selected.

~~STRING OPERATION USING %

SQL> select * from employee where name like 'a%';

no rows selected
SQL> select * from employee where name like 'A%';

EMPID NAME POSITION SALARY

---------- -------------------- -------------------- ----------
1 Akilan Manager 50000
7 Akilan Data Scientist 6000

SQL> select * from employee where name like '%per';

no rows selected
SQL> select * from employee where position like '%per';

EMPID NAME POSITION SALARY

---------- -------------------- -------------------- ----------
2 Bob Developer 4500
4 David Developer 4000

~~ STRING OPERATION USING ‘_’

SQL> select * from employee where name like '_';

no rows selected
SQL> select * from employee where position like '_a%';

EMPID NAME POSITION SALARY

---------- -------------------- -------------------- ----------
1 Akilan Manager 50000
3 Charlie Data Analyst 5500
6 Frank Data Scientist 6000
7 Akilan Data Scientist 6000
8 Babu Data Analyst 5500

SQL> select * from employee where name like 'b%_';

no rows selected
SQL> select * from employee where name like 'B%_';

EMPID NAME POSITION SALARY

---------- -------------------- -------------------- ----------
2 Bob Developer 4500
8 Babu Data Analyst 5500

~~ STRING OPERATION USING ASCENDING

SQL> select * from employee order by salary ASC;

EMPID NAME POSITION SALARY

---------- -------------------- -------------------- ----------
5 Eve HR Specialist 3500
4 David Developer 4000
2 Bob Developer 4500
3 Charlie Data Analyst 5500
8 Babu Data Analyst 5500
6 Frank Data Scientist 6000
7 Akilan Data Scientist 6000
1 Akilan Manager 50000

8 rows selected.

~~ STRING OPERATION USING DECENDING

SQL> select * from employee order by salary DESC;

EMPID NAME POSITION SALARY

---------- -------------------- -------------------- ----------
1 Akilan Manager 50000
7 Akilan Data Scientist 6000
 6 Frank Data Scientist 6000
3 Charlie Data Analyst 5500
8 Babu Data Analyst 5500
2 Bob Developer 4500
4 David Developer 4000
5 Eve HR Specialist 3500
8 rows selected.
8. Consider the following database for a banking enterprise BRANCH(branch-
name:string, branchcity:string, assets:real) ACCOUNT(accno:int, branch-name:string,
balance:real) DEPOSITOR(customername:string, accno:int) CUSTOMER(customer-
name:string, customer-street:string, customercity:string) LOAN(loan-number:int, branch-
name:string, amount:real) BORROWER(customer-name:string, loannumber:int) i. Create
the above tables by properly specifying the primary keys and the foreign keys ii. Enter at
least five tuples for each relation iii. Find all the customers who have at least two accounts
at the Main branch. iv. Find all the customers who have an account at all the branches
located in a specific city. v. Demonstrate how you delete all account tuples at every branch
located in a specific city.

~~ CREATE BRANCH TABLE

SQL> CREATE TABLE BRANCH (

2 branch_name VARCHAR(20),
3 branch_city VARCHAR(20),
4 assets REAL,
5 PRIMARY KEY (branch_name) );

Table created.

~~ DESCRIBE THE TABLE BRANCH

SQL> desc BRANCH;

Name Null? Type

----------------------------------------- -------- ----------------------------
BRANCH_NAME NOT NULL VARCHAR2(20)
BRANCH_CITY VARCHAR2(20)
ASSETS FLOAT(63)

~~ INSERT VALUES INTO BRANCH TABLE

SQL> INSERT INTO BRANCH VALUES ('Main', 'New York', 5000000);

1 row created.

SQL> INSERT INTO BRANCH VALUES ('North', 'New York', 3000000);

1 row created.

SQL> INSERT INTO BRANCH VALUES ('East', 'Boston', 4000000);

1 row created.
SQL> INSERT INTO BRANCH VALUES ('West', 'Los Angeles', 3500000);
1 row created.

SQL> INSERT INTO BRANCH VALUES ('South', 'Chicago', 4500000);

1 row created.

~~ DISPLAY THE VALUES OF BRANCH

SQL> SELECT * FROM BRANCH;

BRANCH_NAME BRANCH_CITY ASSETS

-------------------- -------------------- ----------
Main New York 5000000
North New York 3000000
East Boston 4000000
West Los Angeles 3500000
South Chicago 4500000

~~ CREATE ACCOUNT TABLE

SQL> CREATE TABLE ACCOUNT (

2 acc_no INT,
3 branch_name VARCHAR(20),
4 balance REAL,
5 PRIMARY KEY (acc_no),
6 FOREIGN KEY (branch_name) REFERENCES BRANCH(branch_name) );
Table created.

~~ DESCRIBE THE TABLE ACCOUNT

SQL> DESC ACCOUNT;

Name Null? Type

----------------------------------------- -------- ----------------------------
ACC_NO NOT NULL NUMBER(38)
BRANCH_NAME VARCHAR2(20)
BALANCE FLOAT(63)

~~ INSERT VALUE INTO ACCOUNT TABLE

SQL> INSERT INTO ACCOUNT VALUES (1001, 'Main', 5000);

1 row created.

SQL> INSERT INTO ACCOUNT VALUES (1002, 'North', 7000);

1 row created.

SQL> INSERT INTO ACCOUNT VALUES (1003, 'East', 8000);

1 row created.

SQL> INSERT INTO ACCOUNT VALUES (1004, 'Main', 9000);

1 row created.

SQL> INSERT INTO ACCOUNT VALUES (1005, 'South', 10000);

1 row created.

~~ DISPLAY THE VALUES OF TABLE ACCOUNT

SQL> SELECT * FROM ACCOUNT;

ACC_NO BRANCH_NAME BALANCE

---------- -------------------- ----------
1001 Main 5000
1002 North 7000
1003 East 8000
1004 Main 9000
1005 South 10000

~~ CREATE DEPOSITOR TABLE

SQL> CREATE TABLE DEPOSITOR (

2 customer_name VARCHAR(20),
3 acc_no INT,
4 PRIMARY KEY (customer_name, acc_no),
5 FOREIGN KEY (acc_no) REFERENCES ACCOUNT(acc_no) );
Table created.

~~ DISPLAY THE VALUES OF TABLE DEPOSITOR

SQL> DESC DEPOSITOR;

Name Null? Type

----------------------------------------- -------- ----------------------------
CUSTOMER_NAME NOT NULL VARCHAR2(20)
ACC_NO NOT NULL NUMBER(38)

~~ INSERT VALUE INTO DEPOSITOR

SQL> INSERT INTO DEPOSITOR VALUES ('Alice', 1001);

1 row created.

SQL> INSERT INTO DEPOSITOR VALUES ('Alice', 1004);

1 row created.

SQL> INSERT INTO DEPOSITOR VALUES ('Bob', 1002);

1 row created.

SQL> INSERT INTO DEPOSITOR VALUES ('Charlie', 1005);

1 row created.

SQL> INSERT INTO DEPOSITOR VALUES ('David', 1003);

1 row created.

~~ DISPLAY THE VALUES OF TABLE DEPOSITOR

SQL> SELECT * FROM DEPOSITOR;

CUSTOMER_NAME ACC_NO
-------------------- ----------
Alice 1001
Alice 1004
Bob 1002
Charlie 1005
David 1003

~~ CREATE CUSTOMER TABLE

SQL> CREATE TABLE CUSTOMER (

2 customer_name VARCHAR(20),
3 customer_street VARCHAR(20),
4 customer_city VARCHAR(20),
5 PRIMARY KEY (customer_name) );

Table created.

~~ DESCRIBE THE TABLE CUSTOMER

SQL> DESC CUSTOMER;

Name Null? Type

----------------------------------------- -------- ----------------------------
CUSTOMER_NAME NOT NULL VARCHAR2(20)
CUSTOMER_STREET VARCHAR2(20)
CUSTOMER_CITY VARCHAR2(20)

~~INSERT VALUE INTO CUSTOMER TABLE

SQL> INSERT INTO CUSTOMER VALUES ('Alice', '123 Elm St', 'New York');
1 row created.

SQL> INSERT INTO CUSTOMER VALUES ('Bob', '456 Oak St', 'Boston');
1 row created.

SQL> INSERT INTO CUSTOMER VALUES ('Charlie', '789 Pine St', 'Chicago');
1 row created.

SQL> INSERT INTO CUSTOMER VALUES ('David', '101 Maple St', 'Los Angeles');
1 row created.

SQL> INSERT INTO CUSTOMER VALUES ('Eve', '202 Birch St', 'New York');
1 row created.

~~ DISPLAY THE VALUES OF TABLE CUSTOMER

SQL> SELECT * FROM CUSTOMER;

CUSTOMER_NAME CUSTOMER_STREET CUSTOMER_CITY

-------------------- -------------------- --------------------
Alice 123 Elm St New York
Bob 456 Oak St Boston
Charlie 789 Pine St Chicago
David 101 Maple St Los Angeles
Eve 202 Birch St New York

~~ CREATE LOAN TABLE

SQL> CREATE TABLE LOAN ( loan_number INT,

2 branch_name VARCHAR(20),
3 amount REAL,
4 PRIMARY KEY (loan_number),
5 FOREIGN KEY (branch_name) REFERENCES BRANCH(branch_name) );

Table created

~~ DESCRIBE THE TABLE LOAN

SQL> DESC LOAN;

Name Null? Type
----------------------------------------- -------- ----------------------------
LOAN_NUMBER NOT NULL NUMBER(38)
BRANCH_NAME VARCHAR2(20)
AMOUNT FLOAT(63)

~~ INSERT VALUE INTO LOAN TABLE

SQL> INSERT INTO LOAN VALUES (2001, 'Main', 15000);

1 row created.

SQL> INSERT INTO LOAN VALUES (2002, 'North', 20000);

1 row created.

SQL> INSERT INTO LOAN VALUES (2003, 'East', 25000);

1 row created.

SQL> INSERT INTO LOAN VALUES (2004, 'West', 10000);

1 row created.

SQL> INSERT INTO LOAN VALUES (2005, 'South', 30000);

1 row created.

~~DISPLAY THE VALUES OF LOAN TABLE

SQL> SELECT * FROM LOAN;

LOAN_NUMBER BRANCH_NAME AMOUNT

----------- -------------------- ----------
2001 Main 15000
2002 North 20000
2003 East 25000
2004 West 10000
2005 South 30000

~~ CREATE BORROWER TABLE

SQL> CREATE TABLE BORROWER (

2 Customer_name varchar(20),
3 loan_number INT, PRIMARY KEY (customer_name, loan_number),
4 FOREIGN KEY (customer_name) REFERENCES CUSTOMER(customer_name),
5 FOREIGN KEY (loan_number) REFERENCES LOAN(loan_number) );
 Table created.

~~ DESCRIBE THE TABLE BORROWER

SQL> DESC BORROWER;

Name Null? Type

----------------------------------------- -------- ----------------------------
CUSTOMER_NAME NOT NULL VARCHAR2(20)
LOAN_NUMBER NOT NULL NUMBER(38)

~~ INSERT VALUE INTO BORROWER TABLE

SQL> INSERT INTO BORROWER VALUES ('Alice', 2001);

1 row created.

SQL> INSERT INTO BORROWER VALUES ('Bob', 2002);

1 row created.
SQL> INSERT INTO BORROWER VALUES ('Charlie', 2003);
1 row created.

SQL> INSERT INTO BORROWER VALUES ('David', 2004);

1 row created.

SQL> INSERT INTO BORROWER VALUES ('Eve', 2005);

1 row created.

~~ DISPLAY THE VALUES OF TABLE BORROWER

SQL> SELECT * FROM BORROWER;

CUSTOMER_NAME LOAN_NUMBER
-------------------- -----------
Alice 2001
Bob 2002
Charlie 2003
David 2004
Eve 2005

~~ FINDING THE CUSTOMERS WHO HAVE ATLEAST TWO ACCOUNTAT AT THE MAIN
BRANCHES

SQL> SELECT D.customer_name

 2 FROM DEPOSITOR D, ACCOUNT A
3 WHERE D.acc_no = A.acc_no
4 AND A.branch_name = 'Main'
5 GROUP BY D.customer_name HAVING COUNT(D.acc_no) >= 2;

CUSTOMER_NAME
--------------------
Alice

~~ FINDING THE CUSTOMERS WHO HAVE ATLEAST TWO ACCOUNTAT ALL

BRANCHES

SQL> SELECT D.customer_name

2 FROM DEPOSITOR D, ACCOUNT A, BRANCH B
3 WHERE D.acc_no = A.acc_no AND A.branch_name = B.branch_name AND
4 B.branch_city = 'New York' GROUP BY D.customer_name
5 HAVING COUNT(DISTINCT A.branch_name) = (SELECT COUNT(branch_name)
6 FROM BRANCH WHERE branch_city = 'New York');

no rows selected

SQL> DELETE FROM ACCOUNT

2 WHERE branch_name IN (SELECT branch_name FROM BRANCH
3 WHERE branch_city = 'Boston');

DELETE FROM ACCOUNT

*ERROR at line 1:
ORA-02292: integrity constraint (SYSTEM.SYS_C004011) violated - child record found