Flashcards
SCC 201 - Structured Query Language
Cards in this deck (24)
- 1What facilities does SQL provide?Provides facilities for
– data definition
– data manipulation
– view definition - 2SQL 1 released in 1986, what is this also called?– Also called SQL86
- 3SQL 2 released in 1992, what is this also called?– Also called SQL92
- 4What does SQL 3 contain?– Contains object-relational extensions
- 5What does SQL SELECT clause specify?SQL SELECT clause specifies projection
attributes in relational algebra - 6What does SQL where clause specify?SQL WHERE clause specifies selection
condition in relational algebra - 7What happens if query refers two or more attributes with same name?If query refers to two or more attributes
with same name, attribute name is qualified with relation name
– Prefix attribute name by relation name
and separate the two by a period - 8What does an unspecified where clause indicate?• Indicates there is no condition on tuple selection
- 9What happens If FROM clause contains only one relation?– If FROM clause contains only one relation,
all tuples are selected for query result - 10What happens if FROM clause contains more than one relation?– If FROM clause contains more than one relation, a CARTESIAN PRODUCT of relations is selected for result
- 11Explain the use of * in SQL and what is it used to retrieve?Different from * in relational algebra
• In SQL it is used as a wild card character
– Used to retrieve all attribute values
of selected tuples
– No need to specify all attribute names
in SELECT clause explicitly - 12What is the LIKE operator used for and what happens suring comparison?The LIKE operator is used for substring comparison
• During comparison
– Percent % used to match arbitrary number of characters
– Underscore _ used to match a single character - 13What can standard arithmetic operators { +, –, *, / } be applied to?Standard arithmetic operators { +, –, *, / }
can be applied to numeric values
or attributes with numeric domains - 14What can { =, <>, <, >, <=, >= } be used for and the Concatenate operator || ?. { =, <>, <, >, <=, >= } can be used for comparison
• Concatenate operator || can be used to append one string value to another - 15+ and – can be used to do what? What can an interval value be specified as?+ and – can be used to increment or decrement data, time, timestamp and interval values
– An interval value can be specified as a difference between two date, time, or timestamp values - 16What is the ORDER BY clause use for and what are the keywords used?• ORDER BY clause is used for this purpose
• Default order is ascending
• Keywords ASC and DESC
used to explicitly specify the ordering - 17What is the AS keyword used for? Relate this to the use in nested queriesAS keyword used to rename
both attributes and relations
Note that nested queries are possible in SQL
– AS keyword used to rename intermediate relations when using nested queries - 18How are tables treated?• Tables are treated as multi-sets or bags of tuples
– Duplicate tuples can appear more than once in a table and in result of query - 19Why are duplicate tuples not automatically deleted?• It is expensive
• User may want to see duplicate tuples in result
• Aggregate functions need duplicate tuples (in most cases) to perform correct calculations - 20Why is distinct keyword is used?DISTINCT keyword is used in SELECT clause to eliminate duplicate tuples
- 21State built in aggreagate functions?COUNT
– SUM
– MAX
– MIN
– AVG - 22What is the GROUP BY clause used for and give an example?GROUP BY clause used to group tuples in a relation by value of some attributes so we can apply an aggregate function independently to each group
– For example, average salaries of employees by department - 23Where should Grouping attributes in GROUP By clause also appear in? Describe the values resulting from applying each function?Grouping attributes specified in GROUP BY clause should also appear in SELECT clause
– Values resulting from applying each function to group of tuples will then appear along side values of grouping attributes - 24What are the three commands available to modify database?– INSERT
– DELETE
– UPDATE
Information
- Description
- Flashcards on SCC 201 - Structured Query Language, created by Ayo Olutobi on 22/04/2017.
- Created by
- Ayo Olutobi
- Views
- 0
- Rating