Ejercicios Northwind

Insertar en Customers la compañía ‘Compañía XX’ con ‘Ana’, ‘Pi’ como contacto

insert into customers (company,first_name,last_name)
values ('Compañía XX','Ana','Pi')

Modificar en Customer el campo email_address y poner ‘Sin email’

update customers
set email_address='Sin email'

Eliminar el Customer con id = 20

delete from customers
where id=20

Seleccionar todos los proveedores (suppliers) cuyo trabajo sea ‘Sales Manager’

SELECT * from suppliers where job_title='Sales manager'

Seleccionar todos los pedidos (orders) de junio

select * from orders
where month(order_date)=6

Calcular el número de pedidos por cliente

SELECT company,count(orders.id) total FROM customers left join orders on customers.id=orders.customer_id
group by customers.id

Calcular el total de ventas por empleado

SELECT last_name,first_name,sum(quantity*unit_price*(1-discount)) total FROM employees join orders on employees.id=orders.employee_id
join order_details on orders.id=order_details.order_id
group by employees.id

Averiguar el producto más caro

SELECT * FROM `products` order by list_price desc limit 1

Averiguar el número de ventas por producto

select products.*, sum(quantity*unit_price*(1-discount)) total from products join order_details on products.id= order_details.product_id
group by products.id

Mostrar los clientes con más de 10000 en ventas

select customers.*, sum(quantity*unit_price*(1-discount)) total from customers join orders on customers.id= orders.customer_id
join order_details on orders.id= order_details.order_id
group by customers.id
having total>10000

Crear joins en base de datos

index.php:

 <h1>Camino entre tablas</h1>
        Escoja tabla:
        <form>
            <select name="bd">
                <?php
                $user = 'root';
                $pass = '';
                $server = 'localhost';

                $dbh = new PDO("mysql:host=$server", $user, $pass);
                $dbs = $dbh->query('SHOW DATABASES');

                while (( $db = $dbs->fetchColumn(0) ) !== false) {
                    echo "<option>".$db . '</option>';
                }
                ?>
            </select>
            <input type="submit" value="Enviar"></form>
        <?php
        if (!empty($_GET['bd'])){
            ?>
        <p>Seleccione las tablas origen y fin</p>
        <form action="camino.php">
            <input type="hidden" name="bd" value="<?=$_GET['bd']?>">
            <label>Origen:</label>
            <select name="origen">
                <?php
                 $dbs = $dbh->query('USE '.$_GET['bd']);
                 $dbs = $dbh->query('SHOW TABLES');
                 
                while (( $db = $dbs->fetchColumn(0) ) !== false) {
                    echo "<option>".$db . '</option>';
                }
                ?>
            </select>
            <label>Fin:</label>
            <select name="fin"><?php
                 $dbs = $dbh->query('USE '.$_GET['bd']);
                 $dbs = $dbh->query('SHOW TABLES');
                 
                while (( $db = $dbs->fetchColumn(0) ) !== false) {
                    echo "<option>".$db . '</option>';
                }
                ?></select>
             <input type="submit" value="Enviar">
        </form>
        <?php
        }

camino.php

<?php

$bd = $_GET['bd'];
$origen = $_GET['origen'];
$fin = $_GET['fin'];

$user = 'root';
$pass = '';
$server = 'localhost';

$dbh = new PDO("mysql:host=$server", $user, $pass);
$dbs = $dbh->query('use ' . $bd);

$tablas = getTables($dbh);
$tablas = setFKPaths($tablas, $dbh, $bd);

$caminos = [];
buscarCamino($origen, $fin, $origen, 0);
usort($caminos, function($a, $b) {
    return strlen($a) - strlen($b);
});
foreach ($caminos as $camino) {
    $camino = explode(",", $camino);
    echo "<h4>select * from " . $camino[0];
    for ($i = 1; $i < count($camino); $i++) {
        $c = explode("/", $camino[$i]);
        echo " join " . $c[0] . " using (" . $c[1] . ")<br/>";
    }
    echo "</h4>";
}

function getTables($dbh) {
    $tablas = [];
    $dbs = $dbh->query('SHOW TABLES');

    while (( $db = $dbs->fetchColumn(0) ) !== false) {
        $tablas[$db] = [];
    }
    return $tablas;
}

function setFKPaths($tablas, $dbh, $bd) {
    $dbs = $dbh->query("select * from information_schema.INNODB_SYS_FOREIGN where id like '$bd%'");
    $fks = $dbs->fetchAll();
    foreach ($fks as $fk) {
        $dbs = $dbh->query("select * from information_schema.INNODB_SYS_FOREIGN_COLS where id = '$fk[0]'");
        $fc = $dbs->fetchAll();
        $t1 = explode("/", $fk[1])[1];
        $t2 = explode("/", $fk[2])[1];
        $k = $fc[0][1];
        if (!in_array($t1 . "/" . $k, $tablas[$t2])) {
            $tablas[$t2][] = $t1 . "/" . $k;
        }
        if (!in_array($t2 . "/" . $k, $tablas[$t1])) {
            $tablas[$t1][] = $t2 . "/" . $k;
        }
    }
    return $tablas;
}



function buscarCamino($inicio, $fin, $camino, $prof) {
    global $caminos;
    global $tablas;

    if ($inicio == $fin) {
        $caminos[] = $camino;
        return;
    }
    foreach ($tablas[$inicio] as $destino) {
        $d = explode("/", $destino)[0];
        if (strpos($camino, "," . $d . "/") === false && strpos($camino, $d . ",") === false) {
            buscarCamino($d, $fin, $camino . "," . $destino, $prof + 1);
        }
    }
}

Ejemplos subqueries

Pagos por encima de la media:


select * from payment where amount>(
select avg(amount) from payment
)

Películas de la categoría 1:


select distinct first_name,last_name from actor join film_actor using(actor_id) where film_id in
(
select film_id from film_category where category_id=1
)

Películas con el mayor coste de reemplazo:


select * from film where replacement_cost=(
select max(replacement_cost) from film
)

Películas de la categoría con más alquileres:


select * from film join film_category using(film_id)
where category_id in (
select category_id from category join film_category using(category_id)
join film using(film_id)
join inventory using(film_id)
join rental using(inventory_id)
group by category_id
having count(rental_id)=(
select max(total) from (
select count(rental_id) total from category join film_category using(category_id)
join film using(film_id)
join inventory using(film_id)
join rental using(inventory_id)
group by category_id
) alias_falso
)
)

 

Categorías – países

SELECT
country, name
FROM
category
JOIN
film_category ON category.category_id = film_category.category_id
JOIN
film ON film.film_id = film_category.film_id
JOIN
inventory ON film.film_id = inventory.film_id
JOIN
store ON inventory.store_id = store.store_id
JOIN
customer ON store.store_id = customer.store_id
JOIN
address ON address.address_id = customer.address_id
JOIN
city ON city.city_id = address.city_id
JOIN
country ON country.country_id = city.country_id

SQL sentencias para construir base de datos

CREATE DATABASE databasename;

DROP DATABASE databasename;

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
….
);

CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

DROP TABLE table_name;

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
….
);

CREATE TABLE `indices`.`asda` (
`idasda` INT NOT NULL AUTO_INCREMENT,
`asdacol` VARCHAR(45) NULL,
PRIMARY KEY (`idasda`));

CREATE TABLE `otraprueba`.`prueba` (
`idprueba` INT NOT NULL AUTO_INCREMENT,
`nombre` VARCHAR(45) NULL,
PRIMARY KEY (`idprueba`),
INDEX `ix_nombre` (`nombre` ASC));

ALTER TABLE `indices`.`asda`
ADD INDEX `ix_nombre` (`asdacol` ASC);

CREATE TABLE `otraprueba`.`prueba` (
`idprueba` INT NOT NULL AUTO_INCREMENT,
`nombre` VARCHAR(45) NULL,
`idforeign` INT NULL,
PRIMARY KEY (`idprueba`),
INDEX `ix_nombre` (`nombre` ASC),
INDEX `fk_otra_idx` (`idforeign` ASC),
CONSTRAINT `fk_otra`
FOREIGN KEY (`idforeign`)
REFERENCES `otraprueba`.`tabla1` (`idtabla1`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);