01 June, 2023
READ TIME - 10 MINUTES
Thank you to our sponsors who help keep this blog post free for the reader:
This month's issue is proudly sponsored by Saddam Hossain .NET.
The website is owned by none other than Md. Saddam Hossain, who holds the esteemed title of Senior Software Engineer and is known for his unwavering passion for .NET development . To learn more about Md. Saddam Hossain, You are invited to visit the website for comprehensive details and insights.
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.
About the Blogs
As a dedicated .NET developer, I maintain a Patreon account where I share exclusive content related to .NET development. There, you will also gain access to the codebase of this blog post. By becoming a Patreon member, you will have the opportunity to explore and learn from my projects firsthand.
If you have found my contributions helpful in any way, I kindly ask you to consider becoming a Patreon supporter. Your support enables me to continue producing high-quality content, empowering developers like yourself to enhance their skills and stay up to date with the latest developments in the .NET ecosystem. Thank you for considering joining my Patreon community!
Collection of quries
You can access the complete source code for these blog post SQL queries on Patreon.
01 November, 2023
01 October, 2023