use dummy
create table customer(
customer_id varchar(100),
customer_name varchar(100),
customer_address varchar(100),
city varchar(100),
state varchar(100),
zip_code int)
alter table customer change zip_code zip_code varchar(100)
insert into customer values(1,"hello","chenai","chaennai","tn","635451",12345678);
insert into customer
values(1,"hello1","chenai1","chaennai1","tn1","635451",12345677);
insert into customer
values(1,"hello2","chenai2","chaennai2","tn1","6356771",4567678);
alter table customer change customer_address address varchar(100)
alter table customer add column mobile_number int
delete from customer where mobile_number is null
update customer set mobile_number=32455656 where customer_name="hello1"
alter table customer drop column mobile_number
delete from customer where zip_code=635451
select * from customer where customer_id>2;
select * from customer where state="tn1"
truncate table customer
select * from customer
order by
create table student_mark(
S_no int,
name varchar(100),
sub varchar(100),
deparment varchar(100))
alter table student_mark change sub sub_mark int
insert into student_mark values(1,"nikkal",99,"cse");
insert into student_mark values(2,"nikkal",90,"ece");
insert into student_mark values(3,"nikkal",91,"csd");
select * from student_mark
select sum(sub_mark)as total_mark from student_mark
select max(sub_mark) as max_mark from student_mark
select min(sub_mark) as min_mark from student_mark
select avg(sub_mark) as average from student_mark
select count(sub_mark) as no_of_count from student_mark
select * from student_mark where deparment="cse"
select name from student_mark where deparment="cse"
select count(name) from student_mark where deparment="cse"
-- orderby
select name,sub_mark from student_mark order by sub_mark desc
-- group by
select avg(sub_mark),deparment from student_mark group by deparment
select deparment from student_mark group by deparment
group by and order by and having
create table employee(
employee_id int,
employee_name varchar(100),
employee_lastname varchar(100),
department varchar(100),
salary int)
insert into employee(employee_id,employee_name,employee_lastname,department,salary)
values
(1,"john","doe","HR",55000),
(2,"jane","SMITH","IT",60000),
(3,"BOB","JOHNSON","IT",62000),
(4,"JOHNSON","WILLIAMS","HR",54000),
(5,"EVA","DAVIS","FINANCE",58000),
(6,"MIKE","BROWN","FINANCE",59000);
SELECT * FROM employee
select * from employee order by employee_lastname asc
select count(employee_name),department from employee group by department
select * from employee where department="IT" order by salary desc
select count(*),department from employee group by department
select avg(salary),department from employee group by department order by department
asc
select avg(salary),department from employee group by department order by
avg(salary) desc limit 1
select avg(salary),department from employee group by department having
avg(salary)<60000
select avg(salary),department from employee group by department having
avg(salary)>55000 or count(*)>2
exercise
use dummy
create table student1(
id int,
name varchar(100),
age int)
alter table student1 add column grade varchar(100)
alter table student1 change grade finalgrade varchar(100)
INSERT INTO student1 (id, name, age, finalgrade) VALUES
(101, 'John Doe', 20, 'A'),
(102, 'Jane Smith', 22, 'B'),
(103, 'Bob Johnson', 19, 'C'),
(104, 'Alice Brown', 21, 'A'),
(105, 'Charlie Davis', 20, 'B'),
(106, 'Emma Wilson', 23, 'A'),
(107, 'Michael Lee', 20, 'C'),
(108, 'Olivia Moore', 19, 'B'),
(109, 'William Turner', 21, 'A'),
(110, 'Sophia Rodriguez', 22, 'C');
update student1 set age=21 where id=101
delete from student1 where id=101
select * from student1 where age>=19
select * from student1 where name="william turner" or name="alice brown"
select * from student1 where finalgrade in ("a","b") and age>=20
select * from student1 where age>=18 or age<=25
select * from student1 where age<=18
select * from student1 where finalgrade>='c'
select count(*) from student1
select avg(age) from student1
select sum(age) from student1 where finalgrade='a' or finalgrade='b'
select count(*),finalgrade from student1 group by finalgrade
select count(*),finalgrade from student1 group by finalgrade order by finalgrade
select avg(age) from student1 group by age
select count(*),finalgrade from student1 group by finalgrade order by count(*) desc
limit 1
select avg(age),finalgrade from student1 group by finalgrade having avg(age)>=20
select count(*),finalgrade from student1 group by finalgrade having count(*)<=3
select avg(age),finalgrade from student1 group by finalgrade having avg(age)>=20
and avg(age)<=25
or--
select avg(age),finalgrade from student1 group by finalgrade having avg(age)
between 20 and 25
select * from student1 order by age
select * from student1 where finalgrade='a' or finalgrade='b' order by age desc
select * from student1 order by finalgrade
select * from student1
foreign key use dummy
create table employee(
emp_id int auto_increment primary key ,
emp_name varchar(100) not null,
jod_desc varchar(100) default 'no name',
salary int,
pan varchar(100) unique,
check(salary>1000),
branch_id int
);
alter table employee add constraint fr_branch foreign key(branch_id) references
branch(branch_id)
create table branch(
branch_id int auto_increment primary key,
branch_name varchar(100),
address varchar(100));