Queries and Types.txt

-- Finding Year,date,month from date format in MySQL --

select *, substr(birth_day,1,4) as birth_year from employee;
select first_name , extract(day from birth_day) from employee;
select first_name , extract(month from birth_day) from employee;
select first_name , extract(year from birth_day) from employee;


-- Current time finding --

select first_name ,current_timestamp() from employee;
select first_name ,current_date() from employee;
select first_name ,current_time() from employee;

-- Date , time finding with interval --

select first_name, current_date + interval '3' day from employee;
select first_name, current_time + interval '3' hour from employee;
select first_name, current_time + interval '3' minute from employee;
select first_name, current_time + interval '3' minute_second from employee;

-- Age Calculation --

select first_name, extract(year from current_date) - extract(year from birth_day)  as age from employee;


-- Concat method --

SELECT * , concat(first_name,' ',last_name) as fname FROM companydatabase.employee;

--Null Values Handling --

-- Fill null values with NA--
SELECT Name , coalesce(Color,'NA') FROM adventureworks.product;



-- MySQL Constraints --

-- Database creation --

CREATE SCHEMA `mahindra` ;
use mahindra;
create table mahindra.employee_details(
id int not null unique ,
first_name varchar(20) not null,
last_name varchar(20) not null,
salary int not null,
dob date not null,
department varchar(50) not null default "Mahindra Holidays",
primary key(id)
);

-- Adding Constraints --
alter table employee_details
add constraint unique(first_name);

-- Single row data insertion --
insert into employee_details values(45,'Chandan','Sengupta',65000,'1988-01-11','data');


-- Case When Statement --

select first_name ,
case
	when super_id=100 then 'Branch manager'
	when super_id is null then ' Corporate manager'
	else 'Associate'
end as 'designation'
from employee;


-- Distinct Count --
select count(distinct super_id) from employee;