Write a Basic Query Flashcards
A SQL query is a request for ___________ ?
Information.
Update 10/30/21, 3.3
Is SQL a declarative or imperative programming language?
Declarative (3.6)
We describe the result we want, but we can’t control how the result is generated. (3.6)
Name that Clause!
List the columns that should appear in your result grid.
The SELECT Clause
3.5
Name that Clause!
Specify the data sources used in your query, typically tables.
The FROM Clause
3.5
What is a query also know as?
A SELECT Statement.
3.5
SQL queries must be written with their clauses in what order?
- SELECT
- FROM
- WHERE
- GROUP BY
- HAVING
- ORDER BY
(3.5)
SQL processes its clauses in what order?
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
(3.5)
What does the asterisk mean in the following syntax?
SELECT *
FROM TableName
The asterisk (*) means “all columns,” and we have not limited the rows returned.
(3.7)
List and define the two types of comments?
- Line Comments - start with a double-dash ( – ), which will cause the rest of the line to become a comment.
- Block Comments - start with a forward-slash followed by an asterisk ( /* ), which will cause all text to be treated as a comment until it reaches an asterisk followed by a forward slash ( */ )
(3.7)
Do comments have to be on their own line?
Comments can start at any point during the query, on their own line or not.
(3.7)
How do comments provide a method of troubleshooting?
They can be used to temporarily disable sections of code. This can make it easier to selectively focus your attention on a certain section of code.
(3.8)
What does it mean to “comment out” a section of code?
Putting a section of code in a comment enables easily re-adding the code later by uncommenting the section of code: undoing the commenting syntax.
(3.8)
Which type of comment can be used to comment out code?
Both line comments and block comments can be used to comment out code.
(3.8)
How can you control the order the results are returned?
By using the ORDER BY clause.
3.11
What is the default order of the ORDER BY clause?
Ascending order.
3.11
What is the ascending order for values?
Small to high.
What is the ascending order for text?
A to Z.
How would you reverse sort the results of the ORDER BY clause?
By adding DESC after the column.
Example:
SELECT *
FROM PAT_ENC
ORDER BY CONTACT_DATE DESC
(3.11)
How would you list the columns in the ORDER BY clause?
ORDER BY is followed by a comma-delimited list of columns that you want to sort in order or precedence.
(3.11)
Why would you use the SELECT TOP statement?
To limit results to a number of rows or a percentage of total rows.
Example:
SELECT TOP 5 * FROM PATIENT = would return five rows from the PATIENT table
SELECT TOP 1 PERCENT * FROM PATIENT = would return one percent of the rows in the PATIENT table
(8.5)
The SELECT and SELECT TOP statements are technically different statements. However, it can be useful to think of these as the same statement where the optional TOP keyword is evaluated after what clause?
The ORDER BY clause.
8.5
Where are column aliases defined?
In the SELECT clause.
3.14
Why can you use column aliases in the ORDER BY clause?
Because column aliases are defined in the SELECT clause and the SELECT clause is processed before the ORDER BY clause.
(3.14)
What are two ways for defining aliases?
- You can use the AS keyword in a column alias to make it explicit, but it is not required.
- If the column alias has a space in it, it must be enclosed in double quotes (SQL Server also allows [square brackets] and ‘single quotes’)
(3.14)
