Write a script that behaves both in interactive and non-interactive mode. When no arguments are supplied, it picks up each C program from current directory and lists the first 10 lines. It then prompts for deletion of the file. If the user supplies arguments with the script, then it works on those files only.

flag=1
if [ $# -ne 0 ];then # IF USER PROVIDE ARGUMENTS
echo $* >temp
flag=1
else # IF USER NOT PROVIDE ARGUMENTS
ans=`ls *.c`
if [ "" == "$ans" ];then
echo "THERE ARE NO C FILE"
flag=0
else
ls *.c>temp
flag=1

fi
fi
if [ $flag -eq 1 ];then
for filename in `cat temp`
do
if [ -f $filename ];then
echo "\t\t\t========="
echo "\t\t\t$filename"
echo "\t\t\t========="
echo " "
sed -n -e "1,10 p" $filename
rm -i $filename
fi
done
rm temp
fi

Write a shell script to add the statement #include at the beginning of every C source file in current directory containing printf and fprintf

grep -lw -e "printf" -w -e "fprintf" *.c > $$
for filename in `cat $$`
do
sed '1i\
#include<stdio.h>\
#include<conio.h>
' $filename > $$$
`cat $$$>$filename`
rm $$$
done
rm $$

Write a script to calculate gross salary for any number of employees Gross Salary =Basic + HRA + DA. HRA=10% and DA= 15%.

clear
echo "        GROSS SALARY CALCULATOR"
echo "        ======================="
echo "ENTER BASIC SALARY:"
read basic
hra=`expr 10 \* $basic / 100`
da=`expr 15 \* $basic / 100`
gs=`expr $basic + $hra + $da`
# gs=`expr $gs + $da`
echo "GROSS SALARY IS $gs"

Write A Script To Perform Following String Operations Using Menu: COMPARE TWO STRINGS. JOIN TWO STRINGS. FIND THE LENGTH OF A GIVEN STRING. OCCURRENCE OF CHARACTER AND WORDS E. REVERSE THE STRING.

clear
echo "STRING MANIPULATION PROGRAM"
echo "1. COMPARE STRING"
echo "2. JOINT TWO STRING"
echo "3. LENGTH OF STRING"
echo "4. OCCOURANCE OF CHARACTER"
echo "5. OCCOURANCE OF WORD"
echo "6. REVERSE STRING"
echo "ENTER CHOICE:"
read ch
case $ch in
1)
echo "ENTER FIRST STRING:"
read str1
echo "ENTER SECOND STRING:"
read str2

len1=`echo $str1 | wc -c`
len2=`echo $str2 | wc -c`

if [ $len1 -eq $len2 ];then
echo "BOTH STRINGS ARE OF SAME LENGTH"
elif [ $len1 -gt $len2 ];then
echo "$str1 is greater than $str2"
else
echo "$str2 is greater than $str1"
fi

;;
2)
echo "ENTER FIRST STRING:"
read str1
echo "ENTER SECOND STRING:"
read str2
str1="$str1$str2"
echo "COMBINED STRING : $str1"
;;
3)
echo "ENTER STRING:"
read str
len=`echo $str | wc -c`
echo "Length of string is $len"
;;
4)
echo "ENTER STRING:"
read str
echo "ENTER character of word to search:"
read search
len=`echo $str | wc -c`
i=1
while [ $i -lt $len ]
do
ch=`echo $str | cut -c $i`
echo $ch
i=`expr $i + 1`
done
;;
5)
echo "NOT THERE"
;;
6)
echo "ENTER STRING:"
read str
echo "REVERSE STRING IS : "`echo $str | rev`
;;
*)
echo "ENTER PROPER CHOICE"
echo "NOT THERE"
;;
esac

Write a script which reads a text file and output the following Count of character, words and lines. File in reverse. Frequency of particular word in the file. Lower case letter in place of upper case letter.

clear
echo "Enter File Name :="
read filename
echo "1. Number Of Character"
echo "2. Number Of Words"
echo "3. Number Of Lines"
echo "4. File In Reverse"
echo "5. Fequency Of Particular Word"
echo "6. Convert Upper Case to Lower Case"
echo "Enter Your Choice :="
read ch
case $ch in
1)
echo "Total Number Of Characters are : " `cat $filename |wc -c`
;;
2)
echo "Total Number Of Words are : " `cat $filename |wc -w`
;;
3)
echo "Total Number Of Lines are : " `cat $filename |wc -l`
;;
4)
revstr=""
while read -n1 ch;
do
revstr="$ch$revstr"
done<$filename
echo "File In Reverse Order : "
echo $revstr
;;
5)
clear
echo "Enter Word To Search : "
read search
filename="veer.txt"
str=`grep -i "$search" $filename`
`echo $str | tr '[A-Z]' '[a-z]'>file.txt`
i=1
count=0
len=`echo $str | wc -c`
echo "length is $len"
search=`echo $search|tr '[A-Z]' '[a-z]'`
echo "SEARCH WORD:$search"
i=1
while read -n1 ch;
do
if [ "$ch" == " " ] || [ $i -eq $len ];then
if [ "$word" == "$search" ];then
count=`expr $count + 1`
fi
word=""
else
word="$word$ch"
fi
i=`expr $i + 1`
done<file.txt
echo "TOTAL NO OF WORDS ARE:$count"
;;
6)
`cat $filename | tr '[A-Z]' '[a-z]'>file.txt`
`cat file.txt > $filename`
echo "ALL CHARACTER CONVERTED TO LOWER CASE"
;;
*)
echo "Enter poper Values "
esac

Write a script to make following file and directory management operations menu based: 1. Display current directory 2. List directory 3. Make directory 4. Change directory 5. Copy a file 6. Rename a file 7. Delete a file 8. Edit a file

echo " 1 : Display Current Directory"
echo " 2 : List directory"
echo " 3 : Make Directory"
echo " 4 : Change Directory"
echo " 5 : Copy a file"
echo " 6 : Rename a file"
echo " 7 : Delete a file"
echo " 8 : Edit a file"
echo " ENTER CHOICE : ";
read ch
if [ $ch -eq 1 ];then
echo "YOUR CHOICE IS TO DISPLAY CURRENT DIRECTORY"
pwd=`pwd`
echo "YOUR CURRENT DIRECTORY IS : $pwd"
elif [ $ch -eq 2 ];then
echo "YOUR CHOICE IS TO LIST THE FILE IN DIRECTORY"
echo "FILE AND DIRECTORIES IN CURRENT DIRECTORY IS "
echo `ls`
elif [ $ch -eq 3 ];then
echo "YOUR CHOICE IS TO CREATE DIRECTORY"
echo "Enter Name Of Directory:";
read dir
`mkdir $dir`
echo "Directory Created successfully";
elif [ $ch -eq 4 ];then
echo "YOUR CHOICE IS TO CHANGE DIRECTORY"
echo "ENTER DIRECTORY TO CHANGE"
read dir1
cd $dir1
echo "CHANGED SUCCESS "`pwd`
elif [ $ch -eq 5 ];then
echo "Enter file to COPY"
read file
echo "Enter Directory to which file is to be copied"
read dir
cp $file $dir
elif [ $ch -eq 6 ];then
echo "YOUR CHOICE IS TO RENAME A FILE"
echo "ENTER FILE TO RENAME"
read file
echo "ENTER NEW NAME"
read name
mv $file $name
elif [ $ch -eq 7 ];then
echo "YOUR CHOICE IS TO DELETE A FILE"
echo "ENTER FILE TO DELETE"
read file
rm -i $file
elif [ $ch -eq 8 ];then
echo "YOUR CHOICE IS TO EDIT A FILE"
echo "ENTER FILENAME TO EDIT "
read file
echo "ENTER NEW DATA TO WRITE"
read newdata
echo $newdata > $file
else
echo "ENTER CORRECT OPTION"
fi

Write a script for generating a mark sheet after reading data from a file. File contains student roll no, name , marks of three subjects.

len=`cat student.txt | wc -l`
i=1
echo "\n\n\tSTUDENT MARKSHEET"
echo "\t=================\n"
echo "NAME \t \t TOTAL \t \t PERCENTAGE"
echo "====================================================="
while [ $i -le $len ]
do
record=`head -n $i student.txt | tail -n 1`
total=0
for (( j=2 ; $j < 5 ; j=`expr $j + 1` ))
do
marks=`echo $record | cut -d " " -f $j`
total=`expr $total + $marks`
done
name=`echo $record | cut -d " " -f 1`
per=`expr $total / 3`
echo "$name \t \t $total \t \t $per %"
i=`expr $i + 1`
done

Table Updations.

1) Change the s_order_date of cllient_no'C00001' to 24/07/96

update sales_order
set s_order_date='24-jul-96'
where client_no='C00001';

2) Change the selling price of '1.44 Drive' to Rs. 1150.00

update product_master set sell_price=1150 where  description='1.44 Drive';

3) Delete the record with order no 'O19001' from the order table

delete from sales_order where s_order_no='O19001';

4) Delete all the records having delivery date before 10-jul-96

delete sales_order where dely_date<'10-jul-96'; 

5) Change the city of client_no 'C00005' to 'Bombay'

update client_master set city='Bombay' where client_no='C00005';

6) Change the delivery date of order no 'O10008' to 16-08-96

update sales_order

set dely_date='16-aug-96'

where s_order_no='O10008';

7) Change the bal_due of client_no 'C00001' to 1000

update client_master

set bal_due=1000

where client_no='C00001';

8) Change the cost price of '1.22 Floppy Drive' to Rs.950.00

update product_master

set cost_price=950

where description='1.22 Floppy Drive'

Query Using Date.

1) Display the order no and day on which client placed their order.

select s_order_no,to_char(s_order_date,'day')"Day Name" from sales_order;

2) Display the month (in alphabets)and date when the order must be delivered.

select to_char(dely_date,'month')"Month Name",dely_date from sales_order

order by to_char(dely_date,'month');

3) Display s_order_date in the format "dd-month-yy" e.g 12-February-96.

select to_char(s_order_date,'DD-Month-YY') "Date format" from sales_order;

4) Find the date,fifteen days after todays date.

select sysdate+15 from dual;

5) Find the no of days elapsed between today date and the delivery date of the orders placed by the client.

select c.client_no ,(sysdate-s.dely_day) from sales_order s,client_master c where c.client_no=s.client_no;

Nested Queries.

1) find the product_no and description of non-moving products(eg.products not being sold).

select product_no,description from product_master
where product_no not in(select product_no from sales_order_details);

2) Find the customers name,address1,address2,city and pincode for the client who has placed order no "O19001'.

select name,address1,address2,city,pincode from client_master

where client_no in (select client_no from sales_order

where s_order_no='O19001');

3) Find the client name who have placed order before the month of may,96.

select client_no,name from client_master where client_no in(select client_no from sales_order 

where to_char(s_order_date,'mon,yy')<'may,96');

4) Find out if product "1.44 Drive" is ordered by any client and print client_no name to whom it was sold.

select client_no,name from client_master where client_no

in (select client_no from sales_order where s_order_no in (select s_order_no 

from sales_order_details where product_no in(select product_no 

from product_master where description='1.44 Drive')));

5) Find the name of clients who have placed ordered worth RS. 10000 or more.

select name from client_master where client_no in(select client_no from sales_order

where s_order_no in (select s_order_no from sales_order_details

where (qty_ordered*product_rate)>=10000));

Joins and correlation.

1) find out the product which has been sold to 'ivan bayroos'.

select d.product_no,p.description from sales_order_details d , product_master p , client_master c,sales_order s
where p.product_no=d.product_no and s.s_order_no=d.s_order_no and c.client_no=s.client_no and c.name='Ivan Bayross';

2) find out the product and their quantities that will have to delivered in the current month.

select d.product_no,p.description,sum(d.qty_ordered)

from sales_order_details d,sales_order s,product_master p

where p.product_no=d.product_no and s.s_order_no=d.s_order_no and to_char(dely_date,'mon-yy')=to_char(sysdate,'mon-yy')

group by d.product_no,p.description;

3) find the product_no and description of moving products.

select distinct p.product_no,p.description from product_master p ,sales_order_details d

where p.product_no=d.product_no;

4) find the names of the clients who have purchased 'CD Drive'.

select distinct s.client_no,c.name from sales_order_details d,sales_order s,product_master p,client_master c

where p.product_no=d.product_no and s.s_order_no=d.s_order_no and c.client_no=s.client_no and p.description='CD Drive'

5) List the product_no and s_order_no of customers having qty_ordered less than 5 from the order detail Table for the product '1.44 Floppies'.

select d.product_no,d.s_order_no from sales_order_details d,sales_order s,product_master p

where s.s_order_no=d.s_order_no and p.product_no=d.product_no and d.qty_ordered<5 and p.description='1.44 Floppies';

6) Find the products and their quantities for the orders placed by 'Vandana Saitwal' and 'Ivan Bayross'.

select d.product_no,p.description,sum(qty_ordered)"Qty Ordered"

from sales_order_details d,sales_order s,product_master p,client_master c 

where s.s_order_no=d.s_order_no and p.product_no=d.product_no and c.client_no=s.client_no

and (c.name='Ivan Bayross' or c.name='Vandana Saitwal')

group by d.product_no,p.description;

7) Find the products and their quantities for the orders placed by client_no'C00001' and 'C00002'.

select s.client_no,d.product_no,p.description ,sum(qty_ordered)"Qty_ordered"

from sales_order s,sales_order_details d,product_master p,client_master c

where s.s_order_no=d.s_order_no and d.product_no=p.product_no and s.client_no=c.client_no

group by s.client_no,d.product_no,p.description

having s.client_no='C00001' or s.client_no='C00002';

Having and Group By

1) Print the description and  total qty sold for each product.

select s.product_no,p.description, sum(s.qty_ordered) from sales_order_details s,product_master p
where p.product_no=s.product_no
group by s.product_no,p.description;

2) Find the value of each product sold.

select  s.product_no,p.description,sum(s.qty_disp*s.product_rate) "Sales Per Product" from

sales_order_details s,product_master p where p.product_no=s.product_no

group by s.product_no,p.description;

3) Calculate the avarage qty sold for each client that has a maximum order value of 15000.00

select c.client_no,c.name,avg(s.qty_disp) "Avg. Sales" from sales_order_details s ,sales_order so,client_master c

where c.client_no=so.client_no and so.s_order_no=s.s_order_no

group by c.client_no,c.name having max(s.qty_ordered*s.product_rate)>15000;

4) find out the total sales amount receivable for the month of jan.it will be the sum total of all the billed orders for the month.

select s.s_order_no,s.s_order_date,sum(so.qty_ordered*so.product_rate)"Order Billed",sum(so.qty_disp*so.product_rate) "Total Amount" from sales_order s, sales_order_details so

 where so.s_order_no=s.s_order_no and s.billed_yn='Y' and to_char(s_order_date,'mon')='jan'

 group by s.s_order_no,s.s_order_date;

5) Print the information of product_master,order_detail table in the following format for all the record:-{description}worth RS{total sales for the products} was sold.

select p.description||' Worth Rs'||sum(d.qty_disp*d.product_rate) from product_master p, sales_order_details d

where p.product_no=d.product_no group by p.description; 

6) Print the information of product_master,order_detail table in the following format for all the records:-{description}worth RS.{total sales for the product} was ordered in the month of (s_order_date in the month format}.

select p.description||' Worth Rs'||sum(d.qty_disp*d.product_rate)||' was ordered in the month of'||to_char(s_order_date,'month')"Description Total amount Month" from product_master p, sales_order_details d,sales_order s

where p.product_no=d.product_no and s.s_order_no=d.s_order_no group by p.description,s.s_order_date;

Set functions and concatenation.

1) Count the total no. of orders.

select count(*) from sales_order ;

2) Calculate the average price of all the products.

select round(avg(sell_price))as  "average price" from product_master ;

3) Calculate the minimum price of product

select min(sell_price)from product_master ;

4) Determine the maximum and minimum product price.Rename the title as max_price and min_price respectively.

select max(sell_price)"max_price",min(sell_price)"min_price"from product_master;

5) Count the number of product having price greater than or equal to 1500.

select count(*)from product_master where sell_price>=1500 ;

6) Find all products whose qty_on_hand is less than recorder level.

select product_no,description,qty_on_hand from product_master where qty_on_hand < recorder_lvl ;

7) Print the information of client_master,product_master,sales_order table in the following format for all the record.{cust_name}has placed order {order_no}on {s_order_date}.

select name|| ' has placed an order '  || s_order_no || ' on ' || s_order_date from client_master, sales_order where sales_order.client_no=client_master.client_no;

Single Table Retrival.

1) Find out the names of all clients.

select name from client_master ;

2) print the entire client_master table.

select * from client_master ;

3) Retrieve the list of names and the cities of all the clients.

select name,city from client_master;

4) List the various products available from the product_master.

select product_no,description from product_master ;

5) Find the name of all clients having 'a' as the second letter in their names.

select name from client_master where name like '_a%' ;

6) Find out the clients who stay in city whose second letter is 'a' .

select name,city from client_master where city like '_a%' ;

7) Find the list of all clients who stay in bombay or city delhi or city madras.

select name,city from client_master where city like '_a%' ;

8) List all the clients who are  located in 'Bombay'.

select name,city  from client_master where city in('Bombay','Delhi','Madras');

9) Print the list of clients whose bal_due are greater than value 10000

select name,city from client_master where city = 'Bombay' ;

10) Print the information from sales_order table of order placed in month of january.

select name,bal_due from client_master where bal_due > 10000 ;

11) Display order information for client_no 'c00001' and 'c00002' ;

select s_order_no,s_order_date from sales_order where s_order_date in

12) Find the products with description as '1.44 drive' and '1.22 drive' .

select s_order_no,s_order_date from sales_order where s_order_date in
(select s_order_date from sales_order where to_char(s_order_date,'mon')='jan');

14) Find the  product whose selling price is more than 1500 and also find the new selling price as original price * 15

select s_order_no,client_no,s_order_date from sales_order where client_no in('C00001','C00002');

15) Rename the new in the above query as new_price

select product_no||''''|| description||'''' from product_master ;

16) Find the product whose cost price is less than 1500

select product_no,description,sell_price from product_master where sell_price >2000 and sell_price<=5000 ;

17) List the product in sorted order of their description

select product_no,description,sell_price,sell_price * 15  "NEW_PRICE" from product_master where sell_price >1500 ;

18) Calculate the square root of price of each product.

select product_no,description,sqrt(cost_price)"root" from product_master ;

19) Divide the cost of product '540 HDD' by difference between its price and 100.

select cost_price/(cost_price-100)"Diff" from product_master where description = '540 HDD' ;

20) List the names,city,state of clients not in the state of 'Maharashtra' .

select name,city,state from client_master where state <> 'Maharashtra' ;

21) List the product_no,description,sell_price of products whose description begin with letter 'M' .

select product_no,description,sell_price from product_master where description like 'M%' ;

22) List of all orders that were canceled in month of March.

select s_order_no,order_status,s_order_date from sales_order where to_char(s_order_date,'mon')='mar' and order_status='c';

IVAN BAYROSS PROGRAMS (SQL)

TABLES [DOWNLOAD QUESTION]

[Creating Tables]


create table client_master
(
client_no varchar(6) primary key check(client_no like 'C%'),
name varchar(20) NOT NULL,
address1 varchar(30),
address2 varchar(30),
city varchar(15),
state varchar(15),
pincode number(6),
bal_due number(10,2)
);

create table product_master
(
product_no varchar(6) primary key check(product_no like 'P%'),
description varchar(5) NOT NULL,
profit_percent number(5,2) NOT NULL,
unit_measure varchar(10) NOT NULL,
qty_on_hand number(8) NOT NULL,
recorder_lvl number(8) NOT NULL,
sell_price number(8,2) NOT NULL check(sell_price<>0),
cost_price number(8,2) NOT NULL check(cost_price<>0)
);
alter table product_master modify description varchar(20);

create table salesman_master
(
salesman_no varchar(6) primary key check(salesman_no like 'S%'),
salesman_name varchar(20) NOT NULL,
address1 varchar(30) NOT NULL,
address2 varchar(30),
city varchar(20),
pincode varchar(6),
state varchar(20),
sal_amt number(8,2) NOT NULL check(sal_amt<>0),
tgt_to_get number(6,2) NOT NULL check(tgt_to_get<>0),
ytd_sales number(6,2) NOT NULL,
remarks varchar(60)
);
create table sales_order
(
s_order_no varchar(6) primary key check(s_order_no like 'O%'),
s_order_date date,
client_no varchar(6) references client_master(client_no),
dely_addr varchar(25),
salesman_no varchar(6) references salesman_master(salesman_no),
dely_type char(1) check(dely_type in ('P','F','D')),
billed_yn char(1),
dely_date date,
order_status varchar(10) check(order_status in('IP','F','B','C')),
check(dely_date>s_order_date)
);

create table sales_order_details
(
s_order_no varchar(6) references sales_order(s_order_no),
product_no varchar(6) references product_master(product_no),
qty_ordered number(8),
qty_disp number(8),
product_rate number(10,2)
);

create table challan_header
(
challan_no varchar(6) primary key check(challan_no like 'CH%'),
s_order_no varchar(6) references sales_order(s_order_no),
challan_date date not null,
billed_yn char(1)
);

create table challan_details
(
challan_no references challan_header(challan_no),
product_no references product_master(product_no),
qty_disp number(8)
);

[Inserting Records]


insert into client_master values('C00001','Ivan Bayross','','','Bombay','Maharashtra',400054,15000);
insert into client_master values('C00002','Vandana Saitwal','','','Madras','Tamil Nadu',780001,0);
insert into client_master values('C00003','Pramada Jaguste','','','Bombay','Maharashtra',400057,5000);
insert into client_master values('C00004','Basu Navindgi','','','Bombay','Maharashtra',400056,0);
insert into client_master values('C00005','Ravi Sreedharan','','','Delhi','',100001,2000);
insert into client_master values('C00006','Rukmini','','','Bombay','Maharashtra',400050,0);

insert into product_master values('P00001','1.44 Floppies',5,'Piece',100,20,525,500);
insert into product_master values('P03453','Monitors',6,'Piece',10,3,12000,11280);
insert into product_master values('P06734','Mouse',5,'Piece',20,5,1050,1000);
insert into product_master values('P07865','1.22 Floppies',5,'Piece',100,20,525,500);
insert into product_master values('P07868','Keyboards',2,'Piece',10,3,3150,3050);
insert into product_master values('P07885','CD Drive',2.5,'Piece',10,3,5250,5100);
insert into product_master values('P07965','540 HDD',4,'Piece',10,3,8400,8000);
insert into product_master values('P07975','1.44 Drive',5,'Piece',10,3,1050,1000);
insert into product_master values('P08865','1.22 Drive',5,'Piece',2,3,1050,1000);


insert into salesman_master values('S00001','Kiran','A/14','Worli','Bombay',400002,'MAH',3000,100,50,'Good');
insert into salesman_master values('S00002','Manish','65','Nariman','Bombay',400001,'MAH',3000,200,100,'Good');
insert into salesman_master values('S00003','Ravi','P-7','Bandra','Bombay',400032,'MAH',3000,200,100,'Good');
insert into salesman_master values('S00004','Ashish','A/5','Juhu','Bombay',400044,'MAH',3000,200,150,'Good');

insert into sales_order values('O19001','12-jan-1996','C00001','','S00001','F','N','20-jan-1996','IP');
insert into sales_order values('O19002','25-jan-1996','C00002','','S00002','P','N','27-jan-1996','C');
insert into sales_order values('O46865','18-feb-1996','C00003','','S00003','F','Y','20-feb-1996','F');
insert into sales_order values('O19003','03-apr-1996','C00001','','S00001','F','Y','07-apr-1996','F');
insert into sales_order values('O46866','20-may-1996','C00004','','S00002','P','N','22-may-1996','C');
insert into sales_order values('O10008','24-may-1996','C00005','','S00004','F','N','26-may-1996','IP');

insert into sales_order_details values('O19001','P00001',4,4,525);
insert into sales_order_details values('O19001','P07965',2,1,8400);
insert into sales_order_details values('O19001','P07885',2,1,5250);
insert into sales_order_details values('O19002','P00001',10,0,525);
insert into sales_order_details values('O46865','P07868',3,3,3150);
insert into sales_order_details values('O46865','P07885',3,1,5250);
insert into sales_order_details values('O46865','P00001',10,10,525);
insert into sales_order_details values('O46865','P03453',4,4,1050);
insert into sales_order_details values('O19003','P03453',2,2,1050);
insert into sales_order_details values('O19003','P06734',1,1,12000);
insert into sales_order_details values('O46866','P07965',1,0,8400);
insert into sales_order_details values('O46866','P07975',1,0,1050);
insert into sales_order_details values('O10008','P00001',10,5,525);
insert into sales_order_details values('O10008','P07975',5,3,1050);


insert into challan_header values('CH9001','O19001','12-dec-1995','Y');
insert into challan_header values('CH6865','O46865','12-nov-1995','Y');
insert into challan_header values('CH3965','O10008','12-oct-1995','Y');


insert into challan_details values('CH9001','P00001',4);
insert into challan_details values('CH9001','P07965',1);
insert into challan_details values('CH9001','P07885',1);
insert into challan_details values('CH6865','P07868',3);
insert into challan_details values('CH6865','P03453',4);
insert into challan_details values('CH6865','P00001',10);
insert into challan_details values('CH3965','P00001',5);
insert into challan_details values('CH3965','P07975',2);



Perform the following queries on the basis of these tables.

• Single Table Retrival.
• Set functions and concatenation.
• Having and Group By.
• Joins and correlation.
• Nested Queries.
• Query Using Date.
• Table Updations.

Write a script to display the last modified file.

record=`ls -lt | head -n 2`
filename=`echo $record | cut -d " " -f 11`
echo "Last modified file is $filename"

Write a script to display all lines of a file in ascending order.

[[   METHOD 1   ]]

echo "Enter filename"
read filename
sort -t "~" -k 1 $filename

[[   METHOD 2   ]]

echo "Enter filename"
read filename
sort $filename

Write a script to display all words of a file in ascending order.

clear
echo "\n\t\t\tAscending order of word in File"
echo "\t\t\t==============================="
echo "\t\t\tEnter File Name "
read filename
echo "\n\n\t\t\t Main File "
echo "\n\t\t\t ========="
echo "\n\t\t\t  "`cat $filename`
 for i in `cat $filename`
 do
echo $i >> tempfile.txt
 done
 `sort tempfile.txt>$filename`
 echo "\n\n\t\t\t Sorted Word in File "
 echo "\n\t\t\t ==================="
 echo "\n\t\t\t  "`cat $filename`
 rm tempfile.txt

Write a script to check whether a given number is palindrome or not.

clear
echo "              NUMBER PALINDROME OF NOT"
echo "              ========================"
echo "ENTER NUMBER : "
read no
temp=$no
revno=0
while [ $no -ne 0 ]
do
a=`expr $no % 10`
no=`expr $no / 10`
revno=`expr $revno \* 10 + $a`
done
if [ $temp -eq $revno ];then
echo "Number is palindrome"
else
echo "Number is not palindrome"
fi

Write a script to check whether a given string is palindrome or not.

clear
echo " STRING PALINDROME OF NOT"
echo " ========================"
echo "ENTER STRING : "
read str
len=`echo $str | wc -c`
i=1

while [ $i -le $len ]
do
ch=`echo $str | cut -c $i`
revstr="$ch$revstr"
i=`expr $i + 1`
done
if [ "$str" == "$revstr" ];then
echo "String is palindrome"
else
echo "String is not palindrome"
fi

Write a script to calculate gross salary for any number of employees Gross Salary =Basic + HRA + DA. HRA=10% and DA= 15%.

clear
echo "        GROSS SALARY CALCULATOR"
echo "        ======================="
echo "ENTER BASIC SALARY:"
read basic
hra=`expr 10 \* $basic / 100`
da=`expr 15 \* $basic / 100`
gs=`expr $basic + $hra + $da`
# gs=`expr $gs + $da`
echo "GROSS SALARY IS $gs"

Write A Script To Perform Following String Operations Using Menu: COMPARE TWO STRINGS. JOIN TWO STRINGS. FIND THE LENGTH OF A GIVEN STRING. OCCURRENCE OF CHARACTER AND WORDS E. REVERSE THE STRING.

clear
echo "STRING MANIPULATION PROGRAM"
echo "1. COMPARE STRING"
echo "2. JOINT TWO STRING"
echo "3. LENGTH OF STRING"
echo "4. OCCOURANCE OF CHARACTER"
echo "5. OCCOURANCE OF WORD"
echo "6. REVERSE STRING"
echo "ENTER CHOICE:"
read ch
case $ch in
1)
echo "ENTER FIRST STRING:"
read str1
echo "ENTER SECOND STRING:"
read str2

len1=`echo $str1 | wc -c`
len2=`echo $str2 | wc -c`

if [ $len1 -eq $len2 ];then
echo "BOTH STRINGS ARE OF SAME LENGTH"
elif [ $len1 -gt $len2 ];then
echo "$str1 is greater than $str2"
else
echo "$str2 is greater than $str1"
fi

;;
2)
echo "ENTER FIRST STRING:"
read str1
echo "ENTER SECOND STRING:"
read str2
str1="$str1$str2"
echo "COMBINED STRING : $str1"
;;
3)
echo "ENTER STRING:"
read str
len=`echo $str | wc -c`
echo "Length of string is $len"
;;
4)
echo "ENTER STRING:"
read str
echo "ENTER character of word to search:"
read search
len=`echo $str | wc -c`
i=1
while [ $i -lt $len ]
do
ch=`echo $str | cut -c $i`
echo $ch
i=`expr $i + 1`
done
;;
5)
echo "NOT THERE"
;;
6)
echo "ENTER STRING:"
read str
echo "REVERSE STRING IS : "`echo $str | rev`
;;
*)
echo "ENTER PROPER CHOICE"
echo "NOT THERE"
;;
esac

Write a script which reads a text file and output the following Count of character, words and lines. File in reverse. Frequency of particular word in the file. Lower case letter in place of upper case letter.

clear
echo "Enter File Name :="
read filename
echo "1. Number Of Character"
echo "2. Number Of Words"
echo "3. Number Of Lines"
echo "4. File In Reverse"
echo "5. Fequency Of Particular Word"
echo "6. Convert Upper Case to Lower Case"
echo "Enter Your Choice :="
read ch
case $ch in
1)
echo "Total Number Of Characters are : " `cat $filename |wc -c`
;;
2)
echo "Total Number Of Words are : " `cat $filename |wc -w`
;;
3)
echo "Total Number Of Lines are : " `cat $filename |wc -l`
;;
4)
revstr=""
while read -n1 ch;
do
revstr="$ch$revstr"
done<$filename
echo "File In Reverse Order : "
echo $revstr
;;
5)
clear
echo "Enter Word To Search : "
read search
filename="veer.txt"
str=`grep -i "$search" $filename`
`echo $str | tr '[A-Z]' '[a-z]'>file.txt`
i=1
count=0
len=`echo $str | wc -c`
echo "length is $len"
search=`echo $search|tr '[A-Z]' '[a-z]'`
echo "SEARCH WORD:$search"
i=1
while read -n1 ch;
do
if [ "$ch" == " " ] || [ $i -eq $len ];then
if [ "$word" == "$search" ];then
count=`expr $count + 1`
fi
word=""
else
word="$word$ch"
fi
i=`expr $i + 1`
done<file.txt
echo "TOTAL NO OF WORDS ARE:$count"
;;
6)
`cat $filename | tr '[A-Z]' '[a-z]'>file.txt`
`cat file.txt > $filename`
echo "ALL CHARACTER CONVERTED TO LOWER CASE"
;;
*)
echo "Enter poper Values "
esac

Write a script to make following file and directory management operations menu based: 1. Display current directory 2. List directory 3. Make directory 4. Change directory 5. Copy a file 6. Rename a file 7. Delete a file 8. Edit a file

echo " 1 : Display Current Directory"
echo " 2 : List directory"
echo " 3 : Make Directory"
echo " 4 : Change Directory"
echo " 5 : Copy a file"
echo " 6 : Rename a file"
echo " 7 : Delete a file"
echo " 8 : Edit a file"
echo " ENTER CHOICE : ";
read ch
if [ $ch -eq 1 ];then
echo "YOUR CHOICE IS TO DISPLAY CURRENT DIRECTORY"
pwd=`pwd`
echo "YOUR CURRENT DIRECTORY IS : $pwd"
elif [ $ch -eq 2 ];then
echo "YOUR CHOICE IS TO LIST THE FILE IN DIRECTORY"
echo "FILE AND DIRECTORIES IN CURRENT DIRECTORY IS "
echo `ls`
elif [ $ch -eq 3 ];then
echo "YOUR CHOICE IS TO CREATE DIRECTORY"
echo "Enter Name Of Directory:";
read dir
`mkdir $dir`
echo "Directory Created successfully";
elif [ $ch -eq 4 ];then
echo "YOUR CHOICE IS TO CHANGE DIRECTORY"
echo "ENTER DIRECTORY TO CHANGE"
read dir1
cd $dir1
echo "CHANGED SUCCESS "`pwd`
elif [ $ch -eq 5 ];then
echo "Enter file to COPY"
read file
echo "Enter Directory to which file is to be copied"
read dir
cp $file $dir
elif [ $ch -eq 6 ];then
echo "YOUR CHOICE IS TO RENAME A FILE"
echo "ENTER FILE TO RENAME"
read file
echo "ENTER NEW NAME"
read name
mv $file $name
elif [ $ch -eq 7 ];then
echo "YOUR CHOICE IS TO DELETE A FILE"
echo "ENTER FILE TO DELETE"
read file
rm -i $file
elif [ $ch -eq 8 ];then
echo "YOUR CHOICE IS TO EDIT A FILE"
echo "ENTER FILENAME TO EDIT "
read file
echo "ENTER NEW DATA TO WRITE"
read newdata
echo $newdata > $file
else
echo "ENTER CORRECT OPTION"
fi

Write a script to implement the following commands: Tree (of DOS) which (of UNIX)

echo "Enter path Name :="
read path
name=`find  "$path"  -print0 | xargs -0`
for i in $name
do
if [ -d $i ];then
echo $i>file.txt
count=`awk 'NF{print NF-1}' FS="/" file.txt`
format="|__"
temp=`expr $count + 1`
i=`echo $i|cut -d "/" -f $temp`
for (( j=0 ; $j < $count ; j=`expr $j + 1` ))
do
format="|    "$format
done
echo "$format$i"
fi
done

Write a script to display the directory in the descending order of the size of each file.

[[   METHOD 1   ]]

echo "ENTER DIR"
read dir
`echo ls -lS $dir` > file.txt
len=`cat file.txt | wc -l`
i=2
while [ $i -le $len ]
do
record=`ls -lS $dir | head -n $i | tail -n 1`
# record=`cat file.txt | head -n $i | tail -n 1`
filename=`echo $record | cut -d " " -f 9`
echo "$filename"
i=`expr $i + 1`
done

[[   METHOD 2   ]]

clear
echo "ENTER DIR"
read dir
`echo ls -lS $dir` > file.txt
len=`cat file.txt | wc -l`
i=2
echo "SIZE          FILENAME"
echo "====       ==============="
while [ $i -le $len ]
do
record=`ls -lS $dir | head -n $i | tail -n 1`
# record=`cat file.txt | head -n $i | tail -n 1`
filename=`echo $record | cut -d " " -f 9`
size=` echo $record | cut -d " " -f 5`
echo "$size        $filename"
i=`expr $i + 1`
done