Very simple queries of DBMS

3
Queries for Data Definition and Data Manipulation Language
1. Table Student:
Create a table using create command:
Create table Student
(Rollno int, Name char(10), F_name char(10), City char(10), Contact_no num(10), Email_id varchar(15));
Use of Desc command:
Decs students;
Use of Alter command:
alter table students add(course varchar(10));
alter table students modify(roll_no varchar(20));
alter table students drop(permanent_address);

4
Use of insert command:
insert into students values('101','01jul96','mohit tripathi','tara Chandra
tripathi','lalkuan',7351982624,'mohit07tripathi@gmail.com','kaushaliya tripathi' ');
insert into students values('102','21mar97','megha tripathi','tara Chandra
tripathi','lalkuan',9690723702,'mohit01tripathi@gmail.com','kaushaliya tripathi');
insert into students values('103','23sep99','gaurav tripathi','tara Chandra
tripathi','lalkuan',8954290592,'mohittripathi95@gmail.com','kaushaliya tripathi');
Use of select * command:
select *from Student;

5
Use of update command:
update students set address='rampur' where address='haldwani';
Use of delete command:
delete from students where roll_no=108;
2. Table Faculty:
Create table Faculty
(fid varchar(20) primary key,fname varchar(20) not null,fContact_no number(10) not null,
fEmail_id varchar(25) not null,fsalary int check(fsalary>30000),fcourse char(10), fdesignation char(15));
Use of Insert command:
insert into Faculty values('f01','Ajay','12345','abc@gmail.com','32000','MCA','Asstprof');
insert into Faculty values('f02','Juhi','23456','bcd@gmail.com','36000','BCA','Asstprof');
insert into Faculty values('f03','Rajesh','34567','cde@gmail.com','31000','MCA''Asstprof');

6
Use of Select command:
select *from Faculty;
Queries
A. Retrieve the name of faculties earning more than Rs.50000.
select fname from faculty where fsalary >50000;
B. Retrieve the name of faculty who are teaching MCA and are Asstprof.
select fname from faculty where fcourse='MCA' and fdesignation='Asstprof';
C. Retrieve the e-mail and course of juhi and rajesh.
select f_email_id,f_course from faculty
where f_name='juhi' or f_name='rajesh';

7
D. Change the designation of aarti from asstprof to lecturer.
update faculty set f_designation='lacturer'
where f_name='aarti';
E. Change the course cse to cs and e
update faculty set f_course='cs&e'
where f_course='cse';
F. Retrieve the name of employees who have a gmail account.
select fname from Faculty where fEmail_id like '%@gmail.com';
G. Retrieve all the details of faculties whose name start with letter V.
select *from Faculty where fname like 'V%';

8
H. Remove faculty who is earning more than Rs.71000.
delete from Faculty where fsalary >71000
I. Retrieve all the details of the person who is provided a number ‘23456’.
select fname,fEmail_id,fsalary,fcourse,fdesignation from Faculty where fcontact_no='23456';
3. Table Staff:
Create table Staff
(sno varchar(10), fname char(10), lname char(10), salary number(10,2), position char(20));
desc Staff;
insert into Staff values('SL100','John','White','30000.00','Manager');
insert into Staff values('SL101','Susan','Brand','24000.00','Manager');
select *from Staff;

9
Queries based on Aggregate functions for table Staff:
1. select sum(salary) as sum_salary from Staff where position='Manager';
2. select sum(salary) as sum_salary from Staff;
3. select sum(salary) as sum_salary from Staff where position='Project Manager';
4. select avg(Distinct salary) as avg_salary from Staff where Staff.position='Project Manager';
5. select avg(salary) as avg_salary from Staff where Staff.position='Project Manager';
6. select sum(Distinct salary) as sum_salary from Staff;
7. select min(salary)as min_salary from Staff;
8. select max(salary)as max_salary from Staff;
9. select fname from Staff where salary in(select min(salary) from Staff);

10
10. select fname from Staff where salary in(select max(salary) from Staff);
11. select count(sno) as sno_count from Staff where Staff.position='Project Manager';
12. select count(sno) as sno_count from Staff where salary>9000;
13. select count(sno) as sno_count,sum(salary) as sum_salary from Staff where Staff.position='Manager';
Updation on table Staff:
alter table Staff add(bno varchar(10));
update Staff set bno='B3' where fname='John';
update Staff set bno='B5' where fname='Susan';
14. select bno,count(sno) as count,sum(salary) as sum from Staff group by bno;
15. select bno,count(sno) as count,sum(salary) as sum from Staff group by bno order by bno;

11
4. Table Address, People1, PhoneNumbers1:
create table Address
(AddressID int primary key, Company char(10),
Address number(10),Zip number(10));
describe Address;
create table People1
(Id int primary key,Name char(10),
AddressId int references Address(AddressID));
describe People1;
create table PhoneNumbers1
(PhoneID int primary key,Id int references People(Id),Phone number(10));
describe PhoneNumbers1;
insert into Address values('1','ABC','123','12345');
insert into Address values('2','XYZ','456','14454');
insert into Address values('3','PDQ','789','14423');
select *from Address;
select *from People1;
select *from PhoneNumbers1;

12
Queries based on foreign key (or Sub-queries) for above tables:
1. Select name, PhoneID, Company from People1, PhoneNumbers1, Address where
Address.AddressID=People1.AddressID and People1.Id=PhoneNumbers1.Id;
2. select Company,Zip from Address, People1 where name='Jane' and Address.AddressID=People1.AddressID;
3. select Address,Company,Phone from Address,PhoneNumbers1,People1 where name='Chris' and
Address.AddressID=People1.AddressID and People1.Id=PhoneNumbers1.Id;
4. select Company,Zip from Address where AddressId in(select AddressId from People1 where name='Jane');
5. select Address,Company,Phone from Address,PhoneNumbers1 where AddressId in(select AddressId from
People1 where name='Chris');
6. select Phone from Phonenumbers1 where ID in(select ID from People1 where name='Joe');
7. select Name from People1,Address where Company!='ABC' and Address.AddressId=People1.AddressId;

13
8. select Name from People1 where AddressId in(select AddressId from Address where Company!='ABC');
5. Table Branch, Borrow1, Customer, Deposit:
create table Branch(BName char(10) primary key,City char(10));
create table Borrow1
(LoanNo number(10) primary key ,CName char(10), Bname char(10) references Branch(BName),Amount
number(10));
create table Customer(CName char(10) primary key,City char(10));
create table Deposit
(AccountNo number(10) primary key,CName char(10) references Customer(CName),BName char(10),Amount
number(10),ADate date);
insert into Branch values('Vrce','Nagpur');
insert into Branch values('Ajni','Nagpur');
select* from Branch;
select* from Borrow1;

14
select* from Customer;
Select* from Deposit;
Queries related to above tables:
select CName from Deposit where BName in(select BName from Branch where Branch.city='Delhi') and
CName in(select CName from Customer where Customer.City='Mumbai');
select CName from Deposit where BName in (select BName from Branch where CName in(select CName from
Customer where Branch.City=Customer.City));

15
PL/SQL
Programmes of VIEW:
Create view:
1. create view staff_1 as select f_name,position,salary from staff;
2. select * from staff_1;
3. desc staff_1;

16
4. Insert into staff_1 values('mohit','director',100000);
5. select * from staff_1 where position='manager';
6. desc staff;
7. alter view staff_1 add('s_name',varchar);
8. update staff_1 set f_name='ratnesh' where position='director';

17
9. create view staff2 as select position,salary from staff;
10. select * from staff2;
11. delete from staff_1 where f_name='ratnesh';
12. drop view staff2;

18
Write a pl/sql block to print “HELLO” five time.
Write a pl/sql block to print a name:

19
Write a pl/sql block to print a predefine error:
TRIGGERS
Creating a trigger

Very simple queries of DBMS

More Related Content

Similar to Very simple queries of DBMS

Recently uploaded

Very simple queries of DBMS