To understand and be able to write simple SQL queries.
Tools for the assignment
For this assignment, you will use your SQL Server / MYSQL.
PART I: Database 1: You can create database table and insert some data in these tables with query .
Write SQL queries to answer the following:
How many European customers have a balance that is less than $8000.
Which region (Aisa, Europe...) supplies the most distinct parts.
What is the average time for shipping items by TRUCK. FOLLOW UP. List the quantity and orderkey of all the items shipped by TRUCK which takes less than the above time
List all the items which have NO discount and not sold to customer in EUROPE
List name(s) of the customer(s) who has not place any orders.
List the (distinct) items which are ordered by the customers from the country where suppliers have the highest average balance
Find the average price of all the orders which contain no parts with size larger than 40
List the name of the suppliers which supplies more than 5 parts along with the number of parts their supplied
Find the names of all the distinct parts which receive the highest discount
PART II: database 2:You can create database table and insert some data in these tables with query .
List the genre of tracks which is contained in the most playlist
Find audio tracks which have a length longer than the average length of all the audio tracks
Which playlist(s) contain the largest number of pop tracks
Find the number of employees live in the same city with each customer, sorted by descending order 5. Which artist(s) has the most tracks which can be classified to Jazz
Find the name of the German customer(s) who has paid the most in total without company name
List the name and age of the employees who support more than 5 customers (Hint: You can use GETDATE() function to get the current date, and use an other function from last assignment to calculate ages)
Find the manger who manages most employees but also being managed by someone else (Note: there are employees who do not have managers, i.e., there may be NULL values in ReportsTo column)
List the name of the artists with more than 5 tracks
Find the playlist(s) which contains most tracks by artist ”AC/DC”
We have top rated database experts that can do your work with complete explanation as per your given requirement details.
for more details you can contact us at: