StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
10466568
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
1
CommunityOwnedDate
CreationDate
2012-05-05T22:40:24.037
FavoriteCount
0
LastActivityDate
2012-05-05T22:46:13.443
LastEditDate
2017-05-23T12:34:01.780
LastEditorUserId
-1
OwnerUserId
0
ParentId
10466270
PostTypeId
2
Score
11
ViewCount
0
LastEditorDisplayName
user756519
text
Body
Since this is a homework assignment, I would like to provide some input on how to design the tables in a proper manner. Just my two cents. <h3>Design suggestion</h3> I would suggest that you alter the table design. All your tables essentially contain the same data: <ul> <li>a number</li> <li>name</li> </ul> Instead of having multiple tables, you should design your database something along this line that would include only two tables. <pre><code>Department ---------- dept_no INTEGER dept_name VARCHAR(30) Person ------ person_no INTEGER person_name VARCHAR(30) dept_no INTEGER </code></pre> <h3>Create tables with constraints</h3> You should design tables with appropriate constraints. Here are few things to note. <ul> <li>The constraint <code>PRIMARY KEY</code>, as the name states, would create a primary key on the table to keep the data unique so that you don't end up with multiple ids with the same value.</li> <li>The constraint <code>FOREIGN KEY</code> is creating a relation between the tables Department and Person.</li> <li>For this example, these keys may not be necessary but in real world applications it is always a best practice to define proper relations using the foreign key constraints. There are lots of other things you can search about <code>PRIMARY KEY</code> and <code>FOREIGN KEY</code> on the web.</li> <li>You cannot switch the order in which these tables are created. In other words, you cannot create Person table first and then the Department table. The reason is that Person table is creating a constraint that references the Department table. If you create the Person table first, you will get the error <code>Failed: ORA-00942: table or view does not exist</code>.</li> <li>You can also make the <code>dept_no</code> and <code>person_no</code> to be auto incremented numbers so that you don't have to manually insert those numbers. I use SQL Server. So, I am not much familiar with Oracle syntax to make a column to auto incremented number. Search for sequence number for Oracle, you might find something.</li> </ul> Script: <pre><code>CREATE TABLE Department ( dept_no INTEGER , dept_name VARCHAR(30) , CONSTRAINT pk_department PRIMARY KEY (dept_no) ); CREATE TABLE Person ( person_no INTEGER , person_name VARCHAR(30) , dept_no INTEGER , CONSTRAINT pk_person PRIMARY KEY (person_no) , CONSTRAINT fk_person_dept FOREIGN KEY (dept_no) REFERENCES Department (dept_no) ); </code></pre> <h3>Populate the table</h3> <ul> <li>Below given script first populates the Department table and then populates the Person table.</li> <li>It is actually inserting multiple values into the table at the same time instead of calling INSERT INTO for every single row. </li> <li>dual is a dummy table in Oracle with single row. <a href="https://stackoverflow.com/questions/73751/what-is-the-dual-table-in-oracle">Read here in this SO answer about dual.</a></li> </ul> Script: <pre><code>INSERT ALL INTO Department (dept_no, dept_name) VALUES (1, 'Cleaner') INTO Department (dept_no, dept_name) VALUES (2, 'Driver') INTO Department (dept_no, dept_name) VALUES (3, 'Mechanic') SELECT * FROM dual; INSERT ALL INTO Person (person_no, person_name, dept_no) VALUES (1, 'Cleaner 1', 1) INTO Person (person_no, person_name, dept_no) VALUES (2, 'Cleaner 2', 1) INTO Person (person_no, person_name, dept_no) VALUES (3, 'Cleaner 3', 1) INTO Person (person_no, person_name, dept_no) VALUES (4, 'Cleaner 4', 1) INTO Person (person_no, person_name, dept_no) VALUES (5, 'Driver 1', 2) INTO Person (person_no, person_name, dept_no) VALUES (6, 'Driver 2', 2) INTO Person (person_no, person_name, dept_no) VALUES (7, 'Driver 3', 2) INTO Person (person_no, person_name, dept_no) VALUES (8, 'Mechanic 1', 3) INTO Person (person_no, person_name, dept_no) VALUES (9, 'Mechanic 2', 3) INTO Person (person_no, person_name, dept_no) VALUES (10, 'Mechanic 3', 3) INTO Person (person_no, person_name, dept_no) VALUES (11, 'Mechanic 4', 3) INTO Person (person_no, person_name, dept_no) VALUES (12, 'Mechanic 5', 3) INTO Person (person_no, person_name, dept_no) VALUES (13, 'Mechanic 6', 3) SELECT * FROM dual; </code></pre> <h3>How to get my data grouped by department?</h3> <ul> <li>Now that you have the table and data, it is time to query the information.</li> <li>Your requirement is to get the list of all the departments and the number of people in each department.</li> <li>If you run the following query, you will simply get the list of departments but that is not what you need.</li> </ul> Simple Select: <pre><code>SELECT dept_no , dept_name FROM Department; </code></pre> Output: <pre><code>DEPT_NO DEPT_NAME ------- --------- 1 Cleaner 2 Driver 3 Mechanic </code></pre> <ul> <li>So, you might ask 'How can I get that information?'. Well, that information is in the table Person. So, we need to join both the tables to find the desired data. We will use <code>INNER JOIN</code> to join both the tables on a common field. In this case, the common field in both the tables is dept_no. </li> </ul> <h3>Query that will give you the desired output</h3> Script: <pre><code>SELECT d.dept_no , d.dept_name , COUNT(p.person_no) AS No_of_employees FROM Department d INNER JOIN Person p ON p.dept_no = d.dept_no GROUP BY d.dept_no , d.dept_name ORDER BY d.dept_no; </code></pre> Output: <pre><code>DEPT_NO DEPT_NAME NO_OF_EMPLOYEES ------- --------- --------------- 1 Cleaner 4 2 Driver 3 3 Mechanic 6 </code></pre> <h3>Explanation</h3> <ul> <li>The query uses quite a few things like INNER JOIN, GROUP BY, COUNT and ORDER BY. Let's look each one of these.</li> <li>INNER JOIN joins the tables based on the common field, in this case dept_no.</li> <li>COUNT function would allow the query group all the count of employees by their department number and department name.</li> <li><code>COUNT</code> is an aggregate function. When you use an aggregate function with non-aggregate columns, then you need to use GROUP BY clause. Here the dept_no and dept_name are non-aggregate columns. <code>SUM</code>, <code>MAX</code>. <code>MIN</code> are some of the aggregate functions.</li> <li>Finally, we apply the ORDER BY clause to sort the output by dept_no column.</li> </ul> <h3>Demo</h3> <a href="http://sqlfiddle.com/#!4/c83fc/5" rel="nofollow noreferrer">Click here to view the demo in SQL Fiddle.</a>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POHow do I create a view with multiple aggregate functions count and sum?
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USCommunity
UserOwnerUserId
1. This table or related slice is empty.
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POHow do I create a view with multiple aggregate functions count and sum?
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId
1. COGr8 input! It is shame that I cannot assign any points - many thanks. I simplified my question little bit regarding structure of DB and I have "some level of awareness" of what is probably bad practise.
 singulars
 PostPostId
 PO
 UserUserId
 USJestem_z_Kozanowa

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.