What is one-to-many relationship data structure?

-



 One to Many Relationships:

Each record in the Table A is associated with the multiple records in the Table B but each and every record of the table B is associated with only one record of Table A.Foreign key is used in the Table B and primary key is used in the Table A.It is type of relationships which is used for joining work or task. It is used as to store the information in the relational database.Sometimes,Table A is called as the parent table whereas Table B is called as the child table. It is also called as hierarchical relationship. The data should be structured in such way which clearly define the relationships between the entities like primary table and secondary table. Normalization should be applied to avoid the redundant data for an efficient querying system.

One class has high number of students to attend for learning purpose is an example of this.

Primary and Foreign keys:

One side of the relationship must have the primary key which uniquely identifies each record. Many sides of the relationship must have the foreign key which references the primary key of one side. For an example, create the two tables i.e. employees and department table. Indexes should be created on the foreign key column.

PRATICAL QUERIES:

Table departments is created with columns department_id and department_name.Primary key is applied on the department_id.There is no need to mention NOT NULL, if primary key is mentioned after datatype.

create table departments (department_id number primary key,department_name varchar2(20));




desc departments;

Here,department_id is automatically showing NOT NULL, even though NOT NULL is not used at the time of table creation. This is due to primary key is used.

INSERT ALL
INTO departments(department_id ,department_name) VALUES (10,'IT')
INTO departments(department_id ,department_name) VALUES (20,'RETAIL')
INTO departments(department_id ,department_name) VALUES (30,'FINANCE')
select * from dual;

Three records have been inserted into the department table.

It will display all the three records which have been inserted into the departments table.

create table employees (employee_id NUMBER PRIMARY KEY,employee_name varchar2(20),
department_id NUMBER, FOREIGN KEY (department_id) REFERENCES departments(department_id));

Table employees is created with columns employee_id,employee_name and department_id. Primary key is applied on the employee_id.On the department_id,foreign key is used.

INSERT ALL
INTO employees(employee_id ,employee_name,department_id) VALUES (1,'Rahul',10)
INTO employees(employee_id ,employee_name,department_id) VALUES (2,'Rishav',20)
INTO employees(employee_id ,employee_name,department_id) VALUES (3,'Roshan',30)
INTO employees(employee_id ,employee_name,department_id) VALUES (4,'Ganesh',30)
INTO employees(employee_id ,employee_name,department_id) VALUES (5,'Seema',30)
INTO employees(employee_id ,employee_name,department_id) VALUES (6,'Mani',20)
INTO employees(employee_id ,employee_name,department_id) VALUES (7,'Mohan',20)
INTO employees(employee_id ,employee_name,department_id) VALUES (8,'Shiv',10)
INTO employees(employee_id ,employee_name,department_id) VALUES (9,'Radha',10)
select * from dual;

Nine records have been inserted into the employees table with duplicate department_id 10,20 and 30.
select * from employees;




select e.employee_name,d.department_name
FROM employees e 
JOIN departments d
on e.department_id=d.department_id
order by d.department_name;

This query is joining the employees table with the departments table using the department_id as the foreign key and display the list of employee names along with their respective department names.




Comments

Post a Comment

Popular posts from this blog

Difference between union and union all in oracle sql plsql

-
Image
"This is one of the most important questions that interviewer might be asking in an interview.Infyosis,Linked group,Kyndryl,Vodafone Idea, IBM and Mphasis etc." INTRODUCTION: It is said to be set operators which is used to combine, add or concatenate two or more result sets. It is used in the multiple select statements where to combine to produce the final desired results. In other words, multiple select statements of similar column name in same order and having same data type to produce the unified result. It merges the content of the two structurally compatible organized tables into single table.  In MIS datalake,when you are adding the modules of GST reconciliation which is to be used in micros in Excel or VB script, union and union all set operator is preferred for eliminating duplicate records or keeping duplicate records as per business requirement. There are several alternatives like bulk collect,join,set operators like intersect & minus for combining the data from...
Read more

Explain oracle pl sql datatypes interview question and answer for fresher and experienced.

-
Image
 Oracle PLSQL Data Types : PLSQL constant,value,variable,parameter,function which return the value are having the data type. It will determine the storage space information occupied and occurred in the memory or storage format and range constraints for valid values and operations as per specified size limit. It will determine how the data will be going to stored, handled and treated by the oracle engine during the period of processing and storage. It is used in the PLSQL blocks. It allows you to define your sub-types. PLSQL codes are embedded into the java program. Subtypes are used to make the data types compatible with data types used in the PLSQL program while embedding the PLSQL code into the java program. Subtype will be compatible with ANSI/ISO & IBM. It provides the pre-defined data types. Pre-defined data types are categorized into the four types: Composite LOB Reference Scalar Scalar Datatypes: It stores the values without the internal components. It holds the single ...
Read more

Difference between full join and cross join-oracle sql plsql

-
Image
 Difference between full join and cross join in sql:- They behave differently to combine the rows from the two tables. FULL OUTER JOIN: It is also called as the full join .It will return all the rows when there is either match in the left table or right table. If there is no match, it will return NULL for the rows of the columns which is lacking the matching rows. Syntax: select c.*,d.*  from tableC c FULL OUTER JOIN tableD d on c.id=d.id; select e.ename,d.dname from emp e FULL OUTER JOIN dept d ON e.deptno=d.deptno; When you want to retain the unmatched rows i.e operations which is not present in the emp table and having deptno as 40.So,ename is appeared as NULL. CROSS JOIN: It returns the cartesian product of both the tables i.e. Every row of the table A is combined with every row of the table B.ON condition is not used due to not match rows. Syntax: select c.*.d.* from tableC c CROSS JOIN tableD d; select e.ename,d.dname from emp e CROSS JOIN dept d; ENAME ...
Read more