We have started the intermediate course to prepare you for the Exam 98-364.
Download the class notes for day 1.
Just another CUNY Academic Commons site
We have started the intermediate course to prepare you for the Exam 98-364.
Download the class notes for day 1.
Download the class notes for day 10 (quiz3 & quiz3b).
-- Just for fun, read and run or run and read. DECLARE @yourName VARCHAR(50) = 'your_name'; DECLARE @CourseCd VARCHAR(15) = 'WS18SQL1002'; PRINT CONCAT(CHAR(084),CHAR(104),CHAR(097),CHAR(110), CHAR(107),CHAR(032),CHAR(121),CHAR(111),CHAR(117), CHAR(044),CHAR(032),@yourName,CHAR(044),CHAR(032), CHAR(102),CHAR(111),CHAR(114),CHAR(032),CHAR(116), CHAR(097),CHAR(107),CHAR(105),CHAR(110),CHAR(103), CHAR(032),CHAR(099),CHAR(108),CHAR(097),CHAR(115), CHAR(115),CHAR(032),@CourseCd,CHAR(046),CHAR(013), CHAR(083),CHAR(101),CHAR(101),CHAR(032),CHAR(121), CHAR(111),CHAR(117),CHAR(032),CHAR(105),CHAR(110), CHAR(032),CHAR(116),CHAR(104),CHAR(101),CHAR(032), CHAR(105),CHAR(110),CHAR(116),CHAR(101),CHAR(114), CHAR(109),CHAR(101),CHAR(100),CHAR(105),CHAR(097), CHAR(116),CHAR(101),CHAR(032),CHAR(099),CHAR(108), CHAR(097),CHAR(115),CHAR(115),CHAR(046),CHAR(013), CHAR(013),CHAR(070),CHAR(046),CHAR(079),CHAR(108), CHAR(118),CHAR(101),CHAR(114),CHAR(097),CHAR(013), CHAR(102),CHAR(111),CHAR(108),CHAR(118),CHAR(101), CHAR(114),CHAR(097),CHAR(064),CHAR(098),CHAR(109), CHAR(099),CHAR(099),CHAR(046),CHAR(099),CHAR(117), CHAR(110),CHAR(121),CHAR(046),CHAR(101),CHAR(100), CHAR(117),CHAR(013),CHAR(104),CHAR(116),CHAR(116), CHAR(112),CHAR(058),CHAR(047),CHAR(047),CHAR(102), CHAR(111),CHAR(108),CHAR(118),CHAR(101),CHAR(114), CHAR(097),CHAR(046),CHAR(099),CHAR(111),CHAR(109), CHAR(109),CHAR(111),CHAR(110),CHAR(115),CHAR(046), CHAR(103),CHAR(099),CHAR(046),CHAR(099),CHAR(117), CHAR(110),CHAR(121),CHAR(046),CHAR(101),CHAR(100), CHAR(117),CHAR(047)); -- characters in ASCII
Here’s a little more.
We have finished the course and we only have one more day for the final.
Download the class notes for day 9.
We are two classes away from the end of this course. I hope you have been having fun with creating, altering and dropping objects including stored procedures and user-defined functions.
Download the class notes for day 8.
Programming SQL of course
of course, other than myself.
We are almost done with the course — two more days before the final. We are having lots of fun as we get ready to write functions and procedures.
Download the class notes for day 7.
We have started to CREATE database objects.
CREATE object_type object_name [AS structure]
So far we are making (CREATE),
CREATE TABLE table_name ( field1 datatype [null|not null] [unique] [primary key], field2 datatype [null|not null], ... )
modifying (ALTER)
ALTER TABLE table_name ADD|DROP|ALTER COLUMN field_name datatype
and destroying (DROP) tables.
DROP TABLE table_name
Download the class notes for day 6.
We have been working with WHERE to limit our output.
SELECT table1.field1, table1.field2... table2.field1, table2.field2... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON table1.shared_field1 = table2.shared_field1 WHERE table1.field2 = some_value;
We have also seen the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN.
Download the class notes for day 5.
We are almost half way done with the course. We have covered how to retrieve data (SELECT), filtering data (WHERE), organizing data (ORDER BY), grouping data (GROUP BY) when using aggregate functions and formatting numeric values (FORMAT()).
SELECT table1.field1, table1.field2... table2.field1, table2.field2... table3.field1, table3.field2... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON table1.shared_field1 = table2.shared_field1 AND table1.shared_field2 = table2.shared_field2 INNER|LEFT|RIGHT JOIN table3 ON table1.shared_field1 = table3.shared_field1 AND table1.shared_field2 = table3.shared_field2... WHERE condition1 AND|OR condition2 ...
Download the class notes for day 4.