Difference between revisions of "Help Desk"

From SQLZOO
Jump to navigation Jump to search
(Created page with "<div class='qu'> <p class=imper'>There are three issues that include the words "index" and "Oracle". Find the call_date for each of them</p> <source lang='sql' class='def'> </...")
 
 
(8 intermediate revisions by 2 users not shown)
Line 1: Line 1:
<div class='qu'>
+
==Scenario==
<p class=imper'>There are three issues that include the words "index" and "Oracle". Find the call_date for each of them</p>
+
A software company has been successful in selling its products to a number of customer organisations, and there is now a high demand for technical support. There is already a system in place for logging support calls taken over the telephone and assigning them to engineers, but it is based on a series of spreadsheets. With the growing volume of data, using the spreadsheet system is becoming slow, and there is a significant risk that errors will be made.
<source lang='sql' class='def'>
+
*[[Helpdesk Easy Questions]]
</source>
+
*[[Helpdesk Medium Questions]]
<pre class=output>
+
*[[Helpdesk Hard Questions]]
+---------------------+----------+
 
| call_date          | call_ref |
 
+---------------------+----------+
 
| 2017-08-12 16:00:00 |    1308 |
 
| 2017-08-16 14:54:00 |    1697 |
 
| 2017-08-16 19:12:00 |    1731 |
 
+---------------------+----------+
 
</pre>
 
</div>
 
  
<div class='qu'>
+
[[File:helpdesk.png]]
<p class=imper'>Samantha Hall made three calls on 2017-08-14. Show the date and time for each
 
</p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
+---------------------+------------+-----------+
 
| call_date          | first_name | last_name |
 
+---------------------+------------+-----------+
 
| 2017-08-14 10:10:00 | Samantha  | Hall      |
 
| 2017-08-14 10:49:00 | Samantha  | Hall      |
 
| 2017-08-14 18:18:00 | Samantha  | Hall      |
 
+---------------------+------------+-----------+
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'>There are 500 calls in the system (roughly). Write a query that shows the number that have each status.
 
</p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
+--------+--------+
 
| status | Volume |
 
+--------+--------+
 
| Closed |    486 |
 
| Open  |    10 |
 
+--------+--------+
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'>Calls are not normally assigned to a manager but it does happen. How many calls have been assigned to staff who are at Manager Level?
 
</p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
+------+
 
| mlcc |
 
+------+
 
|  51 |
 
+------+
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'>--5 Show the manager for each shift. Your output should include the shift date and type; also the first and last name of the manager.</p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
+------------+------------+------------+-----------+
 
| Shift_date | Shift_type | first_name | last_name |
 
+------------+------------+------------+-----------+
 
| 2017-08-12 | Early      | Logan      | Butler    |
 
| 2017-08-12 | Late      | Ava        | Ellis    |
 
| 2017-08-13 | Early      | Ava        | Ellis    |
 
| 2017-08-13 | Late      | Ava        | Ellis    |
 
| 2017-08-14 | Early      | Logan      | Butler    |
 
| 2017-08-14 | Late      | Logan      | Butler    |
 
| 2017-08-15 | Early      | Logan      | Butler    |
 
| 2017-08-15 | Late      | Logan      | Butler    |
 
| 2017-08-16 | Early      | Logan      | Butler    |
 
| 2017-08-16 | Late      | Logan      | Butler    |
 
+------------+------------+------------+-----------+
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 
 
 
<div class='qu'>
 
<p class=imper'></p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
</pre>
 
</div>
 

Latest revision as of 21:51, 22 March 2018

Scenario

A software company has been successful in selling its products to a number of customer organisations, and there is now a high demand for technical support. There is already a system in place for logging support calls taken over the telephone and assigning them to engineers, but it is based on a series of spreadsheets. With the growing volume of data, using the spreadsheet system is becoming slow, and there is a significant risk that errors will be made.

Helpdesk.png