Question

/* 1. Fix the CREATE and INSERT statements below to create the SHIPMENT table and insert its data in DB Fiddle*/

CREATE TABLE SHIPMENT (
ShipmentID Int NOT NULL,
ShipperName Char(35) NOT NULL,
ShipperInvoiceNumber Int NOT NULL
DepartureDate Date NULL,
ArrivalDate Date NULL,
InsuredValue Numeric(12,2) NOT NULL,
CONSTRAINT Shipment_PK PRIMARY KEY (ShipmentID))
);

INSERT INTO SHIPMENT VALUES (1,'ABC Trans-Oceanic', 2008651, '10-Dec-14', '15-Mar-18', 15000.00);
INSERT INTO SHIPMENT VALUES (2,'ABC Trans-Oceanic', 2009012, '10-Jan-18', '20-Mar-18', 12000.00);
INSERT INTO SHIPMENT VALUES (3,'Worldwide', 49100300, '05-May-18', '17-Jun-18', 20000.00)
INSERT INTO SHIPMENT VALUES (4,'International', 399400, '02-Jun-18', '17-Jul-18', 17500.00 );
INSERT INTO SHIPMENT VALUES (5,'Worldwide', 84899440, '10-Jul-18', '28-Jul-18', 25000.00);
INSERT INTO SHIPMENT VALUES (6,'International', 488955, '05-Aug-18', '11-Sep-18', 18000.00);

Now you can work on the following 5 queries:

/* Query 1. Show the shipper, the invoice number and insured value for each shipment. */

/* Query 2. Show the shipper and the number of shipments made by each one. */

/* Query 3. Show the shipper and the number of shipments made by each one but only include shipments with insured values that are 15,000 or over. */

/* Query 4. Show the shipper and the number of shipments made by each one but only include shipments with insured values that are 15,000 or over and only show shippers with 2 or more shipments.*/

/* Query 5. Show the shipper, the number of shipments made by each one and the total insured value for all shipments made by each shipper -- but only include shipments with insured values that are 15,000 or over and only show shippers with 2 or more shipments and only show shippers with a total insured value over 40000. */

Answer 1

1.Table Name :SHIPMENT
CREATE TABLE SHIPMENT (
ShipmentID Int NOT NULL,
ShipperName Char(35) NOT NULL,
ShipperInvoiceNumber Int NOT NULL,
DepartureDate Date NULL,
ArrivalDate Date NULL,
InsuredValue Numeric(12,2) NOT NULL,
CONSTRAINT Shipment_PK PRIMARY KEY (ShipmentID));

/*inserting data*/
INSERT INTO SHIPMENT VALUES (1,'ABC Trans-Oceanic', 2008651, '14/12/10', '18/03/15', 15000.00);
INSERT INTO SHIPMENT VALUES (2,'ABC Trans-Oceanic', 2009012, '18/01/10', '18/03/20', 12000.00);
INSERT INTO SHIPMENT VALUES (3,'Worldwide', 49100300, '18/05/05', '18/06/17', 20000.00);
INSERT INTO SHIPMENT VALUES (4,'International', 399400, '18/06/02', '18/07/17', 17500.00 );
INSERT INTO SHIPMENT VALUES (5,'Worldwide', 84899440, '18/07/10', '18/07/28', 25000.00);
INSERT INTO SHIPMENT VALUES (6,'International', 488955, '18/08/05', '18/09/11', 18000.00);

/*select records from SHIPMENT*/
select * from SHIPMENT;

**************************************************

/* the shipper, the invoice number and insured value for each shipment. */ :

SQL query :

select ShipperName,ShipperInvoiceNumber,InsuredValue from SHIPMENT;

Query result :

**************************************

/* the shipper and the number of shipments made by each one. */

SQL query :

select ShipperName,count(ShipperInvoiceNumber) as 'Number of shipments' from
SHIPMENT group by ShipperName;

Query result :

************************************

/* the shipper and the number of shipments made by each one but only include shipments with insured values that are 15,000 or over. */

SQL query :

select ShipperName,count(ShipperInvoiceNumber) as 'Number of shipments' from
SHIPMENT where InsuredValue>15000
group by ShipperName;

Query result :

****************************************
/* the shipper and the number of shipments made by each one but only include shipments with insured values that are 15,000 or over and only show shippers with 2 or more shipments.*/

SQL query :

select ShipperName,count(ShipperInvoiceNumber) as 'Number of shipments' from
SHIPMENT where InsuredValue>15000
group by ShipperName
having count(ShipperInvoiceNumber)>=2;

Query result :

************************************

/* the shipper, the number of shipments made by each one and the total insured value for all shipments made by each shipper -- but only include shipments with insured values that are 15,000 or over and only show shippers with 2 or more shipments and only show shippers with a total insured value over 40000. */

SQL query :

select ShipperName,count(ShipperInvoiceNumber) as 'Number of shipments' from
SHIPMENT where InsuredValue>15000
group by ShipperName
having count(ShipperInvoiceNumber)>=2 and sum(InsuredValue)>40000;

Query result :

************************************