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]

  1. 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;

sql1

  1. List all students, course ids and course descriptions for courses they are currently taking

sql2

  1. 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;

sql3

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;

sql5

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

screen-shot-2016-11-03-at-9-22-33-pm

                                                                                                                                          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)

screen-shot-2016-11-03-at-9-25-22-pm

(Oracle)

screen-shot-2016-11-03-at-9-27-38-pm

                                                                                                                                           

Question # 3

Verify to make sure that no student is registered for the same class twice

screen-shot-2016-11-03-at-9-35-37-pm

                                                                                                                                           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)

 

screen-shot-2016-11-03-at-9-32-07-pm

(Oracle)

screen-shot-2016-11-03-at-9-34-13-pm

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.screen-shot-2016-11-03-at-9-36-57-pm

 

Leave a Reply

Your email address will not be published. Required fields are marked *