Section 1
Preview this deck
clients, servers, network
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
0
All-time users
0
Favorites
0
Last updated
6 years ago
Date created
Mar 1, 2020
Cards (303)
Section 1
(50 cards)
clients, servers, network
Three main components of a client/server system
back-end processing
the processing that's done by the DBMS
front end
the client
rows and columns
tables consist of ________
bit
a value of 1 or 0 for true or false
database management system
DBMS
float
floating-point values that contain an approximation of a decimal vallue
primary key
uniquely identifies each row in the column
DBMS
to store and manage databases, each server requires a _____________ like Microsoft SQL server
table
typically modeled after a real-world entity, such as an invoice or a vendor
application server
does the work that the user wants to do
LAN
in a client/server system, the server is typically a high-powered PC that communicates with the clients over a _____________
one-to-many
the most common type of relationship between the tables in a database
column
represents some attribute of the entity, such as the amount of an invoice or a vendor's address
SQL
the standard language for working with a database
client/server system
the server is typically a high-powered PC that communications with the clients over a local area network
non-primary key or unique key
uniquely identifies each row in the table
query results
When the DBMS receives a query, it provides a service like returning the requested data (the __________) to the client
int
integer values
tables
consist of rows and columns
data access API
provides the interface between the application program and the DBMS
cell
the intersection of a row and a column
tables
relational databases consist of_________
foreign key
identifies a primary kye in another table
clients
In a file handling system, all of the processing is done on the__________
back end
the database server is referred to as the _____________
client
the PCs, Macs, or workstations of the system
records
rows
WAN
Individual systems and LANs can be connected and share data over larger private networks such as _________________
relational database
consists of tables
decimal
decimal values that are accurate to the least significant digit
web server
used to store web applications and web services
index
provides an efficient way to access data from a table based on the values in specific columns
structured query language
SQL
fields
columns
application programming interface
API
database server
when a server stores databases
row
contains a set of values for a single instance of the entity, such as one invoice or one vendor
single
a cell stores a ______ value
server
a computer system that stores the files and databases of the system and provides services to the clients
SQL queries
The application software communicates with the DBMS by sending _________ through the data access API
index
An ______ is created automatically for a table's primary and non-primary keys
rows
records
columns
fields
money
monetary values that are accurate to four decimal places
front-end processing
the processing that's done by the client software
the client and the server
In a client/server system, the processing done by an application is typically divided between _____________
enterprise system
A client/server system can also consist of one or more PC based systems, one or more midrange systems, and a mainframe system in dispersed geographical locations. This is commonly referred to as an ________________
network
the cabling, communication lines, and other components that connect the clients and the servers of the system
composite primary key
a primary key that consists of two or more columns
Section 2
(50 cards)
and, or, not
the three logical operators that can follow a where clause
Select*
All results are shown
SELECT TOP 5 PERCENT ANSWERS
write a select statement that includes the top 5 percent of answers
SELECT TOP 5 ANSWERS
write a select statement that includes the top 5 answers
ORDER BY
you should always include an _______ clause when you use the top keyword
SELECT TOP 5 WITH TIES ANSWERS
write a select statement that includes answers with ties
included
the upper and lower limits of a between phrase are _______ in the result set
select, from, where, order by
the correct order of the basic syntax for a select statement (4 clauses)
not between
used to show only results that are not between the upper and lower limits
from
identifies the base table
all or distinct
used to determine whether or not duplicate rows are returned
expressions
a combination of column names and operators that evaluate to a single value
arguments
parameters
order by
sorts the rows in the results by the specified sequence
parameters
a function consists of the function name, followed by a set of parentheses that contain any __________ required by the function
hierarchical database model
provides only for one-to-many relationships called parent/child relationships
in
used to test whether an expression is equal to a value in a list of expressions
string expression
can consist of one or more character columns, one or more literal values, or a combination of both
select
Distinct and all are coded immediately after the _______ statement
nchar
a string of letters, symbols, and numbers in the Unicode character set
between
used to test whether an expression falls within a range of values
with ties
if you include _____ in the top clause, additional rows will be included if their values match the values of the last row
top
clause used to retrieve a specific number or percent of rows
where
filters the rows in the base table; only the rows that match the search condition are included in the result set
percent
if you include _____ in the top clause, the first n percent of the selected rows are included in the result set
char
a string of letters, symbols, and numbers in the ASCII character set
select
retrieves the specified columns from the base table and stores them in a result set
distinct
prevents duplicate(identical) rows from being included in the result set
column alias
if you want to specify a name other than the same name as the column in the base table
function
performs an operation and returns a value
single quotes
a string literal or a date literal is enclosed in
null values
indicates that the value of the column is unknown
all
is the default distinct or all?
identity column
a numeric column whose value is generated automatically when a row is added to the table
parentheses
the statement following an 'in' or 'not in' phrase are enclosed in
column alias
substitute name
select
The as clause is usually used in the ____ statement
base table
the from clause identifies the _____
no quotes
a numerical literal is enclosed in
+
concatenation operation
arithmetic, string
In the select clause, you can code _____ expressions, _____ expressions, and expressions that include one or more functions
network database model
can accomodate any type of relationship
default value
used if another value isn't provided when a row is added to the table
concatenation operation
used to combine columns and literals in a string expression
data type
The ________ that's assigned to a column determines the type and size of the information that can be stored in the column
all
causes all rows matching the search condition to be included in the result set, regardless of whether rows are duplicated
not in
used to provide results that are not in a list of expressions
parameters
arguments
substitute name
column alias
datetime
dates and times
Section 3
(50 cards)
Query editor
the window you type your SQL statements into
LIKE 'SAN%'
matches san diego and santa ana
entity
In access, rows are called
database diagram
shows the relationships between tables; can be used to illustrate the relationships between the tables in a database
not like
includes only the rows with values that don't match the specified string pattern
diagram, criteria, SQL
the three panes in the query designer window
not like '[1-9]%'
match numbers of any length that don't start with a 1 or a 9
SQL
displays the SQL statement built by the Query Designer based on the information in the other two panes.
Criteria
The query designer pane that displays the columns selected in the Diagram pane and lets you specify the sort order and the criteria you want to use to select the rows for the result set
mask
the string pattern used in a like clause
[ ^ ]
wildcard symbol; matches a single character not listed after the symbol inside
[ - ]
wildcard symbol; matches a single character within the given range
LIKE 'COMPU_ER%'
matches compuserve and computerworld
%
wildcard symbol; matches any string of zero or more characters
client tools
provide an interface for working with the databases
IS NULL
used to test for a null value
Completion list
displayed by the intellisense feature
Books Online
The documentation for SQL server 2012
;
signifies the end of command
like 'n[^k-y]'
matches nc and nj but not nk or ny; use caret
Windows Authentication.
In most cases, you can select the ________________ option to let Windows Supply the appropriate login name and password for you.
SQL Server Express
adequate for many small and medium sized applications
database server
provides the services for managing SQL server databases
like 'DAMI[EO]N'
matches damien and damion
[]
wildcard symbol; matches a single character listed within
SQL Server Configuration manager
A graphical tool that you can use to start and stop the database server.
Intellisense feature
automatically displays completion lists that help you complete the statement
database diagram
can show an unlimited # of results and don't have to show relationships
query designer
can be used for multiple tables and 1-to-many or one-to-one queries
Windows Authentication or SQL Server Authentication
The two options you can use to connect to a server.
<> 0
equivalent of not equal to
SQL Server 2012 Express Edition
a free version of SQL Server
Diagram
the pane in the query editor that displays the tables used by the query and lets you select the columns you want to include in the query
* / % + -
Order of precedence: addition, subtraction, modulo, multiplication, division
schema
a container that holds objects
like
used to retrieve rows that match a string pattern
NOT, AND, OR
The logical order of and, not, and or
Query designer
a tool that can help you create queries using a graphical interface
_
wildcard symbol; matches any single character
database engine
SQL server is a _________, or database server.
like 'n[a-j]'
matches nc and nj but not nk or ny; use hyphen
AutoHide
click to automatically hide the Object Explorer
SQL Server 2012 Express Edition
restricts the number of processors, the amount of memory, and the amount of data that SQL Server can manage.
SQL Server Management Studio
The primary graphical tool that a developer uses to work with a SQL server 2012 database. You can use this tol to work directly with database objects and t develop and test SQL statements.
instance
The dialog box that is displayed when Management Studio is started lets you select the _______ of SQL Server you want to connect to.
after the where clause and the expression
the IS NULL clause is placed
DBMS and the database
The database management servers holds ____________
attributes
In Access, columns are called
query designer
can handle simple, but not more complex queries
Section 4
(50 cards)
inner join
only the rows that satisfy the join condition are included in the result set
distinct
This keyword eliminates duplicate results
qualified table name
type the table name, a dot, then the column name
first select clause
in a union, the column names in the final result set are taken ONLY from the _______
full outer join
In the Venn diagram, both circles are entirely filled in
primary key, foreign key
typically, join conditions are based n the relationship between the _____ in one table and the _____ in the other table
partially qualified object name
missing one or more parts of the fully qualified object name
cartesian product
the result set from a cross join
where clause
Explicit syntax is used because there are less complications with the _________
explicit syntax
Keywords 'join, on, and =' are used in __________
asc
default order by syntax
cross join
produces a result set that includes each row from the first table joined with each row from the second table
SQL statements
the type of statement accepted by query editor and management studio
theta syntax
another term for implicit syntax
nested subquery
a subquery within a subquery
asc
order by smallest to largest
innermost to outermost
nested subqueries evaluate from _______ to _______
last select clause
in a union, place the order by statement in the __________
distinct
self-joins frequently include the ______ keyword
join conditions
joins are based on the ______ you specify
inner and outer
Explicit syntax is used because it allows for __________ joins
outer join
retrieves all rows that satisfy the join condition, plus unmatched rows in one or both tables
table alias
used when long table names make qualified column names long or confusing (correlation names)
single value, result set with one column, result set with more than one column
subqueries can return ______, _______, and ______
null
when a row with unmatched columns is retrieved, any columns from the other table that are included in the result set are given _____ values
right outer join
In the Venn diagram, the entire right circle is filled in
desc
order by largest to smallest
number of columns and data type
Concerning unions, both tables must have the same _________
ad hoc relationships
join tables that are based on relationships not defined in the database
duplicate rows
unions eliminate _______________
union
combines rows from two or more result sets
left outer join
In the Venn diagram, the entire left circle is filled in
fully qualified object name
made up of the server name, the database name, the schema name, and the name f the bject
columns
qualified table names are required when the ______ in a join condition have the same name
from
in the explicit syntax, the join condition is coded in the _______ clause
where, having, from, select
subqueries can be introduced in which four clauses?
dot qualifier
required when joining multiple tables at the same column name
self join
a table is joined to itself
correlation names
self-joins require ________
FROM
correlation names are assigned in the _____ clause
join
used to combine columns from two or more tables into a result set
implicit and explicit
the two types of inner or outer joins
correlation names
temporary table names assigned in the FROM clause (table alias)
linked server
before you can specify a server name, you must add a _________ to the current instance of the server
major sort
The first column listed in the order by clause is referred to as ______________
where
in the implicit syntax, the join condition is coded in the _____ clause
interim result set
when coding a multi-table join, a series of two-table joins are performed from left to right. Each of these join is called an _______________.
--
represents a single line comment
minor sort
Any columns following the first column in the order by clause are __________
subquery
a select statement that's coded within another SQL statement
Section 5
(50 cards)
subquery predicate
another term for a subquery search condition
subquery search condition
a subquery that's used in a WHERE or HAVING clause is called a ___________
true
If no rows are returned by the subquery, a comparison that uses ALL is always ________
search condition
if a subquery is introduced in the where clause, it is as a _______________
derived table
A subquery that's coded in the FROM clause returns a result set called (a) _________________
subqueries
Most joins can be restated as ________
single column
A subquery that returns (a) _______ can be coded in place of a list of values, such as the values for an IN phrase
correlated subqueries
The EXISTS operator is used most often with ________________
Join
If you were using a relationship between a primary key and a foreign key, you should use a _______
correlated subquery
a subquery that is executed once for each row processed by the outer query
join
Should you use a subquery or a join in the select clause?
one or more columns
A subquery that returns (a) _____ can be introduced in place of a table in the FROM clause
Join
Only a ______ can produce a result set that includes columns from both tables
indication of whether any rows meet the condition
A subquery with the exists operators returns an ______________________
single column of values
If you use the IN operator, the subquery must result (a) _____________
NOT EXISTS
Use this operator to test that no rows are returned by the subquery
calculated value
If you use a _________ in the select list, you must name the column
table specification
if a subquery is introduced in the from clause, it is as a ___________
INTO, SELECT
The ______ clause lets you create a new table based on the result set of the _______ statement
0
A subquery with the EXISTS operator returns __________ rows
faster and more readable
Code a join instead of a subquery in the select clause because it's _________________
aggregate value, outer query
You can use a subquery to pass an ___________ to the ________
false
If all of the rows returned by the subquery contain a null value, a comparison that uses ALL is always _________
noncorrelated subquery
a subquery that is executed only once
ad hoc
A subquery is more intuitive when it comes to an __________ relationship between the two tables
outer query
The result set of a query with a subquery can ONLY include columns from the table named in the _________
any or some
The ______________ keyword tests that a condition is true for at least one of the values returned by a subquery
EXISTS
Use this operator to test that one or more rows are returned by the subquery
search condition
if a subquery is introduced in the having clause, it is as a _________
FROM
When you code a subquery in the ________ clause, you must assign names to any calculated values in the result set
subquery search condition
the most common use for a subquery
different
Each execution of a subquery returns (a) ____________ result
ALL
The ALL keyword tests that a comparison condition is true for _________ of the values returned by a subquery
the value of a column in the outer query
A correlated subquery refers to ____________________
derived tables
Most useful when you need to further summarize the results of a summary query
alias
When you create a derived table, you must assign a(n) ______________
long, complex queries
Which type of queries are easier to code with subqueries?
single value
A subquery that returns (a)_______ can be coded anywhere an expression is allowed
result set
The definitions of the columns in the new table are based on the columns in the _________
single value
If you code a search condition without the SOME, ANY, and ALL keywords, the subquery must return a __________
column specification
if a subquery is introduced in the select clause, it is as a ________
false
if no rows returned by the subquery or all of the rows returned by the subquery contain a null value, a comparison that uses ANY or SOME is always _________
joins
Most subqueries can be restated as _____
outer join
Instead of using the NOT IN operator with a subquery, use a(n) __________
single value
When you code a subquery for a column specification in the SELECT clause, the subquery must return a _________
qualified column name
A correlated subquery uses a ___________ that includes the table name from the outer query
correlated subquery
a subquery that's coded within a select clause is usually a ________________
alias or correlation name
If the subquery uses the same table as the outer query, you must assign a(n) ______________ to one of the tables
Join
A ______ tends to be more intuitive when it uses an existing relationship between the two tables
Join
Which performs faster, joins or subqueries?
Section 6
(50 cards)
This type of restraint prevents null values from being stored in the column
not null
What five attributes can you specify at the column level?
null/not null, primary key/unique, identity, default, sparse
literal, expression, default, or null
When using the update statement, you can specify for the value for a column by using which four types of values?
subqueries, joins
Use ______ or ______ in the from clause to base the delete operation on the data tables other than the one named in the delete clause
This type of constraint requires that each row have a unique value in the column and DOES allow null values
unique
statements used to create, modify, and delete data contained within a database
DML statements
derived table, insert
If you code a subquery in place of the values clause, the rows in the ____________ are inserted into the _________ table
This type of index includes a where clause that filters the number of rows that are included in the index
Filtered index
If __________ is specified, null values ____ allowed
PRIMARY KEY, aren't
This type of constraint requires that each row in the table have a unique value and doesn't allow null values
primary key
correct order
The subquery must return values for required columns in the ______________
where
When using a subquery for an update statement to provide one or more values used in the search condition, place it in the ________ clause
The CREATE DATABASE statements creates _________________
A new, empty database on the current server
values
If you include an optional column list in your insert statement, use this clause to specify which values you'll be inserting.
set
When using a subquery for an update statement to return the value assigned to a column, place it in the ________ clause
This type of constraint limits the values for a column
check
default values, null values, identity columns
If you include a column list, you can omit columns with _____________ and columns that accept ____________. You must omit ____________.
The clustered key is for the ___________
primary key
join, from
If you need to specify column values or search conditions that depend on data in a table other than the one named in the update clause, use a _________ in the ___________ clause
This type of constraint enforces referential integrity between a column in the new table and a column in a related table
references
where
You can use any of the columns returned by a subquery or join in the ________ clause (delete)
You can only have one _________ index per table
clustered
from
you can specify additional criteria for the delete clause in the ________ clause
When the CREATE DATABASE statement is executed, SQL Server also creates a __________
log file
What are the five types of restraints?
not null, primary key, unique, check, references
insert
Use this statement to add a new row to a table
column definitions and data
Only _______ and ________ are copied to the new table.
every column, identity column
If you don't include a column list, you must code a value for ___________ except the ___________
set, where
You can use columns from the joined tables in the values that you assigned to columns in the _______ clause or in the search condition of a __________ clause
The file used to record modifications to the database
log file
Set
Use this clause to name the columns to be modified and the value to be assigned to each column
The nonclustered key is for ________
every other unique key
the name of the column you want to add a row to
In the insert clause, specify
This type of index applies to every row in the table
Full index
from
When using a subquery for an update statement to identify the rows that are available for update, place it in the _______ clause
Table-level constraints apply the constraint to ________________
one or more columns
in the same order as they appear in the column list
If you do include a column list, you must specify the column values ________________
update
Use this statement to modify one or more rows in the named table
all the rows in the column will be updated
If you omit the where clause from the update statement, ______________
default, values, omit the list of values
If all the table columns are identity columns, default values, or columns that allow null values, code the ________ keyword at the beginning of the _______ clause and _________________
Is NULL or NOT NULL the default? (which will execute if neither is specified?)
null
delete
Use this statement to delete one or more rows from the table you name in this clause
Statements used to create, modify, and delete database objects such as the database itself, the tables contained in a database, and the indexes for those tables
DDL statements
Null
Insert a single row with a null value using this keyword
What type of index can improve performance when the number of rows in the index is small relative to the number of rows in the table?
Filtered index
drop table
If the table you name in the into clause exists, you must delete it using the ________ statement
Default
Insert a single row with a default value using this keyword
where
You specify the conditions that must be met for a row to be delete in the __________ clause
DML
data manipulation language
DDL
data definition language
Section 7
(3 cards)