Concepts Flashcards
SQL statement
A block of SQL code that is to be executed on a relational database is known as a SQL statement.
A SQL statement that results in a dataset is called a SQL query
table
A table is a database object made up of rows and columns; each row represents a different entity and each column represents a different attribute about that entity. In relational database terminology, row and record are often used interchangeably.
row
each row represents a different entity
column
each column represents a different attribute about that entity.
referring to a specific column NAME
When referring to a specific column “COLUMN_NAME” in a specific table “TABLE_NAME”, to avoid ambiguity it’s customary to refer to the pair by listing both separated by a period: “TABLE_NAME.COLUMN_NAME”.
primary key
Each table has a primary key: one or more columns whose value(s) uniquely identify a row in that table.
foreign keys
Tables may have one or more foreign keys. A foreign key is one or more columns whose value(s) uniquely identify a row in another table. In other words, foreign keys hold primary key values of another table.
relational database
Special columns hold values that indicate how the different entities relate to each other, hence the term relational database.
Character string
SQL Server Examples: NVARCHAR, VARCHAR
Oracle Examples: VARCHAR2
A combination of characters is often called a string. ‘Bob Thompson’, ‘V13.4’, and ‘-46’ are all valid strings. A string column has a defined maximum length.
Numeric
SQL Server Examples: NUMERIC, FLOAT, BIGINT, INT, TINYINT, BIT
Oracle Examples: NUMBER, FLOAT
Numeric columns contain real numbers. 42, -1, 3.141592653, and 6.022x10^23 are all examples. In general, number columns will have a defined precision and scale. The precision is the maximum number of digits, the scale is the number of digits to the right of the decimal point.
Date/time
SQL Server Examples: DATE, DATETIME, DATETIME2
Oracle Examples: DATE
Date/time columns hold dates and times. Values in these columns may also vary by levels of precision: from days down to fractions of seconds.
Nullable
The intersection of a row and column in a table is a specific value. In some cases, the value may be NULL. This means that the value is not populated. There is nothing there: not ‘’, not 0, not ‘NULL’; nothing. This may indicate that the value was never recorded or that the value isn’t applicable to the row.
A column is nullable if it allows NULL values. Primary keys are inherently non-nullable, but other columns may be non-nullable as well. Whether or not a column is nullable has implications on how the column should be used.
Views
Views are queries that are stored in the database. A view can be accessed as if it were a table. Unlike tables, however, views do not actually hold data. This means that using a view will not necessarily increase the efficiency of a query. However, views can reduce the amount of work that business intelligence developers must do by reducing the complexity of a query.
Examples: V_SCHED_APPT, V_ACCESS_LOG
Stored procedures
A SQL statement stored in your SQL Server so that it can be run as needed. These are often used to populate tables based off of other information already stored in the database.
Example: ESP_F_SCHED_APPT (the script that populates F_SCHED_APPT)
Materialized views
Materialized views are tables populated from queries that run against other tables. Materialized views are often populated by stored procedures. Using materialized views can reduce the amount of work that business intelligence developers must do: one materialized view might take the place of several tables. Materialized views can increase query efficiency; they are like pre-compiled and saved query results.
Examples: F_SCHED_APPT, D_MU_MEASURES
