Sentencias SQL

Materia: Taller de Base de Datos
Profesor: Roberto Rangel Anguiano
Problema: Basic Select
Your company is doing a survey of how many customers are registered in the states,
however, lacked to raise the data of the state of the 'Rio Grande do Sul'.
Then, you must show the names of all customers whose state is 'RS'.
SELECT name FROM customers WHERE state = 'RS';

Problema: Customer Address
The company will make an event celebrating the 20th anniversary of the market, and for
that we will make a great celebration in the city of Porto Alegre. We also invite all our
customers who are enrolled in this city.
Your job is in having the names and addresses of customers who live in 'Porto Alegre', to
deliver the invitations personally.
SELECT name, street FROM customers WHERE city = 'Porto Alegre';

Problema: Under 10 or Greater Than 100

The financial sector of the company needs a report that shows the ID and the name of the
products whose price is less than 10 or greater than 100.
SELECT id,name FROM products WHERE price < '5' OR price > '100';
Problema: Executive Representatives

The financial sector needs a report on the providers of the products we sell. The reports
include all categories, but for some reason, providers of products whose category is the
executive, are not in the report.
Your job is to return the names of the products and providers whose category ID is 6.
SELECT products.name, providers.name

FROM products FULL OUTER JOIN providers ON products.id_providers =
providers.id
WHERE products.id_categories = 6;
Problema: Categories
When the data were migrated to the database, there was a small misunderstanding on the
DBA.
Your boss needs you to select the ID and the name of the products, whose categorie name
start with 'super'.
SELECT products.id, products.name

FROM products FULL OUTER JOIN categories ON products.id_categories =
categories.id
WHERE categories.name LIKE 'super%';
Problema: Providers' City in Alphabetical Order

Every month the company asks for a report from the cities that providers are registered. So,
do a query that returns all the cities of the providers, but in alphabetical order.
OBS: You must not show repeated cities.
SELECT DISTINCT city FROM providers ORDER BY city;

Problema: Higher and Lower Price

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.
SELECT MAX(price), MIN(price) FROM products;

Problema: Products by Categories

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.
SELECT categories.name, SUM(products.amount)

FROM products FULL OUTER JOIN categories ON products.id_categories =
categories.id GROUP BY categories.name;
Problema: Average Value of Products

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.
SELECT ROUND (AVG(price),2) AS price FROM products;

Problema: Action Movies

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'.
SELECT movies.id, movies.name

FROM movies FULL OUTER JOIN genres ON movies.id_genres = genres.id
WHERE genres.description = 'Action';
Problema: The Actors Silva

Once actors win big awards and recognition in the film world, the demand for films in which
they participate increases. So we want to know what movies the brothers Silva have in our
catalog.
To do this, select the code and the name of the movies in which the actors 'Marcelo Silva'
or 'Miguel Silva' acted and that the genre of the film is 'Action'
SELECT movies.id,movies.name
FROM movies FULL OUTER JOIN movies_actors ON movies.id =
movies_actors.id_movies
FULL OUTER JOIN actors ON actors.id = movies_actors.id_actors
FULL OUTER JOIN genres ON movies.id_genres = genres.id
WHERE (actors.name = 'Marcelo Silva' OR actors.name = 'Miguel Silva');
Problema: Cheap Movies

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.
SELECT movies.id, movies.name

FROM movies FULL OUTER JOIN prices ON movies.id_prices = prices.id
WHERE prices.value < 2.00;
Problema: September rentals

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.
SELECT customers.name,rentals.rentals_date
FROM customers FULL OUTER JOIN rentals ON customers.id =
rentals.id_customers
WHERE rentals.rentals_date BETWEEN '2016-09-01' AND '2016-09-30';
Problema: Expanding the Business

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.
SELECT DISTINCT city FROM customers;

Problema: No Rental
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.
SELECT customers.id,customers.name
FROM customers LEFT JOIN locations ON customers.id =
locations.id_customers
WHERE locations.id_customers IS NULL;
Problema: Provider Ajax SA

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'
SELECT products.name, providers.name

FROM providers FULL OUTER JOIN products ON products.id_providers =
providers.id
WHERE providers.name = 'Ajax SA';
Problema: Imported Products

Our company's import sector needs a report on the import of products from our Sansul
providers.
Your task is to display the name of the products, the name of the supplier and the name of
the category, for the products supplied by the supplier 'Sansul SA' and whose category
name is 'Imported'.
SELECT products.name, providers.name, categories.name

FROM products
FULL OUTER JOIN providers ON products.id_providers = providers.id
FULL OUTER JOIN categories ON products.id_categories = categories.id
WHERE providers.name = 'Sansul SA' AND categories.name = 'Imported';
Problema: Super Luxury

Our company is looking to make a new contract for the supply of new super luxury
products, and for this we need some data of our products.
Your job is to display the name of the products, the name of the providers and the price, for
the products whose price is greater than 1000 and its category is' Super Luxury
SELECT products.name, providers.name, products.price

FROM products
FULL OUTER JOIN providers ON products.id_providers = providers.id
FULL OUTER JOIN categories ON products.id_categories = categories.id
WHERE products.price > 1000 AND categories.name = 'Super Luxury';
Problema: Orders in First Half

The company's financial audit is asking us for a report for the first half of 2016. Then
display the customers name and order number for customers who placed orders in the first
half of 2016.
SELECT customers.name, orders.id

FROM customers
FULL OUTER JOIN orders ON customers.id = orders.id_customers
WHERE orders.orders_date
BETWEEN '2016-01-01' AND '2016-06-30';
Problema: Amounts Between 10 and 20

When it comes to delivering the report on how many products the company has in stock, a
part of the report has become corrupted, so the stock keeper asked for help, he wants you
to display the following data for him.
Display the name of products whose amount are between 10 and 20 and whose name of
the supplier starts with the letter 'P'.
SELECT products.name
FROM products FULL OUTER JOIN providers ON products.id_providers =
providers.id
WHERE (products.amount BETWEEN 10 AND 20) AND providers.name LIKE
'P%';
Problema: Legal Person

The sales industry wants to make a promotion for all clients that are legal entities. For this
you must display the name of the customers that are legal entity.
SELECT customers.name
FROM customers
RIGHT JOIN legal_person ON customers.id = legal_person.id_customers;
Problema: Categories with Various Products

The sales industry needs a report to know what products are left in stock.
To help the sales industry, display the product name and category name for products
whose amount is greater than 100 and the categorie ID is 1,2,3,6 or 9.
SELECT products.name,categories.name
FROM products INNER JOIN categories ON products.id_categories =
categories.id
WHERE products.amount > 100 AND products.id_categories IN (1,2,3,6,9);
Problema: Number of Cities per Customers

The company board asked you for a simple report on how many cities the company has
already reached.
To do this you must display the number of distinct cities in the customers table
SELECT COUNT(DISTINCT city)

FROM customers;
Problema: CPF Validation

Your company's communications managers want a report on the natural person customer
data that is registered in the database. But the old report had a problem. customers CPF
data came without validation.
So your job now is to select all the CPFs of all the customers, and apply a mask on the
return of the data.
The CPF mask looks like: '000.000.000-00'
SELECT
CONCAT(SUBSTR(CPF,1,3),'.',SUBSTR(CPF,4,3),'.',SUBSTR(CPF,7,3),'-
',SUBSTR(CPF,10,2)) AS CPF
FROM natural_person;
Problema: Lawyers
The manager of Mangojata Lawyers requested a report on the current lawyers.
The manager wants you to show him the name of the lawyer with the most clients, the one
with the fewest and the client average considering all lawyers.
OBS: Before presenting the average, show a field called Average to make the report more
readable. The average must be presented as an integer.
SELECT name, customers_number FROM lawyers WHERE customers_number =

(SELECT MAX(customers_number)
FROM lawyers) UNION SELECT name, customers_number FROM lawyers
WHERE customers_number = (SELECT MIN(customers_number) FROM lawyers)
UNION ALL
SELECT 'Average' AS name, ROUND(avg(customers_number), 0) AS
customers_number
FROM lawyers;
Problema: Contest
The Mars Technology University has Open Positions for researchers. However, the
computer responsible for processing the candidates' data is broken. You must present the
candidate list, containing the name and final score (with two decimal places of precision) of
each candidate. Remember to show the list ordered by score (highest first).
The score is given by the weighted average described as:
Avg=(math∗2)+(specific∗3)+(project_plan∗5)10
SELECT candidate.name,
ROUND(((score.math*2)+(score.specific*3)+(score.project_plan*5))/10,2)
AS avg from candidate FULL OUTER JOIN score ON candidate.id =
score.candidate_id
ORDER BY avg DESC;
Problema: Payday
The Central Bank of Financing lost many registers after a server failure that happened last
October. The collection dates for the parcels where lost.
The bank requested your help to select the names and day of month in which each client
must pay theirs parcel.
OBS: The day of month must be an integer
SELECT name,CAST(EXTRACT(DAY FROM payday) AS integer) AS day FROM

loan;
Problema: League
The International Underground Excavation League is a success between alternative sports,
however the staff responsible for organizing the events doesn’t understand computers at
all, they only know how to dig and the sport rule set. As such, you were hired to solve the
League’s problem.
Select the three first placed with the initial phrase "Podium: " and select the last two, which
will be demoted to a lower league with the initial phrase “Demoted:".
SELECT CONCAT('Podium: ',team) FROM league

WHERE position <= 3 UNION ALL SELECT CONCAT('Demoted: ',team)
FROM league, (SELECT position FROM league ORDER BY position DESC limit
2) AS B
WHERE B.position = league.position;
Problema: Students' Grades

The semester is over at South Transylvania University. Every grade was closed, and only
Alchemy 104 haven’t published its list of approved students.
Therefore, you should show the word 'Approved: ' alongisde the the name of a student
and the grade, for those who have been approved (grade ≥7).
Remember to sort the list by grade (higher grades first)
SELECT CONCAT('Approved: ',name),grade

FROM students WHERE grade >= 7 ORDER BY grade DESC;
Problema: Richard's Multiverse

Richard is a famous scientist because of his multiverse theory, where he describes every
hypothetical set of parallel universes by means of a database. Thanks to that you now have
a job..
As your first task, you must select every Richard from dimensions C875 and C774, together
with its existence probability (the famous factor N) with three decimal places of precision.
Remember that the N factor is calculated by multiplying the omega value by 1,618. The
data must be sorted by the least omega value
SELECT life_registry.name, ROUND(life_registry.omega*1.618,3)

FROM life_registry FULL OUTER JOIN dimensions ON
life_registry.dimensions_id = dimensions.id
WHERE(life_registry.name LIKE '%Richard%') AND (dimensions.name =
'C875' OR dimensions.name = 'C774');
Problema: Number of Characters

The Global Organization of Characters at People’s Names (GOCPN) made a census to
figure how many characters people have in theirs names.
To help OMCNP, you must show the number of characters of each name sorted by
decreasing number of characters.
SELECT name, length(name) AS length FROM people ORDER BY length DESC;

Problema: Passwords
You were hired to be the consultant for a company. Analyzing the database, you noticed
that the passwords are stored as text files and, as everyone knows, this is a terrible
security practice as they are not encrypted.
Therefore you must convert every password to the MD5 format. Show the client id, the
password before conversion and the new MD5
SELECT id, password,MD5(password)

FROM account;
Problema: Taxes
You are going to the International Personal Tax meeting and your proposal is: every
individual with income higher than 3000 must pay a tax to the government, which is 10% of
his/her income.
Show the name and the tax value of each person who earns more than 3000, with two
decimal places of precision
SELECT name, ROUND(salary*0.1,2) AS tax

FROM people WHERE salary > 3000;
Problema: Viruses
Viruses are evolving, but new research has proven that by switching some proteins the
vaccine becomes unbeatable. The protein H1(Hemagglutinin) when replaced by the X
protein (Xenomorphina) has interesting effects against almost every viral disease. Some
conspiracists say that after the vaccine’s discovery some strange 3 meters tall creatures
were found in the surroundings of the laboratories, but this is clearly a lie.
Therefore, you must replace every string “H1”( Hemagglutinin ) by 'X' ( Xenomorphina ).
SELECT REPLACE (name,'H1','X')

FROM virus;

Sentencias SQL

Uploaded by

Copyright:

Available Formats

Sentencias SQL

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Sentencias SQL

Uploaded by

Copyright:

Available Formats

Materia: Taller de Base de Datos

Profesor: Roberto Rangel Anguiano

Problema: Basic Select

SELECT name FROM customers WHERE state = 'RS';

Profesor: Roberto Rangel Anguiano

Problema: Customer Address

SELECT name, street FROM customers WHERE city = 'Porto Alegre';

Profesor: Roberto Rangel Anguiano

Problema: Under 10 or Greater Than 100

Profesor: Roberto Rangel Anguiano

Problema: Executive Representatives

SELECT products.name, providers.name

Profesor: Roberto Rangel Anguiano

SELECT products.id, products.name

Profesor: Roberto Rangel Anguiano

Problema: Providers' City in Alphabetical Order

OBS: You must not show repeated cities.

SELECT DISTINCT city FROM providers ORDER BY city;

Profesor: Roberto Rangel Anguiano

Problema: Higher and Lower Price

SELECT MAX(price), MIN(price) FROM products;

Profesor: Roberto Rangel Anguiano

Problema: Products by Categories

SELECT categories.name, SUM(products.amount)

Profesor: Roberto Rangel Anguiano

Problema: Average Value of Products

SELECT ROUND (AVG(price),2) AS price FROM products;

Profesor: Roberto Rangel Anguiano

Problema: Action Movies

SELECT movies.id, movies.name

Profesor: Roberto Rangel Anguiano

Problema: The Actors Silva

Profesor: Roberto Rangel Anguiano

Problema: Cheap Movies

SELECT movies.id, movies.name

Profesor: Roberto Rangel Anguiano

Problema: September rentals

Profesor: Roberto Rangel Anguiano

Problema: Expanding the Business

SELECT DISTINCT city FROM customers;

Profesor: Roberto Rangel Anguiano

Profesor: Roberto Rangel Anguiano

Problema: Provider Ajax SA

SELECT products.name, providers.name

Profesor: Roberto Rangel Anguiano

Problema: Imported Products

SELECT products.name, providers.name, categories.name

Profesor: Roberto Rangel Anguiano

Problema: Super Luxury

SELECT products.name, providers.name, products.price

Profesor: Roberto Rangel Anguiano

Problema: Orders in First Half

SELECT customers.name, orders.id

Profesor: Roberto Rangel Anguiano

Problema: Amounts Between 10 and 20

Profesor: Roberto Rangel Anguiano

Problema: Legal Person

Profesor: Roberto Rangel Anguiano

Problema: Categories with Various Products