Un paio di query extra-difficili

di
JackTortora
il
3 risposte
J
JackTortora
Pochi posts
Iscritto da
nov, 2019
Messaggi:
2

Un paio di query extra-difficili

Buongiorno,

sperando che questa sia la sezione giusta, vorrei proporre le seguenti interrogazioni (che sto faticando molto a risolvere):

1)
STUDENTE(MatricolaS, Nome, Cognome, Corso di Laurea)
HOMEWORK DA CONSEGNARE(CodHW, Titolo, Argomento, DataScadenzaPrevista)
DOCENTE(CodDocente, Nome, Cognome, Dipartimento)
VALUTAZIONE HOMEWORK CONSEGNATI(MatricolaS, CodHW, CodDocente,
DataConsegna, DataValutazione, Valutazione)

Visualizzare la matricola, il cognome e il corso di laurea di ciascun studente che non ha
mai consegnato un compito in una data successiva alla data di scadenza prevista, e che
ha consegnato tutti i compiti attesi ricevendo in ciascuno di questi la valutazione più
alta.

2)
TAVOLO(NumTavolo, NumPosti)
PIATTO(CodP, DescrizioneP, Prezzo)
ORDINAZIONE(NumOrdinazione, Data, NumTavolo)
PIATTO-ORDINATO(CodP,NumOrdinazione, Quantità)
INGREDIENTI(CodP, NomeIngrediente)

Per i piatti con prezzo maggiore di 10 euro ordinati in almeno 10 tavoli diversi nella stessa
giornata, visualizzare il codice del piatto, la descrizione, la quantità totale di piatti ordinati
nella giornata e l'importo totale corrispondente.

Grazie a chiunque riesca a darmi una mano

3 Risposte

  • Biagio De Franco
    Biagio De Franco
    Utente Attivo
    Iscritto da
    mag, 2017
    Messaggi:
    384

    Re: Un paio di query extra-difficili

    JackTortora ha scritto:

    ......
    Non so se ho ben capito......all'ingrosso prova così
    1)
    SELECT MATRICOLA, COGNOME, CORSO_DI_LAUREA
    FROM STUDENTE
    INNER JOIN VALUTAZIONE_HOMEWORK_CONSEGNATI ON STUDENTE.MATRICOLAS=VALUTAZIONE_HOMEWORK_CONSEGNATI.MATRICOLAS
    INNER JOIN HOMEWORK_DA_CONSEGNARE ON HOMEWORK_DA_CONSEGNARE.CODHW=VALUTAZIONE_HOMEWORK_CONSEGNATI.CODHW
    WHERE
    MAX(VALUTAZIONE_HOMEWORK_CONSEGNATI.DATACONSEGNA)<=HOMEWORK_DA_CONSEGNARE.DATASCADENZAPREVISTA
    GROUP BY MATRICOLAS

    2)

    SELECT CODP, DESCRIZIONE, SUM(QUANTITA) AS NUMERO_PIATTI, SUM(QUANTITA)*PREZZO AS IMPORTO
    FROM PIATTO
    INNER JOIN PIATTO_ORDINATO ON PIATTO.CODP=PIATTO_ORDINATO.CODP
    INNER JOIN ORDINAZIONE ON PIATTO_ORDINATO.NUMORDINAZIONE=ORDINAZIONE.NUMORDINAZIONE
    INNER JOIN TAVOLO ON ORDINAZIONE.NUMTAVOLO=TAVOLO.NUMTAVOLO
    WHERE PREZZO>10
    AND COUNT(NUMTAVOLO)>=10
    GROUP BY CODP, DATA
  • J
    JackTortora
    Pochi posts
    Iscritto da
    nov, 2019
    Messaggi:
    2

    Re: Un paio di query extra-difficili

    Ti ringrazio molto per i tentativi, tuttavia i punti più difficili non riesco ancora a risolverli poiché nel 1) occorre selezionare le matricole che per ogni homework hanno ottenuto la massima valutazione in quel particolare homework; nel 2), invece, avevo pensato anche io di raggruppare per Data ma mi risulta che quanto compare nel GROUP BY debba essere scritto anche nel SELECT, a meno delle funzioni aggregate, e anche riuscendo a scrivere qualcosa di simile penso sarebbe ancora incompleto.
  • A
    asql
    Molto attivo
    Iscritto da
    dic, 2018
    Messaggi:
    515

    Re: Un paio di query extra-difficili

    Salve a tutti,

    certo che cosi' e' complicato anche rispondere... sarebbe a mio parere meglio fornire gia' sia il DDL iniziale che un po' di INSERT INTO al fine di definire uno scenario popolato come desiderato...
    ci si mette piu' tempo a preparare lo scenario che a scrivere le query :D

    di seguito, se ho compreso gli scenari, una soluzione in Transact-SQL (SQL Server)
    problema 1: 
     
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE [dbo].[STUDENTE] (
	MatricolaS int, 
	Nome varchar(5), 
	Cognome varchar(5), 
	[Corso di Laurea] varchar(5)
	);
CREATE TABLE [dbo].[HOMEWORK DA CONSEGNARE] (
	CodHW int, 
	Titolo varchar(5), 
	Argomento varchar(5),  
	DataScadenzaPrevista date
	);
CREATE TABLE [dbo].[DOCENTE](
	CodDocente int, 
	Nome varchar(5), 
	Cognome varchar(5),  
	Dipartimento varchar(5)
	);
CREATE TABLE [dbo].[VALUTAZIONE HOMEWORK CONSEGNATI] (
	MatricolaS int, 
	CodHW int, 
	CodDocente int, 
	DataConsegna date, 
	DataValutazione date, 
	Valutazione int
	);
GO
INSERT INTO [dbo].[DOCENTE] 
	VALUES ( NULL, NULL, NULL, NULL );	 -- NON SERVE

INSERT INTO [dbo].[STUDENTE]
	VALUES ( 1, 'Sn1', 'Sc1', 'L1' ), ( 2, 'Sn2', 'Sc2', 'L1' ), ( 3, 'Sn3', 'Sc3', 'L2' ), ( 4, 'Sn4', 'Sc4', 'L3' ), ( 5, 'Sn5', 'Sc5', 'L1' ), ( 6, 'Sn6', 'Sc6', 'L2' );

INSERT INTO [dbo].[HOMEWORK DA CONSEGNARE]
	VALUES (1, 'T1', 'TA1', '2019-01-01'), (2, 'T2', 'TA2', '2019-02-01'), (3, 'T3', 'TA3', '2019-03-01'), (4, 'T4', 'TA4', '2019-04-01');

INSERT INTO [dbo].[VALUTAZIONE HOMEWORK CONSEGNATI]
	VALUES ( 1, 1, 1, '2019-01-01', NULL, 5 ) -- SI + SI
		,  ( 2, 1, 1, '2019-01-02', NULL, 5 ) -- NO
		,  ( 3, 1, 1, '2019-01-01', NULL, 4 ) -- SI
		,  ( 4, 1, 1, '2019-01-02', NULL, 5 ) -- NO
		,  ( 5, 1, 1, '2019-01-01', NULL, 5 ) -- SI + SI
		,  ( 6, 1, 1, '2019-01-01', NULL, 5 ) -- SI + SI

		,  ( 1, 2, 1, '2019-02-01', NULL, 5 ) -- SI + SI
		,  ( 2, 2, 1, '2019-02-02', NULL, 5 ) -- NO
		,  ( 3, 2, 1, '2019-02-02', NULL, 5 ) -- NO
		,  ( 4, 2, 1, '2019-02-02', NULL, 5 ) -- NO
		,  ( 5, 2, 1, '2019-02-01', NULL, 4 ) -- SI + NO
		,  ( 6, 2, 1, '2019-02-01', NULL, 5 ) -- SI + SI

		,  ( 1, 3, 1, '2019-03-01', NULL, 5 ) -- SI + SI
		,  ( 2, 3, 1, '2019-03-01', NULL, 4 ) -- SI + NO
		,  ( 3, 3, 1, '2019-03-01', NULL, 4 ) -- SI + NO
		,  ( 4, 3, 1, '2019-03-02', NULL, 5 ) -- NO
		,  ( 5, 3, 1, '2019-03-02', NULL, 5 ) -- NO
		,  ( 6, 3, 1, '2019-03-01', NULL, 5 ) -- SI + SI

		,  ( 1, 4, 1, '2019-04-01', NULL, 5 ) -- SI + SI
		,  ( 2, 4, 1, '2019-04-01', NULL, 4 ) -- SI + NO
		,  ( 3, 4, 1, '2019-04-01', NULL, 4 ) -- SI + NO
		,  ( 4, 4, 1, '2019-04-02', NULL, 5 ) -- NO
		,  ( 5, 4, 1, '2019-04-02', NULL, 5 ) -- NO
		,  ( 6, 4, 1, '2019-04-01', NULL, 5 ) -- SI + SI
		;

WITH cteVoti AS (
	SELECT w.[CodHW], MAX(wc.Valutazione) AS [Voto]
		FROM [dbo].[HOMEWORK DA CONSEGNARE] w
			JOIN [dbo].[VALUTAZIONE HOMEWORK CONSEGNATI] wc ON wc.[CodHW] = w.[CodHW]
			JOIN [dbo].[HOMEWORK DA CONSEGNARE] ww ON ww.[CodHW] = wc.[CodHW]
		WHERE wc.[DataConsegna] <= ww.[DataScadenzaPrevista]		
		GROUP BY w.[CodHW]
	),
cteStudenti AS (
	SELECT wc.[MatricolaS]
		FROM [dbo].[VALUTAZIONE HOMEWORK CONSEGNATI] wc 
			JOIN cteVoti cv ON wc.[CodHW] = cv.[CodHW] AND wc.[Valutazione] = cv.[Voto]
			JOIN [dbo].[HOMEWORK DA CONSEGNARE] ww ON ww.[CodHW] = wc.[CodHW] 
		WHERE wc.[DataConsegna] <= ww.[DataScadenzaPrevista]
		GROUP BY wc.[MatricolaS]
			HAVING COUNT(*) = (SELECT COUNT(*) FROM [dbo].[HOMEWORK DA CONSEGNARE])
	)
	SELECT s.[MatricolaS], s.[Cognome], s.[Corso di Laurea]
		FROM [dbo].[STUDENTE] s
			JOIN cteStudenti cs ON cs.[MatricolaS] = s.[MatricolaS];

GO

--<------------
MatricolaS  Cognome Corso di Laurea
----------- ------- ---------------
1           Sc1     L1
6           Sc6     L2
    problema 2: 
    
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE [dbo].[TAVOLO] (
	NumTavolo int, 
	NumPosti int
	);
CREATE TABLE [dbo].[PIATTO] (
	CodP int, 
	DescrizioneP varchar(5), 
	Prezzo decimal (18,4)
	);
CREATE TABLE [dbo].[ORDINAZIONE] (
	NumOrdinazione int, 
	Data datetime, 
	NumTavolo int
	);
CREATE TABLE [dbo].[PIATTO-ORDINATO] (
	CodP int,
	NumOrdinazione int, 
	[Quantità] int
	);
CREATE TABLE [dbo].[INGREDIENTI] (
	CodP int, 
	NomeIngrediente varchar(5)
	);
GO
INSERT INTO [dbo].[TAVOLO]
	VALUES ( 1, 2 ), ( 2, 2 ), ( 3, 2 ), ( 4, 2 ), ( 5, 2 ), ( 6, 2 ), ( 7, 2 ), ( 8, 2 ), ( 8, 2 ), ( 9, 2 ), ( 10, 2 );
INSERT INTO [dbo].[PIATTO]
	VALUES ( 1, 'p1', 10 ), ( 2, 'p2', 11 ), ( 3, 'p3', 12 ), ( 4, 'p4', 12 ), ( 5, 'p5', 12 ), ( 6, 'p6', 12 );
INSERT INTO [dbo].[INGREDIENTI]
	VALUES ( NULL, NULL ); -- NON SERVE
INSERT INTO [dbo].[ORDINAZIONE]
	VALUES ( 1, '2019-01-01', 1 ), ( 2, '2019-01-01', 2 ), ( 3, '2019-01-01', 3 ), ( 4, '2019-01-01', 4 ), ( 5, '2019-01-01', 5 ), ( 6, '2019-01-01', 6 ), ( 7, '2019-01-01', 7 ), ( 8, '2019-01-01', 8 ), ( 9, '2019-01-01', 9 ), ( 10, '2019-01-01', 10 );
INSERT INTO [dbo].[PIATTO-ORDINATO]
	VALUES ( 1, 1, 1 ), ( 1, 2, 1 ), ( 1, 3, 1 ), ( 1, 4, 1 ), ( 1, 5, 1 ), ( 1, 6, 1 ), ( 1, 7, 1 ), ( 1, 8, 1 ), ( 1, 9, 1 ), ( 1, 10, 1 ) -- NO
		,	( 2, 1, 1 ), ( 2, 2, 1 ), ( 2, 3, 1 ), ( 2, 4, 1 ), ( 2, 5, 1 ), ( 2, 6, 1 ), ( 2, 7, 1 ), ( 2, 8, 1 ), ( 2, 9, 1 ), ( 2, 10, 1 ) --piatto 2 SI
		,	( 3, 1, 1 ) -- NO
		,	( 4, 1, 2 ), ( 4, 2, 2 ), ( 4, 3, 2 ), ( 4, 4, 2 ), ( 4, 5, 2 ), ( 4, 6, 2 ), ( 4, 7, 2 ), ( 4, 8, 2 ), ( 4, 9, 2 ), ( 4, 10, 2 ) --piatto 4 SI
		,	( 5, 1, 1 ), ( 5, 2, 1 ), ( 5, 3, 1 ), ( 5, 4, 1 ), ( 5, 5, 1 ), ( 5, 6, 1 ), ( 5, 7, 1 ), ( 5, 8, 1 ), ( 5, 9, 1 ), ( 5, 10, 1 ) --piatto 5 SI
		,	( 6, 1, 1 ), ( 6, 1, 1 ), ( 6, 3, 1 ), ( 6, 4, 1 ), ( 6, 5, 1 ), ( 6, 6, 1 ), ( 6, 7, 1 ), ( 6, 8, 1 ), ( 6, 9, 1 ), ( 6, 10, 1 ) -- NO
		;

DECLARE @data date = '2019-01-01';
WITH ctePiattoTavoli AS (
	SELECT po.[CodP], COUNT( DISTINCT t.[NumTavolo]) AS [TavoliServiti]
		FROM [dbo].[PIATTO-ORDINATO] po
			JOIN [dbo].[ORDINAZIONE] o ON o.[NumOrdinazione] = po.[NumOrdinazione]
			JOIN [dbo].[PIATTO] p ON p.[CodP] = po.[CodP] 
			JOIN [dbo].[TAVOLO] t ON t.[NumTavolo] = o.[NumTavolo]
		WHERE o.[Data] = @data
			AND p.[Prezzo] > 10
		GROUP BY po.[CodP]
	),
ctePiattoQta AS (
	SELECT po.[CodP], SUM(po.[Quantità]) AS [qta]
		FROM [dbo].[ORDINAZIONE] o
			JOIN [dbo].[PIATTO-ORDINATO] po ON po.[NumOrdinazione] = o.[NumOrdinazione]
			JOIN [dbo].[PIATTO] p ON p.[CodP] = po.[CodP] AND p.[Prezzo] > 10
		WHERE o.[Data] =  @data
		GROUP BY po.[CodP]
	)
	SELECT p.[CodP], p.[DescrizioneP], pq.[qta], pq.[qta] * p.[Prezzo] AS [Totale]
		FROM [dbo].[PIATTO] p
			JOIN ctePiattoQta pq ON pq.[CodP] = p.[CodP]
			JOIN ctePiattoTavoli pt ON pt.[CodP] = p.[CodP]
		WHERE pt.[TavoliServiti] >= 10;

GO
--<----------
CodP  DescrizioneP qta  Totale
----- ------------ ---- ----------
2     p2           10   110.0000
4     p4           20   240.0000
5     p5           10   120.0000

    anche se eseguito in tempdb, ricordarsi magari al termine di distruggere gli oggetti, comando DDL che non inserisco per non subire le penalizzazioni del parser di valutazione dei post del forum...
    salutoni omnia
    --
    Andrea
Devi accedere o registrarti per scrivere nel forum
3 risposte