Ejemplo for php

 <?php
 $tam = 6;
 echo "<ul>";
 for ($i = 1; $i <= $tam; $i++) {
 echo "<li>Elem. $i </li>";
 }
 echo "</ul>";
 ?>
 <ul>
 <?php for ($i = 1; $i <= $tam; $i++) { ?>
 <li>Elemento <?=$i?></li>
 <?php } ?>
 </ul>

Php foreach

 <?php
 $a = [2, 5, 2, 3, 5];
 foreach ($a as $v) {
 echo $v . "<br/>";
 }
 $a[7]=9;
 $a['juan']='pablo';
 foreach ($a as $clave=>$valor) {
 echo $clave." - ".$valor . "<br/>";
 }
 for($i=0;$i<count($a);$i++){
 echo $a[$i] . "<br/>";
 }
 ?>

Php for

<?php
 for ($i = 0; $i < 10; $i += 2) {
 echo $i . "<br/>";
 }
 $i = 0;
 for (; $i < 10; $i += 2) {
 echo $i . "<br/>";
 }

$i = 0;
 for (; $i < 10;) {
 echo $i . "<br/>";
 $i += 2;
 }
 $i = 0;
 for (;;) {
 echo $i . "<br/>";
 $i += 2;
 if ($i > 10) {
 break;
 }
 }
 echo $i;
 ?>

Bucle while

<?php
 $a = 0;
 while ($a < 10) {
 echo "$a<br/>";
 $a += 2;
 }

$a = 0;
 while ($a < 10) {
 if ($a % 2 == 0) {
 echo "**<br/>";
 } else {
 echo "*****<br/>";
 }
 $a++;
 }

$a = 0;
 while ($a < 10) {
 if ($a % 2 == 0) {
 ?>
 <p style="background-color: black">.......</p>
 <?php
 } else {
 ?>
 <p style="background-color: red">.......</p>
 <?php
 }
 $a++;
 }
 ?>

Else if y switch

<?php
 $a = 1;

if ($a < 5) {
 echo '<div style="background-color: red">
 <h2>Menor de 5</h2>
 <h2>No estás autorizado a entrar</h2>
 </div>';
 } elseif ($a < 10) {
 echo "Menor de 10";
 } elseif ($a < 20) {
 echo "Menor de 20";
 } else {
 echo "Mayor de 20";
 }

switch ($a) {
 case 1:
 echo "Vale uno";
 break;
 case 2:
 case 3:
 echo "Vale 2 o 3";
 break;
 default:
 echo "Vale más de tres";
 }
 ?>

PHP if

<?php
 $a = 1;

if ($a < 5) {
 echo '<div style="background-color: red">
 <h2>Menor de 5</h2>
 <h2>No estás autorizado a entrar</h2>
 </div>';
 } else {
 echo "Mayor de 5";
 }
 if ($a < 5) {
 ?>
 <div style="background-color: red">
 <h2>Menor de 5</h2>
 <h2>No estás autorizado a entrar</h2>
 </div>
 <?php
 } else {
 ?>
 <h2>Menor de 5</h2>
 <?php
 }
 if ($a < 5):
 ?>
 <div style="background-color: red">
 <h2>Menor de 5</h2>
 <h2>No estás autorizado a entrar</h2>
 </div>
 <?php
 else:
 ?>
 <h2>Menor de 5</h2>
 <?php
 endif
 ?>

Ejemplos create view

Primero creamos una vista base:

create view country_payment as

select country, payment.* from country
join city using (country_id)
join address using (city_id)
join customer using (address_id)
join payment using (customer_id)

Después nos basamos en esta para crear las siguientes:

create view country_total as

select country, sum(amount) total from country_payment
group by country


create view country_month as

select country, month(payment_date) month, count(payment_id) total from country_payment
group by country, month

Ejemplos Create view

create view peliculas_pais as
select country, count(rental_id) total
from country join city using (country_id)
join address using(city_id)
join customer using(address_id)
join rental using(customer_id)
group by country_id
CREATE VIEW peliculas_categoria AS
select name, count(film_id) total
from category join film_category using (category_id)
group by category_id

 

Ejercicios subconsultas

Alquileres por encima de la media

select title, amount from film join inventory using(film_id)
join rental using (inventory_id)
join payment using (rental_id)
where amount> (select avg(amount) from payment)

Clientes con más películas alquiladas que alguno de los que empiezan por ‘A’

select first_name,count(rental_id) total from
customer join rental using (customer_id)
group by first_name
having total> any (select count(rental_id) total from
customer join rental using (customer_id)
where first_name like 'a%'
group by first_name
)

Clientes con más películas alquiladas que todos los que empiezan por ‘A’

select first_name,count(rental_id) total from
customer join rental using (customer_id)
group by first_name
having total> all (select count(rental_id) total from
customer join rental using (customer_id)
where first_name like 'a%'
group by first_name
)

Actores con más películas que el actor de id 1

select first_name, count(film_id) total 
from actor join film_actor using(actor_id)
group by actor_id
having total>(select count(film_id) from film_actor where actor_id=1)

Actores que trabajan en películas con rating ‘R’

select distinct first_name, last_name from actor join film_actor using(actor_id)
where film_id in
(SELECT film_id FROM sakila.film
where rating='R')

o

select distinct first_name, last_name from actor join film_actor using(actor_id)
join film using (film_id) where rating='R'

Actores que no han trabajado en películas con rating ‘R’

select distinct first_name, last_name from actor 
where actor_id not in
(SELECT actor_id FROM film_actor join sakila.film using(film_id)
where rating='R')

Actores que no hayan trabajado en películas de acción

select * from actor where actor_id not in (
select actor_id from film_actor join film using(film_id)
join film_category using (film_id)
join category using(category_id)
where name='ACTION'
)

Actores que no hayan trabajado en películas de rating ‘R’ con exists

select distinct first_name, last_name from actor a
where not exists 
(SELECT film_id FROM sakila.film join film_actor fa using(film_id)
where rating='R' and a.actor_id=fa.actor_id)

Eliminar actores que no hayan trabajado en películas de rating ‘R’

delete from actor 
where not exists 
(SELECT film_id FROM sakila.film join film_actor fa using(film_id)
where rating='R' and actor.actor_id=fa.actor_id);

delete from actor
where actor_id in 
(select actor_id 
from film_actor join film using(film_id) 
where rating='R');

Clientes que no han alquilado  películas de rating ‘R’

select * from customer where customer_id not in (
select customer_id from customer join rental using(customer_id)
join inventory using(inventory_id)
join film using(film_id)
where rating='R');

select * from customer where not exists(
select 1 from rental 
join inventory using(inventory_id)
join film using(film_id)
where rating='R' and customer.customer_id=rental.customer_id)

Media de películas alquiladas por clientes:

select avg(total) from
(select first_name,count(rental_id) total from
customer join rental using (customer_id)
group by customer_id) ttt

Media de películas por categoría

select avg(total) media from
(select name, count(film_id) total
from category join film_category using (category_id)
group by category_id) tabla