DBMS Assignment-Level-1

Sample DBMS Assignment

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	25000	C03
8	Ankur	9865237411	Akbar Road	Delhi	20700	B03

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 the sum of salaries of all vendors working in the department having dept id=A03.
• Retrieve the 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 in each city.
• List the cities and the number of vendors in each city. The result should be in 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) whose IDs are within the range 02 and 07 and the highest salary is more than 1000.

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

Thank you