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
finding total sales
(Different between count(*) and count(Sales) is that: count(*) count null values while count(sales) don't count any null values)
select from where group by having order by
Count the customer City wise
Count the customer City wise and segmentwise
Find the customer order which has been sold at a loss
Find total number of customer from Bihar in each segment
Display customer name
Display customer name in descending way
Display Three most ordered product
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;
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
Display first character for first name and last name in upper case
Monthwise order shipped
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
Print the customer names starting with A, B, C, D and ending with er.
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
Print the order number of most valuable order by sales
Print most customer customer
Print three most common products
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.
Example: Display product code , productname and product description
INNER JOIN with GROUP BY clause example
Method 2: Using keyword
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.
Customer who has not yet made any order
=>SQL Query Result
=> Sql Query Result
3. Right Join
- 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 :
For Example finding the rank of customers based on sales amount value