Structured Query Language (SQL) is the language used to manipulate data in relational databases. In rSQUAREedge Certified Data Analytics Practitioner (CDAP) Program, I was luckily be taught by Sam Sultan ,director of Information Technology at Home Box Office (HBO). In the classes, we use SQL to select, update, insert, and delete data from database tables, and acquire hands-on experience with both Oracle and MySQL. In addition, I learned Database design, table relationships, and normalization techniques. This course prepares me to work with relational database, such as Oracle, DB2, SQL Server, or MySQL.
Course website: [samsultan.com/db1]
- List all students and course ids of classes they are currently taking
SELECT s.lname ‘Stu-last’, s.fname ‘Stu-first’, c.course_id “Course ID”
FROM student s JOIN class c
ON s.ssn = c.stu_ssn;
- List all students, course ids and course descriptions for courses they are currently taking
- List all instructors, and their current students
SELECT i.lname ‘Inst-last’, i.fname ‘Inst-first’, s.lname ‘Stu-last’, s.fname ‘Stu-first’
FROM instructor i JOIN class c
ON i.ssn = c.inst_ssn
JOIN student s
ON c.stu_ssn = s.ssn;
4.
MySQL:
SELECT s.lname ‘Stu-last’, s.fname ‘Stu-first’, co.course_id “Course ID”, co.description “Course Description”
FROM student s LEFT JOIN class c
ON s.ssn = c.stu_ssn
LEFT JOIN course co
ON c.course_id = co.course_id;
Oracle:
SELECT s.lname “Stu-last”, s.fname “Stu-first”, co.course_id “Course ID”, co.description “Course Description”
FROM student s, class c, course co
WHERE s.ssn = c.stu_ssn(+)
AND c.course_id = co.course_id(+);
5.
Oracle:
SELECT i.lname “Inst-last”, i.fname “Inst-first”, cl.course_id “Course ID”, s.lname “Stu-last”, s.fname “Stu-first”
FROM instructor i FULL JOIN class cl
ON i.ssn = cl.inst_ssn
FULL JOIN student s
ON s.ssn = cl.stu_ssn;
MySQL:
SELECT i.lname “Inst-last”, i.fname “Inst-first”, cl.course_id “Course ID”, s.lname “Stu-last”, s.fname “Stu-first”
FROM instructor i LEFT JOIN class cl
ON i.ssn = cl.inst_ssn
RIGHT JOIN student s
ON cl.stu_ssn = s.ssn
UNION
SELECT i.lname “Inst-last”, i.fname “Inst-first”, cl.course_id “Course ID”, s.lname “Stu-last”, s.fname “Stu-first”
FROM student s LEFT JOIN class cl
ON cl.stu_ssn = s.ssn
RIGHT JOIN instructor i
ON i.ssn = cl.inst_ssn;
Question # 1 Count, sum and average the prices of all courses
Question # 2
Aggregate by summing the amount and counting the number of payments per vendor and per description. Rollup this data up to the vendor level and grand total level
(MySQL)
(Oracle)
Question # 3
Verify to make sure that no student is registered for the same class twice
Question # 4 Display all students (including students taking no classes) and the number of classes they are taking. Sort the output by descending number of classes taken, then by last name and first name.
(MySQL)
(Oracle)
Question # 5
Display all vendors, descriptions and amounts from the payment table, and a row at the bottom that displays the total amount of all payments made. Do not display sub-total lines – i.e. do not use ‘rollup’ feature.