Database basics

If you use any aggregate function, and also wants to display another column along size it, you must use "GROUP by" select char_length(city) as citynamesize,city from station group by city order by citynamesize DESC; 21 Marine On Saint Croix 18 West Baden Springs 17 Frankfort Heights 3 Roy 3 Lee SELECT count(CustomerID), CustomerName,City FROM Customers where CustomerName Like '%Ho%' Group by City ; If you run below querry, it will give 7 in count column, because it's illogical, you can find the people/row, whos names contains "Ho", but against what, you cant display 7|Hosa ? SELECT count(CustomerID), CustomerName FROM Customers where CustomerName Like '%Ho%'; So that's why it should be more meaningful, like display the names and count of people, whos name contains "Ho" per city, So against each city, you will find count of people. whos name starts with "Ho" but again displaying names doesn't make...

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...