Scribd
Upload
110 views11 pages

ADTA Practical: Ranking Functions in SQL

This document contains an assignment on advanced SQL topics for a student named Nikita S. Bhosle. It includes questions on ranking and dense ranking functions, first/last functions, lead/lag functions, and windowing clauses. The student is asked to create and query tables to demonstrate their understanding of these SQL concepts.

Uploaded by

Utkarsh Lavate
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
110 views11 pages

ADTA Practical: Ranking Functions in SQL

This document contains an assignment on advanced SQL topics for a student named Nikita S. Bhosle. It includes questions on ranking and dense ranking functions, first/last functions, lead/lag functions, and windowing clauses. The student is asked to create and query tables to demonstrate their understanding of these SQL concepts.

Uploaded by

Utkarsh Lavate
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Name: Nikita S.

Bhosle
Roll No.:07
Class: MCA (B)

Bharati Vidyapeeth’s
Institute of Management & Information Technology

MCA SEM IV
ADTA PRACTICAL
Assignment 2
Topic: Rank And Dense Date:

Q1. Create a table student with attribute roll_num, name, subject, marks.

1. Display content of table.


Name: Nikita S. Bhosle
Roll No.:07
Class: MCA (B)

2. Assign sequence order for the student for the same subject based on their marks.
Name: Nikita S. Bhosle
Roll No.:07
Class: MCA (B)

3. Assign sequential order for the student for the same subject based on their marks in descending
order.

4. Assign sequential order using dense rank function.


Name: Nikita S. Bhosle
Roll No.:07
Class: MCA (B)

cume_dist() and percent_rank()

1. Select roll number, name , subject, marks, cume_dist() over partition by subject order by
marks from student
Name: Nikita S. Bhosle
Roll No.:07
Class: MCA (B)

2. Select roll number, name , subject, marks, percent_rank() over partition by subject order by marks
from student

FIRST AND LAST


Name: Nikita S. Bhosle
Roll No.:07
Class: MCA (B)
Q2.) 1. Display the lowest marks of each subject.

2. Display the highest marks of each subject.


Name: Nikita S. Bhosle
Roll No.:07
Class: MCA (B)

LEAD AND LAG

Q3. Create a table employee with attribute empid, name, deptid,deptname, salary and joining date.

1. Display all the details of table.

2. Display the joining details of the employees joined just after the joining date of each employee in
sales department.
Name: Nikita S. Bhosle
Roll No.:07
Class: MCA (B)

3. Display joining date of all employee joined just before the joining date of employee

4. For each employee in employee table display the salary of the employee joined just before.

Windowing Clause

Q4. Create a table employee with attribute emp_no ,emp_name , deptid,dept_name and salary.
Name: Nikita S. Bhosle
Roll No.:07
Class: MCA (B)

1. Display the contents of the table.

2. Display emp_no,emp_name,dept_name,salary and dept wise sum of salary of current and previous
two records.
Name: Nikita S. Bhosle
Roll No.:07
Class: MCA (B)

3. Display emp_no,emp_name,dept_name,salary and sum of salary for 3 earlier row and 1 next row
dept wise.

4. Display emp_no,emp_name,dept_name,salary and sum of salary for all preceding row and current
row dept wise.

5. Display emp_no.emp_name,dept_name,salary and sum of salary 3 preceding row and 1 preceding


row dept wise.
Name: Nikita S. Bhosle
Roll No.:07
Class: MCA (B)

6. Display emp_no.emp_name,dept_name,salary and sum of salary 1 following and 3 following row


dept wise.

You might also like