Brainscape's Knowledge GenomeTM

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


See full index

Database > Exam 2 > Flashcards

Exam 2 Flashcards

1
Q

LIKE wildcards

A

_ single char, % multichar

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

Get all employee last names starting with G

A

SELECT EmpLast FROM EMPLOYEE WHERE EmpLast LIKE ‘G%’;

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

Sort all employee records by first name

A

SELECT * FROM EMPLOYEE ORDER BY EmpFirst ASC;

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

Using EMPLOYEE, display a count of employees (‘EmpNum’) in each department that has more than 4 employees

A

SELECT DeptID, COUNT() AS EmpNum FROM EMPLOYEE GROUP BY DeptID HAVING COUNT() > 3;

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

What’s a view?

A

A virtual table created from SELECT statements on other tables/views that has no data of its own.

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

Why use a view?

A

To hide columns or rows, assign different permissions to different views, display computation results, hide messy JOINS, and layer built-in functions.

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

DML 3 syntax

A

INSERT UPDATE DELETE

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

DDL 3 syntax

A

CREATE ALTER DROP

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

Add record with EmpID = 62 and SalaryCode = 11 to EMPLOYEE

A

INSERT INTO EMPLOYEE (EmpID, SalaryCode) VALUES (62, 11);

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

Best practices for redesign?

A

Understand the current db, backup errything, and test changes on a test db

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

Dependency graphs

A

Diagrams portraying dependencies of elements (tables, views, triggers) on each other

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

Diagrams portraying the relationships between tables/views/triggers

A

Dependency graphs

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

How to add a NOT NULL col to an existing table

A

1–Add col as NULL 2–Add data to every row 3–ALTER to NOT NULL

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

Turning 1:1 into 1:N

A

Remove UNIQUE constraint on FK, or move FK to the other table (w/ no UNIQUE)

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

Turning 1:N into N:M

A

Make an intersection table!

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

Turning N:M into 1:N

A

Put FK in parent and move values from intersection to there

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

Turning 1:N into 1:1

A

Remove duplicates in FK and make it UNIQUE

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

Indices

A

Structures to improve db performance. Contains a copy of some table data.

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

B-tree

A

Root/branch/leaves; way to do indices.

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

Clustered Index

A

Table sorted to match–can only be 1.

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

Index where table is sorted to match

A

Clustered Index

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

Unclustered Index

A

Unsorted rows in heap

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

Index with unsorted rows in heap

A

Unclustered Index

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

API

A

Application Programming Interface. Collection of obj/meth/props.

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

ODBC

A

Open DB Connectivity. API standard for DBMS-independent way to process relational db data.

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

ODBC data source

A

A db, dbms, and its hardware. Could even be a spreadsheet!

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

Driver manager

A

Intermediary between app and DBMS drivers. Determines type of DBMS for a data source and loads the right driver.

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

Driver

A

Processes requests and submits them to the data source. Can also process requests for shit sources, and translate errors.

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

SQL/PSM

A

Persistent Stored Modules. Standard for embedding procedural prog. funct. Adds user-def fns, triggers, and stored procedures.

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

User-defined functions

A

Called by name from a statement. Returns an output value.

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

Trigger

A

Stored program that’s executed by DBMS when a specified event occurs. ON INSERT/UPDATE/DELETE, BEFORE/INSTEAD OF/AFTER. Useful for complex default value calculations, min card of 1, or audit trails.

32
Q

Stored procedure

A

Program stored in db and compiled when used. Not called from a SELECT statement.

33
Q

Transaction boundary markers

A

BEGIN/COMMIT/ROLLBACK TRANSACTION (often used with IF/ELSE)

34
Q

ACID

A

Atomic (all-or-nothing), Consistent (1-at-a-time per record), Isolated (when shit’s visible), Durable (commits permanent)

35
Q

Dirty Read

A

Record changed but not actually committed

36
Q

Record changed but not actually committed

A

Dirty Read

37
Q

Inconsistent/Non-Repeatable Read

A

Data changed upon re-read

38
Q

Data changed upon re-read

A

Inconsistent/Non-Repeatable Read

39
Q

Phantom Read

A

New data upon re-read

40
Q

New data upon re-read

A

Phantom Read

41
Q

Authorization

A

Having rights to perform certain actions

42
Q

Reprocessing

A

All actions since last backup are manually reperformed.

43
Q

Rollback

A

Undoing shit. Must have a db image from prior.

44
Q

Rollforward

A

Redoing things/updating backup with new changes.

45
Q

Implicit vs explicit locks

A

Implicit issued by DBMS based on activity. Explicit asked for by users.

46
Q

Exclusive lock

A

Block all activity on locked resource

47
Q

Blocks all activity on locked resource

A

Exclusive Lock

48
Q

Shared lock

A

Allows certain activity on locked resource

49
Q

Allows certain activity on locked resource

A

Shared lock

50
Q

Optimistic Locking

A

Only lock resources right before modifying, then check if it’s all okay before COMMIT

51
Q

Pessimistic Locking

A

Lock at start of transaction, then release after COMMIT

52
Q

Reporting vs Stat/Mining Systems

A

Reporting = Descriptive, S/M = Predictive

53
Q

Where does BI data come from?

A

Extracts of operational data and purchased data

54
Q

Data Warehouse

A

Collection of ETLed data from multiple sources.

55
Q

Data Mart

A

Collection of data from warehouse, tailored to a specific department or function

56
Q

Dimensional DB

A

Non-normalized db structures for analytics. Often uses star schema.

57
Q

Star schema

A

Dimensional DB format. 1+ fact table(s) that reference any number of dimension tables. Facts are quant and dimensions are qual, generally.

58
Q

OLAP

A

Online analytical processing. Reporting system.

59
Q

4 types of NoSQL

A

Key-Value, col family/tabular, document, graph

60
Q

XML

A

Extensible markup lang. 123

61
Q

Graph DB structure

A

Nodes, properties, edges (relationships)

62
Q

Tabular DB structure

A

Cols like cells, they contain name/val/timestamp. Can be grouped into supercolumns. Rows of grouped cols.

63
Q

Get list of departments from EMPLOYEE with no repeats

A

SELECT DISTINCT Dept FROM EMPLOYEE;

64
Q

Get employee records from Accounting department or over age 50

A

SELECT * FROM EMPLOYEE WHERE Dept = ‘Accounting’ OR Age > 30;

65
Q

Get employee records from those age between 55 and 65 inclusive

A

SELECT * FROM EMPLOYEE WHERE Age BETWEEN 55 AND 65;

66
Q

Get employee records where SalaryCode = 11, 12, or 33

A

SELECT * FROM EMPLOYEE WHERE SalaryCode IN (11, 12, 33);

67
Q

Get employee records with a value for EmpMid

A

SELECT * FROM EMPLOYEE WHERE EmpMid IS NOT NULL;

68
Q

Get employee records in reverse-alphabetical order

A

SELECT * FROM EMPLOYEE ORDER BY LastName DESC;

69
Q

Get employee last names displayed Last

A

SELECT EmpLast AS Last FROM EMPLOYEE;

70
Q

Get employee ages divided by salary code (why not?)

A

SELECT Age / SalaryCode FROM EMPLOYEE;

71
Q

Get employee last names and their department building number

A

SELECT EmpLast, Bldg FROM EMPLOYEE e JOIN DEPARTMENT d ON e.Dept = d.Dept;

72
Q

Get employee records for those who work in building 230 (subquery)

A

SELECT * FROM EMPLOYEE WHERE Dept IN (SELECT Dept FROM DEPARTMENT WHERE Bldg = 230);

73
Q

Get rid of all records of employees named Carl

A

DELETE FROM EMPLOYEE WHERE EmpFirst = ‘Carl’;

74
Q

Change the salary code of all employees who work in accounting to 69

A

UPDATE EMPLOYEE SET SalaryCode = 69 WHERE Dept = ‘Accounting’;

75
Q

Multi-table join syntax

A

SELECT * FROM EMPLOYEE JOIN DEPARTMENT ON …. JOIN OTHER_THING ON ….

Database (2 decks)

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