SELECT - FROM - WHERE Query

A SQL SELECT statement can be broken down into numerous elements, each beginning with a keyword. Although it is not necessary, common convention is to write these keywords in all capital letters. In this article, we will focus on the most fundamental and common elements of a SELECT statement, namely

• SELECT
• FROM
• WHERE
• ORDER BY

If we want to retrieve all of the information about all of the customers in the Employees table, we could use the asterisk (*) as a shortcut for all of the columns, and our query looks like

SELECT * FROM Employees
-------------------------------
Get only specific columns data for all of the rows in the table:

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees

-------------------------------

The WHERE Clause

--Filter Data for city "London"

SELECT * FROM Employees WHERE City = 'London'

-------------------------------

---The employees who do not live in London

SELECT Employee,* FROM Employees WHERE City <> 'London'

-------------------------------

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM EmployeesWHERE HireDate >= '1-july-1993'

-------------------------------

SELECT EmployeeID, FirstName, LastName, HireDate, CityFROM EmployeesWHERE (HireDate >= '1-june-1992') AND (HireDate <= '15-december-1993')

-------------------------------
Note that SQL also has a special BETWEEN operator that checks to see if a value is between two values (including equality on both ends). This allows us to rewrite the previous query as

SELECT EmployeeID, FirstName, LastName, HireDate, CityFROM EmployeesWHERE HireDate BETWEEN '1-june-1992' AND '15-december-1993'

-------------------------------
We could also use the NOT operator, to fetch those rows that are not between the specified dates:

SELECT EmployeeID, FirstName, LastName, HireDate, CityFROM EmployeesWHERE HireDate NOT BETWEEN '1-june-1992' AND '15-december-1993'
-------------------------------
What if we want to check if a column value is equal to more than one value? If it is only 2 values, then it is easy enough to test for each of those values, combining them with the OR operator and writing something like

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM EmployeesWHERE City = 'London' OR City = 'Seattle'
-------------------------------
However, if there are three, four, or more values that we want to compare against, the above approach quickly becomes messy. In such cases, we can use the IN operator to test against a set of values. If we wanted to see if the City was either Seattle, Tacoma, or Redmond, we would write

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM EmployeesWHERE City IN ('Seattle', 'Tacoma', 'Redmond')
-------------------------------
As with the BETWEEN operator, here too we can reverse the results obtained and query for those rows where City is not in the specified list:

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE City NOT IN ('Seattle', 'Tacoma', 'Redmond')
-------------------------------

Finally, the LIKE operator allows us to perform basic pattern-matching using wildcard characters. For Microsoft SQL Server, the wildcard characters are defined as follows:

Wildcard Description

_ (underscore) = matches any single character

% = matches a string of one or more characters

[ ] = matches any single character within the specified range (e.g. [a-f]) or set (e.g. [abcdef]).

[^] = matches any single character not within the specified range (e.g. [^a-f]) or set (e.g. [^abcdef]).

A few examples should help clarify these rules.

WHERE FirstName LIKE '_im' finds all three-letter first names that end with 'im' (e.g. Jim, Tim).

WHERE LastName LIKE '%stein' finds all employees whose last name ends with 'stein'

WHERE LastName LIKE '%stein%' finds all employees whose last name includes 'stein' anywhere in the name.

WHERE FirstName LIKE '[JT]im' finds three-letter first names that end with 'im' and begin with either 'J' or 'T' (that is, only Jim and Tim)

WHERE LastName LIKE 'm[^c]%' finds all last names beginning with 'm' where the following (second) letter is not 'c'.

Here too, we can opt to use the NOT operator: to find all of the employees whose first name does not start with 'M' or 'A', we would write

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM EmployeesWHERE (FirstName NOT LIKE 'M%') AND (FirstName NOT LIKE 'A%')
-------------------------------
The ORDER BY Clause

By default, the sort order for a column is ascending (from lowest value to highest value), as shown below for the previous query:

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees ORDER BY City

-------------------------------

--Order by 2 columns

SELECT EmployeeID, FirstName, LastName, HireDate, Country, City FROM Employees ORDER BY Country, City DESC

-------------------------------

--Order by 2 columns but in different order

SELECT EmployeeID, FirstName, LastName, HireDate, Country, City FROM EmployeesORDER BY Country ASC, City DESC

-------------------------------

Deleting duplicate rows from a table

A table with a primary key doesn’t contain duplicates. But if due to some reason, the keys have to be disabled or when importing data from other sources, duplicates come up in the table data, it is often needed to get rid of such duplicates.
This can be achieved in tow ways :-

(a) Using a temporary table.

CREATE TABLE Employee_Test1
(Emp_ID INT,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2))

INSERT INTO Employee_Test1 VALUES (1,'Anees',1000);
INSERT INTO Employee_Test1 VALUES (2,'Rick',1200);
INSERT INTO Employee_Test1 VALUES (3,'John',1100);
INSERT INTO Employee_Test1 VALUES (4,'Stephen',1300);
INSERT INTO Employee_Test1 VALUES (5,'Maria',1400);
INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);
INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);

Step 1:Create a temporary table from the main table as:-

select top 0* into employee_test1_temp from employee_test1

Step 2: Insert the result of the GROUP BY query into the temporary table as:-

insert into employee_test1_tempselect Emp_ID,Emp_name,Emp_Salfrom employee_test1group by Emp_ID,Emp_name,Emp_Sal

Step3: Truncate the original table as:-

truncate table employee_test1


Step4: Fill the original table with the rows of the temporary table as:-

insert into employee_test1select * from employee_test1_temp

Now, the duplicate rows from the main table have been removed.

select * from employee_test1

(b) Without using a temporary table.

with T as( select * , row_number() over (partition by Emp_ID order by Emp_ID) as rank from employee_test1)

deletefrom Twhere rank > 1

nth Highest Salary

--nth Highest Salary

select min(Emp_Sal) from Employee_Test where Emp_Sal in(select distinct top n Emp_Sal from Employee_Test order by Emp_Sal desc)

3rd highest salary

--3rd Highest Salary

Select min(Emp_Sal) from Employee_Test where Emp_Sal in(select distinct top 3 Emp_Sal from Employee_Test order by Emp_Sal desc)

Finding the Highest salary of an employee.

--Highest Salary
select max(Emp_Sal) from Employee_Test