Brainscape's Knowledge GenomeTM

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


See full index

IS 335 > exam 3 - Basic SQL Queries > Flashcards

exam 3 - Basic SQL Queries Flashcards

1
Q

SQL Sub-Structures

A

DML: Data Manipulation Language
DDL: Data Definition Language
DCL: Data Control Language

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

DML: Data Manipulation Language
◦Select Data
◦Modify Data

A

DML: Data Manipulation Language
◦Select Data
◦Modify Data

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

DDL: Data Definition Language
◦Create Artifacts
◦Drop Artifacts
◦Modify Artifacts

A

DDL: Data Definition Language
◦Create Artifacts
◦Drop Artifacts
◦Modify Artifacts

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

DCL: Data Control Language

◦Securing Artifacts

A

DCL: Data Control Language

◦Securing Artifacts

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

Define Database Objects

A

Define Database Objects

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

null [important]

A

unknown

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

Data Control Language [3 important words]
Manage security and Permissions
[3]

A

manage security and permission

  • grant - set it
  • revoke - remove
  • deny - trump card [you can deny it to a certain user]
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Data manipulation language [4 key words, important for the exam]
select and modify data
[4]

A 
Select and modify Data
- select - read the data 
- insert
 - update 
 - delete 
Most SQL that you will write will fall into this category
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Database queries return _____

A

Database queries return information

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

A SQL Query is structured in sections

- select :

A

SELECT: Column list to return

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

A SQL Query is structured in sections

- from:

A

FROM: Table list which will be the source

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

A SQL Query is structured in sections

- where:

A

WHERE: Row filter criteria

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

A SQL Query is structured in sections

order by:

A

ORDER BY: Sort Rules

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

A SQL Query is structured in sections

- group by:

A

GROUP BY: Organize columns into groups

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

A SQL Query is structured in sections

having:

A

HAVING: Group filter criteria

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

Simplest form requires only

[2]

A

Simplest form requires only

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

Select all columns by using a *

[2]

A

Select all columns by using a *

  • careful not to ask for more than you need
  • take extra time to resolve SELECT list
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Providing a list of columns is better

[2]

A

Providing a list of columns is better

  • reduce data
  • explicit Resolution
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Returning Calculated Values

- A SELECT list can also contain

A

A SELECT list can also contain calculated data

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

Returning Calculated Values

- Encourages the return of

A

Encourages the return of relevant data

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

Returning Calculated Values

- Reassembles

A

Reassembles normalized data

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

SELECT AuthorID, FirstName + ‘ ‘ + LastName as NameFROM Authors

A

select the column first +

adding a space + Last Name as Name (make a new column named Name, Name isn’t in the Data”)

FROM Authors
(getting it from the Authors files)

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

as statements

A

gives a name for the column

24
Q

The WHERE clause allows

A

The WHERE clause allows row filtering

25
Q

where clause

- Further reduces data

A

Further reduces

26
Q

where clause

- With indexes, can improve ______

A

With indexes, can improve performance

27
Q

where clause

- Filters can use data not in the ______ ______

A

Filters can use data not in the Select List

28
Q

where clause

A

filters it to only a certain word u use

for example if you want the data to only show motorcycles

WHERE LastName = ‘motorcycles’

29
Q

Standard comparison operators can be used in the WHERE clause

A

, <=, >=, =, <>

30
Q

Can compare strings or numbers

◦Depending on the

A

Can compare strings or numbers

◦Depending on the install, comparisons may be case-sensitive

31
Q

If you want revenues less than 50 the string would be

A

WHERE revenue < 50

32
Q

Logical Operators
- Complex filters can be created using logical operators
[3]

A
  • and
  • or
  • not
33
Q

String Comparisons

When comparing strings, use the LIKE operator with Wildcards
◦
◦
◦

A

String Comparisons

When comparing strings, use the LIKE operator with Wildcards
◦%, replace 0 to many characters
◦_, replace a single character
◦[], replace a single specific character

34
Q

s%

A

s% = anything that begins with a letter S (S%)

35
Q

s_

A

s_ = anything that has one character after S (S_)

36
Q

s[] = A[tup]%

A

s[] = a [t OR u OR p] % [ only 3 letters]

37
Q

need to use ____ for WildCards

A

need to use LIKE for WildCards

38
Q

Using Lists

  • Use the IN operator to filter __
  • Similar to using an OR ____
A

Using Lists

  • Use the IN operator to filter by values in a list
  • Similar to using an OR operator
39
Q

SELECT SlotMast_ID, SlotNumber FROM CDS_SlotMastWHERE SlotMast_ID = 1 OR SlotMast_ID = 2

SELECT SlotMast_ID, SlotNumber FROM CDS_SlotMastWHERE SlotMast_ID IN (1,2)

A

same thing

just easier to use IN so you have to type an OR statement

40
Q

Data Ranges

  • ____ ______ can use the BETWEEN operator
  • BETWEEN always includes the ______
A

Data Ranges

  • Range filters can use the BETWEEN operator
  • BETWEEN always includes the endpoints
41
Q

BETWEEN 1 AND 15

A

get 1-15

42
Q

Removing Duplicates

  • Filtering out _____ requires the DISTINCT keyword in the SELECT list
  • Reduce __ ___ ..
A

Removing Duplicates

  • Filtering out duplicates requires the DISTINCT keyword in the SELECT list
  • Reduce redundant data which lowers bandwidth
43
Q

Using Functions
- SQL has ___
◦
◦

A

Using Functions
- SQL has numerous built-in functions
◦CURDATE
◦COUNT, AVG, MIN, MAX

44
Q

Using Functions a in SELECT list

- SELECT Count(*) FROM Authors:

A

Using Functions a in SELECT list

- SELECT Count(*) FROM Authors: number of rows

45
Q

WHERE LastLoggedInDate > CURDATE()

A

WHERE LastLoggedInDate > CURDATE():

people who login before our current date

46
Q

Sorting Data

  • Sort data using the ORDER BY ____
  • Multiple sort columns are separated by _____
  • You can sort by any ___ ___ ; does not have to be in the SELECT list
A

Sorting Data

  • Sort data using the ORDER BY clause
  • Multiple sort columns are separated by commas
  • You can sort by any available field; does not have to be in the SELECT list
47
Q

Grouping Data

  • The GROUP BY clause allows you to
  • Any data not grouped must be
  • Use Functions for
A

Grouping Data

  • The GROUP BY clause allows you to aggregate data into groups
  • Any data not grouped must be aggregated
  • Use Functions for aggregation
48
Q 
Joining Tables
- Relational databases separate data into multiple tables
[2] 
◦ 
◦
A 
Joining Tables
- Relational databases separate data into multiple tables
[2] 
◦Reduces data redundancy
◦Eases data maintenance
49
Q

Joining Tables
- data in different tables can be correlated
[2]

A

Joining Tables

  • data in different tables can be correlated
  • use join statements
  • correlate on common data fields
50
Q

Joining tables

- cautions:

A

Joining tables

- cautions: can reduce performance

51
Q

The JOIN Operator

  • Correlates data by
  • You must identify the
A

The JOIN Operator

  • Correlates data by finding matching data in two different tables
  • You must identify the tables and the correlation fields
52
Q

inner join

A

add two tables and correlates the data between each other

53
Q

SQL is NOT a programming language

A

SQL is NOT a programming language

54
Q

Data manipulation language

A

Data manipulation language

55
Q

•Lacks many programming constructs

A

•Lacks many programming constructs

IS 335 (15 decks)

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