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

ER Diagram, A database will be made to store information about patients in ...

A database will be made to store information about patients in a hospital. On arrival, each patient’s personal details (name, address, and telephone number) are recorded where poss

Objectives-the structured query language, Objectives After reading this...

Objectives After reading this part, you should be able to: Modify, create, and delete database schema objects; Update database using SQL command; Retrieve data f

What is a project operation, What is a PROJECT operation? The project o...

What is a PROJECT operation? The project operation is a unary operation that returns its argument relation with particular attributes left out. Projection is shown by pie (π).

Relationship sets, A relationship set is a set of relationships of the simi...

A relationship set is a set of relationships of the similar type. For example, let consider the relationship among two entities sets course and student. Collection of all the in

Explain different types of keys and relational model., Relational Model: T...

Relational Model: The relational model uses a collection of tables to represent both data and the relationships among those data. Each table has multiple columns and each column h

Problem about data mining for business intelligence, Do the 11.1 11.1 Cre...

Do the 11.1 11.1 Credit Card Use. Consider the following hypothetical bank data on consumers’ use of credit card credit facilities in Table 11.3. Create a small worksheet in Exce

ERD, entity relationship diagram about finance

entity relationship diagram about finance

Express types of information available in mysql, Express types of informati...

Express types of information available in MySQL? Information as regards the result of queries:  This includes number of records affected by any SELECT or UPDATE or DELETE st

Differance between open addressing and chaining collision, Differance betwe...

Differance between Open addressing and chaining for collision resolution ? In open addressing , proceeding from the occupied position specified by hash address the program che

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