More SQL 4th week of classes
Go to GROUP BY, LIKE condition, NULL condition, SUBQUERIES, ALIASES, EXISTS, UPDATE, DELETE, INSERT, VIEWS
Built-In Functions
Q1. Is there a way to get the sum or average or
the maximum (minimum) of a column of values in a given table?
A1. SQL has the following function built-in:
SUM - sums
the values in a specified column
MAX -
returns the largest value in a specified column
MIN -
returns the smallest value in a specified column
AVG -
returns the average of the values in a specified column
COUNT -
counts the number of values in a specified column
Q2. How would I get the total number of suppliers in Paris?
A2. SELECT COUNT (sNo)
FROM S
WHERE city = 'Paris';
Q3. How would I get the total number of parts supplied by supplier S1?
A3. Here we are summing the qty column in spj and not
counting.
SELECT SUM (QTY)
FROM SPJ
WHERE sNo = 'S1';
Q4. How would I find the smallest quantity ever shipped?
A4. SELECT MIN (qty)
FROM SPJ;
Go to top
Q5. How would I inquire about subgroups of
records? For example, find the total quantity supplied for each part,
not just some specific part like P2.
A5. SELECT pNo, SUM(qty)
FROM SPJ
GROUP BY
pNo;
This query will sum the qty of each group of
records that have the same pNo in the SPJ table.
Q6. How about grouping by a combination of
records. Find the total quantity of parts shipped for each supplier
shipping to each job.
A6. SELECT sNo, jNo, SUM(qty)
FROM SPJ
GROUP BY
sNO, jNo;
Note that in the SPJ table the rows that have the
same pair sNo, jNo values form a subgroup of records in table SPJ.
Q7. What if I wanted to know which part numbers were shipped more than twice?
A7. Here we are imposing a condition on each subgroup, i.e. we want those subgroups that have more than 2 records in them and grouped by pNo.
SELECT pNo
FROM SPJ
GROUP BY pNo
HAVING COUNT (*) > 2;
HAVING is to GROUPS what WHERE is to ROWS. HAVING always goes with GROUP BY it is used to eliminate groups just as WHERE is used to eliminate rows.
Expressions in HAVING clause such as COUNT (*) must be single valued. The * inside COUNT means to count rows that satisfy the HAVING condition.
Go to top
Q8. How do you retrieve records based on some word
buried in some text string? For example retrieve all records whose name starts
with Sm letters.
A8. SELECT *
FROM S
WHERE
S.sName LIKE 'Sm*';
Q9. Would this be a good way to look for certain skills in a resume if the resume was a long text field in a Person table? Suppose I wanted to know which candidates knew UNIX and Java?
A9. You would try something like this:
SELECT *
FROM Person
WHERE resume LIKE '*UNIX*'
AND resume LIKE '*JAVA*' ;
Assuming there is a Person table and that table has a column named resume.
If you wanted either UNIX or JAVA then the AND would be replaced by OR.
Go to top
Q10. How would I retrieve records that have value
NULL? Say I want records that do not have a NULL City value in the S table?
A10. SELECT *
FROM S
WHERE
City IS NOT NULL;
Note that City <> NULL or City = NULL will
not work. It's City IS NULL
or City IS NOT NULL.
Go to top
Q11. Is there another way to retrieve information besides JOINS where more than one table is involved? For example, get supplier names who supply to Job J3.
A11. Yes, with nested queries.
SELECT sName
FROM S
WHERE sNO IN
(SELECT sNO
FROM SPJ
WHERE jNo = 'J3') ;
Note that the column in the WHERE clause must match the column in the SELECT clause of the subquery. This approach reminds us the way we would look up the supplier name if we had a file on shipments and on suppliers. We first would look up records that contain shipments to J3 and find out the supplier numbers. Then having the supplier numbers we would go to the supplier file to look up the names.
Q12. What about more then one nesting? Get me the supplier name for suppliers that supply at least one green part.
A12. SELECT sName
FROM S
WHERE
sNo IN
(SELECT
sNO
FROM SPJ
WHERE
pNo IN
(SELECT
pNo
FROM P
WHERE
color = 'Green') ) ;
Q13. Can you do the same query with a JOIN?
A13. Yes!
SELECT S.sName
FROM S, P, SPJ
WHERE S.sNO = SPJ.sNO
AND P.pNO = SPJ.pNO
AND P.color = 'Green'
Q14. How about finding the supplier names for
suppliers that never shipped anything?
A14. SELECT S.sName
FROM S
WHERE
sNo NOT IN
(SELECT DISTINCT sNO
FROM SPJ)
;
This one is especially worth remembering. The
subquery generates a set of supplier numbers for suppliers that made at least
one shipment. The outer query lists the names whose sNo is not in the set.
Q15. What about using something other than IN. Get the supplier names for suppliers who have the minimum status.
A15. SELECT S.sName
FROM S
WHERE status =
(SELECT MIN(status)
FROM S) ;
Note that you can use = or < or > or ,<= or >= only if the
subquery return a single value.
Q16 What about a nested query involving the same table?
Get the supplier names for suppliers who are in the same City as supplier S3.
A16. SELECT sName
FROM S
WHERE City
IN
(SELECT
City
FROM S
WHERE sNo
= 'S3') ;
Q17. Is it possible for an inner select to refer
to data in the outer select?
A17. Yes but an outer select can not refer
to values in the inner select. Example: Get names of suppliers that shipped
part with pNo = 'P2'.
SELECT SNAME
FROM S
WHERE 'P2' IN
( SELECT pNo
FROM SPJ
WHERE jNo = 'J3'
AND SPJ.sNo = S.sNo ) ;
Notice that we made a reference to S.sNo in
the subquery even though there is no table S in the subquery. These kind of
nested statements are referred to as correlated select statements.
Go to top
Q18Can we do the same query with JOIN, instead of nesting?
A18. It's informative to do the same query (Q16) with JOIN, to see how to JOIN a table with itself.
SELECT sName
FROM S S1, S S2
WHERE S2.sNo = 'S3'
AND S1.City = S2.City;
S1 and S2 are aliases for S. So to make an alias name its table name space then alias name in the FROM clause. That's it.
Go to top
NOT EXISTS
Q19. What if I wanted to know the supplier names that shipped every part (in the P table)?
A19. This is difficult in SQL, because it can't be done with either JOIN or nested queries discussed so far. SQL has EXISTS construct which is more powerful than the usual JOIN. To check if every part was shipped would require a loop through all the parts in P. But SQL does not support loops. But if we rephrase question 18 as: Get the suppliers such that there is no part (in the P table) that they did not supply. Then it can be done in SQL as follows:
SELECT sName
FROM S
WHERE NOT EXISTS
( SELECT *
FROM P
WHERE NOT EXISTS
(SELECT *
FROM SPJ
WHERE SPJ.sNo = S.sNo
AND SPJ.pNo = P.pNo ) ) ;
Q20. That was very confusing. Is there a simpler way to answer such query which requires checking that everything is in the set and not that at least one item is in a set?
A20. There is, but not in SQL. Embedded SQL in a procedural language such as C or Oracle's PL/SQL would allow us to do loops through the items to check if every item is in the set.
Q21 Are we finished yet (with SQL)?
A21 Not yet but we almost there. We still have to do updates, inserts, and deletes; these are transactions. Also creating tables views and indexes, are part of DDL (Data Definition Language).
UPDATES
Q22. How would I change the COLOR of part P2 in the P table from Green to Red?
A22. UPDATE P
SET color = 'Red'
WHERE pNo = 'P2' ;
Q23. Can I do updates to several records at once,
like change the status of each supplier in London to 20?
A23. UPDATE S
SET
status = 20
WHERE
city = 'London' ;
Q24. How about one with a subquery, like set
shipment quantity to zero for all suppliers in London.
A24. UPDATE SPJ
SET QTY = 0
WHERE
SPJ.sNO IN
(SELECT
S.sNo
FROM S
WHERE S.city
= 'London') ;
GO to TOP
DELETE
Q25. How do we delete all records from the P
table, where the color of parts is blue?
A25. DELETE
FROM P
WHERE
color = 'Blue' ;
Please remember that you always delete the entire row and
never just certain parts of a row.
Q26. Delete all parts that were never shipped.
A26. DELETE
FROM P
WHERE pNo NOT IN
( SELECT DISTINCT pNo
FROM SPJ ) ;
This one was tricky. The inner query produced a set of parts that were shipped. The outer query deleted all the rows from P whose pNo is not in the set of pNo for parts that were shipped.
Go to TOP
INSERT
Q27. How do I add a new part record to the P
table?
A27. INSERT
INTO P
(pNo, city, weight)
VALUES
('P7', 'Athens', 24) ;
Note that since we did not mention pName and
color in the INSERT statement they will get null values automatically.
Q28. Can we omit the column names in INSERT?
A28. If you omit the field names then you must
specify all the values for all the columns.
INSERT
INTO P
VALUES
('P8', 'Sprocket', 'Pink', 14, 'New York') ;
Q29. Is there anyway I can save the answer to a query in the database?
A29. Yes, you can create a table with columns that correspond to the columns in the original select and do a multiple INSERT. For example, for each part supplied get the part number and the total quantity supplied and save the result in the database.
CREATE TABLE
Temp
(pNo char
(3) Primary Key,
TotQty INTEGER);
INSERT
INTO Temp
(pNo, TotQty)
SELECT
pNo, SUM (qty)
FROM SPJ
GROUP BY
pNo;
The SELECT is executed and the result is
automatically put into table Temp.
Q30. Is that how tables are created with SQL, with the CREATE statement.
A30. Yes. We will go into it in more detail in the INTEGRITY lesson.
In SQL, you might (check your DBA) have access to create views for yourself. What a view does is to allow you to assign the results of a query to a new, personal table, that you can use in other queries, where this new table is given the view name in your FROM clause. When you access a view, the query that is defined in your view creation statement is performed (generally), and the results of that query look just like another table in the query that you wrote invoking the view. For example, to create a view:
CREATE VIEW
GoodParts AS
SELECT
* FROM P
WHERE
color = ‘Red’
OR color = ‘Blue’;
Now we can
write a query treating GoodParts view as a table.
SELECT *
FROM GoodParts;
This query shows all Parts from the P table where the part color is Red
or Blue. Views can be used to restrict database access, as well as, in this
case, simplify a complex query.
Go to top