1Practical 1: Create following two tables including integrity constraints using SQL commands: Table Name : Employee Field Name Data Type Field Name | Data Type | Field Size Employee ID | Number Foreign Key sine | : Company Text Primary Key Name Salary Number 10,2 >10000 AND <25000 Now, perform the following operations using SQL commands : (i) List all employees names working in ‘FBC’. (ii) Find the number of employees earning a Salary greater than average salary of all employees. Primary key.
2Practical 2: Create following tables : Student(StudentID, Student_name, age) Registered(StudentID, CourselD, dt_of Join) Add the following constraints to the table using alter command: (i) Primary Key and respective foreign key for all tables. (ii) age>20 and age<60 List the student id and name of each student along with the total number of courses that the student is registered for.
3Practical 3: Create following two tables including integrity constraints using SQL commands: Table Name : Part Field Name Data Type Field Size Table Name : Subpart Field Name | Data Type | Field Size Sub Part ID Primary Key Count Number 5 >=0 AND <100 Now, perform the following operations using SQL commands : (i) List the name of all the subparts whose part-id is “P-100”. (iii) Find the total cost of part “P.100” including subparts. Foreign Key.
4Practical 4: Create following two tables including integrity constraints using SQL commands: Table Name : Student Table Name : Enrollment Now, perform the following operations using SQL commands : (i) List all the students enrolled for ‘BBA’ and have grade ‘A’. (ii) Find the number of students enrolled in each course. Primary key. Foreign Key.
5Practical 5: Create following tables : Employee(empno, name, office, age) Books(isbn, title, author, publisher) Loan(empno, isbn, date) Add the following constraints to the table using alter command: (iii) Primary Key and respective foreign key for all tables. (iv) age>20 and age<60 List the names of employees having any book published by ‘TMH’.
6Practical 6: Create following two tables including integrity constraints using SQL commands: Table Name : Student CSTCITY Text 3 Perform the following operations using SQL commands : (i) Provide constraint names while creating table. (ii) Drop the check constraint on CSTCITY. (iii) Create a new constraint chkdep to check that the deposit is within the range 1500 to 30000.
7Practical 7: Consider EMP table and create a new table based on the EMP table. Add the necessary constraint to the new table and perform the following using SQL commands : (i) List employee number, employee name, total salary (i.e. salary+commission). (Note: Manipulate the NULL values accordingly) (ii) Display the name, job and salary of all employees whose job is CLERK or ANALYST and their salary is not equal to 1000, 3000 or 5000. (iii) Display the name, salary and commission for all employees whose commission amount is greater than their salary increased by 10%.
8Practical 8: Create following two tables including integrity constraints using SQL commands: Table Name : Student Table Name : Enrollment Field Name - Data Type- Field Size - Constraints Roll No Number Primary key, Foreign Key Now, perform the following operations using SQL commands : (i) List all the students who have the same grade as that of ‘SUNIL’. (ii) List all the students who have enrolled in maximum number of courses.
9Practical 9: Consider EMP table and perform the following using SQL commands : (i) Add a new column called total sal to the table. (ii) Update the total sal column with sal+commission. (iii) Find the employees having salary greater than average salary of the employees working in dept 10. (iv) List employee name and yearly salary and arrange the output on the basis of yearly salary in descending order. (v) Retrieve the names of departments in ascending order and their employees in descending order.
10Practical 10: Consider EMP table and perform the following operations using SQL commands : (i) Select the name, Job, salary and department number of all employees except MANAGER from department number 5. (ii) List all departments in which more than 2 employees are working. (iii) Find the employee earning the second highest salary. (iv) List all the employees whose salary is greater than yearly commission.
11Practical 11 (PL/SQL code Block): 1. Write a PL/SQL code block to calculate the area of a circle for a value of radius varying from 3 to 7. Store the radius and the corresponding values of calculated area in an empty table named Areas. 2. Write a PL/SQL Block of code for inverting a number 5639 to 9365. 3. Write a PL/SQL Block of code that will merge the data available in the newly created table NEW_BRANCHES with the data available in the table BRANCH_MSTR. If data in the first table already exist in the second table then that data should be skipped. 4. Write a PL/SQL block to display whether the given number is Odd or Even.
12Practical 12 (PL/SQL code Block): 1. Write a PL/SQL block to display BPIBS 10 times using for loop. 2. Write a PL/SQL Block to print the sum of numbers from 1 to 50. 3. Write a PL/SQL block to find the salary of a given employee and raise his salary by 20%.
13Practical 13 (PL/SQL code Block): 1. Write a PL/SQL block to calculate the average salary from table ‘Emp’ and print increase the salary if the average salary is less that 10,000. 2. Write a PL/SQL block to print the deptno from the employee table using the case statement; if the deptname is ‘Technical’ then deptno is 1, if the deptname is ‘HR’ then the deptno is 2 else deptno is 3. 3. Write a PL/SQL block to display the detail about given employee from EMP table.
14Practical 14 (PL/SQL Procedures): 1. Create a procedure on table employee to display the details of employee whose salary will be provided during execution. 2. Write a PL/SQL Procedure to print the following output. * Ok * ie ee . 4 on eae “fy oe ag tt * of *
15Practical 15 (PL/SQL Functions): 1. Create a function that accepts the Programmer_id and checks if the Programmer _id exits knowledge of specified language. 4. Write a function on Programmer table to return age of specified person. 5. Write a function on studies table to return course fee of a specified course.
16Practical 16 (PL/SQL Triggers): 1. Write a database trigger to check that date_of_joining is not empty. Also check that the date of joining should be greater than date_of_birth field in EMPLOYEE table. 2. Write a database trigger on EMPLOYEE table to check that first letter of the emp_id is. 3. Create a trigger on EMPLOYEE table for deletion where the whole table is displayed whenever a delete operation is performed. 4. Create trigger on EMPLOYEE table on update or insert of emp_name to convert emp_name into capital letter.
17Practical 17 (PL/SQL Cursors): 1. Write a PL/SQL block using cursor to update salary of a given programmer by 25%. 2. Create a cursor emp_cur, fetch record from emp table and check whether sal>10000 then update Grade = ‘A’ else if sal >= 5000 and sal <= 10000 then update Grade = ‘B’. 3. Write a PL/SQL block with cursor showing the use of SQL%FOUND attribute. 4. Write a PL/SQL block with cursor showing the use of SQL%ROWCOUNT attribute. 5. Write a PL/SQL block with cursor showing the use of SQL%ISOPEN attribute.