Spatial Queries

To calculate the distance between two place write spatial queries.

Solution:

Declare @Meterpermile Float = 1609.344;

Declare @Locstart GEOGRAPHY = GEOGRAPHY :: Point (30.7333,76.7794,4326)

Declare @LOCDest GEOGRAPHY = Geography :: Point (23.2825, 72.7774, 4326)

 

Select ‘ Chandigarh to Delhi in Miles’ [ Label ], @LocStart.STDistance(@LocDest)/@MeterPerMile [Distance];

—//—

 

Find Hierarchy Level Of Organizations.

Solution:

Select Org.Node.ToString() As Text_OrgNode,

OrgNode.GetLevel() As Emplevel,*

From HumanResources,EmployeeOrg;

—//—

 

To Print Name, Employee of employee i.e, their subordinates.

Solution:

DECLARE @Current Employee Hierarchyid,

Select @ CurrentEmployee = Org.Node

From Human Resource.Employee Org

Where EmployeeID =46;

 

Select *

From HumanResources.EmployeeOrg

Where Org.Node.IsDecendentOf(@CurrentEmployee)=1;

—//—

 

Find root node in the Hierarchy.

Solution:

Select OrgNode.ToString()As Text_OrgNode,*

From HumanResources.EmployeeOrg

Where OrgNode = Hierarchy id::GetRoot();

—//—

Advertisements

SQL Queries

Assume a table containing rollno, name & another table containing roll no, subcd, & Marks.

Write a SQL query to:

  1. Display the student having opted maximum number of subjects.
  2. Display the subject wise maximum marks obtained by students.
  3. Display the merit list based on total marks.
  4. Display the number of students having scored 10 marks higher than average marks in subcd =01.
  5. Display the roll no. of the students who has not opted any subject.
  6. Display the total number of subjects offered.
  7. Display the roll no. of the student who has not opted any subject.
  8. Display the students who have opted all the subjects.
  9. Display the students total marks and the percentage obtained assume the maximum marks in every subject are 100.
  10. Display the top 10 worst performers in every subject.

 

Solutions:

Student (rollno, name)

Marks (rollno, subcd, marks)

 

 

1

->Student having opted maximum number of subjects.

Select Name

from student, marks

Where students rollno. = marks.rollno

Group by marks.rollno

Order by count (subcd)

DESC LIMIT 1

—//—

2

->Display the subject wise maximum marks obtained by students.

Select Subcd, max(marks)

From Marks

Group By Subcd

—//—

3

-> Display the merit list based on total marks.

Select roll no, sum (marks)

From Marks

Group By roll no

Order By sum (marks) DESC

—//—

4

-> Display the number of students having scored 10 marks higher than    average marks in subcd =01.

Select count* From Marks

Where subcd =01 && marks –(select avg(marks))

From marks Group by subcd

Having subcd =01 ) =10

 

—//—

5

-> Display the roll no. of the students who has not opted any subject.

Select name

from student

Where name like ‘% Rahul Jain %’ ;

—//—

6

-> Display the total number of subjects offered.

Select count (distinct subcd)

from marks

group by Subcd

—//—

 

7

-> Display the roll no. of the student who has not opted any subject.

Select students roll no

from students

where students, roll no., not in

(select marks,roll no

From marks)

—//—

8

-> Display the students who have opted all the subjects.

Select students, roll no.,

From Students, Marks

Where student, roll no. = marks.rollno.

Group by marks.rollno

Having count (subcd) = (Select count (distinct subject)

From marks)

—//—

9

-> Display the students total marks and the percentage obtained assume the maximum marks in every subject are 100.

Select student.name , sum (marks.marks), sum(marks.marks)/count(suncd) as percent

From student and marks

Group by marks.rollno;

—//—

10.

-> Display the top 10 worst performers in every subject.

 

SELECT TOP 10 Student Name

FROM Student.Name, Marks, Subcd

Group By Marks

ORDER BY Sum (Marks) DESC

—//—

 

 

 

 

 

 

 

 

 

SQL Queries Practice & Solutions

Solve following queries using aggregate functions for the table.

Table: sales (orderID OrderDate OrderPrice OrderQuantity CustomerName)

0

1.Count how many orders have made a customer with customer name of Smith.

1

2.Find the number of unique customers that have ordered from the store.

2

3.Find out total no. of items ordered by all the customers.

3

4.Find out the average number of items per order.

4

5.Find out the average order quantity for all orders with order price greater than 200.

5

6.Find out what was the minimum price paid for any of the orders.

6

7.Find out the highest order price from the given sales table.

7

8.List out unique customers name only from the table.

8

9.List out the name of the customers who have given an order in the month of DECEMBER.

9

10.Find out the total amount of money spent for each of the customers.

10

11. Select all unique customers, who have spent more than 1200 in the store.

11


Now I had updated my table and inserted more values in it.

Capture


12.Select all the customers that have ordered more than 5 items in total from all their orders.

12

13.Select all customers who have spent more than 1000, after 10/01/2005.

13

14.Select orders in increasing order of order price.

14

15 Select orders in decreasing order of order price.

15


Thank You, Like, Share, Subscribe.