Click Here to ask SQL Question
Basic Sql Query
Group by query in sql
Group by query is used to group row that have the same values.
This is basically is used with aggregate function (Max(), Min(), Sun(), Avg())
Example 1: Get number of employees in each country
Example 2: Get number of student in each class
Ex-1: Suppose we have table Employee and it's columns are EmployeeID, Country,
Then group by query would be like this
SELECT COUNT(EmployeeID), Country
FROM Employee
GROUP BY Country;
Ex-2: Suppose we have table Item and it's cloumns are ItemName, Quantity
Then group by query would be like this
SELECT ItemName, SUM(Quantity) AS "Total quantity"
FROM Item
GROUP BY ItemName;
Sql Keys:
- Primary Key : Never allow null value
- Unique Key : Allow 1 null value
- Foreign Key
- Composite Key
- Super Key
- Candidate Key : Including Primary Key
- Alternate Key : Excluding primanry key
d/f b/w union and join in mysql
ReplyDeleteJOIN combines data horizontally, by adding a new set of rows adjacent to the existing set. The horizontal new set of rows have same number of rows but can have different number of columns.
DeleteFor example: Employee & Department table, to list employees from all Departments you needs a JOIN, like:
SELECT
D.DeptID,
D.DeptName,
E.EmployeeID,
E.EmployeeName,
E.DOB,
E.Gender,
E.DOJ
FROM Department D
JOIN Employee E
ON E.DeptID = D.DeptID
UNION combines data vertically, by adding a new set of rows just below to the existing set. The vertical new set of rows have same number of columns but can have different number of rows.
For example: Employee & Customers table, to list all people in one list with their common details, like:
SELECT
EmpID as ID,
EmpName as Name
FROM Employee
UNION
SELECT
CustID as ID,
CustName as Name
FROM Customer
Please note that with UNION, the column names should be same stacked together.
Hope this make sense.
Thanks
joins is used to combine data into new columns
ReplyDeleteUnions is used to combine data into new rows