exam 3 - Basic SQL Queries

Question 1

SQL Sub-Structures

Answer 1

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

Question 2

DML: Data Manipulation Language
◦Select Data
◦Modify Data

Answer 2

DML: Data Manipulation Language
◦Select Data
◦Modify Data

Question 3

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

Answer 3

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

Question 4

DCL: Data Control Language

◦Securing Artifacts

Answer 4

DCL: Data Control Language

◦Securing Artifacts

Question 5

Define Database Objects

Answer 5

Define Database Objects

• create
• alter
• drop

Question 6

null [important]

Answer 6

unknown

Question 7

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

Answer 7

manage security and permission

• grant - set it
• revoke - remove
• deny - trump card [you can deny it to a certain user]

Question 8

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

Answer 8

Select and modify Data
- select - read the data 
- insert
 - update 
 - delete 
Most SQL that you will write will fall into this category

Question 9

Database queries return _____

Answer 9

Database queries return information

Question 10

A SQL Query is structured in sections

- select :

Answer 10

SELECT: Column list to return

Question 11

A SQL Query is structured in sections

- from:

Answer 11

FROM: Table list which will be the source

Question 12

A SQL Query is structured in sections

- where:

Answer 12

WHERE: Row filter criteria

Question 13

A SQL Query is structured in sections

order by:

Answer 13

ORDER BY: Sort Rules

Question 14

A SQL Query is structured in sections

- group by:

Answer 14

GROUP BY: Organize columns into groups

Question 15

A SQL Query is structured in sections

having:

Answer 15

HAVING: Group filter criteria

Question 16

Simplest form requires only

[2]

Answer 16

Simplest form requires only

• select
• from

Question 17

Select all columns by using a *

[2]

Answer 17

Select all columns by using a *

• careful not to ask for more than you need
• take extra time to resolve SELECT list

Question 18

Providing a list of columns is better

[2]

Answer 18

Providing a list of columns is better

• reduce data
• explicit Resolution

Question 19

Returning Calculated Values

- A SELECT list can also contain

Answer 19

A SELECT list can also contain calculated data

Question 20

Returning Calculated Values

- Encourages the return of

Answer 20

Encourages the return of relevant data

Question 21

Returning Calculated Values

- Reassembles

Answer 21

Reassembles normalized data

Question 22

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

Answer 22

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)

Question 23

as statements

Answer 23

gives a name for the column

Question 24

The WHERE clause allows

Answer 24

The WHERE clause allows row filtering

Question 25

where clause | - Further reduces data

Answer 25

Further reduces

Question 26

where clause | - With indexes, can improve ______

Answer 26

With indexes, can improve performance

Question 27

where clause | - Filters can use data not in the ______ ______

Answer 27

Filters can use data not in the Select List

Question 28

where clause

Answer 28

filters it to only a certain word u use for example if you want the data to only show motorcycles WHERE LastName = ‘motorcycles’

Question 29

Standard comparison operators can be used in the WHERE clause

Answer 29

, <=, >=, =, <>

Question 30

Can compare strings or numbers | ◦Depending on the

Answer 30

Can compare strings or numbers | ◦Depending on the install, comparisons may be case-sensitive

Question 31

If you want revenues less than 50 the string would be

Answer 31

WHERE revenue < 50

Question 32

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

Answer 32

- and - or - not

Question 33

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

Answer 33

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

Question 34

s%

Answer 34

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

Question 35

s_

Answer 35

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

Question 36

s[] = A[tup]%

Answer 36

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

Question 37

need to use ____ for WildCards

Answer 37

need to use LIKE for WildCards

Question 38

Using Lists - Use the IN operator to filter __ - Similar to using an OR ____

Answer 38

Using Lists - Use the IN operator to filter by values in a list - Similar to using an OR operator

Question 39

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)

Answer 39

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

Question 40

Data Ranges - ____ ______ can use the BETWEEN operator - BETWEEN always includes the ______

Answer 40

Data Ranges - Range filters can use the BETWEEN operator - BETWEEN always includes the endpoints

Question 41

BETWEEN 1 AND 15

Answer 41

get 1-15

Question 42

Removing Duplicates - Filtering out _____ requires the DISTINCT keyword in the SELECT list - Reduce __ ___ ..

Answer 42

Removing Duplicates - Filtering out duplicates requires the DISTINCT keyword in the SELECT list - Reduce redundant data which lowers bandwidth

Question 43

Using Functions - SQL has ___ ◦ ◦

Answer 43

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

Question 44

Using Functions a in SELECT list | - SELECT Count(*) FROM Authors:

Answer 44

Using Functions a in SELECT list | - SELECT Count(*) FROM Authors: number of rows

Question 45

WHERE LastLoggedInDate > CURDATE()

Answer 45

WHERE LastLoggedInDate > CURDATE(): people who login before our current date

Question 46

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

Answer 46

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

Question 47

Grouping Data - The GROUP BY clause allows you to - Any data not grouped must be - Use Functions for

Answer 47

Grouping Data - The GROUP BY clause allows you to aggregate data into groups - Any data not grouped must be aggregated - Use Functions for aggregation

Question 48

``` Joining Tables - Relational databases separate data into multiple tables [2] ◦ ◦ ```

Answer 48

``` Joining Tables - Relational databases separate data into multiple tables [2] ◦Reduces data redundancy ◦Eases data maintenance ```

Question 49

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

Answer 49

Joining Tables - data in different tables can be correlated - use join statements - correlate on common data fields

Question 50

Joining tables | - cautions:

Answer 50

Joining tables | - cautions: can reduce performance

Question 51

The JOIN Operator - Correlates data by - You must identify the

Answer 51

The JOIN Operator - Correlates data by finding matching data in two different tables - You must identify the tables and the correlation fields

Question 52

inner join

Answer 52

add two tables and correlates the data between each other

Question 53

SQL is NOT a programming language

Answer 53

SQL is NOT a programming language

Question 54

Data manipulation language

Answer 54

Data manipulation language

Question 55

•Lacks many programming constructs

Answer 55

•Lacks many programming constructs