DBMS > Structural Query Language (SQL) > Some question for Database design and SQL

Example of queries.

 

1. Relations: building

 After practice the queries write the query
into your exam script

 

Relation:

 

Building={ b_id, b_name, location, hold_no }

Flat ={f_id, b_id,nroom,floor, contact}

Supplier = {sup_id, sup_name, address, mobile}

Item ={ i_id, i_name, desc}

Stock = {i_id, sup_id, sup_date, u_price, quantity}

Service = {s_id, b_id, f_id, i_id, sup_id, s_date, s_cost }

 

Political Activist Management

Purpose: searching  division, district, thana, union, ward-wise,

 

Tables: 

Division = (div_id, div_name)

District = {dis_id, dis_name, div_id}

Thana  = {th_id, th_name, dis_id}

Union = {un_id, u_name, th_id}

Ward =( w_id, w_name, un_id)

Activist = { a_id, name, contact, w_id, bloodg}

Desig = {des_id, deg_name)

Resp = { res_id, a_id, des_id, start_date, end_date, status, comment }

SQL:

1. List all activist from ward=1

Select * from activist where w_id=1

2. List all activist from ward name= “Fultala 11”

Select * from activist  where w_id= (

         select w_id from ward where w_name=”Fultala 11”)

 

 

3. List all activist from union=1

Select * from activist, ward, union

where

     Activist.w_id= ward.w_id and

     Ward.un_id=Union.un_id and

     Un_id=1

 

4. List all activist name and w_name

Select activist.name, ward.w_name from activist, ward

     Where activist.w_id=ward.w_id

5. List ward-wise number of activists

Select ward_id, count(*)  from activist

      Group by w_id

 

No More

Next

Write SQL to create the database and tables.

Feedback

Submit