Databases | SQL | Oracle | MS Access Answers

1.      Display the highest, lowest, sum and average UnitPrice of each Category. Label column as CategoryId, Maximum, Minimum, Sum and Average, respectively. Round your results to the nearest whole number. (Table: Products)

2.      Display the highest, lowest, sum and average UnitPrice of each Category, where highest UnitPrice lies in the range of 50$ to 100$. Label column as CategoryId, Maximum, Minimum, Sum and Average, respectively. (Table: Products)

3.      From customers table, Count all customers is each region where region is not null. (Table: Customers)

4.      Write a query to display the number of ContactName with same ContactTitle. Sort contact title in descending order. (Table: Customers)

5.      Write a query that count all orders against each product id. No of orders should be greater than 50. (Table: [Order Details])

How many 5MB mp3 files can be stored on a 1GB flash drive

Create a stored procedure that will displayed only the countries in Asia. Provide me the sql file or screenshot per commands and show the BEFORE and AFTER of the data

Dineo has heard that there are alot difference kinds of database out there and she’s trying to figure out what the differences are

Create an inforgraphic illustration the four different ways in which DBMS can ble classified. include the discription of each classification and example

Write a query which displays staff name, department code, department name, and salary for all staff who earns more than 20000

Get the number of reactions (likes/dislikes) generated in each hour of the day in the year 2020.

Note:

For this question, convert thehour_of_dayin string datatype to INT datatype.

Sort the output in the ascending order ofhour_of_day

Write a basic procedure to list countries in Asia.

Create a database with the names of the countries (please refer to this link: https://www.newworldencyclopedia.org/entry/List_of_countries_by_continent ) and tables such as Country Name and Continent.

Create a stored procedure that will displayed only the countries in Asia.

Provide me the sql file or screenshot per commands and show the BEFORE and AFTER of the data.

Case 2: JOB APPLICATION

DBD Sports has decided to allow applicants to apply for jobs online. Using the following business rules, create an ERD:

1. When a person applies for a job, the following information is required:

a. Full Name b. Address c. Phone

d. Email

2. An open position is assigned to a job category.

3. A person may apply for more than 1 job, but can apply for each job only once.

4. Each job applicant is assigned to an HR representative who processes the application and helps the applicant with any

questions.

5. Once an HR representative has been assigned to a job applicant, the same HR representative will be assigned to the

job applicant each time he/she applies for a job.

6. Job applications older than 1 year will be deleted.

7. Applications for an open job will be accepted until the position is filled.

Get the number of reactions (likes/dislikes) generated in each hour of the day in the year 2020.

Note:

• For this question, convert thehour_of_dayin string datatype to INT datatype.
• Sort the output in the ascending order ofhour_of_day

Expected Output Format

hour_of_day no_of_reactions

Get videos from TEDx channel (id=353) with more than 50 thousand views.

Note:

Sort the output in the descending order of

no_of_viewsand in the ascending order of videonameExpected Output Format

video_id name duration_in_sec sno_of_views