State 7020 Exam 1 Reading Assignments

Table

a structured file that can store data of a specific type. A table might contain a list of customers, a product catalog, or any other list of information

Table Names

Every table in a database has a name that identifies it. That name is always unique —meaning no other table in that database can have the same name

Schema

Information about database and table layout and properties.

Column

A single field in a table. All tables are made up of one or more columns. The vertical columns in the grid are the table columns

Datatype

A type of allowed data. Every table column has an associated datatype that restricts (or allows) specific data in that column.

Row

A record in a table. Horizontal rows are the table rows.

Primary Keys

A column (or set of columns) whose values uniquely identify every row in a table. The primary key is used to refer to a specific row

Conditions of a primary key

• No two rows can have the same primary key value.

• Every row must have a value in the primary key column(s). (So, no NULL values.)

• Values in primary key columns should never be modified or updated.

• Primary key values should never be reused. (If a row is deleted from the table, its primary key may not be assigned to any new rows in the future.)

SELECT statement

to retrieve information from one or more tables.

FROM keyword

specifies the name of the table from which to retrieve the data.

Retrieving multiple columns

To retrieve multiple columns from a table, the same SELECT statement is used. The only difference is that multiple column names must be specified after the SELECT keyword, and each column must be separated by a comma

Retrieving all columns

using the asterisk (*) wildcard character

Retrieving distinct rows

SELECT DISTINCT tells the DBMS to only return distinct (unique) rows. If used, the DISTINCT keyword must be placed directly in front of the column names.

DISTINCT keyword example

SELECT DISTINCT vend_id

FROM Products

TOP keyword

limit the top number of entries

TOP keyword example

SELECT TOP 5 prod_name

FROM Products

Clause

A clause usually consists of a keyword and supplied data. An example of this is the SELECT statement’s FROM clause

ORDER BY clause

takes the name of one or more columns by which to sort the output.

WHERE clause

data is filtered by specifying search criteria

WHERE clause operators

= Equality

<> Nonequality

!= Nonequality

< Less than

<= Less than or equal to

!< Not less than

> Greater than

>= Greater than or equal to

!> Not greater than

BETWEEN Between two specified values

IS NULL Is a NULL value

Operator

A special keyword used to join or change clauses within a WHERE clause. Also known as logical operators.

AND keyword

A keyword used in a WHERE clause to specify that only rows matching all the specified conditions should be retrieved

OR keyword

OR operator instructs the database management system software to retrieve rows that match either condition.

Wildcard

Special characters used to match parts of a value.

LIKE operator

Within a search string, %means match any number of occurrences of any character.

LIKE Operator example

SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '%bean bag%'

Brackets in Wildcards

The brackets ([]) wildcard is used to specify a set of characters, any one of which must match a character in the specified position

Brackets in wildcards example

SELECT cust_contact

FROM Customers

WHERE cust_contact LIKE '[^JM]%'

Field

Essentially means the same thing as column and often used interchangeably, although database columns are typically called columns and the term fields is usually used in conjunction with calculated fields

Concatenate

Joining values together (by appending them to each other) to form a single long value.

Concatenate example

SELECT vend_name + '(' + vend_country + ')'

FROM Vendors

ORDER BY vend_name

RTRIM()

function trims all space from the right of a value. When you use RTRIM(), the individual columns are all trimmed properly.

RTRIM () Example

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'

FROM Vendors

ORDER BY vend_name;

Common Manipulation Functions

• LEFT() (or use substring function

  • Returns characters from left of string

• LENGTH() (also DATALENGTH() or LEN())

  • Returns the length of a string

• LOWER()

  • Converts string to lowercase

• LTRIM()

  • Trims white space from left of string

• RIGHT() (or use substring function)

  • Returns characters from right of string

• RTRIM()

  • Trims white space from right of string

• SUBSTR() or SUBSTRING()

  • Extracts part of a string (as noted in Table 8.1)

• SOUNDEX() Returns a string’s SOUNDEX value

• UPPER() Converts string to uppercase

DATEPART() function

returns a part of a date. DATEPART() takes two parameters: the part to return and the date to return it from.

DATEPART() function example

SELECT order_num

FROM Orders

WHERE DATEPART(yy, order_date) = 2020;

Aggregate Functions

Functions that operate on a set of rows to calculate and return a single value

AVG() Function

used to return the average value of a specific column by counting both the number of rows in the table and the sum of their values. AVG() can be used to return the average value of all columns or of specific columns or rows.

AVG() Function example

SELECT AVG(prod_price) AS avg_price

FROM Products

COUNT() function

• Use COUNT(*) to count the number of rows in a table, whether columns contain values or NULL values.

• Use COUNT(column) to count the number of rows that have values in a specific column, ignoring NULL values.

MAX() Function

returns the highest value in a specified column.

MIN() Function

returns the lowest value in a specified column.

SUM() Function

return the sum (total) of the values in a specific column.

GROUP BY clause

instructs the DBMS to sort the data and group it

• must come after any WHERE clause and before any ORDER BY clause.

SQL Ordering

1. SELECT

2. FROM

3. WHERE

4. GROUP BY

5. ORDER BY

Query

usually used to refer to SELECT statements

Subquery

Subqueries are always processed starting with the innermost SELECT statement and working outward. When the preceding SELECT statement is processed, the DBMS actually performs two operations.

Subquery example

SELECT cust_id

FROM Orders

WHERE order_num IN (SELECT order_num

FROM OrderItems

WHERE prod_id = 'RGAN01')

INNER JOIN

a join based on the testing of equality between two table

UNION operator

return the results as a single query result set.

UNION operator example

SELECT cust_name, cust_contact, cust_email

FROM Customers

WHERE cust_state IN ('IL','IN','MI')

UNION

SELECT cust_name, cust_contact, cust_email

FROM Customers

WHERE cust_name = 'Fun4All';

structured data

tables with well-defined rows and columns

entity relationship diagrams

data model

definition of the tables, the columns, and the relationships among them

logical data model

explains the database in terms that business users understand. The logical data model communicates the contents of the database because it defines many business terms and how they are stored in the database

physical data model

explains how the database is actually implemented. That is, every entity in the logical data model corresponds to a table in the database; every attribute corresponds to a column.

Entity-Relationship Diagram

“relational databases” refers to the fact that different tables relate to each other via keys, and to the fact that columns in a given row relate to the values for that column via the column name

foreign key

a column whose contents are the primary key of another table

dataflow

graphical way of visualizing data transformations

nodes

dataflow diagram transform data, taking zero or more inputs and producing output

edges

dataflow diagram are pipes connecting the nodes

READ operator

reads all the columns of data from a database table or file

OUTPUT operator

creates desired output, such as a table in a row-column format or some sort of chart based on the data.

IN statement

used in a WHERE clause to choose items from a set.