SQL-Basics 4(Time&Date Functions)

 create database today;

-----How to check which databases are used right now------

select database();

use today;

CREATE TABLE sales (

order_id VARCHAR(15) NOT NULL, 

order_date VARCHAR(15) NOT NULL, 

ship_date VARCHAR(15) NOT NULL, 

ship_mode VARCHAR(14) NOT NULL, 

customer_name VARCHAR(22) NOT NULL, 

segment VARCHAR(11) NOT NULL, 

state VARCHAR(36) NOT NULL, 

country VARCHAR(32) NOT NULL, 

market VARCHAR(6) NOT NULL, 

region VARCHAR(14) NOT NULL, 

product_id VARCHAR(16) NOT NULL, 

category VARCHAR(15) NOT NULL, 

sub_category VARCHAR(11) NOT NULL, 

product_name VARCHAR(127) NOT NULL, 

sales DECIMAL(38, 0) NOT NULL, 

quantity DECIMAL(38, 0) NOT NULL, 

discount DECIMAL(38, 3) NOT NULL, 

profit DECIMAL(38, 8) NOT NULL, 

shipping_cost DECIMAL(38, 2) NOT NULL, 

order_priority VARCHAR(8) NOT NULL, 

`year` DECIMAL(38, 0) NOT NULL);

select * from sales;

select count(*) from sales;

-------To check the sql_mode ------------

 select @@sql_mode;

 

 -------How to load data from the file into a table-------

 

 LOAD DATA INFILE

 'D:/sales_data_final.csv'

 into table sales

 FIELDS terminated by ','

 enclosed by '"'

 lines terminated by '\n'

 IGNORE 1 ROWS; 

 ---------How to change the parameter in SQL_MODE---------

 SET session SQL_MODE=''

  

 LOAD DATA INFILE

 'D:/sales_data_final.csv'

 into table sales

 FIELDS terminated by ','

 enclosed by '"'

 lines terminated by '\n'

 IGNORE 1 ROWS; 

 

 select * from sales limit 5;

 

select count(*) from sales;

-----------How to convert varchar datatype to date datatype Format---------

select str_to_date(order_date,'%m/%d/%Y') from sales;

select str_to_date(order_date,'%m/%d/%Y') from sales group by order_date;

select * from sales group by ship_date limit 5;

--------How to ADD new column-------------

alter table sales

add column  order_date_new date after order_date;

alter table sales

add column  ship_date_new date after ship_date;

------How to fetch the record with limit--------

select * from sales limit 5;

desc sales

-------How to Update the column ---------

 update sales

set order_date_new=str_to_date(order_date,'%m/%d/%Y');

update sales 

set ship_date_new=str_to_date(ship_date,'%m/%d/%Y');

select * from sales;

-------How to use the where clause--------

select * from sales where order_date='1/1/2011';

select count(*) from sales where order_date='1/1/2011';

select * from sales where order_date<'1/9/2011';

select count(*) from sales where order_date<'1/9/2011';

select * from sales where ship_date='1/6/2011';

select count(*) from sales where ship_date='1/6/2011';

select * from sales where ship_date>'1/6/2011'; 

select count(*) from sales where ship_date>'1/6/2011'; 

select * from sales where ship_mode='standard class';

select count(*) from sales where ship_mode='standard class';

select count(*) from sales where ship_mode='same day';

select * from sales where ship_mode='same day'; 

 select * from sales where state='New South Wales';   

 select count(*) from sales where state='New South Wales'; 

 select * from sales where order_priority='High';  

select count(*) from sales where order_priority='High'; 

 --------How to use between / and operators -----------

select * from sales where ship_date_new between '2011-01-06' and '2012-12-05';

select count(*) from sales where ship_date_new between '2011-01-06' and '2012-12-05'; 

select * from sales where ship_mode between 'standard Class' and 'same day';

Note:-

-------

-----This query is wrong(Bcz it should have proper condition )

select * from sales where sales between '408' and '667';

select count(*)from sales where sales between '408' and '667';

select * from sales where quantity between '1' and '5';

select count(*) from sales where quantity between '1' and '5';

select * from sales where quantity between '1' and '2';

select count(*) from sales where quantity between '1' and '2';

--------Default Functions useful for Time,Year,Date,Day ----------

select now();   (it will give current date along with time)

select curdate();(it will give only current date)

select curtime();(it will give only current time)

Note:-

-----

---->For the above stmnts is equal(=) is not working bcz what we are giving the below intervals we have many dates we will get.

----------------DATE_SUB(It is as function)-------------------------------

----->"date_sub" is a function it is used to subtract the dates.

Below are the Examples for DATE_SUB

----------------------------------------------------

select count(*) from sales where ship_date_new<date_sub(now(),interval 1 week) 

select count(*) from sales where ship_date_new<date_sub(now(),interval 2 year) 

select count(*) from sales where ship_date_new<date_sub(now(),interval 6 year)

select count(*) from sales where ship_date_new<date_sub(now(),interval 7 year)

select count(*) from sales where ship_date_new<date_sub(now(),interval 8 year)  

select count(*) from sales where ship_date_new<date_sub(now(),interval 10 year) 

select count(*) from sales where ship_date_new<date_sub(now(),interval 3500 day) 

select ship_date from sales group by ship_date; 

select ship_date from sales order by ship_date;

----------------date_sub function is used to subtract date------------------------

select date_sub(now(),interval 1 week);

select date_sub(now(),interval 1 year);

select date_sub(now(),interval 1 month);

select date_sub(now(),interval 2 month);

select date_sub(now(),interval 2 week);

select date_sub(now(),interval 2 year);

select date_sub(now(),interval 1 day);

select date_sub(now(),interval 4 day);

select date_sub(now(),interval 25 day);

select year(now());

select month(now());

select day(now());

select time(now());

select dayname(now());

select dayname(date_sub(now(),interval 1 day));

select year(date_sub(now(),interval 1 day));

select month(date_sub(now(),interval 1 month));

select day(date_sub(now(),interval 1 year));

select date_sub(now(),interval 256 day);

select dayname('2021-12-28 18:34:55');

select date('2021-12-28 18:34:55');

select month('2021-12-28 18:34:55');

select year('2021-12-28 18:34:55');

-------------How to add flag column-------------------

select * from sales limit 5;

alter table sales add column flag date after order_id;

-----------How to update the column--------------

update sales set flag=now();

-----------How to use modify column------------

alter table sales modify `year` date;

desc sales;

-------------How to add new columns------------------

alter table sales add column year_new date after ship_date_new;

alter table sales add column month_new date before ship_mode

#Here I tried 'before' keyword but is not working.

alter table sales before ship_mode add column month_new date 

#Here I tried before keyword but is not working.

alter table sales ship_mode before add column month_new date

 #Here I tried before keyword but is not working.

alter table sales add column month_new date after year_new;

alter table sales add column day_new date after month_new;

select * from sales limit 5;

------How to fetch the data with year,day,month seperately------

select year(ship_date_new) from sales;

select count(year(ship_date_new)) from sales;

select day(ship_date_new) from sales;

select count(day(ship_date_new)) from sales;

select month(ship_date_new) from sales;

select count(month(ship_date_new)) from sales;

------How to update only year,month,day to the columns(year_new,month_new,day_new) -----

update sales set year_new=year(ship_date_new);

update sales set month_new=month(ship_date_new);

update sales set day_new=day(ship_date_new);

#Note:-

---->While am updating the above column am getting the null values for all 3 columns,Bcz I have mentioned data type is date ----

---->But am updating the values with a single integer values,To resolve this we need to modify the data types with int data type --- \

-----How to modify the cloumns for changing the data types ------

syntax:-

alter table <table name> modify column <column name> <datatype>;

alter table sales modify column year_new int;

alter table sales modify column month_new int;

alter table sales modify column day_new int;

select * from sales limit 5;

------ Again I try  to update only year,month,day to the columns(year_new,month_new,day_new) -----

update sales set year_new=year(ship_date_new);

update sales set month_new=month(ship_date_new);

update sales set day_new=day(ship_date_new);

------How to use order by(it gives the result as per order of column),group by(It group the all the duplicates like one) -------

select * from sales order by year_new; ----it give the result ascending order(bottom to top)------

select * from sales order by year_new desc;-----desc is used for descending order(top to bottom)-----

select year_new from sales order by year_new;

select year_new from sales order by year_new desc;

select * from sales group by year_new;

select year_new from sales group by year_new;

select * from sales group by day_new;

select * from sales group by month_new limit 5;

select * from sales limit 5;

---------How to use avg(),group by at the same time-------

select * from sales limit 5;

select avg(sales) from sales;

select avg(sales) from sales group by year_new;

select avg(sales),year_new from sales group by year_new;

select avg(sales) from sales group by month_new;

select avg(sales),month_new from sales group by month_new;

---------How to use sum(),group by at the same time-------

select sum(sales) from sales group by year_new;

select year_new,sum(sales) from sales group by year_new;

select month_new,sum(sales) from sales group by month_new;

---------How to use multiple columns with  group by--------------

select year_new,month_new,sum(sales) from sales group by year_new,month_new;

select sum(quantity) from sales group by year_new;

select year_new,sum(quantity) from sales group by year_new;

select year_new,month_new,sum(quantity) from sales group by year_new,month_new;

select year_new,month_new,day_new,sum(quantity) from sales group by year_new,month_new,day_new;

---------How to use min(),group by at the same time-------

select year_new,min(sales) from sales group by year_new;

select year_new,day_new, min(sales) from sales group by year_new,day_new;

---------How to use max(),group by at the same time-------

select max(sales) from sales group by year_new;

select year_new,max(sales) from sales group by year_new;

select year_new,month_new,max(sales) from sales group by year_new,month_new;

select year_new,month_new,day_new,max(sales) from sales group by year_new,month_new,day_new;

-------How to Use group by on multiple columns------

select avg(sales),month_new,year_new from sales group by month_new,year_new;

-------How to Use order by on multiple columns -------

select year_new,month_new from sales order by year_new,month_new;

-------How to Use order by with desc  on multiple columns -------

select year_new,month_new from sales order by year_new desc ,month_new desc;

select * from sales limit 5;

select (sales*discount+shipping_cost) as CTC from sales;

select count((sales*discount+shipping_cost)) as CTC from sales;

select year_new,(sales*discount+shipping_cost) as CTC from sales group by year_new;

----------How to write if else condition ------------

select if(discount>0,'yes','no') as discount_flag from sales;

select order_id,if(discount>0,'yes','no') as discount_flag from sales;

select order_id,discount,if(discount>0,'yes','no') as discount_flag from sales;

select if(sales>400,'good','Some_what_better') as About_sales from sales;

select sales,if(sales>400,'good','some_what_better') as About_Sales from sales;

select year_new,if(sales>400,'good','Some_what_better') as About_sales from sales;

select year_new,month_new,if(sales>400,'good','Some_what_better') as About_sales from sales; 

select year_new,month_new,if(sales>400,'good','Some_what_better') as About_sales from sales group by year_new;

select sales from sales group by year_new;

select year_new,if(sales>400,'good','Some_what_better') as About_sales from sales group by year_new;

select * from sales limit 5

--------update with if condition---------

alter table sales add column discount_flag date after discount ; 

----Here we are modifying the column with varchar,bcz we are inserting the data as string---

 alter table sales modify column discount_flag varchar(30);

update sales set discount_flag=if(discount>0,'YES','NO');

alter table sales add column About_Sales varchar(30) after sales;

update sales set About_Sales=if(sales>200,'Good','Need_to_improve');

select count(discount_flag) from sales group by discount_flag;

select discount_flag,count(discount_flag) from sales group by discount_flag;

select About_Sales from sales group by About_Sales;

select count(About_Sales) from sales group by About_Sales;

select About_sales,count(About_Sales) from sales group by About_Sales;

select count(*) from sales where discount>0;

select count(*) from sales where discount<0;

select count(*) from sales where sales>200;

select count(*) from sales where sales<200;