lab assignment 1 - google search

Lab Assignment–1

1. Create table Student (Rno, Name, DOB, Gender, Class,

College, City, Marks)

2. Insert 5 records in student table

3. Display the information of all the students

4. Display the detail structure of student table

5. Display Rno, Name and Class information of ‘Patiala’

students.

6. Display information on ascending order of marks

7. Change the marks of Rno 5 to 89.

8. Change the name and city of Rno 9.

9. Delete the information of ‘Amritsar’ city records

10. Delete the records of student where marks<30.

Lab Assignment-2

1. Create table emp which has the following attributes (employee table)

(empno, ename, job, sal, deptno)

2. Insert appropriate records in above tables.

3. Get employee no and employee name who works in dept no 10

4. Display the employee names of those clerks whose salary > 2000

5. Display name and sal of Salesperson & Clerks

6. Display all details of employees whose salary between 2000 and 3000

7. Display all details of employees whose dept no is 10, 20, or 30

8. Display name of those employees whose commission is NULL

9. Display dept no & salary in ascending order of dept no and with in each dept no

salary should be in descending order

10. Display name of employees that starts with ‘C’

11. Display name of employees that ends with with ‘C’

12. Display name of employees having two ‘a’ or ‘A’ chars in the name

13. Display the name of the employees whose second char is ‘b’ or ‘B’

14. Display the name of the employees whose first or last char is ‘a’ or ‘A’

Assignment3

Write queries to:

1. Display the system date

2. Display current day

3. Display current month and spell out year

4. Display spell out current date

5. Check whether it is AM or PM right now

6. Display the date of next Friday

7. Round the system date on month

8. Truncate the system date on month

9. Round the system date on year

10. Truncate the system date on year

11. Find the day after three days

Queries Based on EMP table

12. Display day of date of joining column

13. Display those employees who join the company on Monday

14. Display those employees who join the company this month

15. Display those employees who join the company in last 30 days

Create a table train having three four columns

16. Train Number, date of Departure, time of departure, time of arrival

17. Insert five columns in train table

18. Display all the records

19. Display the time values inserted in the columns

20. Display those trains which arrived on PM

21. Display train number who are going to depart in next on hour.

Lab Assignment–4

Q1) Use the following functions

1. chr (n):

2. cancat(char1,char2):

3. instr(string,char):

4. length(n):

5. lpad(char1 ,n [,char2]):

6. ltrim(string [,char(s)]):

7. rpad(char1 ,n [,char2]):

8. rtrim(string [,char(s)]):

9. replace(char ,search_string , replacement_string):

10. substr(string ,position ,substring length):

11. initcap(char):

12. lower(string):

13. upper(string):

14. translate(char ,from string ,to string):

15. abs(n):

16. ceil(n):

17. cos(n):

18. exp(n):

19. floor(n):

20. mod(m ,n):

21. power(x ,y):

22. round(x [,y]):

23. sign(n):

24. sqrt(n);

25. trunc(x ,n):

26. sysdate:

27. add_months(d ,n):

28. last_day():

29. months_between(date1 ,date2):

30. next_day(date ,char):

31. greatest(expr):

32. least(expr):

Q2) Display current time in hour : min : sec format

Q3) Display salary + commission of emp table

Q4) Store any date value in hiredate column of table ?

Q5) Display name of employee(s) who join the company in

1985 ?

Q6) Display name of the employee(s) who join the company

this year ?

Lab Assignment–5

1. Create table emp which has the following attributes

(employee table)

(@empno, ename, job, sal, deptno)

Where empno is primary key, ename is unique, job in

(Prof, AP, and Lect), sal is not NULL, and deptno is

foreign key

2. Create table dept which has the following attributes

(department table)

(@deptno, dname)

Where deptno is primary key, dname in (Acc, comp, elect)

3. Create table S which has the following attributes

(Salesperson table)

(@sno, sname, city)

Where sno is primary key

4. Create table P which has the following attributes (Part table)

(@pno, pname, color)

Where pno is primary key

5. Create table J which has the following attributes

(ProJect table)

(@jno, jname, city)

Where jno is primary key

6. Create table SPJ which has the following attributes (@ (sno, pno, jno), qty)

Where combination of (sno, pno, jno) is primary key, also

sno, pno, jno are foreign keys

7. Insert appropriate records in above tables.

Lab Assignment–6

Q1) Check the structure of tables.

Q2) Check the constraint name for applied constraints?

Q3) Drop the unique constraint on ENAME

Q4) Drop the Foreign Key constraint on DEPTNO

Q5) Add Foreign Key constraint on DEPTNO

Q6) Change Data type of ENAME

Q7) Change width of DNAME

Q8) Add COMM column in EMP table

Q9) Drop CITY column from J table

Q10) Create duplicate copy of EMP table

Q11) Copy structure of DEPT table in another table with

different column names

Q12) Change the name and job of the employee whose EMPNO

=100

Q13) Delete the record of employee who belong to computer

department

Q14) Drop DEPT Table

Q15) Drop duplicate table of EMP table

Lab Assignment–7

1. List the total number of employees?

2. List the total no of departments ?

3. List the total, maximum, & minimum salary where deptno is

30?

4. Display the name of the employee getting maximum salary?

5. Display the total salary for each department?

6. Display the total salary for each job.

7. Display the total salary for each job within each

department.

8. Display the average salary for each job in deptno 20.

9. Display the total salary for each job excluding the

‘manager’ job.

10. Display the average salary for each job in deptno 20,

but only display those jobs where total salary is greater

than 2000 & display the output in descending order of

salary?

11. Display the total no of employees for each department

excluding the dno 10 & display only those departments

where more then five (5) employees work. Display the

output in descending order of total no of employees?


excluding the comp dept & display only those departments



13. Display total number of prof in univ.

14. Display total number of prof in deptno 10.

15. Display total number of prof in each dept.

16. Display total number of emp working in each job in

each dept.

Queries based on Student Table

17. List the total number of students from each city.

18. Name of city having max students.

19. Name of Institute having min students.

20. Name of Institute whose student got max marks.

21. Name of institute having more than 5 students

excluding ‘tu’.

22. Name of Institute whose student got max marks.

Lab Assignment–8

23. Display the total salary for each department?

24. Display the total salary for each job.

25. Display the total salary for each job within each

department.

26. Display the average salary for each job in deptno 20.

27. Display the total salary for each job excluding the

‘manager’ job.

28. Display the average salary for each job in deptno 20,

but only display those jobs where total salary is greater

than 2000 & display the output in descending order of

salary?


excluding the dno 10 & display only those departments




excluding the comp dept & display only those departments



31. Display total number of prof in univ.

32. Display total number of prof in deptno 10.

33. Display total number of prof in each dept.

34. Display total number of emp working in each job in

each dept.

Lab Assignment–8

Queries based on S,P and SP Tables

1. Display total no of suppliers supplying parts?

2. Display the name of the supplier who supplies

quantity greater than the average quantity supplied?

3. Display total quantity supplied by each supplier?

4. Display part name & total quantity supplied for each

part?

5. Display total no of parts supplied by each supplier

excluding suppliers of Patiala & also display only

those suppliers who supply more than five (5) parts?

6. For each part supplied get part number and names of

all cities supplying the part.

7. Get Qty supplied for Red parts.

8. Get Sname supplying Red part.

9. Get Sname, Pname who supply qty more than 100.

10. Get all pairs of suppliers numbers such that the

two suppliers are located in the same city.

11. Get Sname for suppliers who supply part P2.

12. Get Supplier numbers for suppliers who supply at

least one part supplied by supplier S2.

13. Get supplier names for suppliers who do not

supply part P2.

14. Get suppliers numbers for suppliers who are

located in same city as supplier S1.

15. Get ename and mgrname from emp table

16. Get name of emp who join the company before their

managers

17. Get dname and ename also get those deptt which has no

emp

18. Get all possible parts which can be supplied by

suppliers of patiala

Lab Assignment–9

1. Display ename who belong to same department as that of

‘ajay’

2. Display ename who do same job as that of ‘ajay’

3. List all top five (5) highest paid employees from

employee tabe

4. List name of five (5) employees who has minimum pay

5. Display the detail of department where manager no is 101

6. List named of employees whose salary is less then any

clerk and are not clerks

7. Display the job name with highest average salary

8. Display name of the employee(s) whose salary id greater

than average salary of department

9. Display name of those employee in each department who get

the highest salary

10. List all employees who have at least one (1) person

reporting to him/her

11. List of employees who do not manage any other

employee

12. Display name of those employees whose salary is

greater than the lowest salary of employee belonging to

deptno 20


greater than the average salary of manager


greater than highest salary of all employees belonging to

deptno 20

15. List the name of the employees who earn more then the

highest paid manager

Lab Assignment–11

Syntax:

Declare

Begin

End;

If COND then

Else

End if;

If cond then

Elsif

Elsif

End if;

Loop

If cond then

Exit;

End if;

End loop;

Loop

Exit when

cond;

End loop;

While cond

loop

End loop;

For I in 1..10

Loop

End loop;

Problems:

1. PL/SQL block to update total sal for empno 100.

Eno,ename, bp,da,hra,total.

2. PL/SQL block to calculate fine for rno 100

Rno, bookno, doi, dor, fine

Fine is rs 1 if days<7

Fine is rs 2 if days<14 and >7

Fine is rs 3 if days>14

Amount mentioned is for each day.

3. PL/SQL block that performs addition (+), subtraction

(-), multiplication (*) and division (/) of two

numbers as choice by the user.

4. PL/SQL block to find the largest of three numbers.

5. WAP that generate multiplication table of a number

(n).

6. PL/SQL block to generate multiplication table for 3 to

n.

7. PL/SQL block to print 5, 10, 15,20

8. Pl/SQL block to display welcome message like good

morning, good afternoon, good night depending on

system time.

9. WAP that calculate simple interest for principal 1000, time 2 years and rate of interest varies from 5 to 15.

Store it in a table.

Principal time rate interest

Lab Assignment–12

1. WAP to print name of employees belonging to deptno 10

2. WAP to find total salary of n employees.

3. WAP to find top 5 highest paid employees

4. Perform Q1, 2 & 3 with cursor for loop.

5. WAP to increase the salary of employees of deptno 10 10 by 10%, deptno by 20%, and sore the old sal and new

sal for each employee in another table.

6. Use update & delete statement in PL/SQL, print the

number of records updated/deleted or operation is

succeeded / failed.

Lab Assignment–13

1. Write a stored procedure to calculate addition, subtraction, multiplication and division of two

numbers.

2. Call this procedure from a block

3. Write a local procedure for the same.

4. Write stored function to return he number of record updated.

5. Write a local function for the same.

6. Write functions and procedures to perform Insert, Update, Delete and Retrieve operations on emp.

7. Write a function to calculate the total marks and call this function from cursor to process all the records

of student.

Lab Assignment–14

1. Write a trigger to implement primary key constraint

2. Write a trigger to implement foreign key constraint

3. Write a trigger such that no updation on emp can takes place on Sunday.

4. Write a trigger such that commission cannot be grater than sal.

5. Write a trigger to maintain backup table for emp.

6. Write a trigger to implement Log table to store the nature of each operation and time.

lab assignment 1 - google search

Documents