-- creates db
create database student;
create database sales;
-- uses the db which is mentioned
use student;
-- drops db
drop database student;
-- creates table
create table student( id int, name varchar(20), gpa decimal(3,2));
-- description of a table
describe student;
-- to alter/add coloumn in table
alter table student add column department varchar(20);
-- to delete a coloumn in table
alter table student drop column department;
insert:
-- inserting values in table
insert into student values(1,"abc",3.5);
insert into student values(2,"def",4.6),(3,"ghj",6.7);
-- inserting specifically,remain value shows null
insert into student (id,name) values(4,"xyz");
select:
-- selecting all tables
select *from student;
-- selecting specified tables
select id,name from student;
excersie 1:
-- check available db
show databases;
-- use specified db
use sales;
-- create table
create table employee(emp_id int, ename varchar(20), jobdesc varchar(20), salary int);
-- insert values
insert into employee values(1,"ram","admin",10000);
insert into employee values(2,"harini","manager",25000);
insert into employee values(3,"george","sales",20000);
insert into employee values(4,"ramya","sales",13000);
insert into employee values(5,"meena","hr",20000);
insert into employee values(6,"ashok","manager",30000);
insert into employee values(7,"abdul","hr",20000);
insert into employee values(8,"ramya","engineer",10000);
insert into employee values(9,"raghu","ceo",80000);
insert into employee values(10,"arvind","manager",28000);
insert into employee values(11,"akhshay","engineer",10000);
insert into employee values(13,"abinaya","engineer",21000);
-- to display all data in table
select *from employee;
-- where clause and conditions
-- all coloumns
select *from employee
where salary>20000;
-- specified column
select ename,jobdesc,salary from employee
where salary<20000;
-- or operation
select *from employee
where jobdesc="engineer" or jobdesc="hr";
-- alter way( using in )
select *from employee
where jobdesc in("hr","engineer");
-- (using not in)
-- removes jobdesc of ceo,hr,sale and shows remaining
select *from employee
where jobdesc not in("ceo","hr","sales");
-- and operation
select ename,salary from employee
where salary<25000 and jobdesc="engineer";
-- range value ( between)
select *from employee
where salary between 20000 and 30000;
-- limit values (till 3)
select *from employee
limit 3;
-- like keyword ( used for pattern filtering)
-- wildcards (percent) % means zero or more character
-- wildcards (underscore) _ means one character
select *from employee
where ename like 'a%'; -- return name starts with 'a'
select *from employee
where ename not like 'a%'; -- return name that does not start with 'a'
select *from employee
where ename like 'a%a'; -- name that starts and ends with 'a'
select *from employee
where ename like '%i%' ; -- name with i in center
select *from employee
where ename like '__i%' ; -- name with third letter i ( use _ )
--update
update employee
set jobdesc="Analyst"
where jobdesc="engineer";
-- delete queries
delete from employee
where emp_id=12;
-- distinct and order by
use sales;
select distinct jobdesc
from employee;
-- order by
select *from employee
order by ename;
-- order by in descending
select *from employee
order by salary desc;
-- order by two columns
select *from employee
order by salary,ename;
-- using orderby with where clause
select *from employee
where jobdesc="analyst"
order by salary;
select *from employee
order by jobdesc;
-- order by custom [ordering by our wish ]
select *from employee
order by(case jobdesc
when 'ceo' then 1
when 'manager' then 2
when 'hr' then 3
when 'analsyt' then 4
else 100 end
);
-- functions
-- performs a specific task
use sales;
-- count
select count(*)
from employee;
-- to change the name of column
select count(*) total
from employee;
select count(*) no_of_manager
from employee
where jobdesc='manager';
-- average
select avg(salary)
from employee;
-- max
select max(salary)
from employee;
-- min
select min(salary)
from employee
where jobdesc='manager';
-- sum
select sum(salary)
from employee
where jobdesc='analyst';
-- string functions
-- uppercase
select ucase(ename),salary
from employee;
select ucase(ename) name,salary
from employee;
-- character count
select ename,char_length(ename)
from employee;
-- concatenation
select ename,concat('Rs ',salary)
from employee;
select ename,concat('Rs ',salary) salary_rs
from employee;
-- format
select ename,concat('Rs ',format(salary,0)) salary_rs
from employee;
-- Date and date functions
alter table employee add column hire_date date;
update employee
set hire_date="2010-05-16";
select *from employee;
update employee
set hire_date="2015-03-14"
where jobdesc="manager";
-- current date and time
select now();
-- current date only
select curdate();
-- changing date format
select date_format(curdate(),"%d/%m/%y"); -- y 24
select date_format(curdate(),"%d/%m/%Y"); -- Y 2024
select date_format(curdate(),"%d/%m/%Y") as date; -- column name date
-- difference between two date
select datediff(curdate(),"2021/05/23");
select datediff(curdate(),"2021/05/23") as days; -- column name days
-- one week later , one month or day later
select date_add(curdate(),interval 1 day) as 'after one day';
select date_add(curdate(),interval 1 week) as 'after one week';
select date_add(curdate(),interval 1 month) as 'after one month';
select date_add(curdate(),interval 1 year) as 'after one year';
use sales;
-- group by
select jobdesc,avg(salary)
from employee
group by jobdesc;
select jobdesc,count(emp_id)
from employee
group by jobdesc;
-- where filters rows
-- having filters group by
select jobdesc,count(emp_id)
from employee
group by jobdesc
having count(emp_id)>1 ;
select jobdesc,count(emp_id)
from employee
group by jobdesc
having count(emp_id)>1
order by jobdesc ;
-- i need salary count < 15 k
select jobdesc,count(emp_id)
from employee
where salary>15000
group by jobdesc
having count(emp_id)>1
order by jobdesc ;
-- constraints
-- not null ( if value is forget to insert,it does not shows null value ex: ename
-- default ( does not show null value it shows default value what we assigned
-- unique ( shows unique value ex: pan number)
-- check ( used for special condition)
-- auto increment ( even if empid not inserted it shows value from 1 to ...alter
use sales;
create table employee1(
empid int primary key,
ename varchar(30),
jobdesc varchar(30),
salary int,
pan int);
drop table employee1;
create table employee1(
empid int primary key auto_increment,
ename varchar(30) not null,
jobdesc varchar(30) default 'unassigned',
salary int,
pan varchar(10) unique,
check (salary>15000)
);
insert into employee1(ename,salary) values ("ram",18000);
select *from employee1;
drop table employee1;
-- how to add constraints in the already existing table
create table employee1(
empid int primary key,
ename varchar(30),
jobdesc varchar(30),
salary int,
pan int);
-- add not null constraint
alter table employee1
modify ename varchar(30) not null;
-- drop not null constraint
alter table employee1
modify ename varchar(30);
-- add default constraint
alter table employee1
alter jobdesc set default 'unassigned';
-- drop default constraint
alter table employee1
alter jobdesc drop default;
