{"id":478,"date":"2023-03-13T03:08:22","date_gmt":"2023-03-12T21:38:22","guid":{"rendered":"https:\/\/jassweb.com\/new22\/solved-sql-querying-doctors-and-hospitals-closed\/"},"modified":"2023-03-13T03:08:22","modified_gmt":"2023-03-12T21:38:22","slug":"solved-sql-querying-doctors-and-hospitals-closed-2","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/","title":{"rendered":"[Solved] SQL querying \u2013 Doctors and Hospitals [closed]"},"content":{"rendered":"<h2> Introduction <\/h2>\n<p>[ad_1]<\/p>\n<p>SQL querying is a powerful tool for managing data in a database. It is used to retrieve, manipulate, and store data in a database. In this article, we will discuss how to use SQL querying to manage data related to doctors and hospitals. We will look at how to query data related to doctors, such as their names, specialties, and contact information, as well as data related to hospitals, such as their locations, services, and patient reviews. We will also discuss how to use SQL querying to create reports and analyze data. Finally, we will discuss how to use SQL querying to optimize performance and ensure data integrity.<\/p>\n<h2> Solution<\/h2>\n<p><\/p>\n<p>SELECT d.name AS &#8216;Doctor Name&#8217;, h.name AS &#8216;Hospital Name&#8217;<br \/>\nFROM doctors d<br \/>\nINNER JOIN hospitals h<br \/>\nON d.hospital_id = h.id; <\/p>\n<p><\/p>\n<div class=\"entry-content\" itemprop=\"text\">\n<script async src=\"https:\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-1088640234840270\" crossorigin=\"anonymous\"><\/script><br \/>\n<script><\/p>\n<p><\/script><\/p>\n<p><\/p>\n<div id=\"answer-16570560\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"16570560\" data-parentid=\"16569914\" data-score=\"1\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>I\u2019ll help you with your first question, and I\u2019ll leave to you the second.<\/p>\n<blockquote>\n<ol>\n<li>Display doctorid, dname, total fees received by the doctor(s) who have treated more than one patient?<\/li>\n<\/ol>\n<\/blockquote>\n<p>Let\u2019s split this problem in pieces:<\/p>\n<p>So you need first to know which doctors have treated more than one patient. That information is in the table <code>billing<\/code>. So:<\/p>\n<pre><code>select doctorId, count(patientId) as patientCount\nfrom (select distinct doctorId, patientId from billing) as a\ngroup by doctorId\nhaving count(patientId)&gt;1;\n<\/code><\/pre>\n<p>This query will return only the Ids of the doctors that have more than one patient. Notice that I\u2019m using a subquery to deduplicate the doctor-patient tuple.<\/p>\n<p>Now let\u2019s attack the other part of this question: The total fees of each doctor. Again, that info is in the table <code>billing<\/code>:<\/p>\n<pre><code>select doctorId, sum(fees) as totalFees\nfrom billing\ngroup by doctorId;\n<\/code><\/pre>\n<p>Finally, let\u2019s put it all together, and include the doctor\u2019s info, which is in the table <code>doctor<\/code>:<\/p>\n<pre><code>select\n    d.doctorId, d.doctorName, a.totalFees\nfrom\n    doctor as d\n    inner join (\n        select doctorId, sum(fees) as totalFees\n        from billing\n        group by doctorId\n    ) as a on d.doctorId = a.doctorId\n    inner join (\n        select doctorId, count(patientId) as patientCount\n        from (select distinct doctorId, patientId from billing) as a\n        group by doctorId\n        having count(patientId)&gt;1;\n    ) as b on d.doctorId = b.doctorId;\n<\/code><\/pre>\n<p>Hope this helps<\/p>\n<hr>\n<p>Things you need to study and (or) keep in mind:<\/p>\n<ol>\n<li>You need to understand how to relate data stored in different tables. Study how to use <code>INNER JOIN<\/code> (and also <code>LEFT JOIN<\/code> and <code>RIGHT JOIN<\/code>)<\/li>\n<li>You need to understand how does <code>GROUP BY<\/code> works, and how to use aggregate functions (<code>sum()<\/code>, <code>count()<\/code>, etcetera).<\/li>\n<li>You know how to write subqueries. Now try to use them not only for <code>where<\/code> conditions, but as data sources (including them in <code>from<\/code> statements)<\/li>\n<li>Keep a copy of the reference manual of your RDBMS at hand. Also a good book on SQL can help you (go to a bookstore or library and find one you like).<\/li>\n<\/ol>\n<\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p> <span class=\"d-none\" itemprop=\"commentCount\">6<\/span> <\/p>\n<\/div>\n<\/div>\n<p>solved SQL querying \u2013 Doctors and Hospitals [closed] <\/p>\n<p><script async src=\"https:\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-1088640234840270\" crossorigin=\"anonymous\"><\/script><br \/>\n<script><\/p>\n<p><\/script><\/div>\n<p>[ad_2]<\/p>\n<h1>Solved SQL Querying &#8211; Doctors and Hospitals<\/h1>\n<p>SQL is a powerful language used to query databases. It can be used to retrieve information from a database, such as doctors and hospitals. In this article, we will discuss how to use SQL to query a database for doctors and hospitals.<\/p>\n<h2>Retrieving Doctors<\/h2>\n<p>The first step in retrieving doctors from a database is to create a query. The query should include the table name, the columns to be retrieved, and any conditions that need to be met. For example, if we wanted to retrieve all doctors from a database, the query would look like this:<\/p>\n<pre>SELECT * FROM doctors;<\/pre>\n<p>This query will return all the columns from the doctors table. If we wanted to retrieve only certain columns, we could specify them in the query. For example, if we wanted to retrieve only the doctor&#8217;s name and specialty, the query would look like this:<\/p>\n<pre>SELECT name, specialty FROM doctors;<\/pre>\n<p>We can also add conditions to the query to filter the results. For example, if we wanted to retrieve only doctors who specialize in pediatrics, the query would look like this:<\/p>\n<pre>SELECT * FROM doctors WHERE specialty = 'pediatrics';<\/pre>\n<h2>Retrieving Hospitals<\/h2>\n<p>The process for retrieving hospitals from a database is similar to retrieving doctors. The query should include the table name, the columns to be retrieved, and any conditions that need to be met. For example, if we wanted to retrieve all hospitals from a database, the query would look like this:<\/p>\n<pre>SELECT * FROM hospitals;<\/pre>\n<p>This query will return all the columns from the hospitals table. If we wanted to retrieve only certain columns, we could specify them in the query. For example, if we wanted to retrieve only the hospital&#8217;s name and address, the query would look like this:<\/p>\n<pre>SELECT name, address FROM hospitals;<\/pre>\n<p>We can also add conditions to the query to filter the results. For example, if we wanted to retrieve only hospitals in a certain city, the query would look like this:<\/p>\n<pre>SELECT * FROM hospitals WHERE city = 'New York';<\/pre>\n<h2>Conclusion<\/h2>\n<p>SQL is a powerful language used to query databases. It can be used to retrieve information from a database, such as doctors and hospitals. By using the correct query syntax, we can easily retrieve the information we need from a database.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction [ad_1] SQL querying is a powerful tool for managing data in a database. It is used to retrieve, manipulate, and store data in a database. In this article, we will discuss how to use SQL querying to manage data related to doctors and hospitals. We will look at how to query data related to &#8230; <a title=\"[Solved] SQL querying \u2013 Doctors and Hospitals [closed]\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/\" aria-label=\"More on [Solved] SQL querying \u2013 Doctors and Hospitals [closed]\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[341],"class_list":["post-478","post","type-post","status-publish","format-standard","hentry","category-solved","tag-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] SQL querying \u2013 Doctors and Hospitals [closed] - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] SQL querying \u2013 Doctors and Hospitals [closed] - JassWeb\" \/>\n<meta property=\"og:description\" content=\"Introduction [ad_1] SQL querying is a powerful tool for managing data in a database. It is used to retrieve, manipulate, and store data in a database. In this article, we will discuss how to use SQL querying to manage data related to doctors and hospitals. We will look at how to query data related to ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2023-03-12T21:38:22+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] SQL querying \u2013 Doctors and Hospitals [closed]\",\"datePublished\":\"2023-03-12T21:38:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/\"},\"wordCount\":733,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"sql\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/\",\"name\":\"[Solved] SQL querying \u2013 Doctors and Hospitals [closed] - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2023-03-12T21:38:22+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] SQL querying \u2013 Doctors and Hospitals [closed]\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] SQL querying \u2013 Doctors and Hospitals [closed] - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] SQL querying \u2013 Doctors and Hospitals [closed] - JassWeb","og_description":"Introduction [ad_1] SQL querying is a powerful tool for managing data in a database. It is used to retrieve, manipulate, and store data in a database. In this article, we will discuss how to use SQL querying to manage data related to doctors and hospitals. We will look at how to query data related to ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/","og_site_name":"JassWeb","article_published_time":"2023-03-12T21:38:22+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] SQL querying \u2013 Doctors and Hospitals [closed]","datePublished":"2023-03-12T21:38:22+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/"},"wordCount":733,"commentCount":0,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["sql"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/","url":"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/","name":"[Solved] SQL querying \u2013 Doctors and Hospitals [closed] - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2023-03-12T21:38:22+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-sql-querying-doctors-and-hospitals-closed-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] SQL querying \u2013 Doctors and Hospitals [closed]"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/478","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=478"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/478\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=478"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=478"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=478"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}