Download Null value • Query language • SQL query structure and more Lecture notes Database Programming in PDF only on Docsity!
Practice Exercises 105
• Null value
• Query language
• SQL query structure
◦ (^) select clause ◦ (^) from clause ◦ (^) where clause
• Natural join operation
• as^ clause
• order^ by clause
• Correlation name (correlation vari-
able, tuple variable)
• Set operations
◦ (^) union ◦ (^) intersect ◦ (^) except
• Null values
◦ (^) Truth value “unknown”
• Aggregate functions
◦ (^) avg, min, max, sum, count ◦ (^) group by ◦ (^) having
• Nested subqueries
• Set comparisons
◦ (^) {<, <=, >, >=} { some, all } ◦ (^) exists ◦ (^) unique
• lateral clause
• with clause
• Scalar subquery
• Database modification
◦ (^) Deletion ◦ (^) Insertion ◦ (^) Updating
Practice Exercises
3.1 Write the following queries in SQL, using the university schema. (We sug- gest you actually run these queries on a database, using the sample data that we provide on the Web site of the book, db-book.com. Instructions for setting up a database, and loading sample data, are provided on the above Web site.) a. Find the titles of courses in the Comp. Sci. department that have 3 credits. b. Find the ID s of all students who were taught by an instructor named Einstein; make sure there are no duplicates in the result. c. Find the highest salary of any instructor. d. Find all instructors earning the highest salary (there may be more than one with the same salary). e. Find the enrollment of each section that was offered in Autumn 2009. f. Find the maximum enrollment, across all sections, in Autumn 2009. g. Find the sections that had the maximum enrollment in Autumn 2009.
106 Chapter 3 Introduction to SQL
person ( driver id , name , address ) car ( license , model , year ) accident ( report number , date , location ) owns ( driver id , license ) participated ( report number , license , driver id , damage amount )
Figure 3.18 Insurance database for Exercises 3.4 and 3.14.
3.2 Suppose you are given a relation grade points ( grade , points ), which provides a conversion from letter grades in the takes relation to numeric scores; for example an “A” grade could be specified to correspond to 4 points, an “A−” to 3.7 points, a “B+” to 3.3 points, a “B” to 3 points, and so on. The grade points earned by a student for a course offering (section) is defined as the number of credits for the course multiplied by the numeric points for the grade that the student received. Given the above relation, and our university schema, write each of the following queries in SQL. You can assume for simplicity that no takes tuple has the null value for grade. a. Find the total grade-points earned by the student with ID 12345, across all courses taken by the student. b. Find the grade-point average ( GPA ) for the above student, that is, the total grade-points divided by the total credits for the associated courses. c. Find the ID and the grade-point average of every student.
3.3 Write the following inserts, deletes or updates in SQL, using the university schema. a. Increase the salary of each instructor in the Comp. Sci. department by 10%. b. Delete all courses that have never been offered (that is, do not occur in the section relation). c. Insert every student whose tot cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000.
3.4 Consider the insurance database of Figure 3.18, where the primary keys are underlined. Construct the following SQL queries for this relational database. a. Find the total number of people who owned cars that were involved in accidents in 2009. b. Add a new accident to the database; assume any values for required attributes. c. Delete the Mazda belonging to “John Smith”.
108 Chapter 3 Introduction to SQL
employee ( employee name , street , city ) works ( employee name , company name , salary ) company ( company name , city ) manages ( employee name , manager name )
Figure 3.20 Employee database for Exercises 3.9, 3.10, 3.16, 3.17, and 3.20.
b. Find the names, street addresses, and cities of residence of all em- ployees who work for “First Bank Corporation” and earn more than $10,000. c. Find all employees in the database who do not work for “First Bank Corporation”. d. Find all employees in the database who earn more than each employee of “Small Bank Corporation”. e. Assume that the companies may be located in several cities. Find all companies located in every city in which “Small Bank Corporation” is located. f. Find the company that has the most employees. g. Find those companies whose employees earn a higher salary, on av- erage, than the average salary at “First Bank Corporation”.
3.10 Consider the relational database of Figure 3.20. Give an expression in SQL for each of the following queries.
a. Modify the database so that “Jones” now lives in “Newtown”. b. Give all managers of “First Bank Corporation” a 10 percent raise unless the salary becomes greater than $100,000; in such cases, give only a 3 percent raise.
Exercises
3.11 Write the following queries in SQL, using the university schema.
a. Find the names of all students who have taken at least one Comp. Sci. course; make sure there are no duplicate names in the result. b. Find the ID s and names of all students who have not taken any course offering before Spring 2009. c. For each department, find the maximum salary of instructors in that department. You may assume that every department has at least one instructor. d. Find the lowest, across all departments, of the per-department maxi- mum salary computed by the preceding query.
Exercises 109
3.12 Write the following queries in SQL, using the university schema.
a. Create a new course “CS-001”, titled “Weekly Seminar”, with 0 credits. b. Create a section of this course in Autumn 2009, with sec id of 1. c. Enroll every student in the Comp. Sci. department in the above sec- tion. d. Delete enrollments in the above section where the student’s name is Chavez. e. Delete the course CS-001. What will happen if you run this delete statement without first deleting offerings (sections) of this course. f. Delete all takes tuples corresponding to any section of any course with the word “database” as a part of the title; ignore case when matching the word with the title.
3.13 Write SQL DDL corresponding to the schema in Figure 3.18. Make any reasonable assumptions about data types, and be sure to declare primary and foreign keys.
3.14 Consider the insurance database of Figure 3.18, where the primary keys are underlined. Construct the following SQL queries for this relational database. a. Find the number of accidents in which the cars belonging to “John Smith” were involved. b. Update the damage amount for the car with the license number “AABB2000” in the accident with report number “AR2197” to $3000.
3.15 Consider the bank database of Figure 3.19, where the primary keys are un- derlined. Construct the following SQL queries for this relational database. a. Find all customers who have an account at all the branches located in “Brooklyn”. b. Find out the total sum of all loan amounts in the bank. c. Find the names of all branches that have assets greater than those of at least one branch located in “Brooklyn”.
3.16 Consider the employee database of Figure 3.20, where the primary keys are underlined. Give an expression in SQL for each of the following queries. a. Find the names of all employees who work for “First Bank Corpora- tion”. b. Find all employees in the database who live in the same cities as the companies for which they work. c. Find all employees in the database who live in the same cities and on the same streets as do their managers.
Tools 111
3.23 Consider the query:
select course id , semester , year , sec id , avg ( tot cred ) from takes natural join student where year = 2009 group by course id , semester , year , sec id having count ( ID ) >= 2;
Explain why joining section as well in the from clause would not change the result. 3.24 Consider the query:
with dept total ( dept name , value ) as ( select dept name , sum ( salary ) from instructor group by dept name ), dept total avg ( value ) as ( select avg ( value ) from dept total ) select dept name from dept total , dept total avg where dept total.value >= dept total avg.value ;
Rewrite this query without using the with construct.
Tools
A number of relational database systems are available commercially, including IBM DB2 , IBM Informix, Oracle, Sybase, and Microsoft SQL Server. In addition several database systems can be downloaded and used free of charge, including Postgre SQL, My SQL (free except for certain kinds of commercial use), and Oracle Express edition. Most database systems provide a command line interface for submitting SQL commands. In addition, most databases also provide graphical user interfaces (GUIs), which simplify the task of browsing the database, creating and submitting queries, and administering the database. Commercial IDEs for SQLthat work across multiple database platforms, include Embarcadero’s RAD Studio and Aqua Data Studio. For Postgre SQL, the pgAdmin tool provides GUI functionality, while for My SQL, phpMyAdmin provides GUI functionality. The NetBeans IDE provides a GUI front end that works with a number of different databases, but with limited functional- ity, while the Eclipse IDE supports similar functionality through several different plugins such as the Data Tools Platform ( DTP) and JBuilder. SQL schema definitions and sample data for the university schema are pro- vided on the Web site for this book, db-book.com. The Web site also contains
