Ignou MCSL 45

Download as docx, pdf, or txt
Download as docx, pdf, or txt
You are on page 1of 20

PART-I : MCS-041

Q1. Write the UNIX commands for the following :


(a) To search for text using specific regular expressions in file.
Answer : - grep is an acronym that stands for Global Regular Expression Print.

The grep filter searches a file for a particular pattern of characters, and displays
all lines that contain that pattern. The pattern that is searched in the file is
referred to as the regular expression.

Syntax :

grep [options] Pattern [files]

Example :

grep Computer Test.txt

grep Computer Test.txt Data.txt Index.txt

(b) To sort the contents of a file in reverse alphabetical order.


Answer : - sort command is used to sort a file, arranging the records in a
particular order. By default, the sort command sorts file assuming the contents
are ASCII. Using options in sort command, it can also be used to sort
numerically.

Syntax :

sort [options] file

Example :

sort -r Index.txt

[sort Index.txt file in reverse order]

(c) To count the no. of characters and words in a given text file.
Answer : - wc command is used to find out number of lines, words and
characters present in the files specified in the file arguments.
By default it displays four-columnar output. First column shows number of
lines present in a file specified, second column shows number of words present
in the file, third column shows number of characters present in file and fourth
column itself is the file name which are given as argument.

Syntax :

wc [options] [files]

Example :

wc Test.txt

wc Data.txt Index.txt

wc -c Test.txt

[count the number of characters present in the file]

wc -w Test.txt

[count the number of words present in the file]

(d) Create a small text file from your console.


Answer : -

Creating a file using "echo" command

echo 'My name is Debabrata' > myfile.txt

Creating a file using "cat" command

cat command is used to immediately add some text to your new file.

Type the following command at the terminal prompt and then press Enter :

cat > myfile.txt

After pressing Enter, you are not returned to the terminal prompt. Instead, the
cursor is placed on the next line, and you can start entering text directly into
your file. Type your lines of text, pressing Enter after each line. When you are
done, press Ctrl+D to exit the file and return to the prompt.

Creating a file using "touch" command

Using touch command we can create one or multiple empty files.

touch myfile.txt
touch Test.txt Demo.txt

Creating a file using "vi / vim" text editor

vi myfile.txt

[After pressing Enter, press i to insert new text into the file. Press ESC and then
press :x to save the file].

(e) Change the file permissions to Read, Write and Execute for
everyone on a data file created by you.
Answer : - To change file and directory permissions, use the command chmod
(change mode). The owner of a file can change the permissions for user (u),
group (g), or others (o) by adding (+) or subtracting (-) the read, write, and
execute permissions.

There are two basic ways of using chmod to change file permissions: The
symbolic method and the absolute form.

Symbolic Method

Access class Operator Access Type

u (user) + (add access) r (read)

g (group) - (remove access) w (write)

o (other) = (set exact access) x (execute)

a (everyone or all : u, g, and o)

Example :

chmod a+rwx myfile.txt

[Add permission for everyone to read, write and execute a file]

Absolute Form - The other way to use the chmod command is the absolute form,
in which you specify a set of three numbers that together determine all the
access classes and types. Rather than being able to change only particular
attributes, you must specify the entire state of the file's permissions.

The three numbers are specified in the order : user (or owner), group, and
other. Each number is the sum of values that specify read, write, and execute
access :

Permission Number

Read (r) 4

Write (w) 2

Execute (x) 1

Example :

chmod 777 myfile.txt

[Add permission for everyone to read, write and execute a file]

(f) To create a physical link between two files f1 and f2.


Answer : - A link in UNIX is a pointer to a file. Like pointers in any programming
languages, links in UNIX are pointers pointing to a file or a directory. Creating
links is a kind of shortcuts to access a file. Links allow more than one file name
to refer to the same file, elsewhere.

There are two types of links :

1. Hard Links
2. Soft Link or Symbolic links

Hard Links - Users are not allowed to create hard links for directories. This
might transform the directory tree into a graph with cycles, thus making it
impossible to locate a file according to its name.

Links can be created only among files included in the same filesystem.

Syntax :

ln [original filename] [link name]


Example :

ln Demo.txt Test.txt

(g) Compare two text files and display the first difference.
Answer : -

"cmp" command - This command is used to compare two files character by


character, and if they differ, it tells the first byte and line number where they
differ.

Syntax :

cmp [option] file1 file2

Example :

cmp Demo.txt Test.txt

"comm" command - This command is used to compare two sorted files.

Syntax :

comm [option] file1 file2

Example :

comm Demo.txt Test.txt

"diff" command - This command is used to compare two files line by line.

Syntax :

diff [option] file1 file2

Example :

diff Demo.txt Test.txt

(h) To display the disk usage.


Answer : - du command is used to estimate file space usage. The du command
can be used to track the files and directories which are consuming excessive
amount of space on hard disk drive.

Syntax :
du [options] [directories and/or files]

Example :

du /shell/Test.txt

[Show disk usage of "Test.txt" file]

du -h /shell

[Show disk usage of "shell" directory in human readable format]

(i) Create a file containing 20 lines, split it into two files


containing 10 lines each.
Answer : - split command is use to split a file.

Syntax :

split [options] filename prefix

Replace "filename" with the name of the large file you wish to split. Replace
"prefix" with the name you wish to give the small output files. You can exclude
[options], or replace it with either of the following :

● -l (lowercase L) → line number


● -b → bytes

Example :

assume Index.txt is 20 lines long :

split -l 10 Index.txt subfile

[This will output 2 10-line files : subfileaa and subfileab]

(j) To change the owner of a file.


Answer : - chown command is used to change the file Owner or group.
Whenever you want to change ownership you can use chown command.

Syntax :

chown [options] [owner][:[group]] [files]

Example :
chown Debabrata Demo.txt

[change the owner of Demo.txt file]

chown :mygroup Demo.txt

[change group ownership]

chown Debabrata:mygroup Demo.txt

[change the owner as well as group]

Q2. (a) Write a shell program to count and print the no. of
positive and negative integers in a list of integers given as input
by the user.
Answer : -echo "How many numbers you want to take input... "

read n

p_no=0

n_no=0

for((i=0;i<n;i++))

do

echo "Enter the number... "

read a[$i]

if test ${a[i]} -ge 0

then

p_no=`expr $p_no + 1`

else

n_no=`expr $n_no + 1`

fi

done

echo "Number of Positive Integer in the List = $p_no"

echo "Number of Negative Integer in the List = $n_no"


Output

How many numbers you want to take input...

Enter the number...

10

Enter the number...

-60

Enter the number...

45

Enter the number...

92

Enter the number...

-56

Number of Positive Integer in the List = 3

Number of Negative Integer in the List = 2


Q2. (b) Given two filenames by the user as the input, write a shell
script to append the contents of file2 to file1.
Answer : -

echo "Enter the file name which you want to append..."

read file1

echo "Enter the file name from which content is copy..."

read file2

# Check whether the file1 has write permission or not

if test -w $file1

then

# Check whether the file2 has read permission or not

if test -r $file2

then

while read line

do

# Copy each line from file2 to file1

`echo ${line} >> $file1`

done < $file2

else

echo "$file2 does not exist or not have read permission"

fi

else

echo "$file1 does not exist or not have write permission"

fi

Output

Enter the file name which you want to append...


/shell/Demo.txt

Enter the file name from which content is copy...

/shell/Index.txt

Q2. (c) Write a shell script to display the no. of times the given
pattern occurs in a .dat file and display the count.
Answer : -
echo "Enter the pattern..."

read pattern

file="/shell/a.dat"

patternLen=${#pattern}

count=0

while read line

do

# Pick each line from the given file

line=`echo ${line}`

# Calculate the length of each line

len=${#line}

start=1

end=$patternLen

while test $end -le $len

do

s=`echo $line | cut -c$start-$end`

if [ "$s" = "$pattern" ]

then

count=`expr $count + 1`

fi

start=`expr $start + 1`

end=`expr $end + 1`

done

done < $file

echo "Pattern Occurs = $count"

Output
Enter the pattern...

name is

Pattern Occurs = 2

Content of a.dat file

My name is Debabrata

My mother name is Jyotsna


PART-II : MCS-043
NOTE - I use Oracle 12c for all the SQL Queries, Triggers and Procedure

Q1. Design a database for a Mobile Phones’ Sales and Service


Showroom which deals with sales and service of various brands
of mobile phones. It also sells various accessories pertaining to
mobile phones. You are required to perform the following
activities for the maintenance of the above :
(a) Draw an enhanced entity relationship (EER) diagram.
Answer : -
(b) Create the complete database.
Answer : -

Before create a user run the command given below -

SQL> ALTER SESSION SET "_ORACLE_SCRIPT"=true;


SQL> COMMIT;

Create a new user -

SQL> CREATE USER showroom IDENTIFIED BY Pass1234

SQL> GRANT UNLIMITED TABLESPACE TO Showroom

SQL> GRANT CREATE SESSION TO showroom;

SQL> GRANT CREATE TABLE TO showroom;

SQL> GRANT CREATE VIEW TO showroom;

SQL> GRANT CREATE ANY TRIGGER TO showroom;

SQL> GRANT CREATE ANY PROCEDURE TO showroom;

SQL> GRANT CREATE SEQUENCE TO showroom;

SQL> GRANT CREATE SYNONYM TO showroom;

SQL> COMMIT;

SQL> CONNECT showroom

Enter password : Pass1234

Connected.

SQL>

SQL> CREATE TABLE shop (shop_id INT, shop_name VARCHAR2(50) NOT


NULL, shop_phone INT NOT NULL, PRIMARY KEY(shop_id));

SQL> CREATE TABLE product (p_code VARCHAR2(10), p_name


VARCHAR2(25) NOT NULL, brand VARCHAR2(20) NOT NULL, model
VARCHAR2(20) NOT NULL, unit_price INT NOT NULL, stock INT NOT NULL,
PRIMARY KEY(p_code));

SQL> CREATE TABLE sells (shop_id INT NOT NULL, p_code VARCHAR2(10)
NOT NULL);

SQL> CREATE TABLE bill_master (bill_no INT, bill_amount INT NOT NULL,
bill_date DATE NOT NULL, shop_id INT NOT NULL, cust_id VARCHAR2(10) NOT
NULL, PRIMARY KEY(bill_no));

SQL> CREATE TABLE customer (cust_id VARCHAR2(10), cust_name


VARCHAR2(50) NOT NULL, cust_address VARCHAR2(100) NOT NULL,
cust_phone INT NOT NULL, PRIMARY KEY(cust_id));
SQL> CREATE TABLE bill_details (bill_no INT NOT NULL, p_code
VARCHAR2(10) NOT NULL, serial_no VARCHAR2(25) NOT NULL);

SQL> CREATE TABLE service_order (service_no INT, cust_id VARCHAR2(10)


NOT NULL, shop_id INT NOT NULL, submit_date DATE NOT NULL, PRIMARY
KEY(service_no));

SQL> CREATE TABLE device_details (service_no INT NOT NULL, phone_brand


VARCHAR2(20) NOT NULL, phone_model VARCHAR2(20) NOT NULL, serial_no
VARCHAR2(25) NOT NULL, service_description VARCHAR2(200) NOT NULL);

SQL> CREATE TABLE service_bill (service_bill_no INT, total_amount INT NOT


NULL, service_bill_date DATE NOT NULL, shop_id INT NOT NULL, cust_id
VARCHAR2(10) NOT NULL, service_no INT NOT NULL, PRIMARY
KEY(service_bill_no));

SQL> CREATE TABLE service_bill_details (service_bill_no INT NOT NULL,


service_no INT NOT NULL, phone_brand VARCHAR2(20) NOT NULL,
phone_model VARCHAR2(20) NOT NULL, serial_no VARCHAR2(25) NOT NULL,
repairing_cost INT NOT NULL, repair_description VARCHAR2(200) NOT
NULL);

(c) Write the following queries using SQL :


(i) To display the Brand, Model and Price of the all the mobile
phones sold on a particular date.
Answer : - SELECT DISTINCT brand, model, unit_price FROM product,
bill_details, bill_master WHERE product.p_code=bill_details.p_code AND
bill_details.bill_no=bill_master.bill_no AND
bill_master.bill_date=TO_DATE('04-10-2019','DD-MM-YYYY');

(ii) To find the details of the mobile phones which were


purchased in the same shop and were serviced on a particular
date.
Answer : - SELECT DISTINCT product.p_code, brand, model, unit_price FROM
product, bill_details, bill_master, service_bill_details, service_bill WHERE
service_bill.shop_id=bill_master.shop_id AND
service_bill.service_bill_no=service_bill_details.service_bill_no AND
bill_master.bill_no=bill_details.bill_no AND
service_bill_details.serial_no=bill_details.serial_no AND
bill_details.p_code=product.p_code AND service_bill_date=TO_DATE('01-10-
2019','DD-MM-YYYY');
(iii) To display all the Brands and Models of the mobile phones
whose price is more than Rs.15000/-.
Answer : - SELECT brand, model FROM product WHERE unit_price > 15000;

(iv) Create a view of the items for the manager showing overall
performance of the week for each Brand and Model of the mobile
phones.
Answer : - CREATE OR REPLACE VIEW performance AS SELECT
product.p_code, brand, model, bill_master.bill_no, bill_date, shop_id, cust_id
FROM product, bill_master, bill_details WHERE
bill_master.bill_no=bill_details.bill_no AND bill_details.p_code=product.p_code;

(d) Perform the following activities :


(i) Create a trigger that prints the daily catalogue on change of a
price of any mobile phone or accessory.
Answer : -

price_update_alert.sql

CREATE OR REPLACE TRIGGER price_update_alert


AFTER UPDATE OF unit_price ON product
FOR EACH ROW
WHEN (NEW.unit_price != OLD.unit_price)
BEGIN
dbms_output.put_line('Product Code - ' || :OLD.p_code);
dbms_output.put_line('Brand - ' || :OLD.brand);
dbms_output.put_line('Model - ' || :OLD.model);
dbms_output.put_line('Old Price - ' || :OLD.unit_price);
dbms_output.put_line('New Price - ' || :NEW.unit_price);
END;
/

Run the trigger in oracle using the following command -

SQL> @ File_Location\File_name;
Example -

SQL> @ D:\Oracle\price_update_alert.sql;

To display the output of a procedure in oracle use the following command at


once -

SQL> SET SERVEROUTPUT ON;

(ii) Create a trigger whenever there is a new entry (of a new


model) in the mobile phone table. Also design a trigger
whenever there is a deletion of the record.
Answer : -

insert_alert.sql

CREATE OR REPLACE TRIGGER insert_alert


AFTER INSERT ON product
FOR EACH ROW
WHEN(NEW.p_code IS NOT NULL)
BEGIN
dbms_output.put_line('Product Code - ' || :NEW.p_code);
dbms_output.put_line('Brand - ' || :NEW.brand);
dbms_output.put_line('Model - ' || :NEW.model);
dbms_output.put_line('Price - ' || :NEW.unit_price);
END;
/

delete_alert.sql
CREATE OR REPLACE TRIGGER delete_alert
AFTER DELETE ON product
FOR EACH ROW
BEGIN
dbms_output.put_line('Product Code - ' || :OLD.p_code);
dbms_output.put_line('Brand - ' || :OLD.brand);
dbms_output.put_line('Model - ' || :OLD.model);
dbms_output.put_line('Price - ' || :OLD.unit_price);
END;
/

(e) Create a transaction that finds the total items sold per week
and prints the overall revenue generated.
Answer : -

transaction_details.sql

CREATE OR REPLACE PROCEDURE transaction_details (start_date IN DATE, end_date IN


DATE) AS
quantity INT;
revenue INT;
BEGIN
SELECT COUNT(bill_details.p_code) INTO quantity FROM bill_details, bill_master WHERE
bill_details.bill_no=bill_master.bill_no AND bill_master.bill_date>=start_date AND
bill_master.bill_date<=end_date;
SELECT SUM(bill_amount) INTO revenue FROM bill_master WHERE
bill_master.bill_date>=start_date AND bill_master.bill_date<=end_date;
dbms_output.put_line('Total Item Sold = ' || quantity);
dbms_output.put_line('Overall Revenue Generated = ' || revenue);
COMMIT;
END;
/
Run the procedure in oracle using the following command -

SQL> @ File_Location\File_name;

Example -

SQL> @ D:\Oracle\transaction_details.sql;
To display the output of a procedure in oracle use the following command at
once -

SQL> SET SERVEROUTPUT ON;

Execute the procedure in oracle using the following command -

SQL> EXEC Procedure_Name;

SQL> EXEC Procedure_Name(Parameter List);

Example -

SQL> EXEC transaction_details(TO_DATE('30-09-2019','DD-MM-


YYYY'),TO_DATE('06-10-2019','DD-MM-YYYY'));

(g) Create two different types of users: the first user – a manager
who can see reports and change the items and its price value and
second user (a salesperson) who sells these items.
Answer : - Solve it Yourself

You might also like