Transcript PPTX

Other SQL Query Examples
CSE
4701
SQLDML.1
Homework 3 from Spr 2015

Problem 6.18 from the 6th
edition done in SQL and
NOT relational expressions
CSE
4701
SQLDML.2
Problem 6.18 in 6th edition
a. How many copies of the book titled The Lost Tribe are owned by the
library branch whose name is ‘Sharpstown’?
SELECT NoOfCopies
CSE FROM ( (BOOK NATURAL JOIN BOOK_COPIES )
4701
NATURAL JOIN LIBRARY_BRANCH )
WHERE Title='The Lost Tribe' AND BranchName='Sharpstown’
BaB= (BOOKCOPIES * (Title=‘The Lost Tribe’ (BOOK))) )
BookId
Ans = No_Of_Copies( (BranchName=‘Sharpstown’ (LIBRARY-BRANCH)) * BaB)
BranchID
SQLDML.3
Problem 6.18 in 6th edition
b. How many copies of the book titled The Lost Tribe are owned by each
library branch?
SELECT BranchName, NoOfCopies
CSE FROM ( (BOOK NATURAL JOIN BOOK_COPIES )
4701
NATURAL JOIN LIBRARY_BRANCH )
WHERE Title='The Lost Tribe'
CaB = BOOKCOPIES * LIBRARY_BRANCH)
BranchId
Ans = BranchName, No_Of_Copies( (Title=‘The Lost Tribe’ (BOOK)) * CaB)
BookId
SQLDML.4
Problem 6.18 in 6th edition
c. Retrieve the names of all borrowers who do not have any books checked out
SELECT Name
FROM BORROWER B
CSE WHERE NOT EXIST
4701
( SELECT *
FROM BOOK_LOANS L
WHERE B.CardNo = L.CardNo )
d. For each book that is loaned out from the Sharpstown branch and whose
Due_date is today, retrieve the book title, the borrower’s name, and the
borrower’s address.
SELECT B.Title, R.Name, R.Address
FROM BOOK B, BORROWER R, BOOK_LOANS BL, LIBRARY_BRANCH LB
WHERE LB.BranchName='Sharpstown' AND LB.BranchId=BL.BranchId AND
BL.DueDate='today' AND BL.CardNo=R.CardNo AND BL.BookId=B.BookId
SQLDML.5
Problem 8.11/6.18 in 6th edtion
e. For each library branch, retrieve the branch name and the total number
of books loaned out from that branch.
SELECT L.BranchName, COUNT(*)
FROM BOOK_COPIES B, LIBRARY_BRANCH L
WHERE B.BranchId = L.BranchId
CSE
GROUP BY L.BranchName
4701 f. Retrieve the names, addresses, and number of books checked out for all
borrowers who have more than five books checked out.
SELECT B.CardNo, B.Name, B.Address, COUNT(*)
FROM BORROWER B, BOOK_LOANS L
WHERE B.CardNo = L.CardNo
GROUP BY B.CardNo
HAVING COUNT(*) > 5
g. For each book authored (or coauthored) by Stephen King, retrieve the
title and the number of copies owned by the library branch whose name
is Central.
SELECT TItle, NoOfCopies
FROM ( ( (BOOK_AUTHORS NATURAL JOIN BOOK)
NATURAL JOIN BOOK_COPIES)
NATURAL JOIN LIBRARY_BRANCH)
WHERE Author_Name = 'Stephen King' and BranchName = 'Central'
SQLDML.6
Homework 3 from Spr 2015

Problem 4.10 in 6th edition
CSE
4701
SQLDML.7
Problem 4.10 in 6th edition
CSE
4701
SQLDML.8
Problem 4.10 in 6th edition
(a) Retrieve the names of employees in department 5 who
work more than 10 hours per week on the 'ProductX' project.
SELECT LNAME, FNAME
CSE FROM EMPLOYEE, WORKS_ON, PROJECT
4701 WHERE DNO=5 AND SSN=ESSN AND
PNO=PNUMBER AND PNAME='ProductX' AND HOURS>10
LNAME
FNAME
SELECT LNAME, FNAME
FROM EMPLOYEE
Smith
John
WHERE DNO=5 AND SSN IN
English Joyce
( SELECT ESSN
FROM WORKS_ON
WHERE HOURS>10 AND PNO IN
( SELECT PNUMBER
FROM PROJECT
WHERE PNAME='ProductX' ) )
SQLDML.9
Problem 4.10 in 6th edition
(b) List the names of employees who have a dependent
with the same first name as themselves.
SELECT LNAME, FNAME
CSE FROM EMPLOYEE, DEPENDENT
4701 WHERE SSN=ESSN AND FNAME=DEPENDENT_NAME
Another possible SQL query uses nesting as follows:
SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE EXISTS
( SELECT *
FROM DEPENDENT
WHERE FNAME=DEPENDENT_NAME
AND SSN=ESSN )
Result (empty):
SQLDML.10
Problem 4.10 in 6th edition
(c) Find the names of employees that are
directly supervised by 'Franklin Wong'.
LNAME FNAME
Smith John
Narayan Ramesh
English Joyce
CSE SELECT E.LNAME, E.FNAME
4701 FROM EMPLOYEE E, EMPLOYEE S
WHERE S.FNAME='Franklin' AND
S.LNAME='Wong' AND E.SUPERSSN=S.SSN
Another possible SQL query uses nesting as follows:
SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE SUPERSSN IN
( SELECT SSN
FROM EMPLOYEE
WHERE FNAME='Franklin' AND LNAME='Wong' )
SQLDML.11
Problem 4.10 in 6th edition
(d) For each project, list the project name and the total hours per week (by
all employees) spent on that project.
CSE
SELECT PNAME, SUM (HOURS)
4701
FROM PROJECT, WORKS_ON
WHERE PNUMBER=PNO
GROUP BY PNAME
Result:
PNAME
SUM(HOURS)
ProductX
52.5
ProductY
37.5
ProductZ
50.0
Computerization55.0
Reorganization
25.0
Newbenefits
55.0
SQLDML.12
Problem 4.10 in 6th edition
(e) Retrieve the names of employees who work on every project.
SELECT LNAME, FNAME
CSE FROM EMPLOYEE
4701 WHERE NOT EXISTS
( SELECT PNUMBER
FROM PROJECT
WHERE NOT EXISTS
( SELECT *
FROM WORKS_ON
WHERE PNUMBER=PNO AND ESSN=SSN ) )
Result (empty):
SQLDML.13
Problem 4.10 in 6th edition
(f) Retrieve the names of employees who do not
work on any project.
CSE SELECT LNAME, FNAME
4701 FROM EMPLOYEE
WHERE NOT EXISTS
( SELECT *
FROM WORKS_ON
WHERE ESSN=SSN )
Result (empty):
SQLDML.14
Problem 4.10 in 6th edition
(g) For each department, retrieve the department name,
and the average salary of employees working in that department.
CSE
4701 SELECT DNAME, AVG (SALARY)
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER=DNO
GROUP BY DNAME
Result:
DNAME
AVG(SALARY)
Research
33250
Administration 31000
Headquarters
55000
SQLDML.15
Problem 4.10 in 6th edition
(h) Retrieve the average salary of all female employees.
CSE
4701
SELECT AVG (SALARY)
FROM EMPLOYEE
WHERE SEX='F’
Result:
AVG(SALARY)
31000
SQLDML.16
Problem 4.10 in 6th edition
(i) Find the names and addresses of employees who work on at least one
project located in Houston but whose department has no location in Houston.
SELECT LNAME, FNAME, ADDRESS
CSE FROM EMPLOYEE
4701 WHERE EXISTS
( SELECT *
FROM WORKS_ON, PROJECT
WHERE SSN=ESSN AND PNO=PNUMBER
AND PLOCATION='Houston' )
AND NOT EXISTS
( SELECT *
FROM DEPT_LOCATIONS
WHERE DNO=DNUMBER
AND DLOCATION='Houston' )
Result:
LNAME
FNAME
ADDRESS
Wallace Jennifer 291 Berry, Bellaire, TX
SQLDML.17
Problem 4.10 in 6th edition
(j) List the last names of department managers who have no
dependents.
CSE
4701
SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE EXISTS
( SELECT *
FROM DEPARTMENT
WHERE SSN=MGRSSN )
AND NOT EXISTS
( SELECT *
FROM DEPENDENT
WHERE SSN=ESSN )
Result:
LNAME FNAME
Borg James
SQLDML.18
Homework 4 from Spring 2015
Problem 3.3 for the Chinook Database Schema
a.
Find the list of all Customers and Employees that have the same
last name and print out the Last Name (only once), Address,
City, and State for each.
CSE
b.
Find and print the Customer Names (First and Last) and
4701
Company Name of all Customers that have purchased a Rock
Album. (where the Name is ‘Rock’ in the Genre table).
c.
Find all of print album name and tracks of all of the albums by
the composer James Hetfield, grouped by Album.
d.
For each customer that lives in Canada (CA- Country attribute
of Customer), find all invoices and for each result, and print
Customer Last Name, number of invoices for customer, and the
total amount paid for all invoices.
SQLDML.19
Explaining Chinook Schema



CSE 
4701


Employees and Customers are Two Main DB Users
Repository Track Artists and Their Albums
Each Album has Multiple Tracks
Tracks are of a Particular Media and Genre
Customers can Also Create Playlists of Tracks
Customers have Invoices
 Each Invoice has Multiple Lines
 Each Line References one Track
SQLDML.20
CSE
4701
Chinook
EER
SQLDML.21
https://chinookdatabase.codeplex.com/wikipage?title=Chin
ook_Schema&referringTitle=Documentation
CSE
4701
SQLDML.22
What Does Data Look Like?
CSE
4701
StevesSongs
PL12,
PL12,
PL12,
PL13,
T3
T5
T7
T3
T3, Hello, A1
T4, Bye, A1
T5, MySong, A1
etc.
SQLDML.23
Problem 3.3a
SELECT employee.LastName, employee.Address,
employee.City, employee.State, customer.Address,
CSE
customer.city, customer.state
4701
FROM chinook.employee, chinook.customer
WHERE employee.LastName = customer.LastName;
SQLDML.24
Problem 3.3b
SELECT DISTINCT customer.FirstName, customer.LastName,
customer.Company
FROM chinook.customer, chinook.invoice, chinook.invoiceline,
CSE
chinook.track, chinook.genre
4701
WHERE customer.CustomerId = invoice.CustomerId
AND invoice.InvoiceId = invoiceline.InvoiceId
AND invoiceline.TrackId = track.TrackId
AND track.GenreId = genre.GenreId AND genre.name = 'rock';
SQLDML.25
Problem 3.3c


CSE
4701
Find all of print album name and tracks of all of the albums by
the composer James Hetfield, grouped by Album
What Does Data for Track Look Like?
SELECT NAME, COMPOSER FROM CHINOOK.TRACK;
SQLDML.26
Problem 3.3c


CSE 
4701
There are other Spellings to Consider:
 J. Hetfield
There are some Hetfields without First Name or Initial
What SQL Command is Needed for Searching
Composer?
 LIKE is Used to Compare Partial Strings
 '%' (or '*') Replaces an Arbitrary # of characters
'_' replaces a single arbitrary character
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE ADDRESS LIKE '%Houston,TX% '
SQLDML.27
Problem 3.3c
SELECT album.title, track.name
FROM chinook.album, chinook.track
CSE
WHERE album.AlbumId = track.AlbumId AND track.Composer
4701
LIKE '%Hetfield%';
SQLDML.28
Problem 3.3d.
SELECT customer.LastName, COUNT(invoice.InvoiceId),
SUM(invoice.total)
CSE
FROM chinook.customer, chinook.invoice
4701
WHERE customer.Country = 'Canada'
AND customer.CustomerId = invoice.CustomerId
GROUP BY customer.LastName;
SQLDML.29
Homework 4 from Spring 2015
Problem 3.4 for the Northwind Database Schema
a.
Find and print the company names and company addresses of
all Suppliers that supply the category name Seafood.
b.
Count and print the number of suppliers for each of the eight
CSE
different categories of food which by name are: Beverages,
4701
Condiments, Confections, Dairy Products, Grains/Cereals,
Meat/Poultry, Produce, Seafood.
c.
For each country (ShipCountry in Orders), total the Freight
charges. The countries are: France, Germany, Brazil, Belgium,
Switzerland, Venezuela, Austria, Mexico, USA, Sweden,
Finland, Italy, Spain, UK, Ireland, Portugal, Canada, Denmark,
Poland, Norway, Argentina
SQLDML.30
Explaining Northwind Schema



CSE
4701





Suppliers: A Suppliers Contact Info and web link.
Products: Names, suppliers, and Prices
Categories: Categories of Northwind products such as
Beverages, Condiments, Confections, Dairy Products,
Grains/Cereals, Meat/Poultry, Produce, Seafood
Orders: For each Customer with dates &Shipping
Order Details: Products, quantities, and price.
Employees: Typical Info.
Customers: Typical Info.
Shippers: Typical Info.
SQLDML.31
Northwind Schema
CSE
4701
SQLDML.32
Northwind Schema – Key Types
CSE
4701
SQLDML.33
Problem 3.4a
CSE
4701
SELECT *
FROM NORTHWIND.PRODUCTS
WHERE UNITPRICE >= 10 AND UNITPRICE <= 20
ORDER BY UNITPRICE DESC;
SQLDML.34
Problem 3.4b
SELECT categories.CategoryName,
COUNT(suppliers.SupplierID)
CSE
FROM northwind.categories, northwind.products,
4701
northwind.suppliers
WHERE suppliers.SupplierID = products.SupplierID
AND products.categoryID = categories.CategoryID
GROUP BY categories.CategoryName;
SQLDML.35
Problem 3.4c
CSE
4701
SELECT *
FROM NORTHWIND.PRODUCTS
WHERE UNITPRICE >= 10 AND UNITPRICE <= 20
ORDER BY UNITPRICE DESC;
SQLDML.36