Completely Solved C, C++ Programs Assignment.




SQL-Query performs on a Business Oriented Database system

Filed Under: ,

Problem Statement:- Query performs on a Business Oriented Database system.

Consider the following employee database:-
i)Customer ( Custid, Custname, Annual_revenue, Custtype)
ii)Shipment ( Shipmentno, Custid, Weight, Truclno, Destination)
iii)Truck ( Truckno, Drivername)
iv)City ( Cityname, Population)

Given Queries:-
1.What are the names and populations of cities that have received shipments weighing over
100 pounds?
2.List the cities that have received shipments from every customer.
3.List the name and revenue of customers where shipments have been delivered by truck driver
Jenson.

Commands for table creations of the schemas in:-

i) SQL> create table Customer
2 ( custid number(5),
3 custname varchar2(15) NOT NULL,
4 revenue number(10) default 100000,
5 custtype varchar2(15),
6 PRIMARY KEY(custid),
7 CHECK(custid > = 100 and custid < = 10000), 8 CHECK(custtype in ( ‘manufacture’ , ‘wholesale’ , ‘retail’ )) ); Table Created. Name Null? Type ----------------------------------------- -------- ------------- CUSTID NOT NULL NUMBER(5) CUSTNAME NOT NULL VARCHAR2(15) REVENUE NUMBER(10) CUSTTYPE VARCHAR2(15) SQL> INSERT INTO Customer VALUES (100, ‘ram’ ,100500 , ‘wholesaler’ );
1 row created.
SQL> INSERT INTO Customer VALUES (150, ‘shyam’ ,100600 , ‘wholesaler’ );
1 row created.
SQL> INSERT INTO Customer VALUES (200, ‘jadu’ ,100900 , ‘retailer’ );
1 row created.
SQL> INSERT INTO Customer VALUES (250, ‘madhu’ ,101000 , ‘manufacturer’ );
1 row created.
SQL> INSERT INTO Customer VALUES (300, ‘ajay’ ,102000 , ‘manufacturer’ );
1 row created.
SQL> INSERT INTO Customer VALUES (350, ‘bijay’ ,102000 , ‘retailer’ );
1 row created.

ii) SQL> create table Shipment
2 ( shipmentno number(5),
3 custid number(5),
4 weight number(7,2),dr
5 truckno number(5),
6 destination varchar2(15),
7 PRIMARY KEY(shipmentno),
8 FOREIGN KEY (custid) references Customer(custid),
9 FOREIGN KEY (truckno) references Truck(truckno),
10 FOREIGN KEY (destination) references City(cityname) );
Table Created.

Name Null? Type
----------------------------------------- -------- -------------
SHIPMENTNO NOT NULL NUMBER(5)
CUSTID NUMBER(5)
WEIGHT NUMBER(7,2)
TRUCKNO NUMBER(5)
DESTINATION VARCHAR2(15)

SQL> INSERT INTO Shipment VALUES (500,200,110,600, ‘delhi’ );
1 row created.
SQL> INSERT INTO Shipment VALUES (510,150,510,610, ‘kanpur’ );
1 row created.
SQL> INSERT INTO Shipment VALUES (520,250,90,620, ‘kolkata’ );
1 row created.
SQL> INSERT INTO Shipment VALUES (530,300,50,630, ‘nagpur’ );
1 row created.
SQL> INSERT INTO Shipment VALUES (540,350,99,620, ‘patna’ );
1 row created.

iii) SQL> create table Truck
2 ( truckno number(5),
3 drivername varchar2(15),
4 PRIMARY KEY(truckno) );
Table Created.

Name Null? Type
----------------------------------------- -------- ------------
TRUCKNO NOT NULL NUMBER(5)
DRIVERNAME VARCHAR2(15)

SQL> INSERT INTO Truck VALUES (620, ‘jenson’ );
1 row created.
SQL> INSERT INTO Truck VALUES (600, ‘nagen’ );
1 row created.
SQL> INSERT INTO Truck VALUES (610, ‘shanu’ );
1 row created.
SQL> INSERT INTO Truck VALUES (630, ‘sanjay’);
1 row created.


iv) SQL> create table City
2 ( cityname varchar2(15),
3 population number(5),
4 PRIMARY KEY(cityname) );
Table Created.

Name Null? Type
----------------------------------------- -------- -------------
CITYNAME NOT NULL VARCHAR2(15)
POPULATION NUMBER(5)


SQL> INSERT INTO City VALUES (‘delhi’ , 350000 );
1 row created.
SQL> INSERT INTO City VALUES (‘nagpur’ , 150000 );
1 row created.
SQL> INSERT INTO City VALUES (‘kanpur’ , 330000 );
1 row created.
SQL> INSERT INTO City VALUES (‘kolkata’ , 400000 );
1 row created.
SQL> INSERT INTO City VALUES (‘patna’ , 200000 );
1 row created.



Solution for the queries:-

Query Number 1 :
What are the names and populations of cities that have received shipments weighing over
100 pounds?

SQL Statement
SQL> select custname “NAME” , revenue “REVENUE”
2 from Customer
3 where custid in( select custid
4 from Shipment
5 where weight > 100 );

Output to Query number 1

NAME REVENUE
----------------------- --------------
shyam 100600
jadu 100900


Query Number 2 :
List the cities that have received shipments from every customer.

SQL Statement
SQL> select cityname “CITYNAME”
2 from City
3 where cityname in ( select destination
4 from Shipment
5 where custid in ( select
6 custid from Customer));

Output to Query number 2

CITYNAME
---------------------------
kolkata
delhi
kanpur
nagpur
patna


Query Number 3 :
List the name and revenue of customers where shipments have been delivered by truck driver
Jenson.

SQL Statement
SQL> select custname “NAME” , revenue “REVENUE”
2 from Customer
3 where custid in ( select custid
4 from Shipment
5 where truckno in ( select truckno
6 from truck
7 where drivername=’jenson’));

Output to Query number 3

NAME REVENUE
---------------------- ------------------
madhu 101000
bijay 102000



Discussion:-
The above database created is used to find out different information about a shipment business customers, shipments, truck, and city.
We have solved some given queries using this database, some of which have alternative methods.
Example:-
Query no. 2 as written above to find the cities that have received shipments from every customer can also be done by searching out total no. of customers in table customer and comparing it with total no. of customers from whom each city received shipment. If both matches then that row gets selected.
We can write this as follows:-
“select destination,count(*) from shipment group by destination having count(*)=(select count(*) from customer);”
This would also give us the same output we desire.
 Back to main directory: Software Practical | Structured Query Language | DBMS


Get Free Programming Tutorials and Solved assignments