Database Management Essentials provides the foundation you need for a career in database development, data warehousing, or business intelligence, as well as for the entire Data Warehousing for Business Intelligence specialization. In this course, you will create relational databases, write SQL statements to extract information to satisfy business reporting requests, create entity relationship diagrams (ERDs) to design databases, and analyze table designs for excessive redundancy. As you develop these skills, you will use either Oracle or MySQL to execute SQL statements and a database diagramming tool such as the ER Assistant to create ERDs. We’ve designed this course to ensure a common foundation for specialization learners. Everyone taking the course can jump right in with writing SQL statements in Oracle or MySQL.
SELECT statement introduction video lecture
Loading...
來自 University of Colorado System 的課程
数据库管理精要
1281 個評分
University of Colorado System
1281 個評分
課程 1（共 5 門，Specialization Data Warehousing for Business Intelligence）
從本節課中
Basic Query Formulation with SQL
This module is all about acquiring query formulation skills. Now that you know the relational data model and have basic skills with the CREATE TABLE statement, we can cover basic syntax of the SQL SELECT statement and the join operator for combining tables. SELECT statement examples are presented for single table conditions, join operations, and grouping operations. You’ll practice writing simple SELECT statements using the tables that you created in the assignment for module 3.
與講師見面
Michael ManninoAssociate Professor
Business School, University of Colorado Denver
Welcome to Lesson 2 of Module 4 on Basic Query Formulation with SQL.
I'm gonna start with two questions for you.
One basic question on query formulation and
the other question on a fine point of query formulation.
What database details should you know before you try to formulate a query?
When should you use parenthesis in writing condition
to limit rows returned in a result?
Query formulation is an important skill in application development.
Everyone involved in application development
must be competent in query formulation.
Most students will be involved, at least initially,
in application development, rather than as a role as a database specialist.
Database specialists must also understand query formulation and SQL.
Lesson two provides your first look at the SELECT statement.
You will learn to write SELECT statements that use individual tables.
Later lessons will expand the range of select statement coverage and
query formulation skills to multiple tables and row summaries.
This lesson has practical objectives for
you related to the usage of the SELECT statement.
I want you to use either Oracle or MySQL to write and
execute practice problems using a SELECT statement statement involving row
conditions on individual tables.
In this lesson, we will study a simple structure for the SELECT statement.
After this brief introduction,
the lesson will show examples that you can mimic as you complete problems.
The SELECT statement begins with the SELECT's keyword and
a list of column expressions to specify the result table.
I will discuss expressions shortly.
The simplest column expression is just the column name.
The result of the statement in the SELECT clause
is followed by the tables used in the FROM clause.
For this lesson,
we will use statements that have just a single table following the FROM keyword.
The WHERE and ORDER BY clauses are optional.
The WHERE clause indicates conditions that result rows must satisfy.
The ORDER BY clause is used if the result rows should be in a specific order.
The word expression is important in most computer languages,
including the SQL SELECT statement.
In most places in the SELECT statement, whenever a column can be used,
a column expression can also be used.
Examples of simple column expressions are FacSalary * 1.1,
to increase a salary by 10%, and EnrGrade- 1, to reduce a grade by one point.
The WHERE clause can use logical expressions, using the and, or, and
not operators to combine conditions.
Logical expressions have the value of true or false.
Only rows in which the logical expression is true are in the result.
For example the logical expression OffTerm = 'FALL' AND
OffYear = 2012 indicates that rows in the result must be in Fall 2012.
This database diagram shows tables, columns, relationships and data types.
You should know or consult a database diagram when formulating queries.
The examples in this lesson use only single tables.
So relationship details are not necessary to know in this lesson.
Note that this diagram shows Oracle data types
somewhat different than standard SQL data types.
You focus on the three major types of data.
Numeric, being integer, fixed decimal or floating point.
String, char or varchar.
And date.
You will want to use operations and
comparisons with constants that are compatible with the major types of data.
For example, you should use a numeric constant
in a condition with a numeric column, not a string constant.
The first example retrieves all columns and rows from the Faculty table.
The asterisk following the SELECT keyword
means all columns of the tables in the FROM clause.
The absence of a WHERE clause means that all rows of the tables in the FROM clause
are in the result.
Let's execute the first example in the SQL Developer.
The result contains six rows and all columns of the Faculty table.
The second example adds a WHERE clause to restrict Faculty rows in the result
to a specific faculty number.
The statement result is a subset of the first example.
Let's execute the second example in the SQL developer.
The result contains one row in all columns of the Faculty table.
The third example retrieves a subset of the columns of the Faculty table, for
faculty with a rank of full professor and a salary greater than 65,000.
For conditions on text columns, Oracle is case sensitive by default.
Since the values of FacRank were entered with upper case,
the condition will not match correctly if the constant is not upper case.
Let's execute the third example in the SQL developer.
The result contains one row and three columns of the Faculty table.
The fourth example demonstrates duplicate rows in the result.
Since many faculty live in the same city and
state, duplicate rows are in the result.
To eliminate duplicate rows, the DISTINCT keyword should be used.
Let's execute both versions of the fourth example in the SQL Developer.
The first result contains six rows with four duplicate rows.
The second result contains three rows with no duplicate rows,
due to the DISTINCT key word.
Example 5 retrieves the name, that's a first and
last, an inflated salary of faculty hired after 2001.
The expression in the SELECT clause multiplies the faculty salary by 1.1 and
renames the column in the result to IncreasedSalary, using the AS keyword.
Using a name for a computed column avoids a meaningless default name.
The WHERE clause uses an expression to extract the year component
of the FacHireDate column.
Functions to manipulate dates differ across DBMSs.
In Oracle, two functions, to_char and to_number are required.
In MySQL, only one function, date_format, is required.
As you can see, the SELECT statements are not portable across Oracle and
MySQL because of the different date functions in the two DBMS's.
Let's execute the fifth example in the Oracle SQL developer.
The result shows three rows with four columns, including the renamed column for
the computed column.
Now let's execute the statement again without the as keyword to rename
the computed column.
Note that Oracle uses a name with the expression.
Columns using a character string or text data type,
CHAR or VARCHAR, support both exact and inexact matching.
You can use the equality, the equal comparison operator for
exact matching with the string column, as shown in previous examples.
Inexact matching supports conditions that match some pattern
rather than matching an identical string value.
One of the most common types of inexact matching is to find
values having a common prefix such as IS, for information systems.
Example 6 uses the LIKE operator along with the pattern matching character,
the percent symbol, to perform prefix matching.
The string constant, IS%,
means match strings beginning with IS and ending with anything.
The wildcard character % matches any string.
Note that the pattern matching character only has a special meaning
with the LIKE operator, not with the equality operator.
The SQL standard has other pattern matching characters, but
the wildcard character is the most important.
Let's execute the sixth example in the Oracle SQL Developer.
The result contains ten rows,
with a course number beginning with IS in each result row.
Example 7 depicts range matching on a column with a date data type.
In both Oracle and MySQL, single quotation marks enclose date constants.
In Oracle SQL the standard date format uses a two digit day,
a three letter month abbreviation, and a four digit year.
In MySQL, the standard date format is the four digit year,
two digit month and two digit day.
Date columns can be compared, just like numbers,
with the usual comparison operators, such as equal to, less than and so on.
The BETWEEN AND operator defines a closed interval including the end points.
Let's execute the seventh example in the Oracle SQL developer.
The result contains two rows with hiring date between January 1st 2004 and
December 31st, 2005, for each row.
You should not use the LIKE operator in pattern matching characters
in conditions with date columns.
Some DBMSs allow the LIKE operator, but is not portable across DBMSs.
And the results may vary within a given DBMS.
You should treat date columns as numeric, not text.
Besides testing columns for
specified values, you sometimes need to test the lack of a value.
No values are used when there's no normal value for a column.
A null can mean that the value is unknown or the value is not applicable to the row.
For the Offering table, a null value for
the fac number column, means that the instructor is not yet assigned.
Testing for null values is done with the IS NULL comparison operator
as shown in Example 8.
Let's execute the eighth example in the Oracle SQL developer.
The result contains one row for
the only summer 2013 course without an assigned faculty.
Example 9 depicts a complex logical expression,
involving both logical operators, AND, and OR.
Example 9 retrieves offerings in FALL 2012 or WINTER 2013.
I've seen frequent mistakes by students mixing the AND
and OR operators without using parentheses.
So I strongly recommend using parenthesis when mixing AND and
OR in a logical expression.
Otherwise, the reader of the SELECT statement
may not understand the grouping of the AND and OR conditions.
Without parenthesis, you must depend on the default way that AND
and OR conditions are grouped.
The reader of the statement may not be aware of the default grouping.
In addition, the statement may not be portable across DBMSs without parentheses.
Let's execute the ninth example in the SQL developer.
The result table contains five rows for the offerings in fall 2012 or winter 2013.
Let's wrap up this lesson that gave you an initial look at the SQL SELECT statement
with examples on single tables, an important sub set of the SELECT statement.
You were shown examples with conditions on numeric columns, stringer text columns,
and date columns.
Some of the examples used expressions, inexact matching with a LIKE operator,
column renaming, the DISTINCT keyword and
logical expressions, combining the AND and OR operators.
Now, let's return to the opening questions of the lesson.
For single table problems, you must know the columns of a table and data types.
Lessons three and
four in module four will focus on problems involving multiple tables, so that you
must know the relationships in a database in addition to the columns and data types.
When mixing logical operators AND and OR you should always use parentheses
to avoid errors and to help the reader understand the meaning of your statement.
I encourage you to work lots of problems to master query formulation and
the SELECT statement.
