ORACLE ASSGN-4

1. Create a query to display the last name and salary of employees earning more than $12,000.
Place your SQL statement in a text file named lab4_1.sql. Run your query.
2. Create a query to display the employee last name and department number for employee number
176.
3. Modify lab4_1.sql to display the last name and salary for all employees whose salary is
not in the range of $5,000 and $12,000. Place your SQL statement in a text file named lab4_3.sql

4. Display the employee last name, job ID, and start date of employees hired between February
20, 1998, and May 1, 1998. Order the query in ascending order by start date.
5. Display the last name and department number of all employees in departments 20 and 50 in
alphabetical order by name.
6. Modify lab4_3.sql to list the last name and salary of employees who earn between $5,000
and $12,000, and are in department 20 or 50. Label the columns Employee and Monthly
Salary, respectively. Resave lab4_3.sql as lab4_6.sql. Run the statement in
lab4_6.sql.
7. Display the last name and hire date of every employee who was hired in 1994.
8. Display the last name and job title of all employees who do not have a manager.
9. Display the last name, salary, and commission for all employees who earn commissions. Sort
data in descending order of salary and commissions.
10. Display the last names of all employees where the third letter of the name is an a.
11. Display the last name of all employees who have an a and an e in their last name.
12. Display the last name, job, and salary for all employees whose job is sales representative or
stock clerk and whose salary is not equal to $2,500, $3,500, or $7,000.
13. Modify lab4_6.sql to display the last name, salary, and commission for all employees
whose commission amount is 20%. Resave lab4_6.sql as lab4_13.sql. Rerun the
statement in lab4_13.sql.

ORACLE ASSIGN-3

1. Add a table-level PRIMARY KEY constraint to the EMP table on the ID column. The constraint should be named at creation. Name the constraint my_emp_id_pk

2. Create a PRIMARY KEY constraint to the DEPT table using the ID column. The constraint
should be named at creation. Name the constraint my_dept_id_pk.

3. Add a column DEPT_ID to the EMP table. Add a foreign key reference on the EMP table that ensures that the employee is not assigned to a nonexistent department. Name the constraint
my_emp_dept_id_fk.

4.Confirm that the constraints were added by querying the USER_CONSTRAINTS view. Note the types and names of the constraints.

5. Display the object names and types from the USER_OBJECTS data dictionary view for the EMP and DEPT tables. Notice that the new tables and a new index were created.

6. Modify the EMP table. Add a COMMISSION column of NUMBER data type, precision 2, scale 2. Add a constraint to the commission column that ensures that a commission value is greater than zero.

7.Create the EMPLOYEES2 table based on the structure of the EMPLOYEES table. Include only
the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and DEPARTMENT_ID columns.
Name the columns in your new table ID, FIRST_NAME, LAST_NAME, SALARY , and
DEPT_ID, respectively.

8. Create the DEPT table based on the following table instance chart, then execute the statement in the script to create the table. Confirm that the table is created.
ID NUMBER 7
NAME VARCHAR2 25

9. Populate the DEPT table with data from the DEPARTMENTS table. Include only columns that
you need.

ORACLE ASSGN-2

1. Create an employee table with attributes

emp_id not null number(6)
First_name varchar2(20)
Last_name not null varchar2(25)
Email not null varchar2(25)
Phone_no varchar2(20)
Hire_date not null date
Job_id not null varchar2(10)
Salary number(8,2)
Commission number(8,2)
Manager_id number(6)
Dept_id number(4)

2. Insert 10 records. Confirm the addition of records into the table

3. Create a query to display the last name, job_id, hire_date, emp_id for each employee with emp_id appearing first. Provide an alias startdate for the hire_date column. Save your SQL statements to a file named lab_2.sql.

4. Create a query to display unique job_IDfrom the employee table
display the last name concatenated with the job_id separated by a comma and space, and name the column ‘employee and title’ .

5. Create a query to display all the data from the employee table. Separate each column by a comma. Name the column output.

6 Change the last name of employee 3 to ‘Alexander’
7 Change the salary to 10000 for all the employees with a salary < 9000. Verify the changes into the table.
8 Delete employee 4 record from employee table. Confirm the deletion.
9. Create a department table with attribute
Dept_id
Dept_name
Manage_id
Location_id

Insert 10 records into it.

10. Creat a dept table and populate the dept table with data from the department table. include the only columns you need
11. Create the emp table and populate the emp table with data from the employee table
modify the emp table to allow for longer employee last names. Confirm your modification
12. Create the emp_2 table based on the employee table. Include only emp_id, first_name, Clast_name, Salary and dept_id.
13. Drop the emp table
14. Rename the emp_2 table as emp
15. Drop the first_name column from the emp table

ORACLE ASSIGN-1

Q.1 Create Table

A) create the table CLIENT with following attributes

Clientno varchar2(6)
Name varchar2(20)
Address1 varchar2(30)
Address2 varchar2(30)
City varchar2(15)
Pincode number(8)
State varchar2(15)
Baldue number(10,2 )


B) create the table PRODUCT with following attributes

Productno varchar2(6)
description varchar2(15)
profitpercent number(4,2)
unitmeasure varchar2(10)
qtyinhand number(8)
reorderlvl number(8)
sellprice number( 8,2)
costprice number (8,2)

C) create the table SALESMAN with following attributes

SALESMANNO varchar2( 6)
SALESMANNAME varchar2( 20 )
ADDRESS1 varchar2 (30 )
ADDRESS2 varchar2 (30)
CITY varchar2 (20 )
PINCODE number (8 )
STATE varchar2 (20 )
SALAMT number (8,2 )
TGTTOGET NUMBER (6,2 )
YTDSALES NUMBER (6,2 )
REMARKS varchar2 (60)
Q.2 Insert the following data into respective tables:

a. data for CLIENT table (6 records)
b. data for PRODECT table (9 records)
c. data for SALESMAN table (4 records)

Q.3 Retrieving records from a table

a. find out the names of all the clients
b. retrieve the entire contents of the client table
c. retrieve the list of names, city and the state of all the clients.
d. List the various products available from the product table
e. List all the clients who are located in Mumbai
f. Find the names of salesmen who have a salary equal to Rs 3000.

Q.4 updating records in a table

a. Change the city of clinet no ‘C00005’ to ‘Bangalore’
b. Change the BalDue of ClientNo ‘C00001’ to Rs.1000
c. Change the cost price of ‘Trousers’ to Rs.950.00
d. Change the city of the salesman to Pune.

Q.5 Delete records in a table
a) Delete all salesmen from the Salesman whose salaries are equal to Rs. 3500
b) Delete all products from Product where the quantity on hand is equal to 100
c) Delete from Client where the column state holds the value ‘Tamil nadu’.

Q.6 Altering table structure

a. Add a column called ‘Telephone’ of data type ‘number’ and size=’10’ to the Client table.
b. Change the size of Sellprice column in product to 10,2

Q.7 deleting the table structure along with the data
a. Destroy the table Client along with its data

Q.8 Renaming the table
a. Change the name of the salesman table to sman.

UNIX ASSIGN-1

1. Display calender for the year 1752. Comment on the answer.
2.Display date in dd/mm/yyyy. display "Today is 12 september 2008"
3. Display 30 september 2008.
4 Convert decimal to binary . ex- 35,108
5. Convert binary to decimal. ex-1101110
6. convert decimal to hexadecimal. ex-96
7. Display time only in hour min format separated by hypen.