SQL Program to Solve IT Asset Management Assignment Solution

/* Ex 3 Queries from a Business Purpose */

/* In Ex 1 you inserted data into the IT Asset Management (ITAM) schema tables

of user jaherna42. In Ex 2 you wrote wrote select command with explicit join

syntax. The tasks for this exercise refer to the ITAM tables of user jaherna42

schema on the class server.

Before a task is presented to you here, an example from the Murach book is

given that is somewhat similar (structurally) to the query you need to write

for the task. The example from Murach refers to the AP tables of user ap or

user jdoe22. The task that follows the example refers to the ITAM business

process and schema.

The deliverable for this exercise is a Word document named

ex3_lastname_firstname_section#.docx. Put your name and the exercise number

(Ex 3) as a heading in a Word document as you begin the exercise.*/

/*Here in the exercise instructions, you are prompted to (1) complete tasks,

(2) take screen shots that document your hands-on work and insert them into

the deliverable document, and/or (3) write something in the deliverable

document. Use a tool to capture just the portion of the screen you need to

document your work.

Whenever you capture work with a screenshot, include your name in the

screenshot. This guideline applies to any screenshots you take to document your

hands-on work throughout the semester. You may not get credit for something if

you do not include your name as requested.*/

/*Connect to the class server using your assigned username.*/

/* For each task, do not get data from the it_asset_inv_summary table

unless the task specifically asks you to do so. */

/*Example 1: Murach Chapter 4 #3 tells you to write a SELECT statement that

returns three columns from two different tables. A business reason that a

manager might state for the query is, "I need a list of vendors and a

description of what type of product they normally sell us. By that I mean, you

know, the description of how the things we usually buy from them are categorized

in our accounting system." */

select v.vendor_name, gla.account_description

from jdoe22.vendors v join jdoe22.general_ledger_accounts gla

on v.default_account_number=gla.account_number;

/* Task 1. Your manager says, "I need a list of all our employees and their cell

mobile phone numbers. Put the list in alphabetical order by their last name

followed by their first name initial. Then put their cell number and, last, put

which department they work in. At the top of each column put Employee Name, Cell

No., and Department."

In addition to your name and your query, your screenshot should include the

All Rows Fetched: n in x.xxx seconds response from Oracle SQL Developer. Make

sure n is the total number of rows fetched by scrolling to the end of the

returned data. Include about 5 records of returned data (does not have to be

exactly 5). */

--Task 1 Employee List

--Type Your Name Here

/* Task 2. Your manager says, "Get me descriptions of different IT assets we

have in our ITAM system that are signed out to at least one employee so they can

use it to do their job. I'm not talking about heavy-duty IT stuff that's not for

one person to use, like servers and routers. Just IT stuff that people use to

make sales, or do accounting, or something. I need to know the make and model

of the asset and what type of thing it is, like is it a computer or is it

software. Also, if the same asset described is used by more than one person, I

need the asset information only once in the list."

In addition to your name and your query, your screenshot should include the

All Rows Fetched: n in x.xxx seconds response from Oracle SQL Developer and it

should show the first record retrieved with about 5 records that follow it (if

there are at least 5 records retrieved). */

--Task 2 Asset Description for Assets in Use

--Type Your Name Here (there could be variation in which columns are selected

/*Example 3: Murach Chapter 5 #3 tells you to write a SELECT statement that

employs aggregate functions. A business reason that a manager might state for

needing the query is, "I need a list of vendors who have sent us invoices over

the years, with a count of how many invoices they have sent, and the grand total

of the invoice amounts for each vendor. "*/

SELECT vendors.vendor_name, COUNT(*) as invoice_qty,

SUM(invoices.invoice_total) FROM

jdoe22.invoices JOIN jdoe22.vendors

ON invoices.vendor_id = vendors.vendor_id

GROUP BY vendors.vendor_name

ORDER BY COUNT(*);

/* Task 3. Your manager says, "I would like to know how many Lenovo computers

employees are being supported by our IT people and what is the description of

the Lenovos in use? By description I mean I would like to know the make and

model of the Lenovo for each count. "

In writing your query, you know that the derived information in the

it_asset_inv_summary table is not kept up to date in an automated way, so you

need to use only non-derived data from other tables to determine the count(s).

In addition to your name and your query, your screenshot should include the All

Rows Fetched: n in x.xxx seconds response from Oracle SQL Developer and it

should show the first record retrieved with about 5 records that follow it

(if there are at least 5 records retrieved). */

--Task 3 Lenovos Supported

--Geogianna Washington

select count(cii.asset_desc_id),

iad.asset_make,iad.asset_model,iad.asset_ext,

c.cpu_details, c.graphics,c.vol_memory --optional and could inclue others from computer

from jaherna42.employee_ci eci

join jaherna42.ci_inventory cii

on eci.ci_inv_id = cii.ci_inv_id

join jaherna42.it_asset_desc iad

on cii.asset_desc_id = iad.asset_desc_id

join jaherna42.computer c on c.asset_desc_id = iad.asset_desc_id

where upper(iad.asset_make) like '%LENOVO%'

and eci.use_or_support = 'SUPPORT'

group by iad.asset_make,iad.asset_model,iad.asset_ext,

c.cpu_details, c.graphics, c.vol_memory;

/*Example 4: Murach Chapter 6 #3 tells you to write a SELECT statement that

uses a subquery in the where clause with a NOT EXISTS operator. A business

reason that a manager might state for needing the query is, "I need to know

all the expenditure categories they use in accounting that have never been

used to categorize something we have received an invoice for - whether we

have paid for it yet or not.*/

SELECT account_number, account_description

FROM jdoe22.general_ledger_accounts gla

WHERE NOT EXISTS (SELECT * FROM

jdoe22.invoice_line_items ili

WHERE gla.account_number = ili.account_number)

ORDER BY account_number;

/* Task 4. Your manager says, "I need a list of the make, model, and other

information for computers we have in inventory that are spares. By spare, I mean

that no one is currently assigned the computer as the one they use to do their

job. I also do not want a computer in the list if it is broken.

In addition to your name and your query, your screenshot should include the

All Rows Fetched: n in x.xxx seconds response from Oracle SQL Developer and it

should show the first record retrieved with about 5 records that follow it (if

there are at least 5 records retrieved).*/

--Task 4 Spare CIs

--Type Your Name Here (using subquery)

/* Task 5. From the ITAM tables in the jaherna42 schema, answer this question

from a manager, "Are there computers that we can use as loaners for employees

when their assigned computer is in for repair or upgrade? In particular, we

need to know if we have some Lenovos or Dells on hand that are spare and how

many of each brand we have on hand".

While you might be able to answer the manager's question by looking at data in

the it_asset_inv_summary table, that derived data is still not kept up to date

through automation. Thus, you need to use only non-derived data from other

tables to determine the count(s).

In addition to your name and your query, your screenshot should include the

All Rows Fetched: n in x.xxx seconds response from Oracle SQL Developer and it

should show the first record retrieved with about 5 records that follow it

(if there are at least 5 records retrieved).*/

--Task 5 Spare Lenovos and Dells

--Type Your Name Here

/* Task 6. Now write a query that retrieves the same information that

requested by Task 5, only this time use the summary information in

it_asset_inv_summary table to get the counts. Note that since there is no

automation in place that keeps the derived information in it_asset_inv_summary

table current, the data returned by the Task 6 query could be different from

the data returned by the Task 5 query.

In addition to your name and your query, your screenshot should include the

All Rows Fetched: n in x.xxx seconds response from Oracle SQL Developer and it

should show the first record retrieved with about 5 records that follow it

(if there are at least 5 records retrieved).*/

--Task 6 Spare Dells and Lenovos

--Type Your Name Here

/* Task 7. Use the union operator to join the results from the Task 5 query

with the results from the Task 6 query for an easier comparison of the data

returned. Add a first column to each column list that indicates which query

(Task 5 or Task 6) generated the retrieved data. To do this, just add:

'Task 5' as "Which Task" as the first column for the Task 5 query and add:

'Task 6' as "Which Task" as teh first column for the Task 6 query.*/

--Task 7 Compare Spare Dells and Lenovos Results

--Type Your Name Here

/* Task 8. Retrieve the employee name, asset_description, and date_assigned,

for all assets assigned to an employee for use after 11 AM on January 12 of 2020

year. For this task, hard code the date to be compared to. */

--Task 8 Assets Assigned After a Specific Date

--Type Your Name Here

/* Task 9. Retrieve the employee name, asset_description, and date_assigned,

for all assets assigned to an employee for use where the assignment happened

in the month of April in any year. */

--Task 9 Assets Assigned in One Month of Any Year

--Type Your Name Here

/* Task 10. Retrieve the employee name, asset_description, and date_assigned,

for all assets assigned where the assignment happened on a specific date of

your choosing. Your query must retrieve all rows for the specific date,

regardless of the time the assignment got recorded on that date.*/

--Task 10 Assets Assigned on a Specific Date

--Type Your Name Here