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

Define the normalisation form, Define in which normalisation form if an att...

Define in which normalisation form if an attribute of a composite key is dependent on an attribute of other composite key. Ans: 3NF A relation is in 3 NF if an attribute of

What do you mean by correlated subquery, What do you mean by Correlated sub...

What do you mean by Correlated subquery? Subqueries, or nested queries, are used to bring back a set of rows to be used by the parent query. Depending on how the subquery is writ

What are the advantages of relational data model, What are the advantages o...

What are the advantages of relational data model? Normal 0 false false false EN-IN X-NONE X-NONE MicrosoftInternetExplorer

Define thomas write rule, Define Thomas write rule Thomas' write rule: ...

Define Thomas write rule Thomas' write rule: The Thomas' write rule is a modification of timestamp-ordering protocol for concurrency control. Assume that transaction T i issue

Describe specialsation and generalization, Describe Specialsation and gener...

Describe Specialsation and generalization? Specialisation /generalization: This is a top down and bottom up approach to the design of database. It displays the IS_A relation

Define multivalued dependency, Define multivalued dependency.  What do unde...

Define multivalued dependency.  What do understand by trivial multivalued dependency?  Ans: A multivalued dependency is a full constraint among two sets of attributes in a rela

Which operator is used to compare a value to a list literals, Which operato...

Which operator is used to compare a value to a list of literals values that have been specified? BETWEEN operator is used to compare a value to a list of literals values that h

What is document scanning and imaging, What is document scanning and imagin...

What is document scanning and imaging? Document scanning and imaging, or digital archiving, is the method of scanning a document into a digital image to archive and retrieve a

What is foreign key, What is foreign key? A relation schema r1 derived ...

What is foreign key? A relation schema r1 derived from an ER schema may contain among its attributes the primary key of another relation schema r2.this attribute is known as a

What is difference among mysql_connect and mysql_pconnect, What is differen...

What is difference among mysql_connect and mysql_pconnect? Mysql_connect() opens a new connection to the database while mysql_pconnect() opens persistent connection to the data

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