SELECT Query Basics

Communicator
Tools
Profile Settings

Loading, please wait ...

Open its course - Database Systems Labs

SELECT Query Basics By Mukhtiar Zamin
Summary

Summary

4
Teachers with
Mukhtiar Zamin 19
Followers
For Learning All
Visibility
Security Status 7
Contributions
By Teachers

Notes

Students are auto-evaluated against related topics to ensure they learned it.

In the lab we learn the following concepts used in SELECT Query of SQL.

Lab Objectives

Understanding Distinct
Understanding Aliases
Reading of Data from multiple tables
Filtering of Data based on single and compound conditions
Understanding Order By

Lab Pre-Requisites

Step-1: Ensure your sample data in “DreamHome” database (Part of Lab 6)
Step-2: Your MyDatabaseLabs.sql file is with you which can do the following:

Create and drop a database by only providing the name of the database to it
Create and drop tables of DreamHome database
Insert queries that will add your sample data to the database DreamHome

Distinct and Aliases for All, By Mukhtiar Zamin

Distinct

Distinct keyword before the column(s) eliminate duplicate values and return only different values.

Task: On DreamHome database run the following queries:

Select city from Branch;

Select distinct(city) from Branch

The first query returns duplicate city, while the second one returns only a distinct set of values.

Aliases

Provides a way to give a different name to a table, column or a derived column in the results. Note that name are only applicable for results, they do not affect the metadata of the tables.

Task: On DreamHome database run the following queries:

Select city from Branch;

Select city As MyCity from Branch;

Select staffNo as ID, fName as FirstName, lName as [Last Name], position as Position, sex as Gender, DOB as [Date of Birth], salaray as Income from Staff;

Select (fName +' '+lName) as [Name], position as Position from Staff;

Tip: You can concatenate column for a derived columns.

SQL Script File for All, By Mukhtiar Zamin

Click Me to open the SQL Script file of this lab.

Filtering Data for All, By Mukhtiar Zamin

Filtering of Data based on single and compound conditions You can select data of your choice from all rows of a table by specifying a condition using a Where clause in the SELECT Command.

Row selection (WHERE clause)

The five basic search conditions:

Comparison Compare the value of one expression to the value of another expression.
Range Test whether the value of an expression falls within a specified range of values.
Set membership Test whether the value of an expression equals one of a set of values.
Pattern match Test whether a string matches a specified pattern.
Null Test whether a column has a null (unknown) value.

In SQL, the following simple comparison operators are available:

= equals
<> is not equal to (ISO standard)
!= is not equal to (allowed in some dialects)
< is less than
<= is less than or equal to
> is greater than
>= is greater than or equal to

More complex predicates can be generated using the logical operators AND, OR, and NOT, with parentheses (if needed or desired) to show the order of evaluation.

The rules for evaluating a conditional expression are:

An expression is evaluated left to right
Subexpressions in brackets are evaluated first
NOTs are evaluated before ANDs and ORs
ANDs are evaluated before ORs.

Task: On DreamHome database run the following queries and note your results. More queries will be asked in the class and you will provide the solutions:

SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary >10000;

SELECT * FROM Branch WHERE city ='London' OR city='Glasgow';

SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary BETWEEN 20000 AND 30000;

SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary >= 20000 AND salary <=30000;

SELECT staffNo, fName, lName, position FROM Staff WHERE position IN (‘Manager’, ‘Supervisor’);

SELECT staffNo, fName, lName, position FROM Staff WHERE position =‘Manager’ OR position =‘Supervisor’;

Note: The IN test provides a more efficient way of expressing the search condition, particularly if the set contains many values.

Pattern based searching for All, By Mukhtiar Zamin

SQL has two special pattern-matching symbols:

% percent character represents any sequence of zero or more characters (wild-card).
_ underscore character represents any single character. All other characters in the pattern represent themselves.

For example:

address LIKE ’H%’ means the first character must be H, but the rest of the string can be anything.
address LIKE ’H___’ means that there must be exactly four characters in the string, the first of which must be an H.
address LIKE ’%e’ means any sequence of characters, of length at least 1, with the last character an e.
address LIKE ’%Glasgow%’ means a sequence of characters of any length containing Glasgow.
address NOT LIKE ’H%’ means the first character cannot be an H.

Examples

Find all owners with the string "Glasgow" in their address.

SELECT ownerNo, fName, lName, address, telNo FROM PrivateOwner WHERE address LIKE ‘%Glasgow%’;

NULL search condition (IS NULL/IS NOT NULL)

List the details of all viewings on property PG4 where a comment has not been sup-plied.

SELECT clientNo, viewDate FROM Viewing WHERE propertyNo = ’PG4’ AND comment IS NULL

Reading of Data from multiple tables for All, By Mukhtiar Zamin

You can read data from multiple tables by separating table name by a comma ",". Note that if you do not specify a condition then results will be a cross multiplication of tables and may results into invalid rows. Therefore, to generate meaningful information we should have some condition among the tables.

Task: On DreamHome database run the following queries:

Select * from Branch,Staff;

Select * from Branch,Staff Where Branch.banchNo = Staff.branchNo;

Select * from Branch as b,Staff as s Where b.banchNo = s.branchNo;

Select * from Branch as b,Staff as s,PropertyForRent as pfr Where b.banchNo = Staff.branchNo And b.banchNo = pfr.branchNo;

Select * from Branch as b,Staff as s, PropertyForRent as pfr Where b.banchNo = Staff.branchNo And b.banchNo = pfr.branchNo And s.staffNo = pfr.staffNo;

Tip: You can concatenate column for a derived columns.

Understanding Order By for All, By Mukhtiar Zamin

ORDER BY is used with SELECT Query to sort the results in ascending or descending order.

Task: On DreamHome database run the following queries:

Select city from Branch order by city;

Select distinct(city) from Branch order by city desc;

Lab Tasks for All, By Mukhtiar Zamin

Print the list of postcode without any repetition
Print all fName from Staff without repetition
List all staff with renaming all its columns in results
List all clients with re-naming all its columns to synonyms.
List all staff with a salary greater than 10,000.
List all managers and supervisors.