25 Essential SQL Tricks for Beginners

01 June, 2023

READ TIME - 10 MINUTES

Introduction:

Master the basics of SQL queries with this beginner-friendly collection of 25 essential tricks for SQL Server. Explore fundamental techniques such as data filtering, sorting, and manipulation. Learn how to join tables to combine information seamlessly. Discover the power of aggregate functions for data analysis. Elevate your skills and gain confidence in managing and retrieving data effectively. With this comprehensive guide, you'll be equipped to tackle SQL queries with ease and embark on your journey to becoming a proficient SQL developer.

Note: In this scenario, two tables are created: Employees and Departments. The Employees table consists of columns such as Id (a unique identifier for each employee), Name (employee name), Department (the department in which the employee works), Salary (the employee's salary), and HireDate (the date the employee was hired). The Departments table includes columns such as Id (a unique identifier for each department) and DepartmentName (the name of the department). By utilizing these tables, we can execute SQL queries to retrieve and manipulate data.

1. Use aliases for tables and columns to make your queries more readable.

                       SELECT column_name AS alias_name FROM table_name;
                   

2. Use the DISTINCT keyword to retrieve unique values from a column.

                       SELECT DISTINCT column_name FROM table_name;
                   

3. Use the WHERE clause to filter rows based on specific conditions.

                       SELECT * FROM table_name WHERE condition;
                   

4. Use the ORDER BY clause to sort the result set based on one or more columns.

                  SELECT * FROM table_name ORDER BY column_name ASC|DESC;
                   

5. Use the LIMIT clause to restrict the number of rows returned by a query.

                       SELECT TOP number_of_rows * FROM table_name;
                   

6. Use the LIKE operator with wildcard characters (%) for pattern matching.

                       SELECT * FROM table_name WHERE column_name LIKE 'pattern%';
                   

7. Use the IN operator to match a value against a list of possible values.

                      SELECT * FROM Employees WHERE Department IN ('IT', 'HR');
                   

8. Use the BETWEEN operator to retrieve values within a range.

                      SELECT * FROM Employees WHERE Salary BETWEEN 5000.00 AND 5500.00;
                   

9. Use the GROUP BY clause to group rows based on a specific column.

                       SELECT Department, COUNT(*) AS TotalCount FROM Employees GROUP BY Department;
                   

10. Use the HAVING clause to filter groups based on aggregate conditions.

                    SELECT Department, COUNT(*) AS TotalCount FROM Employees GROUP BY Department HAVING COUNT(*) > 1;
                   

11. Use the JOIN keyword to combine rows from multiple tables based on a related column.

                        SELECT emp.Name, dept.DepartmentName
                        FROM Employees AS emp
                        JOIN Departments AS dept ON emp.Id = dept.Id;
                   

12. Use the INNER JOIN keyword to return only matching rows between tables.

                        SELECT emp.Name, dept.DepartmentName
                        FROM Employees AS emp
                        INNER JOIN Departments AS dept ON emp.Id = dept.Id;
                   

13. Use the LEFT JOIN or RIGHT JOIN keywords to return all rows from one table and matching rows from the other table.

                        SELECT emp.Name, dept.DepartmentName
                        FROM Employees AS emp
                        LEFT JOIN Departments AS dept ON emp.Id = dept.Id;
                   

14. Use the UNION operator to combine the result sets of two or more SELECT statements.

                        SELECT Name FROM Employees WHERE Department = 'IT'
                        UNION
                        SELECT Name FROM Employees WHERE Department = 'HR';
                   

15. Use the EXISTS operator to check if a subquery returns any rows.

                       SELECT Name FROM Employees WHERE EXISTS (SELECT * FROM Departments WHERE Departments.Id = Employees.Id);
                   

16. Use the CASE statement to perform conditional logic in a query.

                       SELECT Name,
                       CASE WHEN Salary > 5500.00 THEN 'High'
                            WHEN Salary > 5000.00 THEN 'Medium'
                            ELSE 'Low'
                       END AS salary_category
                       FROM Employees;
                   

17. Use the AVG, SUM, COUNT, MIN, and MAX aggregate functions to perform calculations on groups of rows.

                        SELECT AVG(Salary) AS AverageSalary FROM Employees;
                        SELECT SUM(Salary) AS TotalSumSalary FROM Employees;
                        SELECT COUNT(Salary) AS CountedSalary FROM Employees;
                        SELECT MIN(Salary) AS MinimumSalary FROM Employees;
                        SELECT MAX(Salary) AS MaximumSalary FROM Employees;
                   

18. Use the DATE and TIME functions to manipulate date and time values.

                       SELECT Name, CAST(HireDate AS DATE) AS hire_date
                       FROM Employees;
                   

19. Use the NULLIF function to replace a specific value with NULL.

                       SELECT NULLIF(Department, 'IT') DepartmentName FROM Employees;
                   

20. Use the COALESCE function to return the first non-null value in a list.

                       SELECT COALESCE(Department, 'Unknown') FROM Employees;
                   

21. Use the TRIM function to remove leading and trailing spaces from a string.

                       SELECT TRIM(Name) FROM Employees;
                   

22. Use the UPPER or LOWER functions to convert text to uppercase or lowercase.

                       SELECT UPPER(Name) FROM Employees;
                   

23. Use the CAST function to convert data types.

                      SELECT Name, CAST(Salary AS INT) Salary FROM Employees;
                   

24. Use the RANK, ROW_NUMBER, and DENSE_RANK functions for ranking and numbering rows.

                       SELECT Name, RANK() OVER (ORDER BY Salary DESC) Salary FROM Employees;
                   

25. Use the CREATE TABLE statement to create a new table.

                     CREATE TABLE Employees (
                      Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
                      Name VARCHAR(50),
                      Department VARCHAR(50),
                      Salary DECIMAL(10, 2),
                      HireDate DATE
                    );

                    CREATE TABLE Departments (
                      Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
                      DepartmentName VARCHAR(50),
                    );
                   

---

The provided images offer guidance on creating a database and utilizing a specific database within your SQL server. Additionally, the image includes supplementary query details for your reference.