RDBMS Assignments

Assgn - 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 dataa. Destroy the table Client along with its data

Q.8 Renaming the tablea. Change the name of the salesman table to sman.
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 tabledisplay 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-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 constraintshould 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 constraintmy_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 , andDEPT_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.

Assignment - 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 number176.
3. Modify lab4_1.sql to display the last name and salary for all employees whose salary isnot 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 February20, 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 inalphabetical order by name.
6. Modify lab4_3.sql to list the last name and salary of employees who earn between $5,000and $12,000, and are in department 20 or 50. Label the columns Employee and MonthlySalary, respectively. Resave lab4_3.sql as lab4_6.sql. Run the statement inlab4_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. Sortdata 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 orstock 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 employeeswhose commission amount is 20%. Resave lab4_6.sql as lab4_13.sql. Rerun thestatement in lab4_13.sql.

oracle assgn 5
1. Display the highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, and Average, respectively. Place your SQL statement in a text file named lab5_1.sql.

2. Display highest, lowest, average salary of the employees whose job ID contains keyword R&D.

3. Count the total number of employees whose department_id =50

4. Display the number of department values in the EMPLOYEES table.

5. Display the number of distinct department values in the EMPLOYEES table.

6. Display the average salaries for each department

7. Display the total salary of the employees for each job on each department.

8. Display the average salary above 8000 of the employees for each department.

9. Display the maximum salary above 8000 for each department.

10. Display the job ID and total monthly salary for each job with a total payroll exceeding $13,000. sort the list by the total monthly salary.

11. Display the maximum average salary for each department.

12. Write a query to display the number of people with the same job.

13 Determine the number of managers without listing them. Label the column Number of Managers. Hint: Use the MANAGER_ID column to determine the number of managers.

14. Write a query that displays the difference between the highest and lowest salaries. Label the column DIFFERENCE.

15. Display the manager number and the salary of the lowest paid employee for that manager. Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is $6,000 or less. Sort the output in descending order of salary.
ORACLE ASSGN - 6
1.Write a query to display the last name, department number, and department name forall employees.

2.Create a unique listing of all jobs that are in department 80. Include the location of thedepartment in the output.

3.Write a query to display the employee last name, department name, location ID, and city ofall employees who earn a commission.Display the employee last name and department name for all employees who have an a(lowercase) in their last names. Place your SQL statement in a text file named lab4_4.sql.

4.Write a query to display the last name, job, department number, and department name for allemployees who work in Toronto.

5.Display the employee last name and employee number along with their manager’s last nameand manager number. Label the columns Employee, Emp#, Manager, and Mgr#,respectively. Place your SQL statement in a text file named lab4_6.sql.

6.Modify lab4_6.sql to display all employees including King, who has no manager. Orderthe results by the employee number.Place your SQL statement in a text file named lab4_7.sql. Run the query inlab4_7.sql.

7.Create a query that displays employee last names, department numbers, and all theemployees who work in the same department as a given employee. Give each column anappropriate level.

ORACLE Assignment-7
1.Write a query to display the current date. Label the column Date.
2.For each employee, display the employee number, last_name, salary, and salary increased by 15% and expressed as a whole number. Label the column New Salary. Place your SQL statement ina text file named lab3_2.sql.
3.Modify your query lab3_2.sql to add a column that subtracts the old salary fromthe new salary. Label the column Increase. Save the contents of the file as lab3_4.sql.Run the revised query.
4.Write a query that displays the employee’s last names with the first letter capitalized and all otherletters lowercase and the length of the name for all employees whose name starts with J, A, or M.Give each column an appropriate label. Sort the results by the employees’ last names.
5.For each employee, display the employee’s last name, and calculate the number of monthsbetween today and the date the employee was hired. Label the column MONTHS_WORKED. Orderyour results by the number of months employed. Round the number of months up to the closestwhole number.
6.Create a query to display the last name and salary for all employees. Format the salary to be 10characters long, left-padded with $. Label the column SALARY.
7.Using the DECODE function, write a query that displays the grade of all employees based on thevalue of the column JOB_ID, as per the following data:JOB GRADE
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
None of the above 0