Scribd Logo
Upload

Welcome to Scribd!

  • 98 views

    CHAPTER 5: More SQL: Complex Queries, Triggers, Views, and Schema Modification

    Uploaded by

    Gaziza Yestemirova
    Soulition of the fifth chapters ex

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd

    CHAPTER 5: More SQL: Complex Queries, Triggers, Views, and Schema Modification

    Uploaded by

    Gaziza Yestemirova
    98 views2 pages
    Soulition of the fifth chapters ex

    Original Title

    Elmasri 6e_ISM 05

    Copyright

    © © All Rights Reserved

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Soulition of the fifth chapters ex

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    98 views2 pages

    CHAPTER 5: More SQL: Complex Queries, Triggers, Views, and Schema Modification

    Uploaded by

    Gaziza Yestemirova
    Soulition of the fifth chapters ex

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    of 2

    Chapter 5: More SQL: Complex Queries, Triggers, Views, and Schema Modification

    CHAPTER 5: More SQL: Complex Queries, Triggers, Views, and Schema


    Modification
    Answers to Selected Exercises
    5.5 - Specify the following additional queries on the database of Figure 3.5 in SQL. Show
    the query results if applied to the database of Figure 3.6.
    (a) For each department whose average employee salary is more than $30,000, retrieve
    the department name and the number of employees working for that department.
    (b) Suppose we want the number of male employees in each department rather than all
    employees (as in Exercise 5.4a). Can we specify this query in SQL? Why or why not?
    Answers:
    (a) SELECT DNAME, COUNT (*)
    FROM DEPARTMENT, EMPLOYEE
    WHERE DNUMBER=DNO
    GROUP BY DNAME
    HAVING AVG (SALARY) > 30000

    Result:
    DNAME DNUMBER COUNT(*)
    Research 5 4
    Administration 4 3
    Headquarters 1 1
    (b) The query may still be specified in SQL by using a nested query as follows (not all
    implementations may support this type of query):
    SELECT DNAME, COUNT (*)
    FROM DEPARTMENT, EMPLOYEE
    WHERE DNUMBER=DNO AND SEX='M' AND DNO IN ( SELECT DNO
    FROM EMPLOYEE
    GROUP BY DNO
    HAVING AVG (SALARY) > 30000 )
    GROUP BY DNAME

    Result:
    DNAME DNUMBER COUNT(*)
    Research 5 3
    Administration 4 1
    Headquarters 1 1

    5.6 - Specify the following queries in SQL on the database schema of Figure 1.2.
    (a) Retrieve the names and major departments of all straight-A students (students who
    have a grade of A in all their courses).
    (b) Retrieve the names and major departments of all students who do not have any
    grade of A in any of their courses.

    Copyright 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.

    Chapter 5: More SQL: Complex Queries, Triggers, Views, and Schema Modification

    Answers:
    (a) SELECT Name, Major
    FROM STUDENT
    WHERE NOT EXISTS ( SELECT *
    FROM GRADE_REPORT
    WHERE StudentNumber= STUDENT.StudentNumber AND NOT(Grade='A'))

    (b) SELECT Name, Major


    FROM STUDENT
    WHERE NOT EXISTS ( SELECT *
    FROM GRADE_REPORT
    WHERE StudentNumber= STUDENT.StudentNumber AND Grade='A' )

    5.7 - In SQL, specify the following queries on the database specified in Figure 3.5 using the
    concept of nested queries and the concepts described in this chapter.
    a. Retrieve the names of all employees who work in the department that has the
    employee with the highest salary among all employees.
    b. Retrieve the names of all employees whose supervisors supervisor has '888665555'
    for Ssn.
    c. Retrieve the names of employees who make at least $10,000 more than the employee
    who is paid the least in the company.
    Answers:
    a) SELECT LNAME FROM EMPLOYEE WHERE DNO =
    ( SELECT DNO FROM EMPLOYEE WHERE SALARY =
    ( SELECT MAX(SALARY) FROM EMPLOYEE) )
    b) SELECT LNAME FROM EMPLOYEE WHERE SUPERSSN IN
    ( SELECT SSN FROM EMPLOYEE WHERE SUPERSSN = 888665555 )
    c) SELECT LNAME FROM EMPLOYEE WHERE SALARY >= 10000 +
    ( SELECT MIN(SALARY) FROM EMPLOYEE)

    5.8 No solution provided.


    5.9 No solution provided.

    Copyright 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.

    You might also like