Term
|
Definition
| Structured Query Language |
|
|
Term
|
Definition
| Regional Database Management System |
|
|
Term
|
Definition
| Data Manipulation Language: Includes Select, Update, Delete, Insert Into |
|
|
Term
|
Definition
| Data Definition Language: Creates deletes, imposes constraints, creates keys (indices). Includes Create Database, Alter Database, Create Table, Alter Table, Drop Table, Create Index, Drop Index |
|
|
Term
|
Definition
Stores results in new table called result-set.
syntax:
SELECT columnname FROM tablename
* means all |
|
|
Term
|
Definition
Stores unique results in new table.
SELECT DISTINCT column_name(s)
FROM table_name |
|
|
Term
|
Definition
Modifier of other criterion
The WHERE clause is used to extract only those records that fulfill a specified criterion.
SQL WHERE Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator value |
|
|
Term
|
Definition
Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN If you know the exact value you want to return for at least one of the columns
Use ' ' around text, no ' ' around numeric values |
|
|
Term
|
Definition
Returns results if both are true.
SELECT * FROM Persons
WHERE FirstName='Tove'
AND LastName='Svendson' |
|
|
Term
|
Definition
Returns results where either is true or both are true.
SELECT * FROM Persons
WHERE FirstName='Tove'
OR FirstName='Ola' |
|
|
Term
|
Definition
Returns results organized (default ascending) by selected column.
Select column name(s)
FROM tablename
Order by column name (DESC if you want descending) |
|
|
Term
|
Definition
Insert Into tablename (column, column, column)
VALUES (value1,value2, value3)
Remember all text in ' ' |
|
|
Term
|
Definition
Change values in a table
Update tablename
Set column1=value1, column2=value1
Where somecolumn=somevalue |
|
|
Term
|
Definition
Delete from tablename
where column1=value1
You can delete all info in a table without destroying its structure:
delete * from tablename
or delete from tablename
Cannot undo Delete functions |
|
|
Term
|
Definition
Specifies number of records to return
Select top percentage/number column names from table name
OR
select columnname
from tablename
limit number
OR
Select column names
From table name
Where rownum <=number |
|
|
Term
|
Definition
Operator on where
Select colum
From table
Where column
LIke some pattern
's%' starts with s
'%s' ends with s
'%tav%' has tav in middle somewhere
Can use Like or NOT LIKE |
|
|
Term
|
Definition
use in like queries to substitute for letters
%- substitute for 0 or more characters
_ substitute for exactly one character
[charlist] any single character in there
[!charlist] or [^charlist] anything not in charlist |
|
|
Term
|
Definition
Allows you to select multiple values for where query
Select columnanmes
From tablename
Where column IN (value1, value2, value3) |
|
|
Term
|
Definition
Operator used w/where to select range of data between two values
Databeses treat endpoints seperately |
|
|
Term
|
Definition
Gives a shorter name to column or table
Alias for table
Select alias.columnname
From tablename AS alias
Alias for column name
Select columnname AS alias
from table name |
|
|
Term
|
Definition
Takes data from multiple tables using relationship between a column in both.
A primary key is a column with a unique value for each row. Use these to bind data across tables w.out repeating all data in every table.
JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables |
|
|
Term
|
Definition
Same as join. Only lists if at least one match in both tables.
Select columnnames from tablename1 inner join tablename2 ON table1.column=table2.column |
|
|
Term
|
Definition
Also known as Left Outer Join.
Select columns
From tablename
LEFT Join tablename2
ON table name1.columnname=table2.columnname
Returns all rows from the left table even if there are no matches in the right table |
|
|
Term
|
Definition
THe right join returns all rows from right table (table2) even if there are no matches in table1.
Select columnames
From tablename
Right Join Tablename2
on tablename1.column=tablename2=columnname |
|
|
Term
|
Definition
Returns rows when there is a match in one of the tables
Select columns
From tablename
FULL JOIN tablename2
On tablename1.column=tablename2.column |
|
|
Term
|
Definition
Combines results from 2 Select statements
Select columns from table
UNION
Select columns from table
ONLY SELECTS DISTINCT VALUES
Use Union All for all values
Retains name of first column |
|
|
Term
|
Definition
Puts data into new table. Most often used to back up data.
Select * INTO new Table name
From old table name
Can combine with where and join. |
|
|
Term
|
Definition
|
|
Term
|
Definition
Makes a table in a databse:
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)
List of acceptable types here
http://www.w3schools.com/sql/sql_datatypes.asp |
|
|
Term
|
Definition
Constraints are used to limit the type of data that can go into a table.
Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).
We will focus on the following constraints:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT |
|
|
Term
|
Definition
|
|
