Select Queries

• Used for fetching information from an SQL database.
• 'Outputs' the data once successfully selected
• Allows selection from multiple tables but not multiple databases.

The Basic Construct

  SELECT [data] FROM [table]
  WHERE [condition]
  ORDER BY [data] (DESC) ;

ORDER BY is set to Ascending by default. Adding DESC for Descending will turn this list upside-down.

Basic Example

 SELECT * FROM  Book
 WHERE Price > 100.00
 ORDER BY Title;

Remember the SELECT can have * to select all fields, however questions will normally specify what fields to select. The FROM section should identify which table(s) to select the data from. The WHERE section should include the criteria used to select the data, this could be a simple statement as above but remember you can also include other operators such as AND, OR, NOT, LIKE, and so on.

Relational Databases & Select

Using a relational database (two or more related tables) makes the select statement more complex because you can obviously select data from multiple tables and also use criteria on multiple tables in the where section. So:

1. Any field name which causes ambiguity must also have the table specified, ie table.fieldname
2. The from section must include the tables for every field in the where or select sections
3. The where section must contain the criteria to explain how the tables in the from section are related

So for example:

• this should be primary key to foreign key, so if you have just 2 tables - table1.primary = table2.foreign
• 3 tables would have something like table1.primary = table2.foreign AND table1.primary = table3.foreign

Remember this is on top of the criteria you need to use for the question or output required.

Relational Databases & Select Example

 SELECT TutorGroup.Name, Student.Name
 FROM  TutorGroup, Student
 WHERE Student.ID = TutorGroup.StudentID
 AND Student.YearGroup = 12 AND Student.Gender = 'Male'
 ORDER BY TutorGroup.Name;

This would create a list of male students in year group 12 in order of TutorGroup.Name, for each record it will select TutorGroup.Name and Student.Name.

Quiz

All these questions will use this table called Employees:

1. which of the following Select queries will select the 'First Name' and 'Last Name' of every record?

	Select 'First Name', 'Last Name' from Employees
→	Correct
	Select * from Employees
→	Incorrect
	Select All from Employees
→	Incorrect
	Select 'First Name' from Employees
→	Incorrect

2. which of the following Select queries will select the 'First Name' and 'Last Name' of every employee with the 'Last Name' of 'woman'?

	Select 'First Name', 'Last Name' from Employees where Employee 'Last Name' = 'woman'
→	Incorrect
	Select 'First Name', 'Last Name' from Employees where 'Last Name' = 'woman'
→	Correct
	Select * from Employees where 'Last Name' = 'woman'
→	Incorrect
	Select * from Employees where Employee 'Last Name' = 'woman'
→	Incorrect

3. which of the following Select queries will select the 'First Name' and 'Last Name' of every employee over the age of 50?

	Select 'First Name', 'Last Name' from Employees where Employee 'Age' < 50
→	Incorrect
	Select 'First Name', 'Last Name' from Employees where 'Age' < 50
→	Incorrect
	Select 'First Name', 'Last Name' from Employees where Employee 'Age' > 50
→	Incorrect
	Select 'First Name', 'Last Name' from Employees where 'Age' > 50
→	Correct

4. which of the following Select queries will select the 'First Name' and 'Last Name' of every employee over the age of 50 but under the age of 75?

	Select 'First Name', 'Last Name' from Employees where Employee 'Age' < 50 And 'Age' > 75
→	Incorrect
	Select 'First Name', 'Last Name' from Employees where 'Age' < 50 And < 75
→	Incorrect
	Select 'First Name', 'Last Name' from Employees where 'Age' > 50 And <75
→	Incorrect
	Select 'First Name', 'Last Name' from Employees where 'Age' > 50 And Age <75
→	Correct

Your score is 0 / 0