Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/94

Click to flip

94 Cards in this Set

  • Front
  • Back
What is the SQL version used by Microsoft SQL Server?
Transact-SQL
What is Data Denfinition Language (DDL)?
A sub-language of SQL used to alter or drop objects in a database.
What is the SQL version used by Microsoft Oracle?
PL/SQL
What is the Select keyword used for?
Used to retrieve data from database tables.
What does SQL stand for?
Structured Query Language
What is a null value?
A field value that is undefined or simply not known. (No data has been entered into the field) Zero in a numeric and blank in a text field is NOT null.
__ are precompiled AQL statements that accept arguments(parameters) from clients. The are designed to automate tasks.
stored procedures
SQl is often broken down into three sub languages. What are they?
1) Data Denfinition Language (DDL)
2)Data Manipulation Language(DML)
3) Data Control Language(DCL)
What are some commands used within a SELECT statement to bring multiple tables together?
1)equi-joins
2)inner-joins
3)outer-joins
4)union queries
The simplest Select statement contains at least what?
1) The fields we want to return

2)The table we retrieve the fields from
What are some of the main benefits of views?
1) can be used for security purposes
2) They can be used to extract only information relevant to the users needs
3)confer the abilty to modify field names and aliases on the server rather then on the client
What is the Distinct keyword?
Optional

Specifies that each record that displays in the recordset should be unique.
What are columns?
Columns contain the column name, data type, and any other attributes for the column.
Give examples of logical expressions?
True, False, And, OR, Not, Unknown
What is simular about views, stored procedures, and triggers?
They are complex SQL queries that are stored as objects on the server.
What is Data Manipulation Language (DML)?
A sub-language of SQL used to retrieve, and operate on, the data in a database
What does WHERE clause do?
(optional) specifies which data values or rows will be returned or displayed, based on the criteria described after the keyword
What is Data Control Language?
A SQL-Sub Language used to provide sequrity to a database.
When adding new records to a database we use the _____ statement in conjunction with the __ keyword.
INSERT INTO , VALUES
When we create a stored procedure we start with what command?
CREATE PROCEDURE
What clause refines a query such that a record only apears in a resulting recordset if a common field is found?
WHERE
The only difference between a View SQL statement and a normal one is what?
This Sql statement has a header that specifies that a view that is to be created from the Select statement. To do this the AS keyword is used.
The _____ statement in conjunction with the ____ clause is used to update database tables.
UPDATE,SET
What are three things to take into consideration when updating views?
1) We can only update or insert into views that are included in the SELECT statement.
2)If we decided to insert a new record via our view all records not included in our view would be assigned a null value
3)We can only update fields from one table at a time
Once we define a SQL statement to bring together many tables we define it as a __?
View
What allows users easy access to highly specific information and can be used in a security context?
Views
The statement used to remove unwanted or archived data easily is?
DELETE
Once a view is created we can simply call it with what?
A SELECT statement.
What are rows?
Contain the records or data for the columns.
How would you start making a view named TitleView?
Create View titleview
AS
The syntax of the DELETE is simular is basicly the same as the __ command?
SELECT
The benefits of stored procedure mainly relate to what?
1) performance enhancements
2)protection of data integrity
equi-joins are a special type of what kind of join?
INNER JOIN
The ___ statement is used to make current or change records that match a specified criteria. This is accomplished by carefully constructing a where clause.

UPDATE
You might use a subquery inside a INSERT statement to what?
1) Create sample data
2) Archive data to a backup table
What keyword can be used as and alternative to the OR operator?
IS
What kind of join allows us to specify that the records returned should include all of the records from one table, even in a correlating record in another table does not exist?
OUTER JOIN
What follows the keyword from is a SQL statement?
Specifies the table that will be queried to retrieve the desired results
What are the five clauses of the SELECT statement?
FROM
WHERE
GROUP BY
ORDER BY
Having
In updating records it is expectally common to use a subquery inside a ___ statement.
INSERT
What kind of join that allows to merge to unrelated tables based on simular field structures?
UNION
What pattern matching operator can also be used in the conditional selection of the where clause. it is a very powerful operator that allows you to select only rows that are the same as what you specify. The percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified.
LIKE
What command is used to delete a table and all rows in the table.
DROP TABLE
A relational database system contains one or more objects called what?
tables
What is rule associated with a column that the data entered into that column must follow.
Contraint
Some common relational database management systems that use SQL are?
Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc
What statement is used to is used to remove records or rows from the table.
DELETE
A generic name for a type of join that requires common field values to exsist in each table in order for a record to be returned. What is it??
equi-joins
Give and example of a SQL statement used to make a new table?
create table "tablename"
("column1" "data type",
"column2" "data type",
"column3" "data type");
What statement is used to make a new table?
CREATE TABLE
What is the keyword used if you would like to only retrieve unique records in specified columns?
LIKE
What wildcard is used in a LIKE statement?
% (percent)
What is char(size)?
Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
What are aliases?
Fields that only exists in a recordset
What outer join returns all of the records from the table specified first in the from clause?
LEFT OUTER JOIN
What is the SQL statement to list the records from the Customers table and Country Field - insuring for instance Mexico would only be listed once?
SELECT DISTINCT Country FROM Customers
What is number(size,d)?
Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal.
Write select statement: Table = Customers Sort by Records :Title then LastName
Select * from Customers
ORDER BY Title, LastName
What are the rules of the UNION join?
1) must request the same amount of records from each table
2) records must be of the same data type
What is the keyword Where used for?
Identifies criteria that records must satisfy.
What is varchar(size)?
Variable-length character string. Max size is specified in parenthesis.
What clause allows us to group our records based on a field?
GROUP BY
What are the two most popular constraints?
not null & primary key
What keyword can be used as and alternative to the OR operator?
IS
What are the two kinds of OUTER JOIN 's ?
1) LEFT OUTER JOIN
2) RIGHT OUTER JOIN
What are three ways of joining value expresions?
1) Arithmetic Operators
2) Concatenating Text
3) Concatenating Fields
What is the statement is used to place or add a row of data into the table.
INSERT
What predicate allows us to specify a pattern to search for when implementing a SQL statement?
LIKE
To insert records into a table, enter the key words ____ followed by the___ , followed by an open parenthesis, followed by a list of column names separated by commas, followed by a closing parenthesis, followed by the keyword ___, followed by the list of values enclosed in parenthesis.
INSERT INTO, table name, VALUES
What predicate is used to find all records where the value in a selected field is Null?
NULL
What OUTER JOIN returns all of the records from the JOINED clause?
RIGHT OUTER JOIN
Give and example of the IN keyword for table Customers for the field Country that will display only records from Mexico and Spain.
Select *
FROM Customers
WHERE Country IN ('Mexico','Spain')
What keyword is used to create a alias?
AS
In order of operation what takes precedence AND or OR?
AND
What clause is used as a restriction on the GROUP BY clause?
HAVING
The Convert keyword is specific to what database?
Microsoft SQL Server
What clause seems simular to the group by clause at first?
LIKE
What are some of the most common SQL clauses
1)Select
2)Distinct
3)From
4)Where
5)Group By
6)Having
7)Order By
What are subqueries?
SQL statements embedded inside other SQL statements.
What is the keyword Having used for?
Allows filtering out of groups that don't satisfy a search condition.
What is the keword ORDER BY used for?
Identifies the order of field values for a particular field.
What is the Keyword used to change the variable from one data type to another?
Convert
In a WHERE clause what values would not be returned?
False or UNKNOWN
What clause can only by used with the GROUP BY clause?
HAVING
The GROUP BY clause is often used in connection with what?
Aggregate Functions
List some standard ANSI aggregate functions?
1)Sum
2)COUNT
3)MIN
4)MAX
5)AVG
When using a LIKE statement what can be used to indicate a single missing character?
_ (underscore)
Where is the easiest place to introduce a sub query?
Within the WHERE clause of a SELECT statement.
What predicate allows us to specify a range of values to return?
BETWEEN
What is the keyword Group By used for?
Gathers rows into groups, based on the values in the grouping column.
The BETWEEN predicate ussually goes hand in hand with what operator?
AND
What is the keyword From used for?
Required

Identifies the tables where the fields are found.
What functions are used to modify data in a database?
1)UPDATE
2)DELETE
3)INSERT
What keyword is used to reverse the outcome of a search condition?
NOT