7/06/16 Group bys

Today we basically did a exercise on group by for SQL server 2008 but I am kind of having problems because of the dang syntax but plan to do this exercise later on.

But I need to make sure [ bracket instead as that seems to be a common problem with me.

We also went over the queries for milestone 3 which is due in a week and 4 days.

7/06/16 Group bys

3/06/16 data stored and DB admin

Cluster indexes called a heap.

Hash key will find the thing you are after in the heap.

Root of the tree, nodes and leaves make tree diagram.

Second type of a tree has root and a node which can be stored as an index.

Bee trees keys are called M and you can have up to 4 links +1.

Changes every time you insert or delete data

Balance tree keeps balancing it’s self

Cluster tables have a thing called clustered index and when you select data it will change the tables index.

Allocation units are used to manage data in a heap and b-table.

NOTE TO SELF :Study from the videos later

CREATE INDEX (table name and row) ON (Table)

CREATE Clustered index idx_Product_Name_Price on dbo.Product(ProductName ASC, ProductPrice ASC);
go

 

create NONCLUSTERED index idx_Vendor_City on dbo.Vendor(VendorCity ASC, VendorName ASC);
go

3/06/16 data stored and DB admin

CRUD tables 27/05/16

Today we went over the CRUD table which is a process that you can check if you got all the processes and the entities and also to check where creates, retrieves, updates and deletes will happen in the database.

When ever you are adding a record it will hit all the attributes in the tables that it hits.

CRUD is also good to check your attributes and relations.

 

CRUD tables 27/05/16

Joins tables 24/05/16

Today we went over the join tables in class today and here are the following notes that I wrote from the resources given.

A join works in 2 ways it specifies the 2 column’s to join the tables together by the foreign keys.

You don’t have to have inner join you can just have join.

A example of an inner join is:

USE AdventureWorks2008R2;
GO
SELECT *
FROM HumanResources.Employee AS e
 INNER JOIN Person.Person AS p
 ON e.BusinessEntityID = p.BusinessEntityID
ORDER BY p.LastName

The above inner join is also known as an equi-join and will return all the columns from both of the tables but it returns only rows that has an equal value from the join table.

E =employee table

p = person table

Joins Using Operators Other Than Equal

You also don’t have to have 2 equal columns you can also have a AND clause.

USE AdventureWorks2008R2;
GO
SELECT DISTINCT p.ProductID, p.Name, p.ListPrice, sd.UnitPrice AS ‘Selling Price’
FROM Sales.SalesOrderDetail AS sd
JOIN Production.Product AS p
ON sd.ProductID = p.ProductID AND sd.UnitPrice < p.ListPrice
WHERE p.ProductID = 718;
GO

 

Joins Using the Not-equal Operator

You can also use just the one table or two tables with the same name but you must have the where clause.

USE AdventureWorks2008R2;
GO
SELECT DISTINCT p1.ProductSubcategoryID, p1.ListPrice
FROM Production.Product p1
INNER JOIN Production.Product p2
ON p1.ProductSubcategoryID = p2.ProductSubcategoryID
AND p1.ListPrice <> p2.ListPrice
WHERE p1.ListPrice < $15 AND p2.ListPrice < $15
ORDER BY ProductSubcategoryID;

OUTER JOIN

There a 3 different types of outer joins and these are left join, right join and full join.

 

 

 

 

 

Joins tables 24/05/16

13/05/16 Continue of using SQL

Today we basically went over creating tables in SQL which is CREATE Table Example_name(column data_type,┬ácolumn data_type….)

We also went over what relation databases look like and it basically shows each row is a record.

Also went over the different data types which are integer, decimal, char, varchar and date.

We also went over selecting a table which can be the whole table Select * from table_name or Select product_description * from table_name.

We also learnt how to restrict rows where it equals for a example a certain name which will only show names that equal that or another example is greater than which will only show data that is greater than the specific number.

13/05/16 Continue of using SQL