SQL aggregate functions perform calculations on a set of values to return a single value. For example, the function 'SELECT COUNT(*) AS total, AVG(price) AS avg_price FROM products;' computes the total number of products and the average price of the products, returning 3 as total and 30.00 as the average price .

Using SELECT * in queries can lead to inefficient use of resources as it retrieves all columns, increasing data transfer between the database and application. It can also negatively impact maintainability since the data retrieved could change when table schemas are updated, leading to unexpected results if column orders change or irrelevant data is included .

The ORDER BY clause sorts the result-set of a query based on one or more columns in either ascending (ASC) or descending (DESC) order. For instance, 'SELECT name FROM customers ORDER BY name ASC;' arranges customer names in alphabetical order, hence the output is Ayesha, Ravi, Sneha .

The WHERE clause in SQL allows you to filter records that meet a specific condition, which refines the query results to only include data that are relevant. For example, in the statement 'SELECT name FROM customers WHERE city = 'Pune';', only customers living in Pune are selected, which in this instance is Ravi .

SQL queries can be optimized by using indexes for faster retrieval of records, minimizing the use of wildcard characters, ensuring that only necessary columns are queried, avoiding complex joins and subqueries when possible, and ensuring proper use of WHERE clauses to filter data early in the query process .

The GROUP BY clause organizes data into groups based on one or more columns. It is frequently used with aggregate functions to perform calculations on each group independently. For instance, 'SELECT grade, COUNT(*) AS total FROM students GROUP BY grade;' provides the count of students for each grade, resulting in two students for grade A and one for grade B .

The HAVING clause filters groups formed by GROUP BY based on a specified condition, similar to WHERE but applied to aggregated data. For instance, if we want to find groups of grades with more than one student, the HAVING clause can follow the GROUP BY to specify this condition, ensuring that only such groups appear in the results .

The INNER JOIN clause is used to combine rows from two or more tables based on a related column between them. The SQL statement 'SELECT c.name, o.item FROM customers c JOIN orders o ON c.id = o.customer_id;' joins the customers and orders tables to match each customer with their corresponding orders, showing results like Ayesha with Laptop and Ravi with Phone .

To perform a LEFT JOIN in SQL, merge data from two tables based on a related column in each table, ensuring all records from the left table appear in the result. For example, 'SELECT c.name, o.item FROM customers c LEFT JOIN orders o ON c.id = o.customer_id;' would return all customers, including those without matching orders, with NULLs in the item column for unmatched cases .

COUNT(*) counts all rows in a table, whereas COUNT(column_name) counts only the rows where the column contains a non-NULL value. COUNT(*) is used when rows' total number is needed, while COUNT(column_name) is useful when we need to count rows with data present in a specific column .