Setup

Overview

Time: 0 min

Objectives

Open the W3schools SQL Tryit Editor v.1.6

Explore the sample database included with the SQL Tryit Editor

Workshop Setup

There is no setup required for this workshop. This workshop will utilize W3schools “SQL Try it Editor v1.6” for all exercises practicing SQL queries. Please follow the link below to access the SQL Tryit Editor in your browser.

SQL Tryit Editor v1.6

About the Data Used in this Workshop:

The database and tables used in the examples are from the W3Schools “SQL Editor”. These databases are present to all the users and can bee used to implement the concepts that are discussed in this workshop.

W3Schools Tryit Editor Database

Key Points

Introduction

Overview

Time: 5 min

Objectives

Introduction To SQL

INTRODUCTION TO SQL

A query is really a question or request for data. For example, “Tell me how many books there are on computer programming” or “How many Rolling Stones albums were produced before 1980?” When we query databases, we can use a common language to get the information.

SQL stands for Structured Query Language and is the standard language used in a Relational Database Management System (RDBMS). SQL is used to communicated with the database and perform necessary operations such as update existing data (or) retrieve data.

A database generally comprises of multiple tables and each table consists of multiple records (rows) and attributes (columns). SQL helps perform operations on the tables of the database.

Intro_1

Key Points

Introduction and advantages of SQL

Basic Queries

Overview

Time: 20 min

Objectives

Build understanding on important keywords of SQL

SELECT:

The SELECT statement is used to indicate what is required to be selected from the table. To select columns from the table, mention the names of the columns after SELECT keyword. To select all columns, indicate the asterisk (*) symbol after SELECT keyword.

Important Note – The SQL statement keywords are NOT case-sensitive and hence the keywords select and SELECT are treated the same way.

Consider the below table called Customers which contains details about various customers. (Only first five rows are shown)

Intro_2

Syntax	Description
SELECT * FROM table_name	Will select all the columns from the table
SELECT column1, column2,… FROM table_name	Will select only the specified columns from the table
SELECT column1 AS new_name FROM table_name	Selects only the specified column but renames the column to the new_namewhile displaying the result

Selecting all the columns

SELECT * FROM Customers

Intro_3

Basically, we are saying show me all the columns (*) from the table Customers.

Selecting particular columns

SELECT CustomerName, City FROM Customers

Intro_4

Renaming columns for display

SELECT CustomerName AS Name, City FROM Customers

Intro_5

Result columns are displayed in the order specified

SELECT Country, ContactName, PostalCode, CustomerID FROM Customers

Intro_6

WHERE:

The WHERE keyword is used to specify conditions. This keyword is used when you need to retrieve information matching certain conditions.

Syntax

SELECT columns FROM table_name WHERE condition

SELECT * FROM Customers WHERE Country="Germany"

Intro_7

Note : While using strings, the inverted commas (“string”) needs to be used.

DISTINCT:

The DISTINCT keyword is used to select and display all the unique values under the specified column. The keywords in SQL can be combined to get necessary results based on what kind of results which is required. For example, to identify all the different countries in the table, to identify the different departments where employees work in a employee table etc.

Syntax

SELECT DISTINCT column_name FROM table_name

SELECT DISTINCT Country FROM Customers

Intro_8

SELECT DISTINCT Country FROM Customers WHERE Country="USA"

Intro_9

ORDER BY:

The ORDER BY keyword is used to display the results in a particular order. You can specify the name of the column (or) a number indicating the column position based on which the results need to be ordered. By default, the results are ordered in ascending order but can be changed to descending using DESC keyword. Ordering can also be done based on more than one column.

Syntax

ASCENDING ORDER

SELECT column_name FROM table_name ORDER BY column_name/column_number

DESCENDING ORDER

SELECT column_name FROM table_name ORDER BY column_name/column_number DESC

FIRST COLUMN DESCENDING AND SECOND COLUMN ASCENDING

SELECT column_name FROM table_name ORDER BY column_1 DESC, column_2

FIRST COLUMN ASCCENDING AND SECOND COLUMN DESCENDING

SELECT column_name FROM table_name ORDER BY column_1, column_2 DESC

Examples:

SELECT CustomerID, CustomerName, ContactName, City FROM Customers ORDER BY CustomerName

Intro_10

SELECT CustomerID, CustomerName, ContactName, City FROM Customers ORDER BY CustomerName DESC

Intro_11

Aliases

SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of that query.

An alias is created with the AS keyword.

Syntax

SELECT column_name AS alias_name FROM table_name

SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

Intro_12

Key Points

Introduction to SQL and its keywords like SELECT, FROM, AS, WHERE, DISTINCT, ORDER BY

Operators

Overview

Time: 20 min

Objectives

Understanding the concepts of Operators using SQL

OPERATORS

The operators help perform operations on the data in the table. They can be used to perform arithmetic operations or filter based on certain conditions.

Consider the below table:

Opt_1

ARITHMETIC OPERATORS:

The arithmetic operators help perform arithmetic operations on the data in the table

Opt_2

Example:

SELECT ProductID, Unit, Price, Price * Unit AS Total_sales, Price % Unit AS Modulo_result FROM Products

Opt_3

COMPARISON OPERATORS:

The comparison operators are used to filter the results based on a condition. They are generally passed in the WHERE clause and filters the result based on the condition provided.

Opt_4

Syntax:

SELECT columns FROMv table_name WHERE condition <comparison_operator_of_choice> value

Examples:

SELECT ProductID, ProductName, Price FROM Products WHERE ProductID <= 15

Opt_5

SELECT ProductID, ProductName, Price FROM Products WHERE Price = 18

Opt_6

LOGICAL OPERATORS:

SQL has three logical operators which can be used to specify more than one condition in the WHERE clause.

The three operators are:

AND–Returns results when all the specified conditions are true
OR–Returns results when at least one of the specified conditions are true
NOT –Returns results when the opposite of conditions are true

Syntax:

SELECT columns FROM table_name WHERE condition1 AND/OR condition2

SELECT columns FROM table_namem WHERE NOTcondition

Examples:

SELECT CustomerName, Country, City, PostalCode
From Customers
Where Country = 'Brazil' AND City = 'São Paulo'

Opt_7

SELECT CustomerName, Country, City, PostalCode
From Customers
Where City = 'Nantes' OR City = 'Paris'

Opt_8

SELECT CustomerName, Country, City, PostalCode
From Customers
Where Not City = 'London'

Opt_9

Key Points

Familiarize with Arithmetic operations, Comparison operators and Logical Operators

Joins

Overview

Time: 20 min

Objectives

Understanding the concepts of JOINS

Data manipulation using Joins in SQL

JOINS

A database generally comprises of multiple tables and a RDBMS comprises of tables which are related. When data is required to be extracted from two or more tables which are related to each other then we perform a join to combine the tables first and then extract the result. Joins_1

Consider the below two tables which will be used for upcoming examples Joins_2

Joins_3

INNER JOIN:

The INNER JOIN combines tables and returns the records only when there is an exact match between the columns on which the join is performed.

Syntax:

SELECT columns FROM Table1 INNER JOIN Table2 ON Table1.column=Table2.column

Example:

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID

Joins_4

LEFT JOIN:

The LEFT JOIN combines tables and returns all the records from the left table. For records where there is no match in the right table it is displayed as a null Syntax

Syntax:

SELECT columns FROM Table1 LEFT JOIN Table2 ON Table1.column=Table2.column

Example:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

Joins_5

RIGHT JOIN:

The RIGHT JOIN combines tables and returns all the records from the right table. For records where there is no match in the left table it is displayed as a null

Syntax:

SELECT columns FROM Table1 RIGHT JOIN Table2 ON Table1.column=Table2.column

Example:

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID

Joins_6

OUTER JOIN:

The OUTER JOIN combines tables and returns the records only when there is an exact match between the columns on which the join is performed.

Syntax:

SELECT columns FROM Table1 FULL OUTER JOIN Table2 ON Table1.column=Table2.column

Joins_7

SELF JOIN:

A SELF-JOIN refers to a condition where a table is joined with itself. We do not use a keyword for self-join but rather specify the name of the table twice (ach with a different alias) and then provide the joining condition in the WHERE clause.

Syntax:

SELECT columns FROM Table1 T1, Table2 T2 ON T1.column=T2.column

Example:

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City

Joins_8

Key Points

Familiarize the concepts of left join, right join, inner join, outer join and self join along with hands on examples