One of the most important features of SQL Server is its ability to execute queries and subqueries to retrieve data from the database. In this article, we will discuss SQL Server queries and subqueries in detail.

A SQL Select Query is used to return records from the SQL Server database. Using select queries you can populate variables, cursors, tables, views.

Queries

A query in SQL Server is a request for data from one or more tables or views in the database. SQL Server uses the Structured Query Language (SQL) to execute queries. SQL Server queries can be simple or complex, depending on the requirements of the user. A simple query can retrieve data from a single table, while a complex query can retrieve data from multiple tables and perform advanced operations such as sorting, filtering, and grouping.

GROUP BY

The T-SQL GROUP BY is used when an aggregate function exists in the select query.

SELECT r.course_id, SUM(c.price) AS CourseSUM
FROM courses c, register_course r
WHERE c.id=r.course_id
GROUP BY r.course_id;

HAVING

The T-SQL HAVING specifies a search condition for a group or an aggregate function.

SELECT r.course_id, SUM(c.price) AS CourseSUM
FROM courses c, register_course r
WHERE c.id=r.course_id
GROUP BY r.course_id
HAVING SUM(c.price) > 70;

ORDER BY

The T-SQL ORDER BY sort rows returned in an select query.

SELECT r.course_id, SUM(c.price) AS CourseSUM
FROM courses c, register_course r
WHERE c.id=r.course_id
GROUP BY r.course_id
HAVING SUM(c.price) > 70
ORDER BY SUM(c.price), r.course_id;

WHERE

The T-SQL WHERE specifies a search condition for the rows returned by the select query.

SELECT * FROM courses c WHERE c.price > 50;

BETWEEN

The T-SQL BETWEEN returns rows if the value is within the range of comparisons.

SELECT * FROM courses WHERE price BETWEEN 50 AND 80;

LIKE

The T-SQL LIKE returns rows if a character string matches a pattern.

SELECT * FROM courses c WHERE name LIKE '%Engineering%';
SELECT * FROM courses c WHERE name LIKE 'Eng%';
SELECT * FROM courses c WHERE name LIKE '%ring';

Subqueries

A subquery in SQL Server is a query that is nested inside another query. The subquery can be used to retrieve data that will be used as input for the outer query. The outer query can be a SELECT, UPDATE, or DELETE statement. Subqueries are useful when you need to retrieve data from multiple tables and combine the results into a single result set. Subqueries can also be used to perform calculations or aggregate functions on data before it is used in the outer query.

EXISTS

The T-SQL EXISTS return rows if a SQL subquery contains any rows.

SELECT * FROM courses c
WHERE EXISTS (SELECT 1 FROM register_course r WHERE c.id=r.course_id);

IN

The T-SQL IN returns rows if a specified value matches any value in a SQL subquery.

SELECT * FROM courses c
WHERE c.id IN (SELECT r.course_id FROM register_course r);

SOME

The T-SQL SOME compares a value with a single column set of values.

SELECT * FROM courses c
WHERE c.id = SOME (SELECT r.course_id FROM register_course r);

ANY

The T-SQL ANY returns rows if any value is equal to one value in a subquery.

SELECT * FROM courses c
WHERE c.id = ANY (SELECT r.course_id FROM register_course r);

SQL Server queries and subqueries are powerful tools for retrieving data from a database. SQL Server subqueries can be used to retrieve data from multiple tables and perform advanced operations on the data before it is used in the outer query. By mastering SQL Server queries and subqueries, you can become an expert in data analysis and reporting.