CIS-3010 Lab Assignment #2: Basic Queries

Due: Friday, February 3, 2023

Reading: Refer to the SQL SELECT slides. In the text Chapter 7 describes SQL. Section 7.3.1 is specifically about SELECT. Be aware that the text covers features of SELECT we haven't discussed yet. The slides might be a more reliable guide for the features of SELECT that you'll need for this lab. However, reviewing the text would be good too. It also covers many of the features you will need here, and we will later discuss many of the other features it covers as well.

In this lab you will do some queries against the VariableStars example data you loaded in the last lab. This will give you some practice with the important SQL SELECT statement. For each part below, write a query that retrieves the information requested. Make your query as specific as possible (i.e., don't return any information that doesn't satisfy the request). To give you an idea if you are on track, I've shown the number of rows of data you should expect in each case.

In all cases (unless otherwise noted), retrieve the name and constellation of the stars in addition to whatever other attributes are requested in the question. Also, "stars" refers to variable stars as listed in our data set. In other words a phrase like "all stars" really means "all variable stars in our data set" not all stars in the sky! (Our data set constitutes our "universe of discourse")

Find all stars in the constellation Auriga ('Aur') with a minimum brightness of at least 8.0 (that is, brighter than 8.0... so a smaller number). Such stars can be seen in binoculars even when at their minimum. Display the maximum and minimum brightness of each star. [58 rows]
Find all stars in Taurus ('Tau') and Gemini ('Gem') with maximum brightness that is fainter than 6.0 (larger number) and a minimum brightness that is brighter than 12.0 (smaller number). These stars are too faint to see with the naked eye, but might be fun objects for a small, backyard telescope. Display the maximum and minimum brightness of each star. Hint: be careful with my use of the word 'and' in the phrase "Taurus and Gemini." English uses logical connectives in ambiguous ways. [276 rows]
Run the same query as in the previous question except sort the output in order of increasing maximum brightness (this will result in the brightest stars appearing first). [276 rows]
Run the same query as in the previous question except add a calculated attribute to your SELECT statement (in addition to the attributes you displayed before) that shows the difference in brightness (min_bright - max_bright) labeled as "mag_diff." Also sort the output in decreasing order of mag_diff (so the stars with the greatest brightness variation appear first). [276 rows]
Find all stars in Ursa Major ('UMa', aka "the Big Dipper") with a magnitude difference between 0.5 and 1.0 magnitudes. Use BETWEEN in the WHERE clause. Display the magnitude difference as well as the right ascension (ra) and declination (dec) and sort the output in increasing order of the magnitude difference. [136 rows]
Find all stars in a rectangular region of the sky with right ascension between 5 hours and 6 hours and declination between +20 and +30 degrees. Use the constellation name as the primary sort key and the star name as the secondary sort key (so the names are sorted within each constellation). Display the brightnesses and positions of each star. [147 rows]
How many constellations have variable stars with a maximum brightness in the range from 2.0 to 2.5? Hint: Use DISTINCT. For this question, display just the constellation names, sorted. [19 rows]
BONUS! How many variable stars does each constellation have? Hint: Use the COUNT aggregation function together with GROUP BY to aggregate appropriate groups and count their membership. Display only the constellation names and variable counts sorted in descending order of counts. [88 rows; Sagittarius has the highest count at 5093]

Submit a document (PDF, text, or MS-Word) showing the queries you used (the SQL SELECT statement) for each part above. You do not need to submit the result of the query. Since we are all using the same data, I can see the result myself if I need to by just running your query on the data. (20 points).