Create a New MS Word document and Save it as: U3_Assignment1_Lastname
For all of the scenarios below, write the question numbered followed by the SQL SELECT syntax (copy & paste) and a screen shot of the SQL Select with results from SQL Server
Use the syntax:
SELECT ...
FROM ...
WHERE …
ORDER BY ….
Aggregate Functions – all of the following Select statements must use a function
Show the total number of bowlers that live in Bothell, WA (hint: How many?)
Show the number of games that the bowler with bowlerID 8 has won thus far.
What is the total raw scores of all bowlers who played in match 13, game 3? (hint: add the scores)
Display the total handicap scores of the bowler that has a BowlerID of 18. Rename the result column Total Handicap.
Show the most recent tournament date in the bowling league schedule. Rename the resulting column Last Tournament Date.
Display how many tournaments have been played at Sports World Lanes. Rename the result column Number of Tournaments.
Aggregate Functions, Grouping Data (GROUP BY & HAVING)
Display the bowler's ID and the average raw score for each bowler. Sort the results by the average raw score in descending order.
Calculate the total raw score (add the scores) for each bowler. Display the bowler’s ID and total raw score. Rename the total raw score as Total Pins.
Display match ID and the highest raw score for each match. Only display the results for bowlers who have a high raw score more than 193.
Display the match id, game number and the average handicap score per match and game. Only show results for average handicap scores less than 185. Rename the average score result set Average Under 185. In the results, concatenate the match id and game numbers into one column, include the literal comma, and literal text “Match ID” and “Game Number” as shown in the sample results below:
Display all bowlers first and last names in one column result set, renaming the column Bowler's Name
SELECT
BOWLERFIRSTNAME || ',' || BOWLERLASTNAME BOWLER_NAME
FROM
BOWLERS;
Display a list of all bowlers and addresses formatted suitability for a mailing list, sorted by zipcode.
SELECT
BOWLERFIRSTNAME || ',' || BOWLERLASTNAME "BOWLER NAME",
BOWLERADDRESS || ',' || BOWLERCITY || ',' || BOWLERSTATE || ',' || BOWLERZIP ADDRESS
FROM
BOWLERS
ORDER BY
BOWLERZIP;
What was the point spread between a bowler's handicap and raw score for each game played in match 10? Rename the point spread field in the data result set as Point Difference and sort on the match field.
SELECT
GAMENUMBER,
RAWSCORE - HANDICAPSCORE "Point Difference"
FROM
BOWLER_SCORES
WHERE
MATCHID = 13
ORDER BY
GAMENUMBER
Display the 3rd, 4th, and 5th letters of each tournament location displaying the resulting data set as Location Letters.
SELECT
SUBSTR(TOURNEYLOCATION , 3 , 3) "Location Letters"
FROM
TOURNAMENTS;
Using a string function, display the tournament location of the tournament that occurred on 12/04/2012 in uppercase letters.
SELECT
UPPER(TOURNEYLOCATION) LOCATION
FROM
TOURNAMENTS
WHERE
TOURNEYDATE = TO_DATE('12/04/2012', 'DD/MM/YYYY');
Show the last name of each bowler that lives in Seattle, WA in lowercase letters. Rename the column in the dataset bowler.
SELECT
LOWER(BOWLERLASTNAME) Bowler
FROM
BOWLERS
WHERE
BOWLERCITY = 'Seattle';
Display each bowler's last name, first, name and middle initial all in lowercase in one result column. Be sure that you include spaces between each piece of data. Also, rename the field as Full Name.
SELECT
LOWER(BOWLERLASTNAME) || ' ' || LOWER(BOWLERFIRSTNAME) || ' ' || LOWER(BOWLERMIDDLEINIT) "Full Name"
FROM
BOWLERS;
For the bowler named David Cunningham, display only the world Old that is located in his address. Rename the field in your result Partial Address.
SELECT
SUBSTR(BOWLERADDRESS, 6,3) "Partial Address"
FROM
BOWLERS
WHERE
...
The benefits of buying study notes from CourseMerit
Assurance Of Timely Delivery
We value your patience, and to ensure you always receive your homework help within the promised time, our dedicated team of tutors begins their work as soon as the request arrives.
Best Price In The Market
All the services that are available on our page cost only a nominal amount of money. In fact, the prices are lower than the industry standards. You can always expect value for money from us.
Uninterrupted 24/7 Support
Our customer support wing remains online 24x7 to provide you seamless assistance. Also, when you post a query or a request here, you can expect an immediate response from our side.
