Scribd
Upload
34 views8 pages

SQL Notes

Uploaded by

4018 Dhanalakshmi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
34 views8 pages

SQL Notes

Uploaded by

4018 Dhanalakshmi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 8

-- 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;

You might also like