SQL HOMEWORK

SPECIFICATIONS:

Implement the following queries (write the queries and use the web interface to execute them).

NOTE: I am showing you the expected results for each one of the queries.

Find the client first name and last name of the clients that have a meeting.

FIRSTNAME	LASTNAME
Joyce	Cummings
Frances	Hughes
Irene	Jacobs
Winston	Lee
Ryan	Jones
Samantha	Babson
Robert	Barnes

2. Write three different queries to answer the question below (Hint: using OR, using UNION, using IN, etc.) Do not show duplicate records.

Find the client first name and last name of the clients that have ordered a picture size of wallet or 8by10.

FIRSTNAME	LASTNAME
Joyce	Cummings
Winston	Lee

Find the total number of bookings per client. In other words list the clientid and in front of it list how many bookings he/she has scheduled.

CLIENTID	COUNT(*)
111	1
111111	2
222222	2
333333	1
444444	1
555555	1
666666	1

4. Use the INTERSECT operation to write the query below.

Find the client first name and last name of the clients that have ordered a picture size of wallet AND 8by10.

FIRSTNAME	LASTNAME
Joyce	Cummings
Winston	Lee

Find the number of jobs per photographer. In other words, list the empid of the photographer and in front of it list how many jobs (contracts) he/she has been assigned to work on.

PHOTO_EMPID	COUNT(*)
123456	3
876543	2
234567	2

6. Use set difference (MINUS)

Find the contract number and the event name for the jobs that do not have any payments.

CONTRACTNO	EVENTNAME
121212	Hughes wedding
131313	Jacobs wedding
141414	graduation
151515	-
161616	Police Dept Promotion

7. Write query 6 using a sub-query with NOT EXISTS

8. Write query 6 using a sub-query with NOT IN

9. List the average of totalcost, the minimum totalcost and the maximum totalcost per type of job. In other words, list the type of job and in front of it; list the average total cost, the minimum total cost and the maximum total cost.

TYPE	AVG(TOTALCOST)	MIN(TOTALCOST)	MAX(TOTALCOST)
ceremony	200	100	300
wedding	3100	2500	4000
portrait	225	100	350

10. List the clientid, lastname, contractno, datepaid, paytype and amount for all the payments (Photo_payment) that the client 111 has made.

CLIENTID	LASTNAME	CONTRACTNO	DATEPAID	PAYTYPE	AMOUNT
111	Cummings	111111	02-JAN-06	deposit	600
111	Cummings	111111	22-APR-06	event	700
111	Cummings	111111	20-MAY-06	proof del	700

Photography Database Relational Schema Photo_Client (clientId, firstname ,lastName , areaCode ,phoneNumber Stpeet, zip) Meeting (clientId,meetDate,meetTime, rename) Photographer (empId, firstname,LastName , areaCode ,phoneNumber, Ftreet ,zip) Booking (clientId, bookingDate, bookingTime,duration, type, photd_empId,asst_empId PackageMenu (packageNo, numWallet,num5y, numBby10, umliby14,num16by20,albumType albumPages,albumCover,price) Job (contractNo, type, eventName, location, clientIdjobdate, jobtine, duration,photo-empid, as st-empid, packageNoChosen, totalCost,amtPaid) Proof (contractNo, proofNo, quality) Photo_Order VorderNo, dateOrdered, totalAmount,packageNoordered, contractNo) OrderItem (ordet No,prootNo picturesize, contractNo, quantity,dateDelivered) Photo_Payment (contractNo datePaid, payType, amount)