Why Bash Scripting? (Bash) Bash stands for ‘Bourne Again Shell’ Developed in the 80’s but a very popular shell today. Default in many Unix systems, Macs AWS, Google, ...
Deleting and Altering Triggers
Deleting Table and View Triggers DROP TRIGGER PreventNewDiscounts; Deleting Database Triggers DROP TRIGGER PreventNewDiscounts; DROP TRIGGER Preven...
Known Limitations of Triggers
Advantages of Triggers Used for database integrity Enforece business rules directly in the database Control on which statements are allowed in a database Implem...
AFTER triggers
Definition and properties Performs a set of actions when fired The actions are performed only after the DML event is finisehd Used with INSERT, UPDATE, and DELETE sta...
Introduction to Triggers
What is a trigger? Special type of stored procedure Executed when an event occurs in the database server Types of Trigger(based on T-SQL commands) ...
Case Study EDA and Imputation
Data Imputation Divide by zero error when calculating Avg Fare/TripDistance EDA uncovers hundreds of TaxiRide trip records with Trip Distance = 0 Data Imputation meth...
Stored Procedures
What is a stored procedure? Routines that Accept input parameters Perform actinos (EXECUTE, SELECT, INSERT, UPDATE, DELETE, and other SP statments) Return status ...
User Defined Functions
What are User Defined Functions (UDFs)? User Defined Functions (UDFs) are routines that can accept input parameters perform an action return result (single scalar...
EDA
Temporal EDA Exploratory Data Analysis (EDA) Process Iterative No specific checklist for EDA questions SQL function...
Transaction Isolation Levels
What is concurrency? Concurrency: two or more transactions that read/change shared data at the same time. Isolate our transaction from other transactions Transaction isolation...
Transactions
Transactions What is a transaction? Transaction: one or more statements, all or none of the statements are executed. Example: Transfer $100 ...
Error Handling 2
Raise Error Raise Errors Statements RAISERROR THROW Microsoft suggests THROW RAISEERROR ( {msg_str | msg_id | @local_variable_message}, seve...
Error Handling 1
Datasets Products Table Buyers Table Staff Table Orders Table Getting an error CONSTRAINT unique_product_name UNIQUE(product_name); INSERT INTO products(product_name, sto...
Database Roles and Access Control
Granting and revoking access to a view GRANT privilege(s) or REVOKE privilege(s) ON object TO role or FROM role Privileges: SELECT, INSERT, UPDATE, DELETE, etc. Obe...
Database Views
In a database, a view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object. Virtual table that is n...
Database Schemas
Star and Snowflake Schema Star Schema Dimensional modeling: star schema Fact tables Holds records of a metric Changes regularly Connects to dimensions via forei...
OLTP and OLAP
How should we organize and manage data? Schemas: How should my data be logically organized? Normalization: Should my data have minimal dependency and redundancy? View...
Aggregate Arithmetic Functions
COUNT() Returns the number of items found in a group. COUNT([ALL] expression) COUNT(DISTINCT expression) COUNT(*) Example: SELECT ...
Functions for Positions
Position Functions LEN() CHARINDEX() PATINDEX() LEN() Returns the number of characters of the provided string. Syntax: LEN...
Functions that return system date and time
Common mistakes when working with dates and time Inconsistent date time formats or patterns Arithmetic Operations Issues with time zones Time Zones in...
Data Types and Conversion
Categories of Data Types Exact numerics Approximate numerics Date and Time Character Strings Unicode Character Strings Binary Strings Other da...
Using Aggregation Functions Over Windows
Using Aggregation Functions Over Windows Ranking Functions ROW_NUMBER(): Unique, ascending integer value starting from 1. RANK(): Ascending integer value starting from 1. ...
Basic Aggregate Functions
Key Aggregation Functions Counts COUNT() COUNT_BIG() COUNT(DISTINCT) Other Aggregates SUM() ...
Basic Date From Parts
Dates from parts DATEFROMPARTS(year,month,day) TIMEFROMPARTS(hour, minute, second, fraction, precision) DATETIMEFROMPARTS(year, month, day, hour, minute, seconds,ms) ...
Building Dates
Building Dates Building a Date SELECT GETDATE() AS DateTime_LTz, GETUTCDATE() AS DateTime_UTC; SELECT SYSDATETIME() AS DateTime2_LTz,...
Window Functions
Grouping data in T-SQL SELECT SalesPerson, SalesYear, CurrentQuota, ModifiedDate FROM SaleGoal WHERE SalesYear = 2011 ...
Loops
Using Variables in T-SQL Variables are needed to set values DECLARE @variablename data_type Must start with the character @ Vari...
Counts and Totals
Examining Totals with Counts SELECT COUNT(*) FROM Incidents The above sql statments returns the total number of rows in the Incidents table. Count w...
Intermediate To SQL Server
Exploring Data With Aggregation Reviewing summarized values for each column is a common first step in analyzing data If the data exists in a database, fastest way to aggreg...
Model 1:N relationships with foreign keys
Implementing relationships with Foreign Keys A Foreign Key (FK) points to the Primary key (PK) of another table. Domain of FK must be equal to the domain of PK Each v...
Keys and Superkeys
What is a Key? Attribute(s) that idenfity a record uniquely As long as attributes can be removed: superkey If no more attributes can be removed: minimal superky or ke...
Constraints
Integrity Constraints Attribute Constraints, e.g data types on columns Key Constraints, e.g primary keys Referential Integrity Constraints, enforced through foreign k...
Relational Databases
Using information schema SELECT table_schema, table_name FROM information_schema.tables; Taking the look at the columns of a ...
CRUD Operations
CRUD OPERATION CREATE Databases, Tables or Views Users, prermissions and security groups READ Example: SELEC...
Joining Tables
PRIMARY KEYS: A primary key is used to ensure data in the specific column is unique. It is a column cannot have NULL values. It is either an existing table column or a column that is speci...
Aggregating Data
SUM It is used to calculate the total amount of a column. Example: SELECT SUM(affected_customers) AS total_affec...
Introduction to SQL Server
A SQL Server is a relational database system developed by Microsoft. And a Transact-SQL (T-SQL) is a Microsoft’s implemenataion of SQL butwith added functionality. Queries SELECT ...
Convolutional Neural Network (CNN / ConvNet)
Convolutional Neural Networks (CNN/ ConvNet) A Convolutional Neural Network, also known as CNN or ConvNet, is a class of neural networks that specailizes in processing data that has a grid-like to...
Back Propagation
What is Backpropagation? Backpropagation is a supervised learning algorithm, for training Multi-layer Perceptrons (Artificial Neural Networks).It refers to the method of calculating the gradient o...
Artificial Neural Network (ANN / MLP)
To understand the concept of the architecture of an artificial neural network, we have to understand what a neural network consists of. In order to define a neural network that consists of a large ...