Em Andamento

Oracle Database Express Assignment

In these exercises, you query the Centrepointe church database.

Exercise 3.1

If possible, write SQL queries to do the following.

[url removed, login to view] the cross-product of all person and all household records.

[url removed, login to view] the people who have birthdays specified in the database, ordered by day-of-year.

Note that you can compute the day of year value using the Oracle function: TO_CHAR(birthdate, 'DDD').

Here are some further query exercises that use nested queries, both correlated and non-correlated, tuple variables and set operations. Oracle syntax is not always identical to the ANSI standard used in the text; you can find the Oracle SQL reference linked to the course " .

Exercise 3.2

Write queries on the Centrepointe database that do the following.

[url removed, login to view] the youngest person in the database. Write this both as a sub-select and as a ROWNUM query (see the notes below).

You should’t need to use aggregate functions like MAX() to solve this problem; we cover those in the next unit.

[url removed, login to view] the IDs and full names of people who share the same first name. What happens when there are three or more people who share the same name?

[url removed, login to view] the names of all people who are on the music team but not in Byl’s home group. Write this both as a sub-select and as a set operations query.

Go back and indicate which of your nested selects are correlated and which are not correlated.

We can use Oracle’s “magic” column named ROWNUM to limit our query results (cf. TOP() in mySql). Use ROWNUM to retrieve the first record of a SELECT result as follows.

SELECT *

FROM (SELECT * FROM table(s) WHERE condition(s) ORDER BY specification(s))

WHERE ROWNUM = 1;

Because Oracle assigns the row numbers for the resulting table before applying the ORDER BY clause (and the SELECT, GROUP BY and HAVING clauses as well), you need to encapsulate the selection and ordering inside an inline view, i.e., a sub-select in the FROM clause, and then select the first row number of the resulting table. For more details, see On ROWNUM and Limiting Results.

Exercise 3.3

If possible, write SQL queries to do the following.

[url removed, login to view] the names and mandate statements of all teams along with the ID of their “chair” member. If a chair member does not exist, include NULL for the ID. Write this query in two ways, one using outer joins and the other using set operations.

[url removed, login to view] you’re looking for a challenge, modify the previous query to return the chair person’s full name instead of just their ID.

Now, consider aggregation and grouping.

Exercise 3.4

If possible, write SQL queries to do the following.

[url removed, login to view] the average age of all the people in the database. Note you can use the following Oracle features in this query.

months_between(laterDate, earlierDate) computes the number of months between the earlier and later dates.

SYSDATE gives the current date.

TRUNC() rounds a number down to the nearest integer.

This query uses an agregate function, but is it doing grouping? If so, what is the group? If not, explain what it’s doing instead?

[url removed, login to view] the household ID and count of members of all households in Grand Rapids having at least 2 members. Order the results by decreasing size.

[url removed, login to view] the previous query to retrieve the phone number of the household as well.

You may need to add a few carefully chosen sample data items to ensure that your queries actually return something.

Must be done on oracle database express, and include SQL statements and answers on a separate document.

Habilidades: MySQL, Oracle, SQL

Ver mais: what is specification document, what is express, what is an assignment problem, what are problem statements, top in oracle sql, top function in oracle sql, top function in oracle, top 1 sql oracle, the select group, the assignment problem, team express, sql top oracle, sql oracle top, sql oracle get date, sql function in oracle, solve the assignment problem, solve assignment problem, sample problem statements, problem statements, problem assignment, order express, oracle functions sql, named query, assignment solve, assignment problem in or

Acerca do Empregador:
( 3 comentários ) San Bernardino, United States

ID do Projeto: #10484155

Premiar a:

davedcb

Hello, I am interested in your project. I am a software engineer and I have professional experience with Oracle SQL. Thank you, Best regards, David

$22 USD em 3 dias
(3 Avaliações)
1.8

15 freelancers estão ofertando em média $52 para este trabalho

eperfections

I have 10+ years experience. I am the BEST programmer on this platform. Ready to start immediately.

$100 USD in 3 dias
(175 Comentários)
6.9
truongnguyen86

Hello there, i'm expert on Oracle database, both designing and writing complex queries, i've done many complex report before. You can check my profile to see what i've done in this field. Waiting for your feedback. Tha Mais

$100 USD in 2 dias
(125 Comentários)
6.2
vinaysinghh

I am Oracle DBA having more then 6 yrs of experience. I can do this task with perfection

$45 USD em 1 dia
(23 Comentários)
4.6
scorpioangry55

You can contact with me at any moment to discuss your task. You can contact with me at any moment to discuss your task.

$100 USD in 2 dias
(10 Comentários)
4.6
point5nyble

Hello, I have been working with a MNC based company since last ~ 5 years, as an IT BI DWH Professional. The project is a BI DWH project which covers 3 layers of BI (ETL, Data warehouse & Reporting) and helped me in Mais

$55 USD in 2 dias
(16 Comentários)
4.0
ref2

Hi I have 15+ years of experience in developing applications on oracle database. I have bachelor degree in cs, 25 oracle certified trainings and web app dev certificate from University in Chicago. Your project is we Mais

$45 USD em 1 dia
(16 Comentários)
3.8
dekinghtg

hi, i can do this for you.

$30 USD in 5 dias
(7 Comentários)
3.6
rashmisingh2990

I am database expert having experience of 3 years.I will complete your work as per requirements and deliver it on time.

$50 USD em 1 dia
(16 Comentários)
3.6
zvonimircarek

I work on Oracle DB for more than 6 years. So I am not some hobby Oracle developer or a student with 3 months experience. Your task is simple in terms that it is just a school assignment, but based on number of tasks i Mais

$70 USD in 2 dias
(3 Comentários)
2.1
ZishanChuhan

I am expert in SQL, Oracle Certified Professional, Can do the task as per your requirements with complete accuracy.

$30 USD in 3 dias
(3 Comentários)
1.9
jitendra2013

Hello I am software developer of php technologies , I have 6+ Exp in php and different technology, [url removed, login to view] , Wordpress, Joomla, Mengendo.etc.. [url removed, login to view] Technologies 3. C, C++ , Core Java , Advanced Java , Python 4. Mais

$25 USD em 1 dia
(5 Comentários)
1.6
shantailarry

I am a developer focusing particularly on database management and the like. I have used Oracle before and can therefore guarantee you that I can accomplish this task to your expectations and submit within your require Mais

$35 USD em 1 dia
(3 Comentários)
1.6
$55 USD em 1 dia
(0 Comentários)
0.0
techminds4

Dear Prospect Hiring Manager. Thank you for giving me a chance to bid on your project. i am a serious bidder here and i have already worked on a similar project before and can deliver as u have mentioned I have c Mais

$25 USD em 1 dia
(0 Comentários)
0.0
jasjayasinghe

I am a master degree holder with good database knowledge, i will do this for you. further information contact me via my skype : samantha sanjeewa jayasinghe

$25 USD in 2 dias
(0 Comentários)
0.0