University
I want to know how many subjet contain the letter “e”
select count(idasignatura)
from asignatura
where lower (nombre) like '%e%'
Show the invoice code, client name and date from all the invoices from the database where the invoice IVA is not null
select facturas.codfac, clientes.nombre, facturas.fecha
from facturas,clientes
where facturas.codcli=clientes.codcli and IVA is not null;
Show the name of the subjets which their basic cost is between 20 and 40
select name from asignatura where costebasico between 20 and 40;
Date of the last invoice for the client with code 6
select min(fecha),max(fecha) from facturas,clientes where clientes.codcli=facturas.codcli and codcli like 6
Shows the name and cost of the subjects of the second semester along with the name of the degree to which it belongs
Select asignatura.nombre, costebasico, titulacion.nombre from asignatura, titulacion where titulacion.idtitulacion=asignatura.idtitulacion and cuatrimestre=2;
I want to know the name and idalumno of the student with dni “16161616A”
select nombre, idalumno
from alumno, persona
where alumno.dni like '16161616A'
Show the FIRST name of the province, ordered by the name (Show it in lowercase) with the lowest number of clients
select *
from (
select lower(provincias.nombre)
from clientes, pueblos, provincias
where clientes.codpue = pueblos.codpue and pueblos.codpro = provincias.codpro
group by provincias.nombre
order by count(codcli) asc, provincias.nombre)
where rownum <= 1;
Show the name and credits of the subjects from the second quarter
select nombre, creditos from asignatura where cuatrimestre=2;
Code of those items that is has checked (quantity*price) more than 6000 euros
select codart from lineas_fac group by codart having sum( cant * precio ) > 560
Shows the name of the subjects in which Luis Ramírez is enrolled along with the cost of the same
Select asignatura.nombre, costebasico from asignatura, alumno_asignatura, alumno, persona where asignatura.idasignatura=alumno_asignatura.idasignatura and alumno_asignatura.idalumno=alumno.idalumno and alumno.dni=persona.dni and persona.nombre like 'Luis' and persona.apellido like 'Ramirez';
Are many students enrolled in the subject of maths?
SELECT count (idalumno) FROM alumno_asignatura AND lower (titulacion.nombre) LIKE ‘matematicas’ GROUP BY idasignatura;
Name of the provinces whose second letter is an 'O'
select nombre from provincias where nombre like '_O%' or nombre like '_o%'
Obtain name, last name (in a alone field), dni and student id of students enrolled in the subject 150212
SELECT nombre || ' ' || apellido as "Nombre Completo", persona.DNI, alumno.idalumno
FROM persona JOIN alumno ON persona.dni = alumno.dni JOIN alumno_asignatura on alumno.idalumno = alumno_asignatura.idalumno
WHERE idasignatura = 150212
Showv code and date of invoices without iva (iva=0 or iva null)
select codfac, date from envoices where iva=0 or iva is null;
Show the name and surnames of the teachers.
select person.name, person.surnames from person, teacher where person.dni=teacher.dni;
Name of the provinces with more than 20 villages?
SELECT provincias.nombre FROM pueblos, provincias WHERE provincias.codpro=pueblos.codpro GROUP BY provincias.nombre HAVING count (*)>10;
Name of the customers whose postal code begins with "02", "11" or "21"
select nombre from clientes where codpostal like '02%' or codpostal like '11%' or codpostal like '21%'
Get the name of the client where his province begins for AN. Obligatory use upper.
SELECT clientes.nombre
FROM clientes JOIN pueblos ON clientes.codpue = pueblos.codpue JOIN provincias on pueblos.codpro = provincias.codpro
WHERE upper(provincias.nombre) like 'AN%'
Cost of the cheaper and the most expensive subject.
SELECT MIN(costebasico), MAX(costebasico)
FROM asignatura;
Show the name of all the subject taught by each teacher showing the name of that teacher.
SELECT p.nombre, a.nombre FROM asignatura a, profesor pr, persona p WHERE a.idprofesor = pr.idprofesor AND pr.dni = p.dni ORDER BY pr.idprofesor;
Number of villages where we do not have clients
select COUNT(distinct codpue) from pueblos where codpue not in (select distinct codpue from clientes);
How many students are enrolled in each subject, along with the ID of the certification.
SELECT a.idtitulacion , COUNT(aa.idalumno) as NumAlumnos
FROM alumno_asignatura aa, asignatura a
WHERE a.idasignatura = aa.idasignatura
GROUP BY a.idtitulacion;
Show the name of the subjects that have more than 4 credits, and also, or are taught in the second semester or are the first year.
Select idasignatura from asignatura where creditos>4 and (cuatrimestre=2 or curso=1);
Show the name of the clients with the name of their village
select clientes.nombre, pueblos.nombre FROM clientes JOIN pueblos ON clientes.codpue = pueblos.codpue
Show which is the most expensive degree.
SELECT idtitulacion FROM asignatura WHERE costebasico = (SELECT MAX(costebasico) FROM asignatura);