SQL Day 10: Select Statement – Part 1

LO1: Select Specific or All Columns
LO2: Distinct Rows
LO3: Filtering with Where Clause

Prerequisite: SQL Day 3: Creating Tables & Enforcing Primary and Foreign Key

The Select statement is used to retrieve the all columns or specific columns from a table.

Different Methods to Retrieve the Columns Using Select

1. Using * Sign

When we use * sign is select statement then this will retrieve all the columns list of a table.

Syntax: 

select * from (Table_Name)

Example:

select * from tblEmployee


2. Using all Column List

Same as the * sign but can retrieve the selected columns as per the given list of columns.

Syntax:

select (Column_Names) from (Table_Name)

Example:

select ID, Name, Email from tblEmployee


3. Writing Select Query Graphically

We can also generate the select statement graphically. To do that, you need to right-click on the table in Object Explorer and then go to the New Query Editor Window as per the below screenshot.



This will create the select statement for the selected table.



In this table, you can see that table is mentioned as three part table name. This is the fully qualified name of the table.

[Sample].[dbo].[tblEmployee]

The [Sample] is the database name, [dbo] is the schema (This will be discussed in later sessions) and [tblEmployee] is the table name.

When we are in the different context of the database and execute the query then the query will get executed successfully.


Selecting Distinct Rows

Distinct is used to remove the duplicate values from the columns i.e. if we are using the Distinct keyword with the select statement then we will get the unique values.

Let’s say we have the table tblEmployee in which City ‘London’ is repeated twice.



Now if we want to select the unique city then we can use the Distinct keyword in the select statement.

Syntax:

select Distinct (Column_Names) from (Table_Name)

Example:

select Distinct City from tblEmployee

You can see that only 6 rows are returned by running above query because of the Distinct keyword.



If we use the Distinct keyword with multiple columns then the values should be distinct across these columns.

For example, if we take Name with City column then all the 7 rows will return because the values are not distinct in City and the Name column.

select Distinct Name, City from tblEmployee


Filtering with Where Clause

Where clause is used to filter the rows on the specified condition.

Let’s say we want all the people names who lived in London, to do that we need to add a where clause in the select statement.

Syntax: 

select * from (Table_Name) where (Condition)

Example:

select * from tblEmployee where City = 'London'

Now, Let’s say we want all the people names who not lived in London, to do that we need to add a where clause in the select statement.

select * from tblEmployee where City != 'London'

or

select * from tblEmployee where City <> 'London'



Operator != and <> is considered as NOT EQUAL to operator.

There are many operators that we used with where clause like >, <, >=, <=, BETWEEN, IN, LIKE and NOT.

We also have some wild card Operators %, _, [] and [^].

These operators will be discussed in next part.

 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