Basics of SQL Part 5

-

 SQL Union


- Used to combine the result set of two or more select statements removing duplicates.


- Each select statement with the union must have the same number of columns.


- The selected columns must be of similar data types and must be in the same order in each select statement.


- More than 2 queries can be clubbed.


Syntax: select column name from table 1

union

select column name from table 2;


Sample: select product _name from items 1

union

select product _name from items 2;


SQL Union All


- Used to combine the results of two select statements including duplicate rows.


- The same rules that apply to the union clause will apply to the union all operator.


Syntax: select column name from table 1

union all

select column name from table 2;


Sample: select product _name from items 1

union all

select product _name from items 2;


SQL Joins


Combine rows/columns from two or more tables, based on a related column between them in a database.


SQL Inner Join:


The inner join creates a new result table by combining column values of two tables based upon the join predicate.

The query compares each row of table 1 with each row of table 2 to find all pairs of rows which satisfy the join predicate.


Syntax: select table1.col1, table2.col2, .....

               from table1

               inner join table2

               on table1.commonfiled=table2.commonfiled;


Sample: select e.emp_id, e.first_name, e.last_name, d.dept_id, d.dept_name

                from emp e

                inner join dept d

                on e.dept_id=d.dept_id;


SQL Left Join:


The left join returns all the values from the left table, plus matched values from the right table or null in case of no matching join predicate.


Syntax: select table1.col1, table2.col2

                from table1

                left join table2

                on table1.commonfiled=table2.commonfiled;


Sample: select e.emp_id, e.first_name, e.last_name, d.dept_id, d.dept_name

                from emp e

                left outer join dept d

                on e.dept.id=d.dept_id;


SQL Right Join


The right joins returns all the values from the right table, plus matched values from the left table or null in case of no matching join predicate.


Syntax: select table1.col1, table2.col2

                from table1

                right join table2

                on table1.commonfiled=table2.commonfiled;


Sample: select e.emp_id, e.first_name, e.last_name, d.dept_id, d.dept_name

                from emp e

                right join dept d

                on e.dept.id=d.dept_id;


#data #sql #database #day5


Mail id: arpitmishra1629.ftp@gmail.com

Contact no: 8127430702

Linkedin: https://www.linkedin.com/in/arpit-mishra-9319b5131



Comments

Post a Comment

Popular posts from this blog

The curriculum of our current education system

-
Image
                                        The curriculum of our current education system Today's blog is based on " The curriculum of our current education system ". I wholeheartedly agree that the tight curriculum of our current education system leaves no room for imagination and creativity. The academic pressure is so much on them, they have forgotten their innate creativity and imagination. They are always under the pressure of studies. This pressure is put on them from the beginning of the session. Formative assessment activities of all the subjects, studies and their assignments of all the subjects, unit tests, co-curricular activities, homework, classwork, projects, practical exams, summative examination, date sheet, result, again the new term, etc. So much of pressure on the young growing children! Are we educating them or training them in the art of being stressful throughou...
Read more

Basics of SQL Part 7

-
Image
 Triggers in SQL A trigger is a special type of stored procedure that runs automatically when an event occurs in the database server.  Types of triggers in SQL - Data Manipulation trigger - Data Definition trigger - Logon trigger How to use before insert trigger?   Trigger Command: delimiter //                                create trigger trigger_name()                                 before insert on table_name()                                 for each row                                 if new.marks < 0 then set new.marks =50;                                  end if ...
Read more

National Cadet Corps

-
Image
National Cadet Corps Welcome to my blog and thank you for this much support. So let's start. Today's blog is based on the " National Cadet Corps ".  The  National Cadet Corps  is the youth wing of Armed Forces with its Headquarters at New Delhi, Delhi, India. It is open to school and college students voluntarily. National Cadet Corps is a Tri-Services Organization, comprising the Army, Navy and Air Wing, engaged in grooming the youth of the country into disciplined and patriotic citizens. The National Cadet Corps in India is a voluntary organization which recruits cadets from high schools, higher secondary, colleges and universities all over India. The Cadets are given basic military training in small arms and parades. The officers and cadets have no liability for active military service once they complete their course. The NCC is the  largest uniformed youth organization . Its motto is 'Unity and Discipline'. The NCC in India has f...
Read more