-
Notifications
You must be signed in to change notification settings - Fork 58
/
Day-28_ORACLE_SQL_JOINS.txt
202 lines (142 loc) · 7.56 KB
/
Day-28_ORACLE_SQL_JOINS.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
"Welcome To Ashok IT"
"Oracle Database"
Topic : Oracle Joins
Date : 29/12/2022
(Session - 28)
_____________________________________________________________________________________________________________________________
Important Information
*********************
>> Oracle Class Notes ::: https://github.com/ashokitschool/ORACLE_CLASS_NOTES
>> Class Recording ::: Will be available through Ashok IT Portal
>> Class Related Updates "Join In WhatsApp Group" check with Admin Team.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yesterday Session
=================
1) Ways for Interacting the MYSQL Database
- Command Line Interface(CLI) >>>>> By using this interface we always able to connect with DBA Account only
- Graphical User Interface(My SQL Work Bench) >>> We can use for both DBA Account & User Account.
2) SQL Commands to connect with MYSQL
- select current_user();
- create database <database_name>;
- show databases >>> Get the list of Databases available in Current user
- use <database_name> >>>>> Connecting to particular database from list of databases
- show tables >>>> Getting list of tables from selected databases
- \! cls >>>>> clearing the console history
- select now() >>>> getting the timestamp value from mysql database
- DDL Commands(Create,alter,drop,truncate),DML(insert,update,select,delete) are same for all Relational Database softwares.
- creating user (create user 'suresh'@'localhost' identified by 'suresh')
- Granting permissions to the user (grant all privilliges on *.* to suresh@localhost);
- Granting specific permissions to the user(GRANT CREATE,SELECT,INSERT ON *.* TO suresh@localhost);
- `ALL PRIVILEGES`: The user is granted all privileges except GRANT OPTION and PROXY.
`ALTER`: The user can change the structure of a table or database.
`CREATE`: The user can create new databases and tables.
`DELETE`: The user can delete rows in a table.
`INSERT`: The user can add rows to a table.
`SELECT`: The user can read rows from a table.
`UPDATE`: The user can update rows in a table
- Removing permission for an user(REVOKE ALL PRIVILEGES ON *.* FROM ‘suresh’@’localhost’)
- Resetting password for an root account (or) user account
ALTER USER 'local_user'@'localhost' IDENTIFIED BY 'new_password';
- Dropping the user (DROP USER 'local_user'@'localhost');
Today Session :
===============
* In the earlier discussion we created the relationship between database tables using primary key and foreign key constraints i.e., Physical Join.
* Joins in oracle are mainly used to retreive the data from one or more database tables.
* Basically we can retrieve the data from multiple tables based on common columns between the tables.
* If there is no common column between the database tables then we won't extract the data from multiple tables.
* Whenever we wanted to extract the data from multiple tables as programmer we need to specify the "JOIN CONDITION".
* JOIN CONDITION is nothing but primary key column of one table and foreign key column of another table.
dept table >>>>>>>>>>>>>>> deptno >>>>>>>>>>>> primary key
emp table >>>>>>>>>>>>>>> deptno >>>>>>>>>>>> Foreign key
select * from emp e,dept d where d.deptno = e.deptno;
d.deptno = e.deptno >>>>>>>>>>>>Join Condition
* Oracle supports following types of Joins
1) Inner Join
2) left Join
3) Right Join
4) Full Outer Join
5) Cross Join
6) Self Join
7) Natural Join
* For better Understanding of the above JOINS created office database which contains employees,departments,managers,projects
for this we are going to maintain the data with below tables
ashokit_dept >>>>>>>>>>>>>> Maintaining the department information
ashokit_managers >>>>>>>>>>>>>> Maintaining the Managers information
ashokit_emp >>>>>>>>>>>> Maintaining the employees information
ashokit_projects >>>>>>>>> Maintaining the projets related information
* Observe the information for above tables
***************************************
ashokit_dept >>>>> Primary Key(dept_id)
***************************************
dept_id dept_name
------- --------
D1 IT
D2 HR
D3 FINANCE
D4 ADMIN
D5 NON-ADMIN
D6 MARKETING
D7 SALES
*******************************************************************
ashokit_managers >>>>> Primary Key(Manager_id), ForeignKey(dept_id)
*******************************************************************
Manager_id Manager_name dept_id
---------- ------------ -------
M1 ASHOK D4
M2 MAHESH D1
M3 SURESH D2
M4 RAMESH D2
M5 RAJESH D6
M6 NARESH D7
M7 YAGNESH D5
M8 GANESH D1
M9 RAGHU D1
M10 CHANTI D4
**********************************************************************
ashokit_emp >>>> Primary Key(emp_id), Foreign keys(dept_id,manager_id)
**********************************************************************
emp_id emp_name salary dept_id manager_id
------ -------- ------ ------- ----------
E1 Anil 18000 D7 M1
E2 Avinash 28000 D1 M2
E3 Raju 38000 D2 M3
E4 Raghavendra 48000 D4 M6
E5 Abdul 58000 D6 M5
E6 Gupta 68000 D1 M2
E7 Bhavya 78000 D7 M1
E8 SUNIL 88000 D11 M1
E9 ABHI 98000 D12 M2
***********************************************
ashokit_projects >>>>> Primary Key(Project_id)
***********************************************
Project_id project_name team_member_id
---------- ------------ --------------
P1 SPRING PROJECT E1
P1 SPRING PROJECT E2
P1 SPRING PROJECT M1
P2 BANKING PROJECT E3
P2 BANKING PROJECT E6
P2 BANKING PROJECT M2
P3 WEBSITE PROJECT M3
P3 WEBSITE PROJECT E5
************************
1) INNER JOIN/EQUI JOIN
************************
* In this type of Join will get only matched rows from left hand side table and right hand side based on join condition.
* Make sure need to have the common column between the tables is mandatory.
* We can specify the join condition in two ways
1) We can specify the join condition in where condition but it is not recommended because always where condition is used to add filters while seleting the data from table.
SYNTAX
======
select columnlist from <tables> where <Join Condition>;
Ex: select e.empno,e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
2) We can specify the join condition in "on" clause which is recommended in the real time application development
SYNTAX
======
select columnlist from <parent_Table_Name> join/inner join <child_table_name> on <Join Condition>;
Ex: select e.empno,e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
***************
Example Queries
***************
1) Display employee name and department name they belonging too using office database tables
SQL