Gain insights into SQL by exploring database creation, data manipulation, and advanced queries. Learn about practical applications and practice for SQL interviews to build confidence and readiness.

sql.tar.gz

The ability to work SQL is becoming an increasingly in-demand skill, both for software developers and people in less technical roles. If you’re interested in learning SQL and have no prior experience with it, then this course will be your light in a dark tunnel.

You’ll start by covering the basics of SQL such as how to create a database, how to insert, query, and update data. You’ll also learn fundamental concepts that developers and data scientists use everyday such as multi-table operations, nested queries, and how to set up views. 

Throughout, you’ll get to execute SQL queries in your browser and see results in real-time - you won’t need to worry about set-up.

At the end of this course, you’ll also get some hands-on practice with common SQL interview questions, so when the time comes, you’ll be ready and confident to answer any question that comes your way. Let’s get started!

An Introductory Guide to SQL

# Transaction

A common online definition of a database transaction reads "A transaction symbolizes a unit of work performed within a database management system against a database, and treated in a coherent and reliable way independent of other transactions." In simpler terms, think about taking a two-legged flight from Mumbai to New York. The first airline takes you from Mumbai to Dubai and the second takes you from Dubai to New York. The two flight legs represent portions of a transaction. You must travel both legs to reach your destination, but if immigration forbids you from taking the second leg of the flight you'd want to return back to Mumbai and not remain stranded at Dubai. A database transaction is similar. You either want all the actions within the transaction to complete or none at all. You don't want a transaction to complete halfway through and then abort. 

# Need for Transactions
One may wonder why we need transactions. Well, whenever multiple users are interacting with a database it is possible for the actions of one user to interleave with another user and bring the data in an inconsistent state. The classic example is transferring funds from one bank account to another. Say the bank application wants to add 100 dollars to your bank account with a balance of 500 dollars. The application reads your balance, adds 100 dollars to it and then updates the new amount 600 dollars in the database against your username. It could happen that between the time the application reads and updates the new amount, you make an ATM withdrawal of 200 dollars. The application has already read your balance and doesn't know that 200 dollars has been withdrawn and mistakenly writes your balance as 600 dollars to the database.

The application layer could avoid this mistake if it performed the two tasks of reading and then updating your balance atomically, or in other words as a transaction. Transactions allow you to batch together SQL statements as an indivisible set that either succeeds or has no effect on the database.

# Syntax to Start & Commit a Transaction
> START TRANSACTION;
>
> ** -- SQL statements
>
> COMMIT;


# Syntax to Start & Rollback a Transaction
> START TRANSACTION;
>
> ** -- SQL statements
>
> ROLLBACK;

>Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command **./DataJek/Lessons/39lesson.sh** and wait for the MySQL prompt to start-up.


This lesson defines and explains the concept of a transaction in the context of relational databases.

Introduction

Basic SQL

Joins

Nested Queries

Multi Table Operations

Views

Stored Procedures

Triggers

Miscellaneous

Practice & Prep

Epilogue

Transaction

Transaction