Lecture 7: SQL + RA Flashcards
Explain the use of the select command
- 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
Write / recite the select command general syntax
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
What is the purpose of Distinct?
Use distinct to eliminate duplicates.
e.g select propertyNo FROM viewing. —>
SELECT distinct propertyNo from viewing
How to use “AS”
creating a new column
SELECT staffNo, fName, lName, salary/12 AS monthlySalary FROM staff
Name and give an example of a comparison search condition
List all staff with a salary greater than 10,000. Using WHERE condition
How do you write “List al staff with a salary between 20,000 and 30,000
select staffNo, fName, lName, position, salary
from staff
where salary BETWEEN 2000 AND 30000
What are some other range search conditions
- NOT BETWEEN
and - logical operators (< , >, =>, =
How to use function “IN”
select staffNo, fName
FROM staff
WHERE position IN (‘Manager’,’position’)
How can you use the negated version NOT IN?
select staffNO, fName, lName, position
FROM staff
WHERE position NOT IN (‘Manager’,’Supervisor’)
Whats another option for writing “AND” ?
also use OR logical operator
SELECT staffNo, fName, position
FROM staff
WHERE position = ‘Manager’ OR
position = ‘Supervisor’
Pattern matching operator is?
LIKE operatore is unique because it allows us to use wildcard characters search for patterns in strings
What are SQL’s two special matching symbols?
% sequence of zero or more characters
_ (underscore) are single character
Use % in a SQL statement
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%’
how to use the underscore matching pattern
select fName, lName, branchNo
FROM staff WHERE
lName LIKE ‘_o%’
how to search a NULL condition
select * from viewing WHERE comment LIKE ‘’;
or
WHERE comment is NULL;
how to single columns ordering?
USE DESC or ASC;
e.g. order by salary DESC;
Provide example of multiple columns ordering
ORDER BY type
How to arrange in order of rent, specify minor order?
select propertyNo, type, room, rent
FROM propertyForRent
ORDER BY type, rent DESC
Why theory (mathematical grounding/RA0
- 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
Describe Relational Algebra (RA)
- 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
Why is RA important?
- it provides a formal foundation for relational model operations
- it is used as a basis for implementing and optimising queries in a RDBMS
- some of its concepts are incorporated into SQL
Only List the set operations
selection (where) projection (from) cartesian product union set different
List operations specific to RDBMS
join
intersection
division
what are the logical operator symbols for:
AND
OR
NOT
AND ^
OR ^(OPP)
NOT ~
