SQL Commands and Grouping

SQL Commands

• ALIAS: Creates an alias for a column using the AS keyword.
  • Example: SELECT column_name AS alias_name FROM table_name;

SQL GROUP BY

• GROUP BY: Groups rows that have the same values in specified columns.
  • Example: SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);

SQL HAVING

• HAVING: Filters groups of rows based on a condition.
  • Example:SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);

SQL ORDER BY

• ORDER BY: Sorts the result set in ascending (ASC) or descending (DESC) order.
  • Example: SELECT column1, column2 FROM table_name ORDER BY column1 ASC, column2 DESC;

SQL JOINS

• INNER JOIN: Returns rows where the values in specified columns match in both tables.

  • 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, and the matching rows from the right table.

  • Example: SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

• RIGHT JOIN: 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, combining matching rows and rows with no match in the other.

  • Example: SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

SQL FUNCTIONS

• AVG(): Calculates the average of a numeric column.

  • Example: SELECT AVG(column_name) FROM table_name WHERE condition;

• SUM(): Calculates the sum of a numeric 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;
  • Example: SELECT COUNT(column_name) FROM table_name;

• MIN(): Finds the minimum value in a numeric column.

  • Example: SELECT MIN(column_name) FROM table_name WHERE condition;

• MAX(): Finds the maximum value in a numeric column.

  • Example: SELECT MAX(column_name) FROM table_name WHERE condition;

SQL WHERE Clause

• LIKE: Matches values based on a pattern.
  • Example: SELECT column1, column2 FROM table_name WHERE column1 LIKE '%abc%';
• IN: Matches values within a specific set of values.
  • Example: SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);
• BETWEEN: Matches values within a specified range.
  • Example: SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2;
• ANY/SOME: Compares a value to any/some value in a subquery.
  • Example: SELECT column_name FROM table_name WHERE column_name > ANY (SELECT column_name FROM another_table);
• EXISTS: Checks if a subquery returns any rows.
  • Example: SELECT column_name FROM table_name WHERE EXISTS (SELECT 1 FROM another_table WHERE condition);
• AND, **OR, **NOT: Combines conditions using logical operators.

Boolean Operators

• AND: Returns TRUE if both conditions are TRUE.
• OR: Returns TRUE if at least one condition is TRUE.
• NOT: Returns the opposite of the condition.

Test your knowledge on SQL commands like ALIAS, GROUP BY, HAVING, and ORDER BY. This quiz will help you understand how to manipulate and retrieve data efficiently from databases using SQL. Perfect for students and professionals looking to sharpen their database skills.