What do you know about commands for calculating field values?
SELECT MAX (seats) FROM buses // Output the bus with the maximum number of seats
SELECT MIN (seats) FROM buses // Displays the bus with the minimum number of seats
SELECT SUM (seats) FROM buses // Output the total number of seats in all buses
SELECT AVG (seats) FROM buses // Output the average number of seats
SELECT COUNT (*) FROM buses // Output the total number of buses in the table
SELECT COUNT (*) FROM buses WHERE brand = “LAZ” // Displays the number of LAZ buses
Suppose we have a table in which there are name and id fields. You need to display the name with the largest id, without using the MAX command. How can I do that?
Sort by id in the decreasing direction, but print only the first id. He will be the most chime.
SELECT name, id FROM customers ORDER BY id DESC LIMIT 1
We do not remember exactly how to spell “Mercedes” or “Mercedes”, but you need to select from the table the buses of this brand. How to be?
Use an underscore, which means “any character”: SELECT * FROM BUSES WHERE brand LIKE “Mer_edes”
What can you say about the GROUP BY command?
GROUP BY is used to group the result of one or more columns.
Syntax: SELECT column_name, aggregate_function (column_name)
WHERE column_name operator value
GROUP BY column_name
What is the difference between WHERE and HAVING?
Using HAVING, all previously grouped by GROUP BY data blocks that satisfy the conditions specified in the HAVING. This is an additional the ability to “filter” the output set.
Conditions in HAVING differ from the conditions in WHERE:
– In the WHERE clause, aggregate functions cannot be specified;
– HAVING excludes from the result set a group with aggregated- values;
– WHERE excludes from the calculation of total values by grouping records, conditions.
What is the difference between LEFT, RIGHT and INNER JOIN?
The main difference is how the tables are joined if there is no common records. A simple JOIN is the same as an INNER JOIN; it shows only the common records of both tables and persons. How records are considered common are determined by the fields in the join expression. For example, measures, the following entry: FROM t1 JOIN t2 on t1.id = t2.id means that records with the same id existing in both tables will be shown.
LEFT JOIN (or LEFT OUTER JOIN) means to show all records from the left table (the one that paradise goes first in the join-expression) regardless of whether there are corresponding entries in the right table.
RIGHT JOIN (or RIGHT OUTER JOIN) acts as opposed to LEFT JOIN – shows all records from the right (second) table and only coincided from the left (first) table. LEFT JOIN: – when the condition of the table is concatenated, the cells from the first table are joined cells of the second; – if the condition is not met, empty cells are attached.
INNER JOIN: – when the condition is fulfilled, as with LEFT JOIN; – if the condition is not met, the line is generally ignored (there will not be cells even from the first tables). Simply put, LEFT JOIN will select all records from the first table, even if in the second table no coincidence on some condition. INNER JOIN will choose only those that fully comply with the condition.