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

# Cursors
In the previous lesson on iterative processing we looped through a set of rows. Cursors is a kind of a loop which is used to traverse the rows returned by a query. Just as the cursor on the computer screen shows the current position, a database cursor also shows its current position in the result-set. Cursors can only be used in stored procedures, functions and triggers to loop through the results of a query one row at a time. Cursors have some properties which will be discussed next. 

Cursors are __read-only__ meaning they can only be used to view the result-set and not update it. They are __non-scrollable__ meaning they can only show rows in the result-set in a sequential manner. It is not possible to view rows in a different order than the one returned by the query or to skip some rows to reach a specific row. MySQL cursors are __asensitive__ meaning they point to the actual data in the table. There is another type of cursor which creates a temporary table for the result-set. This type is called insensitive cursors and these are not supported by MySQL. A potential drawback of working with the actual data in the table and not a temporary copy is that any changes made to the data from another connection can affect the results of the asensitive cursor.

To use a cursor, it is first declared using a __DECLARE__ statement which mentions the query with which the cursor is associated. It is necessary to declare the variables that will be used to manipulate the results returned by the query before declaring the cursor. Failure to do so will result in an error. The __OPEN__ statement is used to initialize the cursor by fetching the rows resulting from the execution of the query. Next, to process each row of the result-set, the __FETCH__ statement is used. This statement retrieves the row pointed to by the cursor and moves the pointer to the next row. As we are fetching rows one after the other, the cursor points to the next row in the result set. After fetching the last row, a condition is raised when the cursor cannot find the next row. To handle this situation we must define the __NOT FOUND__ handler which sets a variable LastRowFetched to 1. This variable is checked in every iteration as the terminating condition of the loop. Lastly, the __CLOSE__ statement is used to deactivate the cursor and release memory associated with it. 

The following diagram illustrates the working of MySQL cursors:



## Syntax
> DECLARE **CursorName** CURSOR FOR 
>
> **SELECTStatement;**
>
> DECLARE CONTINUE HANDLER FOR NOT FOUND 
>
> SET **LastRowFetched** = 1;
>
> OPEN **cursor_name**;
>
> FETCH **cursor_name** INTO **variables list**;
>
> CLOSE **cursor_name**;

>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/56lesson.sh** and wait for the MySQL prompt to start-up.


This lesson explores the concept of cursors which offer an efficient mechanism to process query results containing multiple rows.

Introduction

Basic SQL

Joins

Nested Queries

Multi Table Operations

Views

Stored Procedures

Triggers

Miscellaneous

Practice & Prep

Epilogue

Cursors

Cursors