DBMS assignment-1
create table employee__ (emp_id int primary key , emp_name
char(20) not null , job char(20) , salary number(20) , commission
number(20) , Dept_No number(10));
1. Write a query of display employee name, salary and commission
from emp table.
->select emp_name , salary , commission from employee__;
2. Display all the record of emp table whose commission is not
NULL.
select * from employee__ where commission is not null;
3.display sum of salary of all employee.
select sum(salary) from employee__;
4. Find the no of employee from emp table.
select count(emp_id) from employee__;
5. Write a query to display the highest salary of employee.
select max(salary) from employee__;
6. Create a query to display the name and salary of employees
earning more than Rs. 5000/-.
select emp_name , salary from employee__ where salary >5000;
7. Create a query to display the name whose salary between 5,000
to 10,000 Rs.
select emp_name from employee__ where salary between 5000
and 10000;
8. . Display all names of employee whose salary is less than 6,000
select emp_name from employee__ where salary <6000;
9. Create a query to display the employee name and department
number for employee number 1002.
select emp_name , dept_no from employee__ where emp_id =
1002;
10. . Display the minimum salary of employee.
select min(salary) from employee__;
DBMS assignment-2
Student
create table stude__ (Roll_No int primary key , Fname char(20) not
null , Lname char(20) not null , Sname char(20) not null , dept_no
int references dept__(dept_no) , semester int , contact_no
number(10) unique , gender char(10));
dept.
create table dept__(dept_no int primary key , dept_name
varchar(20) not null);
sql query
1. Display the first name and contact number of all students.
select fname,contact_no from stude__;
2. Display Roll no, First name, dept id and Dept name.
select stude__.roll_no , stude__.fname , dept__.dept_no ,
dept__.dept_name from stude__ inner join dept__ on
stude__.dept_no = dept__.dept_no;
3. Display the firstname, Sname , contact no and Dept name
whose first name contain ‘a’.
select stude__.fname , stude__.sname , stude__.contact_no ,
dept__.dept_name from stude__ full join dept__ on
stude__.dept_no = dept__.dept_no where fname like 'a%';
4. Give the Firstname and roll no of all students of information
technology who are members.
select stude__.roll_no , stude__.fname from stude__ full
outer join dept__ on stude__.dept_no = dept__.dept_no
where dept_name = 'information technology';
5. Display the name of departments whose students are
members.
select stude__.fname , dept__.dept_name from stude__ inner
join dept__ on stude__.dept_no = dept__.dept_no;
6. Display name of departments for which no students
are members.
select dept__.dept_name from dept__ left join stude__ on
stude__.dept_no = dept__.dept_no where stude__.dept_no IS
NULL;
7. Combine the Firstname, Middlename and Surname.
select fname || lname || sname as full_name from stude__;
8. Count the Total no of Students in each department.
select count(stude__.roll_no) from stude__ left outer join
dept__ on stude__.dept_no = dept__.dept_no group by
dept__.dept_name;
9. Count the Total no of Female Students in each department.
select count(stude__.roll_no) from stude__ left outer join
dept__ on stude__.dept_no = dept__.dept_no where
stude__.gender ='F' group by dept__.dept_name;
10.Count the Total no of Students in each department and
display only those department which having more than 3
students.
select count(stude__.roll_no) , dept__.dept_name from
stude__ left outer join dept__ on stude__.dept_no =
dept__.dept_no group by dept__.dept_name having
count(stude__.roll_no)>3;
DBMS assignment – 3
1. From the following table, write a SQL query to find the details of those
salespeople who come from the 'Paris' City or 'Rome' City. Return
salesman_id, name, city, commission.
select sal_id , sal_name , city , commission from salesman
where city in ('paris','rome');
2. From the following table, write a SQL query to retrieve the details of all
customers whose ID belongs to any of the values 3007, 3008 or 3009.
Return customer_id, cust_name, city, grade, and salesman_id.
select cust_id , cust_name , city , grade , sal_id from customer
where cust_id in(3007,3008,3009);
3. From the following table, write a SQL query to find salespeople who
receive commissions between 0.12 and 0.14 (begin and end values are
included). Return salesman_id, name, city, and commission. Sample
table: salesman
select sal_id , sal_name , city , commission from salesman where
commission between 0.12 and 0.14;
4. From the following table, write a SQL query to retrieve the details of
the salespeople whose names begin with any letter between 'A' and 'L'
(not inclusive). Return salesman_id, name, city, commission.
select sal_id , sal_name , city , commission from salesman where
sal_name between 'a%' and 'l%';
5. From the following table, write a SQL query to calculate total purchase
amount of all orders. Return total purchase amount. Go to the editor
select distinct sum(purchase_amnt) from orders;
6. From the following table, write a SQL query to find the maximum
purchase amount.
select distinct max(purchase_amnt) from orders;
7. From the following tables, write a SQL query to locate all the customers
and the salesperson who works for them. Return customer name, and
salesperson name.
select salesman.sal_name , customer.cust_name from salesman
inner join customer on salesman.sal_id = customer.sal_id;
8. From the following tables write a SQL query to find the salesperson and
customer who reside in the same city. Return Salesman, cust_name
and city.
select salesman.sal_name , customer.cust_name from salesman
inner join customer on salesman.city = customer.city;
9. From the following tables, write a SQL query to find all salespeople and
customers located in the city of London
select salesman.sal_name , customer.cust_name from salesman
full outer join customer on salesman.city = customer.city where
customer.city = 'london';
10. From the following table, create a view for all salespersons.
Return salesperson ID, name, and city.
create view v1 as select sal_id , sal_name , city , commission
from salesman;
select sal_id , sal_name , city from v1;
