SQL [part 3/5]
• SQL queries
• Simple queries
SQL queries
SQL queries are primarily declarative
● Queries specify the properties of the result and not the process to obtain it
● Queries are translated by the DBMS’s query optimiser into the procedural
language internal to the DBMS
● SQL programmers should focus on readability, not on efficiency
SQL queries
By the end of this lecture, you will know how to express query
“What is Mavis Brown’s date of birth?” and much more complex queries in SQL.
SQL
select BirthDate
from STUDENTS
where FirstName=‘Mavis’
and Surname=‘Brown’
Simple SQL queries
● SQL queries are expressed by the select statement
● Syntax:
select AttrExpr [ [as] Alias ] { , AttrExpr [ [as] Alias ] }
from Table [ [as] Alias ] {, [ [as] Alias] }
[ where Condition ]
● The tree parts of the query are usually called:
○ target list
○ from clause
○ where clause
● The query considers the Cartesian product of the tables in the from clause,
selects only the rows that satisfy the condition in the where clause and for each
row evaluates the attribute expressions in the target list (projection)
Simple SQL queries – Algebraic interpretation of SQL queries
The generic SQL query
SQL
select T1.Attribute11, …, Th.Attributehm
from Table1 T1, …, Tablen Tn
where Condition
Corresponds to the relational algebra query
Simple SQL queries
where the employees live
Example
where the departments are located
● PrimaryKey(EMPLOYEE) = {FirstName, Surname}
● PrimaryKey(DEPARTMENT) = {DeptName}
● Dept is a foreign key of EMPLOYEE that references DeptName in DEPARTMENT
Simple SQL queries
Q1: Find the salaries of employees whose surname is Brown.
select Salary
from EMPLOYEE
where Surname=‘Brown’
or
select E.Salary as Remuneration
from EMPLOYEE E
where Surname=‘Brown’
Simple SQL queries – Target list – * in target list
Q2: Find all information relating the employees whose surname is Brown.
select *
from EMPLOYEE
where Surname=‘Brown’
Note: Asterisk (*) in target list represents selection of all attributes of the tables
in the from clause
Simple SQL queries – Target list – Attribute expressions
Q3: Find the monthly salary of the employees whose surname is O’Brien.
select Salary / 12 as MonthlySalary
from EMPLOYEE
where Surname=‘O’’Brien’
Note: Target list can contain generic expressions on the attribute values
Simple SQL queries – from clause – Simple join query
Q4: Find the names of the employees and the cities in which they work.
select EMPLOYEE.FirstName, EMPLOYEE.Surname, DEPARTMENT.City
from EMPLOYEE, DEPARTMENT
where EMPLOYEE.Dept=DEPARTMENT.DeptName
Simple SQL queries – from clause – Table aliases
Q4: Find the names of the employees and the cities in which they work.
Use table aliases to make the query more readable
select E.FirstName, E.Surname, D.City
from EMPLOYEE E, DEPARTMENT D
where E.Dept=D.DeptName
Simple SQL queries – from clause – Alternative syntax for joins in SQL
-
-
● Alternative syntax for joins – representing them explicitly in the from clause
select AttrExpr [ [as] Alias ] { , AttrExpr [ [as] Alias ] }
from Table [ [as] Alias ]
{ [ JoinType ] join Table [ [as] Alias] on JoinConditions }
[ where OtherCondition ]
● JoinType can be any of
inner, right [outer], left [outer] or full [outer]
● The keyword natural may precede JoinType
NOTE: This module only considers the simpler join syntax in the previous slide
Simple SQL queries – where clause – Predicate conjunction & disjunction
Q5: Find the first names and surnames of the employees who work in office
number 20 of the Administration department.
select FirstName, Surname
from EMPLOYEE
where Office=20 and Dept=‘Administration’
Simple SQL queries – where clause – Predicate conjunction & disjunction
Q6: Find the first names and surnames of the employees who work in either the
Administration or the Production department.
select FirstName, Surname
from EMPLOYEE
where Dept=‘Administration’ or Dept=‘Production’
Simple SQL queries – where clause – Complex logical expressions
Q7: Find the first names of the employees with surname Brown who work in the
Administration department or the Production department.
select FirstName
from EMPLOYEE
where Surname=‘Brown’ and
(Dept=‘Administration’ or Dept=‘Production’)
Simple SQL queries – where clause – Operator like
Q8: Find the employees whose first names start with ‘Ma’ and contain a letter
‘r’ (e.g., Mary, Mairead, Margaret, Maighread, etc.)
select *
from EMPLOYEE
where FirstName like ‘Ma%r%’
Note: Operator like allows string pattern matching, 2 special characters:
percentage (%) represents a string (possible empty) of arbitrary characters
underscore (_) represents an arbitrary character
Simple SQL queries – Management of null values
● Null values represent an absence of information, and can mean
○ A value is not applicable
○ A value is applicable but unknown
○ It is unknown if a value is applicable or not
● Predicate is null can be used to select terms with null values
Attributes is [ not ] null select *
from EMPLOYEE
where Salary is not null
● Two alternative solutions for dealing with comparisons involved null values
○ Two-valued logic (SQL-89): return false
○ Three-valued logic (SQL-2): return unknown
Simple SQL queries – Duplicates
● In relational algebra, query results do not contain duplicates
● In SQL, query results can contain duplicates (i.e., identical rows)
● Duplicates can be removed by including keyword distinct in the select
clause
select City select distinct City
from Department from Department
Simple SQL queries – Table variables
-
Q9: Find the first names and surnames of the employees who share an office
with an employee of the Administration department and earn a higher salary
S
than the Administration department’s employee they share the office with.
select E.FirstName, E.Surname
from Employee E, Employee AD
where AD.Dept=‘Administration’
and E.Office=AD.Office
and E.Salary>AD.Salary
Simple SQL queries – Table variables
● Table aliases may be interpreted as table variables
● They correspond to the renaming operator ρ of relational algebra
● E and AD in the examples can be considered as two instances of EMPLOYEE
select E.FirstName, E.Surname
from Employee E, Employee AD
where AD.Dept=‘Administration’
and E.Office=AD.Office
and E.Salary>AD.Salary
Simple SQL queries – Ordering
● By default, rows in a table or a query result do not follow any order
● The order by clause can be added at the end of the query to order the
rows in the query result
● Syntax:
order by OrderingAttribute [ asc | desc ]
{, OrderingAttribute [ asc | desc ]}
Q10: Extract the data of table EMPLOYEE and return it in ascending order of
Surname and FirstName.
select *
from EMPLOYEE
order by Surname asc, FirstName asc
Simple SQL queries – Ordering
select *
from EMPLOYEE
order by Surname asc, FirstName asc
