Scribd
Upload
172 views10 pages

SQL Data Manipulation Techniques Explained

This document contains review questions and exercises about SQL data manipulation statements. It describes the basic DML statements (SELECT, INSERT, UPDATE, DELETE) and discusses the importance and application of the WHERE clause. It also explains the function of clauses in the SELECT statement and restrictions on their use, as well as how to combine results from multiple queries using operators like UNION, INTERSECT, and EXCEPT. The exercises demonstrate performing simple and aggregate queries, using subqueries and joins, and grouping and ordering results.

Uploaded by

Jawad Ali
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
172 views10 pages

SQL Data Manipulation Techniques Explained

This document contains review questions and exercises about SQL data manipulation statements. It describes the basic DML statements (SELECT, INSERT, UPDATE, DELETE) and discusses the importance and application of the WHERE clause. It also explains the function of clauses in the SELECT statement and restrictions on their use, as well as how to combine results from multiple queries using operators like UNION, INTERSECT, and EXCEPT. The exercises demonstrate performing simple and aggregate queries, using subqueries and joins, and grouping and ordering results.

Uploaded by

Jawad Ali
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Chapter 6 SQL: Data Manipulation

Review Questions

6.1 Briefly describe the four basic SQL DML statements and explain their use.

Section 6.3 describes the usage of basic data manipulation SQL statements; SELECT INSERT, UPDATE and
DELETE.

6.2 Explain the importance and application of the WHERE clause in the UPDATE and DELETE statements.

Application

 Optionally used in the UPDATE and DELETE statements.


 Used when update or delete is done to some few rows
 Not used if the updates is to reflect all the available rows

Importance

 It controls where updates needs to be effected


 Protects data from being changed and deleted unnecessarily
 Enforces data quality

6.3 Explain the function of each of the clauses in the SELECT statement. What restrictions are imposed on these
clauses?

FROM Specifies the table or tables to be used.


WHERE Filters the rows subject to some condition.
GROUP BY Forms groups of rows with the same column value.
HAVING Filters the groups subject to some condition.
SELECT Specifies which columns are to appear in the output.
ORDER BY Specifies the order of the output.

If the SELECT list includes an aggregate function and no GROUP BY clause is being used to group data
together, then no item in the SELECT list can include any reference to a column unless that column is the
argument to an aggregate function.

When GROUP BY is used, each item in the SELECT list must be single-valued per group. Further, the
SELECT clause may only contain:

 Column names.
 Aggregate functions.
 Constants.
 An expression involving combinations of the above.

All column names in the SELECT list must appear in the GROUP BY clause unless the name is used only in an
aggregate function.

6.4 What restrictions apply to the use of the aggregate functions within the SELECT statement? How do nulls affect
the aggregate functions?

An aggregate function can be used only in the SELECT list and in the HAVING clause.

Apart from COUNT(*), each function eliminates nulls first and operates only on the remaining non-null values.
COUNT(*) counts all the rows of a table, regardless of whether nulls or duplicate values occur.

6.5 How can results from two SQL queries be combined? Differentiate how the INTERSECT and EXCEPT
commands work.

There are number of relational operators that can be used to combine results of two queries. Some of them are
UNION, INTERSECT, and EXCEPT. For the two results to be semanticaly combined, order and number of
columns from both results should be identical, and the combining columns should be of the same domain.
The difference between the two is well explained in section 6.3.9 where the intersect returns the rows that are
common to both result sets, and except returns rows that are available in the first result set and not in the second
result set.

6.6 Differentiate between the three types of subqueries. Why is it important to understand the nature of a subquery
result before you write an SQL statement?

The subquery types are scalar, row, and table subquery. The main difference is on the nature of results they
return; scalar subquery returns a single column and single row that is a single value, row subquery returns
multiple columns and single row, while table subquery returns multiple columns and multiple rows. It is so
import to understand the nature of subquery result so that you can make the best choice about the operator to be
used

Exercises

For the Exercises 6.7 – 6.28, use the Hotel schema defined at the start of the Exercises at the end of Chapter 3.

Simple Queries

6.7 List full details of all hotels.

SELECT * FROM Hotel;

6.8 List full details of all hotels in London.


SELECT * FROM Hotel WHERE city = ‘London’;

6.9 List the names and addresses of all guests in London, alphabetically ordered by name.

SELECT guestName, guestAddress FROM Guest WHERE address LIKE ‘%London%’


ORDER BY guestName;

Strictly speaking, this would also find rows with an address like: ‘10 London Avenue, New York’.

6.10 List all double or family rooms with a price below £40.00 per night, in ascending order of price.

SELECT * FROM Room WHERE price < 40 AND type IN (‘D’, ‘F’)
ORDER BY price;

(Note, ASC is the default setting).

6.11 List the bookings for which no dateTo has been specified.

SELECT * FROM Booking WHERE dateTo IS NULL;

Aggregate Functions

6.12 How many hotels are there?

SELECT COUNT(*) FROM Hotel;

6.13 What is the average price of a room?

SELECT AVG(price) FROM Room;

6.14 What is the total revenue per night from all double rooms?

SELECT SUM(price) FROM Room WHERE type = ‘D’;

6.15 How many different guests have made bookings for August?

SELECT COUNT(DISTINCT guestNo) FROM Booking


WHERE (dateFrom <= DATE’2004-08-01’ AND dateTo >= DATE’2004-08-01’) OR
(dateFrom >= DATE’2004-08-01’ AND dateFrom <= DATE’2004-08-31’);

Subqueries and Joins


6.16 List the price and type of all rooms at the Grosvenor Hotel.

SELECT price, type FROM Room


WHERE hotelNo =
(SELECT hotelNo FROM Hotel
WHERE hotelName = ‘Grosvenor Hotel’);

6.17 List all guests currently staying at the Grosvenor Hotel.

SELECT * FROM Guest


WHERE guestNo =
(SELECT guestNo FROM Booking
WHERE dateFrom <= CURRENT_DATE AND
dateTo >= CURRENT_DATE AND
hotelNo =
(SELECT hotelNo FROM Hotel
WHERE hotelName = ‘Grosvenor Hotel’));

6.18 List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the
room is occupied.

SELECT r.* FROM Room r LEFT JOIN


(SELECT [Link], [Link], [Link] FROM Guest g, Booking b, Hotel h
WHERE [Link] = [Link] AND [Link] = [Link] AND
hotelName= ‘Grosvenor Hotel’ AND
dateFrom <= CURRENT_DATE AND
dateTo >= CURRENT_DATE) AS XXX
ON [Link] = [Link] AND [Link] = [Link];

6.19 What is the total income from bookings for the Grosvenor Hotel today?

SELECT SUM(price) FROM Booking b, Room r, Hotel h


WHERE (dateFrom <= CURRENT_DATE AND
dateTo >= CURRENT_DATE) AND
[Link] = [Link] AND [Link] = [Link] AND
hotelName = ‘Grosvenor Hotel’;

6.20 List the rooms that are currently unoccupied at the Grosvenor Hotel.

SELECT * FROM Room r


WHERE roomNo NOT IN
(SELECT roomNo FROM Booking b, Hotel h
WHERE (dateFrom <= CURRENT_DATE AND
dateTo >= CURRENT_DATE) AND
[Link] = [Link] AND hotelName = ‘Grosvenor Hotel’);

6.21 What is the lost income from unoccupied rooms at the Grosvenor Hotel?

SELECT SUM(price) FROM Room r


WHERE roomNo NOT IN
(SELECT roomNo FROM Booking b, Hotel h
WHERE (dateFrom <= CURRENT_DATE AND
dateTo >= CURRENT_DATE) AND
[Link] = [Link] AND hotelName = ‘Grosvenor Hotel’);

Grouping

6.22 List the number of rooms in each hotel.

SELECT hotelNo, COUNT(roomNo) AS count FROM Room


GROUP BY hotelNo;

6.23 List the number of rooms in each hotel in London.

SELECT hotelNo, COUNT(roomNo) AS count FROM Room r, Hotel h


WHERE [Link] = [Link] AND city = ‘London’
GROUP BY hotelNo;

6.24 What is the average number of bookings for each hotel in August?

SELECT AVG(X)
FROM ( SELECT hotelNo, COUNT(hotelNo) AS X
FROM Booking b
WHERE (dateFrom <= DATE’2004-08-01’ AND
dateTo >= DATE’2004-08-01’) OR
(dateFrom >= DATE’2004-08-01’ AND
dateFrom <= DATE’2004-08-31’)
GROUP BY hotelNo);

Yes - this is legal in SQL-92!

6.25 What is the most commonly booked room type for each hotel in London?

SELECT MAX(X)
FROM ( SELECT type, COUNT(type) AS X
FROM Booking b, Hotel h, Room r
WHERE [Link] = [Link] AND [Link] = [Link] AND
city = ‘London’
GROUP BY type);

6.26 What is the lost income from unoccupied rooms at each hotel today?

SELECT hotelNo, SUM(price) FROM Room r


WHERE roomNo NOT IN
(SELECT roomNo FROM Booking b, Hotel h
WHERE (dateFrom <= CURRENT_DATE AND
dateTo >= CURRENT_DATE) AND
[Link] = [Link])
GROUP BY hotelNo;

Populating Tables

6.27 Insert records into each of these tables.

INSERT INTO Hotel


VALUES (‘H111’, ‘Grosvenor Hotel’, ‘London’);

INSERT INTO Room


VALUES (‘1’, ‘H111’, ‘S’, 72.00);
INSERT INTO Guest
VALUES (‘G111’, ‘John Smith’, ‘London’);
INSERT INTO Booking
VALUES (‘H111’, ‘G111’, DATE’2005-01-01’, DATE’2005-01-02’, ‘1’);

6.28 Update the price of all rooms by 5%.

UPDATE Room SET price = price*1.05;

General

6.29 Investigate the SQL dialect on any DBMS that you are currently using. Determine the compliance of the DBMS
with the ISO standard. Investigate the functionality of any extensions the DBMS supports. Are there any
functions not supported?

This is a small student project, the result of which is dependent on the dialect of SQL being used.

6.30 Demonstrate that queries written using the UNION operator can be rewritten using the OR operator to
produce the same result.
Hint: students should explain a query that can be written in two ways by using UNION and OR operators.

6.31 Apply the syntax for inserting data into a table.

Description of the data insert can be found in section 6.3.10. Studenst should simulate data entry by using the
provided syntax

Case Study 2

For Exercises 6.32–6.40, use the Projects schema defined in the Exercises at the end of Chapter 5.

6.32 List all employees from BRICS countries in alphabetical order of surname.

SELECT * FROM Employee WHERE address LIKE ‘%Brazil%’ OR address LIKE ‘%Rusia%’ OR
address LIKE ‘%India%’ OR address LIKE ‘%China%’ OR address LIKE ‘%South Africa%’
ORDER BY lName, fName;

6.33 List all the details of employees born between 1980--90.

SELECT * FROM Employee WHERE DOB like ‘%198%’;

6.34 List all managers who are female in alphabetical order of surname, and then first name.

SELECT * FROM Employee where position=‘Manager’ and sex=‘F’ ORDER BY lName, fName;

6.35 Remove all projects that are managed by the planning department.

DELETE FROM Project p


WHERE projNo IN (SELECT [Link]
FROM project p, workson w
WHERE [Link]=[Link]);

6.36 Assume the planning department is going to be merged with the IT department. Update employee records
to reflect the proposed change.

UPDATE employee
SET deptNO=”IT”
WHERE deptNo=”PL”;

6.37 Using the UNION command, list all projects that are managed by the IT and the HR department.

SELECT * FROM PROJECT


WHERE deptNo=”IT”
UNION
SELECT * FROM PROJECT
WHERE deptNo=”HR”;

6.38 Produce a report of the total hours worked by each female employee, arranged by department number and
alphabetically by employee surname within each department.
SELECT [Link], [Link], hoursWorked
FROM WorksOn w, Employee e, Department d
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link]=’F’
ORDER by [Link], [Link];

6.39 Remove all project from the database on which no employees worked.

DELETE FROM Project p


WHERE projNo IN (SELECT [Link]
FROM project p, workson w
WHERE [Link]=[Link]);

6.40 List the total number of employees in each department for those departments with more than 10 employees.
Create an appropriate heading for the columns of the results table.

SELECT COUNT(empNo) AS empCount, deptNo


FROM Employee
GROUP BY deptNo
HAVING COUNT(empNo) > 10;

Case Study 3
For Exercises 6.41–6.54, use the Library schema defined in the Exercises at the end of Chapter 5.

6.41 List all book titles.

SELECT title FROM Book;

6.42 List all borrower details.

SELECT * FROM Borrower;

6.43 List all books titles published between 2010 and 2014.

SELECT *
FROM book
WHERE year BETWEEN ‘2010’ AND ‘2014’;

6.44 Remove all books published before 1950 from the database.
DELETE FROM book
WHERE year < ‘1950’;
6.45 List all book titles that have never been borrowed by any borrower.

SELECT *
FROM BOOK
WHERE ISBN NOT IN (SELECT ISBN
FROM BOOKCOPY C, BOOKLOAN L
WHERE [Link]=[Link]);
6.46 List all book titles that contain the word ‘database’ and are available for loan.

SELECT [Link], [Link], [Link], [Link], [Link]


FROM book b, bookcopy c
WHERE [Link]=[Link] AND [Link] like ‘%database%’ AND [Link]=’yes’;
6.47 List the names of borrowers with overdue books.

SELECT borrowerName
From Borrower bw, BookLoan bl
WHERE [Link] = [Link] and dateDue > today’s date;

6.48 How many copies of each book title are there?

SELECT [Link], count ([Link]) AS numberOfcopies


FROM book b, bookcopy c
WHERE [Link]=[Link]
Group by [Link];

6.49 How many copies of ISBN “0-321-52306-7” are currently available?

SELECT COUNT(copyNo)
FROM BookCopy
WHERE available = ‘Y’ AND ISBN = ‘0-321-52306-7’;

6.50 How many times has the book with ISBN “0-321-52306-7” been borrowed?
SELECT COUNT(*)
FROM BookCopy bc, BookLoan bl
WHERE [Link] = [Link] AND ISBN = ‘0-321-52306-7’;

6.51 Produce a report of book titles that have been borrowed by “Peter Bloomfield”.

SELECT DISTINCT title


FROM Borrower bw, Book b, BookCopy bc, BookLoan bl
WHERE [Link] = [Link] AND [Link] = [Link]
AND [Link] = [Link] AND borrowerName = ‘Peter Bloomfield’;

6.52 For each book title with more than 3 copies, list the names of library members who have borrowed them.

SELECT title, borrowerName


FROM Borrower bw, Book b, BookCopy bc, BookLoan bl
WHERE [Link] = [Link] AND [Link] = [Link]
AND [Link] = [Link] AND EXISTS
(SELECT ISBN, COUNT([Link])
FROM BookCopy bc1
WHERE [Link] = [Link]
GROUP BY [Link]
HAVING COUNT([Link]) > 3);

6.53 Produce a report with the details of borrowers who currently have books overdue.

SELECT borrowerName, borrowerAddress


From Borrower bw, BookLoan bl
WHERE [Link] = [Link] and dateDue > today’s date;

6.54 Produce a report detailing how many times each book title has been borrowed.

SELECT ISBN, COUNT(*)


FROM BookCopy bc, BookLoan bl
WHERE [Link] = [Link]
GROUP BY ISBN;

Common questions

Powered by AI

Null values have a significant impact on aggregate functions. Most aggregate functions, except COUNT(*), automatically disregard nulls and operate only on non-null values. For instance, SUM and AVG will not include nulls in their calculations. However, COUNT(*) includes all rows, nulls included, in its count, thereby differentiating the behavior of this function from the others .

Understanding the type of result a subquery will return is crucial because it guides how the subquery will be incorporated into the main query and which operators can be used. Scalar subqueries return a single value; hence they can be used wherever a single value is accepted. Row subqueries return a single row with multiple columns; they fit in predicates such as IN clauses. Table subqueries that return multiple rows could be used with EXISTS, IN, or joins. Using a subquery type inappropriately can lead to errors or unexpected results in SQL statements .

The WHERE clause is essential in the UPDATE and DELETE statements because it specifies the conditions that must be met for a row to be affected by the statement. This clause ensures that only specific rows are updated or deleted, thereby preventing unwanted changes to the entire table. It protects data from unnecessary modifications and upholds data integrity and quality by enforcing precise conditions under which updates or deletions occur .

SQL dialects may have varying compatibility with the ISO SQL standard because each Database Management System (DBMS) could implement proprietary extensions and omit certain standard functions. Some DBMS might offer advanced functions like specific analytical, spatial, or machine-learning capabilities, which aren't standardized. To assess whether a DBMS is compliant, one would review the availability of standardized features and any non-standard extensions that differentiate the platform .

The four basic SQL DML (Data Manipulation Language) statements are SELECT, INSERT, UPDATE, and DELETE. The SELECT statement is used to query data from a database. The INSERT statement is used to add new rows of data to a table. UPDATE is used to modify existing data within a table, and DELETE is used to remove existing rows from a table. These statements are critical for managing and manipulating database content efficiently .

COUNT(*) is unique because, unlike other aggregate functions, it counts all rows in a result set, including those with null values. Other aggregate functions like AVG or SUM disregard nulls and operate solely on non-null values. This behavior of COUNT(*) makes it distinct in handling null entries, enabling a comprehensive count of all data entries irrespective of their null status .

INTERSECT and EXCEPT are set operations to combine query results. INTERSECT retrieves rows common to both query results, effectively providing the intersection of the two data sets. EXCEPT returns rows from the first query result that don't exist in the second query result, thereby excluding overlapping data present in both query results. Both operations require the queries to have the same number and order of columns and identical data types .

The SQL statement used could be 'UPDATE employee SET deptNO='IT' WHERE deptNo='PL''; this changes the department number from 'PL' to 'IT' for all employees in the 'PL' department, reflecting a merger. Precision is necessary to ensure only relevant rows are updated without affecting unintended entries, maintaining data accuracy, and ensuring the system's logical structure aligns with organizational changes .

Students can illustrate the replaceability of the UNION operator by rewriting a SQL query to use an OR operator that achieves the same result. For instance, if two tables contain similar columns, a UNION query combining data from both sources can be transformed into a SELECT query using OR logic in the WHERE clause to incorporate elements from both tables based on the conditions that would be stipulated in separate queries combined via UNION .

In a SELECT statement, the FROM clause specifies the tables involved. The WHERE clause filters rows based on conditions. GROUP BY groups rows with identical values. HAVING filters the grouped data. SELECT decides column output. ORDER BY sorts the output. Restrictions include: aggregate functions without a GROUP BY can't include non-aggregated columns; if GROUP BY is used, only aggregated or grouped columns may appear in SELECT unless part of an aggregate function; all column names in SELECT must appear in GROUP BY unless used in an aggregate function .

You might also like