I WILL ISSUE LOAN OR CREDIT CARD TO MY BANK CUSTOMERS ONLY BANK

IF YOU NEED A CREDIT CARD OR A LOAN , YOU MUST HAVE BANK ACCOUNT FIRST WITH MECUSTOMERS

CUST_ID CUST_NAME CREDIT DEBIT LOAN NUMBER LOAN AMOUNT

10 RAJ 10000 0 142535 500000
20 RAM 0 20000 1485201 200000
30 RAMYA 50000 10000 14582696 1000000
10 RAJ 0 0 0 0
20 RAM 0 0 258963252 700000

MAIN TABLE PARENT TABLE MASTER TABLE CHILD

CUST_ACC CUST_LOAN
PK FK REFERENCES TO CUST_ACC(CUS
CUST_ID CUST_NAME CREDIT DEBIT CUSTOMER_ID
10 RAJ 10000 0 10
20 RAM 50000 20000 20
30 RAMYA 70000 5000 10
40 DIVYA 8000 6000 20
50 ADHYA 9000 100 NULL
60

FK CHILD TABLE DETAIL TABLE

CREDIT CARD
CUSTOMER_ID CUST_NAME CREDITCARD N CVV EXPIRY DATE
10 RAJ 123456789 123 Nov-23
20 RAM 147852963 258 Dec-25
10 RAJ 369852147 147 Nov-24
20 RAM 258741963 125 Dec-26
40 DIVYA 852741963 475 Jan-27
50 ADHYA 447785296 458 Feb-26
70 SUMAN 258963217 148 Jan-26
 CUST.CUST_ID=LOANS=CUST_ID

CUST LOANS
CUST_ID CUST_NAME CREDIT DEBIT CUST_ID CUST_NAME
10 RAJ 10000 0 10 RAJ
20 RAM 0 20000 20 RAM
30 RAMYA 50000 10000 30 RAMYA

CUST_ID CUST_NAME CREDIT DEBIT LOAN NUMBER LOAN AMOUNT LOAN TYPE
10 RAJ 10000 0 142535 500000 CAR
20 RAM 0 20000 1485201 200000 PERSONAL
30 RAMYA 50000 10000 14582696 1000000 HOME
 LOAN TYPE CREDITCARD NUCVV EXPIRY DATE
CAR 1234567896 123 May-25
PERSONAL 147852852 456 Jun-24
HOME 14785269852 789 May-23
NA 741852963 258 Jan-24
HOME 259963257 256 May-25

DETAIL

FERENCES TO CUST_ACC(CUST_ID)
CUST_NAME LOAN NUMBER LOAN AMOUNT LOAN TYPE
RAJ 123456 5L CAR
RAM 456123 10L PERSONAL
RAJ 859147 50L HOME
RAM 369852 10L CAR
NULL 258741 15L PERSONAL
RAVI 147258 5L CAR
LOAN NUMBELOAN AMOUNT LOAN TYPE
142535 500000 CAR
1485201 200000 PERSONAL
14582696 1000000 HOME
 BANK

CUST_ID CUST_NAME CREDIT DEBIT LOAN NUMBER

10 RAJ 10000 0 142535
20 RAM 0 20000 1485201
30 RAMYA 50000 10000 14582696
10 RAJ 0 0 0
20 RAM 0 0 258963252

customers PARENT TABLE

PK FK
CUST_ID CUST_NAME CREDIT DEBIT CUSTOMER_ID
10 RAJ 10000 0 10
20 RAM 0 20000 20
30 RAMYA 50000 10000 30
40 kavya 60000 1000 10
50 ADHYA 70000 50000 20
NULL
I WILL ISSUE LOAN to my bank customers only
I will ISSUE credit card to my bank customers only
I want to see all the customers who are having loans customers.cust_id=loans.customer_id
creditcard child ttable
FK
C_ID CUST_NAME CREDITCARD NUCVV EXPIRY DATE
10 RAJ 1234567896 123 May-25
20 RAM 147852852 456 Jun-24
40 kavya 14785269852 789 May-23
10 RAJ 741852963 258 Jan-24
20 RAM 259963257 256 May-25
 CUSTOMERS

LOAN AMOUNT LOAN TYPE CREDITCARD NUCVV EXPIRY DATE

500000 CAR 1234567896 123 May-25
200000 PERSONAL 147852852 456 Jun-24
1000000 HOME 14785269852 789 May-23
0 NA 741852963 258 Jan-24
700000 HOME 259963257 256 May-25

loans CHILD TABLE

CUST_NAME LOAN NUMBER LOAN AMOUNT LOAN TYPE

RAJ 1040 1000000 CAR
RAM 2582 2000000 PERSONAL
RAMYA 1028 5000000 HOME
RAJ 1050 2500000 PERSONAL
RAM 2050 3000000 HOME
ADHYA 70000 50000 HOME

mers.cust_id=loans.customer_id
 WHATEVET THE VALUES ARE IN PK
FOREGIN KEY FK COLUMN CAN WE REPETATIVE

PARENT TABLE
MASTER TABLE
DATATYPE SHOULD BE SAME.
EMP
PRIMARY KEY
EMPID ENAME SALARY DEPTID
101 RAJ 10000 10
102 RAM 20000 20
103 RAMESH 30000 30
104 RAMYA 40000 40
105 RAJESH 50000 50
106 RAVI 60000 70
107 JOHN 7000 80
110 JOHN 80000 120

EMP_DEPT
I WANT TO SEE ALL THE DEPARTMENT NAMES OF ALL THE EMPLOYEES

EMPID ENAME SALARY DEPTID DEPARTMENT_ID DEPART_NAME LOCATION

101 RAJ 10000 10 10 SALES BANGALORE
102 RAM 20000 20 20 HR CHENNAI
103 RAMESH 30000 30 30 IT DELHI
104 RAMYA 40000 40 40 ADMIN MUMBAI
105 RAJESH 50000 50 50 OPERATIONS HYDERABAD
106 RAVI 60000 70 70 R&d DELHI
107 JOHN 7000 80 80 ABC KOLKAKA
101 RAJ 10000 10 10 SALES BANGALORE
102 RAM 20000 20 20 HR CHENNAI
PK
EMPID ENAME SALARY
101 RAJ 10000
102 RAM 20000
103 RAMESH 30000
104 RAMYA 40000
105 RAJESH 50000
106 RAVI 60000
107 JOHN 7000
EMPID ENAME SALARY DEPTID DEPTNAME
101 RAJ 10000 10 SALES
102 RAM 20000 20 HR
103 RAMESH 30000 30 IT
104 RAMYA 40000 40 ADMIN
105 RAJESH 50000 50 OPERATIONS
106 RAVI 60000 60 R&d
107 JOHN 7000 70 ABC

EMP.DEPT_ID=DEPT.DEPT_ID

EMP
EMPID ENAME SALARY DEPTID
101 RAJ 10000 10
102 RAM 20000 20
103 RAMESH NULL 30
104 NULL 40000 40
105 RAJESH 50000 50
106 RAVI 60000 60
107 JOHN 7000 70
108 ADHYA NULL NULL
108 ADHYA NULL 10

SELECT A.* , B.* FROM EMP A , DEPT B

WHERE A.EMP_ID=B.EMP_ID
AND A.DEPT_ID=B.DEPT_ID

EMPID ENAME SALARY DEPTID DEPTID_1

101 RAJ 10000 10 10
102 RAM 20000 20 20
103 RAMESH NULL 30 30
104 NULL 40000 40 40
107 JOHN 7000 70 70
108 ADHYA NULL 10 10
TARGET TABLE IN INFORMMATICA

EMP_TGT

EMP_ID
ENAME
SALARY
DEPT_ID PK
EMP
EMPID ENAME SALARY DEPTID
101 RAJ 10000 10
102 RAM 20000 20
103 RAMESH NULL 30
104 NULL 40000 40
105 RAJESH 50000 50
106 RAVI 60000 60
107 JOHN 7000 70
108 ADHYA NULL NULL
108 ADHYA NULL 10

SELECT EMP.EMP_ID , EMP.ENAME , EMP.SALARY , EMP.DEPT_ID, DEPT.DEPARTMENT_ID , DEPT.DNAME

FROM EMP , DEPT
WHERE EMP.DEPTID=DEPT.DEPARTMENT_ID

EMPID ENAME SALARY DEPTID DEPTID

HATEVET THE VALUES ARE IN PK , ONLY THOSE VALUES SHOULD HAVE IN FOREIGN KEY COULUMN
COLUMN CAN WE REPETATIVE

CHILD TABLE
DETAIL TALBE
TATYPE SHOULD BE SAME.
DEPT
FOREIGN KEY REFERENCES TO EMP.DEPT_ID
DEPARTMENT_ID DEPART_NAME LOCATION
10 SALES BANGALORE
20 HR CHENNAI
30 IT DELHI
40 ADMIN MUMBAI
50 OPERATIONS HYDERABAD
70 R&d DELHI
80 ABC KOLKAKA
100 ABC KOLKATA
60 ABC BANGALORE
20 HR CHENNAI
10 SALES BANGALORE
null null null

DEPARTMENT NAMES FOR ALL MY EMPLOYEES

MANAGER_ID
102
103
104
105
106
101
101
LOCATION
BANGALORE
CHENNAI
DELHI
MUMBAI
HYDERABAD
DELHI
KOLKAKA

DEPT
DEPTID DEPTNAME LOCATION EMP_ID
10 SALES BANGALORE 101
20 HR CHENNAI 102
30 IT DELHI 103
40 ADMIN NULL 104
50 NULL HYDERABAD 110
60 R&d DELHI 120
70 ABC KOLKAKA 107
10 SALES BANGALORE 108
30 IT DELHI 108
NULL ABC KOLKAKA 108

DEPTNAME LOCATION EMP_ID_1

SALES BANGALORE 101
HR CHENNAI 102
IT DELHI 103
ADMIN NULL 104
ABC KOLKAKA 107
SALES BANGALORE 108
DEPT_TGT

DEPARTMENT_ID FK
DEPT_NAME
LOACTION
 DEPT
DEPARTMENT_ID DEPTNAME LOCATION
10 SALES BANGALORE
20 HR CHENNAI
30 IT DELHI
40 ADMIN NULL
50 NULL HYDERABAD
60 R&d DELHI
70 ABC KOLKAKA
10 SALES BANGALORE
30 IT DELHI
NULL ABC KOLKAKA

DEPARTMENT_ID , DEPT.DNAME , DEPT.LOCATION, DEPT.LOCATION_ID

DEPTNAME LOCATION EMP_ID

LOCATIONS

PRIMARY KEY
LOCATION_ID LOCATION_NAME CITY
1001 JAYANAGAR BANGALORE
1002 GANDHI NAGAR HYDERBAD
1003 ANNA NAGAR CHENNAI
1004 MG ROAD MUMBAI
1005 ABC ROAD KOCHI
 WHATEVET THE VALUES ARE IN PK , ONLY THOSE VALUES SHOULD HAVE IN FOREIGN K
FOREGIN KEY FK COLUMN CAN WE REPETATIVE

PARENT TABLE CHILD TABLE

MASTER TABLE DETAIL TALBE
DATATYPES SHOULD MATCH
EMP DEPT
PRIMARY KEY FOREIGN KEY REFERENCES TO EMPLOYEE.DEPT_ID
EMPID ENAME SALARY DEPTID DEPARTMENT_ID
101 RAJ 10000 10 10
102 RAM 20000 20 20
103 RAMESH 30000 30 30
104 RAMYA 40000 40 40
105 RAJESH 50000 50 50
106 RAVI 60000 70 70
107 JOHN 7000 80 80

PK- DEPTID PK-LOCATION_ID

I want TO SEE all the employees and their department names

EMPID ENAME SALARY DEPTID DEPART_NAME

101 RAJ 10000 10 SALES
102 RAM 20000 20 HR
103 RAMESH 30000 30 IT
104 RAMYA 40000 40 ADMIN
105 RAJESH 50000 50 OPERATIONS
106 RAVI 60000 70 R&d
107 JOHN 7000 80 ABC
 PRIMARY KEY FOREIGN KEY REFERENCES TO EMPLOYEE.DEPT_ID
EMPID ENAME SALARY DEPTID DEPARTMENT_ID
101 RAJ 10000 10 10
102 RAM 20000 20 20
103 RAMESH 30000 30 30
104 RAMYA 40000 40 40
105 RAJESH 50000 50 50
106 RAVI 60000 70 70
107 JOHN 7000 80 80
10

EMP_ID ENAME DEPT_ID DEPART_NAME

101 RAJ 10 SALES
102 RAM 20 HR
103 RAMESH 30 IT
104 RAMYA 40 ADMIN
105 RAJESH 50 OPERATIONS
106 RAVI 70 R&d
107 JOHN 80 ABC
101 RAJ 10 SALES
SE VALUES SHOULD HAVE IN FOREIGN KEY COULUMN

LOC
KEY REFERENCES TO EMPLOYEE.DEPT_ID PRIMARY KEY FK REFERENCES TO DEPT.LOCATION_ID
DEPART_NAME LOCATION_naLOCATION_ID LOC_ID LOC ADDRESS country
SALES BANGALORE 101 101 ABC CNTRY1
HR CHENNAI 102 102 DEF CTRY2
IT DELHI 103 103 HIJ CNTRY3
ADMIN MUMBAI 104 100 ABC CNTRY
OPERATIONS HYDERABAD 105 108 KIL CNTRY5
R&d DELHI 108 109 JUK CNTRY6
ABC KOLKAKA 109
KEY REFERENCES TO EMPLOYEE.DEPT_ID PRIMARY KEY
DEPART_NAME LOCATION LOCATION_ID
SALES BANGALORE 101
HR CHENNAI 102
IT DELHI 103
ADMIN MUMBAI 104
OPERATIONS HYDERABAD 105
R&d DELHI 108
ABC KOLKAKA 109
SALES BANGALROE 100
NOT ALLOW
 VARHCAR CAN ACCEPTS CHARACHETERS AND NUMBERS
NUMBERS WILL NOT ACCEPT CHARACHETERS A PRIMARY KEY DEFINED ON EMPID , ENAME
NUMBER VARCHAR NUMBER NUMBER COMBINATION OF EMP_ID ,ENAME SHOULD BE UN

NOT NULL NOT NULL PK

EMPID ENAME SALARY DEPTID EMPID ENAME SALARY
10 RAJ 1000 10 10 RAJ 1000
20 RAJ NULL NULL 10 RAM 2000
20 RAJ NULL NULL 10 DIVYA 3000
NULL RAM 10000 20 20 RAJ 4000
40 NULL 5000 50 20 RAM 5000
10 RAJ 6000
10 RAM 7000
NULL RAM 8000
10 NULL 9000

PK
EMPID ENAME SALARY
UNIQUE UNIQUE 10 RAJ 10000
EMPID ENAME SALARY DEPTID 20 RAJ 10000
NULL NULL NULL NULL 10 RAJ 20000
10 RAJ 1000 10 10 RAM 10000
20 RAM 2000 20 20 RAM 5000
10 RAMYA 2000 30 NULL RAMESH 6000
40 RAJ 5000 60 50 NULL 500000
100 RAJESG 6000 20 60 RAMYA NULL

UNIQUE UNIQUE NOT NULL 60%

NOTNULL
EMPID ENAME SALARY DEPTID
10 RAJ 1000 200
20 NULL 1000 300
20 RAM 5000 50
NULL RAMESH 6000 60
40 RAJ 5000 70
50 RAMYA NULL 80
60 NULL 8000 200
70 DIVYA 9000 NULL
80 NULL 95000 60
NED ON EMPID , ENAME
MP_ID ,ENAME SHOULD BE UNIQUE
NUMBER
PK
DEPTID EMPID ENAME SALARY DEPTID
1 10 RAJ 1000 1
2 10 RAJ 2000 2
3 10 RAM 1000 1
4 20 RAJ 1000 3
5 10 RAM 1000 5
6 NULL RAJESH 5000 6
7 70 NULL 6000 7
8 80 RAMYA NULL 8
9

VARCHAR2
PK
DEPTID EMPID ENAME SALARY DEPTID
100 10 ' 1000 100
200 20 0 1000 100
300 30 NULL 3000 400
500 40 RAJ 5000 500
500 50 RAJ 6000 500
5000
6000
70000
 MAIN TABLE PARENT TABLE MASTER TABLE CHILD
CUST_ACC CUST_LOAN
PK FK REFERENCES TO CUST_ACC(CUST
CUST_ID CUST_NAME CREDIT DEBIT CUSTOMER_ID
10 RAJ 10000 0 10
20 RAM 50000 20000 20
30 RAMYA 70000 5000 10
40 DIVYA 8000 6000 20
50 ADHYA 9000 100 NULL
60

FK CHILD TABLE DETAIL TABLE

CREDIT CARD
CUSTOMECREDITCARD NUMBER CVV EXPIRY DATE
10 123456789 123 Nov-23
20 147852963 258 Dec-25
10 369852147 147 Nov-24
20 258741963 125 Dec-26
40 852741963 475 Jan-27
50 447785296 458 Feb-26
70 258963217 148 Jan-26
 DETAIL

REFERENCES TO CUST_ACC(CUST_ID)
LOAN NUMBER
123456
456123
859147
369852
258741
147258

I WANT TO SEE
 CUSTOMERS

CUST_ID CUST_NAME CREDIT

10 RAJ 10000
20 RAM 0
30 RAMYA 50000
10 RAJ 0
20 RAM 0
500 COLUMNS

DEBIT LOAN NUMBER LOAN AMOUNT LOAN TYPE CREDITCARD NUMBE

0 142535 500000 CAR 1234567896
20000 1485201 200000 PERSONAL 147852852
10000 14582696 1000000 HOME 14785269852
0 0 0 NA 741852963
0 258963252 700000 HOME 259963257
CVV
123
456
789
258
256
 VACHAR2 WILL ACCEPT CHARACTHERS AND ALSO NUMBER
NUMBER WILL ONLY ACCEPT NUMBERS

PK not null
st_id st_name marks st_id st_name marks
10 RAJ 100 10 RAJ 100
20 RAM 200 NULL RAM 200
NULL RAJESH 300 20 NULL 3200
10 RAM 100 30 RAJ NULL
 unique
st_id st_name marks
10 RAJ 100
20 NULL 100
30 RAJ 100
NULL NULL 100
 PRIMARY KEY PRIMARY KEY
EMPNO ENAME SALARY LOCATION EMPNO ENAME
10 RAJ 1000 BANGALORE 10 RAJ
10 RAM 1000 BANGALORE 10 RAJ
10 RAJ 2000 CHENNAI 10 ram
20 RAJ 10000 BANGALORE 10 RAJ
30 RAM 20000 CHENNAI 20 RAM
NULL RAJ 10000 BANGALORE NULL RAM
RAJ NULL 2000 BANGALORE 10 NULL
10 RAM
10 RAMESH
PRIMARY KEY A PK CONTANIS 3 COULMS
SALARY LOCATION CANDIDATE KEY
10000 BANGALORE NATURAL
20000 BANGALORE COMPOSIT KEY
20000 CHENNAI
10000 BANGALORE
20000 CHENNAI
10 BANGALORE
10 CHENNAI
NULL BAN
30000 NULL
 PRIMARY KEY
ST_ID ST_NAME MARKS CITY
10 RAJ 100 BANGALORE
10 RAM 200 CHENNAI
20 Raj 300 Goa
20 Ram 400 kolkata
30 Ramya null null
10 Null 500 Kochi
null Sai 600 Mysore
40 Adhya 100 BANGALORE

````
 WHATEVET THE VALUES ARE IN PK , O
FOREGIN KEY FK COLUMN CAN WE REPETATIVE

PARENT TABLE
MASTER TABLE
DATATYPE AND SIZE SHOULD BE
EMP
PRIMARY KEY
EMPID ENAME SALARY DEPTID
101 RAJ 10000 10
102 RAM 20000 20
103 RAMESH 30000 30
104 RAMYA 40000 40
105 RAJESH 50000 50
106 RAVI 60000 70
107 JOHN 7000 80

EMP_ID ENAME SALARY DEPT_ID DEPARTNAME LOCATION

101 RAJ 10000 10 SALES BANGALORE
PK FK
EMPID ENAME SALARY MANAGER_ID
101 RAJ 10000 102
102 RAM 20000 103
103 RAMESH 30000 104
104 RAMYA 40000 105
105 RAJESH 50000 106
106 RAVI 60000 101
107 JOHN 7000 101
HE VALUES ARE IN PK , ONLY THOSE VALUES SHOULD HAVE IN FOREIGN KEY COULUMN
AN WE REPETATIVE

CHILD TABLE
DETAIL TALBE
D SIZE SHOULD BE
DEPT
FOREIGN KEY REFERENCES TO EMP.DEPT_ID
DEPARTMENT_ID DEPART_NAME LOCATION
10 SALES BANGALORE
MANAGER_ID
LOCATIONS

PRIMARY KEY
LOCATION_ID LOCATION_NAME CITY
1001 JAYANAGAR BANGALORE
1002 GANDHI NAGAR HYDERBAD
1003 ANNA NAGAR CHENNAI
1004 MG ROAD MUMBAI
1005 ABC ROAD KOCHI
THE COMBINATION OF ST_ID , ST_NAME SHOULD BE UNIQUE
BUT NULLS SHOULD NOT PRESENT IN BOTH THE COLUMNS

PRIMARY KEY
st_id st_name marks city
10 RAJ 100 BANGALORE
20 RAM 200 CHENNAI
10 RAMYA 300 BANGALORE
10 RAM 300 BANGALORE
20 RAJ 300 BANGALORE
NULL DIVYA 400 BANGALORE
30 NULL 400 BANGALORE
OULD BE UNIQUE
THE COLUMNS
 PRIMARY KEY
st_id st_name city marks
10 RAJ BANGALORE 100
10 RAJ CHENANI 200
10 RAM CHENANI 300
10 RAJ BANGALORE 400
NULL RAJ CHENNAI 500
10 NULL BANGALORE 600
10 RAJ NULL 400

ST_ID M
PK
st_id st_name city marks
10 RAJ BANGALORE 100
10 RAJ CHENNAI 300
10 RAM CHENNAI 400
10 RAJ MUMBAI 400
NULL RAJ KOCHI 500
30 NULL GOA 600
40 RAMYA NULL 700
10 RAJ BANGALORE 500
 WHATEVER THE VALUES ARE IN PK , ONLY THOSE VALU
FOREGIN KEY FK COLUMN CAN WE REPETATIVE VALUES AND ALSO N

PARENT TABLE
MASTER TABLE
DATATYPE AND SIZE SHOULD BE SAME
EMP
PRIMARY KEY
EMPID ENAME SALARY DEPTID
101 RAJ 10000 10
102 RAM 20000 20
103 RAMESH 30000 30
104 RAMYA 40000 40
105 RAJESH 50000 50
106 RAVI 60000 70
107 JOHN 7000 80
 emp_tgt dept_tgt

empno not null department_id

ename deptname
salary loaction
dept_id pk
 ARE IN PK , ONLY THOSE VALUES SHOULD HAVE IN FOREIGN KEY COULUMN
PETATIVE VALUES AND ALSO NULLS

CHILD TABLE
DETAIL TALBE
OULD BE SAME
DEPT
FOREIGN KEY REFERENCES TO EMP.DEPT_ID
DEPARTMENT_ID DEPART_NAME LOCATION
10 SALES BANGALORE
20 HR CHENNAI
30 IT DELHI
40 ADMIN MUMBAI
50 OPERATIONS HYDERABAD
10 SALES BANGALORE
10 SOFTWARE GOA
70 R&d DELHI
80 ABC KOLKAKA
90 ABC DEF
10 SALES BANGALORE
100 DEF CHENNAI
NULL SALES BNAGLORE

LOC
FK REFERENCES TO DEPT.LOCATION_ID
LOC_ID LOC ADDRESS COUNTRY
101 ABC CNTRY1
102 DEF CTRY2
103 HIJ CNTRY3
100 ABC CNTRY
108 KIL CNTRY5
109 JUK CNTRY6
fk

not null
LOCATIONS

PRIMARY KEY
LOCATION_ID LOCATION_NAME CITY
1001 JAYANAGAR BANGALORE
1002 GANDHI NAGAR HYDERBAD
1003 ANNA NAGAR CHENNAI
1004 MG ROAD MUMBAI
1005 ABC ROAD KOCHI