Oracle

Oracle Lab Questions

1.            Create the table Dept based on following information.

Column Name	Datatype	Length	Constraint
ID	Number	5	Primary Key
Dept_name	Varchar2	50	Not Null

CREATE TABLE `Dept` (

  `Id` int(5) NOT NULL,

  `Dept_name` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`Id`)

)

2.            Insert the values in Dept table as follows.

ID	Dept_name
10	IT
20	Account
30	HR
40	Marketing
50	Manufacturing

INSERT INTO dept (`ID`,`Dept_name`) VALUES ( '10','IT');

INSERT INTO dept (`ID`,`Dept_name`) VALUES ( '20','Account');

INSERT INTO dept (`ID`,`Dept_name`) VALUES ( '30','HR');

INSERT INTO dept (`ID`,`Dept_name`) VALUES ( '40','Marketing');

INSERT INTO dept (`ID`,`Dept_name`) VALUES ( '50','Manufacturing');

3.            Create the table Emp based on following information.

Column Name	Datatype	Length	Constraint
ID	Number	5	Primary Key
Emp_name	Varchar2	50
Address	Varchar2	50
DateOfBirth	Date
Gender	Char	1	Check (M or F) only
Post	Varchar2	50	Not Null
Email	Varchar2	20	Unique
Salary	Number	10,2	Not Null
Dept_id	Number	5	Foreign Key References with Dept_id of Dept table.

CREATE TABLE `emp` (

  `ID` int(5) NOT NULL,

  `Emp_name` varchar(50) DEFAULT NULL,

  `Address` varchar(50) DEFAULT NULL,

  `DateOfBirth` date DEFAULT NULL,

  `Gender` enum('M','F') DEFAULT NULL,

  `Post` varchar(50) NOT NULL,

  `Email` varchar(20) NOT NULL,

  `Salary` double(10,2) NOT NULL,

  `Dept_id` int(5) DEFAULT NULL,

  PRIMARY KEY (`ID`,`Email`),

  KEY `FK_emp` (`Dept_id`),

  CONSTRAINT `FK_emp` FOREIGN KEY (`Dept_id`) REFERENCES `dept` (`ID`)

)

4.            Describe the Structure of the above created table DEPT and EMP.

5.            Insert about 10 values in Emp table.

INSERT INTO  emp (

ID ,Emp_name ,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id

)

VALUES (

'',  'emp1',  'address1',  '2014-06-19',  'M',  'post1',  'email1@email.com',  '10,000',  '10'

);

INSERT INTO  emp (

ID ,Emp_name ,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id

)

VALUES (

'',  'emp2',  'address2',  '2014-06-19',  'M',  'post2',  'email2@email.com',  '20,000',  '20'

);

INSERT INTO  emp (

ID ,Emp_name ,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id

)

VALUES (

'',  'emp3',  'address3',  '2014-06-19',  'M',  'post3',  'email3@email.com',  '30,000',  '30'

);

INSERT INTO  emp (

ID ,Emp_name ,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id

)

VALUES (

'',  'emp4',  'address4',  '2014-06-19',  'M',  'post4',  'email4@email.com',  '40,000',  '40'

);

INSERT INTO  emp (

ID ,Emp_name ,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id

)

VALUES (

'',  'emp5',  'address5',  '2014-06-19',  'M',  'post5',  'email5@email.com',  '50,000',  '50'

);

INSERT INTO  emp (

ID ,Emp_name ,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id

)

VALUES (

'',  'emp6',  'address6',  '2014-06-19',  'M',  'post6',  'email6@email.com',  '60,000',  '10'

);

INSERT INTO  emp (

ID ,Emp_name ,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id

)

VALUES (

'',  'emp7',  'address7',  '2014-06-19',  'F',  'post7',  'email7@email.com',  '70,000',  '20'

);

INSERT INTO  emp (

ID ,Emp_name ,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id

)

VALUES (

'',  'emp8',  'address8',  '2014-06-19',  'M',  'post8',  'email8@email.com',  '80,000',  '30'

);

INSERT INTO  emp (

ID ,Emp_name ,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id

)

VALUES (

'',  'emp9',  'address9',  '2014-06-19',  'F',  'post9',  'email9@email.com',  '90,000',  '40'

);

INSERT INTO  emp (

ID ,Emp_name ,Address ,DateOfBirth ,Gender ,Post ,Email ,Salary ,Dept_id

)

VALUES (

'',  'emp10',  'address10',  '2014-06-19',  'M',  'post10',  'email10@email.com',  '100,000',  '50'

);

6.            Modify the Data type of email to varchar2(50).

ALTER TABLE `emp` CHANGE `Email` `Email` VARCHAR2( 50 ) 

7.            Change the datatype of Gender of Emp table to Varchar2(10).

ALTER TABLE `emp` CHANGE `Gender` `Gender` VARCHAR2( 10 ) 

8.            Increase the salary of Employees who works in IT department by 25%.

update `emp` SET `Salary`=salary+(salary*0.25) WHERE `Dept_id`=10

9.            Add the Not Null constraint to Emp table on Emp_name column.

ALTER TABLE `emp` CHANGE `Emp_name` `Emp_name` VARCHAR( 50 )  NOT NULL

10.        Drop the column Address from the table Emp.

ALTER TABLE `emp` DROP `Address`

11.        List all the Employees who works in the IT department.

SELECT * FROM `emp` WHERE `Dept_id`=10

12.        List all the Employees name, email and salary whose name starts with "a". Sort data in descending order based on Salary paid.

SELECT `Emp_name`,`Email`,`Salary` FROM `emp` ORDER BY `Salary` DESC

13.        List the Average salary of each Department.

SELECT AVG(Salary) as average_salry,Dept_name

FROM `emp` JOIN dept

ON

dept.ID=emp.Dept_id

GROUP BY `Dept_id`

Read more →

linux method to write practical

Aim of the Experiment
Create a user Student and assign the password to P@ssw0rd.
Objective
The main objective of the experiment is to create a user and assign password. We need to use ‘useradd’ command, which is responsible for creating a new user or update default new user information.
The ‘useradd’ command creates a new user account using the values specified on the command line and the values from the system. The new user account will be entered into the system files (/etc/passwd) as needed, the home directory (/home/username) will be created, and initial files copied, depending on the command line options.
System Requirements
Hardware Requirement:
PIV Computer
512 MB RAM
40 GB Hard Disk
Software Requirement:
Linux OS
Aim of the Experiment
Create a user Student and assign the password to P@ssw0rd.
Objective
The main objective of the experiment is to create a user and assign password. We need to use ‘useradd’ command, which is responsible for creating a new user or update default new user information.
The ‘useradd’ command creates a new user account using the values specified on the command line and the values from the system. The new user account will be entered into the system files (/etc/passwd) as needed, the home directory (/home/username) will be created, and initial files copied, depending on the command line options.
System Requirements
Hardware Requirement:
PIV Computer
512 MB RAM
40 GB Hard Disk
Software Requirement:
Linux OS
Algorithm
Syntax:
Syntax is as follows for useradd command:
useradd <username>
By default the user account is locked, you need to setup a new password:
Passwd <username>
Code:

# adduser Student
# passwd Student   

Output
# adduser Student
# passwd Student
Changing password for user Student.
New password: P@ssw0rd
Retype new password: P@ssw0rd
Passwd: all authentication tokens updated successfully.
Conclusion
In this way, a user Student with the password P@ssw0rd is created successfully.


Aim of the Experiment
Display the name of current working directory.
Objective
The main objective of the experiment is to display the name of the current working directory. The current directly is the directory in which a user is working at a given time. Even user is always working within a directly. There are several ways of determining what the current directly is. One is by looking at the command prompt. The ‘pwd’ command displays the absolute pathname of the current working directory to the computer screen.
System Requirements
Hardware Requirement:
PIV Computer
512 MB RAM
40 GB Hard Disk
Software Requirement:
Linux OS
Algorithm
Syntax:
Syntax is as follows:
pwd
Code:

# pwd   

Output
# pwd
Output: /root/user
Conclusion
In this way, the name of the currently working directory is displayed.

Aim of the Experiment
List all the files and directories in /etc file system.
Objective
The main objective of the experiment is to list all the files and directories in /etc file system. The ‘ls’ command lists all the files and directories in  /etc file system.
System Requirements
Hardware Requirement:
PIV Computer
512 MB RAM
40 GB Hard Disk
Software Requirement:
Linux OS
Algorithm
Syntax:
Syntax is as follows:
ls [/directory_name]
Code:

# ls /etc   

Output
# ls /etc
Output:
acpi        fstab        magic        rc.local    adduser.conf
fstab.d        magic.mime            mail.rc    report    apm        group-    rmt
Conclusion
In this way, the files and direcotries in /etc file system is listed.


Syntax:
Syntax is as follows for useradd command:
useradd <username>
By default the user account is locked, you need to setup a new password:
Passwd <username>
Code:

# adduser Student
# passwd Student   

Output
# adduser Student
# passwd Student
Changing password for user Student.
New password: P@ssw0rd
Retype new password: P@ssw0rd
Passwd: all authentication tokens updated successfully.
Conclusion
In this way, a user Student with the password P@ssw0rd is created successfully.


Aim of the Experiment
Display the name of current working directory.
Objective
The main objective of the experiment is to display the name of the current working directory. The current directly is the directory in which a user is working at a given time. Even user is always working within a directly. There are several ways of determining what the current directly is. One is by looking at the command prompt. The ‘pwd’ command displays the absolute pathname of the current working directory to the computer screen.
System Requirements
Hardware Requirement:
PIV Computer
512 MB RAM
40 GB Hard Disk
Software Requirement:
Linux OS
Algorithm
Syntax:
Syntax is as follows:
pwd
Code:

# pwd   

Output
# pwd
Output: /root/user
Conclusion
In this way, the name of the currently working directory is displayed.

Aim of the Experiment
List all the files and directories in /etc file system.
Objective
The main objective of the experiment is to list all the files and directories in /etc file system. The ‘ls’ command lists all the files and directories in  /etc file system.
System Requirements
Hardware Requirement:
PIV Computer
512 MB RAM
40 GB Hard Disk
Software Requirement:
Linux OS
Algorithm
Syntax:
Syntax is as follows:
ls [/directory_name]
Code:

# ls /etc   

Output
# ls /etc
Output:
acpi        fstab        magic        rc.local    adduser.conf
fstab.d        magic.mime            mail.rc    report    apm        group-    rmt
Conclusion
In this way, the files and direcotries in /etc file system is listed.

Read more →

Linux Practical

Linux Lab Questions
Create a user Student and assign the password to P@ssw0rd.
Open terminal
Switch user to root
    # su
    password: <root password>
Synax: useradd <username>
passwd <username>
solution:
 # useradd Student
# passwd Student
new unix password : P@ssw0rd
retype new unix password : P@ssw0rd

Display the name of current working directory.
Syntax : pwd
# pwd
/home/student


List all the files and directories in /etc file system.
Syntax : ls <dir name>
# ls /etc

List all the hidden files and directories in your home directory.
Syntax : ls –a <dir name>
# ls –a  ~

Write a command that displays only directory in your home directory.
Syntax: ls –d */
# cd ~
# ls –d */
Write a command to see the list of recent command used.
Syntax: history
# history
1 cd ~
2 ls –d */
Create a directory 'test' on your home directory.
Syntax : mkdir <directory name>
# cd ~
# mkdir test
Create a file 'myFile' on your home directory using vi editor.
Syntax : vi <file name>
# vi myFile
File will create and open
Press i  for insert mode
Write following text
“this is test file”
Press “ESC” to switch command mode
Then press “shift + : ”
:w + Enter (to save)
:wq + Enter (to save and exit)
:wq! + Enter (to save and exit force fully)
:x + Enter (to save and exit)



Create a Symbolic Link of ‘myFile’ in the currenct directory.
Syntax : ln –s <source file path>  <destination file path>
# ln –s /home/student/myFile   softfile
Rename the ‘test’ directory to ‘NewTest’ directory.
Syntax : mv <old dir> <new dir>
# mv test NewTest

Delete the ‘myFile’ file from the home directory.
Syntax : rm <fille name>
# cd ~
# rm myFile
Write the symbolic representation (eg: rwx-wx—x) of each of the following numeric permissions.
644 ………………….. : rw-r--r--
755 …………………..
600 …………………..
000 …………………..
137 …………………..
124 …………………..
You have given a file with permissions 755, write a command that would change the permissions to r-xr—rwx ?
Syntas : chmod <permission> <file/dir>
# chmod 547 myFile
Change the permission of a file ‘myFile’ such that owner gets full permission, group member get read , write permission & other gets read only permission.
Syntas : chmod <permission> <file/dir>
# chmod 761 myFile

Change the permission of the file so that all category of the user gets full permission.
Syntax : chmod <permission> <file/dir>
# chmod 777 myFile

Write a command that search ‘passwd’ file in /etc directory.
Syntax :find <file name>
# cd /etc
# find passwd

Redirect the above search result to the ‘result.out’ file.
Syntax : find <search result> > <output file name>
# find passwd > result.out
# cat result.out


Redirect the error message to the ‘error.out’ file.

Write a command that counts words, lines, bytes and characters of the ‘result.out’ file.
syntax: wc <option>  <filename>
e.g.
# wc –w result.out  //words
# wc –l result.out  //lines
# wc –c result.out  //bytes
# wc –m result.out  //characters

Sort the ‘error.out’ file.

Sort the content of /etc/passwd in dictionary word and save the sorted result in another file named sorted_passwd.
Syntax: sort <filename> > <result filename>
# sort /etc/passwd   > sorted_password


Display the /etc/passwd line for any account that starts with the letter ‘a’.
Syntax: grep “search text/letter” <filename>
# grep “^a” /etc/passwd

Find all the configuration files inside  /etc directory that have .conf  as the file extension.
Syntax:find <folder path> *.extension
# find /etc/*.conf

Find all the empty files inside the current directory.
Syntax: find <directory name>  -type  <type> -empty
#find –type f -empty

Find all the files and directories that belongs to a user and that have 777 permission.


Display the number of lines in /etc/passwd file.
Write a shell script that displays “Hello World” .
Write a shell script that prompts for age and displays it to screen.
Write a shell script that guess root password. If password match displays the message “you are right”, other wise displays “try again”.
Write a shell script that calculate simple interest for 3 sets of P,N,R values.
Write a shell script that takes a command –line argument and reports on whether it is directry, a file, or something else.
Write a shell script that computes the gross salary of a employee according to the following.
a) if basic salary is <5000 then Allowance 10% of the basic salary.
b) if basic salary is >5000 then Allowance 25% of the basic salary.
The basic salary is entered interactively through the key board
Write a shell script that displays a list of all files in the current directory to which the user has read write and execute permissions .
Develop an interactive script that asks for a word and file name and then tells how many times that word occurred in the file.
Write a shell script which receives two files names as arguments. It should check whether the two file contents are same or not. If they are same then second file should be deleted.

Read more →

FIFA World Cup 2014 : Live Streaming SONY SIX

Read more →