Database basics

EXIST https://www.youtube.com/watch?v=HM6Jc5YGubQ here outer query shows the result only of inner query returns atleast one row/tuple, in above example join was not required as we wanted result set only from one table, if we want our result to have column from both the table, then we  require joins. Question : PROFESSION and STUDENT table provide name of al the profession, who is advisor to atleast one female student select P.Name from profession as P    EXIST ( select rollnumber from student as S                    where P.id = s.advisorId                     and s.sex ='F') *SET COMPARASION OPERATION LIKE*            IN(Item1,....)            ANY(Item1,...)            ALL(Item1,....)

https://www.youtube.com/watch?v=SM9cDMxAeK4 Where inner query will be executed for each row of outer query , Difference between sub Query/Inner query and correlated nested sub-query  Lest see by real example EMPLOYEE table EmployeeId NAME Salary DepartmentId 1 Sandeep 2 2 2 3 1 4 6 Requirement 1: FIND EMPLOYEE NAME, WHOSE SALARY IS GREATER THAN THE AVG SALARY OF ALL THE EMPLOYEE Requirement 2: FIND EMPLOYEE NAME, WHOSE SALARY IS GREATER THAN THE AVG SALARY OF HIS DEPARTMENT. QUERY for Requirement-1 select NAME from EMPLOYEE where salary > (select avg(salary) from EMPLOYEE). Here the inner query is executed only once, resulted in average salary from all the employee, and then outer query is executed once. QUERY for Requirement -2 select ename from EMPLOYEE AS E  wh...

Basics Employee  Empid  Name  courseid  1  Sandeep  10  2  Deepak  20  3  Dharmu Course  id  courseName  duration  10  mysql  1.5  20  java programming  2  30  String programming  1 Inner Join or Simply Join : select * from Employee JOIN Course ON Employee.courseid = Course.id; Output: this will first internally do Cartesian product from each row of left table to each row in right table, then applies the filter , to match row, which has same courseid and id. So this will be the output, next all joins will add few more rows on this output.   Empid   Name   courseid   id   cou...