Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

IT Final > SQL > Flashcards

SQL Flashcards

1
Q

SQL

A

Structured Query Language

Standard language for relational database systems

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Three SQL Statement Categories

A

DDL (Data Definition Language)
DML (Data Manipulation Language)
DCL (Data Control Language)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Commands that define a database, including creating, altering, and dropping tables and establishing constraints
-Create, Drop, Alter, Truncate

A

DDL (Data Definition Language)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

DDL (Data Definition Language)

A

Commands that define a database, including creating, altering, and dropping tables and establishing constraints
-Create, Drop, Alter, Truncate

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Commands that maintain and query a database

-Select, Insert, Update, Delete, Merge

A

DML (Data Manipulation Language)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

DML (Data Manipulation Language)

A

Commands that maintain and query a database

-Select, Insert, Update, Delete, Merge

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Commands that control a database, including administering privileges and committing data
-grant, revoke

A

DCL (Data Control Language)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

DCL (Data Control Language)

A

Commands that control a database, including administering privileges and committing data
-grant, revoke

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Requires order of included clauses

A 
Select
From
Where
Group By
Having
Order By
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

SELECT statement

A 
Statement used for queries on a single table or for queries that involve multiple tables
Clauses:
Select
From
Where
Group by/Having
Order By
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

SQL Boolean Operators (used in where clause to join two conditions)

A

Not
And
Or

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Order of the evaluation of operators

A

Not first
Then And
Then Or

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

SQL aggregate functions

A 
Count
Avg 
Max
Min
Sum
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

COUNT

A

The number of entries in a column that are not NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

AVG

A

The average for entries in a column - argument must be numeric

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

MAX

A

The maximum value in a column (NULL values ignored)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

MIN

A

The minimum value in a column (NULL values ignored)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

SUM

A

The sum of the numeric values in a column

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Used to summarize aggregations for records grouped by a given criteria. For each group, one record (row) is returned

A

GROUP BY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

GROUP BY

A

Used to summarize aggregations for records grouped by a given criteria. For each group, one record (row) is returned

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Used together with GROUP BY to specify “group-level” conditions to restrict the groups returned to only those satisfying the group condition(s)

A

HAVING

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

HAVING

A

Used together with GROUP BY to specify “group-level” conditions to restrict the groups returned to only those satisfying the group condition(s)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

3 types of JOINs

A

INNER JOIN
LEFT (OUTER) JOIN
RIGHT (OUTER) JOIN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Returns only the set of records that match in both TABLE A and TABLE B (according to some attribute)

A

INNER JOIN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Returns only the set of records that match in both TABLE A and TABLE B (according to some attribute)
INNER JOIN
26
returns all the records in Table A and only the records that match from Table B. It leaves out records in B that have no match in A.
LEFT JOIN
27
LEFT JOIN
returns all the records in Table A and only the records that match from Table B. It leaves out records in B that have no match in A.
28
Returns all the records in Table B and only the records that match from Table A. It leaves out records in A that have no match in B
RIGHT JOIN
29
RIGHT JOIN
Returns all the records in Table B and only the records that match from Table A. It leaves out records in A that have no match in B
30
Firms use information for what two purposes?
Transactional and analytical
31
Transactional purposes
Day-to-day operations
32
Analytical purposes
Trend analyses, forecasts, and input generation for strategies that improve profit or long-term sustainability - compete on analytics
33
A named, two dimensional table of data which consists of rows (records) and columns (attributes/fields)
Relation
34
Requirements for a table to qualify as a relation (5)
The table must have a unique name Attributes (columns) in tables must have unique names Every attribute (field) value must be atomic (not multivalued) Every record (row) must be unique The order of the rows and columns must be irrelevant
35
Consists of one or more (probably interconnected) relations
Relational database
36
Relational database
Consists of one or more (probably interconnected) relations
37
3 crucial activities of a relational database
1. Input information into the database 2. Update/delete information in the database 3. Retrieve information from the database, i.e. run a query on the database
38
An attribute or set of attributes that uniquely identifies each row
Primary key
39
Primary key
An attribute or set of attributes that uniquely identifies each row Existence of a primary key ensures that all rows are unique Attributes in the primary key cannot be empty (NULL) Notation when listing attributes: dashed-underlined attribute name(s)
40
Simple vs composite primary keys
Single attribute vs. multiple attributes | Student ID number vs. so oak security number with birthdate
41
Natural attribute vs artificial attribute
Name vs student ID number
42
A named, two dimensional table of data which consists of rows (records) and columns (attributes or fields)
A relation
43
A relation
A named, two dimensional table of data which consists of rows (records) and columns (attributes or fields)
44
5 requirements for a table to qualify as a relation
The table must have a unique name Attributes (columns) in tables must have unique names Every attribute (field) value must be atomic (NOT multivalued) Every record (row) must be unique The order of the columns and rows must be irrelevant
45
Consists of one or more (probably interconnected) relations
Relational database
46
Relational database
Consists of one or more (probably interconnected) relations
47
Three crucial activities of a relational database
1. Input information into the database 2. Update/delete information in the database 3. Retrieve information from the database, i.e. run a query on the database
48
An attribute or set of attributes that uniquely identifies each row
Primary key
49
Primary key
An attribute or set of attributes that uniquely identifies each row - existence of a primary key ensures that all rows are unique - attributes in the primary key cannot be empty (NULL) - notation when listing attributes: underlined attribute name(s)
50
Simple vs composite primary key
Single attribute vs multiple attributes | Student ID number vs security number with birth date
51
Natural attribute vs artificial attribute
Name vs. student ID number
52
A relation will probably be normalize if: (6)
When we insert a new record, we don't have to include more information than might be appropriate at the time When we delete a record, we don't have to delete more information than we intend to When we modify a record, we don't have to modify the information in more than one place -only stores information about one thing or entity -a given fact is stored in only one row -a given fact is only stored in one place in the database
53
First step of normalization
Identify entities in the stored information
54
A type of person, place, object, event, concept about whom/which the organization/user wishes to maintain data to solve a problem
Entity
55
Entity
A type of person, place, object, event, concept about whom/which the organization/user wishes to maintain data to solve a problem -traditionally named in the singular
56
Is an attribute an entity
An attribute is NOT an entity but a feature of an entity that is interesting in the context of the problem we are trying to solve
57
An instance of an entity
Refers to a specific example of an entity in the "real world"
58
Second step of normalization
Recognize relationships between entities
59
Unary relationships
Between instances of the same entity
60
Binary relationships
Between instances of two entities
61
Ternary (n-ary relationships)
Between instances of three (or more entities)
62
One-to-one relationship
Each instance on either side is related to at most one instance in the other side
63
Each instance on either side is related to at most one instance in the other side
One to one relationship
64
Each instance on one side of the relationship can have many related instances, but an instance on the other side will have a maximum of one related instance
One to many relationship
65
One-to-many relationship
each instance on one side of the relationship can have many related instances, but on the other side will have a maximum of one related instance
66
Many-to-many relationship
Instances on both sides of the relationship can have several related instances on the other side
67
Instances on both sides of the relationship can have several related instances on the other side
Many-to-many relationship
68
Third step of normalization
Map the relationships/associations into normalized (well structured), linked relations (tables) (Might have to be repeated several times)
69
An attribute or set of attributes in a relation of a database that serves as the primary key of a different relation in the same database
Foreign key
70
Foreign Key
An attribute or set of attributes in a relation of a database that serves as the primary key of a different relation in the same database
71
An entity whose primary key is a composite attribute of the primaries keys of each relation it links. Attributes of the relationship itself are also stored as attributes of this
Associate entity
72
Associative entity
An entity whose primary key is a composite attribute of the primary keys of each relation it links. Attributes of the relationship itself are also stored as attributes of the associative entity
73
It powers everything from media consumption to appliances, drives modern farming, scientific discovery, and even driving.
The software industry

IT Final (10 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions