MS SQL Server Clause
MS SQL Server DISTINCT Clause
- A DISTINCT clause is used to remove similar records from the table.
- It is used with the SELECT Statement.
- Syntax:
SELECT DISTINCT expression FROM [database_name].[dbo].[table_name] [WHERE conditions];
- Example:
SELECT DISTINCT Name FROM [University].[dbo].[Student] WHERE CGPA>7.0;
| Name |
| Ravi |
| Savi |
| Aayush |
| Kavi |
- Example:
SELECT DISTINCT Name, CGPA, Age FROM [University].[dbo].[Student] WHERE CGPA>7.0;
| Name | CGPA | Age |
| Ravi | 9.86 | 19 |
| Aayush | 7.25 | 20 |
| Kavi | 7.50 | 19 |
MS SQL Server WHERE Clause
- WHERE clause is used to fetch records that fulfill a specified condition.
- It works with the UPDATE and DELETE query.
- Syntax:
SELECT column_list FROM table_name WHERE search_condition;
Example:
SELECT Name, Branch, Age FROM Student WHERE Age>=18;
Output:
| Name | Branch | Age |
| Ravi | CSE | 19 |
| Aayush | CSE | 20 |
| Kavi | CSE | 19 |
MS SQL Server ORDER BY
- ORDER BY is used to fetch data in ascending or descending order.
- Syntax:
SELECT column_lists FROM table_name WHERE conditions ORDER BY column_name [ASC | DESC];
- If we don't define ASC or DESC keyword, SQL Server will use ASC as the default sorting order.
Example:
SELECT name, Age ,CGPA FROM student ORDER BY Age;
Output:
| Name | CGPA | Age |
| Savi | 9.23 | 18 |
| Ravi | 9.86 | 19 |
| Kavi | 7.50 | 19 |
| Aayush | 7.25 | 20 |
Example:
SELECT name, Age ,CGPA FROM student ORDER BY Age DESC;
Output:
| Name | CGPA | Age |
| Aayush | 7.25 | 20 |
| Ravi | 9.86 | 19 |
| Kavi | 7.50 | 19 |
| Savi | 9.23 | 18 |
MS SQL Server GROUP BY
- GROUP BY Clause is used to select to group rows by one or more columns.
- Syntax:
SELECT expression1, expression2, ... expression_n, aggregate_function (expression)
FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n;
Example:
SELECT Branch, SUM(CGPA) AS "Total CGPA" FROM [University].[dbo].[Student]
GROUP BY Branch;
Output:
| Branch | Total CGPA |
| CSE | 33.84 |
Example:
SELECT Branch, COUNT(*) AS "Number of Students" FROM [University].[dbo].[Student]
GROUP BY Branch;
Output:
| Branch | Number of students |
| CSE | 04 |
MS SQL Server HAVING
- HAVING clause is used with SELECT statement which returns rows where values meet the condition.
- Syntax:
SELECT column_list,aggregate_function (expression) FROM table_name GROUP BY group_list
HAVING search_conditions;
Example:
SELECT name, SUM(Marks) AS "Total Marks" FROM student GROUP BY name HAVING SUM(Marks) > 400;
Output:
| Name | Marks |
| Ravi | 432 |
| Savi | 425 |
| Aayush | 467 |
| Kavi | 480 |
MS SQL Server SELECT
- SELECT Statement is used to display all and some records.
- Syntax:
SELECT column1, column2,... columnN FROM table1, table2....[WHERE condition]
[GROUP BY column] [HAVING condition] [ORDER BY column];
SELECT * FROM tables;
Example:
SELECT name FROM student;
Output:
| Name |
| Ravi |
| Savi |
| Aayush |
| Kavi |
Example:
SELECT name,Branch,CGPA FROM student;
Output:
| Name | Branch | CGPA |
| Ravi | CSE | 9.34 |
| Savi | CSE | 8.23 |
| Aayush | CSE | 7.25 |
| Kavi | NULL | NULL |
Example:
SELECT * FROM student;
Output
| Id | Name | Branch | CGPA |
| EN19CS301254 | Ravi | CSE | 9.34 |
| EN19CS301246 | Savi | CSE | 8.23 |
| EN19CS301248 | Aayush | CSE | 7.25 |
| EN19CS301258 | Kavi | NULL | NULL |
