Exercise 1
Please select the correct answer or highlight the clause(s) as described. In highlight questions, the answer can be one or more clauses. To highlight the code, please click on each line and chose "highlight".
Please don't use the example book when answering this part.
1. With SQL, how do you select first 100 objID from a table/ view named “PhotoPrimary”? A. SELECT objID FROM PhotoPrimary
B. SELECT TOP 100 PhotoPrimary.objID. C. EXTRACT objID FROM PhotoPrimary
D. SELECT TOP 100 objID FROM PhotoPrimary E. I don’t know
2. With SQL, which clause would you use to sort the results? A. ORDER
B. SORT C. ORDER BY D. SORT BY E. I don’t know
50 A. IN B. BETWEEN C. RANGE D. JOIN E. I don’t know
4. With SQL, which clause would you use to categorize results based on the value of one or more column? A. GROUP BY B. ORDER BY C. JOIN D. SELECT INTO E. I don’t know
5. With SQL, which clause would you use to select rows in more than one table based on their common column? A. UNION B. GROUP BY C. JOIN D. COMBINE E. I don’t know
6. In the following query, could you highlight the clause(s) that give(s) the table a nickname to make it easy to read and write?
SELECT TOP 100 sp.objID, sp.ra, sp.dec, px.taiBegin, px.taiEnd, FROM specPhoto AS sp JOIN plateX AS px ON sp.plateID = px.plateID WHERE (sp.class='QSO') I don't know
7. In the following query, could you highlight the clause(s) that select(s) objects from a certain range of g magnitude?
SELECT TOP 100 objID, ra, dec, cModelMag_g FROM Galaxy
WHERE ra BETWEEN 178 AND 180 AND dec < 0
51 I don't know
8. In the following query, could you highlight the clause(s) that specify(ies) the criteria for smilar spectra (colors)?
SELECT TOP 10 P.ObjID FROM PhotoPrimary AS P
JOIN Neighbors AS N ON P.ObjID = N.ObjID
JOIN PhotoPrimary AS L ON L.ObjID = N.NeighborObjID WHERE P.ObjID < L. ObjID and abs((P.u-P.g)-(L.u-L.g))<0.05 and abs((P.g-P.r)-(L.g-L.r))<0.05 and abs((P.r-P.i)-(L.r-L.i))<0.05 and abs((P.i-P.z)-(L.i-L.z))<0.05 I don't know
9. In the following query, could you highlight the clause(s) that avoid(s) duplicates in results？
SELECT TOP 10 P.ObjID FROM PhotoPrimary AS P
JOIN Neighbors AS N ON P.ObjID = N.ObjID
JOIN PhotoPrimary AS L ON L.ObjID = N.NeighborObjID WHERE P.ObjID < L. ObjID and abs((P.u-P.g)-(L.u-L.g))<0.05 and abs((P.g-P.r)-(L.g-L.r))<0.05 and abs((P.r-P.i)-(L.r-L.i))<0.05 and abs((P.i-P.z)-(L.i-L.z))<0.0 I don't know
10. In the following query, could you highlight the clause(s) that count(s) objects and names the result column?
SELECT plate.programname, class, COUNT(specObjId) AS numObjs FROM SpecObjAll
JOIN PlateX AS plate ON plate.plate = specObjAll.plate GROUP BY plate.programname, class
52 I don't know
Exercise 2
Please highlight the clause(s) as described. In highlight questions, the answer can be one or more clauses.
You can refer to the example book.
1. In the following query, could you highlight the clause(s) that give(s) columns or tables a nickname/ alias?
--Compare different redshift measurements of the same object for objects with high redshift
SELECT prim.bestObjId,
prim.mjd AS PrimMJD, prim.plate AS PrimPlate, other.mjd AS OtherMJD, other.plate AS OtherPlate, prim.z AS PrimZ, other.z AS OtherZ,
plate.programname FROM SpecObjAll prim
JOIN SpecObjAll other ON prim.bestObjId = other.bestObjId
JOIN platex AS plate ON other.plate = plate.plate AND other.mjd = plate.mjd WHERE other.bestObjId > 0 AND prim.sciencePrimary = 1 AND other.sciencePrimary = 0 AND prim.z > 2.5 ORDER BY prim.bestObjId I don't know
2. In the following query, could you highlight the clause(s) that specify(ies) the condition of combining objects in multiple tables?
-- Find galaxies that are blended with a star, and output the deblended galaxy magnitudes
SELECT TOP 10 G.ObjID, G.u, G.g, G.r, G.i, G.z FROM Galaxy AS G
JOIN Star AS S
ON G.parentID = S.parentID WHERE G.parentID > 0
I don't know
3. In the following query, could you highlight the keyword(s) that return(s) the number of objects matching the specific critiria?
53 0.4 < z < 0.5 range.
SELECT round(absMagR,1) as absMagR, COUNT(*) as cnt
FROM Photoz WHERE
z BETWEEN 0.4 and 0.5
AND photoErrorClass=1 and cnt>95 AND zErr BETWEEN 0 and 0.03 group by round(absMagR,1) order by round(absMagR,1)
I don't know
4. When using SDSS DR15 Image List Tool, all Image List queries must have a clause to retrieve coordinate pairs of objects. In the following query, could you highlight the clause(s) used for this function?
SELECT TOP 100 P1.objID AS name, P1.ra AS ra, P1.dec AS dec,
FROM PhotoTag P1, Neighbors N, PhotoTag P2
WHERE P1.objID = N. objID
AND P2.objID = N.NeighborObjID AND N.Distance < 0.05
I don't know
5. In the following query, could you highlight the clause(s) that ensure(s) that each distinct source's measurements appear sequentially in the results?
SELECT p.*, c.*
FROM gaiadr1.cepheid AS c, gaiadr1.phot_variable_time_series_gfov AS p WHERE p.source_id = c.source_id
ORDER BY p.source_id, p.observation_time