Program 1
1)Create a Table & Perform the Following (i) Create (ii) Insert (iii) Select
Query:-
Create table Company (Sno int ,name varchar(10), department varchar (20), emp_id int);
Insert into company values (01,'Hari','IT',010203);
Insert into company values (02,'Bob','sales',020304);
Insert into company values (03,'Babu','Accounts',030405);
Select* from company;
2)Create a Table & Perform the Following- (i) Update (ii) Delete (iii) Alter
Create table company (emp_id int , Name varchar (10),depart varchar (10),salary int);
Insert into company values (01,'Babu','Accounts',30000);
Insert into company values (02,'Bob','sales',40000);
Insert into company values (04,'Sai','IT',45000);
i) Update:-
Update company set salary=25000 where emp_id='02';
ii) Delete:-
Delete from company where emp_id='02';
iii) Alter:-
alter table company add column ('phno int');
3) Create a table & perform the following i) union ii) union all iii)Intersection
Query:-
Table -A
CREATE TABLE TableA ( ID INT, Name VARCHAR(100));
Insert into TableA values (01,'Alice');
Insert into TableA values (02,'Bob');
Insert into TableA values (03,'Charlie');
Table -B
CREATE TABLE TableB ( ID INT, Name VARCHAR(100));
Insert into TableB values (01,'Bob');
Insert into TableB values (02,'Charlie');
Insert into TableB values (03,'David');
i) union:-
SELECT ID, Name FROM TableA UNION SELECT ID, Name FROM TableB;
ii) union all:-
SELECT ID, Name FROM TableA UNION ALL SELECT ID, Name FROM TableB;
iii) intersection:-
Select Name from TableA where Name in(select Name from TableB);
4)Create a Table: Company with its Fields & Perform the Queries:- (i) Ascending &
DescendingOrder (ii) Greater & smaller than function (iii) Ranging Function
Query:-
Create table payment (sno int, name varchar (20),pay-mode varchar (20),amount_int);
Insert into payment values (01,'Alice','g-pay',20000);
Insert into payment values (02,'Bob','paytm',25000);
Insert into payment values (03,'Charlie','BHIM',54000);
i) Ascending:-
Select amount from payment order by amount asc;
ii) Descending:-
Select amount from payment order by amount desc;
iii) Greater and Smaller than:-
Select amount from payment where payment>30000;
Select amount from payment where payment<30000;
iv) Ranging Function:-
Select * from payment where amount BETWEEN 10000 AND 30000;
5)Create a Table: Employee with its Fields & Perform the Queries: (i) Particular Employee Details
(ii) Particular Department Details (iii) Particular Resident Details
Query:-
Create table empl(Emp_id int, Emp_name varchar (20), department varchar (20), resident
varchar(10), salary int);
Insert into empl values (001,'Alice','HR','123 ELM St',55000);
Insert into empl values (002,'Bob','IT','456 oak St',45000);
Insert into empl values (003,'Charlie','Finance','123 oak St',60000);
i) Particular employee details:-
Select* from empl where Emp_id='003';
ii) Particular department details:-
Select* from empl where department='HR';
iii) Particular resident details:-
Select* from empl where resident='123 ELM St';
6)Create a Table: Student with its Fields & Perform the Queries:(i) Finding Average (ii) Greater &
Smaller than function (iii) Between Details
Query:-
Create table student( stu_id int, name varchar (10), age int, grade int);
Insert into student values (1,'Alice',20,350);
Insert into student values (2,'Bob',22,475);
Insert into student values (3,'charile',19,310);
i) Finding average:-
Select avg(grade) as avg_grade from student;
ii) Greater and Smaller than:-
Select* from student where grade>300;
Select* from student where grade<400;
iii) Between details:-
Select * from student where grade BETWEEN 300 AND 450;
7)Create a Table: Product with its Fields & Perform the Queries: (i) SUM Operation
(ii)COUNTOperation (iii) TOTAL Operation
Query:-
Create table product (p_if int, p_name varchar(20), price int, quantity int);
Insert into product values (1,'Laptop',800,10);
Insert into product values (2,'Smartphone',500,15);
Insert into product values (3,'Desk',250,5);
Insert into product values (4,'chair',150,20);
i) sum operation:-
Select sum(price* quantity) as total_revenue from product;
ii) count operation:-
Select count (quantity) as total_quantity from product;
iii) Total operation:-
Select sum (quantity) as total_quan from product;
8)Create a Table: Bank with its Fields & Perform the Queries: (i) Deposits Details
(ii)BranchDetails(iii) Percentage Calculation
Query:-
CREATE TABLE Bank (AccountID INT,AccountHolder VARCHAR(100), Branch_name INT,Balance
int);
Insert into Bank values (1,'Alice','Downtown',1500);
Insert into Bank values (2,'Bob','Uptown',2500);
Insert into Bank values (3,'Charlie','Downtown',3000);
Insert into Bank values (4,'David','Midtown',1000);
Insert into Bank values (5,'Eve','Uptown',4500);
i) Deposit details:-
Select* from Bank where Balance >0;
ii) Branch Details:-
Select Branch_name,count(*) as total_accounts,sum(Balance) as total_balance from Bank
group by Branch_name;
iii) Percentage Calculation:-
Select Branch_name ,sum(balance) as Branch_balance,(sum(Balance)/(select sum (Balance)
from Bank)*100) as percentage_balance from Bank Group by Branch_name;
9)Create a Table: Pay roll with its Fields & Perform the Queries: (i) Net Pay Operation
(ii)GrossPay Operation (iii) PF Operation
Query:-
Create table payroll (emp_id int, emp_name varchar(10), basic_salary int, gross pay int,
deduction int,ot_hour int, ot_rate int, bonus int,pf_percentage int);
Insert into payroll values (1,'Alice',30000,26000,200,8,150,100,12);
Insert into payroll values (2,'Bob',40000,35000,300,7,120,100,12);
Insert into payroll values (3,'charile',40000,36000,150,8,150,100,12);
Insert into payroll values (4,'David',40000,32000,250,8,150,100,12);
i)Net pay operation:-
Select emp_id , gross_pay, deduction (gross_pay - deduction)as net_pay from payroll;
ii)Gross pay operation:-
Select emp_id, basic_salary,ot_hour + ot_rate as overtime_pay, bonus,(basic_salary + ot_hour +
ot_rate) + bonus) as gross pay from payroll;
iii) PF operation:-
Select emp_id, gross_salary,(gross_salary * pf_percentage/100)as pf_contribution from payroll;
10)Create a table and perform the operators
Query:-
Create table employee (id int, name varchar(10), position varchar (20), salary int);
Insert into employee values (01,'Alice','Manager',75000);
Insert into employee values (02,'Bob','Developer',60000);
Insert into employee values (03,,'charile','Developer',50000);
i) select data:-
Select* from employee;
ii) update data:-
Update employee set salary=80000 where name='Bob';
iii) Delete Data:-
Delete from employee where name ='Bob';
iv) count:-
Select count (*) from employee;
v) average:-
Select avg(salary)as avg_salary from employee;
vi) Filter:-
Select* from employee where position='Developer';
11)Create a table and perform my sql functions
Query:-
Create table marks(sno int,name varchar (10), class int,marks int);
Insert into marks values (1,'Alice',10,350);
Insert into marks values(2,'Bob',12,470);
Insert into marks values (3,'charile',11,480);
Insert into marks values (4,'David',12,500);
i) select avg (marks) from marks;
ii) select count(*) from marks;
iii) select max(marks) from marks;
iv) select min(marks) from marks;
v) select sum(marks) from marks;
vi) select name, marks , marks + 5000 as new_marks from marks;
vii) select name , marks, marks -5000 as reduced _marks from marks;
vii)select name, marks , marks*1.10 as increase_marks from marks;
ix) select name, marks , marks/2 as half_ marks from marks;
12)Create a table using joins
Query:-
Employee table:-
CREATE TABLE employees (employee_id INT,employee_name VARCHAR(50),department_id int);
INSERT INTO employees VALUES(1, 'Alice', 1);
INSERT INTO employees Values (2, 'Bob', 2);
INSERT INTO employees values (3, 'Charlie', NULL);
Department table:-
CREATE TABLE departments (department_id int,department_name varchar (10));
INSERT INTO departments values (1, 'HR');
INSERT INTO departments values (2, 'Finance');
INSERT INTO departments values (3, 'Engineering');
i) INNER JOIN:-
SELECT employee_name,department_name FROM employees JOIN departments ON
employee.department_id = department.department_id;
ii)RIGHT JOIN:-
SELECT employee_name,department_name FROM employees RIGHT JOIN departments ON
employee.department_id = department.department_id;
iii)LEFT JOIN:-
SELECT employee_name,department_name FROM employees LEFT JOIN departments ON
employee.department_id = department.department_id;
