duvida em query

[MIMP]

bom dia!

tenho a seguinte base de dados referente a uma locadora de video

DROP DATABASE IF EXISTS fichaF; 
CREATE DATABASE fichaF;
USE fichaF;

CREATE TABLE socios (
    n_socio                      INTEGER(5) NOT NULL, 
    nome                      CHAR(30) NOT NULL,
    morada                      VARCHAR(30),
    telefone                         INTEGER (9),
    bi             INTEGER (9),
    data_nasc             DATE,
    data_insc             DATE NOT NULL,
    
    CONSTRAINT ch_candidata_socios UNIQUE (nome,bi),
    
    CONSTRAINT ch_prim_n_socio
        PRIMARY KEY (n_socio)
);

CREATE TABLE filmes (
    cod_filme                      INTEGER(5) NOT NULL,
    titulo              CHAR(30) NOT NULL,
    duracao                  TIME,
    
    CONSTRAINT ch_candidata_filmes UNIQUE (titulo),
    
    CONSTRAINT ch_prim_cod_filme
        PRIMARY KEY (cod_filme)
);

CREATE TABLE modalidades (
    modalidade    CHAR(10) NOT NULL,
    preço          DECIMAL(5,2) NOT NULL,
    multa_diaria      DECIMAL(5,2) NOT NULL,
    
    CHECK (preço >=0),
    CHECK (multa_diaria >=0),
    
    CONSTRAINT ch_prim_modalidade
        PRIMARY KEY (modalidade)
);

CREATE TABLE copias (
    cod_filme           INTEGER(5) NOT NULL,
    n_copia          INTEGER(5) NOT NULL,
    formato           CHAR(3)NOT NULL,
    data_aquisicao             DATE, 
    preço           DECIMAL(5,2) NOT NULL,
    
    CHECK (formato= 'VHS' AND formato='DVD'),
    CHECK (preço>=0),
    
    
    CONSTRAINT ch_prim_cod_filme_e_n_copia
        PRIMARY KEY (cod_filme,n_copia)
);


CREATE TABLE alugueres (
    n_aluguer                  INTEGER(5) NOT NULL,
    n_socio                  INTEGER(5) NOT NULL,
    cod_filme                             INTEGER(5) NOT NULL,
    n_copia                               INTEGER(5) NOT NULL,
    modalidade                CHAR(10) NOT NULL,
    data_aluguer                DATE,
    data_entrega                         DATE,
    preço                           DECIMAL(5,2) NOT NULL,
    multa                                   DECIMAL (5,2),
    
    CHECK (multa >=0 OR NULL),
    CHECK (preço >=0),
    
    
    CONSTRAINT ch_prim_n_aluguer
        PRIMARY KEY (n_aluguer),
        
        CONSTRAINT ch_estr_n_socio 
        FOREIGN KEY (n_socio)
        REFERENCES socios (n_socio)
        ON UPDATE CASCADE  
        ON DELETE CASCADE, 
        
        CONSTRAINT ch_estr_cod_filme 
        FOREIGN KEY (cod_filme,n_copia)
        REFERENCES copias (cod_filme,n_copia) 
        ON UPDATE CASCADE
        ON DELETE CASCADE,
        
        CONSTRAINT ch_estr_modalidade
        FOREIGN KEY (modalidade)
        REFERENCES modalidades (modalidade)
        ON UPDATE CASCADE 
        ON DELETE CASCADE
);

agora estou a tentar definir uma query que vai seleccionar a multa maxima e vai dar o respectivo cod_filme.

select multa,cod_filme
    from alugueres
    group by cod_filme
               having max(multa);

esta query n me dá o k quero. será me podem ajudar...

obrigado

[Denis Courcy]

agora estou a tentar definir uma query que vai seleccionar a multa maxima e vai dar o respectivo cod_filme.

select multa,cod_filme
    from alugueres
    group by cod_filme
               having max(multa);

esta query n me dá o k quero. será me podem ajudar...

Tente:

select cod_filme, max(multa)
    from alugueres
    group by cod_filme;

Se você quer para um filme específico, então use:

select max(multa)
    from alugueres
    where cod_filme = ofilmequequero;

[Anderson F R]

a multa máxima você pode pegar via order by e limit

SELECT multa FROM alugueres ORDER BY multa DESC LIMIT 1

o primeiro resultado vai ser a maior multa.

[MIMP]

vou aproveitar este tópico para tirar todas as minhas .....

base de dados:

DROP DATABASE IF EXISTS agencia_bancaria; 
CREATE DATABASE agencia_bancaria;
USE agencia_bancaria;

CREATE TABLE tipos_cartao (
    tipo                      CHAR(10) NOT NULL, 
    condicoes_pagamento          CHAR(30),
    limite_credito              DECIMAL(7,2),
    
    CHECK(limite_credito>0),
    
    CONSTRAINT ch_prim_tipo
        PRIMARY KEY (tipo)
);

CREATE TABLE clientes (
    n_cliente                  INTEGER(5) NOT NULL, 
    nome                        CHAR(30) NOT NULL,
    morada                        VARCHAR(30),
    telefone                   INTEGER (9),
    bi                           INTEGER (9),
    nif                         INTEGER(9),
    
    CONSTRAINT ch_candidata_clientes UNIQUE (nome,bi,nif),
    
    CONSTRAINT ch_prim_n_cliente
        PRIMARY KEY (n_cliente)
);

CREATE TABLE contas_bancarias (
    n_conta                          INTEGER(5) NOT NULL, 
    n_cliente                      INTEGER(5) NOT NULL,
    saldo_contab                  DECIMAL(7,2),
    saldo_dispon                 DECIMAL(7,2),
    
    CHECK (saldo_contab >= 0),
    
    CONSTRAINT ch_prim_n_conta
        PRIMARY KEY (n_conta),
        
        CONSTRAINT ch_estr_n_cliente 
        FOREIGN KEY (n_cliente)
        REFERENCES clientes (n_cliente)
        ON UPDATE CASCADE  
        ON DELETE CASCADE
);

CREATE TABLE cartoes_credito (
    n_cartao                 INTEGER(5) NOT NULL,
    tipo                    CHAR(10) NOT NULL,
    n_cliente                INTEGER(5) NOT NULL,    
    n_conta                    INTEGER(5) NOT NULL,
    data_emissao            DATE,
    prazo_validade            DATE,
    valor_em_credito        DECIMAL(7,2),
    
    CONSTRAINT ch_prim_n_cartao
        PRIMARY KEY (n_cartao),
        
    CONSTRAINT ch_estr_tipo 
    FOREIGN KEY (tipo)
    REFERENCES tipos_cartao (tipo)
    ON UPDATE CASCADE  
    ON DELETE CASCADE,
    
    CONSTRAINT ch_estr_n_cliente1 
    FOREIGN KEY (n_cliente)
    REFERENCES clientes (n_cliente)
    ON UPDATE CASCADE  
    ON DELETE CASCADE, 
    
    CONSTRAINT ch_estr_n_conta 
    FOREIGN KEY (n_conta)
    REFERENCES contas_bancarias (n_conta)
    ON UPDATE CASCADE  
    ON DELETE CASCADE 
    
);

estou a tentar criar uma query que me dê as contas bancarias cujo saldo disponivel seja inferior ao total do valor em credito de todos os cartoes emitidos sobre cada uma dessas contas

select *
    from contas_bancarias
    where n_conta in
                (select cc.n_conta,sum(valor_em_credito)
                    from cartoes_credito cc, contas_bancarias cb
                    where cc.n_conta=cb.n_conta 
                    group by n_conta
                    having saldo_dispon<sum(valor_em_credito));

esta query dá-me erro pk não posso ir buscar sozinho o n_conta á sub_query. será k me podiam ajudar sff

Obrigado

[Denis Courcy]

a multa máxima você pode pegar via order by e limit

SELECT multa FROM alugueres ORDER BY multa DESC LIMIT 1

o primeiro resultado vai ser a maior multa.

Executar a query deste jeito implica em avaliar toda a tabela em busca do maior valor, ou seja, executar um table scan. Que, dependendo do tamanho da tabela, levará muito tempo para ser executado, mesmo que haja um índice para este campo.

[Denis Courcy]

estou a tentar criar uma query que me dê as contas bancarias cujo saldo disponivel seja inferior ao total do valor em credito de todos os cartoes emitidos sobre cada uma dessas contas

Faça assim:

SELECT cb.n_conta, cb.saldo_dispon, SUM(cc.valor_em_credito) AS credito_cartao
FROM contas_bancarias cb
INNER JOIN cartoes_credito cc ON cc.n_conta = cb.n_conta 
GROUP BY cb.n_conta
HAVING cb.saldo_dispon < sum(cc.valor_em_credito);

[MIMP]

será k me podiam explicar a seguinte função.....

drop function if exists multa_a_pagar;
delimiter @                    
CREATE function multa_a_pagar (aluguer int)
            returns decimal(4,2)
            begin
                declare modal char(1);
                declare hoje date;
                declare m_d decimal(4,2);
                declare d_a date;
                declare dias_multa int;
                select modalid,data_aluguer
                into modal,d_a
                from alugueres
                where num_aluguer=aluguer;
                select multa_diaria
                into m_d
                from modalidades
                where modalid=modal;
                set hoje = now();  
                set dias_multa = hoje - d_a -1; 
                return (dias_multa * m_d);
                end;

sei k esta funçao está relacionada com o armazenamento de codigo na base de dados. Tambem me podiam explicar sff como isso funciona...

Obrigado

[Denis Courcy]

será k me podiam explicar a seguinte função.....

sei k esta funçao está relacionada com o armazenamento de codigo na base de dados. Tambem me podiam explicar sff como isso funciona...

Esta é uma função definida pelo usuário (UDF), também conhecida como storage function. Diferente da storage procedure que não retorna valor, este tipo de código sempre retornará um valor. Neste caso, retornará um valor do tipo decimal.Vou comentar cada linha.

drop function if exists multa_a_pagar; /*exclui a função se ela já existir */
delimiter @                    /*determina que o caracter @ será usado como delimitador de campos */
CREATE function multa_a_pagar (aluguer int)  /* cria a função passando um valor inteiro como parâmetro */
            returns decimal(4,2) /* informa o tivo de valor a ser retornado pela função */
begin /* inicio do corpo da função */
     declare modal char(1); /*declara variaveis */
     declare hoje date;
     declare m_d decimal(4,2);
     declare d_a date;
     declare dias_multa int;

     select modalid,data_aluguer /* executa um select que criará um arquivo */
     into modal,d_a
     from alugueres
     where num_aluguer=aluguer;

     select multa_diaria
     into m_d
     from modalidades
     where modalid=modal;
     set hoje = now();  
     
     set dias_multa = hoje - d_a -1; /*atribui valor a uma variavel */
     return (dias_multa * m_d); /* retorna um valor */
end; /* encerra o corpo da função */