Using MySQL, create the following queries using the following tables...animal ,

Question

Using MySQL, create the following queries using the following tables...animal , volunteer, adopter

List the name, weight, and breed id of each animal in the database and only show animals between 2 different weights. Have the user input those 2 weights.

Create a query that will return the volunteer id followed by the volunteer's first and last name with their initial letters shown and the rest redacted in separate columns. For example, John Doe should display as J*** D**. Show only the people from cities that start with the letter G. Order by their last name.

Create a report that displays the id, animal (animal name and sex concatenated with a comma and space in between), age, and arrival date for all animals. Prompt user for a low and high age which is order by animal's age.

Create a report that display the id, animal name, age('*' that represents each year), and arrival date which is ordered by arrival date between Jan 17 and the present.

Display the name (concatenate the name and age) as name and age with a coma, arrival as arrival, and months there and also do not have a volunteer. Order by days there descending.

Display the adopters full name and street address and prompt user to enter the name of the city where the adopter is from and order by the last name.

Explanation / Answer

List the name, weight, and breed id of each animal in the database and only show animals between 2 different weights. Have the user input those 2 weights.

SELECT name, weight, breed_id FROM animal
WHERE weight BETWEEN @value1 AND @value2;

Create a query that will return the volunteer id followed by the volunteer's first and last name with their initial letters shown and the rest redacted in separate columns. For example, John Doe should display as J*** D**. Show only the people from cities that start with the letter G. Order by their last name.

SELCET volunteer_id , CONCAT(SUBSTR(first_name, 1, 1), SUBSTR('****************', 2, LENGTH(first_name))) as first_name,
CONCAT(SUBSTR(last_name, 1, 1), SUBSTR('**************', 2, LENGTH(last_name))) AS last_name
FROM volunteer
WHERE cities LIKE 'G%'
ORDER last_name

Create a report that displays the id, animal (animal name and sex concatenated with a comma and space in between), age, and arrival date for all animals. Prompt user for a low and high age which is order by animal's age.

SELECT id, CONCAT(name, ', ' ,sex), age, and arrival_date
FROM animal
WHERE age BETWEEN @low AND @high
ORDER BY age;

Create a report that display the id, animal name, age('*' that represents each year), and arrival date which is ordered by arrival date between Jan 17 and the present.
SELECT id, name, age, arrival_date
FROM animal
WHERE arrival_date between '****-17-01' and CURDATE()
ORDER BY arrival_date;

Display the name (concatenate the name and age) as name and age with a coma, arrival as arrival, and months there and also do not have a volunteer. Order by days there descending.

SELECT CONCAT(name, ',' ,age) AS name, arrival as arrival, months
FROM adopter
ORDER BY days;

Display the adopters full name and street address and prompt user to enter the name of the city where the adopter is from and order by the last name.

SELECT CONCAT(fisr_name,last_name) AS fullname, street_addresss
FROM adopters
WHERE city=@city
ORDER BY last_name

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.