1 / 69

Datawarehouse

Datawarehouse. Summarizing With SQL. Febrianti Supardinah. 05. FASILKOM. Teknik Informatika. Client Server Database. Server Side Database Engine Security Fault-tolerance Performance Concurrency Reliable backup. Client Side User Interface Forms Reports Queries.

plato-moore
Télécharger la présentation

Datawarehouse

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. Datawarehouse Summarizing With SQL Febrianti Supardinah 05 FASILKOM Teknik Informatika

  2. Client Server Database Server Side • Database Engine • Security • Fault-tolerance • Performance • Concurrency • Reliable backup Client Side • User Interface • Forms • Reports • Queries

  3. Client Server Database

  4. Database Object • Table • Row • Column • Data Type • Stored Procedure • View • Index

  5. Login to SQL Server

  6. SQL Environment • Data Definition Language (DDL) • Commands that define a database, including creating, altering, and dropping tables and establishing constraints • CREATE, ALTER, DROP • Data Manipulation Language (DML) • Commands that maintain and query a database • SELECT, INSERT, UPDATE, DELETE • Data Control Language (DCL) • Commands that control a database, including administering privileges and committing data • GRANT, DENY, REVOKE

  7. SQL Environment 7

  8. Relational Data Model 8

  9. Relational Data Model

  10. Create Database • Start the Management Studio • Connect to your SQL Server • Right click the Databases folder in the console tree, choose New Database from the context menu • Fill in the boxes in the database properties sheet • Click OK when you are finished

  11. Create Table • Start the Management Studio • Drill down to the target database (ex. MIS), and expand it • Right click on Tables and select New Table • Type the column name and data type, and setup column properties (in the window at the bottom of the screen) • Click on the Save button, enter a name for the table and click OK

  12. Create Table CREATE TABLE [dbo].[par_branch]( [BRANCH] [varchar](4) NOT NULL, [BRANCH_NAME] [varchar](200) NULL, [KCP] [varchar](4) NULL, [KC] [varchar](4) NULL, [KANWIL] [varchar](50) NULL, [NAMA_KANWIL] [varchar](200) NULL, [GROUP_BRANCH] [varchar](4) NULL, [DATI] [varchar](4) NULL, [SANDI] [varchar](9) NULL ) ON [PRIMARY] Primary keys can never have NULL values

  13. Create View • Views provide users controlled access to tables • No data actually stored; instead data from base table made available to user • Based on SQL SELECT statement on base tables or other views • Simplify query commands • Assist with data security • Enhance programming productivity • Contain most current base table data • Use little storage space • Provide customized view for user • Use processing time each time view is referenced (-) • May or may not be directly updateable (-)

  14. Create View • Start the Management Studio • Drill down to the target database • Expand the database and locate View • Right click on View and select New View • In Tables page, select target table and click Add • Edit the view definition in the appearing GUI • Click the Save button • Name the view and save it

  15. Create View CREATE VIEW V_STG_CASA AS SELECT * FROM stg_casa CREATE VIEW V_STG_L_CASA AS SELECT * FROM stg_l_casa * is the wildcard

  16. Changing and Removing Table • ALTER TABLE statement allows you to change column specifications: • ALTER TABLE stg_cif • ADD (TYPE VARCHAR(2)) • DROP TABLE statement allows you to remove tables from your schema: • DROP TABLE stg_cif

  17. Create Index • Speed up random/sequential access to base table data • Example • CREATE INDEX NAME_IDX ON stg_cif(cust_name) • This makes an index for the cust_name field of the stg_cif table

  18. Adds data to a table Inserting a record with all fields INSERT INTO par_branch VALUES (‘0002’,’KC ACHMAD YANI‘,’0002’,’1111’,’KANWIL JABAR’, ‘0191’,’213001000’) Inserting a record with specified fields INSERT INTO par_branch (branch, branch_name) VALUES (‘0002’,’KC ACHMAD YANI‘) Inserting records from another table INSERT INTO stg_l_casa SELECT * FROM stg_casa Insert Statement

  19. Update Statement • Modifies data in existing rows UPDATE par_branch SET dati = ‘3991’ WHERE branch = ‘0006’

  20. Used for queries on single or multiple tables Clauses of the SELECT statement: • SELECT • List thecolumns(and expressions) that should be returned from the query • FROM • Indicate thetable(s) or view(s) from which data will be obtained • WHERE • Indicate theconditionsunder which a row will be included in the result • GROUP BY • Indicate columnsto group the results • HAVING • Indicate the conditionsunder which a group will be included • ORDER BY • Sorts the result according to specified columns Select Statement

  21. SQL Statement Processing Order

  22. Find all loan data within branch UMK SELECT* FROM stg_loan WHEREbranch LIKE ‘UM%’ Select Example

  23. Comparison Operator 23

  24. Alias is an alternative column or table name SELECT CUST.customer_name AS name, CUST.customer_address FROM stg_cifCUST WHERE customer_name LIKE ‘PT. %’ SELECT Example using Alias 24

  25. Using the COUNT aggregate function to find totals Aggregate functions: SUM(), MIN(), MAX(), AVG(), COUNT() SELECT COUNT(*) FROM stg_loan WHERE branch LIKE ‘7%’ SELECT deal_type,MAX(collect) FROM stg_loan GROUP BY deal_type ORDER BY deal_type SELECT Example using Function

  26. AND,OR,andNOTOperators for customizing conditions in WHERE clause SELECT branch,deal_type,outstanding FROM stg_loan WHERE (deal_typeLIKE‘UM%’ORbranch LIKE‘7%’) ANDoutstanding > 500000000 SELECT Example using Boolean Operators Note: the LIKE operator allows you to compare strings using wildcards. For example, the % wildcard in ‘%Desk’ indicates that all strings that have any number of characters preceding the word “Desk” will be allowed

  27. Sort the results first by BRANCH, and within a Branch by Product SELECT customer_name, branch, product FROM CUSTOMER_V WHERE branch IN(‘7008’, ‘7015’, ‘7004’, ‘7076’) ORDER BY branch,product SELECT Example using Order BY Clause Note: the IN operator in this example allows you to include rows whose BRANCH value is either 7008, 7015, 7004 or 7076. It is more efficient than separate OR conditions

  28. SELECT branch, COUNT(*) FROM stg_loan GROUP BYbranch Note: you can use single-value fields with aggregate functions if they are included in the GROUP BY clause SELECT Example using GROUP BY Clause

  29. For use with GROUP BY SELECT branch, COUNT(cif) FROM stg_loan GROUP BY branch HAVINGCOUNT(cif) < 1000; Like a WHERE clause, but it operates on groups (categories), not on individual rows. Here, only those groups with total numbers less than 1000 will be included in final result SELECT Example using HAVING clause 29

  30. UM1 UM1 AVG( ) UM1 UM1 UM2 UM2 AVG( ) UM2 UM2 UM2 UM2 UM2 UD1 AVG( ) UD1 UD1 UD1 GROUP BY Clause DEAL TYPE

  31. A B UNION The unionof A and B (AB) A table containing all the rows from AandB.

  32. A B Intersection Theintersectionof A and B (AB) A table containing only rows that appear in both AandB.

  33. A B Difference Thedifferenceof A and B (A–B) A table containing rows that appear inAbut not in B.

  34. Join Table • SQL provides a convenient operation to retrieve information from multiple tables • This operation is called join • The join operation will combine the tables into one large table with all possible combinations

  35. field1 field2 field2 field1 A 1 1 A A 2 2 B A 3 3 B 1 B 2 B 3 Join Table

  36. Natural /Inner Join • A Natural Join(Inner Join) is a join operation that joins two tables by their common column. This operation is similar to the setting relation of two tables. • SELECT c.cif, c.name, c.gol_deb, l.deal_type • FROM stg_cif c, stg_loan l • WHERE c.cif=l.cif • ORDER BY c.cif, l.deal_type

  37. Same id CIF Name Gol Deb CIF Deal Type 123A89 123A89 Join STG_CIF STG_LOAN CIF Name Deal Type Gol Deb 123A89 Form 11 Natural Join

  38. Inner Join • Match rows between the two tables specified in the INNER JOIN statement based on one or more columns having matching data.  • Preferably the join is based on referential integrity enforcing the relationship between the tables to ensure data integrity. • The most common join needed in applications and/or queries.  • it is possible to also include logic using greater than, less than, not equals, etc.

  39. Outer Join • An Outer Joinis a join operation that includes rows that have a match, plus rows that do not have a match in the other table. • LEFT JOIN • RIGHT JOIN • FULL JOIN

  40. Left Outer Join • Based on the two tables specified in the join clause, all data is returned from the left table.  • On the right table, the matching data is returned in addition to NULL values where a record exists in the left table, but not in the right table. • LEFT and RIGHT OUTER JOIN logic is opposite of one another.  So you can change either the order of the tables in the specific join statement or change the JOIN from left to right or vice versa and get the same results.

  41. Right Outer Join • Based on the two tables specified in the join clause, all data is returned from the right table.  • On the left table, the matching data is returned in addition to NULL values where a record exists in the right table but not in the left table.

  42. Full Outer Join • Based on the two tables specified in the join clause, all data is returned from both tables regardless of matching data.

  43. Delete Statement • Removes rows from a table • Delete certain rows • DELETE FROM STG_LOAN WHERE COLLECT = ‘5’ • Delete all rows • DELETE FROM STG_LOAN

  44. Truncate Statement • Removes all rows from a table • TRUNCATE TABLE STG_LOAN

  45. Variable • User-defined with DECLARE @ Statement • Assigned Values with SET or SELECT Statement • Variables Have Local Scope • DECLARE @produkvarchar(3) • SET @produk = ‘UM1‘ • SELECT distinct branch • FROM stg_m_loan • WHERE deal_type = @produk • ORDER BY branch DECLARE @kolek_terparahvarchar(1), @product varchar(3) set @product='KMP' SELECT @kolek_terparah=max(collect) FROM stg_m_loan where deal_type=@product select @kolek_terparah

  46. Control Flow • Statement Level • BEGIN … END block • IF … ELSE block • WHILE constructs • Row Level • CASE function DECLARE @n int SET @n = 5 IF (@n BETWEEN 4 and 6) BEGIN WHILE (@n > 0) BEGIN SELECT @n AS 'Number‘, CASE WHEN (@n % 2) = 1 THEN ‘GANJIL' ELSE ‘GENAP' END AS 'Type' SET @n = @n - 1 END END ELSE PRINT 'NO ANALYSIS‘ GO

  47. Comment • In line comment [ -- ] • SELECT • cif, • outstanding, principal_amount+int_amount –- konstant payment FROM stg_loan • Block comment [ /* */ ] • /* • ini adalah script • daftar kredit mikro • */ • SELECT • cif, • outstanding, int_rateFROM stg_loan • WHERE branch like ‘7%’

  48. Save the Query as a Script File • Click “File” • Select ” Save SQLQuery1.sql as …” • Type in the file name you want • Click “Save”Save

  49. Import Data • Select Database • Right Click, Select Tasks • Select Import Data • Use Import Export Data Wizard • Specify data source first • Then specify data destination

  50. Export Data • Select Database • Right Click, Select Tasks • Select Export Data • Use Import Export Data Wizard • Specify data source first • Then specify data destination

More Related