SQL for Product Managers
FROM BASICS TO INTERMEDIATE LEVEL
Why SQL is needed for Product Managers?
Product managers sets an objective and fixes a success criteria while monitoring metrics or KPIs(Key-Performance-Indicators).
If you are a Product Manager, SQL will help you:
- Monitoring Metrics & KPIs
- Maked a PM more independent
- Improves the knowledge about the Product
What is SQL?
Structured Query Language is used for RDBMS(Relational Database Management System).
Database contains tables and schemas which are linked through Primary Key & Foreign Keys.
E-R Diagram, Primary-Key and Foreign-Key
Source: Arctype
Primary Key refers to a key or a column which should:
- Have a Unique ID
- Value cannot be Null
Foreign Key refers to the key which is present into other table(mainly in terms of primary key of another table).
Where in Orders Table, primary key is orderID and foreign keys are customerID, employeeID and productID, have a relation of Orders Table with Customers Table, Employees Table and Products Table.
Types of Commands
There are 5 types of SQL Commands:
- DDL(Data Definition Language)
- DML(Data Manipulation Language)
- TCL(Transaction Control Language)
- DQL(Data Query Language)
- DCL(Data Control Language)
Source: GFG (GeeksForGeeks)
Usually, Product Managers need mainly SELECT command for the fetching-out the data and analyzing the data-metrics.
Now, Let’s suppose that we have a Customer Table.
Phase 01: SELECT | WHERE | ORDER BY
Let’s Call-out the table with the whole columns!
- Show the Customer Table:
SELECT*FROM Customers;
Result:
2. Fetch the name of customers who stay in Germany:
SELECT CustomerName
FROM Customers
WHERE Country = "Germany";
Result:
3. Select Customers’ name, City. WHERE Country is “Germany”, and sort the result alphabetically by the column City
.
SELECT CustomerName, City
FROM Customers
WHERE Country = "Germany"
ORDER BY City;
Result:
Phase 02: MAX | MIN | AVG | SUM | COUNT
Now, let’s suppose that we have a Products Table
Give us the ProductID, ProductName which have maximum, minimum and average price of products in Products Table:
Maximum Product Price
SELECT ProductID, ProductName, MAX(PRICE)
FROM Products;
Result:
Minimum Product Price
SELECT ProductID, ProductName, MIN(PRICE)
FROM Products;
Result:
Now, we can calculate the Profits(Use-cases).
Average Product Price
SELECT AVG(PRICE)
FROM PRODUCTS;
Result:
SUM of Products’ Prices
SELECT SUM(Price) FROM Products;
Result:
COUNT of Products:
SELECT COUNT(*) FROM Products;
Result:
Phase 03: WILDCARDS(Like) | IN | BETWEEN
Source: W3SCHOOLS.COM
IN Operator
Let’s again consider Customers Table
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
Result
NOT IN Operator
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');
Result:
BETWEEN Operator
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
Phase 04: JOINS | INNER | OUTER | LEFT | RIGHT
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
Result:
Above example depicted INNER JOIN only.
So, there are various types of JOINS:
Source: W3SCHOOLS.COM
Thank-you 😁