MySQL Practical Commands
Q1. Introduction to MySQL Environment
create database shivanshu;
use shivanshu;
show databases;
show tables;


Q2. Create table with and without constraint
Without Constraint
create table students(Rollno int, fname varchar(20), lname varchar(20), age int, class varchar(20), phoneno int, city varchar(20));
desc students;
insert into students values(01,'shivangi','shetty',16,'SYCOM',124578365,'pune');
insert into students values(02,'manish','shivmat',17,'SYCS',124578325,'mumbai');
insert into students values(03,'manish','mehata',18,'SYCS',124578324,'mumbai');
insert into students values(04,'manisha','rani',18,'SYCS',124578347,'utterpradesh');
insert into students values(05,'ishant','rawat',19,'SYBSC-IT',1245458347,'utterpradesh');
insert into students values(06,'gavari','shawant',19,'SYBSC-IT',1145458347,'utterakhand');
insert into students values(07,'sonam','gupta',20,'SYBMS',1145425347,'mumbai');
insert into students values(08,'shashank','gupta',20,'SYBMS',1145925347,'mumbai');
insert into students values(09,'riyaz','khan',20,'SYBMS',1125925347,'gujrat');
insert into students values(10,'shanawaz','khan',20,'SYBMS',1225925347,'gujrat');
select * from students;
With Constraint
create table empty(EmpID int primary key, Fname varchar(20) not null, Lname varchar(20) unique, city varchar(20), age int, salary int not null);
desc empty;
insert into empty values (101,'atharv','patil','mumbai',19,40000), (102,'heer','suthar','Amdhabad',20,45000), (103,'nehal','vani','surat',22,44000), (104,'nimit','thakur','ujjain',25,45000), (105,'pushkarsingh','dhami','banglor',24,50000), (106,'roshani','kumbhar','selong',26,51000), (107,'inshant','rawat','masuri',23,10000), (108,'ayush','pradhan','puri',21,16000), (109,'sohil','khan','hydrabad',31,155000), (200,'sita','mithila','varanasi',32,57000);
select * from empty;


Q3. Altering and Dropping tables
alter table students add salary int;
desc students;
alter table students drop column salary;
alter table students modify Rollno int;
rename table students to systudent;
drop table stydent;
update systudent set salary= 100000 where Rollno=01;
update systudent set salary= 100000 where Rollno=02;
update systudent set salary= 10000 where Rollno=03;
update systudent set salary= 20000 where Rollno=04;
update systudent set salary= 20000 where Rollno=05;
delete from systudent where salary is null;


Q4. Basic select Query
select * from systudent;
select Rollno, concat(fname,' ',lname) as name, city as address from systudent;
select distinct city from systudent;


Q5. Conditional Query
select * from systudent where city="mumbai";
select * from systudent where city="solapur";
select * from systudent where age>15;
select * from systudent where age<20;
select * from systudent where age=18;
select * from systudent where age between 15 AND 20;
select * from systudent where age=15 OR age=20;
select * from systudent where not age <18;
select * from systudent where fname like '%a';
select * from systudent where fname like 'r%';
select * from systudent where fname like '%a%';
select * from systudent where fname not like '%a%';
Select age from systudent order by age;
Select age from systudent order by age desc;


Q6. Aggregate Function and Grouping
alter table students add salary int;
select * from systudent;
select max(salary) as max from systudent;
select min(salary) as min from systudent;
select avg(salary) as avrg from systudent;
select count(salary) as count from systudent;
select count(distinct salary) as count from systudent;
select sum(distinct salary) as sum_count from systudent;
Select age from systudent group by age;
select salary from systudent GROUP BY salary HAVING (salary)>10000;


MySQL Practical Commands (Advanced)
Practical no 7: Working with Joins
select * from employee;
create view employee_view AS select * from employee where dept_id = 10;
select * from employee_view;
select * from department;
rename table employee to employee1;
select * from employee1;
select e.emp_id, e.dept_id, d.dept_name from employee e left outer join department d on d.dept_id=e.dept_id;
select e.emp_id, e.dept_id, d.dept_name from employee1 e RIGHT OUTER JOIN department d ON e.dept_id=d.dept_id;
select e.emp_id, e.dept_id, d.dept_name from employee1 e CROSS JOIN department d ON e.dept_id=d.dept_id;
select e.emp_id, e.dept_id, d.dept_name from employee1 e inner JOIN department d ON e.dept_id=d.dept_id;
select e.emp_id, e.last_name, e.dept_id, d.dept_name FROM employee1 e left join department d ON e.dept_id=d.dept_id union select e.emp_id, e.last_name, e.dept_id, d.dept_name FROM employee1 e RIGHT JOIN department d ON e.dept_id=d.dept_id;



Practical no 8: Subqueries
select last_name, salary from employee1 where salary<=(select salary from employee1 where last_name='prajapati');


Practical no 9: Complex Queries (ANY, ALL, IN, EXISTS)
select emp_id, last_name, salary from employee1 where salary < any (select salary from employee1 where job_id ='MAN');
select emp_id, last_name, salary from employee1 where salary < all (select salary from employee1 where job_id ='MAN');
select emp_id, last_name, salary from employee1 where salary not in (select salary from employee1 where job_id ='MAN');
SELECT * FROM employee WHERE EXISTS (SELECT empID FROM employee WHERE empID = 141);
SELECT * FROM employee where NOT EXISTS (select empID from employee WHERE empID = 141);


Practical no 10: Views Manipulation
create or replace view employee_view as select * from employee1 where dept_id = 20 and salary > 3000;
select * from employee_view;
CREATE OR REPLACE VIEW employee_view AS SELECT * FROM employee1 WHERE deptID = 20 AND salary > 2000;
select * from employee_view;
drop view employee_view;
drop view employee_view RESTRICT;
show tables;
drop view employee_view CASCADE;
show tables;


Practical no 11: Functions with Group By and Having
select * from employee1 WHERE salary > 2000 GROUP BY deptID=10;
select * from employee1 GROUP BY deptID HAVING deptID > 10;
SELECT deptID, salary, COUNT(jabID) AS 'total employee' FROM employee1 GROUP BY deptID, salary HAVING COUNT(jabID) < 2;
select job_id, sum(salary) from employee1 group by job_id;
select job_id, max(salary) from employee1 group by job_id;
select job_id, min(salary) from employee1 group by job_id;
select job_id, count(salary) from employee1 group by job_id;


Practical no 12: SQL String, Math, and Date Functions
select LOWER('SHIVANSHU');
select UPPER('SHIVANSHU');
select LENGTH('SHIVANSHU');
SELECT replace('I Like mysql','mysql','java');
select trim('SHIVANSHU');
select trim('SHIVA NSHU');
SELECT ABS (-10);
select round(12.34,2);
select round(12.3456,2);
select CEIL(4.38);
select floor(89);
select sqrt(16);
select pow(1,6);
select pow(2,3);
select mod(20,3);
select curdate();
select now();
select year('2025-11-28');
select day('2025-11-28');
select month('2025-11-28');
select week('2025-11-28');
select DATEDIFF('2025-11-28','2025-2-12');
select DATEDIFF('2025-12-11','2025-11-28');
select DATE_ADD('2025-11-28', INTERVAL 10 DAY);
select DATE_ADD('2025-11-28', INTERVAL 10 year);
select DATE_SUB('2025-11-28', INTERVAL 10 year);
select DATE_SUB('2025-11-28', INTERVAL 10 DAY);
select DATE_FORMAT('2025-11-28','%W,%M,%D,%Y');


Practical no 13: Transaction Control (Commit and Rollback)
SET autocommit=0;
select * from players;
commit;
delete from players where ID=006;
select * from players;
rollback;
select * from players;