Simple Select Queries (DML)

Employeesona Project

The data entered into this project is as follows:

Project

Proj Code / Proj_Desc
A21 / Allied Carpets
G02 / Game
I11 / Iceland
S03 / Sainsburys
Z04 / Zavvi

Pay_Structure

Grade / Salary
1 / 12000
2 / 15000
3 / 18000
4 / 21000
5 / 24000
6 / 27000
7 / 30000

Employee

Emp_No / Emp_fname / Emp_sname / Grade
12 / Tom / Jones / 7
25 / Jenny / Smith / 5
32 / Fred / Bloggs / 4
40 / John / Smith / 6
56 / Andrea / Murray / 5
60 / Zayd / Ali / 6

Emp_On_Project

Emp_No / Proj_Code / Date_Joined_Proj / Months_Allocated_To_Proj
12 / A21 / 2000/5/5 / 12
12 / I11 / 2000/12/31 / 10
12 / S03 / 2000/1/1 / 12
25 / G02 / 2002/5/4 / 18
32 / Z04 / 2001/5/1 / 9
56 / A21 / 2000/5/5 / 9
56 / S03 / 2000/5/31 / 6
56 / Z04 / 2000/8/1 / 12
60 / A21 / 2000/7/1 / 10

Using the SELECT * Statement to View All Projects

The first statement (and most used) in SQL is the SELECT statement. SELECT allows the developer to create of queries to retrieve specific data from a database.

For example, if we wanted to retrieve all the Projects columns (fields) from a database we would do the following:

SELECT *
FROM Project;

This requests all fields (* means all) from the table Project.

Note: While I may write my SQL statements in uppercase the language itself is not case sensitive. However, writing your SQL this way helps to make the keywords standout.

Note: You do not need to space out SQL commands as I have above. You can write the SQL statement on one line if you wish.

Click on the SQL tab then enter the following into the text box as shown below:

Next, click the Go button to execute the query and view the results.

SELECTing Specific Fields from a Table

If we wanted to select specific fields from a table rather than all fields, we would do the following:

SELECT Emp_fname, Emp_sname
FROM Employee;

This selects the fields:

Emp_fname
Emp_sname

from the table Employee.

SELECT DISTINCT

SELECT DISTINCT only allows one instance of a particular field to be shown. To see how it works try the following query:

SELECT Emp_No
FROM Emp_On_Project;

Now look at the results:

But what if you just wanted to see a single instance of a particular project code?

SELECT DISTINCT Emp_No
FROM Emp_On_Project;

This just retrieves only one record per distinct project code.

WHERE

If we want to look for specific information in a particular column (field) we can use the WHERE clause. For example, if we wanted to retrieve the details for anyone who’s surname is Smith:

SELECT Emp_No, Emp_fname, Emp_sname
FROM Employee
WHERE Emp_sname='Smith';

Here I have retrieved just their ID's, first name and surname.

Note: Because the value I am looking for is text (string) is in apostrophises.

OR

If I wanted to look for all employees with either the surname 'Smith' or 'Jones':

SELECT Emp_fname, Emp_sname
FROM Employee
WHERE Emp_sname='Smith' Or Emp_sname='Jones';

IN

The IN operator works in the same way as the = (equal to) operator.

SELECT Emp_fname, Emp_sname
FROM Employee
WHERE Emp_snameIN ('Smith');

Note: When using IN, if the search value is text it must be in apostrophises not quotes.

If I wanted to look for all employees with either the surname 'Smith' or 'Jones':

SELECT Emp_fname, Emp_sname
FROM Employee
WHERE Emp_snameIN ('Smith','Jones');

Less Than (<)

We can also use relational operators such as less than (<) to find and retrieve information. So, if we wanted to retrieve all the employees who started before 2000/8/1:

SELECT *
FROM emp_on_project
WHERE Date_Joined_Project<'2000/8/1'

Note: You can combine < with = to become <=

Greater Than (>)

Another relational operator we can use is greater than (>) - this works in exactly the same way as less than. So, if we wanted to find all employees who earn more than £20,000 a year:

SELECT Grade, Salary
FROM Pay_Structure
WHERE Salary>20000;

NOT

If we want to find all records that do not match a particular value we can use the NOT operator. So, if we wanted to find every employee who wasn’t a grade 7 we would write:

SELECT Emp_fname, Emp_sname
FROM Employee
WHEREGrade7;

LIKE

While we can use the equals operator to find an exact match we may only know part of the value we are looking for. If that is the case we can use the LIKE operator. If we wanted to find all employees whose name began with S (such as Smith or Simmons):

SELECT Emp_fname, Emp_sname
FROM Employee
WHERE Emp_snameLIKE 'S%';

Remember % returns all matches - so 'S%' returns anything that starts with an S no matter how many characters follow it. So this could return S, So, Sid, Side, Steve, Steven,etc.

We can also use LIKE and % to find letters in the middle of a field:

SELECT Emp_fname, Emp_sname
FROM Employee
WHERE Emp_sname LIKE '%o%';

AND

Up to now we have looked at trying to find a single condition using the WHERE clause but there may be a need for more specific queries. If we need to search a database for information based on more than one condition we can make use of the AND operator. Using this we could search for all employees have been allocated to project A21 for more than 9 months:

SELECT *
FROM emp_on_project
WHERE Proj_Code='A21'
AND Months_Allocated_To_Project>9

BETWEEN

The BETWEEN operator allows us to select fields which fall between a certain range. If we wanted to find all employees who were on grades 5 to 7 we would write:

SELECT Emp_fname, Emp_sname
FROM Employee
WHEREGrade BETWEEN 5 AND 7;

Construct SQL for the following queries

All project titles
Details for project S03
All employees with the surname ‘Smith’
All projects that end with s
All project titleswith an ‘a’ in
All grades and salaries below 20000
All the pay grades for salaries between 18000 and 24000
Project codes for all projects that started between 2000/5/31 and 2000/8/1