1 / 13

Exam 2 Review: SQL In, Dimensional Modeling, Pivot Tables, ETL

Exam 2 Review: SQL In, Dimensional Modeling, Pivot Tables, ETL. Describe data cube elements Understand facts, dimensions, granularity Create/read a Star Schema How to clean data. ETL in Excel. Take up the Assignment Solutions…. Create a Table. CREATE TABLE `m1orderdb`.`Customer` (

brenna
Download Presentation

Exam 2 Review: SQL In, Dimensional Modeling, Pivot Tables, ETL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Exam 2 Review:SQL In, Dimensional Modeling, Pivot Tables, ETL Describe data cube elements Understand facts, dimensions, granularity Create/read a Star Schema How to clean data

  2. ETL in Excel • Take up the Assignment Solutions…

  3. Create a Table CREATE TABLE `m1orderdb`.`Customer` ( `CustomerID` INT NOT NULL ,`FirstName` VARCHAR(45) NULL , `LastName` VARCHAR(45) NULL , `City` VARCHAR(45) NULL , `State` VARCHAR(2) NULL , `Zip` VARCHAR(10) NULL , PRIMARY KEY (`CustomerID`) );

  4. Data types

  5. Changing a Table’s Structure ALTER TABLE customerADDSkypeID VARCHAR(15) ALTER TABLE customer DROP COLUMN SkypeID ALTER TABLE customer CHANGE COLUMN SkypeIDSkypeID_NEW VARCHAR(15)

  6. Insert a New Row INSERT INTO `m1orderdb`.`Customer` (`CustomerID`, `FirstName`, `LastName`, `City`, `State`, `Zip`) VALUES (1005, 'Chris', 'Taub', 'Princeton', 'NJ', '09120'); Note that field names are surrounded by “back quotes” (`) and string field values are surrounded by “regular quotes” (')

  7. Change a Row (UPDATE) UPDATE `m1orderdb`.`Product` SET ProductName='Honey Nut Cheerios', Price=4.50 WHERE ProductID=2251 Product

  8. DELETE example • DELETE FROM `m1orderdb`.`Customer` WHERE `CustomerID`=1004

  9. The Data Cube • Dimensions (Top Selling Products) • Fill in 3 reasonable values • What’s the fact? • Slice the Data? • Granularity? _______ _____ __________

  10. _______ _____ __________

  11. The Star Schema • Draw our Star Schema, from the Cube.

  12. Pivot Tables • You need to know these too!

More Related