select count(*) as 'bengal_Customers' from cust_dimen where state='West Bengal' and city='Kolkata'
select *from cust_dimen where state in ('Tamilnandu','Karnataka')
select *from cust_dimen where Customer_Segment!='SMALL BUSINESS'
select Customer_Name from cust_dimen where city like 'k_%'
select count(sales) As 'Total Sales Records' from market_fact_full
select count(Customer_Name) as 'City Wise Cutomer', city, Customer_Segment from cust_dimen group by city, customer_segment
select count(ord_id) as 'loss count' from market_fact_full where profit<0
select customer_name from cust_dimen order by customer_name, city desc
-- list most ordered product
select prod_id, sum(order_quantity) from market_fact_full group by prod_id order by sum
Count
finding total sales
select count(*) from market_fact_full as Total_Sales
or
select count(sales) from market_fact_full as Total_Sales
(Different between count(*) and count(Sales) is that: count(*) count null values while count(sales) don't count any null values)
Group Aggregation
select (attributes)
from (table)
where (filter_condition)
group by (attributes_to_be_grouped_upon)
having (filter_condition_on_grouped_values)
order by (values)
limit (no_of_values_to_display);
select from where group by having order by
Count the customer City wise
select count(customer_name) as 'Number of Customer', city from cust_dimen group by city
Count the customer City wise and segmentwise
select count(customer_name) as 'Number of Customer', city, Customer_segment from cust_dimen group by city, customer_segment
Find the customer order which has been sold at a loss
select count(ord_id) as 'Number of Loss Order' from market_fact_full where profit<0
Find total number of customer from Bihar in each segment
select count(customer_name) as 'Segment wise Customer', state, customer_segment from cust_dimen where state='Bihar' group by customer_segment
or
select count(customer_name) as 'Segment wise Customer', customer_segment from cust_dimen where state='Bihar' group by customer_segment
Order by
Display customer name
select distinct customer_name from cust_dimen order by Customer_Name
Display customer name in descending way
select distinct customer_name from cust_dimen order by Customer_Name desc
Display Three most ordered product
select prod_id, sum(order_quantity) from market_fact_full group by prod_id order by sum(order_quantity) desc limit 3;
Display Three least ordered product
select prod_id, sum(order_quantity) from market_fact_full group by prod_id order by sum(order_quantity) asc limit 3;
Having Clause
different between where and filter :
where is the filter which is apply on entire table and having is a filter which will apply after computing the aggregate function.
Print product quantity where sales having more than 50
select prod_id, sum(order_quantity) from market_fact_full group by prod_id having sum(order_quantity)>50 order by sum(order_quantity)
Display first character for first name and last name in upper case
Monthwise order shipped
select count(ship_id) as ship_count, month(Ship_Date) as 'Ship Month'
from shipping_dimen
group by month(Ship_Date)
order by ship_count desc
Regular Expression
Regular expression is a mechanism which can be used for pattern matching in text and widely being used in text processing.
Find the customer names having substring car
select customer_name from cust_dimen where customer_name regexp 'car'
Print the customer names starting with A, B, C, D and ending with er.
select customer_name from cust_dimen where customer_name regexp '^[abcd].*er$'
Rules :
Begin : ^ (Char AT symbol)
Square Brackates[]: charac ters which need be check
Ending : $( dollar matches end of the string)
. (dot) : for anything
*(star): any number of anything
Nested Query
Print the order number of most valuable order by sales
select ord_id, Sales, round(sales) as 'Rounder Sales' from market_fact_full where sales=(select max(sales) from market_fact_full)
Print most customer customer
select customer_name, cust_id from cust_dimen
where cust_id= (select cust_id from market_fact_full group by cust_id order by count(cust_id) desc limit 1)
Print three most common products
select Product_Category, Product_Sub_Category
from prod_dimen
where prod_id in ( select prod_id from market_fact_full group by prod_id order by count(prod_id) desc);
Join Operation
When we wanted to retrieve the data from more than one table( multiple tables) then JOINS operations help to get results.
- Inner join :
shows the common values between two tables. For performing these operations tables should be interconnected to each other. INNER JOINS return all rows from multiple tables where the join condition is met.
Syntax
SELECT columns
FROM table1 as t1
INNER JOIN table2 as t2
ON t1.column = t2.column;
Example:
Example: Display product code , productname and product description
SELECT
t1.productCode,
t1.productName,
t2.textDescription
FROM
products t1
INNER JOIN productlines t2
ON t1.productline = t2.productline;
INNER JOIN with GROUP BY clause example
Table: orderdetails
SELECT
t1.orderNumber,
t1.status,
SUM(quantityOrdered * priceEach) total
FROM
orders t1
INNER JOIN orderdetails t2
ON t1.orderNumber = t2.orderNumber
GROUP BY orderNumber;
Method 2: Using keyword
SELECT
orderNumber,
status,
SUM(quantityOrdered * priceEach) total
FROM
orders o
INNER JOIN orderdetails od USING (orderNumber)
group by orderNumber
Table: Orders
Query Results:
+-------------+------------+----------+
| orderNumber | status | total |
+-------------+------------+----------+
| 10100 | Shipped | 10223.83 |
| 10101 | Shipped | 10549.01 |
| 10102 | Shipped | 5494.78 |
| 10103 | Shipped | 50218.95 |
| 10104 | Shipped | 40206.20 |
| 10105 | Shipped | 53959.21 |
| 10106 | Shipped | 52151.81 |
| 10107 | Shipped | 22292.62 |
| 10108 | Shipped | 51001.22 |
| 10109 | Shipped | 25833.14 |
2. Left join :
The left join selects data starting from the left table. For each row in the left table, the left join compares with every row in the right table.
If the values in the two rows satisfy the join condition, the left join clause creates a new row whose columns contain all columns of the rows in both tables and includes this row in the result set.
If the values in the two rows are not matched, the left join clause still creates a new row whose columns contain columns of the row in the left table and NULL for columns of the row in the right table.
Syntax
SELECT columns
FROM table1 as t1
left JOIN table2 as t2
ON t1.column = t2.column;
Example:
SELECT
c.customerNumber,
customerName,
orderNumber,
status
FROM
customers c
LEFT JOIN orders o
ON c.customerNumber = o.customerNumber;
Customer who has not yet made any order
SELECT
c.customerNumber,
c.customerName,
o.orderNumber,
o.status
FROM
customers c
LEFT JOIN orders o
ON c.customerNumber = o.customerNumber
WHERE
orderNumber IS NULL;
=>SQL Query Result
=> Sql Query Result
3. Right Join
The RIGHT JOIN starts selecting data from the right table (t2). It matches each row from the right table with every row from the left table. If both rows cause the join condition to evaluate to TRUE, the RIGHT JOIN combines columns of these rows into a new row and includes this new row in the result set.
If a row from the right table does not have a matching row from the left table, the RIGHT JOIN combines columns of rows from the right table with NULL values for all columns of the right table into a new row and includes this row in the result set.
In other words, the RIGHT JOIN returns all rows from the right table regardless of having matching rows from the left table or not.
Syntax:
SELECT
select_list
FROM t1
RIGHT JOIN t2 ON
join_condition;
Example :
SELECT
e.employeeNumber,
c.customerNumber
FROM
customers c
RIGHT JOIN employees e
ON c.salesRepEmployeeNumber = e.employeeNumber
ORDER BY
e.employeeNumber;
=>Sql Results
market_fact_full
cust_dimen
Window Function:
- Rank() function :
Limit help us to get top N or bottom N values but what about if we want to retrieve mid-ranked values. rank() function help to get that any rank on table based on passed condition .
If I want to see a single customer rank :
select ord_id, round(sales) as Rounded_Sales, Customer_Name, rank() over
(order by sales desc) as Sales_Amout_Rank
from market_fact_full
inner join cust_dimen using (cust_id)
where customer_name='AARON BERGMAN'
For Example finding the rank of customers based on sales amount value
select ord_id, round(sales) as Rounded_Sales, Customer_Name, rank() over
(order by sales desc) as Sales_Amout_Rank
from market_fact_full
inner join cust_dimen using (cust_id)
Alternate way:
with rank_info as
(
select ord_id, round(sales) as Rounded_Sales, Customer_Name, rank() over
(order by sales desc) as Sales_Amount_Rank
from market_fact_full
inner join cust_dimen using (cust_id)
where customer_name='AARON BERGMAN'
)
select ord_id, rounded_sales, customer_name, sales_amount_rank
from rank_info
where sales_Amount_Rank<10;
