SQL Databases Flashcards
join
joins data from 2 or more tables
what needed to join
common attribute between tables. must have tables related to each other in Common Domain
common domain
foreign key in one table on the many side correspond to primary key on the other tables(one side) share common domain
inner join
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
join on where
when doing join on, select in the top the id on the 1 side not fk many side(multi)
what if you leave where and the two ids?
cartesian problem, you’ll have 150 rows instead of 15
inner join on
can also do that
left outer join
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
right outer join
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
full outer join
select all information(if no where clause
join 4 tables
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;
self join
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
what is subquery?
placing an inner query (select,from,where) within a where or having clause of another(outer) query
subquery example
select cusname,address,
from customer
where customerid=
(select customerid from order_t where OrderID= 10008)
what you use to select group or multiple subquery
IN use to get set of values
not in
NOT IN use to select other then the query value
another subquery
select proddesc, prodstanprice from product_t where prodstandprice> (select AVG(ProductStandPrice) from Product_t); cant do this with a join
subquery table access
you cannot access information from inner query table in outer query if that table not in outer query. they are separate
derived table
if selecting a subquery in from clause you can add a new temp. dirived table
derived table example
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
Field Data Integrity
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
what is denormalization
combining tables for efficiency. a large number of tables will have large number of joins potentially , computing power.
denormalization plus minus
wasted space(dup values), but less processsing , Potentials- one to one relationships and many to many with non key attributes
horizontal partitioning
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
vertical partitioning
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
dimensional modeling 2 parts
rolap - star scheme - like relational but tables named in different way. 2nd is Online Analytical Processes-visuallization of multidemesnial database environment
Olap cubes
Olap cubes deliver superior query performance because of precalculations, indexing stragegies,
OLAP
used with Cubes, can see multidemensional data in hyperdimensional cube. preprocessed data used in a cube
Star Schema OLAP
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
olap tools
graphical tools , metho of data visualation
cube
multidemensional view of data
dimension
sides of a cube
measure
value of each cell in cube
what is a cube
convert relational format to Dimensional format into cube
visualize a cube
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
rolap
rleational OLAP - traditional relational representation
Multidimensional OLAP
MOLAP dat in multidimensioanl formats ‘data cubes’ to answer queries faster.
5 operations with OLAP
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.
cube slicing
come up with 2-d view. think of just viewing Amy(in 3rd dimension.
Cube dicing
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.
Drill down
go from summary to more detailed, think of going from monthly data, to weekly, to daily
Roll-up
going from detailed to summary. aggregate data, say county data to state, to nation.
inserting data order
must be in order of fields unless you specify the fields first in (userid,name) values(1,pete)
DML insert
INSERT INTO TABLE1 SELECT * from CUSTTABLE WHERE ST = ‘CA’;
How to autoincrement
(CUstomerID int IDENTITY(1,1),
DML Delete statement
delete from Table1 where ST = ‘CA’ - deletes rows matching. Delete from Table1 -deletes all rows
DML update vs. Alter
update updates column rows, alter modifys columns
DML select statement
SELECT(selects columns) and FROM(table) are required, WHERE is not
WHERE operates on?
Individual rows
Distinct used for?
No duplicate values
Wildcard
% is any number of char, _ is one
WIldcard use
Use LIKE or NOT LIKE when using wildcard
Boolian
If multiple Boolean operators are used, NOT is first evaluated, then AND, then OR but you can use () to override the precedence.
What does “AS” do?
It creates an Alias ,, ProductStandPrice * .9 AS ‘DIscprice’
What does COUNT do?
Aggregate function to find totals
What does SUM do?
Aggregate function to get sum. SELECT SUM(Orderedquant) as ‘SUMTotal’
Group by
indicate categorization of results
HAVING
Conditions in which a category will be included
ORDER BY
sorts results based on criteria
ORDER BY Clause
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.
