SQL Training:Exercises on Sub-Queries

Exercise 7:

 1. Write a query to display American Airlines flight’s details.

2. Write a query to find which airline is starting from HYDERABAD.

3. Write a query to display Airline name in which contains more no of flights.

4. Write a query to display no of flights airlines wise.

5. Write a query to find which airline contains more than 5 flights.

6. Write a query to display British airways and Delta airlines flights details.

7. Write a query to display airlines details in which airline contains more than maximum no of flights in Delta Airlines.

8. Write a query to display flight details except Delta airlines.

9. Write a query to display total no of flights based on city from.

10. Write a query to display flights details reaching to New York City.

 MY SOLUTIONS:

 ----------------------------------------------
----------------------------------------------
--SUBQUERY EXERCISE

SELECT f.Airlineid
      ,f.flightid
  ,f.cityfrom
  ,f.cityto
 FROM dbo.flights f
WHERE f.Airlineid = (SELECT a.airlineid
                         FROM dbo.Airlines a
WHERE a.airlinename = 'American Airlines')


SELECT a.airlineid
      ,a.airlinename
  ,a.url
 FROM dbo.Airlines a
WHERE a.airlineid IN (SELECT f.Airlineid
                      FROM dbo.flights f
WHERE f.cityfrom LIKE 'HYD%')

SELECT a.airlineid
      ,a.airlinename
  ,a.url
 FROM dbo.Airlines a
WHERE a.airlineid = (SELECT X.TopAirlineid
                       FROM
                     (
                     SELECT TOP 1 f.Airlineid AS TopAirlineid,COUNT(f.flightid) AS FLIGHTCOUNT
                      FROM dbo.flights f
GROUP BY f.Airlineid
ORDER BY FLIGHTCOUNT DESC
) X
)
--NO NEED FOR SUBQUERY
/*SELECT f.Airlineid
      ,COUNT(f.flightid) AS FLIGHTSPERAIRLINE
 FROM dbo.flights f
GROUP BY f.Airlineid
*/
SELECT f.Airlineid
      ,COUNT(f.flightid) AS FLIGHTSPERAIRLINE
 FROM dbo.flights f
WHERE f.Airlineid IN (SELECT a.airlineid
                       FROM dbo.Airlines a
GROUP BY a.airlineid )
GROUP BY f.Airlineid

SELECT a.airlineid
      ,a.airlinename
  ,a.url
 FROM dbo.Airlines a
WHERE a.airlineid IN (
                     SELECT f.Airlineid AS TopAirlineid
                      FROM dbo.flights f
GROUP BY f.Airlineid
HAVING COUNT(f.flightid) > 5
)

SELECT f.Airlineid
      ,f.flightid
  ,f.cityfrom
  ,f.cityto
 FROM dbo.flights f
WHERE f.Airlineid IN (SELECT a.airlineid
                         FROM dbo.Airlines a
WHERE a.airlinename IN ('British Airways','Delta Airlines')
)

SELECT a.airlineid
      ,a.airlinename
  ,a.url
 FROM dbo.Airlines a
WHERE a.airlineid IN (SELECT f.Airlineid
                        FROM dbo.flights f
GROUP BY f.Airlineid
  HAVING COUNT(f.flightid) >
                          (
                           SELECT COUNT(f.flightid) AS FLIGHTCOUNTDELTA
                             FROM dbo.flights f
                            WHERE f.Airlineid = 'DL'
      )
  )

SELECT f.Airlineid
      ,f.flightid
  ,f.cityfrom
  ,f.cityto
 FROM dbo.flights f
WHERE f.Airlineid IN (SELECT a.airlineid
                         FROM dbo.Airlines a
WHERE a.airlinename <> 'Delta Airlines'
)

--NO NEED FOR SUBQUERY
SELECT f.cityfrom
      ,COUNT(f.flightid) AS FLIGHTSPERCITYFROM
 FROM dbo.flights f
GROUP BY f.cityfrom


SELECT f.Airlineid
      ,f.flightid
  ,f.cityfrom
  ,f.cityto
 FROM dbo.flights f

WHERE f.cityto = 'NEW YORK'

Comments

Post a Comment

Popular posts from this blog

SQL Training: Exercises on Stored Procedures and Functions

Exercises on Stored Procedures and Functions: 1. Create a function that should take two inputs those are product id and location id and should return stock level. Note: Consider product Inventory table in adventurworks database for this requirement. 2. Create a function that should take input as store id and returns the columns ProductID, Name, and the aggregate of line total for each product sold to the store. Note: consider product, sales order details, sales order header and customer tables for this requirements. 3. Someone deleted few records from a table which has an IDENTITY column, so need to create stored procedures to find missing sequential numbers. Note: for this requirement create sample table with identity column and insert some data and delete few records from table. 4. Sales order header table in adventureworks database as follows Requirement: Need to create stored procedure that should take sortdate as argument (varchar datatype) then it should displ...
Read more

SQL Training: Exercise on Clauses,String and Mathematical functions,Date Time and Aggregate Functions

Exercise 3: 1. WAQ to display Tool Designer designation employee’s details. 2. WAQ to display Design Engineer and Tool designation employee’s details. 3. WAQ to display female market specialist employee’s details. 4. WAQ to display single female employee’s details. 5. WAQ to display employees whose vocation hours between 40 and 50. 6. WAQ to display total number of hours away from work for each employee by adding the number of hours taken for vacation and the number of hours taken as sick leave. 7. WAQ to display employees whose job title contain engineer word. 8. WAQ to display employees whose vacation hours contain more than or equal to 40. 9. WAQ to display all employees except tool designer designation employees. 10.WAQ to display employee details whose vacation hours not in range 20 and 40. 11.WAQ to display employee details except tool designer and designer engineer. 12. WAQ to display employee details who joined from 2001-01-01 to 200-12-31. 13. WAQ to...
Read more

SQL Training: Exercises on Functions

Exercises on Functions: 1. Write a Function which returns Ename from EMP table by taking empno as argument. 2. Write a scalar value return function which returns sum of two given numbers. 3. Write a Function to display biggest of 3 given numbers(need to take 3 argument for 3 values) 4. Write a Function to display maximum salary of a given deptno from EMP table. 5. Write a Function to display the SUM, AVG sal of employees according to their deptno’s. 6. Write a Function to display details of departments and their corresponding Employee details from DEPT and EMP tables according to deptno. 7. Write a Function to display all the employee details whose salary between first argument value and second argument value. 8. Write a function to display the Names of Employees whose name contain particular character (character we need to take from argument). 9.Write a function to display specific number of senior most employees in arganization. 10.write a table valued return func...
Read more