32% Implements Joins and Aggregate Functions Flashcards
What is the ORDER BY clause?
ORDER BY clause orders selected rows by one or more columns in ascending(alphabetic or increasing) order
DESC keyword with the ORDER BY clause orders rows in descending order.
EXAMPLES:
– Order by Language (ascending)
SELECT *
FROM CountryLanguage
ORDER BY Language;
– Order by Language (descending)
SELECT *
FROM CountryLanguage
ORDER BY Language DESC;
– Order by CountryCode, then Language
SELECT *
FROM CountryLanguage
ORDER BY CountryCode, Language;
What is the DISTINCT clause?
DISTINCT clause is used with a SELECT statement to return only unique or ‘distinct’ values.
EXAMPLE:
SELECT Language
FROM CountryLanguage
WHERE IsOfficial = ‘F’;
This SELECT statement returns only unique languages:
SELECT DISTINCT Language
FROM CountryLanguage
WHERE IsOfficial = ‘F’;
What is HAVING Clause?
HAVING clause is used with the GROUP BY clause to filter group results. The optional HAVING clause follows the GROUP BY clause and precedes the optional ORDER BY clause.
EXAMPLE:
SELECT CountryCode, SUM(Population)
FROM City
GROUP BY CountryCode
HAVING SUM(Population) > 2300000;
EXPLANATION: Although the GROUP BY clause creates two groups based on CountryCode, the HAVING clause selects only the group with a population sum > 2,300,000
What is the GROUP BY clause?
GROUP BY clause consists of the GROUP BY keyword and one or more columns.
EXAMPLE:
SELECT CountryCode, SUM(Population)
FROM City
GROUP BY CountryCode;
The GROUP BY clause forms groups based on the CountryCode column.
What is COUNT() function?
COUNT() counts the number of rows in the set.
EXAMPLE:
SELECT COUNT(*)
FROM Employee
WHERE Bonus > 500;
COUNT() counts how many rows in the Employee Table where Bonus is more than 500
What is MIN() function?
MIN() finds the minimum value in the set.
EXAMPLE:
SELECT MIN(Salary)
FROM Employee;
MIN() finds the smallest value in the Salary column. MIN ignores NULL values.
What is MAX() function?
MAX() finds the maximum value in the set.
EXAMPLE:
SELECT MAX(Salary)
FROM Employee;
MAX() finds the maximum value in the Salary column. MAX ignores NULL values.
What is AVG() function?
AVG() computes the arithmetic mean of all the values in the set.
EXAMPLE:
SELECT AVG(Salary)
FROM Employee;
AVG() finds the average of the Salary column.
What is the SUM()function?
SUM() sums all the values in the set
EXAMPLE:
SELECT CountryCode, SUM(Population)
FROM City
GROUP BY CountryCode;
EXPLANATION:
The SUM() function sums the Population values in each group.
SUM(Salary) + SUM(Bonus) is not equal to SUM(Salary + Bonus).
SUM(Salary) + SUM(Bonus) is calculating all rows in Salary Column first, then calculating all rows in Bonus Column and then adding those two together.
SUM(Salary + Bonus)is calculating Salary+Bonus for each row then adding those together.
What is a JOIN?
JOIN is a SELECT statement that combines data from two tables, known as the left table and right table, into a single result.
EXAMPLE:
SELECT DepartmentName, EmployeeName
explanation-(The join query displays department names and managers)
Department is the left table. Employee is the right table
FROM Department, Employee
WHERE Manager = ID;
The query selects rows for which the foreign key Manager equals the primary key ID.
The FROM clause specifies the left table.
The INNER JOIN or FULL JOIN clause specifies the right table.
The ON clause specifies the join columns.
What is a INNER JOIN?
INNER JOIN selects only matching left and right table rows.
EXAMPLE:
SELECT Department.Name AS Group,
Employee.Name AS Supervisor
FROM Department
INNER JOIN Employee
ON Manager = ID;
EXPLANATION:
Inner joins are written with the keywords INNER JOIN. Duplicate columns Name are replaced with aliases Group and Supervisor.
What is a LEFT JOIN?
Left joins are written with the LEFT JOIN keywords.
EXAMPLE:
SELECT Department.Name AS Group,
Employee.Name AS Supervisor
FROM Department
Employee
ON Manager = ID;
What is a FULL JOIN?
FULL JOIN selects all left and right table rows, regardless of match.
In a FULL JOIN result table, unmatched left table rows appear with NULL values in right table columns, and vice versa.
Full joins are written with the FULL JOIN keywords. Unmatched rows from both tables appear in the result.
The full join selects all rows from both tables, including rows that do not match.
What is a RIGHT JOIN?
Right joins are written with the RIGHT JOIN keywords.
EXAMPLE:
SELECT Department.Name AS Group,
Employee.Name AS Supervisor
FROM Department
Employee
ON Manager = ID;
