DBMS Assignment-Level-1

Create table Vendors as follows:

Ven_id Ven_name Contact Street City Salary Dept_id
1
Ratna

9044670000

Rajiv Chowk

Delhi
30000 A01
2
Anjali

9839780000

Vijay Nagar

Kanpur
21000 A01
3
Rahul

9532890000

Model Town

Delhi
35000 B02
4
Ankit

7522900000

Rajajipuram

Lucknow
24500 C03
5
Sumit

7271000000

Ramadevi

Kanpur
26000 B02
6
Kapil

7890120000

Mall Road

Kanpur
27000 C03
7
Abhinav

9984256311
———–
Lucknow
25000C03
8
Ankur

9865237411

Akbar Road

Delhi
20700B03
  1. Set Ven_id as primary key
  2. Ven_name and contact can’t have null values.

Now perform the following Queries on the Vendor Table:

  • Find the dept_id and the total number of vendors in individual departments.
  • Find the minimum and maximum salary paid to individual vendors of individual departments.
  • Find the average salary of all the departments having a minimum salary paid at least 25000.
  • Find the total number of vendors working in individual departments having a salary of at least 21000. Consider only those vendors whose street is available to the organization.
  • Find the maximum salary of all the departments having more than two vendors.
  • Find the average salary of all the departments having more than two vendors.
  • Retrieve sum of salaries of all vendors working in the department having dept id=A03.
  • Retrieve a number of vendors living in each city.
  • Display the total no. of vendors in each department having a salary of more than 26000.
  • Display the total salary spent on each city.
  • List the cities and the number of vendors in each city. The result should be in the descending order of the number of vendors.
  • Write a SQL statement to find the highest salary of an individual department, for only those vendors whose ID is within the range 03 and 07.
  • Write a SQL statement to display vendor details (ID and salary) who’s IDs are within the range 02 and 07 and the highest salary is more than 1000.

If you have any questions regarding database management system then comment your questions

Thank you

One thought on “DBMS Assignment-Level-1

Leave a Reply

Your email address will not be published. Required fields are marked *