Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

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
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
26
Q

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.

A

LEFT JOIN

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

LEFT JOIN

A

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.

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

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

A

RIGHT JOIN

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

RIGHT JOIN

A

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

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

Firms use information for what two purposes?

A

Transactional and analytical

31
Q

Transactional purposes

A

Day-to-day operations

32
Q

Analytical purposes

A

Trend analyses, forecasts, and input generation for strategies that improve profit or long-term sustainability - compete on analytics

33
Q

A named, two dimensional table of data which consists of rows (records) and columns (attributes/fields)

A

Relation

34
Q

Requirements for a table to qualify as a relation (5)

A

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
Q

Consists of one or more (probably interconnected) relations

A

Relational database

36
Q

Relational database

A

Consists of one or more (probably interconnected) relations

37
Q

3 crucial activities of a relational database

A
  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
Q

An attribute or set of attributes that uniquely identifies each row

A

Primary key

39
Q

Primary key

A

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
Q

Simple vs composite primary keys

A

Single attribute vs. multiple attributes

Student ID number vs. so oak security number with birthdate

41
Q

Natural attribute vs artificial attribute

A

Name vs student ID number

42
Q

A named, two dimensional table of data which consists of rows (records) and columns (attributes or fields)

A

A relation

43
Q

A relation

A

A named, two dimensional table of data which consists of rows (records) and columns (attributes or fields)

44
Q

5 requirements for a table to qualify as a relation

A

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
Q

Consists of one or more (probably interconnected) relations

A

Relational database

46
Q

Relational database

A

Consists of one or more (probably interconnected) relations

47
Q

Three crucial activities of a relational database

A
  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
Q

An attribute or set of attributes that uniquely identifies each row

A

Primary key

49
Q

Primary key

A

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
Q

Simple vs composite primary key

A

Single attribute vs multiple attributes

Student ID number vs security number with birth date

51
Q

Natural attribute vs artificial attribute

A

Name vs. student ID number

52
Q

A relation will probably be normalize if: (6)

A

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
Q

First step of normalization

A

Identify entities in the stored information

54
Q

A type of person, place, object, event, concept about whom/which the organization/user wishes to maintain data to solve a problem

A

Entity

55
Q

Entity

A

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
Q

Is an attribute an entity

A

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
Q

An instance of an entity

A

Refers to a specific example of an entity in the “real world”

58
Q

Second step of normalization

A

Recognize relationships between entities

59
Q

Unary relationships

A

Between instances of the same entity

60
Q

Binary relationships

A

Between instances of two entities

61
Q

Ternary (n-ary relationships)

A

Between instances of three (or more entities)

62
Q

One-to-one relationship

A

Each instance on either side is related to at most one instance in the other side

63
Q

Each instance on either side is related to at most one instance in the other side

A

One to one relationship

64
Q

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

A

One to many relationship

65
Q

One-to-many relationship

A

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
Q

Many-to-many relationship

A

Instances on both sides of the relationship can have several related instances on the other side

67
Q

Instances on both sides of the relationship can have several related instances on the other side

A

Many-to-many relationship

68
Q

Third step of normalization

A

Map the relationships/associations into normalized (well structured), linked relations (tables)
(Might have to be repeated several times)

69
Q

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

A

Foreign key

70
Q

Foreign Key

A

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
Q

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

A

Associate entity

72
Q

Associative entity

A

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
Q

It powers everything from media consumption to appliances, drives modern farming, scientific discovery, and even driving.

A

The software industry

IT Final (10 decks)

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