Complete
SQL using MySQL

Prateek Narang

Module

  • Database

  • DBMS

  • Types of Databases

  • RDBMS

  • Installation & Setup

A database is a collection of data stored in a format that can be easily accessed, managed and updated.

Database

Most applications require a database to store and retrieve data.

A shopping website like Amazon will need a database to store data about customers, products, orders, suppliers etc.

Knowing how to monitor and optimize your database is essential for application performance.

Database

Database management systems are software systems used to manage and manipulate data in a database.
 

Database Management System

DBMS

Database

A DBMS serves as an interface between an end-user and a database, allowing users to create, read, update, and delete data in the database.

Database Management System

DBMS

Database

Text

Types of Databases

DBMS

Database

Relational Database

NoSQL Database

Text

Database Systems

DBMS

Database

RDBMS

NoSQL

Relational Database

  • Its way of representing data in tables.
  • Each row in the table is is a record with unique id called key.
  • The columns of the table hold attributes of data.
  • The relational data model provides a standard way of representing and querying data that could be used by any application
  • Structured query language (SQL) is used to write and query data in a database

 

A relational database example
ProductID ProductName CategoryId Price
1 Jean 1 999
2 Phillips Led 2 220
3 Trimmer 2 1500
4 T-Shirt 1 499
ProductID ProductName CategoryId Price
1 Jean 1 999
2 Phillips Led 2 220
3 Trimmer 2 1500
4 T-Shirt 1 499
CategoryId CategoryName Description
1 Clothing Clothes Mens & Girls
2 Electronics Electronic items ... 
A relational database example

A relational database, or relational database management system (RDMS), stores information in tables. Often, these tables have shared information between them, causing a relationship to form between tables.

 

We use SQL to query these databases.

Relational (SQL) Database

Database

Example - One to Many Relationship
Database Schema
  1. SQL databases are relational, NoSQL databases are non-relational.
  2. SQL databases use structured query language and have a predefined schema. NoSQL databases have dynamic schemas for unstructured data, have their own query language.
  3. SQL databases are vertically scalable, while NoSQL databases are horizontally scalable.
  4. SQL databases are table-based, while NoSQL databases are document, key-value, graph, or wide-column stores. Non-relational databases use a storage model that is optimised for the specific requirements of the type of data being stored.

SQL vs NoSQL Databases

Relational databases are used in several applications

 

  • track inventories, 
  • e-commerce transactions,
  • sales,
  • banking,
  • finance,
  • billing etc.

 

Use of RDBMS

1. MySQL

2. Oracle 

3. Microsoft SQL Server

4. PostgreSQL

SQL Database Systems

  • Free and open-source 
  • Huge community support
  • Extensively tested
  • High stability
  • Available for all major platforms
  • Replication and sharding are available
  • Covers a wide range of use cases 

MySQL

Go to mysql.com Downloads Page.

Download and Install the following using installation wizard.

 

 

  • MySQL Community Server
  • MySQL Workbench

Installation and Setup

Testing our Setup & Creating our first Database for use

Module 1

SELECT STATEMENT

#include <bits/stdc++.h>
using namespace std;
 
void swap(int *xp, int *yp) 
{ 
    int temp = *xp; 
    *xp = *yp; 
    *yp = temp; 
} 
 
// A function to implement bubble sort 
void bubbleSort(int arr[], int n) 
{ 
    int i, j; 
    for (i = 0; i < n-1; i++)     
    
    // Last i elements are already in place 
    for (j = 0; j < n-i-1; j++) 
        if (arr[j] > arr[j+1]) 
            swap(&arr[j], &arr[j+1]); 
} 
 
/* Function to print an array */
void printArray(int arr[], int size) 
{ 
    int i; 
    for (i = 0; i < size; i++) 
        cout << arr[i] << " "; 
    cout << endl; 
} 
 
// Driver code 
int main() 
{ 
    int arr[] = {64, 34, 25, 12, 22, 11, 90}; 
    int n = sizeof(arr)/sizeof(arr[0]); 
    bubbleSort(arr, n); 
    cout<<"Sorted array: \n"; 
    printArray(arr, n); 
    return 0; 
} 



Code Web Dev [CodePen]

// This slide uses Auto-Animate to animate between
// two different code blocks
const distanceBetween = ( p1, p2 ) => {
 // TODO
}
 
distanceBetween([10,10], [50,50])
Code Transitions



Bubble Sort Algorithm # include <bits/stdc++.h> using namespace std ; void swap ( int *xp, int *yp) { int temp = *xp; *xp = *yp; *yp = temp; } // A function to implement bubble sort void bubbleSort ( int arr[], int n) { int i, j; for (i = 0 ; i < n -1 ; i++) // Last i elements are already in place for (j = 0 ; j < n-i -1 ; j++) if (arr[j] > arr[j+ 1 ]) swap(&arr[j], &arr[j+ 1 ]); } /* Function to print an array */ void printArray ( int arr[], int size) { int i; for (i = 0 ; i < size; i++) cout << arr[i] << " " ; cout << endl ; } // Driver code int main () { int arr[] = { 64 , 34 , 25 , 12 , 22 , 11 , 90 }; int n = sizeof (arr)/ sizeof (arr[ 0 ]); bubbleSort(arr, n); cout << "Sorted array: \n" ; printArray(arr, n); return 0 ; } # include <bits/stdc++.h> using namespace std ; void swap ( int *xp, int *yp) { int temp = *xp; *xp = *yp; *yp = temp; } // A function to implement bubble sort void bubbleSort ( int arr[], int n) { int i, j; for (i = 0 ; i < n -1 ; i++) // Last i elements are already in place for (j = 0 ; j < n-i -1 ; j++) if (arr[j] > arr[j+ 1 ]) swap(&arr[j], &arr[j+ 1 ]); } /* Function to print an array */ void printArray ( int arr[], int size) { int i; for (i = 0 ; i < size; i++) cout << arr[i] << " " ; cout << endl ; } // Driver code int main () { int arr[] = { 64 , 34 , 25 , 12 , 22 , 11 , 90 }; int n = sizeof (arr)/ sizeof (arr[ 0 ]); bubbleSort(arr, n); cout << "Sorted array: \n" ; printArray(arr, n); return 0 ; } # include <bits/stdc++.h> using namespace std ; void swap ( int *xp, int *yp) { int temp = *xp; *xp = *yp; *yp = temp; } // A function to implement bubble sort void bubbleSort ( int arr[], int n) { int i, j; for (i = 0 ; i < n -1 ; i++) // Last i elements are already in place for (j = 0 ; j < n-i -1 ; j++) if (arr[j] > arr[j+ 1 ]) swap(&arr[j], &arr[j+ 1 ]); } /* Function to print an array */ void printArray ( int arr[], int size) { int i; for (i = 0 ; i < size; i++) cout << arr[i] << " " ; cout << endl ; } // Driver code int main () { int arr[] = { 64 , 34 , 25 , 12 , 22 , 11 , 90 }; int n = sizeof (arr)/ sizeof (arr[ 0 ]); bubbleSort(arr, n); cout << "Sorted array: \n" ; printArray(arr, n); return 0 ; } # include <bits/stdc++.h> using namespace std ; void swap ( int *xp, int *yp) { int temp = *xp; *xp = *yp; *yp = temp; } // A function to implement bubble sort void bubbleSort ( int arr[], int n) { int i, j; for (i = 0 ; i < n -1 ; i++) // Last i elements are already in place for (j = 0 ; j < n-i -1 ; j++) if (arr[j] > arr[j+ 1 ]) swap(&arr[j], &arr[j+ 1 ]); } /* Function to print an array */ void printArray ( int arr[], int size) { int i; for (i = 0 ; i < size; i++) cout << arr[i] << " " ; cout << endl ; } // Driver code int main () { int arr[] = { 64 , 34 , 25 , 12 , 22 , 11 , 90 }; int n = sizeof (arr)/ sizeof (arr[ 0 ]); bubbleSort(arr, n); cout << "Sorted array: \n" ; printArray(arr, n); return 0 ; }

Bubble Sort Algorithm

/*          *     .        *  .    *    *   . 
 .  *  move your mouse to over the stars   .
 *  .  .   change these values:   .  *
   .      * .        .          * .       */
const STAR_COLOR = '#fff';
const STAR_SIZE = 3;
const STAR_MIN_SCALE = 0.2;
const OVERFLOW_THRESHOLD = 50;
const STAR_COUNT = ( window.innerWidth + window.innerHeight ) / 8;

const canvas = document.querySelector( 'canvas' ),
      context = canvas.getContext( '2d' );

let scale = 1, // device pixel ratio
    width,
    height;

let stars = [];

let pointerX,
    pointerY;

let velocity = { x: 0, y: 0, tx: 0, ty: 0, z: 0.0005 };

let touchInput = false;

generate();
resize();
step();

window.onresize = resize;
canvas.onmousemove = onMouseMove;
canvas.ontouchmove = onTouchMove;
canvas.ontouchend = onMouseLeave;
document.onmouseleave = onMouseLeave;

function generate() {

   for( let i = 0; i < STAR_COUNT; i++ ) {
    stars.push({
      x: 0,
      y: 0,
      z: STAR_MIN_SCALE + Math.random() * ( 1 - STAR_MIN_SCALE )
    });
   }

}

function placeStar( star ) {

  star.x = Math.random() * width;
  star.y = Math.random() * height;

}

function recycleStar( star ) {

  let direction = 'z';

  let vx = Math.abs( velocity.x ),
	    vy = Math.abs( velocity.y );

  if( vx > 1 || vy > 1 ) {
    let axis;

    if( vx > vy ) {
      axis = Math.random() < vx / ( vx + vy ) ? 'h' : 'v';
    }
    else {
      axis = Math.random() < vy / ( vx + vy ) ? 'v' : 'h';
    }

    if( axis === 'h' ) {
      direction = velocity.x > 0 ? 'l' : 'r';
    }
    else {
      direction = velocity.y > 0 ? 't' : 'b';
    }
  }
  
  star.z = STAR_MIN_SCALE + Math.random() * ( 1 - STAR_MIN_SCALE );

  if( direction === 'z' ) {
    star.z = 0.1;
    star.x = Math.random() * width;
    star.y = Math.random() * height;
  }
  else if( direction === 'l' ) {
    star.x = -OVERFLOW_THRESHOLD;
    star.y = height * Math.random();
  }
  else if( direction === 'r' ) {
    star.x = width + OVERFLOW_THRESHOLD;
    star.y = height * Math.random();
  }
  else if( direction === 't' ) {
    star.x = width * Math.random();
    star.y = -OVERFLOW_THRESHOLD;
  }
  else if( direction === 'b' ) {
    star.x = width * Math.random();
    star.y = height + OVERFLOW_THRESHOLD;
  }

}

function resize() {

  scale = window.devicePixelRatio || 1;

  width = window.innerWidth * scale;
  height = window.innerHeight * scale;

  canvas.width = width;
  canvas.height = height;

  stars.forEach( placeStar );

}

function step() {

  context.clearRect( 0, 0, width, height );

  update();
  render();

  requestAnimationFrame( step );

}

function update() {

  velocity.tx *= 0.96;
  velocity.ty *= 0.96;

  velocity.x += ( velocity.tx - velocity.x ) * 0.8;
  velocity.y += ( velocity.ty - velocity.y ) * 0.8;

  stars.forEach( ( star ) => {

    star.x += velocity.x * star.z;
    star.y += velocity.y * star.z;

    star.x += ( star.x - width/2 ) * velocity.z * star.z;
    star.y += ( star.y - height/2 ) * velocity.z * star.z;
    star.z += velocity.z;
  
    // recycle when out of bounds
    if( star.x < -OVERFLOW_THRESHOLD || star.x > width + OVERFLOW_THRESHOLD || star.y < -OVERFLOW_THRESHOLD || star.y > height + OVERFLOW_THRESHOLD ) {
      recycleStar( star );
    }

  } );

}

function render() {

  stars.forEach( ( star ) => {

    context.beginPath();
    context.lineCap = 'round';
    context.lineWidth = STAR_SIZE * star.z * scale;
    context.globalAlpha = 0.5 + 0.5*Math.random();
    context.strokeStyle = STAR_COLOR;

    context.beginPath();
    context.moveTo( star.x, star.y );

    var tailX = velocity.x * 2,
        tailY = velocity.y * 2;

    // stroke() wont work on an invisible line
    if( Math.abs( tailX ) < 0.1 ) tailX = 0.5;
    if( Math.abs( tailY ) < 0.1 ) tailY = 0.5;

    context.lineTo( star.x + tailX, star.y + tailY );

    context.stroke();

  } );

}

function movePointer( x, y ) {

  if( typeof pointerX === 'number' && typeof pointerY === 'number' ) {

    let ox = x - pointerX,
        oy = y - pointerY;

    velocity.tx = velocity.tx + ( ox / 8*scale ) * ( touchInput ? 1 : -1 );
    velocity.ty = velocity.ty + ( oy / 8*scale ) * ( touchInput ? 1 : -1 );

  }

  pointerX = x;
  pointerY = y;

}

function onMouseMove( event ) {

  touchInput = false;

  movePointer( event.clientX, event.clientY );

}

function onTouchMove( event ) {

  touchInput = true;

  movePointer( event.touches[0].clientX, event.touches[0].clientY, true );

  event.preventDefault();

}

function onMouseLeave() {

  pointerX = null;
  pointerY = null;

}

Code Web Dev 

// This slide uses Auto-Animate to animate between
// two different code blocks
const distanceBetween = ( p1, p2 ) => {
 // TODO
}
 
distanceBetween([10,10], [50,50])

Code Transitions

// Measure the distance between two points
const distanceBetween = ( p1, p2 ) => {
  const dx = p1[0]-p2[0];
  const dy = p1[1]-p2[1];
 
  return Math.sqrt( dx*dx + dy*dy );
}
 
distanceBetween([10,10], [50,50])

Code Transitions

Problems!

Let's begin!

Problem Name

Given an array of size N, reverse the array.

 

Sample Input

N = 5

arr[] = {1,2,3,4,5}

 

Sample Output

5,4,3,2,1

Process

Build

During the ideation phase, expect to discuss the project in depth to clearly understand the goals and requirements.

1

2

3

Compile

During the ideation phase, expect to discuss the project in depth to clearly understand the goals and requirements.

Execute

During the ideation phase, expect to discuss the project in depth to clearly understand the goals and requirements.

With built-in \LaTeXLATEX typesetting, you can include math formulas like this:

f(x) = \int_{-\infty}^\infty \hat f(\xi)\,e^{2 \pi i \xi x} \,d\xi

f(x)=∫−∞∞​^(ξ)e2πiξxdξ

Homework

Our design team has a collective 75 years of experience in crafting digital products.

 

Our diverse backgrounds offer a thorough mix of points of view.

Table

C++ Java Python

Discovery of requirements for a project.

1

Creating a Plan that sets the requirements for the design and build phases.

3

Review and Iterate on the designs with testing of ideas, client feedback and prototypes.

5

Review and Iterate on the designs with testing of ideas, client feedback and prototypes.

2

4

6

Research into the project space, competitors and the market.

Design a number of iterations that capture the plans and requirements.

Build the project to an MVP to test and evaluate. Iterate using these learnings.

Made with Slides.com