SQL Query from ERD Case Study Practice Problem Question For Exam and

Here is ERD Case Study Question For Exam  Question and Pracrtice Problem for assignment and homework to understand ERD and its Queries 

Evaluate the following ERD and write the queries for the following scenarios. Write clean and indented code. (30)

            (** All Data is Stored in the Lower Case Format in the Tables)-8”). 

i)          Select name of all those subscribers from the subscriber table where subscribers belong to city (“RWP”) and have designations (“CHAIRMAN”) and who is VIP (“Y”) OR name start with (“MR.”) or name contains (“A”).

ii)         Select the application_date, application_type and connection_type from application table. Only those applications which are submitted in the last 3 months of year 2003 and 1^st month of year 2004. Exclude those applications, where work_completed status in (“C”,”P”)

iii)        Select an_date, advice_note_id, action from advice notes table where issue_date of advice notes is not in odd months of year 2003 and also have old_advice_note_id and old_subscriber_id. Hint, months in (1,3,5,7,9,11)

iv)        Show the number of months between request_date and issue_date and status from demand notes table. Only those records display where difference is greater than 100 days and whose payments are not received till now. Hint months_between function and multiply the months difference by 30 for simplicity.

v)         Write a DDL statement to create advice_notes table as drawn in ERD. Assume data types by yourself.  Delete the column info_by in after creating the table. Also allow non-unique insertions in advice_note_id column. 

vi)        Select city , sum(total_pairs) from the frame table. Only those records where exch_name contains (“NORTAL”) word and also only show those records where total_pairs sum is not greater than 25000.

vii)       Select subscriber name, address, phone_number, application_date, applications exchange and possible from subscribers and applications table. Only select those records where Possible status is (“Y”) and subscriber city is (“RWP”) or the subscriber is VIP.

viii)      Select all those subscriber names to whom advice_notes are issued (issue_date) in the current running year and also the action advice_notes action is (“NEW CONNECTION”) and advice_note status is (“C”,”I”).

ix)        Select the subscriber’s name, designation, application_date, application remarks, an_date, advice note payment_system from application, subscriber and advice_notes where advice_note’s status is (“C”) and subscriber is non-directory (directory is “N”).

x)         Select subscriber name, designation, application_type , application_date , requested_by name, designation and phone_number from application,subscriber and requested_by table where applications work_completed is (‘P’) and de_phones contains (“F-8”