Scribd
Upload
13 views5 pages

DBMS ass

Uploaded by

ishasavani94
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
13 views5 pages

DBMS ass

Uploaded by

ishasavani94
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 5

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;

You might also like