Brainscape's Knowledge GenomeTM

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


See full index

Data Mining > SQL Databases > Flashcards

SQL Databases Flashcards

1
Q

join

A

joins data from 2 or more tables

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

what needed to join

A

common attribute between tables. must have tables related to each other in Common Domain

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

common domain

A

foreign key in one table on the many side correspond to primary key on the other tables(one side) share common domain

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

inner join

A

A join in which the joining condition is based on equality between values in the common columns. example customer and order table. not all customers will have order but will link between those who do. middle overlapping circles filled

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

join on where

A

when doing join on, select in the top the id on the 1 side not fk many side(multi)

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

what if you leave where and the two ids?

A

cartesian problem, you’ll have 150 rows instead of 15

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

inner join on

A

can also do that

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

left outer join

A

from customer.t left outer join order.t on
This selects all customers(left table) even if they dont have order. vin shows left circle full. middle(common) full, right side empty.
it could show customer multiple times

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

right outer join

A

from customer.t right outer join order.t on

will select all. every order. dont always need right outer join, can switch table order and do left outer join

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

full outer join

A

select all information(if no where clause

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

join 4 tables

A

where customer.t.customerID=order_t.customerid AND

order_t.orderID=orderline)t.orderid AND Orderline_t.productID=Product_t.ProductID AND Order_t.orderID=1006;

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

self join

A

rename the same table in the query. example find manager.
select e.employeeid, m.employee_name as manager
from employee as e, employee as m
where e.employeeid=m.managerid

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

what is subquery?

A

placing an inner query (select,from,where) within a where or having clause of another(outer) query

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

subquery example

A

select cusname,address,
from customer
where customerid=
(select customerid from order_t where OrderID= 10008)

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

what you use to select group or multiple subquery

A

IN use to get set of values

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

not in

A

NOT IN use to select other then the query value

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

another subquery

A 
select proddesc, prodstanprice
from product_t
where prodstandprice>
(select AVG(ProductStandPrice) from Product_t);
cant do this with a join
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

subquery table access

A

you cannot access information from inner query table in outer query if that table not in outer query. they are separate

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

derived table

A

if selecting a subquery in from clause you can add a new temp. dirived table

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

derived table example

A

you can create the derived table in the from subquery and give it a single attribute(computed value) .
select ProdcutDes, prodstandprice, AvgPrice
from
(select AVG(ProductStandarPrice) FROM
Product_t) Derived_t (AvgPrice), Product_t
where productstandardprice > AvgPrice

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

Field Data Integrity

A

Default values- assume value if none present
Range Control-Allowable value limitiations
Null value control - allow or prohibit empty fields
Referentia integrity-range control, and null value allowances for foreign-key to primary-key matchups

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

what is denormalization

A

combining tables for efficiency. a large number of tables will have large number of joins potentially , computing power.

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

denormalization plus minus

A 
wasted space(dup values), but less processsing , 
Potentials- one to one relationships and many to many with non key attributes
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

horizontal partitioning

A

Horizontal Partitioning: Distributing the rows of a table into several separate files
Each table created from partitioning has the same columns.
Useful for situations where different users need access to different rows
Example: Partitioning five year purchase data into five separate files each corresponding to a year

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

vertical partitioning

A

Vertical Partitioning: Distributing the columns of a table into several separate relations
The primary key must be repeated in each file
Useful for situations where different users need access to different columns
Example: Partitioning payment data into Cash Payment and Card Payment

26
Q

dimensional modeling 2 parts

A

rolap - star scheme - like relational but tables named in different way. 2nd is Online Analytical Processes-visuallization of multidemesnial database environment

27
Q

Olap cubes

A

Olap cubes deliver superior query performance because of precalculations, indexing stragegies,

28
Q

OLAP

A

used with Cubes, can see multidemensional data in hyperdimensional cube. preprocessed data used in a cube

29
Q

Star Schema OLAP

A

you will have main fact table(just like my schedul) and then you have the dimensional tables just like mine all connecting to the fact table. these tables dont change much, patient, pyshician, etc. the claim table(think my schedule) changes all the time each transaction

30
Q

olap tools

A

graphical tools , metho of data visualation

31
Q

cube

A

multidemensional view of data

32
Q

dimension

A

sides of a cube

33
Q

measure

A

value of each cell in cube

34
Q

what is a cube

A

convert relational format to Dimensional format into cube

35
Q

visualize a cube

A

so convert from Purchaes, name description, location , quantity. left to right apple,bat,dog,front to back amy best charlie, up down dalton armore, boston, then in the cell is the value

36
Q

rolap

A

rleational OLAP - traditional relational representation

37
Q

Multidimensional OLAP

A

MOLAP dat in multidimensioanl formats ‘data cubes’ to answer queries faster.

38
Q

5 operations with OLAP

A

Cube Slicing - come up with 2d view of data by fixing a dimension.
Cube Dicing - come up with a smal cubes by selecting a subset of all dimensions
Drill-down going from summary to more detailed views
Roll-up going from detailed views to a summary view
Pivoting - to rotate the cube accross a dimension to see various faces.

39
Q

cube slicing

A

come up with 2-d view. think of just viewing Amy(in 3rd dimension.

40
Q

Cube dicing

A

you fix some of the attributes to give smaller cube. by selecting subset to all dimensions. slice would be across 1 dimension, dice would be across multiple dimensions.

41
Q

Drill down

A

go from summary to more detailed, think of going from monthly data, to weekly, to daily

42
Q

Roll-up

A

going from detailed to summary. aggregate data, say county data to state, to nation.

43
Q

inserting data order

A

must be in order of fields unless you specify the fields first in (userid,name) values(1,pete)

44
Q

DML insert

A

INSERT INTO TABLE1 SELECT * from CUSTTABLE WHERE ST = ‘CA’;

45
Q

How to autoincrement

A

(CUstomerID int IDENTITY(1,1),

46
Q

DML Delete statement

A

delete from Table1 where ST = ‘CA’ - deletes rows matching. Delete from Table1 -deletes all rows

47
Q

DML update vs. Alter

A

update updates column rows, alter modifys columns

48
Q

DML select statement

A

SELECT(selects columns) and FROM(table) are required, WHERE is not

49
Q

WHERE operates on?

A

Individual rows

50
Q

Distinct used for?

A

No duplicate values

51
Q

Wildcard

A

% is any number of char, _ is one

52
Q

WIldcard use

A

Use LIKE or NOT LIKE when using wildcard

53
Q

Boolian

A

If multiple Boolean operators are used, NOT is first evaluated, then AND, then OR but you can use () to override the precedence.

54
Q

What does “AS” do?

A

It creates an Alias ,, ProductStandPrice * .9 AS ‘DIscprice’

55
Q

What does COUNT do?

A

Aggregate function to find totals

56
Q

What does SUM do?

A

Aggregate function to get sum. SELECT SUM(Orderedquant) as ‘SUMTotal’

57
Q

Group by

A

indicate categorization of results

58
Q

HAVING

A

Conditions in which a category will be included

59
Q

ORDER BY

A

sorts results based on criteria

60
Q

ORDER BY Clause

A

SELECT CustomerName, CustomerState
FROM CUSTOMER_T
WHERE CustomerState IN (‘FL’, ‘TX’, ‘CA’)
ORDER BY CustomerState, CustomerName;
will default sort Customer State A to Z first (ascending)
then alphabetically within state by customer name.
if you put CustomerName(DESC), then within the state would be reverse order.

Data Mining (6 decks)

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