Continuación de casos de negocio

You don't have access to this class

Keep learning! Join and start boosting your career

If you already have an account,

Resources

How to manipulate book data using SQL?

Using SQL to manipulate data offers a powerful set of tools to manage large volumes of information efficiently. Here we explore how to filter, sort, and calculate statistics in a book database.

How can you sort book prices?

First, you can sort prices to identify the most expensive or cheapest books. Using the ORDER BY clause, you can set the direction to either ascending (ASC) or descending (DESC).

SELECT title, price FROM books ORDER BY price DESC LIMIT 10;

With this query, you will get the 10 most expensive books. The book "Fundamentals of Wavelets", for example, might appear as the most expensive with a price of 34.82.

How to calculate the average and standard deviation of prices?

SQL offers built-in mathematical functions such as AVG for calculating the average and STDDEV for the standard deviation, useful for data analysis.

SELECT AVG(price) AS average_price, STDDEV(price) AS standard_deviation FROM books;

The result will indicate that the average price of a book is approximately 22.39. These calculations are essential to understand price variability.

How to relate book data to author nationalities?

The use of JOIN allows combining tables by means of common fields, facilitating contextual analysis, such as relating books to the nationality of their authors.

SELECT A.nationality, AVG(B.price) AS average_price, STDDEV(B.price) AS price_deviationFROM books AS BJOIN authors AS A ON A.authorID = B.authorIDGROUP BY A.nationality;

This JOIN will allow you to discover, for example, that US authors have an average book price of 22.12 and a different standard deviation depending on the number of books.

What else can we calculate with SQL functions?

Exploring more advanced functions in SQL opens up a wide range of possibilities to get meaningful insights from simple data.

How to get the maximum and minimum price of books by country?

Go beyond simple calculations and cross multiple data sources to understand the full scope of your information.

SELECT A.nationality, MAX(B.price) AS max_price, MIN(B.price) AS min_priceFROM books AS BJOIN authors AS A ON A.authorID = B.authorIDGROUP BY A.nationality;

With this query, you will be able to see the range of book prices for each nationality. In the case of Argentina, for example, both the maximum and minimum price can be 10.40 if there is only one book from that country in the database.

How can we create detailed reports from transactions?

A well-designed database allows us to obtain detailed reports, such as a summary of all book transactions.

SELECT C.name, T.type, B.title, CONCAT(A.name, ' (', A.nationality, ')')AS author_infoFROM transactions AS TLEFT JOIN clients AS C ON C.clientID = T.clientIDLEFT JOIN books AS B ON B.bookID = T.bookIDLEFT JOIN authors AS A ON B.authorID = A.authorID;

This multiple JOIN will create a report that includes information about the customer, transaction type, book title, author and nationality at a glance.

These SQL operations are fundamental to transforming raw data into valuable information, driving informed decision making in any business. Keep exploring and experimenting to hone your skills and discover a world of possibilities with SQL!

Contributions 107

Questions 21

Sort by:

Want to see more contributions, questions and answers from the community?

José Martín Miret

6 years ago

Dato de color:
1- El calendario gregoriano comenzó a usarse en el año 1582 D.C., impulsado por el papa Gregorio XIII.
2- El que se utilizaba antes de eso era el calendario Juliano, desde el año 45 A.C.
3- El año 0 no existe, del 1 A.C. pasa directamente al 1 D.C., tomandose el 0 como un instante de tiempo y no como un período.
4- Los días del 5 al 14 de octubre de 1582 jamás existieron, es el tiempo que se suprimió para corregir el desfase entre lo calendarios Juliano y Gregoriano.

Emerson Y