Many model biological organism database projects created a standard, shared sche

Question

Many model biological organism database projects created a standard, shared schema for storing their projects' information, and it is called Chado. The complete schema is outside of the scope of this class, but for this problem you'll need to use two tables from it:

feature: This is a highly 'normalized' schema, and all biological feature types are stored in this same table. The type_id column distinguishes them by giving each a different numerical type.

cvterm: Each of the numerical types from the feature table are given more user-friendly text-based names in this table. The cvterm_id field of this table can be directly linked to the type_id field of the feature table.

Login to MySQL using your user name and connect to the 'jorvis_chado' database. Write an SQL query which lists the types of features in the feature table along with a count of each. Your output should look like this (using column aliases to match mine):

+¬¬¬¬¬¬¬¬¬¬¬¬¬¬--------------+¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬---------------+
| feature type | feature count |
+¬¬¬¬¬¬¬¬¬¬¬¬¬¬--------------+¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬---------------+
| assembly | 1 |
| exon | 4200 |
| gene | 4200 |
| mRNA | 4200 |
| polypeptide | 4200 |
+--------------¬¬¬¬¬¬¬¬¬¬¬¬¬¬+¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬---------------+

Explanation / Answer

Select feature.name as "feature type" , count(cvterm.type_id) as "feature count" from feature inner join cvterm on feature.type_id = cvterm.type_id group by feature.name;

The two tables need to be joined to extract information using type_id. For the feature name in feature table ,it is counting the featureids in cvterm table

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.