MCA 4th sem /MCSL-045/Solved Assignment/UNIX and DBMS Lab /2017-2018 New

Part -1 : MCS - 041

Q.1.

A.1.

a) The Unix passwd command
The standard Unix command to change your password is:

  passwd
You must know your current password.
To change your password while logged onto a UNIX machine, type:passwd
To see the possible command line options, type: man passwd
If no options are given, actions are as follows:
The name that the user logged in as is determined.
If an entry for the user exists in the NIS (Network Information Service) passwd database, the password field in that entry is changed.

b) grep [OPTIONS] PATTERN [FILE...]
The pattern matching done by grep command is case sensitive. For example, if the argument to grep command is "LINUX" (instead of "Linux") then grep will not match the lines containing string "Linux". 

Here is an example :
# grep "LINUX" input.txt 
#

c)   head -lines filename
In this example, lines is an optional value specifying the number of lines to be read. If you don't give a number, the default value of 10 is used. Also, filename represents the name of an optional file for the head command to read. Otherwise, it will take its input from stdin (standard input: the terminal, or whatever the shell feeds the process with, usually pipe output).

For example, given a file containing the English alphabet with each letter on a separate line, a user enters the command:

  head -3 alphabetfile
This command would return:

  a
  b
  c
You can also use the head command with pipes. For example, to see the sizes of the first few files in a large directory, you could enter at the Unix prompt:

  ls -l | head

d) To view only the processes owned by a specific user, use the following command:

top -U [username]
Replace the [username] with the required username

If you want to use ps then

ps -u [username]
OR

 ps -ef | grep <username>
OR

ps -efl | grep <username>

e) kill PID
Replace PID with the process ID of the job. If that fails, enter the following:

 kill -KILL PID
To determine a job's PID, enter:
 jobs -l

f)$ wc -l file.txt
1020 file.txt

g)sort sorts the contents of a text file, line by line.
sort [options] filename

The options are:

-b : Ignores leading spaces in each line
-d : Uses dictionary sort order. Conisders only spaces and alphanumeric characters in sorting
-f : Uses case insensitive sorting.
-M : Sorts based on months. Considers only first 3 letters as month. Eg: JAN, FEB
-n : Uses numeric sorting
-R : Sorts the input file randomly.
-r : Reverse order sorting
-k : Sorts file based on the data in the specified field positions.
-u : Suppresses duplicate lines
-t : input field separator

h) $ echo qWeRtY | sed -E 's/([[:lower:]])|([[:upper:]])/\U\1\L\2/g'
QwErTy

i) 

j)
Display a conveniently-formatted calendar from the command line.
cal [options] [[[day] month] year]
Options

-1
Display a single month, which is the default setting.
-3
Display three months: last month, this month, and next month.
-s
Display the calendar using Sunday as the first day of the week.
-m
Display Monday as the first day of the week.
-j
Display dates of the Julian calendar.
-y
Display a calendar for the entire current year.
cal examples

cal
Displays the calendar for this month.
cal 12 2000

Shell Programming:-

1)  
read -p "Enter first Number:" n1
read -p "Enter second Number:" n2
read -p "Enter third Number:" n3
read -p "Enter fourth Number:" n4
read -p "Enter fourth Number:" n5
if[ [ n1 -gt n2 ] && [ n1 -gt n2 ] && [ n1 -gt n3 ] && [ n1 -gt n4 ] && [ n1 -gt n5 ]] ; then
      echo "$n1 is a Greatest Number"
elif[ [ n2 -gt n3 ] && [ n2 -gt n3 ] && [ n2 -gt n4 ] && [ n2 -gt n5 ]] ; then
     echo "$n2 is a Greatest Number"
elif[ [ n3 -gt n4 ] && [ n3 -gt n5 ] ] ; then  
     echo "$n3 is a Greatest Number"
elif[ n4 -gt n5 ] ; then  
     echo "$n4 is a Greatest Number"
else
     echo "$n5 is a Greatest Number"
fi

2)
echo Enter a 7 digit number
read num
n=1
while [ $n -le 7 ]
do
a=`echo $num | cut -c $n`
echo $a 
n=`expr $n + 2`
done

3)
clear
echo "Entre a string to find the number of Vowels "
read st
len=`expr $st | wc -c`
len=`expr $len - 1`
count=0
while [ $len -gt 0 ]
do
ch=`expr $st | cut -c $len`
case $ch in

[aeiou,AEIOU]) count=`expr $count + 1` ;;
esac
len=`expr $len - 1`
done
echo "Number of vowels in the give string is $count"

OutPut

Entre a string to find the number of Vowels
AbcefuSI
Number of vowels in the give string is 4

4) #!/bin/bash 
echo Enter first string: 
read s1 
echo Enter second string: 
read s2 
s3=$s1$s2 
len=`echo $s3 | wc -c` 
len=`expr $len - 1` 
echo Concatenated string is $s3 of length $len

5)
echo "Enter a Number:"
read a

rev=0
sd=0
or=$a

while [ $a -gt 0 ]
do
        sd=`expr $a % 10`
        temp=`expr $rev \* 10`
        rev=`expr $temp + $sd`
        a=`expr $a / 10`
done

echo "Reverse of $or is $rev"

Part -2 : MCS - 043

A.1.(a) 

MCA_Evaluation_System


create table Rent
(
aptid int primary key,
rentsetdate date primary key notnull,
rentpermonth int
);


create table Renter

(
renterID int  foreign key notnull ,
name varchar(20) notnull,

contact varchar(50) notnull
);


create table Rental

(
aptID int foreign key notnull ,
renterID int foreign key notnull,
startdate date notnull,

enddate date notnull,

checkout varchar(2)
);


create Apartment(aptID, city, street, housenr, aptnr, size, purchdate, price).
(

aptID int foreign key notnull ,
city varchar(50) notnull,
street varchar(20) notnull,

housenr int notnull,

aptnr int notnull,

size int notnull,

purchdate date notnull,

price int notnull

);



A.1.(b)

(i)To find the total rent earned during certain time interval (input to be given by the user) for a particular aptID.

Ans:- Select  aptID  from Rent where aptID = '2'

(ii) To display all the aptIDs if the street ID and City are given.

Ans:- Select * from Apartment where street = 'Khajuri' and city = 'Varanasi';

(iii) To display the list of all the renters who have more than one apt.

Ans:- Select * from renter where name>= 2

(iv) To display the list of all aptIDs whose rent is equal to or more than Rs.15,000/- per month in a particular street with apt size more than 850sq feet

Ans:- Select * from Rent where rentpermonth>= 15000  and aptID.size > 850 ;

(v) To display all the details of the apartment which were bought before year 2000 in a particular city and street.

Ans:- Select * from Apartment where  year = '2000' and city = 'Varanasi' and street ='Khajuri';


Q.1.(c)
A.1.(c)

Oracle lets you define procedures called triggers that run implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table or, in some cases, against a view, or when database system actions occur. These procedures can be written in PL/SQL or Java and stored in the database, or they can be written as C callouts.

Triggers are similar to stored procedures. A trigger stored in the database can include SQL and PL/SQL or Java statements to run as a unit and can invoke stored procedures. However, procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.

Data Access for Triggers

When a trigger is fired, the tables referenced in the trigger action might be currently undergoing changes by SQL statements in other users' transactions. In all cases, the SQL statements run within triggers follow the common rules used for standalone SQL statements. In particular, if an uncommitted transaction has modified values that a trigger being fired either needs to read (query) or write (update), then the SQL statements in the body of the trigger being fired use the following guidelines:

• Queries see the current read-consistent materialized view of referenced tables and any data changed within the same transaction.
• Updates wait for existing data locks to be released before proceeding.

The following examples illustrate these points.
Data Access for Triggers Example 1
Assume that the salary_check trigger (body) includes the following SELECT statement:

SELECT min_salary, max_salary INTO min_salary, max_salary
  FROM jobs 
  WHERE job_title = :new.job_title; 

For this example, assume that transaction T1 includes an update to the max_salary column of the jobs table. At this point, the salary_check trigger is fired by a statement in transaction T2. The SELECT statement within the fired trigger (originating from T2) does not see the update by the uncommitted transaction T1, and the query in the trigger returns the old max_salary value as of the read-consistent point for transaction T2.