SQL Zero to Hero

SQL Zero to Hero

• ALIAS (AS): An alias is created using the "AS" keyword. Example: SELECT column_name AS alias_name FROM table_name;

GROUP BY

• Group By Column: Groups rows with the same values in the specified column(s). Example: SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);

HAVING

• Having: Filters groups of data after grouping. Example: SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);

ORDER BY

• ORDER BY (ASC) or ORDER BY DESC: Sorts data in ascending (ASC) or descending (DESC) order based on the specified column(s). Example: SELECT column1, column2 FROM table_name ORDER BY column1 ASC, column2 DESC;

JOINS

• INNER JOIN: Selects rows from two or more tables that have matching values in the specified columns. Example: SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

• LEFT JOIN: Returns all rows from the left table (table1) and the matching rows from the right table (table2). If there's no match in the right table, it returns NULL for the right table's columns. Example: SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

• RIGHT JOIN: Similar to LEFT JOIN, but returns all rows from the right table and the matching rows from the left table. Example: SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

• FULL JOIN: Returns all rows from both tables. If there's no match in either table, it returns NULL for the columns from the other table. Example: SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;

FUNCTIONS

• AVG(): Calculates the average of values in a column. Example: SELECT AVG(column_name) FROM table_name WHERE condition;

• SUM(): Calculates the sum of values in a column. Example: SELECT SUM(column_name) FROM table_name WHERE condition;

• COUNT(): Counts the number of rows or values in a column. Example: SELECT COUNT(*) FROM table_name; or SELECT COUNT(column_name) FROM table_name;

• MIN(): Returns the minimum value in a column. Example: SELECT MIN(column_name) FROM table_name WHERE condition;

• MAX(): Returns the maximum value in a column. Example: SELECT MAX(column_name) FROM table_name WHERE condition;

WHERE

• LIKE: Matches patterns in a column. Example: SELECT column1, column2 FROM table_name WHERE column1 LIKE 'pattern%';

• IN: Matches values in a list. Example: SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);

• BETWEEN: Matches values within a range. Example: SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2;

• ANY: Matches values that satisfy a subquery. Example: SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name);

• EXISTS: Checks if a subquery returns any rows. Example: SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT 1 FROM table_name WHERE condition);

• ALL: Matches values that satisfy a condition for all rows in a subquery Example: SELECT column_name FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name);

• AND, OR, NOT: Combine conditions. Example: SELECT column_name FROM table_name WHERE condition1 AND condition2 OR condition3;