Flashcards in Databases Deck (37):
What's the select statement used for?
The select statement is used to query the database and retrieve selected data that match the criteria that you specify.
Give an example of a select statement
 = optional
What is some of the syntax around columns?
The column names that follow the select keyword determine which columns will be returned in the results. You can select as many column names that you'd like, or you can use a "*" to select all columns.
How do you select what table to draw results from?
The table name that follows the keyword from specifies the table that will be queried to retrieve the desired results.
from "tablename" (
What does the where keyword do?
The where clause (optional) specifies which data values or rows will be returned or displayed, based on the criteria described after the keyword where.
Conditional selections used in the where clause:
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> Not equal to
What does the LIKE condition do when it follows the where keyword?
The LIKE pattern matching operator can also be used in the conditional selection of the where clause. Like is a very powerful operator that allows you to select only rows that are "like" 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.
Give some examples of how to use the LIKE condition
select first, last, city
where first LIKE 'Er%';
This SQL statement will match any first names that start with 'Er'. Strings must be in single quotes.
Or you can specify;
select first, last
where last LIKE '%s';
This statement will match any last names that end in a 's'.
select * from empinfo
where first = 'Eric';
This will only select rows where the first name equals 'Eric' exactly.
How do you use the create table statement?
The create table statement is used to create a new table. Here is the format of a simple create table statement:
create table "tablename"
("column1" "data type",
"column2" "data type",
"column3" "data type");
Format of create table if you were to use optional constraints:
create table "tablename"
("column1" "data type"
"column2" "data type"
"column3" "data type"
[ ] = optional
What are the most common data types used in tables?
char(size)-Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
varchar(size)-Variable-length character string. Max size is specified in parenthesis.
number(size) Number value with a max number of column digits specified in parenthesis.
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.
Give an example of how you'd create a table
create table employee
To create a new table, enter the keywords create table followed by the table name, followed by an open parenthesis, followed by the first column name, followed by the data type for that column, followed by any optional constraints, and followed by a closing parenthesis. It is important to make sure you use an open parenthesis before the beginning table, and a closing parenthesis after the end of the last column definition. Make sure you separate each column definition with a comma. All SQL statements should end with a ";".
What are constraints?
When tables are created, it is common for one or more columns to have constraints associated with them. A constraint is basically a rule associated with a column that the data entered into that column must follow. For example, a "unique" constraint specifies that no two records can have the same value in a particular column. They must all be unique. The other two most popular constraints are "not null" which specifies that a column can't be left blank, and "primary key". A "primary key" constraint defines a unique identification of each record (or row) in a table.
What is the insert statement used for?
The insert statement is used to insert or add a row of data into the table.
How do you use the insert statement?
To insert records into a table, enter the key words insert into followed by the table name, followed by an open parenthesis, followed by a list of column names separated by commas, followed by a closing parenthesis, followed by the keyword values, followed by the list of values enclosed in parenthesis. The values that you enter will be held in the rows and they will match up with the column names that you specify. Strings should be enclosed in single quotes, and numbers should not.
insert into "tablename"
Give an example of how you'd use the insert statement
insert into employee
(first, last, age, address, city, state)
values ('Luke', 'Duke', 45, '2130 Boars Nest',
'Hazard Co', 'Georgia');
Note: All strings should be enclosed between single quotes: 'string'
What does the update statement do?
The update statement is used to update or change records that match a specified criteria. This is accomplished by carefully constructing a where clause.
set "columnname" =
 = optional
Give an example of how you'd use update
set area_code = 623
where prefix = 979;
set last_name = 'Smith', prefix=555, suffix=9292
where last_name = 'Jones';
set age = age+1
where first_name='Mary' and last_name='Williams';
What does the delete statement do?
The delete statement is used to delete records or rows from the table.
delete from "tablename"
[ ] = optional
Give an example of how you'd use the delete statement
delete from employee
where lastname = 'May';
delete from employee
where firstname = 'Mike' or firstname = 'Eric';
To delete an entire record/row from a table, enter "delete from" followed by the table name, followed by the where clause which contains the conditions to delete. If you leave off the where clause, all records will be deleted.
What is SQL?
Structured Query Language (SQL) is a specialized programming language for manipulating databases
A method for implementing an entity ad attributes as a group of related data
a search or sort carried out on data that retrieves the answer to a question
Define client-server database
When a database is implementing on a server so multiple users can access it from their workstations. The processing, for example a query, will take place on the server.
What does DBMS mean?
Database management system (DBMS) software that enables the management of all aspects of a database including adding, updating and querying data
What is a common problem with shared databases?
If two people are trying to update the database at the same time this can cause issues when they try to save data in the same location. The first person to save data will save it in the location then when the second person saves it will overwrite the first persons data.
What are some solutions to having multiple users write to the same database?
Record locks - a technique used to lock the second user who tries to access a data file to a read-only version.
Serialization - a technique to ensure that only one transaction can take place at a time.
Timestamp ordering - Every transaction that takes place on a database is given a timestamp. This timestamp is used to work out if the current transaction should be complete.
Commitment ordering - Looks at both the time the command was made and whether an action has precedence over other commands. If two commands are made at the same time the one that has precedence over the other will take place first
What is a relational databases?
a method of creating a database using related tables, with relationships between the tables.
An object which data is stored about.
What is an attribute?
A characteristic or piece of information about an entity which would be stored as a field in relation to a database.
What is an entity relationship diagram?
A visual method of describing relationships between entities.
A single line means one. A bird foot meant many.
Define primary key
An attribute used to uniquely identify every record within a table.
Define entity identifier
An attribute which can uniquely identify each instance of an entity.
define foreign key
A primary key in another table used to link tables together.
what is normalization?
The process of ensuring that a relational database is structured efficiently. ( no redundant data)
How s first normal format (1NF) achieved?
It is achieved by ensuring that a table doesn't contain repeating attributes or repeating groups and that all data in a table is atomic (all of it's their or none of it is).
How s second normal format (2NF) achieved?
second normal format is achieved by getting the table/database to first normal format and then removing attributes that depend upon part or all of the primary key by creating additional tables.
How s third normal format (3NF) achieved?
By getting the database to second normal format and then removing non-key attributes that depend on other non-key attributes by creating additional tables.