Selectorweb.com New York
home > SQL_test1 Email

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