SE-COMP(B) NISHANT PATIL Roll number :

9629

Experiment no. : 4 Date of Performance :

Date of Submission:

Aim : To implement simple SQL commands

Tool Used : PostgreSQL/MySql

Related Course outcome : At the end of the course, Students will be able to Use
SQL : Standard language of relational database

Rubrics for assessment of Experiment:

Indicator Poor Average Good

Timeliness One or More than

Assignment not One week late (1- 2) Maintains
Maintains assignment
done (0) deadline (3)
deadline (3)

Completeness and neatness

< 80% complete 100% complete
Complete all parts of N/A
(1-2) (3)
QUERY assignment(3)

Originality Assignment has

Extent of plagiarism(2) At least few been solved
Copied it from
questions have been completely
someone without
done without
else(0) copying (2)
copying(1)

Knowledge Unable to
Unable to answer 1 Able to answer
In depth knowledge of the answer 2
question (1) 2 questions (2)
QUERY assignment(2) questions(0)
Assessment Marks :

Timeliness (3)

Completeness and
neatness (3)

Originality(2)

Knowledge (2)

Total (10)

Teacher's Sign :

Basic SQL Commands

EXPERIMENT
4

Aim To implement simple SQL commands

Tools PostgreSQL/MySql
Theory SELECT: SELECT statement returns a result set of records from one or more tables.
The select statement has optional clauses:
WHERE specifies which rows to retrieve
GROUP BY groups rows sharing a property so that an aggregate function can
be applied to each group having group.
HAVING selects among the groups defined by the GROUP BY clause.
ORDER BY specifies an order in which to return the rows.

Syntax:
SELECT<attribute list>
FROM<table list>
WHERE<condition>

Where

Attribute list is a list of attribute name whose values to be retrieved by the

query.
Table list is a list of table name required to process query.
Condition is a Boolean expression that identifies the tuples to be retrieved by
query.
SQL Aggregate Functions
SQL aggregate functions return a single value, calculated from values in a
column.
Useful aggregate functions:
• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum
The SQL ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set by one or more columns. The
ORDER BY keyword sorts the records in ascending order by default. To sort the records
in a descending order, you can use the DESC keyword.
SQL ORDER BY Syntax
SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;

TASK 1:
Procedure 1. Create following table: Data type Size Constraint
Table name : sales_order
Column
Name

order_no varchar 6 Primary Key

NOT
Order_date date
NULL
NOT
Client_no varchar 6
NULL

Dely_addr varchar 25

Salesman_no varchar 6

Dely_type char 1

Billed_yn char 1

Dely_date Date

Order_status varchar 10

2. Insert 5-6 records in table.

3. Find the names of all clients having ‘a’ as the second letter in their
names.
4. Find out the clients who stay in a city whose second letter is ‘a’ 5.
Find the list of all clients who stay in ‘mumbai’ ordered by their
names
6. Print the list of clients whose bal_due is greater than value 10000 7.
Print the information from sales_order table for orders placed in the month of
January
8. Display the order information for client_no C001 and C002
9. Find the products whose selling price is greater than 2000 and less
than or equal to 5000
10. Find the products whose selling price is more than 1500. Calculate
new selling price as original selling price * 1.5. Rename the new column in the
above query as new_price
11. Count the total number of orders
12. Calculate the average price of all the product
13. Determine minimum and maximum product prices
14. count the number of products having price greater than or equal to
1500
15. Display the order number and day on which clients placed their order
16. Display the order_date in the format ‘dd-month-yy’
17. Display the month (in alphabets) and date when the order must be
delivered
18. Find the date, 15 days after today’s date
19. Find the no. of days elapsed between today’s date and the delivery
date of orders placed by the clients.
Post Lab 1. Write a short note on DBA.
Questions: 2. Write different date functions and date formats.
3. Differentiate between group by and having with example.

4. Give different string functions.

SOLUTIONS:
1. Create following table: Table
name : sales_order
CREATE TABLE sales_order_9629(
order_no varchar(6) Primary key,
order_date date NOT NULL,
Client_no varchar(6) NOT NULL,
Dely_addr varchar(25),
Salesman_no varchar(6),
Dely_type char(5),
Billed_yn char(1),
Dely_date date,
Order_status varchar(10)
);
CREATE TABLE
client_master_9629( client_no varchar(6),
client_name varchar(20), address
varchar(30), city varchar(15), pincode
numeric(8), state1 varchar(15), due_bal
numeric(10,2)
);
CREATE TABLE
product_master_9629( product_no
varchar(6), description varchar(15),
profit_percent numeric(4,2), unit_measure
varchar(10), qty_on_hand varchar(8),
reorder_level numeric(8), sell_price
numeric(8,2), cost_price numeric(8,2)
);
2. Insert 5-6 records in table.
INSERT INTO client_master_9629 VALUES('C001','Sam','Kothrud','Pune',411077,'Maharashtra',10000.23);
INSERT INTO client_master_9629 VALUES('C002','John','Kurla','Mumbai',400087,'Maharashtra',50000.55); INSERT INTO
client_master_9629 VALUES('C003','Jack','Kishan Nagar','Gandhinagar',400055,'Gujarat',25000.93);
INSERT INTO client_master_9629 VALUES('C004','Ann','Charminar','Hydrabad',400090,'Andhra Pradesh',77000.00);
INSERT INTO client_master_9629 VALUES('C005','Peter','Bandra','Mumbai',400045,'Maharashtra',88000.23);
INSERT INTO client_master_9629 VALUES('C006','Nick','Vasai','Mumbai',400077,'Maharashtra',10000.23);

INSERT INTO product_master_9629 VALUES('P1','Laptop',30.12,'low',1200,13000,60000.23,50000.34);

INSERT INTO product_master_9629 VALUES('P2','Refrigerator',30.92,'medium',4354,42255,42423.24,35000.55);
INSERT INTO product_master_9629 VALUES('P3','Hard Disk',10.00,'high',5678,8723,3500.00,2500.00);
INSERT INTO product_master_9629 VALUES('P4','Mixture',40.12,'low',2535,44000,10000.23,8000.34);
INSERT INTO product_master_9629 VALUES('P5','Washing Machine',35.12,'high',9000,90000,40000.99,28000.90);

insert into sales_order_9629 values('1','2022-02-11','C001','Link Road','S001','FAST','Y','2022-02-15','CONFIRMED');

insert into sales_order_9629 values('2','2022-01-13','C002','Link Road','S001','REG','Y','2022-02-14','CONFIRMED'); insert
into sales_order_9629 values('3','2022-02-01','C003','Andheri','S002','FAST','N','2022-03-25','CANCELLED'); insert into
sales_order_9629 values('4','2021-12-21','C004','Matunga','S004','REG','Y','2022-03-13','DELIVERY'); insert into
sales_order_9629 values('5','2022-01-10','C005','Bandra','S003','REG','N','2022-04-25','CONFIRMED'); insert into
sales_order_9629 values('6','2021-11-30','C006','Worli','S001','REG','Y','2022-02-15','DELIVERY'); select * from
client_master_9629

select * from product_master_9629

select * from sales_order_9629

3. Find the names of all clients having ‘a’ as the second letter in their names.

SELECT * FROM client_master_9629 WHERE SUBSTR(client_name, 2, 1) = 'a';

4. Find out the clients who stay in a city whose second letter is ‘a’

SELECT * FROM client_master_9629 WHERE city LIKE '_a%'

5. Find the list of all clients who stay in ‘mumbai’ ordered by their names

SELECT * FROM client_master_9629 WHERE city='Mumbai'

6. Print the list of clients whose bal_due is greater than value 10000

SELECT * FROM client_master_9629 WHERE due_bal > 10000

7. Print the information from sales_order table for orders placed in themonth of January

SELECT * FROM sales_order_9629 WHERE TO_CHAR(order_date,'mm') = '01';

8. Display the order information for client_no C001 and C002

SELECT * FROM sales_order_9629 WHERE client_no IN('C001', 'C002');

9. Find the products whose selling price is greater than 2000 and less than orequal to 5000

SELECT * FROM product_master_9629 WHERE sell_price > 2000 AND sell_price <= 5000;

10. Find the products whose selling price is more than 1500. Calculate newselling price as
original selling price * 1.5. Rename the new column in the above query as new_price

SELECT product_no, sell_price * 1.5 AS new_price FROM product_master_9629 WHERE sell_price > 1500;

11. Count the total number of orders

SELECT COUNT(*) AS total_orders FROM sales_order_9629;

12. Calculate the average price of all the product

SELECT AVG(sell_price) as average_price FROM products;

13. Determine minimum and maximum product prices

SELECT MIN(sell_price) as min_price, MAX(sell_price) as max_price FROM product_master_9629;

14.Count the number of products having price greater than or equal to 1500

SELECT COUNT(*) as num_products FROM product_master_9629 WHERE sell_price >= 1500;

15. Display the order number and day on which clients placed their order

SELECT order_no , TO_CHAR(order_date , 'dd') as order_day FROM sales_order_9629;

16. Display the order_date in the format ‘dd-month-yy’

SELECT TO_CHAR(order_date, 'DD-MON-YY') as order_date_formatted FROM sales_order_9629;
17. Display the month (in alphabets) and date when the order must be Delivered

SELECT TO_CHAR(dely_date, 'Month DD') as delivery_date_formatted FROM sales_order_9629;

18. Find the date, 15 days after today’s date

SELECT CURRENT_DATE + INTERVAL '15 days' as date_15_days_after_today;

19. Find the no. of days elapsed between today’s date and the delivery dateof orders placed
by the clients.

SELECT NOW()-dely_date AS days_elapsed FROM sales_order_9629;