Databases | SQL | Oracle | MS Access Answers

1- Creating Sequences.
a. Connect as the DBA. Create a sequence named VENDOR_SEQUENCE that starts with the value 1 and is incremented by 1. Set the values for MINVALUE = 1 and MAXVALUE = 99999. Allow the sequence to CYCLE automatically and allocate NOCACHE as a sequence option.
b. Attempt to SELECT a value from the VENDOR_SEQUENCE by using the DUAL table in your SELECT command.
2- a range-based partitioning table named p_student with attributes empno,empname,job,salary,department no. Make sure that the data entry of the each department goes to its own provided tablespaces such as the cs department goes to the dept10ts tablespace, the data entry of the cis department goes to the dept20ts tablespace, OTHERS goes to other_dept tablespace. specify the storage size parameter .
3- have the parallel query option, then the fastest method to create a table that has the same columns as the p_STUDENT table, but only for those student in department CS.

The table:

_date(2014-03-05,2014-03-05,2014-03-05,2014-03-05,2014-03-05,2014-03-05,2014-03-05,2014-03-05,2014-03-05,2014-03-05,2014-03-05,2014-03-05,2014-04-06,2014-04-06,2014-04-06,2014-04-06,2014-04-06,2014-04-06,2014-04-06,2014-04-06,2014-04-06,2014-04-06)
_period(1,2,3,4,5,1,2,3,4,5,1,2, 1,2,3,4,5,1,2,3,4,5)
_id(x1,x1,x1,x1,x1,x2,x2,x2,x2,x2,x3,x3,x4,x4,x4,x4,x4,x5,x5,x5,x5,x5)
_reading(1,1,1,1,1,2,2,2,2,2,5,6;1,1,1,1,1,2,1,1,1,1)
_reading2 (5,5,5,5,5,4,4,4,4,4,7,7;1,1,1,1,1,1,1,1,1,1)

This should be the answer:

_date(2014-03-05;2014-04-05)
sum_reading(15;11)
sum_reading2 (9;2)

I have this query:

SELECT A._date, sum(_Reading) as _Reading,
sum(Distinct _Reading2) as _Reading2
FROM table A
INNER JOIN (SELECT _ID, _date
FROM table
GROUP BY _ID, _date
HAVING count(_Period) = 5) B
on A._ID = B._ID
and A._Date = B._Date
GROUP BY A._Date

but the result is wrong.

I have a table:
_date(2014-03-05;2014-04-05)
_period(1,2,3,4,5,1,2,3,4,5,1,2; 1,2,3,4,5)
_id(x1,x1,x1,x1,x1,x2,x2,x2,x2,x2,x3,x3;x4,x4,x4,x4,x4)
_reading(1,1,1,1,1,2,2,2,2,2,5,6;1,1,1,1,1)
_reading2 (5,5,5,5,5,4,4,4,4,4,7,7;1,1,1,1,1)

i need a result like below which eliminates all data which doesn't have full period 1 to 5 for the _id, then calculates the sum of _reading for a day and sum of _reading2 for each distinct _id for a day:

_date(2014-03-05;2014-04-05)
sum_reading(5,10; 5)
sum_reading(15; 5)

As i had done my higher schooling. And want to become data analyst in future. can you please guide me what should I study or which course i opt for this ?

Pretend you have been given a 20GB file with data on patients admission. The file includes diagnoses, problem list, date of admission, past conditioon.
Which of the below is the BEST approach to create descriptive stats of the admissions?

**
.
a. That file should be loaded into a DB system and queried before you analyze it in a statistical package

b..That file can only be analyzed in the cloud

c.. There isnt any sufficient data in the file to characterize admissions


Stuck between A and D on this one..or maybe its not either of them? I still am very unsure..anyone know?

Which of the query below list the most expensive service received by each patient?

A. SELECT service, max(cost)
FROM services
GROUP BY service

B. SELECT patientID, max(cost)
FROM services
GROUP by service

C. SELECT patientID, service
FROM services
where cost =
(SELECT max(s.cost) as mc
FROM services s WHERE s.patientID = services.patientID)

D. SELECT patientID, service
FROM services
GROUP BY patientID
HAVING cost = mac(cost)

What does this query mean?

SELECT admissionID, sum(cost)
FROM services
WHERE admissionID > 100 and cost > 50
GROUP BY admissionID
HAVING sum(cost) > 200

A. Admissions with ID greater than 100, cost greater than 50 per service, and total cost per admission greater than 200

B. First 100 admissions with cost greater than 50 and total cost per patient greater than 200

C. Admissions with ID greater than 100, cost greater than 50 per admission, and total cost per patient greater than 200

D. Admissions with ID greater than 100, and total cost higher than 200 for services with individual costs greater than 50