-
Notifications
You must be signed in to change notification settings - Fork 0
/
TYLERSMITHSQL2.txt
57 lines (48 loc) · 1.43 KB
/
TYLERSMITHSQL2.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
1. SELECT first_name||' '||last_name AS "Student Name", phone
FROM student
WHERE employer = 'New York Culture'
ORDER BY last_name;
2. SELECT DISTINCT employer
FROM student
WHERE employer LIKE '%Co.'
ORDER BY employer;
3. SELECT UPPER(last_name||', '|| RPAD(SUBSTR(first_name, 1,1),2,'.' )) AS "Student Name"
FROM student
WHERE phone LIKE '617%'
ORDER BY last_name;
4. SELECT first_name||' '|| last_name AS "Instructor", street_address
FROM instructor
WHERE zip IS NULL;
5. SELECT zip
FROM zipcode
WHERE city = 'Jackson Heights'
ORDER BY zip;
6. SELECT course_no, location
FROM section
WHERE location LIKE '%10'
ORDER BY location;
7. SELECT CASE
WHEN state = 'MA' THEN 'Massachusetts'
WHEN state = 'OH' THEN 'Ohio'
WHEN state = 'PR' THEN 'Puerto Rico'
WHEN state = 'WV' THEN 'West Virginia'
ELSE 'NA'
END "State Name", state, city
FROM zipcode
WHERE state = 'OH' OR state = 'MA' OR state = 'WV' OR state = 'PR'
ORDER BY state;
8. SELECT salutation||'. '||first_name||' '||last_name||' '||street_address||' '||zip AS "Instructor Address"
FROM instructor
WHERE zip LIKE '10015%'
ORDER BY last_name;
9. SELECT student_id, numeric_grade
FROM grade
WHERE section_id = 152 AND grade_type_code = 'QZ'
ORDER BY numeric_grade DESC;
10. SELECT DISTINCT student_id, numeric_grade, CASE
WHEN numeric_grade >= 85 THEN 'Pass'
ELSE 'Fail'
END "GRADE RESULT"
FROM grade
WHERE grade_type_code LIKE 'FI' AND section_ID = '156'
Order BY student_id;