SQL Server Tutorialprovides basic and advanced concepts of C# for beginners and professionals.

  1. Home
  2. SQL Server Tutorial

SQL Server Basic Queries

Back to: SQL Server Tutorial

Here are some basic SQL queries that demonstrate fundamental operations in SQL:

1. Selecting Data Query: Retrieve all columns from a table.

sql code
SELECT * FROM Employees;
SELECT * selects all columns. FROM Employees specifies the table to retrieve data from. Query: Retrieve specific columns.
sql code
SELECT FirstName, LastName FROM Employees;

Selects only the FirstName and LastName columns.

2. Filtering Data Query: Filter data with a condition.

sql code
SELECT * FROM Employees WHERE LastName = 'Smith';
WHERE LastName = 'Smith' filters rows where the LastName column is 'Smith'. Query: Filter with multiple conditions using AND and OR.
sql code
SELECT * FROM Employees WHERE BirthDate < '1990-01-01' AND HireDate > '2010-01-01';
AND combines conditions; retrieves rows where BirthDate is before January 1, 1990, and HireDate is after January 1, 2010. Query: Filter using LIKE for pattern matching.
sql code
SELECT * FROM Employees WHERE FirstName LIKE 'J%';

LIKE 'J%' matches any FirstName that starts with 'J'.

3. Sorting Data Query: Sort data in ascending order (default).

sql code
SELECT * FROM Employees ORDER BY LastName;
ORDER BY LastName sorts the results by LastName in ascending order. Query: Sort data in descending order.
sql code
SELECT * FROM Employees ORDER BY HireDate DESC;

ORDER BY HireDate DESC sorts the results by HireDate in descending order.

4. Limiting Results Query: Limit the number of results returned.

sql code
SELECT TOP 5 * FROM Employees ORDER BY HireDate DESC;

TOP 5 returns the top 5 rows when sorted by HireDate in descending order.

5. Aggregating Data Query: Count the number of rows.

sql code
SELECT COUNT(*) AS EmployeeCount FROM Employees;
COUNT(*) returns the total number of rows in the table. The result is aliased as EmployeeCount. Query: Find the average value of a column.
sql code
SELECT AVG(Salary) AS AverageSalary FROM Employees;
AVG(Salary) calculates the average salary. Query: Find the maximum and minimum values.
sql code
SELECT MAX(Salary) AS MaxSalary, MIN(Salary) AS MinSalary FROM Employees;

MAX(Salary) returns the highest salary, and MIN(Salary) returns the lowest salary.

6. Grouping Data Query: Group data by a column and aggregate.

sql code
SELECT Department, COUNT(*) AS EmployeeCount FROM Employees GROUP BY Department;
GROUP BY Department groups results by Department, and COUNT(*) counts the number of employees in each department. Query: Filter grouped data with HAVING.
sql code
SELECT Department, AVG(Salary) AS AverageSalary FROM Employees GROUP BY Department HAVING AVG(Salary) > 50000;

HAVING AVG(Salary) > 50000 filters groups to only include departments with an average salary greater than $50,000.

7. Joining Tables Query: Join two tables (e.g., Employees and Departments) on a common column. Assuming a Departments table:

sql code
CREATE TABLE Departments ( DepartmentID INT PRIMARY KEY, DepartmentName NVARCHAR(50) );
ALTER TABLE Employees ADD DepartmentID INT;
-- Adding a foreign key constraint
ALTER TABLE Employees ADD CONSTRAINT FK_Department FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);
Query: Inner join to combine data from Employees and Departments.
sql code
SELECT e.FirstName, e.LastName, d.DepartmentName FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;
INNER JOIN combines rows from Employees and Departments where DepartmentID matches. Query: Left join to include all rows from the left table.
sql code
SELECT e.FirstName, e.LastName, d.DepartmentName FROM Employees e LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

LEFT JOIN includes all rows from Employees, with NULL for DepartmentName where there is no match in Departments.

8. Inserting Data Query: Insert a new row into a table.

sql code
INSERT INTO Employees (FirstName, LastName, BirthDate, HireDate) VALUES ('Emily', 'Davis', '1989-12-04', '2020-07-15');

Adds a new employee with the given values.

9. Updating Data Query: Update existing data in a table.

sql code
UPDATE Employees SET HireDate = '2021-01-01' WHERE LastName = 'Davis';

Updates the HireDate for employees with the last name 'Davis'.

10. Deleting Data Query: Delete specific rows from a table.

sql code
DELETE FROM Employees WHERE LastName = 'Davis'; Deletes rows where the LastName is 'Davis'.
These queries cover basic SQL operations, including data retrieval, filtering, sorting, aggregation, and modifying data. Each query can be adapted and extended based on the requirements of your specific database schema and use case.
Scroll to Top