Home
Babin Joshi
Cancel

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 ...