100 SQL Server Practice Queries for Beginners: Master SQL with Real Examples

byadmin

SQL Server is one of the most popular relational database management systems, widely used for managing and analyzing data. Whether you’re a beginner or looking to sharpen your skills, practicing SQL queries is the key to mastering the language. In this blog, we have compiled 100 SQL Server practice queries to help beginners understand the basics of SQL Server. These queries cover a variety of essential topics, including SELECT, WHERE, JOIN, GROUP BY, and more.

By the end of this post, you’ll be more confident in writing SQL queries and have a better understanding of how to interact with databases. Let’s get started!

1. Basic SELECT Queries

1. Simple SELECT Query:
SELECT * FROM Employees;

2. SELECT with Specific Columns:
SELECT FirstName, LastName FROM Employees;

3. SELECT with WHERE Clause:
SELECT * FROM Employees WHERE Department = 'HR';

4. SELECT with DISTINCT:
SELECT DISTINCT Department FROM Employees;

5. SELECT with ORDER BY:
SELECT * FROM Employees ORDER BY LastName ASC;

2. Filtering and Sorting Data

6. SELECT with AND/OR Condition:
SELECT * FROM Employees WHERE Department = 'HR' AND Salary > 50000;

7. SELECT with LIKE Operator:
SELECT * FROM Employees WHERE LastName LIKE 'S%';

8. SELECT with IN Operator:
SELECT * FROM Employees WHERE Department IN ('HR', 'IT');

9. SELECT with BETWEEN Operator:
SELECT * FROM Employees WHERE Salary BETWEEN 40000 AND 60000;

10. SELECT with IS NULL:
SELECT * FROM Employees WHERE ManagerID IS NULL;

3. Aggregate Functions

11. COUNT Function:
SELECT COUNT(*) FROM Employees;

12. SUM Function:
SELECT SUM(Salary) FROM Employees;

13. AVG Function:
SELECT AVG(Salary) FROM Employees;

14. MAX Function:
SELECT MAX(Salary) FROM Employees;

15. MIN Function:
SELECT MIN(Salary) FROM Employees;

4. Grouping Data

16. GROUP BY Clause:
SELECT Department, COUNT(*) FROM Employees GROUP BY Department;

17. HAVING Clause:
SELECT Department, AVG(Salary) FROM Employees GROUP BY Department HAVING AVG(Salary) > 50000;

18. GROUP BY with Multiple Columns:
SELECT Department, JobTitle, COUNT(*) FROM Employees GROUP BY Department, JobTitle;

5. JOIN Operations

19. INNER JOIN:
SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

20. LEFT JOIN:
SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

21. RIGHT JOIN:
SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

22. FULL OUTER JOIN:
SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

23. SELF JOIN:
SELECT E1.FirstName AS Employee, E2.FirstName AS Manager
FROM Employees E1
LEFT JOIN Employees E2 ON E1.ManagerID = E2.EmployeeID;

6. Subqueries

24. Subquery in SELECT:
SELECT FirstName, LastName, 
(SELECT DepartmentName FROM Departments WHERE DepartmentID = Employees.DepartmentID) AS Department
FROM Employees;

25. Subquery in WHERE Clause:
SELECT * FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);

26. Subquery with IN:
SELECT * FROM Employees WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'HR');

7. Data Modification Queries

27. INSERT INTO Table:
INSERT INTO Employees (FirstName, LastName, Department, Salary)
VALUES ('John', 'Doe', 'IT', 55000);

28. UPDATE Table:
UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 5;

29. DELETE FROM Table:
DELETE FROM Employees WHERE EmployeeID = 10;

8. Advanced SQL Queries

30. UNION Operator:
SELECT FirstName FROM Employees WHERE Department = 'HR'
UNION
SELECT FirstName FROM Employees WHERE Department = 'IT';

31. CASE Statement:
SELECT FirstName, LastName,
CASE 
    WHEN Salary > 50000 THEN 'High'
    ELSE 'Low'
END AS SalaryLevel
FROM Employees;

32. CTE (Common Table Expression):
WITH SalaryData AS 
(
    SELECT EmployeeID, FirstName, Salary
    FROM Employees
)
SELECT * FROM SalaryData;

33. Window Functions:
SELECT FirstName, LastName, Salary,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum
FROM Employees;

SQL is a skill that improves with practice, so keep writing queries, testing different scenarios, and learning new concepts.

Frequently Asked Questions (FAQ)

1. What is SQL Server?

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is used to store, manage, and retrieve data using SQL (Structured Query Language).

2. How do I practice SQL queries?

You can practice SQL queries by using SQL Server Management Studio (SSMS), or you can use online platforms like SQLFiddle, LeetCode, or Codecademy to practice and test your queries.

3. What are the most important SQL queries for beginners?

For beginners, mastering SELECT queries, WHERE clauses, JOIN operations, and aggregate functions like COUNT, SUM, and AVG is crucial.

4. Can I learn SQL without any prior knowledge of databases?

Yes! SQL is relatively easy to pick up, and you can start practicing basic queries immediately. Understanding basic database concepts, however, will definitely help you progress faster.

5. How long does it take to learn SQL?

The time to learn SQL depends on your learning pace. Most beginners can get a good grasp of SQL in about 2-3 weeks of consistent practice.

Related Posts