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

Entity relationship diagram, The Enhanced Entity Relationship Diagram (ERD)...

The Enhanced Entity Relationship Diagram (ERD) produced with a drawing or case tool. Your EERD must use a ''standard'' notation style such as Crows Foot or Chen. Please note that t

Implement a cache simulator, You are required to program (in a high level l...

You are required to program (in a high level language such as C, C++, Java) and implement a cache simulator which will have the following inputs and outputs:- INPUTS

What are the several kinds of the update operations relation, What are the ...

What are the several kinds of the update operations on relations? Also explain the constraints on these update operation. Give instance in support of your answer. There are th

Creation of indexes, Creation of Indexes Automatically: When a primar...

Creation of Indexes Automatically: When a primary key or Unique constraint is show in a table definition then a unique index is formed automatically. Manually: User can

Data redundancy, Data Redundancy -A lot of information is being repeated in...

Data Redundancy -A lot of information is being repeated in the relation. For instance, the information that MCS-014 is named SSAD is repetitive, address of Rahul is "D- 27, Main ro

Where does view definition stored, Where does view definition stored? ...

Where does view definition stored? A view definition is permanently stored as part of the database.

Draw the E/R diagrams, for the following business rules . Identify all enti...

for the following business rules . Identify all entities, attributes, relationships, primary keys, cardinalities and constraints where appropriate 3. (9) An art museum owns a larg

Need web-based database, Our company hires independent contractors to insta...

Our company hires independent contractors to install equipment at precise locations across North America. We want to track the installation data and generate reports. The compan

Evaluate the null hypothesis, 1.Suppose you are given a dataset that consis...

1.Suppose you are given a dataset that consists of a random sample of tasters, on which the following variables were obtained: (y) Zpref = taste preference for green beans store

Explain candidate key and super key, Explain Candidate Key and Super Key ...

Explain Candidate Key and Super Key A super key is a set of one or more than attributes that, taken collectively, permits us to identify uniquely a tuple in the relation. Wher

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