SQL Order By, Where Clause, Like Operator and SQL Wildcards

 In this post we will discuss about:

• SQL Order By
• Where Clause
• SQL Like
• SQL Wildcards
• Problems and write SQL queries

Order By 

Order By - The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default.

Where Clause

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition. SQL requires single quotes around text values. However, numeric fields should not be enclosed in quotes.

The following operators can be used in the WHERE clause: =, >, <, >=, <=, <>, BETWEEN, LIKE, IN 

SQL Wildcards

A wildcard character is used to substitute one or more characters in a string.

Wildcard characters are used with the LIKE operator. 

SQL Like 

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

%  - Represents zero or more characters

Example: Hi% finds Hitendra, Hit, Hi

_   -Represents a single character

Example: V_rma find Verma, varma

[] - Represents any single character within the brackets

Example: h[oa]t finds hot and hat, but not hit

^  - Represents any character not in the brackets

h[^oa]t finds hit, but not hot and hat

- Represents a range of characters

Example: c[a-b]t finds cat and cbt

Queries to Solve

• List the employees in the asc order of their Salaries? 

Answer: Select * from EmployeeDetail Order By Salary;

• List the employees in the desc order of their Salaries? 

Answer: Select * from EmployeeDetail Order By Salary DESC;

• List the details of the emps in asc order of the FirstName and desc of Department? 

Answer: SELECT * from EmployeeDetail Order BY Firstname ASC, Department DESC;

• Select employee detail whose name is "Jane" 

Answer: Select * from EmployeeDetail where FirstName='Jane'

• Get all employee detail from Employee table whose "FirstName" start with letter 'r'. 

Answer: Select * from EmployeeDetail where FirstName like 'R%'

• Get all employee details from Employee table whose "FirstName" contains 'a' 

Answer: Select * from EmployeeDetail where FirstName like '%a%'

• Get all employee details from EmployeeDetail table whose "FirstName" end with 'm' 

Answer: Select * from EmployeeDetail where FirstName like '%m'

• Get all employee detail from EmployeeDetail table whose "FirstName" start with any single character between 'a-p' 

Answer: Select * from EmployeeDetail where FirstName like '[a-p]%'

Please refer below YouTube video for more detail explanation:

SQL Part 3: Create new table, Alias, Merge columns

Query to Create EmployeeDetail table:

CREATE TABLE EmployeeDetail

(

EmpId integer PRIMARY KEY, 

FirstName varchar(255),

LastName varchar(255),

Salary integer,

JoiningDate date, 

Department varchar(255),

Gender varchar(255) 

);

INSERT INTO EmployeeDetail VALUES(1,'Tom','Garcia',60000.00,'2008-11-10 13:23:44','IT','Male');

INSERT INTO EmployeeDetail VALUES(2,'Lucy','Martin',70000.00,'2009-11-09 13:23:44','HR','FeMale');

INSERT INTO EmployeeDetail VALUES(3,'Frank','Clark',75000.00,'2012-01-24 13:23:44','Admin','Male');

INSERT INTO EmployeeDetail VALUES(4,'Jane','Joseph',78000.00,'2017-01-28 13:23:44','ICT','FeMale');

INSERT INTO EmployeeDetail VALUES(5,'Robert','Hansen',100000.00,'2012-01-29 13:23:44','HR','Male');

Query to Create ProjectDetail table:

CREATE TABLE ProjectDetail

(

ProjectDetailId integer PRIMARY KEY, 

EmpId integer REFERENCES EmployeeDetail(EmpId), 

ProjectName varchar(255) 

);

INSERT INTO ProjectDetail VALUES(1,1,'ABC');

INSERT INTO ProjectDetail VALUES(2,1,'DDC');

INSERT INTO ProjectDetail VALUES(3,1,'GHI');

INSERT INTO ProjectDetail VALUES(4,2,'HGT');

INSERT INTO ProjectDetail VALUES(5,3,'ABC');

INSERT INTO ProjectDetail VALUES(6,4,'GHI');

INSERT INTO ProjectDetail VALUES(7,3,'XYZ');

INSERT INTO ProjectDetail VALUES(8,5,'DDC');

Alias: Aliases are often used to make column names more readable

Basic SQL Queries for Practice:

1. Write query to get all employee detail from "EmployeeDetail" table:

ANS: SELECT * FROM EmployeeDetail

2. Write query to get only "FirstName" column from " EmployeeDetail " table

ANS: SELECT FirstName FROM EmployeeDetail

3. Write query to get FirstName in upper case as "First Name".

ANS: SELECT UPPER(FirstName) AS [First Name] FROM EmployeeDetail

4. Write query to get FirstName in lower case as "First Name".

ANS: SELECT LOWER(FirstName) AS [First Name] FROM EmployeeDetail

5. Write query for combine FirstName and LastName and display it as "Name" (also include white space between first name & last name).

ANS: 

1st Way - SELECT CONCAT(FirstName+' ',LastName) AS Name FROM EmployeeDetail

2nd Way - SELECT FirstName+' '+LastName AS Name FROM EmployeeDetail

Below are some very good websites to Practice SQL Queries online:

https://sqliteonline.com/

https://www.tutorialspoint.com/execute_sql_online.php

https://rextester.com/l/sql_server_online_compiler

Please refer below YouTube videos for more detail explanation: