Scribd
Upload
5 views5 pages

Lecture SQL Query Practice Questions

The document contains a series of SQL practice questions and their corresponding solutions based on two schemas: one for employee and project data, and another for airline flight information. Each question requires writing SQL queries to retrieve specific information, such as employee titles, salaries, project budgets, and flight details. The solutions provide the exact SQL syntax needed to execute these queries effectively.

Uploaded by

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

Lecture SQL Query Practice Questions

The document contains a series of SQL practice questions and their corresponding solutions based on two schemas: one for employee and project data, and another for airline flight information. Each question requires writing SQL queries to retrieve specific information, such as employee titles, salaries, project budgets, and flight details. The solutions provide the exact SQL syntax needed to execute these queries effectively.

Uploaded by

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

SQL Queries Practice Questions

Question 1
Consider the following schema given below and write the appropriate SQL queries
emp (eno, ename, bdate, title, salary, dno)
proj (pno, pname, budget, dno)
dept (dno, dname, mgreno)
workson (eno, pno, resp, hours)

1. Write an SQL query that returns the employees (number and name only) who have a title of 'EE' or 'SA'
and make more than $35,000.
2. Write an SQL query that returns the employees (name only) in department 'D1' ordered by decreasing
salary.
3. Write an SQL query that returns the employee name, department name, and employee title.
4. Write an SQL query that list name of employees which begins with the 'A' character.
5. Write an SQL query that returns the project name, department name, and budget for all projects with a
budget < $50,000.
Solutions
Write a SQL query that returns the employees (number and name only) who have a title of 'EE' or 'SA' and
make more than $35,000.
SELECT eno, ename FROM emp WHERE (title = 'EE' OR title = 'SA') AND salary > 35000

Write a SQL query that returns the employees (name only) in department 'D1' ordered by decreasing
salary.
SELECT ename FROM emp WHERE dno = 'D1‘ ORDER BY salary DESC

Write a SQL query that returns the employee name, department name, and employee title.
SELECT ename, dname, title FROM emp, dept WHERE [Link] = [Link]

Write a SQL query that list name of employees which begins with the 'A' character.
SELECT * FROM emp WHERE ename LIKE 'A%';

Write a SQL query that returns the project name, department name, and budget for all projects with a
budget < $50,000.
SELECT pname, dname, budget FROM proj, dept WHERE budget < 50000 AND [Link] =
[Link]
Question 2
The following relations keep track of airline flight information:
Flights(flno: integer, from: string, to: string, distance: integer, departs: time, arrives: time, price: real)
Aircraft(aid: integer, aname: string, cruisingrange: integer)
Certified(eid: integer, aid: integer)
Employees(eid: integer, ename: string, salary: integer)

Note that the Employees relation describes pilots and other kinds of employees as well;
every pilot is certified for some aircraft, and only pilots are certified to fly.
Write each of the following queries in SQL.
1. Find employees(s) with highest salary.
2. List flights where the departure time is later than the arrival time
3. Retrieve the names of employees who are certified to operate the
aircraft with the longest cruising range
4. Find the average cruising range of all aircraft
5. Find the names of pilots certified for some Boeing aircraft.
6. Find the aids of all aircraft that can be used on routes from Los Angeles
to Chicago.
7. Find employees who are certified to operate any aircraft
8. List employees who are certified to operate the aircraft with the longest
cruising range
9. Find flights with a price lower than the average price of all flights

You might also like