Databases Exam –Key Questions, Answers & Study Guide.

Databases Exam –Key Questions, Answers & Study Guide. SQL is a domain-specific programming language designed for managing and communicating with data held in a relational database management system SQL is used for: -creating databases -adding, modifying and deleting database structures -inserting, deleting and modifying records in databases -querying databases Data Definition Language (DDL) used to create and modify the structure of the database example: CREATE ALTER or DROP Data Manipulation Language (DML) used to insert, modify, delete and retrieve data example: INSERT INTO UPDATE or DELETE Data Retrieval Language (DRL) used to query or retrieve data from a database example: SELECT Data Control Language (DCL) used for data access control Transaction Control Language used for managing database transactions Keywords These have a very specific meaning in the language. The statement must have at least one. These often define the operation that is performed Identifiers These refer to the names of the tables and columns that are called or manipulated Null Value is a marker for data that has not been entered or is missing, unknown, or inapplicable Not Null Constraint is only a column constraint and not a table constraint; if you don't specify a , the column will accept null values by default Unique Constraint forces each value of a column or table to be unique Check Constraint allows for limiting a particular column based on a particular value rules These include: minimum or maximum value specified value range of values t CAST changes the data type of a column in the query Concatenating Strings combining strings together Trim removes trailing and leading blank spaces Upper displays columns as UPPER case Lower displays column as lower case INITCAP displays column as proper case Length Returns the length in characters of a string Substring is any sequence of contiguous characters from the source string Right pulls the right n characters of a string Left returns left n characters of a string Mid Returns the characters n from the left and o from the right Position returns the position of a given string replace replaces value 1 with value 2 in a column count returns the number of times a column appears count distinct returns the number of unique columns in a array avg calculates the average of a column sum adds the values in a column max returns the maximum value in a column min returns the minimum value in a column Group By returns a single value for each value in the column(s) specified in the clause Having if you want to use a filter that contains an aggregate function, you can't use WHERE Date_Part pulls out a specific date part from a date Extract same as date_part current_date/time returns the current date or current time age returns the difference between two date/time columns make_date/time creates a date or time NOW returns the current date and time Date_Trunc truncates date/time to specified interval Joins allows you to combine data from two relational tables for additional insights Inner Join selects all rows from both tables as long as there is a match between the columns in both tables Left (or Right) Join returns all rows from the left (or right) table, even if there are no matches in the right (or left) table Full joins selects all rows from both tables even if there is no match Cross Joins returns all rows from the first table in which each row from the first table is combined with all rows from the second table natural joins special case of inner join; compares all the columns in one table with corresponding columns that have the same name in the other table Where It's possible to use the WHERE syntax instead of ON Set Operations UNION, INTERSECT, & EXCEPT used to combine the results of two or more SELECT statements that are union compatible Union used to combine the union compatible results of two SELECT statements by listing all rows from the result of the first SELECT statement and all rows from the result of the other SELECT statement Intersect combines the results of two SELECT statements that are union compatible by listing every row that appears in the result of BOTH the SELECT statements Except combines the results of two queries into a single result that has the rows that belong to only the first query Subqueries when you embed one SELECT statement inside another nested subquery is a type of subquery where the outer query uses the results of the inner query to select data referential integrity constraint in each row of a relation containing a foreign key, the value of the foreign key EITHER matches one of the values in the primary key column of the referred relation OR the value of the foreign key is null Delete Restrict option does not allow a record to be deleted if its primary key value is referred by a foreign key Delete Cascade option allows a record to be deleted if its primary key value is referred to by a foreign key value Delete Set-to-Null option allows a record to be delete if its primary key value is referred to by a foreign key value returns it to a null value Delete Set-to-Default option allows a record to be deleted if its primary key value is referred to by a foreign key value allows you to pick the number Update Restrict option does not allow a record to be updated if its primary key value if its primary key value is referred to by a foreign key Update Cascade option allows a record to be updated if its primary key value is referred to by a foreign key value Update Set-to-Null option allows a record to be updated if its primary key value is referred to by a foreign key value sets value to null update set-to-default option allows a record to be updated if its primary key value is referred to by a foreign key value allows you to set the number Index mechanism for increasing the speed of data search and data retrieval on relations with a large number of records accuracy the extent to which data correctly reflects the real-world instances it is supposed to depict uniqueness requires each real-world instances to be represented only once in the data collection completeness the degree to which all the required data is present in the data collection consistency the extent to which the data properly conforms to and matches up with the other data timeliness the degree to which the data is aligned with the proper time window in its representation real world conformity the extent to which the data conforms to its specified format preventive data quality actions actions taken to preclude data quality problems corrective data quality actions actions taken to correct the data quality problems data entry forms enable data input and retrieval for end users data manipulation forms & search forms can be used to delete and update items as well reports present the data and calculations on the data from one or more tables from the database in a formatted way application development component used to develop front-end applications view materialized saving a view as an actual physical table catalog the data dictionary created by the DBMS authentication login procedure using user ID and password access privileges assigned to the database user account authorization matrix implements the access privileges provided by the DBMS but managed by the DBA encryption scrambles data so that information becomes unreadable unless someone has an encryption key encryption key information scrambling algorithm decryption key reverts the informations to its original state backup saving additional physical copies of the data recovery recovering the content of the database after a failure recovery log logs database update & ensures against loss of updates checkpoint part of a recovery log indicates a point when updates are written on the disk commit causes all the updates to be recorded on the disk rollback rolls back all the updates since the last commit complete mirrored backup ensures against complete database destruction query optimization examining multiple ways of executing the same query and choosing the fastest option query optimizer DBMS feature that identifies the possible ways to execute the query and chooses the fastest query plan query cost the time length of execution query hint overrides the default behavior of the query optimizer operational information the information collected and used in support of day to day operational needs in business and other organizations analytical information the information collected and used in support of analytical taks integrated the data warehouse integrates the analytically useful data from the various operational databases refers to this process of bringing the data from multiple data sources into a singular data warehouse structured repository the data warehouse is a database containing analytically useful information any database is a structured repository with its structure represented in its metadata subject orientated refers to the fundamental difference in the purpose of an operational database system and a data warehouse A data warehouse is developed to analyze specific business subject areas whereas an operational database supports specific operations enterprise wide refers to the fact that the data warehouse provides an organization wide view of the analytically useful information it contains time variant refers to the fact that a data warehouse contains slices or snapshots of data from different periods of time across its time horizon with the data slices, the user can create reports for various periods of time within the time horizon retrieval of analytical information a data warehouse is developed for the retrieval of analytical information and is not meant for direct data entry by the users detailed and/or summarized data may include the or or both a data warehouse that contains the data at the first level of detail is the most powerful Enterprise Resource Planning integrated information system data governance formally regulating how, when and by whom the organizational data and metadata are created, stored, updated and archived data steward responsible for the proper use of the data in database data custodian responsible for the technical aspects of data management and use, such as protection, transport, and storage of data master data management a data governance initiative master data authenticated quality version of the key data that provides a common point of reference for the organization's information systems centralized approach single central copy of the master data is used by operational information systems registry dispersed master data connected via a central master data registry hybrid approach central master data copy while individual operational systems may contain their own copies of the master data as well NOSQL databases that is not only based on relational model Big Data massive volumes of diverse and rapidly growing data that are not formally modeled 3 V's: volume, velocity, and variety Mapreduce a computation framework for dealing with Big Data