/*************************************/ /* PODCAST 05 - TSQL DML */ /* Created By: Angelo R Bobak */ /* Created: 04/16/2020 */ /*************************************/ /************/ /* PRE-WORK */ /************/ /*********************/ /* CREATE A CALENDAR */ /*********************/ USE [ORGANIZATION] GO DROP TABLE IF EXISTS MASTER_DATA.CALENDAR GO CREATE TABLE MASTER_DATA.CALENDAR ( CALENDAR_DATE DATE NOT NULL, CALENDAR_YEAR SMALLINT NOT NULL, CALENDAR_QUARTER SMALLINT NOT NULL, CALENDAR_MONTH SMALLINT NOT NULL, CALENDAR_WEEK SMALLINT NOT NULL ) GO DECLARE @StartDate DATE; DECLARE @StopDate DATE; SET @StartDate = '1-1-2010'; SET @StopDate = '12-31-2020'; WHILE @StartDate <= @StopDate BEGIN PRINT CONVERT(VARCHAR,@StartDate); INSERT INTO MASTER_DATA.CALENDAR VALUES( @StartDate, DATEPART(yy,@StartDate), DATEPART(qq,@StartDate), DATEPART(mm,@StartDate), DATEPART(ww,@StartDate) ); SET @StartDate = DATEADD(dd,1,@StartDate); END GO SELECT * FROM MASTER_DATA.CALENDAR GO /***************************/ /* TOPIC 01 - TSQL QUERIES */ /***************************/ USE [TEST] GO SELECT 1,2,3,4,5 GO SELECT 1 AS One, 2 AS Two, 3 AS Three, 4 AS Four, 5 AS Five GO SELECT CONVERT(DECIMAL(5,2),(1 + 2) * 3.0/2) AS DECIMAL_RESULT GO -- WATCH YOUR PARENTHESIS !! !! -- These control order of execution SELECT (1 + 2) * (3.0/2) AS [First Result], (1 + 2 * 3.0)/2 AS [Second Result] GO /***************************/ /* A bit more interesting! */ /***************************/ USE [ORGANIZATION] GO SELECT [DEPT_ID], [DEPT_NAME] FROM [MASTER_DATA].[DEPARTMENT] GO SELECT * FROM [MASTER_DATA].[DEPARTMENT] GO SELECT [DEPT_ID], [EMP_ID], [EMP_FNAME], [EMP_LNAME] FROM [MASTER_DATA].[EMPLOYEE] GO /* SELECT DEPT_ID, EMP_ID, EMP_FNAME, EMP_LNAME FROM [MASTER_DATA].[EMPLOYEE] GO */ SELECT [DEPT_ID] AS [Department Id], [EMP_ID] AS [Employee Id], [EMP_FNAME] AS [Employee First Name], [EMP_LNAME] AS [Employee Last Name], [EMP_FNAME] + ' ' + [EMP_LNAME] AS [Employee Full Name] FROM [MASTER_DATA].[EMPLOYEE] GO /********************/ /* Create test data */ /********************/ SELECT INV_ID AS [Invoice Number], CUS_ID AS [Customer Identifier], PROD_ID AS [Product Identifier], INV_AMT AS [Invoice Amount], INV_DATE AS [Invoice Date] FROM ( SELECT 'INV_01' AS INV_ID, 'CUS_03' AS CUS_ID, 'PROD_03' AS PROD_ID, CONVERT(DECIMAL(10,2),RAND(2) * 100.00) AS INV_AMT, CONVERT(DATE,(SELECT DATEADD(dd,-30,GETDATE()))) AS INV_DATE UNION ALL SELECT 'INV_02' AS INV_ID, 'CUS_03' AS CUS_ID, 'PROD_04' AS PROD_ID, CONVERT(DECIMAL(10,2),RAND(3) * 50.00) AS INV_AMT, CONVERT(DATE,(SELECT DATEADD(dd,-20,GETDATE()))) AS INV_DATE UNION ALL SELECT 'INV_03' AS INV_ID, 'CUS_03' AS CUS_ID, 'PROD_05' AS PROD_ID, CONVERT(DECIMAL(10,2),RAND(2) * 89.00) AS INV_AMT, CONVERT(DATE,(SELECT DATEADD(dd,-50,GETDATE()))) AS INV_DATE UNION ALL SELECT 'INV_04' AS INV_ID, 'CUS_03' AS CUS_ID, 'PROD_06' AS PROD_ID, CONVERT(DECIMAL(10,2),RAND(5) * 75) AS INV_AMT, CONVERT(DATE,(SELECT DATEADD(dd,-126,GETDATE()))) AS INV_DATE ) INVOICES GO /*******************************/ /* END TOPIC 01 - TSQL QUERIES */ /*******************************/ /********************/ /* TOPIC 02 - JOINS */ /********************/ USE [ORGANIZATION] GO /**************/ /* INNER JOIN */ /**************/ SELECT D.[DEPT_ID], D.[DEPT_NAME], E.[EMP_ID], E.[EMP_FNAME], E.[EMP_LNAME] FROM MASTER_DATA.DEPARTMENT D JOIN MASTER_DATA.EMPLOYEE E ON D.DEPT_ID = E.DEPT_ID GO /******************/ /* WORKS THE SAME */ /******************/ SELECT D.[DEPT_ID], D.[DEPT_NAME], E.[EMP_ID], E.[EMP_FNAME], E.[EMP_LNAME] FROM MASTER_DATA.DEPARTMENT D INNER JOIN MASTER_DATA.EMPLOYEE E ON D.DEPT_ID = E.DEPT_ID GO SELECT D.*,E.* FROM MASTER_DATA.DEPARTMENT D INNER JOIN MASTER_DATA.EMPLOYEE E ON D.DEPT_ID = E.DEPT_ID GO SELECT D.DEPT_ID,D.DEPT_NAME,E.EMP_ID,E.EMP_FNAME,E.EMP_LNAME FROM MASTER_DATA.DEPARTMENT D INNER JOIN MASTER_DATA.EMPLOYEE E ON D.DEPT_ID = E.DEPT_ID GO /*******************/ /* LEFT OUTER JOIN */ /*******************/ SELECT D.DEPT_ID,D.DEPT_NAME,E.EMP_ID,E.EMP_FNAME,E.EMP_LNAME FROM MASTER_DATA.DEPARTMENT D -- left table LEFT OUTER JOIN MASTER_DATA.EMPLOYEE E -- right table ON D.DEPT_ID = E.DEPT_ID ORDER BY D.DEPT_ID DESC GO /********************/ /* RIGHT OUTER JOIN */ /********************/ SELECT D.DEPT_ID, D.DEPT_NAME, E.EMP_ID, E.EMP_FNAME, E.EMP_LNAME FROM MASTER_DATA.DEPARTMENT D RIGHT OUTER JOIN MASTER_DATA.EMPLOYEE E ON D.DEPT_ID = E.DEPT_ID ORDER BY E.EMP_ID DESC GO /**************/ /* CROSS JOIN */ /**************/ /***************************************/ /* Let's create a new table on the fly */ /***************************************/ DROP TABLE IF EXISTS MASTER_DATA.EMPLOYEE_COURSE GO SELECT * FROM [MASTER_DATA].[COURSE] GO /* SELECT * INTO [MASTER_DATA].[COURSE_BU] FROM [MASTER_DATA].[COURSE] GO INSERT INTO [MASTER_DATA].[COURSE_BU] SELECT * FROM [MASTER_DATA].[COURSE] GO */ TRUNCATE TABLE [MASTER_DATA].[COURSE] GO INSERT INTO [MASTER_DATA].[COURSE] SELECT [COURSE_ID],[COURSE_NAME] FROM [MASTER_DATA].[COURSE_BU] GO DROP TABLE IF EXISTS [MASTER_DATA].[EMPLOYEE_COURSE] GO SELECT E.[EMP_ID], C.[COURSE_ID] INTO MASTER_DATA.EMPLOYEE_COURSE FROM [MASTER_DATA].[EMPLOYEE] E CROSS JOIN [MASTER_DATA].[COURSE] C GO SELECT * FROM [MASTER_DATA].[EMPLOYEE_COURSE] ORDER BY EMP_ID,[COURSE_ID] GO /************************/ /* END TOPIC 02 - JOINS */ /************************/ /**************************/ /* TOPIC 03 - INSERT ROWS */ /**************************/ USE [TEST] GO DROP TABLE IF EXISTS TEST_TABLE GO CREATE TABLE TEST_TABLE ( TEXT1 VARCHAR(256) NOT NULL, TEXT2 VARCHAR(256) NOT NULL, TEXT3 VARCHAR(256) NULL, TEXT4 VARCHAR(256) NOT NULL DEFAULT 'Not defined', DECIMAL_VALUE DECIMAL(10,2) NOT NULL, INTEGER_VALUE INTEGER NULL ) GO INSERT INTO TEST_TABLE VALUES('String 1','String 2','String 3',DEFAULT,10.2,5) GO SELECT * FROM TEST_TABLE GO INSERT INTO TEST_TABLE ( TEXT1, TEXT2, DECIMAL_VALUE ) VALUES('String 1','String 2',10.2) GO SELECT * FROM TEST_TABLE GO DROP TABLE TEST_TABLE GO USE [ORGANIZATION] GO DROP SCHEMA IF EXISTS [SALES] GO CREATE SCHEMA [SALES] GO DROP TABLE IF EXISTS [SALES].[CATALOG] GO CREATE TABLE [SALES].[CATALOG]( [PROD_ID] [varchar](6) NOT NULL, [PRODUCT_NAME] [varchar](32) NOT NULL, [RETAIL_PRICE] [decimal](10, 2) NULL ) ON [PRIMARY] GO TRUNCATE TABLE [SALES].[CATALOG] GO INSERT INTO [SALES].[CATALOG] VALUES ('P001','PRINTER PAPER',5.00), ('P002','BALL POINT PENS',15.00), ('P003','BOX NOTE PADS',25.00), ('P004','TAPE',7.00), ('P005','PENCILS',4.00); GO SELECT * FROM [SALES].[CATALOG] GO DROP TABLE IF EXISTS [SALES].[PURCHASES] GO CREATE TABLE [SALES].[PURCHASES]( [PURCHASE_KEY] INTEGER IDENTITY NOT NULL, [DEPT_ID] [varchar](6) NOT NULL, [INV_ID] [varchar](32) NOT NULL, [PROD_ID] [varchar](7) NOT NULL, [PURCHASE_DATE] [date] NOT NULL, [QUANTITY] SMALLINT NOT NULL, [PRICE] [decimal](10, 2) NOT NULL, [TOTAL_PRICE] [decimal](10, 2) NOT NULL ) ON [PRIMARY] GO TRUNCATE TABLE [SALES].[PURCHASES] GO INSERT INTO [SALES].[PURCHASES] SELECT D.[DEPT_ID], 'INV-' + CONVERT(VARCHAR,CAL.[CALENDAR_DATE]), CAT.[PROD_ID], CAL.[CALENDAR_DATE] AS [PURCHASE_DATE] , CONVERT(SMALLINT,RIGHT([DEPT_ID],1)) AS QUANTITY, CAT.[RETAIL_PRICE] * CONVERT(SMALLINT,RIGHT([DEPT_ID],1)) AS [PRICE], CONVERT(SMALLINT,RIGHT([DEPT_ID],1)) * CAT.[RETAIL_PRICE] * CONVERT(SMALLINT,RIGHT([DEPT_ID],1)) AS [TOTAL_PRICE] FROM [MASTER_DATA].[DEPARTMENT] D CROSS JOIN [MASTER_DATA].[CALENDAR] CAL CROSS JOIN [SALES].[CATALOG] CAT WHERE DAY([CALENDAR_DATE]) IN (28,30,31) GO SELECT * FROM [SALES].[PURCHASES] ORDER BY [INV_ID] GO SELECT [INV_ID] AS DUP_INVOICES,COUNT(*) AS NUM_DUPS FROM [SALES].[PURCHASES] GROUP BY [INV_ID] HAVING COUNT(*) > 1 GO UPDATE [SALES].[PURCHASES] SET [INV_ID] = [INV_ID] + '-' + CONVERT(VARCHAR,[PURCHASE_KEY]) GO SELECT [INV_ID] AS DUP_INVOICES,COUNT(*) AS NUM_DUPS FROM [SALES].[PURCHASES] GROUP BY [INV_ID] HAVING COUNT(*) > 1 GO SELECT * FROM [SALES].[PURCHASES] ORDER BY [PURCHASE_DATE] GO TRUNCATE TABLE MASTER_DATA.EMPLOYEE GO INSERT INTO MASTER_DATA.EMPLOYEE VALUES ('D001','E001','JOHN','SMITH'), ('D001','E002','MARY','SMITH'), ('D001','E003','SUSAN','GREEN'), ('D001','E004','JOHN','MARONE'), ('D002','E005','TOM','DARBY'), ('D002','E006','MARY','YORK'), ('D002','E007','DEBBIE','MCDONALD'), ('D002','E008','GIOVANNI','BERTONE'), ('D003','E009','LARRY','JONES'), ('D003','E010','MIKE','MCGEE'), ('D003','E011','BORIS','GOLD'), ('D003','E012','BERTHA','SMITH'), ('D004','E013','DONALD','DILBY'), ('D004','E014','JAKE','DORITO'), ('D004','E015','THELMA','PINSKY'), ('D091','E016','JOHN','BARRY'), ('D009','E017','BARRY','SMITH'), ('D099','E018','MARY','GREEN'); GO /******************************/ /* END TOPIC 03 - INSERT ROWS */ /******************************/ /**************************/ /* TOPIC 04 - UPDATE ROWS */ /**************************/ USE [ORGANIZATION] GO /**********/ /* UPDATE */ /**********/ SELECT * FROM [MASTER_DATA].[COURSE] GO TRUNCATE TABLE MASTER_DATA.COURSE GO INSERT INTO MASTER_DATA.COURSE VALUES ('C001','MARKETING BASICS'), ('C002','ACCOUNTING 1'), ('C003','ACCOUNTING 2'), ('C004','BOKKING FOR BEGGINERS'); -- error on purpose GO SELECT * FROM [MASTER_DATA].[COURSE] GO /**************************/ /* INSERT SOME DIRTY DATA */ /**************************/ INSERT INTO [MASTER_DATA].[COURSE] VALUES ('C0119','TSQLPROGRAMMING 3'); -- data quality issue GO /***********************/ /* Backup just in case */ /***********************/ SELECT * FROM [MASTER_DATA].[COURSE] GO DROP TABLE IF EXISTS [MASTER_DATA].[COURSE_BU] GO INSERT INTO [MASTER_DATA].[COURSE_BU] SELECT *,GETDATE() AS BACKUP_DATE --INTO [MASTER_DATA].[COURSE_BU] FROM [MASTER_DATA].[COURSE] GO SELECT * FROM [MASTER_DATA].[COURSE_BU] GO UPDATE [MASTER_DATA].[COURSE] SET COURSE_NAME = 'TSQL PROGRAMMING 3' WHERE COURSE_ID = 'C0119' GO SELECT * FROM [MASTER_DATA].[COURSE] GO UPDATE [MASTER_DATA].[COURSE] SET COURSE_NAME = 'BOOKKEEPING FOR BEGGINERS', COURSE_ID = 'C004.1' WHERE COURSE_NAME LIKE '%BOKKING%' GO SELECT * FROM [MASTER_DATA].[COURSE] GO /******************************/ /* END TOPIC 04 - UPDATE ROWS */ /******************************/ /**************************/ /* TOPIC 05 - DELETE ROWS */ /**************************/ USE [ORGANIZATION] GO SELECT * FROM [MASTER_DATA].[COURSE] GO DELETE FROM [MASTER_DATA].[COURSE] WHERE [COURSE_ID] = 'C004' GO DELETE FROM [MASTER_DATA].[COURSE] WHERE [COURSE_ID] LIKE 'C004%' GO DELETE FROM [MASTER_DATA].[COURSE] WHERE [COURSE_ID] = 'C004.1' GO SELECT * FROM [MASTER_DATA].[COURSE] GO /************/ /* OPPS ... */ /************/ DELETE FROM [MASTER_DATA].[COURSE] WHERE [COURSE_NAME] LIKE 'ACCOUNTING%' GO SELECT * FROM [MASTER_DATA].[COURSE] GO /*******************/ /* DOUBLE OPPS ... */ /*******************/ DELETE FROM [MASTER_DATA].[COURSE] GO TRUNCATE TABLE [MASTER_DATA].[COURSE] GO SELECT * FROM [MASTER_DATA].[COURSE] GO /********************************/ /* Fortunately we have a backup */ /********************************/ SELECT * FROM [MASTER_DATA].[COURSE_BU] GO INSERT INTO [MASTER_DATA].[COURSE] SELECT [COURSE_ID], [COURSE_NAME] FROM [MASTER_DATA].[COURSE_BU] WHERE [BACKUP_DATE] = ( SELECT MIN([BACKUP_DATE]) FROM [MASTER_DATA].[COURSE_BU] ) GO INSERT INTO [MASTER_DATA].[COURSE] SELECT [COURSE_ID], [COURSE_NAME] FROM [MASTER_DATA].[COURSE_BU] WHERE [BACKUP_DATE] = ( SELECT MAX([BACKUP_DATE]) FROM [MASTER_DATA].[COURSE_BU] ) GO SELECT * FROM [MASTER_DATA].[COURSE] GO /***************/ /* Apply fixes */ /***************/ UPDATE [MASTER_DATA].[COURSE] SET COURSE_ID = 'C005', COURSE_NAME = 'TSQL PROGRAMMING 3' WHERE COURSE_ID = 'C0119' GO UPDATE [MASTER_DATA].[COURSE] SET COURSE_NAME = 'BOOKKEEPING FOR BEGGINERS' WHERE COURSE_NAME LIKE '%BOKKING%' GO SELECT * FROM [MASTER_DATA].[COURSE] GO /******************************/ /* END TOPIC 05 - DELETE ROWS */ /******************************/ /*****************************/ /* TOPIC 06 - TRUNCATE TABLE */ /*****************************/ USE [TEST] GO DROP TABLE IF EXISTS TEST_TABLE GO CREATE TABLE TEST_TABLE ( TEXT1 VARCHAR(256) NOT NULL, TEXT2 VARCHAR(256) NOT NULL, TEXT3 VARCHAR(256) NULL, TEXT4 VARCHAR(256) NOT NULL DEFAULT 'Not defined', DECIMAL_VALUE DECIMAL(10,2) NOT NULL, INTEGER_VALUE INTEGER NULL ) GO INSERT INTO TEST_TABLE VALUES('String 1','String 2','String 3','This is string 4',10.2,5) GO INSERT INTO TEST_TABLE VALUES('String 1.1','String 2.1','String 3.1',DEFAULT,10.2,5) GO INSERT INTO TEST_TABLE ( TEXT1, TEXT2, DECIMAL_VALUE ) VALUES('String 1','String 2',10.2) GO SELECT * FROM TEST_TABLE GO BEGIN TRANSACTION DELETE FROM TEST_TABLE WHERE INTEGER_VALUE IS NULL; SELECT * FROM TEST_TABLE; --COMMIT TRANSACTION ROLLBACK TRANSACTION SELECT * FROM TEST_TABLE; TRUNCATE TABLE TEST_TABLE GO SELECT * FROM TEST_TABLE; GO DROP TABLE TEST_TABLE GO USE [ORGANIZATION] GO DROP TABLE IF EXISTS [MASTER_DATA].[COURSE_BU] GO SELECT *,GETDATE() AS BACKUP_DATE INTO [MASTER_DATA].[COURSE_BU] FROM [MASTER_DATA].[COURSE] GO TRUNCATE TABLE [MASTER_DATA].[COURSE] GO SELECT * FROM [MASTER_DATA].[COURSE] GO INSERT INTO [MASTER_DATA].[COURSE] SELECT [COURSE_ID], [COURSE_NAME] FROM [MASTER_DATA].[COURSE_BU] WHERE [BACKUP_DATE] = ( SELECT MAX([BACKUP_DATE]) FROM [MASTER_DATA].[COURSE_BU] ) GO SELECT * FROM [MASTER_DATA].[COURSE] GO /*********************************/ /* END TOPIC 06 - TRUNCATE TABLE */ /*********************************/