Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

Database > Exam 2 > Flashcards

Exam 2 Flashcards

(75 cards)

Start Studying
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
ODBC
Open DB Connectivity. API standard for DBMS-independent way to process relational db data.
26
ODBC data source
A db, dbms, and its hardware. Could even be a spreadsheet!
27
Driver manager
Intermediary between app and DBMS drivers. Determines type of DBMS for a data source and loads the right driver.
28
Driver
Processes requests and submits them to the data source. Can also process requests for shit sources, and translate errors.
29
SQL/PSM
Persistent Stored Modules. Standard for embedding procedural prog. funct. Adds user-def fns, triggers, and stored procedures.
30
User-defined functions
Called by name from a statement. Returns an output value.
31
Trigger
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
Stored procedure
Program stored in db and compiled when used. Not called from a SELECT statement.
33
Transaction boundary markers
BEGIN/COMMIT/ROLLBACK TRANSACTION (often used with IF/ELSE)
34
ACID
Atomic (all-or-nothing), Consistent (1-at-a-time per record), Isolated (when shit's visible), Durable (commits permanent)
35
Dirty Read
Record changed but not actually committed
36
Record changed but not actually committed
Dirty Read
37
Inconsistent/Non-Repeatable Read
Data changed upon re-read
38
Data changed upon re-read
Inconsistent/Non-Repeatable Read
39
Phantom Read
New data upon re-read
40
New data upon re-read
Phantom Read
41
Authorization
Having rights to perform certain actions
42
Reprocessing
All actions since last backup are manually reperformed.
43
Rollback
Undoing shit. Must have a db image from prior.
44
Rollforward
Redoing things/updating backup with new changes.
45
Implicit vs explicit locks
Implicit issued by DBMS based on activity. Explicit asked for by users.
46
Exclusive lock
Block all activity on locked resource
47
Blocks all activity on locked resource
Exclusive Lock
48
Shared lock
Allows certain activity on locked resource
49
Allows certain activity on locked resource
Shared lock
50
Optimistic Locking
Only lock resources right before modifying, then check if it's all okay before COMMIT
51
Pessimistic Locking
Lock at start of transaction, then release after COMMIT
52
Reporting vs Stat/Mining Systems
Reporting = Descriptive, S/M = Predictive
53
Where does BI data come from?
Extracts of operational data and purchased data
54
Data Warehouse
Collection of ETLed data from multiple sources.
55
Data Mart
Collection of data from warehouse, tailored to a specific department or function
56
Dimensional DB
Non-normalized db structures for analytics. Often uses star schema.
57
Star schema
Dimensional DB format. 1+ fact table(s) that reference any number of dimension tables. Facts are quant and dimensions are qual, generally.
58
OLAP
Online analytical processing. Reporting system.
59
4 types of NoSQL
Key-Value, col family/tabular, document, graph
60
XML
Extensible markup lang. 123
61
Graph DB structure
Nodes, properties, edges (relationships)
62
Tabular DB structure
Cols like cells, they contain name/val/timestamp. Can be grouped into supercolumns. Rows of grouped cols.
63
Get list of departments from EMPLOYEE with no repeats
SELECT DISTINCT Dept FROM EMPLOYEE;
64
Get employee records from Accounting department or over age 50
SELECT * FROM EMPLOYEE WHERE Dept = 'Accounting' OR Age > 30;
65
Get employee records from those age between 55 and 65 inclusive
SELECT * FROM EMPLOYEE WHERE Age BETWEEN 55 AND 65;
66
Get employee records where SalaryCode = 11, 12, or 33
SELECT * FROM EMPLOYEE WHERE SalaryCode IN (11, 12, 33);
67
Get employee records with a value for EmpMid
SELECT * FROM EMPLOYEE WHERE EmpMid IS NOT NULL;
68
Get employee records in reverse-alphabetical order
SELECT * FROM EMPLOYEE ORDER BY LastName DESC;
69
Get employee last names displayed Last
SELECT EmpLast AS Last FROM EMPLOYEE;
70
Get employee ages divided by salary code (why not?)
SELECT Age / SalaryCode FROM EMPLOYEE;
71
Get employee last names and their department building number
SELECT EmpLast, Bldg FROM EMPLOYEE e JOIN DEPARTMENT d ON e.Dept = d.Dept;
72
Get employee records for those who work in building 230 (subquery)
SELECT * FROM EMPLOYEE WHERE Dept IN (SELECT Dept FROM DEPARTMENT WHERE Bldg = 230);
73
Get rid of all records of employees named Carl
DELETE FROM EMPLOYEE WHERE EmpFirst = 'Carl';
74
Change the salary code of all employees who work in accounting to 69
UPDATE EMPLOYEE SET SalaryCode = 69 WHERE Dept = 'Accounting';
75
Multi-table join syntax
SELECT * FROM EMPLOYEE JOIN DEPARTMENT ON .... JOIN OTHER_THING ON ....

Database (2 decks)

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