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

English [Auto],

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
Begin with relational database basics and practical sql for data analysis and software development. Install sql server tools, create databases and tables, and master queries, joins, and python pandas visualizations.
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
Learn how to create a database and its first table using phpmyadmin gui, including setting an auto increment primary key, planning columns, and using underscores for table names.
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
Learn to view all data with select star from a table and to fetch specific columns like customer name and created, using comma separated list and semicolons for multiple queries.
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
Discover how a character set defines encoding and allowed characters, while a collation governs text comparison in mysql. This is illustrated by latin1 and latin1_swedish_ci during creation and export.
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;

Install SQL Server Express 20127:32
Install SQL Server Express 2012 on Windows, download SQL Server Express and SQL Server Management Studio, configure the instance, choose mixed mode or Windows authentication, and complete the setup.
Install SQL Server Management Studio and Importing a sample database10:54
Install SQL Server Management Studio and import the Northwind sample database to learn managing databases, tables, and basic queries, including identity and primary key concepts.

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
Learn to use the where clause with equal to and greater than operators to filter string and numeric data, selecting specific columns from customers and products.
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
Create a database and a table in MSSQL using SSMS, configure an identity primary key with seed and increment, and define columns like employer id, name, phone, and e-mail.
How to insert Values in a MSSQL Server Database9:02
Learn how to insert values into a MSSQL Server table using insert into with a column list and values, and via the SSMS UI, including identity columns.
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
Explore how to use limit in MySQL and top in MSSQL Server to restrict query results, select top rows, and tailor data for analytics and software development.
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

SELECT INTO Statement - MSSQL Example8:17
Learn how to use the select into statement in MSSQL to copy data from one table to another, creating backups and copies across databases for full tables or selected columns.
INSERT INTO SELECT Statement - MYSQL and MSSQL10:31
Use insert into select to copy data from one table to another by selecting specific columns with matching data types, creating the destination table first, demonstrated in MySQL and MSSQL.

String Datatypes in MySQL21:23
Explore string datatypes in MySQL, including char, varchar, text, blob, enum, and set. Learn how to define column sizes, constraints, and when to use fixed or variable length storage.
MySQL Numeric Datatypes16:55
Explore MySQL numeric datatypes, from integer types (tinyint to bigint) with unsigned ranges to fixed-point decimal and money, with floating point types like float and double, and auto_increment primary keys.
SQL Server Exact Numeric Datatypes26:30
Explore SQL Server exact numeric datatypes, including decimal and numeric with fixed precision and scale. Learn when to use money, small money, bigint, and identity for table design.
Character Strings and Unicode Character Strings in SQL Server21:06
Explore character strings and Unicode in SQL Server, comparing fixed and variable length types (char, varchar, nchar, nvarchar) with max and text options, and how to store multilingual data.
Binary Strings and Other Datatypes in SQL Server17:35
Explore SQL Server data types, including binary strings and varbinary, image, timestamp, and uniqueidentifier, and learn fixed vs variable length data with examples and beginner guidance.

Working with DATES in SQL Part 114:15
Master working with date data types in SQL, including date, timestamp, and datetime, and learn to create tables, insert current timestamps, and filter data by date using operators and between.
Working with DATES in SQL Part 226:30
Learn to work with SQL dates by querying date ranges, using date functions such as current_timestamp, getutcdate, datepart, datefromparts, and datediff for orders.

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: