Term: What is the simplest SQL retrieval?
Term: What is the purpose of the Where clause when using SELECT?
Term: What are data types and why are they important?
Term: Provide an example of the GROUP by clause, when would you use this clause?
Term: What is an aggregate function (provide 4)? How are they used?
Term: Provide example using the COUNT() function
Term: How is a field name qualified? How is this used when using a JOIN command, why is it important?
Term:
Definition:
What is the simplest SQL retrieval?
Select and followed by a * will retrieve all fields in the query result.
What is the purpose of the Where clause when using SELECT?
Any condition that you want to apply to the data you want to retrieve
What are data types and why are they important?
Used to specify the “type” of data to be stored in a column. A field's data type determines what kind of data it can store. The data types are: INTEGER, SMALLINT, DECIMAL CHAR, and DATE
Provide an example of the GROUP by clause, when would you use this clause?
The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns. Example: SELECT RepNumn, COUNT (*), AVG (Balance) FROM Customer GROUP BY RepNum ORDER BY RepNum
What is an aggregate function (provide 4)? How are they used?
Aggregate functions perform a calculation on a set of values and return a single value. Except for COUNT, aggregate functions ignore null values. Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement. AVG - Returns the average of the values in a group. Null values are ignored. VAR - Returns the statistical variance of all values in the specified expression. MIN - Returns the minimum value in the expression COUNT - Returns the number of items in a group. COUNT always returns an int data type value
Provide example using the COUNT() function
SELECT COUNT(*) FROM Orders
How is a field name qualified? How is this used when using a JOIN command, why is it important?
To qualify a field name, precede the name of the field with the name of the table, followed by a period. In the SQL command line WHERE clause, the data to be retrieved is restrictged to only shoe rows from the 2 tables that match – have common values in matching fields. It is important because you need to “qualify” a field name to specify the particular field you are referencing. Since you are using 2 or more tables they must be included in the SQL query. To join the tables you use the clauses SELECT, FROM and WHERE.
