ZuoTi.Pro
Question

Part II: Group-by and subqueries The following questions use the Spy and Sailors database. Write the...

Part II: Group-by and subqueries The following questions use the Spy and Sailors database. Write the SQL for the following queries. 1) List the sailor id and name for each sailor who has reserved two different red boats. Make sure that each sailor id/name is listed at most once. You must use group by/having.

2) List the missions and their status for each team.

3) For each country in agent, list the country and the count of agents in the country, the min, max, and average salary for all agents who are from the country.

4) List the language and the count of the number of agents with the skill of “Lip Reader” who speak that language.

5) Return the average salary and country for which the agents have the lowest average salary.

6) List the agent id, first, middle, and last provided the number of languages that they speak is four times or more larger than the number of skills they have. For example, if an agent speaks 4 languages and has 1 skill, then that agent id belongs in this query answer. If another agent speaks 10 languages and has 2 skills, then that agent id belongs in this query answer.

engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Request Professional Answer

Request Answer!

We need at least 10 more requests to produce the answer.

0 / 10 have requested this problem solution

The more requests, the faster the answer.

Request! (Login Required)

All students who have requested the answer will be notified once they are available.
Know the answer?
Add Answer to:
Part II: Group-by and subqueries The following questions use the Spy and Sailors database. Write the...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Similar Homework Help Questions

  • Project Steps: 1. Database Design 1.1. Design and create a database in third normal form based...

    Project Steps: 1. Database Design 1.1. Design and create a database in third normal form based on the following requirements: • Each Job is for a specific customer and there can be more than one job per customer. • The name and address must be tracked for each customer. • Each job is described by up to 2000 characters. • Each job has a status of ‘open, ‘in process’, or ‘complete’. • Each job has a start date and end...

  • Plz someone answer my database questions post Exercise I (50 pts): This exercise deals with a...

    Plz someone answer my database questions post Exercise I (50 pts): This exercise deals with a database that stores information abhout Hotel Management System. Customer (C email, name, country) Payment (Invoice id. C email, payment method, date) Invoice (Invoice id, starus, invoice description) Has (Bill id, Invoice id) Bill (Invoice id, Bill id, amount, Bname, type, date, reservation id) Reservation (Hotel id., room id. C email, date, period, reservation id) Rooms(Hotel id, room id. price, category) lotel (Hotel id, H...

  • Plz someone answer my database questions post Exercise I (50 pts): This exercise deals with a...

    Plz someone answer my database questions post Exercise I (50 pts): This exercise deals with a database that stores information abhout Hotel Management System. Customer (C email, name, country) Payment (Invoice id. C email, payment method, date) Invoice (Invoice id, starus, invoice description) Has (Bill id, Invoice id) Bill (Invoice id, Bill id, amount, Bname, type, date, reservation id) Reservation (Hotel id., room id. C email, date, period, reservation id) Rooms(Hotel id, room id. price, category) lotel (Hotel id, H...

  • Write following queries given these schemas: (FK stands for Foreign Key) Customer = {customerID, firstName, lastName,...

    Write following queries given these schemas: (FK stands for Foreign Key) Customer = {customerID, firstName, lastName, income, birthDate} Account = {accNumber, type, balance, branchNumberFK-Branch} Owns = {customerIDFK-Customer, accNumberFK-Account} Transactions = {transNumber, accNumberFK-Account, amount} Employee = {sin, firstName, lastName, salary, branchNumberFK-Branch} Branch = {branchNumber, branchName, managerSINFK-Employee, budget} Focus should be on using: Order By to sort data Set Operators to union/intersect multiple tables Join Operator to join multiple tables Aggregations and Group By to aggregate data Subqueries 3.8 SIN, first name,...

  • May I ask the SQL code as follows? The relational database moviedb has the following database...

    May I ask the SQL code as follows? The relational database moviedb has the following database schema: Movie(title, production year, country, run time, major genre) primary key : {title, production year} Person(id, first name, last name, year born) primary key : {id} Award(award name, institution, country) primary key : {award name} Restriction Category(description, country) primary key : {description, country} Director(id, title, production year) primary key : {title, production year} foreign keys : [title, production year] ⊆ Movie[title, production year] [id]...

  • Write the following questions as queries in SQL. Use only the operators discussed in class (in...

    Write the following questions as queries in SQL. Use only the operators discussed in class (in particular, no outer joins are allowed). Please use renaming consistently! The following database schema is given: RESEARCHER(rid,name,institution,city,country) PAPER(title,journal,volume,number,year) AUTHOR(resid,title) where rid is the identifier (primary key) of RESEARCHER, name is the researcher’s name, institution is where the researcher works, and city and country the city and country where the institution is located; title is the paper identifier (primary key) of PAPER, journal is the...

  • Can you please write the MySQL Query sentences for the following items below? For example: SELECT...

    Can you please write the MySQL Query sentences for the following items below? For example: SELECT * FROM employees; Generate a list of salespeople sorted descending by hire date. Show the ID, first name, last name, hire date, and salary for each salesperson. Generate a list of customers whose last name begins with the letter “M.” Show the first and last names of these customers. Sort the list of customers descending by last name. C. Generate a list of customers...

  • SQL Queries – in this assignment you will be asked to create several SQL queries relating...

    SQL Queries – in this assignment you will be asked to create several SQL queries relating to the Sakila database that we installed in class. You may freely use DBeaver to create these queries, but I will expect your solution to show me the SQL code to answer or complete the tasks below. Write a query that produces the last name, first name, address, district, and phone number for every customer in the customer table. (You don’t need to include...

  • Write following queries given these schemas: (FK stands for Foreign Key) Customer = {customerID, firstName, lastName,...

    Write following queries given these schemas: (FK stands for Foreign Key) Customer = {customerID, firstName, lastName, income, birthDate} Account = {accNumber, type, balance, branchNumberFK-Branch} Owns = {customerIDFK-Customer, accNumberFK-Account} Transactions = {transNumber, accNumberFK-Account, amount} Employee = {sin, firstName, lastName, salary, branchNumberFK-Branch} Branch = {branchNumber, branchName, managerSINFK-Employee, budget} Focus should be on using: Order By to sort data Set Operators to union/intersect multiple tables Join Operator to join multiple tables Aggregations and Group By to aggregate data Subqueries 3.15 Customer ID, first...

  • please read this articel and write one page summary atleast 250 words: MLB managers learn Spanish...

    please read this articel and write one page summary atleast 250 words: MLB managers learn Spanish to unite teams and clubhouses: At spring training of 1962, the newly hired manager of the San Francisco Giants, Alvin Dark, gathered several of his side's Latin American players together behind second base. Once there, he gave an order that left them surprised, stunned and outraged. "He told us that we couldn't speak Spanish to each other in the clubhouse", said Orlando Cepeda, who...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT