Querying Database

Querying Database:

Today we will see how can we write different queries to get the information from Employee table.

Find below the list of columns available in Employee table:

ColumnName

EmpID

EmpName

FirstName

LastName

Salary

Location

a. Select all the employees whose FirstName starts with a and salary greater than $10000

SELECT

      EmpName

FROM

      Employee

WHERE

      Salary > 10000

      AND FirstName LIKE 'a%'

Explanation:

To identify all the employees whose name starts with 'a' we have to use regular expression.

Here, 'a%' tells the query that FirstName should start with 'a' and remaining letters can be anything

LIKE Usage:

In SQL we have to use LIKE keyword whenver we are using regular expressions, if we use '=' operator then SQL will search for those employees whose name is 'a%' but not for those whose name starts with 'a'.

AND Usage:

We need only those employees who satisfy both the conditions that is the reason we are using AND operator over here, if our requirement is to get those employees whose salary great than 10000 or FirstName starts with 'a' then we can use OR in place of AND

b. Get the employee names whose FirstName is not available

SELECT

      EmpName

FROM

      Employee

WHERE

      FirstName IS NULL

Explanation:

Here IS NULL tells that FirstName details are not provided by employee

Try writing queries for below Questions:

a. Write a query to get the list of employees whose name is 'Arun' and they should live in Hyderabad

b. Get the list of EMPIDs whose salary is less than 20000 and LastName is not mentioned or FirstName is not mentioned