source <filename>.sql;

create database cheatsheet;

use cheatsheet;

show databases;

create table employee ( employee_id int primary key, -- Setting primary key(1st method) first_name varchar(50), last_name varchar(50), dept_number int, age int, salary real ); create table department ( dept_number int, dept_name varchar(50), dept_location varchar(50), emp_id int, primary key(dept_number) -- Setting primary key(2nd method) );

show tables;

describe employee; desc employee; show columns in employee;

rename table employee to employee_table; alter table employee_table rename to employee;

alter table employee change column employee_id emp_id int;

alter table employee change column first_name first_name varchar(50) not null;

alter table employee add column salary real;

alter table employee drop column salary;

alter table employee modify column salary int;

truncate employee;

drop table department;

drop database cheatsheet;

insert into employee (employee_id, first_name, last_name, dept_number, age, salary) values (1, "Anurag", "Peddi", 1, 20, 93425.63); insert into employee values (2, "Anuhya", "Peddi", 2, 20, 83425.63);

insert into employee (employee_id, first_name) values (3, "Vageesh");

update employee set salary = 1.1 * salary;

update employee set salary = 1.2 * salary where employee_id = 1;

delete from employee where employee_id = 2;

delete from employee;

set foreign_key_checks = 1;

set foreign_key_checks = 0;

select * from employee;

select employee_id, first_name from employee;

select employee_id, first_name from employee where age > 25;

create table table_name like old_table;

create table table_name select * from old_table;

create view personal_info as select first_name, last_name, age from employees;

select * from personal_info;

update personal_info set salary = 1.1 * salary;

delete from personal_info where age < 40;

drop view personal_info;

select e.fname, p.pname from employees as e inner join project as p on e.eid = p.eid;

select e.fname, p.pname from employees as e left outer join project as p on e.eid = p.eid union select e.fname, p.pname from employees as e right outer join project as p on e.eid = p.eid;

select e.fname, p.pname from employees as e left outer join project as p on e.eid = p.eid;

select e.fname, p.pname from employees as e right outer join project as p on e.eid = p.eid;

select sum(population) from city group by population;

select avg(population) from city group by population;

select district, count(district) from city group by district;

select max(population) from city group by population;

select min(population) from city group by population;

select stddev(population) from city group by population;

select group_concat(population) from city group by population;

create procedure display_dbs() show databases;

call display_dbs();

start transaction;

savepoint sv_pt;

delete from city; -- changing data in table

rollback to sv_pt;

release savepoint sv_pt;

commit;