Download Sample SQL Questions with Answers and more Exercises Database Management Systems (DBMS) in PDF only on Docsity!
SQL Practice Questions
Consider the following schema definitions: Branch (branchNo, street, city, postcode) Staff (staffNo, fName,lName, position, sex, DOB, salary, branchNo) PropertyforRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) Client (clientNo, fName, lName, telNo, prefType, maxRent) PrivateOwner (ownerNo, fName, lName, address, telNo) Viewing (clientNo, propertyNo, viewDate, comment) Registration (clientNo, branchNo, staffNo, dateJoined)
An instance of the above schemas is given in the last page of the examination. (You may detach and use it if necessary)
For each case below, fill in the blanks such that the SQL queries correspond to the English language queries stated. Each blank is worth 2 points.
- List the address of all branch offices in London or Bristol.
SELECT _______ ***** _______ FROM ___ branch ______ WHERE city=’London’ _ OR city=’bristol’ ______
- List the staff with a salary between $10000 and $30000.
SELECT staff_No FROM Staff WHERE __ salary between 10000 AND 30000 ________________
- List the staff in descending order of salary.
SELECT staff_No, salary FROM Staff ORDER BY __ salary DESC __________________
- Find the number of different properties viewed in April 2004.
SELECT __ count (distinct propert_no) FROM Viewing WHERE viewDate BETWEEN ‘1-Apr-04’ AND ’30-Apr-04’
- Find the minimum, maximum and average staff salary.
SELECT _ min(salary) ____, _ max(salary) _, _ avg(salary) _____ FROM Staff
- For each branch office with more than one member of staff, find the number of staff working in each branch and the sum of their salaries.
SELECT branchNo, _ count(staffno) _, __ sum(salary) ___ FROM Staff GROUP BY branchNo HAVING __ count(staffNo) >
- Find the list of all cities where there is both a branch office and a property
(SELECT city FROM Branch) ___ INTERSECT ________ (SELECT city FROM _ PropertyforRent __)
- Give all managers 5% increase to their salary
UPDATE __ staff _____________ SET __ salary=salary1.* WHERE position=’Manager’
- Delete all viewings that belong to property with property number PG4.
DELETE FROM __ viewing __________ WHERE _ propertyNo=’P64’ __
A- Consider the following relation schema for an airline database. customer(id, name, age, gender) onFlight(id, flightNo, flightDate) flightInfo(flightNo, fromCity, toCity, startTime, duration)
Assume all flights take place every day. Fill in the missing slots in each ofd the queries
below. Each slot is worth 2 pts, except the first one, which is worth 1 pt.
- Names of all customers above the age of 10
SELECT _______________name FROM customer WHERE ________________ age>
- Flights (flightNo, flightDate) on which there are at least two customers
SELECT f1.flightNo, f1.flightDate FROM onFlight as f1, onFlight as f WHERE f1.flightNo = f2.flightNo AND f1.flightDate=f2.flightDate AND __________________ f1.id <> f2.id
- Flights (flightNo, flightDate) on which there are at least two customers, as well as the
number of passengers on the flights
SELECT flightNo, flightDate, count(id) as howMany FROM onFlight GROUP BY _____________________ flightNo, flightDate HAVING _____________________ howMany>
- The number of passengers on flight “TK101” on “1/2/1999”
SELECT _________________ count(id ) FROM onFlight WHERE flightNo= “TK101” AND flightDate=“1/2/1999”
- The most popular destination (i.e. the city which received the most number of
travellers)
WITH city_tourists(toCity,HowMany) AS SELECT toCity, count(*) FROM onFlight natural inner join flightInfo GROUP BY toCity WITH mostTourist(HowMany) AS SELECT ___________________ max(HowMany) FROM ___________________ city_tourists SELECT toCity FROM ______________________________ city_tourists, mostTourist WHERE _____________________________ city_tourists.HowMany = mostTourist.HowMany
- How many passengers ever flew to Istanbul? If somebody travelled to Istanbul more
than one time, only one of those visits should be counted.
SELECT ___________________count (distinct id) FROM onFlight natural inner join flightInfo WHERE to_city = “Istanbul”
