460 - Topic 8 - Structured Query Language (SQL)

What is the form of SELECT?

What is the form of a project? (remember: there is no project operator in SQL)

How to alias a column in SQL?

Why does SQL not remove duplicate tuples by default?

1. Duplicates can be meaningful sometimes

   1. overhead

How does ascending/descending ORDER BY work?

What is a computed column? Give an example

A column which is created via basic arithmetic with field values

SQL supports pattern matching, what is its form and what are the 2 wildcards and meaning

Form:
WHERE attribute [optional: not] LIKE ‘pattern’

Wildcards:
_ (underscore) matches any single character
EX: Lon__n (would match ‘London’)
% matches 0 or more characters
EX: ‘%Tucson%’ (would match ‘CSC 460 Dr Tucson AZ"‘

What are the 6 different REGEXP_LIFE options for <pattern>?

What is a barebones/conventional Cartesian Product in SQL?

SELECT *
FROM S, R;

What is set difference named in Oracle? SQL?

MINUS, EXCEPT

What is the purpose of GROUP BY?

To apply aggregates to sub-groups of tuples

^ (McCann def.)
It arranges the data into groups based on some common value specified, and is often used with aggregate functions

What is the purpose of HAVING?

To filter the groups returned by GROUP BY based on some condition(s)

What are IN and NOT IN operators used for?

Testing membership in a set

EX: WHERE guestName IN authorizedGuestList;

What is the EXISTS operator used for?

Testing if a relation holds at least one tuple

What is an informal description of how the count(*) division trick works in SQL?

This solution involves counting the number of distinct items in the divisor relation, and finding all