Codes are needed for Oracle SQL$.$

Task $5$: Your manager says, "Now I would like to know how many Dell

or Samsung computers $($desktops$,$ tablets, laptops$)$ are currently being used by

employees in Sales or in Marketing. I want description information for which

department and the make, model, and extended information for each different

Dell or Samsung computer with a count of how many of each. And, of course, I

don't want information about computers or employees that are gone $($disposed of

or terminated$)."$

Note from IT boss: Be aware that the derived information in the

it$\_$asset$\_$inv$\_$summary table is not kept up to date in an automated way, so use

only data from other tables. $*/$

Task $6.$ Your manager says, "Now for the list of how many Dells

or Samsung computers are currently being used by employees in Sales or

Marketing that you just created, add rows that show the total

numbers of each make for each department."

Note from IT boss: Be aware that the derived information in the

it$\_$asset$\_$inv$\_$summary table is not kept up to date in an automated way, so use

only data from other tables..

ABCCo has several software apps built in$-$house that are shared and used by

customers and potential customers who download one or more apps after registering

online with information that includes their location.

ABCCo wishes to track information about which download server is used the most

by customers requesting a download. ABCCo is designing and building

infrastructure to capture and record data about downloads. Your team is implementing

and testing the backend for the information system being built.

There are ten download servers worldwide in different time zones. Customers are

worldwide. The ten time zones for the servers are Africa$/$Lagos$,$ Africa$/$Johannesburg$,$ America$/$Chicago$,$ America$/$La$\_$Paz, America$/$New$\_$York, America$/$Phoenix$,$ Asia$/$Dubai$,$

Asia$/$Manila$,$ Europe$/$Dublin$,$ and Europe$/$Prague$.$

Currently the download servers host $9$ app versions:

app for registered customers v$1\mathrm{.}0($regcust$\_$app$\_$v$1\mathrm{.}0),$

app for registered customers v$1\mathrm{.}1($regcust$\_$app$\_$v$1\mathrm{.}1),$

app for registered customers v$1\mathrm{.}2($regcust$\_$app$\_$v$1\mathrm{.}2),$

app for subscription customers v$1\mathrm{.}0($subsc$\_$app$\_$v$1\mathrm{.}0),$

app for subscription customers v$1\mathrm{.}1($subsc$\_$app$\_$v$1\mathrm{.}1),$

app for subscription customers v$1\mathrm{.}2($subsc$\_$app$\_$v$1\mathrm{.}2),$

app for non$-$paying customers v$1\mathrm{.}0($freecust$\_$app$\_$v$1\mathrm{.}0),$

app for non$-$paying customers v$1\mathrm{.}1($freecust$\_$app$\_$v$1\mathrm{.}1),$

and app for non$-$paying customers v$1\mathrm{.}2($freecust$\_$app$\_$v$1\mathrm{.}2)$

which was recently released.

A table belonging to user JAHERNA$42$ holds data about app downloads. That table

is named download. A table named customer is also created that contains

customer information from the customer registration process. There is sample

data in both tables.

Tasks $7$ through $10$ refer to the customer and download tables in the schema

belonging to user JAHERNA$42.*/$

Task $7$ Most Recent App Downloads

Write a command to retrieve which app was downloaded recently, that is in $2024.$

Find out when is was downloaded and the name of the customer who downloaded it$.$

In a separate command, compare the time zone offset value for your time zone to

the server time zone offset of the most recent downloaded app. A template for

the second command is provided. $*/$

select 'Central' as zone, tz$\_$offset$(\text{'}$America$/$Chicago$\text{'})$

from dual

union

select 'Most Recent App Download Zone' as zone,

tz$\_$offset$(\text{'}$The Timezone of Your Command $1$ Result'$)$ from dual;

Task $8$ Time Zone Arithmetic

Subtract the most recent app download time from the current server time $($as a

timestamp with timezone value$)$ to see what the day$/$time difference is between

the two. In addition to the commands and output, write a sentence $($as a comment$)$

to state what the data type of the difference you computed is$.*/$

The data type of the result appears to be $...$

$/*$ Task $9$ More than One Download

Some customers have downloaded software more than once. Write a command to

retrieve customer ids for customers who have downloaded more than two times.

$*$Hint: You need a group by clause and a having clause that uses the count function,

but you will not retrieve the count. Rather you will retrieve the customer id$.$

Task $10$ Time Between Earliest and Latest Download for Customers with More

than Two Downloads

Write a query to compute the time between the earliest and latest download

times for customers with more than two downloads. Include the customer names

in the result set. $*$Hint: Use the Task $9$ query as a subquery with a having

clause based on cust$\_$id being in the result set of the Task $9$ query.