I need help with the following especially the having clause and count clause

my database book isn't much help 

 

 

 

(20 points)  Run the following SQL script to reproduce the original spreadsheet first seen in Seminar 1. Copy and paste the resulting table into your Word Document.

SELECT R.RegDate, P.FirstName, P.LastName, P.PhoneNum, P.eMail, R.AdultCnt, R.ChildCnt, R.RoomNum

FROM REGISTRATIONS R, PATRONS P

WHERE R.PatronID = P.PatronID

AND R.RegDate = DateValue("06-01-15")

ORDER BY R.RegID;

 

Write a SQL statement in Microsoft Word to identify patrons who have visited with children. Use SELECT DISTINCT to eliminate duplicates. Copy and paste the resulting table into your Word Document.

 

Write a SQL statement in Microsoft Word to identify different Patrons with the same names (FirstName, LastName). (HINT: Use a GROUP clause and limit your results with a HAVING COUNT(*) > 1 clause.) Copy and paste the resulting table into your Word Document.

 

Write a SQL statement in Microsoft Word to identify Patrons (FirstName, LastName, eMail) who have visited more than once. Sort the output in descending order by Count (ORDER BY Count(*) DESC). Copy and paste the resulting table into your Word Document.

 

CIS114 - S3 Written AssignmentThe Dew Drop Inn was built during the age of “mom and pop” motels. It started out as four rusticlog cabins in the style popular during the sixties. During the ensuing years it was rebuilt, andnow offers eight efficiency units with high speed internet, kitchenettes, 140 TV channels and avariety of rooms. Several years ago the owners moved from an entirely paper system to asystem using Microsoft Excel. This was sufficient for such a small operation, but now that theInn has an internet connection, they want to make more use of the data. For this reason youhave been retained to create a proper database using Microsoft Access. Continue your projectby using the original Microsoft Excel spreadsheets to load data into the Microsoft Accessdatabase.Normally, data would be loaded into an SQL database using INSERT statements such as thefollowing:INSERT INTO ROOMS VALUES (101, Double, 1, 44.00);INSERT INTO ROOMS VALUES (102, Double, 2, 49.00);INSERT INTO ROOMS VALUES (103, Queen, 2, 61.00);INSERT INTO ROOMS VALUES (104, Queen, 2, 61.00);INSERT INTO ROOMS VALUES (105, Queen, 2, 61.00);INSERT INTO ROOMS VALUES (106, King, 1, 61.00);INSERT INTO ROOMS VALUES (107, King, 1, 61.00);INSERT INTO ROOMS VALUES (108, King, 1, 61.00);Because of deficiencies in SQL and Microsoft Access each row must be loaded as a separatestatement, which can become very tedious real quick. An alternate method available for mostdatabase systems is to import data from a comma separated values (csv) file.Download the three data files (rooms.csv, patrons.csv, and registrations.csv) to an appropriatelocation on your computer (for example, c:\cis114). Open the Microsoft Access Dew Drop InnDatabase (DewDropInn.accdb). If you don’t already have one, you may download and open theone provided. You should have three empty tables: PATRONS, REGISTRATIONS, andROOMS.From inside Microsoft Access, click on EXTERNAL DATA | Text File.Fill out the first menu asfollows to add data from rooms.csv to the ROOMS Table and click OK and then Next.