Introduction to Databases - Module 2 test questions and answers

Forms-Based Query Language uses a GUI-panel (interactive visual components) for the creation of a query entering data values directly into a query templet lend itself well to users who are more accustomed to GUI environments than to typing commands ex: database user defines queries by entering sample data values directly into a query template to represent the result that the database is to achieve Command-Based Query Language uses written text commands for the creation of a query lends itself to users who are familiar with command syntax, possess reasonable typing skills, and prefer to write queries directly within a programming language ex: SQL Structured Query Language (SQL) English-like programming language for manipulating data and other objects in relational databases the universal language for relational databases in that nearly every DBMS in modern use supports it Schema refers to the organizational structure of a database tables in the case of relational databases Object a named data structure in the database tables, columns, views, etc Keywords identifies the operation that is to be performed ex: INSERT, JOIN, etc Clauses what statements have the option to be subdivided into Statement begins with a keyword and ends with a semi-colon Parameters SQL Client the program you use to connect and interact with the database ex: PopSQL Language Elements keywords, operators, constants, etc White Space (Blanks) delimiters between language elements Constants numbers or strings used in SQL statements Numeric Constants may include a decimal point, a plus or munis sign, and scientific notation Character Constants must be inclosed in single quotes ex: 'Kind of Blue' Data Query Language (DQL) statements that query the database but do not alter any data values or database objects (that is, tables) ex: SELECT Data Manipulation Language (DML) statements that modify data values, but not database objects ex: INSERT, UPDATE, DELETE, MERGE Data Definition Language (DDL) statements the create and modify database objects, but not the data values contained within them ex: CREATE, ALTER, DROP, TRUNCATE Data Control Language (DCL) statements that manage security privileges within the database ex: GRANT, REVOKE Result Set rows in the query results Predicate the search argument a clause that can be evaluated at true or false ex: WHERE, HAVING SELECT clause that names the operation and lists the desired column FROM clause that names the source table(s) and/or view(s) * select all columns your results will change whenever columns are added or removed from the table ORDER BY clause to put the result set in a particular sequence ex: ASC (default), DESC WHERE clause to specify an expression that filters rows if the result is true, the row is passed on to the query's result set Logical Operators connects compound expressions in a WHERE clause ex: AND, OR, NOT, >, <, =, >=, <=, etc WHERE NOT reverses the result of a predicate ex: WHERE NOT GENDER = 'M'; WHERE GENDER <> 'M'; %NOW matches any character string that ends with NOW NOW% matches any character string that begins with NOW %NOW% matches any character string that contains NOW, whether at the beginning, the middle, or the end N_W matches any string of exactly three characters, where the first character is N and the third character is W %N_W% matches any string that contains the character N followed by any character, which is in turn followed by the character W and continues with any number of characters LIKE operator that compares a string to a pattern _ positional wildcard Non-Correlated Subquery a query that runs only once before the outer query ex: SELECT name, state FROM addresses WHERE state IN (SELECT state FROM states); Cartesian Product the combination of all rows in one table and all rows in another table this produces a result set with inaccurate information Recursive Join (Self-Joins) joining rows in a table to other rows in the same table Inner Join (Exclusive) the result set includes only rows where matches were found in both tables (unmatched rows are excluded from the results) Outer Join (Inclusive) the result sets includes unmatched rows from either side of the join (the left side or the right side) LEFT JOIN includes all values from the table before the JOIN keyword RIGHT JOIN includes all values from the table after the JOIN keyword SQL Function returns a single value each time it is executed UPPER function on character data to shift all alphabetic letters to upper case Aggregate Functions functions that perform calculations SUM, AVG, MIN, MAX, COUNT COUNT(*) counts rows regardless of data values in the rows COUNT(column_name) counts only non-null values in the named column GROUP BY used if you want the aggregate (function) result to be for groups of rows in the query HAVING like the WHERE clause in that it acts as a filter, but it is applied to each group after grouping has taken place ALTER DDL statement that modifies database objects DROP DDL statement that destroys database objects TRUNCATE DDL statement that instantly clears tables of their of contents String Data Types permit values based on character sets or data bits (letters, numbers, symbols) CHARACTER specifies the exact number of characters (which must be from a character set) that will be stored for each value CHAR CHARACTER VARYING specifies the greatest number of characters (which must be from a character set) that can be included in a value VARCHAR CHARACTER LARGE OBJECT stores large groups of characters, up to the specified amount CLOB NATIONAL CHARACTER operates just like the CHARACTER data type, except that it's based on an implementation-defined character set NCHAR NATIONAL CHARACTER VARYING operates just like the CHARACTER VARYING data type, except that it's based on an implementation-defined character set NCHAR VARYING NATIONAL CHARACTER LARGE OBJECT operates just like the CHARACTER LARGE OBJECT data type, except that it's based on an implementation-defined character set NCLOB BINARY specifies the exact number of bytes of binary data that will be stored for each value BINARY BINARY VARYING specifies the greatest number of binary bytes that can be included in a value BINARY VARYING BINARY LARGE OBJECT stores large groups of bytes, up to the specified amount BLOB Numeric Data Types support data consisting only of numeric digits defined with a precision that specifies the maximum number of digits that can be stored, and an optional scale that specifies the number of digits to be placed to the right of the decimal point Precision refers to total digets Scale refers to number of digits after decimal point Floating-Point Numbers (Approximate Numbers) numeric types where the location of the decimal point can vary from row to row REAL, DOUBLE PRECISION, and FLOAT NUMERIC specifies the precision and the scale of a numeric