Tablas y Consultas en MySQL
Cargado por
Angelina Salgado LeonTablas y Consultas en MySQL
Cargado por
Angelina Salgado LeonCommon questions
Con tecnología de IATo apply a conditional discount, use two separate SQL update operations. First, update products with any price by applying a flat discount of 10% with UPDATE ARTICULOS SET Precio=Precio*0.90;. For a specific range, apply a reduction with UPDATE ARTICULOS SET Precio=Precio-10 WHERE Precio>=300;. These operations conditionally adjust prices and update records in the database .
To ensure product names are retrieved without duplicates, use the SQL command SELECT DISTINCT Nombre FROM ARTICULOS;. The DISTINCT keyword filters out any repeated values in the 'Nombre' column, resulting in a list of unique product names .
To order products by price in descending order, use the query SELECT * FROM ARTICULOS ORDER BY Precio DESC;. This command arranges the entries in the ARTICULOS table such that records are displayed from the highest to lowest price, utilizing the ORDER BY clause with DESC to specify descending order .
To safely delete an item from a MySQL database, ensure no foreign key or other dependent relationships could cause integrity issues. Use DELETE FROM ARTICULOS WHERE Clave_articulo=6; to remove the specified record. Prior to executing this command, confirm there are no referencing constraints that could lead to an error, updating or removing related entries if necessary .
To produce a complete list of products including details from related tables, use a join in SQL like SELECT * FROM ARTICULOS, FABRICANTES WHERE ARTICULOS.Clave_fabricante=FABRICANTES.Clave_fabricante;. This joins the ARTICULOS and FABRICANTES tables based on the foreign key relationship, pulling data from both tables to present comprehensive product information .
To set up a MySQL database for a store, you begin by creating the database with the command CREATE DATABASE TIENDA; followed by SHOW DATABASES; to view all databases. Then, activate the created database using USE TIENDA;. Next, create tables as needed, such as with CREATE TABLE ARTICULOS (...); to define the structure for storing items. Subsequently, insert data into these tables, for instance using INSERT INTO ARTICULOS VALUES (...);. These steps ensure the database is ready for storing and managing store inventory efficiently .
To list all products with their prices from a store database, use the command SELECT Nombre, Precio FROM ARTICULOS;. This query retrieves the 'Nombre' (name) and 'Precio' (price) columns from the ARTICULOS table .
To retrieve details of products manufactured by 'Lexar' or 'Kingston', use the query SELECT ARTICULOS.Nombre, ARTICULOS.Precio, FABRICANTES.Nombre FROM ARTICULOS, FABRICANTES WHERE FABRICANTES.Nombre='Lexar' OR FABRICANTES.Nombre='Kingston' AND ARTICULOS.Clave_fabricante=FABRICANTES.Clave_fabricante;. This joins the ARTICULOS and FABRICANTES tables, filtering based on the manufacturer's name and linking records via the foreign key .
To add a new product, execute an INSERT command like INSERT INTO ARTICULOS VALUES (11, 'Altavoces', 120, 2);. This adds a product with ID 11, named 'Altavoces', priced at $120, manufactured by the entity with code 2, adhering to the table schema's structure .
To find the average price of products from a specific manufacturer, such as with code 2, use the query SELECT AVG(Precio) FROM ARTICULOS WHERE Clave_fabricante=2;. This calculates the average of the 'Precio' column for entries in the ARTICULOS table, filtered by the manufacturer’s code, ensuring results are specific to that producer .