SQL Databases

Question 1

join

Answer 1

joins data from 2 or more tables

Question 2

what needed to join

Answer 2

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

Question 3

common domain

Answer 3

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

Question 4

inner join

Answer 4

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

Question 5

join on where

Answer 5

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

Question 6

what if you leave where and the two ids?

Answer 6

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

Question 7

inner join on

Answer 7

can also do that

Question 8

left outer join

Answer 8

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

Question 9

right outer join

Answer 9

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

Question 10

full outer join

Answer 10

select all information(if no where clause

Question 11

join 4 tables

Answer 11

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;

Question 12

self join

Answer 12

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

Question 13

what is subquery?

Answer 13

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

Question 14

subquery example

Answer 14

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

Question 15

what you use to select group or multiple subquery

Answer 15

IN use to get set of values

Question 16

not in

Answer 16

NOT IN use to select other then the query value

Question 17

another subquery

Answer 17

select proddesc, prodstanprice
from product_t
where prodstandprice>
(select AVG(ProductStandPrice) from Product_t);
cant do this with a join

Question 18

subquery table access

Answer 18

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

Question 19

derived table

Answer 19

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

Question 20

derived table example

Answer 20

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

Question 21

Field Data Integrity

Answer 21

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

Question 22

what is denormalization

Answer 22

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

Question 23

denormalization plus minus

Answer 23

wasted space(dup values), but less processsing , 
Potentials- one to one relationships and many to many with non key attributes

Question 24

horizontal partitioning

Answer 24

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

Question 25

vertical partitioning

Answer 25

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

Question 26

dimensional modeling 2 parts

Answer 26

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

Question 27

Olap cubes

Answer 27

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

Question 28

OLAP

Answer 28

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

Question 29

Star Schema OLAP

Answer 29

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

Question 30

olap tools

Answer 30

graphical tools , metho of data visualation

Question 31

cube

Answer 31

multidemensional view of data

Question 32

dimension

Answer 32

sides of a cube

Question 33

measure

Answer 33

value of each cell in cube

Question 34

what is a cube

Answer 34

convert relational format to Dimensional format into cube

Question 35

visualize a cube

Answer 35

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

Question 36

rolap

Answer 36

rleational OLAP - traditional relational representation

Question 37

Multidimensional OLAP

Answer 37

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

Question 38

5 operations with OLAP

Answer 38

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.

Question 39

cube slicing

Answer 39

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

Question 40

Cube dicing

Answer 40

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.

Question 41

Drill down

Answer 41

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

Question 42

Roll-up

Answer 42

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

Question 43

inserting data order

Answer 43

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

Question 44

DML insert

Answer 44

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

Question 45

How to autoincrement

Answer 45

(CUstomerID int IDENTITY(1,1),

Question 46

DML Delete statement

Answer 46

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

Question 47

DML update vs. Alter

Answer 47

update updates column rows, alter modifys columns

Question 48

DML select statement

Answer 48

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

Question 49

WHERE operates on?

Answer 49

Individual rows

Question 50

Distinct used for?

Answer 50

No duplicate values

Question 51

Wildcard

Answer 51

% is any number of char, _ is one

Question 52

WIldcard use

Answer 52

Use LIKE or NOT LIKE when using wildcard

Question 53

Boolian

Answer 53

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

Question 54

What does "AS" do?

Answer 54

It creates an Alias ,, ProductStandPrice * .9 AS 'DIscprice'

Question 55

What does COUNT do?

Answer 55

Aggregate function to find totals

Question 56

What does SUM do?

Answer 56

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

Question 57

Group by

Answer 57

indicate categorization of results

Question 58

HAVING

Answer 58

Conditions in which a category will be included

Question 59

ORDER BY

Answer 59

sorts results based on criteria

Question 60

ORDER BY Clause

Answer 60

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.