Help
Options
Didn't know it?
click below
click below
Knew it?
click below
click below
Don't Know
Remaining cards (0)
Know
retry
shuffle
restart
0:00
Embed Code - If you would like this activity on your web page, copy the script below and paste it into your web page.
Normal Size Small Size show me how
Normal Size Small Size show me how
DBConceptsCh3
Structured Query Language
| Question | Answer |
|---|---|
| use to add, modify, & drop columns & constraints | ALTER TABLE statement |
| organization that creates & publishes SQL standards | American National Standards Institute (ANSI) |
| can be used to assign column name to one that has no name, in SELECT statement | AS keyword |
| function to sort order of output, in ORDER BY clause, as ascending | ASC keyword |
| wildcard character to represent 1/more unspecified characters; in Access | asterisk (*) |
| calculates the average of values found in a specific column of each group; commonly used SQL function; can be used as category on which to sort | AVG |
| statement of policy in business that restricts ways in which data can be inserted, updated, or deleted in database | business rule |
| can be any of following: restrict, no action, cascade, set to null, or set to default; means same change should be made to related column in specified table | CASCADE keyword |
| list of values that limits what values are valid for the column; example, you can specify only the values 1 or 2 are valid | CHECK constraint |
| identifies table constraints; can be used to implement various constraints | CONSTRAINT keyword |
| in SQL, function that counts number of row in query result, regardless of column data type; built-in function | COUNT |
| SQL command used to create database relations; shows how to define primary keys of table by using a table constraint | CREATE TABLE statement |
| language used to describe processing of database; used to create & alter database structures | data definition language (DDL) |
| language used to describe processing of database; used to query & modify data base components | data manipulation language (DML) |
| language for defining & processing database intended to be embedded in programs written in another language | data sublanguage |
| can be used to set initial values | DEFAULT keyword |
| used to remove rows from a table; can remove one or more rows with a single command | DELETE command |
| function to sort order of output, in ORDER BY clause, as descending | DESC keyword |
| determines how many unique values exist in column; can be used to automatically eliminate duplicates | DISTINCT keyword |
| used to remove database objects like tables, users, indexes, and constraints | DROP statement |
| original join operation; sometimes referred to as inner join or | equijion |
| language whose tags can be extended by document designers | Extensible Markup Language (XML) |
| specifies conditions for grouping rows when determining query results, to group by common values; part of SQL SELECT statement | GROUP BY clause |
| specifies conditions used to determined which rows are in groupings in GROUP BY clause; part of SQL SELECT statement | HAVING clause |
| will start by number M & increase by increment of N, for each additional row; exact techniques used to define surrogate key sequences vary from DBMS-DBMS, consult docs for specific product used | IDENTITY (M,N) phrase |
| relational algebra operation on 2 relations that produces a 3rd relation; in theory restrictions other than equality are allowed however such non-equal joins are not used in practice | join operation |
| join operation is also known as | inner join |
| allows you to add 1/more rows to an existing table | INSERT statement |
| can be used in WHERE clause to search for values that are not NULL | IS NOT NULL phrase |
| can be used in WHERE clause to search for null values | IS NULL keyword |
| can be used for joins; rows that do not match join conditions do not appear in results | JOIN...ON syntax |
| used with JOIN...ON syntax in order to join any rows except those on left side | LEFT keyword |
| returns largest value found in specific column in group | MAX |
| calculates smallest value found in specific column in group | MIN |
| not necessary to cascade updated to referenced primary key | NO ACTION keyword |
| to select rows, in WHERE clause, that do not have a specified value; column must not be equal to all values in list | NOT IN phrase |
| to select rows, in WHERE clause, that do not have a specified value | NOT keyword |
| SQL expression to select impartial values, in WHERE clause; pattern matching operator | NOT LIKE phrase |
| indicates that a value must be supplied when a new row is created | NOT NULL constraint |
| indicates that null values are allowed; means row can be created without a value for this column/row | NULL constraint |
| shows what action should be taken if row in table deleted | ON DELETE phrase |
| shows what action should be taken is a value of primary key in table changes | ON UPDATE phrase |
| specifies how query results should be sorted when they are displayed; part of SQL SELECT statement | ORDER BY clause |
| join in which all rows of table appear in resulting relation, regardless of whether they have a match in join condition | outer join |
| wildcard character used to represent series of unspecified characters; SQL; used to represent 1/more characters in a pattern | percent sign (%) |
| style of query interface, 1st developed by IBM but now used by other vendors, that enables users to express queries by providing examples of results they seek | Query by Example (QBE) |
| wildcard character to represent single unspecified character | question mark (?) |
| used with JOIN...ON syntax in order to join any rows except those on right side | RIGHT keyword |
| any of the functions, COUNT, SUM, AVG, MAX, OR MIN, in SQL | SQL built-in functions |
| basic structure of SQL query | SQL SELECT/FROM/WHERE framework |
| virtual relation constructed from single SQL SELECT statement; have at most 1 multivalued path | SQL view |
| data sublanguage for defining structure & processing of relational database; can be used as stand-alone query language, or embedded in application programs | Structured Query Language (SQL) |
| SELECT statement that appears in WHERE clause of SQL statement; can be used to apply 2+ SQL statements | subquery |
| in SQL, function that adds up set of numbers; totals values found in specific column in group; built-in function | SUM |
| standard SQL wildcard character used with LIKE operator to represent single, unspecified character | underscore symbol (_) |
| requires that every value entered into that column be unique, there cannot be any duplicated values | UNIQUE constraint |
| modify values of existing data; | UPDATE...SET command |
| represent unknown characters in a pattern | wildcard characters |
| in most cases, data __ is a procedural language; such as, COBOL, C#, or Visual Basic | sublanguage |
| data sublanguage is a(n) __ __ language because it contains only constructs for data definition & processing | incomplete programming |
| all rows in left-handed relation appear in resulting relation | left outer join |
| all rows in right-handed relation appear in resulting relation | right outer join |
| SQL was developed by IBM and accepted as __ __ by ANSI | national standard |
| can be nested within each other | subqueries |
| question mark (?) is used in __ __ queries, to specify a single character | Access 2010 |
| underscore symbol (_) is used in __ __, to specify a single character | standard SQL |
| asterisk (*) is used in __ __ queries to represent 1/more unspecified characters | MS Access |
| percent sign (%) used in __ __ to specify multiple characters | standard SQL |
| SQL not __ __ language, rather a data sublanguage | complete programming |
| for SQL to obtain full programming language, its statements must be embedded in __ language, such as VBScript, or __ __ languages, such as Java/C# | script; in programming |
| SQL statements can be submitted __, using a DBMS-supplied command prompt | interactively |
| SQL endorsed as national standard by __, in 1986, 1989, & 1992 | ANSI |
| SQL is __ oriented; developed prior to graphical user interfaces & only requires __ __ | text; text processor |
| you cannot do everything with __ __ that you can do with SQL | graphic tools |
| must use SQL to generate SQL statements __ in programming code | dynamically |
| MS Access presents variant of Query by Example (QBE) graphic interface for general use, while using | SQL behind the scenes |
| data definition language (DDL) & data manipulation language (DML) are both categories of | SQL commands |
| MS Access defaults to __ version, instead of ANSI SQL-92 standard | ANSI-89 |
| ANSI-89 differs significantly from SQL-92, therefore some features of SQL-92 language | will not work in MS Access |
| MS Access 2010, 2003, & 2007 versions do contain setting allowing you to use | SQL-92 instead of default SQL-89 |
| the one permanent workaround to SQL commands & clauses that do not work in MS Access ANSI-89 SQL is to choose to use | SQL-92 syntax option |
| column name, column data type, & optionally, a constraint on column values | three parts of column |
| SQL commands are __ __ | case insensitive |
| standard in SQL is to make SQL commands __ | uppercase |
| in SQL, this is done with the SELECT command | data retrieval |
| in SQL, does not add new data or update existing data | SELECT command |
| SELECT colum1, column2, ... columnN FROM table | basic format of the SQL SELECT command |
| SELECT clause & FROM clause | 2 parts of SELECT command |
| used to list columns & expressions you want displayed in output | SELECT clause |
| each column in SELECT clause is followed by a(n) __ except the last column | comma |
| used to list tables where columns exist; two/more tables will be listed when data selected from more than one table | FROM clause |
| will always have SELECT clause & FROM clause | SELECT statements |
| Most (but not all) databases that process SQL statements require statement to end with a(n) __ | semi-colon |
| uses a semi-colon to end each SQL statement, but it is optional | MS Access |
| one that displays all columns & all rows from single table; SQL uses the asterisk to indicate all columns | easiest SELECT statement |
| is to preface tables with tbl code | standard in Access |
| SELECT statement also allows to select any __ columns from table & present those columns in __ __ | specific; any order |
| columns in SELECT clause do not have to be __ | adjoining |
| enables use to retrieve only rows that meet a specific condition | WHERE clause |
| SELECT colum1, column2, ... columnN FROM table | WHERE clause format |
| used in a WHERE clause; is equals operator (=) | most common operator |
| any text data in a database usually has to be __ __ | matched exactly |
| to identify text data in SQL statement it will be made | inside quotation marks |
| tell SQL processor to match this data exactly - including case | quotation marks |
| comparing numbers is different than | comparing text data |
| placed inside quotation marks | text data |
| not placed inside quotation marks | numeric data |
| a date must be placed with a pair of pound signs in | Access |
| is usually more specific to the database software you are using | comparing dates |
| would not be very useful if you could only select data based on the equality of two items | data retrieval |
| important to be able to find rows using inequalities like | greater than & less than |
| =, <, >, <=, >=, <>, !, LIKE, BETWEEN, IN, are all | SQL Comparison Operators |
| match a list operator; use of WHERE clause to specify column should have one of a set of values | IN |
| match range of values operator; used for ranges of values, in WHERE clause | BETWEEN |
| SQL expression to select partial values, in WHERE clause; pattern matching operator; uses the % and the _ characters | LIKE |
| not operator | ! |
| not equal to operator | <> |
| grater than or equal to operator | >= |
| less than or equal to operator | <= |
| greater than operator | > |
| less than operator | < |
| equal to operator | = |
| <, >, <=, >= all work just like the same operators in | algebra |
| may be used to match on a specific list | IN operator |
| are usual characters for implementing "pattern matching;" however, not all databases implement these | % and _ characters |
| can be used to create compound WHERE clauses | AND and OR keywords |
| use when condition that precedes it and condition that follows it must both be true | AND keyword |
| use when either condition that precedes it or condition that follows it must be true | OR keyword |
| tricky part of using AND and OR is when they are | mixed in same WHERE clause |
| AND condition will be __ __ even if it is not listed __ in WHERE clause | evaluated first; first |
| ideally you should use parenthesis (even when not required) to make your intention clear when using both | AND and OR operators in WHERE clauses |
| using AND and OR operators can make SQL statements __ __ | quite complex |
| can use as many __ __ as you wish | AND operators |
| just as you can use multiple AND operators, you can use | multiple OR operators |
| title assigned to each column in SQL query will be __ __ from database by default | column name |
| can substitute a column name, for a(n) __ __ title, by using a column alias | more descriptive |
| follows column it renames and is usually prefaced by keyword AS; can be more than 1 word; different database software applications implement it in slightly different ways | column alias |
| must place double quotes around column alias that includes spaces; keyword AS is optional | in Oracle |
| use [ and ] around column alias; keyword AS is required | in Access |
| Access has __ __ of handling a column alias that is the same as the column heading; solution is to __ column name | unique way; capitalize |
| in Access, you can __ __ of column heading without using an alias | change case |
| without a(n) __ __ __, output will be displayed in an undetermined sequence | ORDER BY clause |
| in most database software applications, order is by __ __ or __ that the row was added to table | primary key; sequence |
| SELECT column1, column2, column3, ..., columnN FROM table ORDER BY expression1, expression2, ..., expression3; | ORDER BY clause format |
| generally you list __ __ for expression1, expression2,...; but sequence can be based on other types of __, like arithmetic formulas and functions | column name; expressions |
| column used in ORDER BY clause does __ __ to appear in SELECT clause | not need |
| in ORDER BY clause, can also __ __ more than one expression | sequence on |
| in ORDER BY clause, can __ __ expression instead of column | sort on |
| in ORDER BY clause, order of output can be sorted into __ or __ __ | descending; ascending sequence |
| in ORDER BY clause, the order options that the output can be sorted into is controlled with __ & __ __ | ASC; DESC keywords |
| in ORDER BY clause, __ __ is the default order that output can be sorted into | ascending sequence |
| ASC keyword in ORDER BY clause statement is __ | optional |
| output, in ORDER BY clause, will default to __ if neither ASC, nor DESC, is specified | ascending |
| SQL function can be used to complete some __ tasks | predefined |
| exist for determining average of column's values, largest/smallest values in group, & sum of group | SQL functions |
| one of most powerful features of relational database; feature that enables data integration w/minimal data redundancy | linking/joining databases |
| selecting data from more than 1 table is usually (but not always) done by __ __ fields (columns) in 2 tables | matching common |
| process of selecting data from multiple tables | joining |
| SQL statement that joins two or more tables, creates a(n) __ table | virtual |
| when joining tables, this displays data from multiple tables as if it were 1 table | virtual table |
| virtual table exists only as __ __ SELECT statement; it does not exist __ in database | result of; permanently |
| in any SELECT statement, can always __ column name w/table name in which it exists and a(n) __/__ | preface; dot/period |
| providing table name and the dot, is often preferred because it __ to database software processing, the statement, to which __, that the columns belong to | hints; table |
| without table name, software must look-up columns in __ __ entry for the correct table, to which the specified column belongs | data dictionary |
| table name & period are often __, but situations arise in __ __ where it will be required | optional; table join |
| there are two parts to __ __ in SELECT statement | joining tables |
| all tables involved are listed in FROM clause, is __ __ of joining tables in SELECT statement | 1st part |
| matching columns from each table are listed in WHERE clause, is __ __ of joining tables in SELECT statement | 2nd part |
| matching columns, when joining tables in SELECT statement, are often a(n) __ key in 1 table & a(n) __ key in different table | primary; foreign |
| when joining tables in SELECT statement, foreign key could also be in same table as primary key for a(n) __ __ | unary relationship |
| matching of a common column accomplishes a(n) | join |
| table joins enable us to treat __ table as one table, so we can retrieve information from __ | combined; both |
| when joining tables, SELECT clause lists columns from both tables as if the two tables were | really just one |
| to accomplish a join for more than 2 tables, you simply use __ keyword and create another __ clause | AND; WHERE |
| you will always have __ __ expression in the WHERE clause, than you have tables to join | one less |
| if you have N tables to to join, you will have __ expressions in WHERE clause | N - 1 |
| if same column name exists in 2 tables, you must __ that column name with the table name and a dot (.) so the SQL processor will know from which __ to access the column | precede; table |
| when same column exists in 2 tables, preceding that column name with table name and dot (.); allows SQL processor to know from which table to access the column | "qualifying" the column name |
| when we need to link tables that do not have common column, we will have to get the data we want by going through a(n) | third table that relates to both |
| column alias will __ __ be used for group function column | almost always |
| if column alias is not used, for group function column, column heading will default to __ or actual __ __ itself | ExpressionN; group function |
| N is sequentially increasing number starting with 1; column heading default when group function column alias not used | ExpressionN |
| one of the restrictions imposed, when using the GROUP BY, is columns in SELECT clause must either be used in a(n) __ __ or column must be __ __ GROUP BY clause | group function; listed in |
| when using the COUNT group function, you have a(n) | choice of columns |
| when using the COUNT group function, you have to be careful not to pick a column that | allows null values |
| when using the COUNT group function, most database software will not count a column | with a null value |
| easiest way to avoid null value problem, when using COUNT group function, is to pick __ __ since it can never be __ | primary key; null |
| MAX and MIN functions can also be applied to __ columns | date |
| when using MAX function with a date, __ __ is selected | most current |
| when using MIN function with a date, the __ __ is selected | oldest date |
| you cannot __ rows selected by group function using a WHERE clause | restrict |
| group function __ __ in a WHERE clause | can't appear |
| HAVING clause is similar to the WHERE clause, in that __ __ rows selected for query statement | both restrict |
| requires one of arguments to be a group function & to have group function in SELECT clause | HAVING clause |
| __ clause can use AND and OR keywords to construct compound conditions | HAVING |
| HAVING clause can also use __ comparison operators __ __ can use (=, >, >=, <, <=, <>, BETWEEN, IN, LIKE, IS NULL) | all; WHERE clause |
| MS Access does not allow for INSERT statements to be processed in the __ __ | SQL window |
| __ INSERT adds one row | simple |
| can add __ __ with INSERT, by using subquery | multiple rows |
| INSERT INTO tablename (column1, column2, .... columnN) | INSERT statement format |
| SQL standard provides quite a bit of __ for INSERT statement | flexibility |
| longest form of the INSERT statement is to specify __ column names & data for __ column; data is in same order as column name | all; every |
| in INSERT statement, it is possible to list columns in | any order |
| in INSERT statement, if you list columns in any order you must match order of __ to __ of columns | data; order |
| in INSERT statement, if you are listing data for __ __ you do not need to specify column __ at all; if all columns in same order as columns in table | every column; names |
| will allow an INSERT statement without column names, when listing data for all columns in same order as columns in table | relational databases |
| if there is a __ __ in a column, then you would leave in commas to represent its position but the value will be provided by database automatically (..., ,...) | default value |
| another way to specify values for "all" columns of an INSERT, is to use __ for those columns in which you don't have a(n) __value | NULL; data |
| in an INSERT statement, you can insert rows with limited data as long as you at least include data for __ __ and any columns that have __ __ constraints | primary key; NOT NULL |
| In INSERT statements, when choosing to list each column into which a value will be placed, the __ of columns listed must match the number of __ provided; doesn't have to be in same sequence as table | number; values |
| in INSERT statements, remember to enclose __ __ in single quotes; usually applies to dates also | character data |
| common error is to forget to put dates in __ __ | single quotes |
| common error to use __ __ where single quotes are required | double quotes |
| numbers are __ enclosed in quotes | never |
| format of dates is __ to relational database software used | specific |
| 09-NOV-2009, is __ __ for Oracle database for dates | default format |
| when processing insert statement, all columns have a(n) __ __ (NUMBER, CHARACTER, VARCHAR2, DATE, ...) and the values in your INSERT statement __ __ type for the column | data type; must match |
| when processing insert statement, you must provide a(n) __ for columns that have a NOT NULL constraint (this could be met with __ values assigned to the column) | value; DEFAULT |
| when processing insert statement, you must provide data for __ __ __, which conceptually have NOT NULL & UNIQUE constraints | primary key fields |
| when processing insert statement, you must provide data that passes any __ constraint | CHECK |
| when processing insert statement, you must provide data that passes any __ constraint | UNIQUE |
| when processing insert statement, you must provide data that passes any __ __ constraint | FOREIGN KEY |
| primary key columns are automatically __ | unique |
| data added to a table, via the INSERT command, must meet all | constraints enabled on the table |
| when processing insert statement, __ may force you to load tables in a specific order | constraints |
| when you convert from 1 system to another, implement new system, or integrate new business/organization into an existing organization constraints may | force you to load tables in a specific order |
| correct strategy is to load the INSERT statements in the correct order, basically you need to | load "parent" tables before "child" tables |
| when using DELETE, table __ __ __ regardless of how many rows you remove | remains in place |
| simplest statement & also most dangerous as it deletes every row in a table | DELETE FROM tablename; |
| generally you want to delete a specific row or several specific rows, which requires | use of a WHERE clause |
| all of comparison operators used in WHERE clause of SELECT statement can be used in | WHERE clause of DELETE statement |
| when you delete only a(n) __ __ __ in table, WHERE clause will specify the primary key (or a column that is a unique index) | single specific row |
| when planning to run a DELETE statement that will delete multiple rows, good idea to run a query (SELECT statement) first with the WHERE condition to see | what rows are selected before running the DELETE statement with the same WHERE clause |
| DELETE statement cannot violate a(n) | foreign key constraint |
| basically a stored query; SQL SELECT statement that has been created & stored so that it can be run at any time | view |
| view doesn't __ data or a table; merely allows you to __ existing data in different way | create; view |
| improve security by limiting access to sensitive data, simplify user queries by predefining complex formulas, or improve database performance by predefining complex query to take advantage of database indexes are | reasons view is usually created |
| improve security by limiting access to sensitive data | most common use of a view |
| database administrator creates the view & sets security necessary to allow users to access the view while also | setting security necessary to block access to original table |
| Microsoft Access views are called | stored queries |
| view can enhance database performance under certain circumstances, which occurs when a frequently run query does not take advantage of __ __; user then opens view instead of writing query | existing indexes |
| views do not have to be __ | static |
| you can enable view to receive data at run time and use that input to | filter the data |
| MS Access supports __ data type, but does not support __ extension to specify number of digits & number of digits to right of decimal place | Number; (m,n) |
| in IDENTITY (M,N) phrase, IDENTITY indicates that this is surrogate key that will start at __ __ for 1st row, created & increased by __ __ as each additional row is created | value M; increment N |
| MS Access ANSI-89 does not support UNIQUE & CHECK __ __, nor DEFAULT keyword | column constraints |
| MS Access documentation treats __ as keyword rather than as constraint | DEFAULT |
| in MS Access, __ constraints & __ values can be set in table Design view; this is solution for UNIQUE, CHECK, & DEFAULT column constraint not working in this DBMS | equivalent; initial |
| means a variable-length character data type | VarChar |
| values are of fixed length; number listed btwn parentheses, i.e. (1), will indicate that number of characters will be stored for every value, regardless of length of value entered (they will be padded with blanks when necessary) | Character (Char) |
| the maximum length of VarChar will be indicated with a(n) | number listed btwn parentheses, i.e. (1) |
| VarChar is not used all of the time, because a few extra __ of processing is required for VarChar columns, in order to store __ of the value | bytes; length |
| allows determination of values that consist of certain decimal number(s), w/set amount prior to & after decimal point | Numeric data type |
| when using Numeric data type, decimal point is not __ & does not __ __ set number(s) | stored; count as |
| in a Numeric data type, such as Numeric(8,2), there are 8 numeric values __ __ & 2 numeric values __ decimal point | prior to; after |
| means that when new row created, if no value provided for specific column, DBMS is to provide the numeric value indicated | DEFAULT followed by a numeric value |
| values will consist of date &/or time values; DBMS handle in various ways consult docs for specific product | DateTime data type |
| every SQL statement should end with a(n) __; although not always required it is considered a good practice | semicolon |
| as a matter of __, at end of SQL statement, ending parenthesis & semicolon placed on line of its own; this blocks out table definitions for easy __ | style; reading |
| DateTime, Numeric, Int, & Char are | four basic SQL data types |
| MS Access reads SQL statements containing both Char & VarChar data types but converts both to __ __ data type in MS Access database | fixed Text |
| column that will be primary key must be given column constraint __ __; after tbl columns defined, tbl constraint used to create __ key | NOT NULL; primary |
| every table constraint has a name followed by the __ of the constraint | definition |
| only naming __ for primary key constraint is that it is unique | restriction |
| defining primary key using table constraints offer advantage, because it is required for defining composite keys because PRIMARY KEY column constraint cannot be used on | more than one column |
| defining primary key using table constraints offer advantage because, by using table constraints you can choose name of constraint that defines primary key; controlling name of constraints has advantages for | administering database |
| defining primary key using table constraints offer advantage, because allows to easily __ __ keys in some DBMS products | define surrogate |
| table constraints can be used for purposes __ __ creating primary & foreign keys | other than |
| one of most important purposes for table constraints is to define constraints on __ __ | data values |
| used to query databases & to modify data in the tables | SQL DML |
| has 2 forms, depending on whether data are supplied for all of the columns | SQL INSERT statement |
| in INSERT statement, if data for some columns are __, then name of columns for which data __ __ must be listed | missing; are provided |
| in INSERT statement, although order of data must match order of __ __, order of column names does not have to match order of __ in table | column names; columns |
| for INSERT statement to work, values for __ NOT NULL must also be provided | all |
| SELECT ColumnNames FROM TableName WHERE SomeConditionExists; | basic SQL SELECT/FROM/WHERE framework clause for querying single table |
| result of an SQL SELECT statement is a(n) __; this is always __ for SELECT statements | relation; true |
| order of column name(s) __ keyword SELECT, determines order of column(s) in the __ __ | after; resulting table |
| if you want DBMS to check for & eliminate __ __, you must use the DISTINCT keyword | duplicate rows |
| SQL statements can also be used to select all __ for certain __; specified by using SQL WHERE clause | columns; rows |
| can also specify __ __ of table by using asterisk (*) after keyword __ | all columns; SELECT |
| pattern SELECT/FROM/WHERE is __ __ of SQL statements | fundamental pattern |
| many different __ can be placed in a WHERE clause | conditions |
| when column data is Char or VarChar __ __ must be placed in single quotes | comparison values |
| when column data is Integer or Numeric __ __ are necessary | no quotes |
| you can place more than __ __ in WHERE clause by using AND keyword, and only rows meeting __ conditions will be selected | one condition; all |
| when using OR keyword, rows that meet __ of condition will be __ | any; selected |
| WHERE clauses can refer to __ of values & __ values | ranges; partial |
| use of underscore (_) means any character __ __ in spot occupied by underscore | can occur |
| one underscore (_) is used for each __ character | unknown |
| SQL __ __ operate on results of SELECT statement | built-in functions |
| operate only on integer, numeric, & other number-oriented columns | SUM, AVG, MAX & MIN |
| in general, built-in functions __ be used with WHERE clause | cannot |
| GROUP BY keyword tells DBMS to sort table by __ __ & then apply built-in function to groups of rows that have __ __ for named | named column; same value |
| can restrict SELECT statement, with GROUP BY keyword, by using __ clause that will apply condition to groups that are formed | HAVING |
| SQL standard specifies that when WHERE & GROUP BY occur together, | WHERE condition will be applied first |
| subqueries are __ for processing multiple tables, as long as result come from __ table | effective; single |
| if we need to display data from two or more tables, subqueries do not work & we need to instead use __ operation | join |
| UPDATE..SET command is powerful command that needs to be used with care because __ __ can modify more than 1 column at time | UPDATE command |
| drops table's structure along with all of table's data; dangerous command | DROP TABLE statement |
| DROP TABLE statement does not work if table contains/could contain valued needed to fulfill | referential integrity constraints |
| advantage to control constraint names by using __ syntax | CONSTRAINT |
| ALTER statement can also be used to add a(n) | constraint |
| CHECK constraints are __ __ WHERE clauses in SQL queries | similar to |
| CHECK constraints __ __ keywords IN, NOT IN, & LIKE (for specification of decimal places) | can contain |
| CHECK constraints can use < & > signs for __ checks | range |
| ALTER TABLE statement is handy when you need to | add/drop columns |
| when adding column to existing table that contains data, in ALTER TABLE statements, you cannot add NOT NULL column; constraint would be __ because there would be __ __ in each row | violated; missing data |
| when adding column to existing table that contains data, in ALTER TABLE statements, if you want column to be NOT NULL, you must create it as __, insert needed data, then __ column to NOT NULL | NULL; modify |
| ALTER statement can be used to modify __ __, but have to be careful because this can result in __ of data | data type; loss |
| SQL views can __ __ to data in multiple tables & even in other views | combine access |
| SQL statements can be __ from command window | processed |
| DEFAULT can be considered __ or __ depending on DBMS | keyword; constraint |
| if no column constraint is specified column is | set to NULL |
| if primary key has only one column, you can define it by | using primary key constraint |
| another way to define primary key is to use __ constraint | table |
| table & primary key constraints can be used to define single-column & multicolumn __ __, & can also implement __ __ constraints | primary keys; referential integrity |
| can specify that updates & deletions should cascade | foreign key definitions |
| after tables & constraints are created you can add data by using __ command | INSERT |
| after tables & constraints are created you can __ __ by using SELECT command | query data |
| can use SELECT to obtain __ columns, __ rows, or both | specific; specific |
| tables & their associated set of columns | field list |
Created by:
lfrancois
Popular Computers sets