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

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

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;

Materials

Page updated

Google Sites

Report abuse

Count

Group Aggregation

Order by

Having Clause

Regular Expression

Nested Query

Join Operation

Inner join :

INNER JOIN with GROUP BY clause example

2. Left join :

3. Right Join

Window Function:

Rank() function :

Materials