SQL Day 10: Select Statement – Part 2

LO1: Operators and Wild Cards in SQL Server

LO2: Joining Multiple Conditions using AND and OR Operator

LO3: Sorting rows using Order By clause

LO4: Selecting top n rows or top n percentage of rows

Prerequisite: SQL Day 10: Select Statement – Part 1

Operators and Wild Cards in SQL Server

Operators:

In previous part we have work with Equal to = and !=  <> operators. Similarly greater than and less than are used to filter the rows with the values greater than or less than as specified.

Below is the list of Operators.

IN Operator

IN Operator is like multiple OR conditions so if you need to specify the list of values then use IN Operators.

Let’s say you need to find the employee from the tblEmployee table whose age is 25, 30 and 28. It is possible by two ways either you specify multiple OR conditions or use IN operator.

select * from tblEmployee where Age=25 or Age=30 or Age=28 
select * from tblEmployee where Age IN (25,30,28)



So instead of using multiple OR conditions, we can use IN operator to get the desired result.

BETWEEN Operator

Let’s say you need to find the employees whose age between 20 to 25 so here we can use BETWEEN operator.



Boundary 20 and 25 are inclusive in the result.

Wild Cards Operators

Below is the list of wild cards characters.


% Operator

Let’s say you need to find the employees city which starts from letter ‘S’ for that case you can use the % Operator with LIKE. You cannot use the = sign in where clause if you specified any wildcard operators.

If you use the % operator with = sign that will treat as the complete value by the SQL server and you will end up getting the blank or undesired values.

select * from tblEmployee where City LIKE 'S%'



Let’s say you need to find the email id’s that are not valid or not contains the @ symbol, to do that you need to use NOT operator with LIKE operator.

select * from tblEmployee where Email NOT LIKE '%@%'


_ (Underscore) Operator

_ Operator is used to specifying exactly one character.

Let’s say you need to find the email that has only one character at the starting of @ symbol and any number of characters after the @ symbol.

select * from tblEmployee where Email LIKE '_@%'

[] Operators

[] operator is used for the list of characters.

Let’s say we need to find the employees whose name starts with T, J, and D and after that, it can be any number of characters.

select * from tblEmployee where Name LIKE '[TJD]%'


[^] Operators

Similarly, [] operator, this [^] operator is used for NOT, that means other than the specified characters.

select * from tblEmployee where Name LIKE '[^TJD]%'


Joining Multiple Conditions using AND and OR Operator

Let’s say we have to find the employees whose lives in London or Paris and Age is greater than 20 so this can be done using the multiple conditions of AND and OR operator

select * from tblEmployee where (City = 'London' OR City = 'Paris') AND Age > 20


Sorting rows using Order By clause

Order By clause is used to sort the result in ascending or descending order.

Ascending Order

Let’s say we need to sort the tblEmployee table by Name, to do that we need to use the Order By clause.

select * from tblEmployee Order By Name ASC



If you did not specify the ASC then by default the sort is remaining ascending.

Descending Order

To sort the values in descending order we need to use the DESC keyword.

select * from tblEmployee Order By Name DESC


Using Multiple Columns to Sort the Values

Let’s say we need to sort the tblEmployee table by Name in ascending order and then by Age in Descending order.

select * from tblEmployee Order By Name ASC, Age DESC



In above screen, you can see that Name is sorted by ascending order and then Age is sorted by Descending Order.

Selecting top n rows or top n percentage of rows

Selecting Top N Rows

If you want to select the only top 2 or any number of rows then TOP keyword can be used. You can either specified * for selecting all the columns from the table or you can specify the list of columns as you want.

select top 2 * from tblEmployee
select top 2 Name, Email from tblEmployee


Selecting Top N Percentage

When you use Percentage keyword then SQL server calculates the percentage as per the total number of rows and return the result as percentage specified.
Let’s say tblEmployee table have the total number of rows 9 then on specifying 50 percent, it will return the 5 rows.

select top 50 Percent * from tblEmployee


Interview Question:

Let’s say we need to find the top salary employee (In our case we are having age column), to do that we need to use the Order By clause with the Top keyword.

Here we have found the employee with the higher age.

select top 1 * from tblEmployee Order By Age DESC



That’s all for today. Thank You.

Comments

Post a Comment

Popular posts from this blog

SQL Day 11: Group By in SQL Server

Image
LO1: Grouping rows using Group By LO2: Filtering Groups LO3: Difference between Where and Having Clause Group By clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expression. It is always used in conjunction with one or more aggregate functions. Let’s say we have a table tblEmployee which contains all the salary of employees including the cities. So if we need to find the maximum salary of employee we can use the MAX(Column_Name) aggregate function to find the highest salary of employees. select MAX(salary) from tblemployee In the same way, we can find the minimum salary and total salary of all the employees using different aggregate functions. select MIN(salary) as [Minimum Salary] from tblemployee select SUM(salary) as [Total Salary] from tblemployee Let’s say we have to find out the employee salary as per the different cities, to achieve this we need to use the Group B...
Keep reading

JAVA Script Executor Code to Click on an Element

Click on an Element in Selenium WebDriver Sometimes click() method does not work because two views are loaded at the same time on a page. In this case, we can use the JavascriptExecutor to get the work done. Below is code to click on an element using Javascript Executor. 1 2 3 4 5 6 7 @FindBy(id = Checkbox. financialAssistanceAccountEmailId ) public WebElement financialAssistanceAccountEmail; //Click to element JavascriptExecutor executor = (JavascriptExecutor) driver; executor. executeScript ( "arguments[0].click()" , financialAssistanceAccountEmail);
Keep reading

SQL Day 2: Creating, Altering and Dropping a Database

Image
LO1: Creating, Altering and Dropping a Database Prerequisite:   Connecting to SQL Server using SSMS After connecting to the database through SSMS first we need to create the database on which different queries will run. Let’s start with creating a Database. A SQL Server Database can be Created, Altered or Dropped in two ways: Graphically using SQL Server Management Studio or Using SQL Query Graphically using SQL Server Management Studio: Within the Object Explorer, right-click on Database folder and select the New Database. When clicked on New Database a new dialog box will open. Here you need to provide the database name and when clicking on OK, SQL Server run the query internally and will create the new database for us. When you expand the database folder then you will see the Sample database here Creating Database Using Query: We can also create the database by executing the query in the query editor. For creating a...
Keep reading