Product Master

Practical No. # 2

Create the Product_Master table described below:

Column Name	Datatype	Size	Constraints
Product_No	Char	6	Primary Key
Description	Varchar2	15	Not Null
ProfitPercent	Number	4,2	Not Null
UnitMeasure	Varchar2	10	Not Null
QtyOnHand	Number	8	Not Null
ReOrderLvl	Number	8	Not Null
SellPrice	Number	8,2	Not Null
CostPrice	Number	8,2	Not Null

Solution:

CREATE TABLE PRODUCT_MASTER(

PRODUCT_NO CHAR(6) PRIMARY KEY,

DESCRIPTION VARCHAR2(15) NOT NULL,

PROFITPERCENT NUMBER (4,2) NOT NULL,

UNITMEASURE VARCHAR2(10) NOT NULL,

QTYONHAND NUMBER(8) NOT NULL,

REORDERLVL NUMBER(8) NOT NULL,

SELLPRICE NUMBER(8,2) NOT NULL,

COSTPRICE NUMBER(8,2) NOT NULL);

Insert the following data into the table:

PRODUCT_NO	DESCRIPTION	PROFITPERCENT	UNITMEASURE	QTYONHAND	REORDERLVL	SELLPRICE	COSTPRICE
P00001	1.44 Floppies	5	Price	100	20	525	500
P00002	Monitors	6	Price	10	3	12000	11280
P00003	Mouse	5	Price	20	5	1050	1000
P00004	1.22 Floppies	5	Price	100	20	525	500
P00005	Keyboard	2	Price	10	3	3150	3050
P00006	540 HDD	2.5	Price	10	3	5250	5100

Perform the following queries on the table:

Q1: List the various products available from the Product_Master table.

Solution:

SELECT DESCRIPTION FROM PRODUCT_MASTER;

Output:

DESCRIPTION

1.44 Floppies

Monitors

Mouse

1.22 Floppies

Keyboard

540 HDD

Q2: Find the products with description as 1.44 and 1.22 floppies

Solution:

SELECT DESCRIPTION FROM PRODUCT_MASTER WHERE DESCRIPTION = ‘1.44 Floppies’ OR DESCRIPTION = ‘1.22 Floppies’;

Output:

DESCRIPTION

1.44 Floppies

1.22 Floppies

Q3: List the products whose selling price is greater than 2000 and less than or equal to 5000.

Solution:

SELECT DESCRIPTION FROM PRODUCT_MASTER WHERE SELLPRICE > 2000 AND SELLPRICE <= 5000;

Output:

DESCRIPTION

Keyboard

Q4: List products whose selling price is more than 5000. Calculate a new selling price as, original selling price * 0.15. Rename the new column in the output of the above query as New_Price.

Solution:

SELECT DESCRIPTION, SELLPRICE*0.15 “NEW_PRICE” FROM PRODUCT_MASTER WHERE SELLPRICE > 5000;

Output:

DESCRIPTION	NEW_PRICE
Monitors	1800
540 HDD	787.5

Q5: List all the products whose cost price is less than 15000.

Solution:

SELECT DESCRIPTION FROM PRODUCT_MASTER WHERE COSTPRICE <>

Output:

DESCRIPTION

1.44 Floppies

Monitors

Mouse

1.22 Floppies

Keyboard

540 HDD

Q6: Calculate square root of price of each product.

Solution:

SELECT SQRT(SELLPRICE), SQRT(COSTPRICE) FROM PRODUCT_MASTER;

Output:

SQRT(SELLPRICE)	SQRT(COSTPRICE)
22.91287847477920003294023596864004244492	22.3606797749978969640917366873127623544
109.544511501033222691393956560160426791	106.207344378814029359871437704620785888
32.40370349203930115482983718043998328852	31.62277660168379331998893544432718533719
22.91287847477920003294023596864004244492	22.3606797749978969640917366873127623544
56.12486080160912078375623098474823952634	55.22680508593630387105456921672158530426
72.4568837309471928685933207858488586157	71.41428428542849997999399811367265278766

Q7: List the product in sorted order of their description.

Solution:

SELECT DESCRIPTION FROM PRODUCT_MASTER ORDER BY DESCRIPTION;

Output:

DESCRIPTION

1.22 Floppies

1.44 Floppies

540 HDD

Keyboard

Monitors

Mouse

Q8: Calculate the average price of all the products.

Solution:

SELECT AVG(SELLPRICE), AVG(COSTPRICE) FROM PRODUCT_MASTER;

Output:

AVG(SELLPRICE)	AVG(COSTPRICE)
3750	3571.666666666666666666666666666666666667

Q9: Determine the minimum and maximum product prices. Rename the output as Min_Price and Max_Price respectively.

Solution:

SELECT MIN(SELLPRICE) “MIN_PRICE”, MAX(SELLPRICE) “MAX_PRICE” FROM PRODUCT_MASTER;

Output:

MIN_PRICE	MAX_PRICE
525	12000

Q10: Count the number of products having price less than 15000.

Solution:

SELECT COUNT(*) FROM PRODUCT_MASTER WHERE SELLPRICE <>

Output:

COUNT(*)

6

Q11: List all the products whose QtyOnHand is less than ReOrderLvl.

Solution:

SELECT DESCRIPTION FROM PRODUCT_MASTER WHERE QTYONHAND <>

Output:

no rows selected

Client Master

Practical No. # 1

Create the Client_Master table described below:

Column Name	Datatype	Size	Constraints
Client_No	Char	6	Primary Key
Name	Varchar2	20	Not Null
Address	Varchar2	100
City	Varchar2	15
Pincode	Number	6
State	Varchar2	20
Bal_Due	Number	8,2

Solution:

CREATE TABLE CLIENT_MASTER(

CLIENT_NO CHAR(6) PRIMARY KEY,

NAME VARCHAR2(20) NOT NULL,

ADDRESS VARCHAR2(100),

CITY VARCHAR2(15),

PINCODE NUMBER(6),

STATE VARCHAR2(20),

BAL_DUE(8,2));

Perform the following queries on the table:

Q1: Find out the names of all clients.

Solution:

SELECT NAME FROM CLIENT_MASTER;

Output:

NAME

Ivan Bayross

Vandana

Praveen

Basu

Ravi

Rukhmani

Q2: Retrieve the entire contents of Client_Master table.

Solution:

SELECT * FROM CLIENT_MASTER;

Output:

CLIENT_NO	NAME	ADDRESS	CITY	PINCODE	STATE	BAL_DUE
C00001	Ivan Bayross	F-111	Bombay	400054	Maharashtra	15000
C00002	Vandana	F-112	Madras	780001	Tamil Nadu	0
C00003	Praveen	F-113	Bombay	400057	Maharashtra	5000
C00004	Basu	F-114	Bangalore	560001	Karnataka
C00005	Ravi	F-115	Delhi	110005	Delhi	2000
C00006	Rukhmani	F-116	Bombay	400060	Maharashtra

Q3: Retrieve the names, city and state of all the clients.

Solution:

SELECT NAME, CITY, STATE FROM CLIENT_MASTER;

Output:

NAME	CITY	STATE
Ivan Bayross	Bombay	Maharashtra
Vandana	Madras	Tamil Nadu
Praveen	Bombay	Maharashtra
Basu	Bangalore	Karnataka
Ravi	Delhi	Delhi
Rukhmani	Bombay	Maharashtra

Q4: Print the structure of Client_Master table.

Solution:

DESC CLIENT_MASTER;

Output:

Name Null Type

------------------------------ -------- ----------------------------------

CLIENT_NO NOT NULL CHAR(6)

NAME NOT NULL VARCHAR2(20)

ADDRESS VARCHAR2(100)

CITY VARCHAR2(15)

PINCODE NUMBER(6)

STATE VARCHAR2(20)

BAL_DUE NUMBER(8,2)

Q5: List the names of all clients having ‘a’ as the second letter in their names.

Solution:

SELECT NAME FROM CLIENT_MASTER WHERE NAME LIKE ‘_a%’;

Output:

NAME

Vandana

Basu

Ravi

Q6: List the names of all clients having ‘a’ as the second letter in their city name.

Solution:

SELECT NAME FROM CLIENT_MASTER WHERE CITY LIKE ‘_a%’;

Output:

NAME

Vandana

Basu

Q7: List all the clients who stay in ‘Bombay’ or ‘Delhi’.

Solution:

SELECT NAME FROM CLIENT_MASTER WHERE CITY = ‘Bombay’ OR ‘Delhi’;

Output:

NAME

Ivan Bayross

Praveen

Ravi

Rukhmani

Q8: List all the clients who are located in Madras.

Solution:

SELECT NAME FROM CLIENT_MASTER WHERE CITY = ‘Madras’;

Output:

NAME

Vandana

Q9: List all clients whose Bal_Due is greater than the value 1000.

Solution:

SELECT NAME FROM CLIENT_MASTER WHERE BAL_DUE > 1000;

Output:

NAME

Ivan Bayross

Praveen

Ravi

Q10: List the names, city and state of clients who are not in the state of ‘Maharashtra’.

Solution:

SELECT NAME, CITY, STATE FROM CLIENT_MASTER WHERE NOT STATE = ‘Maharashtra’;

Output:

NAME	CITY	STATE
Vandana	Madras	Tamil Nadu
Basu	Bangalore	Karnataka
Ravi	Delhi	Delhi