SQL Programming for Data Analysis and Software Development

Name: SQL Programming for Data Analysis and Software Development
Rating: 4.2 (44 reviews)

Learn Fundamentals of RDBMS with MySQL and MS SQL Server

Created byAnkit Srivastava

Last updated 5/2020

English

What you'll learn

Step by Step SQL Programming Language for Data Analysis and Software Development.
Learn MySQL from scratch
Learn MS SQL Server from scratch
Learn Creating Databases for Applications
Learn Database transfer from Local hosting to Web Hosting
Learn basics of Python Pandas for connecting MySQL Database using Anaconda Navigator
Learn Use of tools like MySQL Workbench and SQL Server Management Studio for Database Administration

Course content

22 sections • 119 lectures • 25h 34m total length

Introduction25:12
List of tools on which you will work0:13
01 What is SQL, What is RDBMS and Table16:24
What is SQL
SQL stands for Structured Query Language
SQL is a Programming Language for creating, accessing and manipulating Relational Databases.
What can SQL Do ?
Create Relational Databases for Organizations and Applications.
Create Tables inside Databases
Select, Insert, Update and Delete Records in Database.
Create Stored Procedures in Database.
You can create Database for your Web or Desktop application.
Although SQL is an ANSI/ISO standard, there are different versions of the SQL language.
An RDBMS database program (i.e. MS Access, SQL Server, MySQL etc.) can be used to create and manage databases for your applications.
A Server Side Scripting Language like – Python, PHP, ASP etc. can be used to interact with the SQL databases.
Please Download the PPT File for explanation of various topics.
How to Install MySql0:24
02 How to Install MySQL - Xampp and What is Schema10:45
Go to apachefriends.org website to download.
Note: Whenever you have to open PhpMyAdmin for creating or managing a database, make sure that in your Xampp Control Panel - Apache and MySql Module are running . (turned green)
03 Creating a Database and Datatable Using phpMyadmin GUI27:54
04 Creating SQL Table with SQL Queries CREATE30:58
-- Create a database using SQL query --

CREATE DATABASE `db_super_store`

-- Create table inside that database using query --

-- item_categories - electronics, home & furniture, books --

-- 99 numeric or a string alphabets, numbers and special char--

CREATE TABLE `categories`(
category_id int(2) not null auto_increment primary key,
category_name varchar(100) not null,
category_desc varchar(500) not null
)

create table `customers`(
customer_id int(10) not null auto_increment primary key,
customer_name varchar(50) not null,
customer_phone varchar(15) not null,
customer_email varchar(50) not null
)

-- creating database and table without queries --
-- creating database and table with queries --
-- insert data without query --
-- insert data with query --

INSERT INTO `tbl_school` (`school_id`, `school_name`, `email`,
`website`, `address`, `phone`)
VALUES (NULL, 'XYZ School', 'xyz@example.com',
'xyzexamplesxhool.com', '111, Exxmple Street, Delhi, India',
'+91-345'),
(NULL, 'UVW School', 'uvw@examplemail.com',
'www.uvwexamplesxhool.com', '', '+91-457');
05 Insert with and without queries in Mysql23:29

INSERT INTO `tbl_school` (`school_id`, `school_name`, `email`,
`website`, `address`, `phone`)
VALUES (NULL, 'XYZ School', 'xyz@example.com',
'xyzexamplesxhool.com', '111, Exxmple Street, Delhi, India',
'+91-345'),
(NULL, 'UVW School', 'uvw@examplemail.com',
'www.uvwexamplesxhool.com', '', '+91-457');
06 Insert Exercise, Alter Table Basic and Default value15:31
-- creating database and table without queries --
-- creating database and table with queries --
-- insert data without query --
-- insert data with query --

INSERT INTO `tbl_school` (`school_id`, `school_name`, `email`,
`website`, `address`, `phone`)
VALUES (NULL, 'XYZ School', 'xyz@example.com',
'xyzexamplesxhool.com', '111, Exxmple Street, Delhi, India',
'+91-345'),
(NULL, 'UVW School', 'uvw@examplemail.com',
'www.uvwexamplesxhool.com', '', '+91-457');

-- Insert 1 Value with query --

-- name of `database`, `table`, `columns` in mysql
-- insert into tbl_name (col1, col2, col3 -- coln)
--INSERT INTO `categories` (`category_name`, `category_desc`) --
--VALUES ('Electronics','Electronics equipment like laptop, mobile, tv etc') --

INSERT INTO `categories` (`category_name`, `category_desc`)
VALUES ('Beverages','Hot and cold drinks'),('Books','Text books, good reads')

-- insert into tbl_name (col1, col2, col3) values('val1','val2','val3')

insert into customers(customer_name, customer_email, customer_phone)
values('monish', 'monish@slidescope.com', '9953'),
('amit', 'amit@slidescope.com', '9876'),
('mukul', 'mukul@slidescope.com', '9954')

INSERT INTO `customers`(`customer_name`, `customer_phone`, `customer_email`, `credit`)
VALUES ('vivek','1234','vivek@swapthebook.com',89)

ALTER TABLE `customers`
ADD `credit` FLOAT(5) NOT NULL DEFAULT '100'
AFTER `customer_email`;

--alter table tbl_name
--add newcol varchar(5) not null default 'good'
--after `customer_name`
07 SQL Select All and Select Specific Columns from Table9:25
08 Select Distinct Values from a Column3:56
-- select DISTINCT --

SELECT DISTINCT(customer_name) FROM customers
Sample Databases Download0:10
09 How to Import MySQL Database (Northwind) from a SQL File10:50
Find the SQL file in resources.
What is a Collation and a Character Set in MySQl4:55
UPDATE Records in SQL - MySQL Example19:42
STEP BY STEP SQL TUTORIAL FOR
DATA ANALYTICS AND SOFTWARE
DEVELOPMENT

Topic - UPDATING RECORDS IN SQL TABLE

UPDATE TABLE SET Col2='val', Col3='val2'
WHERE some condition

UPDATE customers SET
`City`='Lucknow', Country='India'
WHERE `CustomerID`= 'ROMEY'
**was a primary key

UPDATE products
SET `UnitPrice` = 19.8
WHERE `ProductName`= 'Chai'

SELECT `city` FROM `customers` WHERE
`Country` = 'India'

Selling to Customer
Purchase by Customer -
decrease in UnitsinStock
2 Chai

Trader will Purchase the product
Supplier
4 Chai from Supplier Mohan
UnitsInStock 18 + 4 = 22

Customer Cancel an Order
units in order
Add Units In Stock
DELETE in MySQL - Delete Records and Drop Table10:08
STEP BY STEP SQL TUTORIAL FOR
DATA ANALYTICS AND SOFTWARE
DEVELOPMENT

Topic - Deleting Records from a table
in SQL

DELETE FROM `customers`
WHERE `CustomerID`= 'LACOR'

DELETE FROM `customers` WHERE
`City`= 'Madrid' AND ContactName
LIKe 'L%'

DELETE FROM `customers`
WHERE `Country`= 'Spain'

DELETE FROM `tbl_hospital`
WHERE `tbl_hospital`.`id` = 2

Drop a table from DB

DROP Table aprofile
Comments in SQL - MYSQL AND MSSQL5:45
-- Single Line Comment in Sql

/* Multiple
lines
comment in SQL */
NULL Functions IFNULL and ISNULL in SQL6:31

-- IFNULL - SQL -- MySQL
SELECT name, phone , IFNULL(email,'NA') from staff_tbl
SELECT name, IFNULL(phone,'Not Entered') AS phone, IFNULL(email,'NA') as email from staff_tbl

-- ISNULL - SQL - MSSQL
SELECT name, ISNULL(email,'Not Entered') from foisnulltest
Order By - ASC and DESC with SELECT13:03
ORDER BY -> ASC | DESC

SELECT * FROM `customers` ORDER BY `ContactName` ASC

SELECT * FROM `customers` ORDER BY Country ASC, ContactName DESC

SELECT * FROM `customers` ORDER BY Country, ContactName
ALIAS Use of AS keyword to Rename Columns in SQL Table12:57
Some Examples of As Keyword You can apply on Northwind Database :
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;

JOIN MORE THAN ONE COLUMNS AND MAKE A BIGGER COLUMN
IN MSSQLSERVER
SELECT ContactName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;
________________-
In MYSQL – the CONCAT

SELECT ContactName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers;
UNION and UNION ALL Operators in SQL11:32
Some Examples of UNION in SQL

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

------
/*The following SQL statement returns
the cities (duplicate values also) from both the "Customers"
and the "Suppliers" table:*/
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

------ WITH WHERE

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

--- WHERE and UNION ALL

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

--- ADVANCED EXAMPLE ---
SELECT 'Customer' As Type, ContactName, City, Country
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers;

09 Where Clause Part 1 with Select, Distinct Revision11:38
WHERE CLAUSE EXAMPLE
-- Select ALL, Where & String Value to Compare =
SELECT * FROM `customers` WHERE Country = 'Germany'
-- Select some columns, string value to compare in where clause =
SELECT ContactName, Country, City FROM `customers` WHERE Country = 'Germany'

-- Select Some Columns with Where , Compare Numeric value =
SELECT ProductName, UnitPrice FROM `products` where UnitPrice = 18
SELECT * FROM products where ProductID = '11'

SELECT * FROM products where ProductID > 11
-- select some columns from products where condition
SELECT ProductName, UnitPrice FROM products where UnitPrice > 11

SELECT * FROM `products` WHERE UnitPrice < 18
SELECT * FROM `products` WHERE UnitPrice <= 18
SELECT * FROM `products` WHERE UnitPrice >= 18

-- Not Equal
SELECT * FROM `products` WHERE UnitPrice <> 18

-- ANALYZE
-- Buy A laptop - Dell, 8 GB Ram, 40000 Rs
-- USER Login
--Select * from users where username='slidescope' and password='abc123' -- correct
--Select * from users where username='slidescope' or password='abc123' -- wrong

SELECT * FROM `products` WHERE UnitPrice > 18 AND UnitsInStock < 50

-- Name of Cutomers who are from Berlin or London
SELECT `ContactName`,`City` FROM `customers` WHERE `City` = 'London' OR `City`='Berlin' OR `City`='Madrid'

SELECT `ContactName`,`City` FROM `customers` WHERE `Country` = 'UK' AND `City`='London'

-- BETWEEN - Between a given range --
SELECT `ProductName`,`UnitPrice`, `UnitsInStock`
FROM `products`
WHERE `UnitsInStock`
BETWEEN 30 AND 50

-- LIKE - to search for a specified pattern in a column. --
-- We use two WILDCARDS with LIKE - '%' Percentage and '_' Underscore

-- % - represents zero, one, or multiple characters
-- _ represents single character
--London--
-- Task - Select City from Customers which have 'on' in their names
SELECT DISTINCT(`City`) FROM `customers` WHERE City LIKE '%on%'

-- Task - Select City Names which end with 'on' from Customers
SELECT DISTINCT(`City`) FROM `customers` WHERE City LIKE '%on'

-- Task - Select City Names which start with 'L' from Customers
SELECT DISTINCT(`City`) FROM `customers` WHERE City LIKE 'L%'
SELECT DISTINCT(`City`) FROM `customers` WHERE City LIKE 'L%' OR City LIKE '%m'

-- Task - Select City Names ( which start with 'L' and have 6 Characters )
-- from Customers
SELECT DISTINCT(`City`) FROM `customers` WHERE City LIKE 'L_____'

-- Task - City Name - L first letter and n is third letter
SELECT DISTINCT(`City`) FROM `customers` WHERE City LIKE 'L_n%'
SELECT DISTINCT(`City`) FROM `customers` WHERE City LIKE '_o_t%'
10 Where Clause with Equal and Greater than Operators22:45
11 WHERE with Greater than Equal, Less than equal , Not equal with AND and OR30:09
-- Greater than and Less than Equal
SELECT * FROM `products` WHERE UnitPrice <= 18
SELECT * FROM `products` WHERE UnitPrice >= 18
-- Not Equal
SELECT * FROM `products` WHERE UnitPrice <> 18

-- ANALYZE
-- Buy A laptop - Dell, 8 GB Ram, 40000 Rs
-- USER Login
--Select * from users where username='slidescope' and password='abc123' -- correct
--Select * from users where username='slidescope' or password='abc123' -- wrong

SELECT * FROM `products` WHERE UnitPrice > 18 AND UnitsInStock < 50

-- Name of Cutomers who are from Berlin or London
SELECT `ContactName`,`City` FROM `customers` WHERE `City` = 'London' OR `City`='Berlin' OR `City`='Madrid'

SELECT `ContactName`,`City` FROM `customers` WHERE `Country` = 'UK' AND `City`='London'
12 BETWEEN Operator in Where Clause6:46
-- BETWEEN - Between a given range Example --
SELECT `ProductName`,`UnitPrice`, `UnitsInStock`
FROM `products`
WHERE `UnitsInStock`
BETWEEN 30 AND 50
13 LIKE Operator in Where Clause21:15

-- LIKE - to search for a specified pattern in a column. --
-- We use two WILDCARDS with LIKE - '%' Percentage and '_' Underscore

-- % - represents zero, one, or multiple characters
-- _ represents single character
--London--
-- Task - Select City from Customers which have 'on' in their names
SELECT DISTINCT(`City`) FROM `customers` WHERE City LIKE '%on%'

-- Task - Select City Names which end with 'on' from Customers
SELECT DISTINCT(`City`) FROM `customers` WHERE City LIKE '%on'

-- Task - Select City Names which start with 'L' from Customers
SELECT DISTINCT(`City`) FROM `customers` WHERE City LIKE 'L%'
SELECT DISTINCT(`City`) FROM `customers` WHERE City LIKE 'L%' OR City LIKE '%m'

-- Task - Select City Names ( which start with 'L' and have 6 Characters )
-- from Customers
SELECT DISTINCT(`City`) FROM `customers` WHERE City LIKE 'L_____'

-- Task - City Name - L first letter and n is third letter
SELECT DISTINCT(`City`) FROM `customers` WHERE City LIKE 'L_n%'
SELECT DISTINCT(`City`) FROM `customers` WHERE City LIKE '_o_t%'

-- *** In MS Access asterisk (*) is used instead of the percent sign (%),
-- and a question mark (?) instead of the underscore (_).
ANY and ALL Operators in SQL8:25
-- SQL ANY Operator -- Works on MSSQL and MYSQL

/*The query will give us all the Product names from Products table
if it finds ANY records in the "Order Details" table with
Quantity = 15

This type of query can be helpful in Analyzing Orders with large number of
products */
-- MSSQL--
SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID
FROM "Order Details" WHERE Quantity = 15);

--MYSQL--
SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID
FROM `Order Details` WHERE Quantity = 15);

-- SQL ALL Operator -- It will return TRUE only if all conditions in
-- subquery are true
EXISTS in SQL - MySQL and MSSQL Examples9:19
--- EXISTS in SQL --- Works in MSSQL and MYSQL

-- Select Name of that Supplier company from Supplier table
-- where products name is Chang
SELECT suppliers.CompanyName
FROM Suppliers
WHERE EXISTS (
SELECT ProductName FROM Products
WHERE products.SupplierID = suppliers.SupplierID
AND products.ProductName = 'Chang'
)

---------------------------------------------------------------
-- Select Name of that Supplier company from Supplier table
-- where products table has a unitsinstock > 53

SELECT CompanyName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products
WHERE Products.SupplierID = Suppliers.supplierID AND UnitsInStock > 53)
14 IN, NOT IN Operator9:02
-- SQL IN
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

-- Task - Get Marketing Manager, Owner From These Countries ('Germany', 'France', 'UK')
-- SQL NOT IN
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

-- We want customers who are not from Germany
-- USE OF NOT
SELECT * FROM Customers
WHERE NOT Country='Germany';
-- alternate query
SELECT * FROM Customers
WHERE Country <> 'Germany'

JOINS in SQL - INNER JOIN with Example19:31
Note : Pls download attached files
use mssql-north.sql is for sql server northwind sample database
user northwindz.sql for MySQL database

--SQL SERVER & MySQL Example -
INNER JOIN
Select categories.CategoryName,
count(products.ProductID) AS Num_of_Products
FROM categories
INNER JOIN products
ON products.CategoryID = categories.CategoryID
GROUP BY categories.CategoryName

Four Table Join

SELECT orders.OrderID, customers.ContactName,
shippers.CompanyName, employees.FirstName, employees.LastName
FROM orders
RIGHT JOIN employees
ON orders.EmployeeID = employees.EmployeeID
LEFT JOIN customers
ON customers.CustomerID = orders.CustomerID
INNER JOIN shippers
ON shippers.ShipperID = orders.ShipVia
WHERE OrderDate BETWEEN '1996-07-04' AND '1996-07-18'
AND OrderID = 10258

----------------------
JOINS in SQL - RIGHT JOIN with MySQL Example7:36
--Example for Northwind Database --
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
JOINS in SQL - LEFT JOIN with MySQL Example8:03
SELECT Customers.ContactName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.ContactName
FULL OUTER JOIN or FULL JOIN IN SQL Server9:12
--- SOME EXAMPLES OF FULL JOIN --

-- A Query to Return OrderIDs and Name of Employees who took that order

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
FULL JOIN Employees
ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

--- A query to Return Custmer Name and the OrderIds of their orders.

SELECT Customers.ContactName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.ContactName;
Self Join in SQL0:28

How to create a Database and Table in MSSQL - Using SSMS11:18
How to insert Values in a MSSQL Server Database9:02
Creatig Backup of Databases in SQL Server10:22
-- Please use this SQL Command to create a Sample Database --
CREATE Database sample_company;

USE sample_company;

CREATE TABLE Employees
(
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
gender VARCHAR(50) NOT NULL,
salary INT NOT NULL,
department VARCHAR(50) NOT NULL
)

INSERT INTO Employees VALUES
(1, 'Mukesh', 'Male', 5000, 'Sales'),
(2, 'Jimmy', 'Female', 6000, 'HR'),
(3, 'Kuldeep', 'Female', 7500, 'IT'),
(4, 'Will', 'Male', 6500, 'Marketing'),
(5, 'Lucy', 'Female', 5500, 'Finance'),
(6, 'Ricky', 'Male', 8000, 'Sales'),
(7, 'Mark', 'Male', 7200, 'HR'),
(8, 'Laura', 'Female', 6600, 'IT'),
(9, 'Jane', 'Female', 5400, 'Marketing'),
(10, 'Laura', 'Female', 6300, 'Finance'),
(11, 'Mac', 'Male', 5700, 'Sales'),
(12, 'Pat', 'Male', 7000, 'HR'),
(13, 'Julie', 'Female', 7100, 'IT'),
(14, 'Elice', 'Female', 6800,'Marketing'),
(15, 'Wayne', 'Male', 5000, 'Finance');

LIMIT in MySQL and TOP in MSSQL Server6:26
Bottom N Values in MySQL and MSSQL Using Derived Table9:17
-- Getting Bottom N Values in SQL --

-- Using A Derived Table --
-- A virtual table that is created within the scope of a query.
-- The table is created with a SELECT statement of its own
-- It is given an alias using the AS clause.
-- Simple Work Around for MySQL --
SELECT * FROM
(SELECT id,name FROM employees Order By id DESC LIMIT 5) AS MT
ORDER BY id ASC

--------------------------------------
-- SQL Server Example --
SELECT * FROM
(SELECT TOP(5) id,name FROM employees Order By id DESC ) AS MT
ORDER BY id ASC

Requirements

Basic Knowledge of Computer Concepts.

Description

What will you learn in this SQL Tutorial?

Introduction to SQl and its Applications
Introduction to RDBMS and Tables
Installing MySql , MSSQL etc.
Creating a Database
Creating a Table and Inserting Data
Writing Queries for
Select, Select Distinct
Where Clause
And, Or & Not
Order By Clause
Insert Into Statement
Null Values
Update Records in Table
Delete Records from Table
Select Top Statement
Min and Max Aggregate Functions
Count, Avg, Sum Aggregate Functions
Like Operator with Where Clause
Wildcards in SQL for Pattern matching
Use of IN Keyword in where and having clause
Between Keyword in where clause
Aliases for renaming columns
Joins - Joining two or more tables to fetch data
Inner Join
Left Join
Right Join
Full Join
Self Join
Union and Union All for Joining two tables
Group By Statement for One and More Tables
Having Clause
Use Exists Keyword
Use of Any and All Operators in SQL
Select Into Statement
Insert Into Select Statement
Functions in MySQL and MSSQL
Datatypes in MySQL and MSSQL
Case Function
Null Functions
What are Stored Procedures and how to use them
How to write Comments in SQL
Create, Drop and Backup DB
Create, Drop and Alter Table
Null and Not Null Constraints
SQL Unique Constraints
Creating SQL Primary Key and PK Constraints
SQL Foreign Key Constraints
SQL Default Constraints
Creating SQL Index
Using SQL Auto Increment
Working with Dates in SQL
Creating SQL Views
SQL Injection
Connecting MySQL Databases with Python Pandas
Using MySQL workbench for accessing MySQL databases , creating tables , inserting , updating, selecting and deleting records.
Using SQL Server Management Studio for accessing MSSQL databases , creating tables , inserting , updating, selecting and deleting records.
Graphical Visualization of Data in Phpmyadmin (mysql) and Python Pandas Module (Basic Graphs).
Web Hosting of MySql and MSSQl Databases

Who this course is for:

Beginners who want to build their career in the field of Data Science or Software Development.
Scool or College students who have SQL Language as a Subject in their curriculum.
Beginners who want to create SQL Database for their own web applications

SQL Programming for Data Analysis and Software Development

What you'll learn

Explore related topics

Course content

Introduction21 lectures • 4hr 20min

Install SQL Server Express and SQL Server Management Studio (Windows OS)2 lectures • 18min

WHERE Clause in SQL8 lectures • 1hr 59min

JOINS in SQL5 lectures • 45min

MSSQL Server Basics - How to Use SQL Server Management Studio3 lectures • 31min

SELECT Records from TOP (In MSSQL), From Bottom and Use of LIMIT in MySQL2 lectures • 16min

"SELECT INTO' and 'INSERT INTO SELECT' Statements2 lectures • 19min

Conditions In SQL - CASE and ELSE Statement1 lecture • 9min

Datatypes in SQL - MySQL and SQL Server Examples5 lectures • 1hr 43min

DATE and TIME Datatype in SQL with Functions2 lectures • 41min

Requirements

Description

Who this course is for: