Lecture 7: SQL + RA

Question 1

Explain the use of the select command

Answer 1

• Use to retrieve and display data from the DB
• extremely powerful and has many forms
• most frequently used SQL command
• easy to learn but requires some practice to master it

Question 2

Write / recite the select command general syntax

Answer 2

select (distinct | ALL) or (column AS newName)..

FROM tableName
WHERE condition
GROUP BY columnList HAVING condition
ORDER BY columnList

Order cannot be changed
Only select and from are mandatory

Question 3

What is the purpose of Distinct?

Answer 3

Use distinct to eliminate duplicates.

e.g select propertyNo FROM viewing. —>

SELECT distinct propertyNo from viewing

Question 4

How to use “AS”

Answer 4

creating a new column

SELECT staffNo, fName, lName, salary/12 AS monthlySalary FROM staff

Question 5

Name and give an example of a comparison search condition

Answer 5

List all staff with a salary greater than 10,000. Using WHERE condition

Question 6

How do you write “List al staff with a salary between 20,000 and 30,000

Answer 6

select staffNo, fName, lName, position, salary
from staff
where salary BETWEEN 2000 AND 30000

Question 7

What are some other range search conditions

Answer 7

• NOT BETWEEN
  and
• logical operators (< , >, =>, =

Question 8

How to use function “IN”

Answer 8

select staffNo, fName
FROM staff
WHERE position IN (‘Manager’,’position’)

Question 9

How can you use the negated version NOT IN?

Answer 9

select staffNO, fName, lName, position
FROM staff
WHERE position NOT IN (‘Manager’,’Supervisor’)

Question 10

Whats another option for writing “AND” ?

Answer 10

also use OR logical operator

SELECT staffNo, fName, position
FROM staff
WHERE position = ‘Manager’ OR
position = ‘Supervisor’

Question 11

Pattern matching operator is?

Answer 11

LIKE operatore is unique because it allows us to use wildcard characters search for patterns in strings

Question 12

What are SQL’s two special matching symbols?

Answer 12

% sequence of zero or more characters

_ (underscore) are single character

Question 13

Use % in a SQL statement

Answer 13

select ownerNo, address, fName
FROM privateOwner
Where address LIKE ‘%Glasgow%’

returns characters of any length containing glasgow

OR

select fName, lName
WHERE fname LIKE ‘J%’

Question 14

how to use the underscore matching pattern

Answer 14

select fName, lName, branchNo
FROM staff WHERE
lName LIKE ‘_o%’

Question 15

how to search a NULL condition

Answer 15

select * from viewing WHERE comment LIKE ‘’;

or
WHERE comment is NULL;

Question 16

how to single columns ordering?

Answer 16

USE DESC or ASC;

e.g. order by salary DESC;

Question 17

Provide example of multiple columns ordering

Answer 17

ORDER BY type

Question 18

How to arrange in order of rent, specify minor order?

Answer 18

select propertyNo, type, room, rent
FROM propertyForRent
ORDER BY type, rent DESC

Question 19

Why theory (mathematical grounding/RA0

Answer 19

• it provides a way of thinking about operations & results
• it is general
• used as a basis to implement and optimise queries in the query processing and optimisation modules

Question 20

Describe Relational Algebra (RA)

Answer 20

• DML can be expressed in a formal way using RA
• RA is a procedural language that defines database operations in terms of algebraic expressions
• RA operations work on one or more relations and produce a new relation f(R) = S
• the result of operation can be an input of another operation, therefore effectively nesting/combining operations

Question 21

Why is RA important?

Answer 21

1. it provides a formal foundation for relational model operations
2. it is used as a basis for implementing and optimising queries in a RDBMS
3. some of its concepts are incorporated into SQL

Question 22

Only List the set operations

Answer 22

selection  (where)
projection (from)
cartesian product
union
set different

Question 23

List operations specific to RDBMS

Answer 23

join
intersection
division

Question 24

what are the logical operator symbols for:
AND
OR
NOT

Answer 24

AND ^
OR ^(OPP)
NOT ~