Execute sql queries, Database Management System

Assignment Help:

Consider the relations described below:
PHYSICIAN (regno, name, telno, city)
PATIENT (pname, street, city)
VISIT (pname, regno, date_of_visit, fee)
Where the regno and pname identify the physician and the patient uniquely corresponding. Express queries (i) to (iii) in SQL.
(i) Get the name and regno of physicians who are in Delhi.
(ii) Find out the name and city of patient(s) who visited a physician on 31 August 2004.
(iii) Get the name of the physician and the total number of patients who have visited her.
(iv) What does the subsequent SQL query answer
SELECT DISTINCT name
FROM PHYSICIAN P
WHERE NOT EXISTS
( SELECT *
FROM VISIT
WHERE regno = p.regno )

(i) Select name, regno from PHYSICIAN where city = ‘Delhi';
(ii) Select pname, city from PATIENT,VISIT where PATIENT.pname=VISIT.pname and date_of_visit = '31-Aug-04';
(iii) select name, count(*) from PHYSICIAN, VISIT where PHYSICIAN.regno = VISIT.regno group by Physician . regno;
(iv) This will provide the name of physicians who have not visited any patient.


Related Discussions:- Execute sql queries

Design and implement a database system, Safe Banking is a Canberra based cr...

Safe Banking is a Canberra based credit union, which has several branches around Canberra and Queanbeyan. YOU have taken over Safe Banking. YOU need to create a database that organ

What is equi join, What is equi join? Equi Join - It generates all th...

What is equi join? Equi Join - It generates all the combinations of tuples from two relations which satisfy a join condition along with only equality comparison (=).

Optimistic concurrency control, Optimistic Concurrency Control Is locki...

Optimistic Concurrency Control Is locking the only way to stop concurrency related problems? There exist some other ways too. One such way is known as an Optimistic Concurrency

Relational algebraic operators, Problem Explain at least five relationa...

Problem Explain at least five relational algebraic operators with a suitable example. Note: All the above descriptions should be done by taking at least two examples from ou

What is dml query langauge, What is DML Query Langauge? Data Manipulati...

What is DML Query Langauge? Data Manipulation Language (DML) - A data manipulation language is a language in which enables users to access or manipulate data as organized throu

Design a gennalization and specialization hierarchy for an a, Design a genn...

Design a gennalization and specialization hierarchy for an automobile sales company

Difference between clustered and a non-clustered index, What are the differ...

What are the difference between clustered and a non-clustered index? A clustered index is a special type of index that reorders the way records in the table are physically stor

Advantages and drawbacks of ddbms, Advantages And Drawbacks Of Ddbms  T...

Advantages And Drawbacks Of Ddbms  There are various reasons for building distributed database systems, with sharing of data, reliability and availability, and speedup of query

Examine about the behavioural diagrams, Examine about the Behavioural Diagr...

Examine about the Behavioural Diagrams  The main objective of behavioural diagrams is to visualize, specify, construct and document dynamic aspects of a system. The interactio

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd