Databases | SQL | Oracle | MS Access Answers

formulate the following queries in relational algebra

question
1.produce a report of book titles that have been borrowed by "peter bloomfield"

Create read only: VIEW_EMPS_NO_SAL

show all of the data from employee table, department id, department name from departments table Dont show salary and commission percent employees table.

4. Create a view called: VIEW_COUNTRY_REGION_INFO

Show the country id, country name, and region name that each country belongs to. Be sure to use syntax that will allow you to rerun the code without dropping the view and make the view read only.

Create READ ONLY view: VIEW_EMP_SAL_INFO

Calculate the following: The minimum, average, maximum, and sum of all salaries,a count to show records used. Have calculations grouped by department name. need to join with the departments table Give the read only constraint the name of vw_emp_sal_info_readonly.

Run the command to Query the data dictionary USER_VIEWS, showing only the view name and the text columns. Show me the results from your query.

Define and discuss the concept of transitive dependency. Include an example. (

Suppose you have student data that is captured in a table as shown below. Normalize the data in this
table to 3NF.

Student Age Subject
Asongile 15 Afrikaans, Life skills
Avela 14 Life skills
Sibusiso 17. Life skills
____________________________________________________________________

What is SQL, and is SQL a scripting language? What do you have to support your answer?

3. Find audio tracks which have a length longer than the average length of all
the audio tracks;
4. Which playlists do not contain any tracks for the artists “Black Sabbath" nor
“Chico Buarque"?

~~ Tracks is a table
~~ Playlist is a table

Name Region Area Population GDP
Afghanistan South Asia 652225 260000000
Albania Europe 28728 3200000 6656000000
Algeria Middle East 2400000 32900000 75012000000
Andorra Europe 468 64000
Bangladesh South Asia 143998 152600000 67144000000
United Kingdom Europe 242514 59600000 2022824000

Now based on the above table

a) Write a query that shows the name, region and population of the smallest country in each region.

b) Select the code that shows the countries with population smaller than Russia but bigger than Denmark

Write the queries that will do the following:

1. Select the top 10 countries with the most number of ip blocks allocated to it and the number of address blocks in it. Order if from greatest to least.

2. Select the top 10 cities with the most number of ip blocks in the united states and the number of address blocks in it. Order it from greatest to least.

3. Select the cities in the united states that have between 4,000 - 5,000 ip address blocks ordered from least to greatest.

4. Select the average number of ip blocks that would be found in a city for each state. Order it by state from least to greatest or A-Z.

The relational tables are below:

Countries
id(pk)
name
code

CityByCountry
city(pk)
country(fk pointing to id in countries table)
name
state

ip4
ip
country(fk pointing to id in countries table)
city(fk pointing to city in CityByCountry table)

First of all go to https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all.
This is the link to W3Schools MySQL Tryit Editor
and the sample database we are using.

Questions:
1. Write a query that lists the first name and the number of sales of each
employee including the ones that has not received any orders yet.
2. Write a query that calculates and lists the total price of each order.
3. Write a query that lists the name of each different product shipped by
Speedy Express.

How many levels of abstraction a typical database should hold