sql_test1.html

LevSelector.com

New York

home > SQL_test1

SQL_test1.
Below there are 60+ problems (for 3 different databases) with answers.
The material is copied from the SQL Tutor.

Company Database:
Tables:
DEPARTMENT (DNAME, DNUMBER, MGR, MGRSTARTDATE)
EMPLOYEE (IRD, LNAME, MINIT, FNAME, BDATE, ADDRESS, SEX, SALARY, SUPERVISOR, DNO)
DEPT_LOCATIONS (DNUMBER, DLOCATION)
PROJECT (PNAME, PNUMBER, PLOCATION, DNUM)
WORKS_ON (EIRD, PNO, HOURS)
DEPENDENT (EIRD, DEPENDENT_NAME, SEX, BDATE, RELATIONSHIP)

Q1. List full details of all employees.
Q2. Retrieve the birthdate adn address of the employee whose name is John Smith.
Q3. Retrieve the name and address of all employees who work for the Research department.
Q4. For every project located in Stafford, list the project number, the controlling department number and the manager's last name, address and birthdate.
Q5. For each employee, retrieve the employee's first and last name and the first and last name of his or her immediate supervisor
Q6. Select all employees' IRDs .
Q7. Select all combinations of employee IRD and department name.
Q8. List all information about employees of department 5.
Q9. Retrieve the salary of every employee.
Q10. Retrieve the IRDs of all employees who work on the project number 1,2 or 3.
Q11. Find the names of employees whose salary is greater than the salary of all the employees in department 5.
Q12. Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee.
Q13. Retrieve the names of employees who have no dependents.
Q14. Retrieve the names of all employees who do not have supervisors.
Q15. Find the names of all employees whose salary is greater than 30000 and less than 50000
Q16. Retrieve the names of all employees whose address is in Houston,TX
Q17. Retrieve the names of all employees who were born during the 1950s.
Q18. Find the names of each employee and his or her supervisor
Q19. Show the names of all employees and the resulting salaries if only employees working on the 'ProductX' project are given a 10% raise.
Q20. Find the sum of salaries of all employees, the maximum salary, the minimum salary, and the average salary.
Q21. Retrieve the number of employees in the 'Research' department.
Q22. Retrieve the names of all employees who have more than two dependents.
Q23. For each department, retrieve the department number, the number of employees in the department, and their average salary.
Q24. For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on the project.
Q25. Retrieve a list of all employees names, department names and the names of projects they are working on, ordered by department (descending) and, within each department, alphabetically by last name, first name.

Movies Database:
Tables:
DIRECTOR (NUMBER, LNAME, FNAME, BORN, DIED)
MOVIE (NUMBER, TITLE, TYPE, AANOM, AAWON, YEAR, CRITICS, DIRECTOR)
STAR (LNAME, FNAME, NUMBER, BORN, DIED,CITY)
CUSTOMER (LNAME, FNAME, NUMBER, ADDRESS, RENTALS, BONUS, JDATE)
TAPE (CODE, MOVIE, PDATE, TIMES, CUSTOMER, HIREDATE)
STARS (MOVIE, STAR, ROLE)

Q26. List full details of all movies.
Q27. Produce a list of all movies, showing only the movie number, title and director number.
Q28. What is the name of customer number 124?
Q29. List the names of all directors born in or after 1920.
Q30. List the titles and numbers of all movies that have won at least one Academy Award and have been made in or after 1988.
Q31. List the titles of all comedies or dramas
Q32. List the titles of all movies that have a critics rating. Note that the fact that a movie has not been rated is encoded in the database as 'NR'.
Q33. Produce a list of customer names, numbers and bonuses, under the assumption that the BONUS attribute does not exists in the CUSTOMER table, but can be computed as one tenth of the RENTALS attribute.
Q34. Produce a list of star numbers of all the stars that acted in the movie number 20.
Q35. List the numbers and titles of all movies made between 1990 and 1993.
Q36. List the nubmers and titles of all movies whose type is COMEDY or DRAMA.
Q37. For all customers who live in Ilam, list their number and name.
Q38. Retrieve the names of all directors born during the 1950s.
Q39. List the names of all directors who are still living.
Q40. List the titles of all movies, arranged in descending order of the number of Academy Awards won.
Q41. List the numbers, names, addresses and join dates of all members. Sort the output by last name descending and by first name ascending.
Q42. How many movies won more than four Academy Awards.
Q43. Find how many comedies there are and how many AA they won.
Q44. Find the nubmer of movies in each category and the total of AA won in each of them. Show categories as well.
Q45. For each director, list the director's number and the total number of awards won by comedies he or she directed if the total is greater than 1.
Q46. List the numbers and names of all members who have rented more tapes than average.
Q47. List the titles of all movies directed by Stanley Kubrick.
Q48. Retrieve the titles of all movies directed by Stanley Kubrick.
Q49. Select all combinations of movie titles and tape codes.
Q50. List the names and addresses of all customers currently renting Mel Brooks' movies.
Q51. Find the nubmers and names of all directors who have directed at least one comedy.
Q52. List the names and numbers of all members. For those of them who are currently renting tapes, list the total number of tapes.
Q53. List the movie number and title for all movies that were nominated for more Academy Awards than any movie directed by Woody Allen.
Q54. Find the list of any pairs of stars who have the same first name.
Q55. List the tape numbers of all tapes that have been rented at least 10 times.
Q56. List the numbers, names and ages of all movie stars who are deceased.
Q57. Find the number and name of the youngest director who has directed at least one comedy.
Q58. For all directors who made more than 5 movies, list their number, names and thetotal number of movies.
••••-
Q60. Show the number of movies of each type made in 1980.
Q61. Find the name of the director who have directed the most movies. Show the number of movies as well.
Q62. Show types of movies for which there are more than 5 movies in the database. Order the results by decreasing number of movies. The number of movies in each category should be shown as a column names NO.
Q63. Find the name of the star who played Vronsky in the movie entitled 'Anna Karenina'.
Q64. Find the names of all directors who directed at least as many movies as the director number 0015.

Registration Database:
Tables:
VEHICLE_TYPE (make, model, power, no_pass, cap, cc)
VEHICLE (plates, year, eng_no, ch_no, type, make, model)
EMPLOYEE (fname, init, lname, IRD, sex, bdate, office, reg_org, sdate)
OWNER (dr_lic, IRD, fname, init, lname, address, bdate, sex, emp, phone)
OWNS (plates, ownerid, date,drr)
COLOR (plates, color)
REG_ORG (number, street, st_num. city, manager)
FIRST_REG (plates, emp, reg_org, reg_date, country, status, drr, amount)
REGISTRATION (PLATES, emp, reg_org, reg_date, amount)

Q59. List full details of all vehicles.
Q65. Get names and addresses of all owners from Christchurch who registered their vehicles during March 1996
Q66. Get the list of vehicles imported from japan since 1985 which had less than 3 owners in New Zealand, listing their plates, makes nad models.
Q67. Find how much money was collected in the organization 1352 on February 1997 for registration of private cars.

A1.
SELECT * FROM EMPLOYEE
••••••••••••••••••••
A2.
SELECT BDATE, ADDRESS
FROM EMPLOYEE
WHERE LNAME='Smith' AND FNAME='John'
••••••••••••••••••••
A3.
SELECT LNAME, FNAME, ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME='Research' AND DNUMBER=DNO
••••••••••••••••••••
A4.
SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUMBER=DNUM AND MGR=IRD AND PLOCATION='Stafford'
••••••••••••••••••••
A5.
SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
FROM EMPLOYEE E, EMPLOYEE S
WHERE E.SUPERVISOR=S.IRD
••••••••••••••••••••
A6.
SELECT IRD
FROM EMPLOYEE
••••••••••••••••••••
A7.
SELECT IRD, DNAME
FROM EMPLOYEE, DEPARTMENT
••••••••••••••••••••
A8.
SELECT *
FROM EMPLOYEE
WHERE DNO=5
••••••••••••••••••••
A9.
SELECT SALARY
FROM EMPLOYEE
••••••••••••••••••••
A10.
SELECT DISTINCT EIRD
FROM WORKS_ON
WHERE PNO IN (1, 2, 3)
••••••••••••••••••••
A11.
SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE SALARY > ALL (SELECT SALARY FROM EMPLOYEE WHERE DNO=5)
••••••••••••••••••••
A12.
SELECT E.LNAME, E.FNAME
FROM EMPLOYEE E
WHERE EXISTS (SELECT * FROM DEPENDENT WHERE E.IRD=EIRD AND SEX=E.SEX AND E.FNAME=DEPENDENT_NAME)
••••••••••••••••••••
A13.
SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE NOT EXISTS (SELECT * FROM DEPENDENT WHERE IRD=EIRD)
••••••••••••••••••••
A14.
SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE SUPERVISOR IS NULL
••••••••••••••••••••
A15.
SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE SALARY BETWEEN 30000 AND 50000
••••••••••••••••••••
A16.
SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE ADDRESS LIKE '%Houston,TX%'
••••••••••••••••••••
A17.
SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE BDATE LIKE '__5_______'
••••••••••••••••••••
A18.
SELECT E.LNAME AS EMPLOYEE_NAME, S.LNAME AS SUPERVISOR_NAME
FROM EMPLOYEE E, EMPLOYEE S
WHERE E.SUPERVISOR=S.IRD
••••••••••••••••••••
A19.
SELECT LNAME, FNAME, 1.1*SALARY
FROM EMPLOYEE, WORKS_ON, PROJECT
WHERE IRD=EIRD AND PNO=PNUMBER AND PNAME='ProductX'
••••••••••••••••••••
A20.
SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM EMPLOYEE
••••••••••••••••••••
A21.
SELECT COUNT(*)
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME='Research' AND DNO=DNUMBER
••••••••••••••••••••
A22.
SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE 2 < (SELECT COUNT(*) FROM DEPENDENT WHERE IRD=EIRD)
••••••••••••••••••••
A23.
SELECT DNO, COUNT(*), AVG(SALARY)
FROM EMPLOYEE
GROUP BY DNO
••••••••••••••••••••
A24.
SELECT PNUMBER, PNAME, COUNT(*)
FROM PROJECT, WORKS_ON
WHERE PNO=PNUMBER
GROUP BY PNUMBER, PNAMEHAVING COUNT(*)>2
••••••••••••••••••••
A25.
SELECT DNAME, LNAME, FNAME, PNAME
FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT
WHERE DNUMBER=DNO AND IRD=EIRD AND PNO=PNUMBER
ORDER BY DNAME DESC, LNAME, FNAME
••••••••••••••••••••
A26.
SELECT *
FROM movie
••••••••••••••••••••
A27.
SELECT NUMBER,TITLE,DIRECTOR
FROM MOVIE
••••••••••••••••••••
A28.
SELECT LNAME,FNAME
FROM CUSTOMER
WHERE NUMBER=124
••••••••••••••••••••
A29.
SELECT LNAME,FNAME
FROM DIRECTOR
WHERE BORN>=1920
••••••••••••••••••••
A30.
SELECT NUMBER,TITLE
FROM MOVIE
WHERE AAWON>=1 AND YEAR>=1988
••••••••••••••••••••
A31.
SELECT TITLE
FROM MOVIE
WHERE TYPE='comedy' OR TYPE='drama'
••••••••••••••••••••
A32.
SELECT TITLE
FROM MOVIE
WHERE NOT(CRITICS='NR')
••••••••••••••••••••
A33.
SELECT LNAME,FNAME,NUMBER,RENTALS/10 AS BONUS
FROM CUSTOMER
••••••••••••••••••••
A34.
SELECT DISTINCT STAR
FROM STARS
WHERE MOVIE=20
••••••••••••••••••••
A35.
SELECT TITLE,NUMBER
FROM MOVIE
WHERE YEAR BETWEEN 1990 AND 1993
••••••••••••••••••••
A36.
SELECT NUMBER,TITLE
FROM MOVIE
WHERE TYPE IN ('comedy','drama')
••••••••••••••••••••
A37.
SELECT LNAME,FNAME,NUMBER
FROM CUSTOMER
WHERE ADDRESS LIKE '%Ilam%'
••••••••••••••••••••
A38.
SELECT LNAME,FNAME
FROM DIRECTOR
WHERE BORN BETWEEN 1950 AND 1959
••••••••••••••••••••
A39.
SELECT LNAME, FNAME
FROM DIRECTOR
WHERE DIED IS NULL
••••••••••••••••••••
A40.
SELECT TITLE,AAWON
FROM MOVIE
ORDER BY AAWON DESC
••••••••••••••••••••
A41.
SELECT NUMBER,LNAME,FNAME,ADDRESS,JDATE
FROM CUSTOMER
ORDER BY LNAME DESC, FNAME ASC
••••••••••••••••••••
A42.
SELECT COUNT(*)
FROM MOVIE
WHERE AAWON>4
••••••••••••••••••••
A43.
SELECT COUNT(*), SUM(AAWON)
FROM MOVIE
WHERE TYPE='comedy'
••••••••••••••••••••
A44.
SELECT COUNT(*) AS COUNT, SUM(AAWON) AS SUM, TYPE
FROM MOVIE
GROUP BY TYPE
••••••••••••••••••••
A45.
SELECT DIRECTOR,SUM(AAWON)
FROM MOVIE
WHERE TYPE='comedy'
GROUP BY DIRECTOR
HAVING SUM(AAWON)>1
••••••••••••••••••••
A46.
SELECT NUMBER,FNAME,LNAME
FROM CUSTOMER
WHERE RENTALS > (SELECT AVG(RENTALS) FROM CUSTOMER)
••••••••••••••••••••
A47.
SELECT TITLE
FROM MOVIE
WHERE DIRECTOR=(SELECT NUMBER FROM DIRECTOR WHERE FNAME='Stanley' AND LNAME='Kubrick')
••••••••••••••••••••
A48.
SELECT
••••••••••••••••••••
A49.
SELECT TITLE,CODE
FROM MOVIE,TAPE
••••••••••••••••••••
A50.
SELECT C.LNAME,C.FNAME,ADDRESS
FROM CUSTOMER C, STAR S, STARS, TAPE
WHERE S.LNAME='Brooks' AND S.FNAME='Mel' AND S.NUMBER=STARS.STAR AND STARS.MOVIE=TAPE.MOVIE AND CUSTOMER=C.NUMBER
••••••••••••••••••••
A51.
SELECT DIRECTOR.NUMBER,LNAME,FNAME
FROM DIRECTOR,MOVIE
WHERE TYPE='comedy' AND MOVIE.DIRECTOR=DIRECTOR.NUMBER
••••••••••••••••••••
A52.
SELECT NUMBER,LNAME,FNAME,COUNT(*) AS NO
FROM CUSTOMER LEFT JOIN TAPE ON CUSTOMER.NUMBER=CUSTOMER
GROUP BY NUMBER,LNAME,FNAME
••••••••••••••••••••
A53.
SELECT NUMBER,TITLE
FROM MOVIE
WHERE AANOM > ALL (SELECT AANOM FROM MOVIE,DIRECTOR WHERE DIRECTOR=DIRECTOR.NUMBER AND LNAME='Allen' AND FNAME='Woody')
••••••••••••••••••••
A54.
SELECT S1.FNAME,S1.LNAME,S2.LNAME
FROM STAR S1, STAR S2
WHERE S1.FNAME=S2.FNAME AND S1.LNAME<>S2.LNAME
••••••••••••••••••••
A55.
SELECT CODE
FROM TAPE
WHERE TIMES>=10
••••••••••••••••••••
A56.
SELECT NUMBER, FNAME, LNAME, DIED-BORN
FROM STAR
WHERE DIED IS NOT NULL AND BORN IS NOT NULL
••••••••••••••••••••
A57.
SELECT DIRECTOR.NUMBER, FNAME, LNAME
FROM DIRECTOR JOIN MOVIE ON DIRECTOR.NUMBER=MOVIE.DIRECTOR
WHERE TYPE='comedy' AND BORN >= ALL(SELECT BORN FROM DIRECTOR,MOVIE WHERE TYPE='comedy' AND DIRECTOR.NUMBER = DIRECTOR)
••••••••••••••••••••
A58.
SELECT DIRECTOR.NUMBER,FNAME,LNAME,COUNT(*)
FROM MOVIE JOIN DIRECTOR ON DIRECTOR=DIRECTOR.NUMBER
GROUP BY DIRECTOR.NUMBER,LNAME,FNAME
HAVING COUNT(*)>5
••••••••••••••••••••
A59.
SELECT *
FROM VEHICLE
••••••••••••••••••••
A60.
SELECT type,count(*)
FROM movie
WHERE year=1980
GROUP BY type
••••••••••••••••••••
A61.
SELECT lname,fname,count(*)
FROM director join movie on director=director.number
GROUP BY lname,fname
HAVING count(*) >= all (select count(*) from director,movie where director=director.number group by director)
••••••••••••••••••••
A62.
SELECT TYPE, COUNT(*) as NO
FROM MOVIE
GROUP BY TYPE
HAVING COUNT(*)>5
ORDER BY NO DESC
••••••••••••••••••••
A63.
SELECT LNAME,FNAME
FROM MOVIE,STAR,STARS
WHERE ROLE='Vronsky'
AND TITLE='Anna Karenina'
AND STAR.NUMBER=STARS.STAR
AND MOVIE.NUMBER=STARS.MOVIE
••••••••••••••••••••
A64.
SELECT LNAME,FNAME
FROM DIRECTOR JOIN MOVIE ON DIRECTOR.NUMBER=DIRECTOR
GROUP BY DIRECTOR.NUMBER,LNAME,FNAMEHAVING COUNT(*)>=(SELECT COUNT(*) FROM MOVIE WHERE DIRECTOR=0015)
••••••••••••••••••••
A65.
SELECT FNAME,INIT,LNAME,ADDRESS
FROM REGISTRATION,OWNER,OWNS
WHERE OWNERID=DR_LIC
   AND OWNS.PLATES=REGISTRATION.PLATES
   AND REG_DATE BETWEEN DATE('01/03/1996') AND DATE('31/03/1996')
   AND ADDRESS LIKE '%Christchurch%'
••••••••••••••••••••
A66.
SELECT VEHICLE.PLATES, MAKE, MODEL
FROM VEHICLE, REGISTRATION
WHERE COUNTRY='Japan'
AND REG_DATE>DATE('01/01/1985')
AND VEHICLE.PLATES=REGISTRATION.PLATES
AND 3>(SELECT COUNT(*) FROM OWNS WHERE VEHICLE.PLATES=OWNS.PLATES)
••••••••••••••••••••
A67.
SELECT SUM(AMOUNT)
FROM REGISTRATION, VEHICLE
WHERE REG_ORG=1352
AND REG_DATE=DATE('15/02/1997')
AND TYPE='p'
AND REGISTRATION.PLATES=VEHICLE.PLATES
••••••••••••••••••••