URI Online Judge Solution

URI Online Judge Solution 2617 Provider Ajax SA Using PostgreSQL Query Language. The financial sector has encountered some problems in the delivery of one of our providers, the delivery of the products does not match the invoice. Your job is to display the name of the products and the name of the provider, for the products supplied by the provider 'Ajax SA'. Schema providers Column Type id (PK) numeric name character varying (255) street character varying (255) city character varying (255) state char (2)   products Column Type id (PK) numeric name character varying (255) amount numeric price numeric id_providers (FK) numeric Tables providers id name street city state 1 Ajax SA Presidente Castelo Branco Porto Alegre RS 2 Sansul SA Av Brasil Rio de Janeiro RJ 3 South Chairs Av Moinho Santa Maria RS 4 Elon Electro Apolo São Paulo SP 5 Mike Electro Pedro da Cunha Curitiba PR   products id name amount value id_providers 1 Blue Chair 30 300.00 5 2 Red Chair 50 2150.00 1 3 Disney Ward

URI Online Judge Solution 2616 Solution No Rental Using PostgreSQL Query Language. The video store company intends to do a promotion for customers who have not yet done any rental. Your job is to deliver us the ID and the name of the customers who have not done any rental. Schema customers Column Type id (PK) numeric name varchar street varchar city varchar   locations Column Type id (PK) numeric locations_date date (ISO/YMD) id_customers (FK) numeric Tables customers id name street city 1 Giovanna Goncalves Oliveira Rua Mato Grosso Canoas 2 Kauã Azevedo Ribeiro Travessa Ibiá Uberlândia 3 Rebeca Barbosa Santos Rua Observatório Meteorológico Salvador 4 Sarah Carvalho Correia Rua Antônio Carlos da Silva Apucarana 5 João Almeida Lima Rua Rio Taiuva Ponta Grossa 6 Diogo Melo Dias Rua Duzentos e Cinqüenta Várzea Grande   locations id locations_date id_customers 1 2016-10-09 3 2 2016-09-02 1 3 2016-08-02 4 4 2016-09-02 2 5 2016-03-02 6 6 2016-04-04 4 Output Sample id name 5 João Almeida Lima

URI Online Judge Solution 2615 Expanding the Business Using PostgreSQL Query Language. The video store company has the objectives of creating several franchises spread throughout Brazil. For this we want to know in which cities our customers live.  For you to help us select the name of all the cities where the rental company has clients. But please do not repeat the name of the city. Schema customers Column Type id (PK) numeric name varchar street varchar city varchar Tables customers id name street city 1 Giovanna Goncalves Oliveira Rua Mato Grosso Canoas 2 Kauã Azevedo Ribeiro Travessa Ibiá Uberlândia 3 Rebeca Barbosa Santos Rua Observatório Meteorológico Salvador 4 Sarah Carvalho Correia Rua Antônio Carlos da Silva Uberlândia 5 João Almeida Lima Rua Rio Taiuva Ponta Grossa 6 Diogo Melo Dias Rua Duzentos e Cinqüenta Várzea Grande Output Sample city Uberlândia Canoas Ponta Grossa Várzea Grande Salvador URI 2615 Solution in SQL: SELECT distinct city FROM customers

URI Online Judge Solution 2614 September Rentals Using PostgreSQL Query Language. The video store is making its semi-annual report and needs your help. All you have to do is select the name of the clients and the date of rental, from the rentals made in September 2016. Schema customers Column Type id (PK) numeric name varchar street varchar city varchar   rentals Column Type id (PK) numeric rentals_date date (ISO/YMD) id_customers (FK) numeric Tables customers id name street city 1 Giovanna Goncalves Oliveira Rua Mato Grosso Canoas 2 Kauã Azevedo Ribeiro Travessa Ibiá Uberlândia 3 Rebeca Barbosa Santos Rua Observatório Meteorológico Salvador 4 Sarah Carvalho Correia Rua Antônio Carlos da Silva Apucarana 5 João Almeida Lima Rua Rio Taiuva Ponta Grossa 6 Diogo Melo Dias Rua Duzentos e Cinqüenta Várzea Grande   rentals id rentals_date id_customers 1 2016-10-09 3 2 2016-09-02 1 3 2016-08-02 4 4 2016-08-02 2 5 2016-03-02 6 6 2016-04-04 4 Output Sample name rentals_date Giovanna Goncalves Ol

URI Online Judge Solution 2613 Cheap Movies Using PostgreSQL Query Language. In the past the studio has made an event where several movies were on sale, we want to know what movies these were. Your job to help us is to select the ID and name of movies whose price is less than 2.00. Schema movies Column Type id (PK) numeric name varchar id_prices (FK) numeric   prices Column Type id (PK) numeric categorie varchar value numeric Tables movies id name id_prices 1 Batman 3 2 The Battle of the Dark River 3 3 White Duck 5 4 Breaking Barriers 4 5 The Two Hours 2   prices id categorie value 1 Releases 3.50 2 Bronze Seal 2.00 3 Silver Seal 2.50 4 Gold Seal 3.00 5 Promotion 1.50 Output Sample id name 3 White Duck URI 2613 Solution in SQL: SELECT movies.id,movies.name FROM movies INNER JOIN prices ON movies.id_prices=prices.id WHERE prices.value < 2.00

URI Online Judge Solution 2611 Action Movies Using PostgreSQL Query Language. A video store contractor hired her services to catalog her movies. They need you to select the code and the name of the movies whose description of the genre is 'Action'. Schema movies Column Type id (PK) numeric name varchar id_genres (FK) numeric   genres Column Type id (PK) numeric description varchar Tables movies id name id_genres 1 Batman 3 2 The Battle of the Dark River 3 3 White Duck 1 4 Breaking Barriers 4 5 The Two Hours 2   genres id description 1 Animation 2 Horror 3 Action 4 Drama 5 Comedy Output Sample id name 1 Batman 2 The Battle of the Dark River URI 2611 Solution in SQL: SELECT id,name FROM Movies WHERE id_genres = (SELECT id FROM Genres WHERE description = 'Action' )

URI Online Judge Solution 2610 Average Value of Products Using PostgreSQL Query Language. In the company that you work is being done a survey on the values of the products that are marketed.  To help the industry that is doing this survey you should calculate and display the average value of the price of the products.  OBS: Show the value with two numbers after the period. Schema products Column Type id (PK) numeric name varchar amount numeric price numeric Tables products id name amount price 1 Two-doors wardrobe 100 800 2 Dining table 1000 560 3 Towel holder 10000 25.50 4 Computer desk 350 320.50 5 Chair 3000 210.64 6 Single bed 750 460 Output Sample price 396.10 URI 2610 Solution in SQL: SELECT ROUND(avg(price),2) "price" FROM products;

URI Online Judge Solution 2609 Products by Categories  Using PostgreSQL Query Language. As usual the sales industry is doing an analysis of how many products we have in stock, and you can help them.  Then your job will display the name and amount of products of each category. Schema products Column Type id (PK) numeric name varchar amount numeric price numeric id_categories (FK) numeric   categories Column Type id (PK) numeric name varchar Tables products id name amount price id_categories 1 Two-doors wardrobe 100 800 1 2 Dining table 1000 560 3 3 Towel holder 10000 25.50 4 4 Computer desk 350 320.50 2 5 Chair 3000 210.64 4 6 Single bed 750 460 1   categories id name 1 wood 2 luxury 3 vintage 4 modern 5 super luxury Output Sample name sum luxury 350 modern 13000 wood 850 vintage 1000   URI 2609 Solution in SQL: SELECT categories.name,sum(amount) "sum" FROM products INNER JOIN categories on products.id_categories=categories.id GROUP BY categories.name;

URI Online Judge Solution 2608 Higher and Lower Price  Using PostgreSQL Query Language. The financial sector of our company, wants to know the smaller and higher values of the products, which we sell. For this you must display only the highest and lowest price of the products table. Schema products Column Type id (PK) numeric name varchar amount numeric price numeric Tables products id name amount price 1 Two-doors wardrobe 100 800 2 Dining table 1000 560 3 Towel holder 10000 25.50 4 Computer desk 350 320.50 5 Chair 3000 210.64 6 Single bed 750 460 Output Sample price price 800 25.50 URI 2608 Solution in SQL: SELECT max(Price) price, min(Price) price FROM products