“SELECT” command: The “SELECT” command is used to retrieve data in a database . Syntax for selecting values from the table is as follows: SELECT STUDENT_ID FROM STUDENT; The given query is used to display each student ID from “STUDENT” table. Subquery: Subquery means one query is placed inside with another query. The inner query is called a subquery. The subquery is calculated first. After the subquery has been determined and the outer query can apply the results of the subquery. Example: The example for subquery is given below: SELECT ORDER_NUMBER FROM ORDER_LINE WHERE BOOK_NUMBER IN(SELECT BOOK_NUMBER FROM BOOKS WHERE CATEGORY = 'CE'); The above query is used to list the order number using subquery. From the given query, the subquery is “(SELECT BOOK_NUMBER FROM BOOKS WHERE CATEGORY = 'CE')”. The subquery is computed first and then outer query is computed. The outer query retrieves the order number on every row in the “ORDER_LINE” table for which the book number is in the results of the subquery. Because that table contains only the book numbers in category “CE”, the results display the preferred list of order numbers. “AVG” function: It is the one function of aggregate function. The “AVG” function is used to compute the average value in a column. Example: The example for “AVG” function is given below: SELECT AVG(MARK_CREDIT) FROM STUDENT; The above query is used to display the average of mark credit from “STUDENT” table using “AVG” function.

Question

List the item ID, description, and on-hand value for each item where on-hand value is at least $1,500. Assign the name ON_HAND_VALUE to the computed column.

Answer 1

Question

Chapter 4, Problem 16TD

Program Plan Intro

“SELECT” command:

The “SELECT” command is used to retrieve data in a database.

Syntax for selecting values from the table is as follows:

SELECT STUDENT_ID FROM STUDENT;

The given query is used to display each student ID from “STUDENT” table.

Subquery:

Subquery means one query is placed inside with another query.

The inner query is called a subquery.

The subquery is calculated first.

After the subquery has been determined and the outer query can apply the results of the subquery.

Example:

The example for subquery is given below:

SELECT ORDER_NUMBER FROM ORDER_LINE WHERE BOOK_NUMBER IN(SELECT BOOK_NUMBER FROM BOOKS WHERE CATEGORY = 'CE');

The above query is used to list the order number using subquery.

From the given query, the subquery is “(SELECT BOOK_NUMBER FROM BOOKS WHERE CATEGORY = 'CE')”.

The subquery is computed first and then outer query is computed.

The outer query retrieves the order number on every row in the “ORDER_LINE” table for which the book number is in the results of the subquery.

Because that table contains only the book numbers in category “CE”, the results display the preferred list of order numbers.

“AVG” function:

It is the one function of aggregate function.

The “AVG” function is used to compute the average value in a column.

Example:

The example for “AVG” function is given below:

SELECT AVG(MARK_CREDIT) FROM STUDENT;

The above query is used to display the average of mark credit from “STUDENT” table using “AVG” function.

Expert Solution & Answer

Trending nowThis is a popular solution!

See solution

Check out sample textbook solution

Students have asked these similar questions

List the item ID, description, and on-hand value for each item where on-hand value is at least $1,500. Assign the name ON_HAND_VALUE to the computed column.

Show all name and phone numbers of students and the type of donation they received including any unmatched student.table name;StudentDonationVolunteer

List the owner number and last name for all owners located in the city of Seattle. Your query should ignore case. For example, a customer with the city Seattle should be included, as should customers whose city is SEATTLE, SEAttle, SeAttle, and so on. StayWell is offering a monthly discount for residents who pay their rent on a quarterly basis. The discount is 1.75 percent of the monthly fee. For each property, list the office number, address, owner number, owner’s last name, monthly rent, and discount as DISCOUNT. The discount should be rounded to the nearest dollar.