SQL Queries for Creating, Populating, and Retrieving Department Data of an University

University Management System - Department Table

Question

You are designing a database for your university management system. You need to create a table named Department to store details about each department as shown below:

Dept_name Building Budget
Physics Shahid 65000
Chemistry Bangabandhu 72000
Mathematics Sonargaon 80000

Answer the following:

  1. Write the SQL statement to create the Department table, specifying an appropriate primary key.
  2. Write SQL INSERT statements to add all the rows shown in the table above.
  3. Write an SQL SELECT query to find all departments where the department is “Physics”, the building is “Shahid”, and the budget is less than or equal to 70000. Also, show the expected output of this query.

Answer

i) Create the Department table

CREATE TABLE Department (
  Dept_name VARCHAR(50) PRIMARY KEY,
  Building VARCHAR(50),
  Budget INT
);

ii) Insert the rows

INSERT INTO Department (Dept_name, Building, Budget)
VALUES 
  ('Physics', 'Shahid', 65000),
  ('Chemistry', 'Bangabandhu', 72000),
  ('Mathematics', 'Sonargaon', 80000);

iii) Select query to find specific departments

SELECT *
FROM Department
WHERE Dept_name = 'Physics' 
  AND Building = 'Shahid' 
  AND Budget <= 70000;

Expected Output:

Dept_name Building Budget
Physics Shahid 65000

একটি মন্তব্য পোস্ট করুন