Sprint2_T2_MySQL_Query

Store Database

• Lists the name of all the products in the product table.

• List the names and prices of all products in the product table.

• Lists all columns of the product table.

• Lists the name of the products, the price in euros and the price in US dollars (USD).

• Lists the name of the products, the price in euros and the price in US dollars (USD). Use the following nicknames for the columns: product name, euros, dollars.

• Lists the names and prices of all the products in the product table, converting the names to uppercase.

• Lists the names and prices of all the products in the product table, converting the names to lowercase.

• List the name of all manufacturers in one column, and in another column capitalize the first two characters of the manufacturer's name.

• Lists the names and prices of all the products in the product table, rounding the price value.

• Lists the names and prices of all the products in the product table, truncating the price value to display it without any decimal number.

• Lists the code of the manufacturers that have products in the product table.

• Lists the code of the manufacturers that have products in the product table, eliminating the codes that appear repeated.

• Lists the names of the manufacturers in ascending order.

• Lists the names of the manufacturers ordered in descending order.

• Lists the names of the products ordered, first, by name in ascending order and, secondly, by price in descending order.

• Returns a list with the first 5 rows of the manufacturer table.

• Returns a list with 2 rows starting from the fourth row of the manufacturer table. The fourth row should also be included in the answer.

• List the name and price of the cheapest product. (Use only the ORDER BY and LIMIT clauses.) NOTE: You couldn't use MIN(price) here, you would need GROUP BY.

• List the name and price of the most expensive product. (Use only the ORDER BY and LIMIT clauses.) NOTE: You couldn't use MAX(price) here, you would need GROUP BY.

• Lists the name of all the manufacturer's products whose manufacturer code is equal to 2.

• Returns a list with the product name, price and manufacturer name of all products in the database.

• Returns a list with the product name, price and manufacturer name of all products in the database. Sort the result by the name of the manufacturer, in alphabetical order.

• Returns a list of the product code, product name, manufacturer code, and manufacturer name of all products in the database.

• Returns the name of the product, its price and the name of its manufacturer, of the cheapest product.

• Returns the name of the product, its price and the name of its manufacturer, of the most expensive product.

• Returns a list of all products from the manufacturer Lenovo.

• Returns a list of all products from the manufacturer Crucial that have a price greater than €200.

• Returns a list with all the products from the manufacturers Asus, Hewlett-Packard and Seagate. Without using the IN operator.

• Returns a list with all the products from the manufacturers Asus, Hewlett-Packard and Seagate. Using the IN operator.

• Returns a list with the name and price of all products from manufacturers whose name ends with the vowel e.

• Returns a list with the name and price of all products whose manufacturer name contains the character w in its name.

• Returns a list with the product name, price and manufacturer name, of all products that have a price greater than or equal to €180. Sort the result first by price (in descending order) and second by name (in ascending order).

• Returns a list with the manufacturer code and name, only of those manufacturers that have associated products in the database.

• Returns a list of all the manufacturers that exist in the database, along with the products that each of them has. The list must also show those manufacturers that do not have associated products.

• Returns a list in which only those manufacturers that do not have any associated product appear.

• Return all products from the Lenovo manufacturer. (Without using INNER JOIN).

• Returns all data for products that have the same price as the most expensive product from the manufacturer Lenovo. (Without using INNER JOIN).

• List the name of the most expensive product from the manufacturer Lenovo.

• List the name of the cheapest product from the manufacturer Hewlett-Packard.

• Returns all products in the database that have a price greater than or equal to the most expensive product from the manufacturer Lenovo.

• Lists all products from the manufacturer Asus that have a price higher than the average price of all its products.

University database

• Returns a list with the first last name, second last name and the name of all the students. The list must be ordered alphabetically from smallest to largest by first last name, second last name and first name.

• Find the first name and both surnames of the students who have not registered their phone number in the database of data.

• Returns the list of students who were born in 1999.

• Returns the list of teachers who have not registered their telephone number in the database and also whose NIF ends in K.

• Returns the list of the subjects that are taught in the first semester, in the third year of the degree that has the identifier 7.

• Returns a list of teachers along with the name of the department to which they are linked. The list should return four columns, first last name, second last name, first name, and department name. The result will be ordered alphabetically from least to greatest by last name and first name.

• Returns a list with the name of the subjects, start year and end year of the school year of the student with NIF 26902806M.

• Returns a list with the name of all the departments that have professors who teach a subject in the Degree in Computer Engineering (Plan 2015).

• Returns a list of all the students who have enrolled in a subject during the 2018/2019 school year.

• Returns a list with the names of all the teachers and the departments they are linked to. The list should also show those teachers who do not have any associated department. The list should return four columns, department name, first last name, second last name and professor's name. The result will be ordered alphabetically from smallest to largest by the name of the department, last name and first name.

• Returns a list of teachers who are not associated with a department.

• Returns a list of departments that do not have associate professors.

• Returns a list of teachers who do not teach any subject.

• Returns a list of subjects that do not have an assigned teacher.

• Returns a list with all the departments that have not taught subjects in any school year.

• Returns the total number of existing students.

• Calculate how many students were born in 1999.

• Calculate how many teachers there are in each department. The result should only show two columns, one with the name of the department and another with the number of professors in that department. The result should only include departments that have associate professors and will have to be ordered from largest to smallest by the number of professors.

• Returns a list with all the departments and the number of professors in each of them. Please note that there may be departments that do not have associate professors. These departments must also appear on the list.

• Returns a list with the name of all the degrees existing in the database and the number of subjects each one has. Keep in mind that there may be degrees that lack associated subjects. These grades must also appear on the list. The result must be ordered from highest to lowest by the number of subjects.

• Returns a list with the name of all the degrees existing in the database and the number of subjects that each one has, of the degrees that have more than 40 associated subjects.

• Returns a list that shows the name of the degrees and the sum of the total number of existing credits for each type of subject. The result must have three columns: name of the degree, type of subject and the sum of the credits of all the subjects that exist of this type.

• Returns a list that shows how many students have enrolled in a subject in each of the school years. The result will have to show two columns, one column with the start year of the school year and another with the number of enrolled students.

• Returns a list with the number of subjects taught by each teacher. The list must take into account those teachers who do not teach any subject. The result will show five columns: id, first name, first last name, second last name and number of subjects. The result will be ordered from highest to lowest by the number of subjects.

• Returns all the data of the youngest student.

• Returns a list of teachers who have an associated department and who do not teach any subject.