Database Lesson #3 of 8 - The Structured Query Language (SQL)

Dr. Soper gives a lecture on using the structured query language (SQL) to work with databases. Topics include CREATE, ALTER, and DROP statements, SELECT, INSERT INTO, UPDATE, and DELETE FROM statements, subqueries, and inner and outer joins.

[@mekabay]

I didn't see the helpful list of timings vs topics, so here's one I created -- with many thanks to Prof Soper! I'm providing links to his excellent lectures to my students in the CS240 Database course at Norwich University as a supplement to our in-class work. And BTW, I've been teaching database theory and practice since 1981! Dr Soper's clarity and detailed explanations are THE BEST!! Notes by M. E. Kabay, PhD, CISSP-ISSMP 0:00:12 Topic Objectives 0:00:41 CRUD defined 0:01:07 Structured Query Language 0:03:28 Applications of SQL 0:05:47 SQL for Data Definition 0:06:44 CREATE 0:19:01 ALTER 0:24:08 UPDATE 0:26:54 DELETE 0:28:44 SELECT 0:42:22 BETWEEN 0:43:56 LIKE 0:46:51 ORDER 0:50:29 COUNT / MIN / MAX 0:50:59 SUM / AVG / STDEV 0:53:05 GROUP BY 0:54:48 GROUP BY with HAVING 0:56:29 Subqueries 0:57:57 Noncorrelated subquery 1:00:24 Correlated subquery 1:03:09 Joins 1:03:35 Typology of DB joins 1:05:48 INNER JOIN...ON 1:07:33 LEFT OUTER JOIN 1:11:50 FULL OUTER JOIN 1:12:25 DROP 1:13:49 ALTER & DROP 1:14:43 CHECK 1:16:01 VIEW

[@wendy_doll]

THANK YOU SO MUCH Michael, you don't know how tremendously this list helps!

[@DaveBabler]

SELECT 'Lesson #3 of 8'' FROM 'Database Lesson' WHERE 'Dr. Soper is awesome and these database videos have been extremely helpful' = TRUE ;

[@Motassem.K]

putting names from the big bang theory was the awesomest xD

[@aristidesmandinuto1111]

Great comment 👏

[@Bille747]

You are not getting enough views for the quality of the videos you make. Really good job man, thanks a lot!

[@abygamezone]

He is now getting

[@m4nu3l30]

Not many people used database management

[@skillspanda4490]

No words are enough to appreciate your excellent teaching!!! Thanks a ton.

[@markweeks1308]

Dr. Soper, I have just found this series of tutorials and I am so pleased with them, so far. It is refreshing to hear things explained clearly using correct and accurate language that I don't have to mentally correct before I can understand. The pace is slow enough for me to ingest and understand the material and it makes perfect sense the way to describe everything. I am learning more from the series than I have from any instruction in a long time. Thank you SO much!

[@chantegainey5002]

These are by far the best training videos i have come across period. The illustrations help out tremendously to tie everything together. I took a practice test a couple of weeks ago and got a 40%. Just getting through your first 2 training videos and half of #3 i was able to score an 80%. I am really grateful!

[@captainkeyboard1007]

I intend to watch this show again so that I may appreciate the value of the Structured Query Language. This is to enable me to appreciate Microsoft Access more.

[@isaacagyeman7089]

Dr. Soper, your tutorials clearly deserve tens of thousands of views. You display such enormous knowledge and clarity in your teaching. In fact, you're a world class tutor. No doubt.

[@aquilesmarcano4386]

This is what I call a real lecture! Well Done!

[@bop78]

Super duper thankful for you and all your time and effort you pour into these. Your a lifesaver. I finally understand complex topics of database!!

[@DeniseCovingtonJustAsIam]

By far the best intro I have heard. Is better then my prof who taught me 10 years go.

[@jimc1499]

Thank you Dr. Soper still reviewing your course. Very deliberate material.

[@karnabudhathoki6289]

Thank you Dr Soper for the lessons. They are very informative and crystal clear.......... I am just trying to learn Database on my own, and I hope these lessons give me a good understanding.....

[@kingsleyakhimien5132]

Fantastic video lesson. Thumbs up Dr. Soper...you are an excellent tutor. Highly recommended!

[@brightforeigner]

This is so awesome! Thank you, Dr. Soper. Your lessons are so clear, and easy to understand- broken up into digestible units.

[@lijessica8392]

Thank you so much, Dr. Soper, it is so informative. You are the best instructor.

[@pordos1]

Thank you for your work. I have been trying to understand how to create a database and have seen many different turtorials. This is by far the best study material.Very easy to understand and it provides the basic foundation for creating any database system. You are the best:)! .

[@firefoxx46]

Prof. Dr. Soper, Thank you very much for your time and effort in making these Excellent tutorials . Highly appreciated.

[@abduihamidwaasi]

This is what I have been looking for in my entire preparatory career into web development. Thank you so much, Dr.

[@JL-qf3hq]

This database series is superb. Very good examples that I can easily relate to in a business function.

[@Jim-Robbins]

I have learned so much about SQL and DBMS in his videos, and I am about to start the fourth video in this series. Great instructional videos . Thank you!

[@tonybiscontri835]

Hi doc this is my 3rd lesson wow your so ezy to understand and you really explain things in simple terms thank you ..wish i had teachers like you back in my school days

[@explorertoad8882]

BEST SQL LECTURE EVERRRRR

[@svetlanakardan3801]

loved the pace of delivery and the quality of content. really helpful!!! Thank you so much! I finally understood those right/left/full joins. believe in impossible!

[@plahdhdhdbdyfucnd]

I enjoyed the hidden gems of tv show references. :)

[@carolynwatkins-taylor1334]

Thank you so much, Dr. Soper, for giving a very clear instruction of this subject. All lessons are helping me tremendously!

[@mr.medusa4534]

Thank you, I have a midterm tomorrow on databases and I'm at work so I can't study. I've been listening to ALL of these database videos while I work. They've been very helpful

[@TranscendingStigma]

Just as all other tutorials on DB by Dr Soper, this too surpasses all similar tutorials by others I've come across.

[@markusenglund4622]

Thank you Daniel. This series has been superb thus far!

[@iliasvlachos3842]

AGREE 256% with Dave Babler SELECT 'Lesson #3 of 8'' FROM 'Database Lesson' WHERE 'Dr. Soper is awesome and these database videos have been extremely helpful' = TRUE

[@marwanmohamed6575]

this is the best thing i seen on youtube so far

[@yuyuqian8486]

I love this series! So straight and clear. I could say, It must acquire a lot of effects! THX, sincerely.

[@kalhowl]

Great tutorial. Learned more here than from instructor!

[@elham-elliem7123]

Dr Soper , thank you very much for such a great tutorial , you explained perfectly in details and examples , very great well structured slides , and you used every seconds efficiently ! These are not only true for this section it is also true for all of the rest 7 tutorials . I have final exam in database ahead which these series are perfect to help me ! You also have a comforting, nice and clear voice 😉

[@akankshadwivedi1998]

this is the best video and training for any student who have no idea about SQL...!!!! I bet there is no better crash course video than this that too in such a small time period.. now i need not look at the book also😍😍😍 thank u soo mch❤😊😊😊😊

[@Shredz47]

you are a life saver DR.Soper

[@darrenking9831]

One of my classes that I am taking is similar to what you are teaching it is called Hands on Database. This video is helping me to understand more of the concepts. thank you

[@polarisk9096]

Thank you so much! This is more well-organized than our lecture

[@Happymoon789]

Thank you very much Dr Daniel Soper! I really enjoy your class, easy, clear, fun.👍It's powerful, helpful!

[@gvardado]

+Julie Jiang nothing fun my stupid girl

[@snotgobbeler]

this is a fantastic tutorial, very high quality, much appreciated!

[@kaizhou8278]

Really high quality lessons, thank you so much Dr.

[@olim93]

Watch these videos at 2x video speed

[@meichai271]

You are doing way better job in explaining than my teacher!

[@priyaponnus8620]

Wonderful explanation. Thanks, Daniel Soper.

[@r.a.p.h4481]

Wow, this is an outstanding lecture!

[@lexuanhoang1109]

Very helpful. You make complex things easy to understand. Thanks a lot.

[@ilehsh30]

Thanks Dr. Soper, it was very interesting.do you have text notes for the lesson.I am also interested if you are teaching online full course on SQL and oracle database 11g fundamental I and II. free or paid.

[@JunaidAhmed-ox2xd]

your way of explaining is really awesome, thanks for the video. looking forward for more videos on java as well

[@joncyrus1350]

Thank you, Dan! Also, I enjoyed the reference and chuckled at your Skills self-assessment :)

[@lingdaosha8133]

Thanks for sharing this video, this is great for learning!

[@mthuthuzeliseptember380]

Excellent lesson Dr Soper. Thank you for you help.

[@2002egncn]

Really amazing set of lectures. Thanks so much!

[@ltManifesto]

THANK YOU SO MUCH FOR THIS VIDEO BECAUSE MY PROFESSOR IS TRASH...DOESNT EVEN TEACH IT THROUGH THE ACTUAL SOFTWARE OR THE ACTUAL CODES HE JUST LECTURES AND ASSUMES WE KNOW HOW TO DO THIS.

[@jayant9151]

Same here

[@davidpotesta]

if you're working along with this tutorial using MySQL, you'll discover that there is no FULL OUTER JOIN in MySQL. To simulate this, you would use a UNION. Dr. Soper's example would look as follows at 1:12:00 --- SELECT empName,deptName FROM Employee e LEFT OUTER JOIN Department d ON e.deptId = d.deptId UNION SELECT empName,deptName FROM Employee e RIGHT OUTER JOIN Department d ON e.deptId = d.deptId;

[@TechWithBeni]

wow great teacher thanks for the video . you really can explain things clearly

[@123weeral]

I learned a lot from these videos , thank you.

[@mohameda.k.lengah9514]

Am really enjoying the Database Lesson

[@mershawubie1053]

It is really intersting video. Thank you providing such interesting topics.

[@varunsudan1]

Awesome job Dr. Soper

[@piyush5903]

cleanly and clearly explained !!!

[@auwalusman8375]

hi you are too much, God bless u I learn a lot from u. more of these sir.

[@TheRasta25]

You are the best thank you for making supper clear and helpful videos

[@jayolson2598]

you are just awesome! thanks for the video

[@Gantumursengee]

I am so pleased to learn from Dr. Sopers lesson. From lesson 3, I feel that I need to do some exercises. Is there any proposition for exercises?

[@Blue4736.]

============================== **INITIAL INTRO TO SQL, TABLES, KEYS AND REFERENCING OTHER TABLES** ============================== 3:42 A. What are the three major components of SQL? 6:05 B. What are the data definition statements? What do they do? 6:51 C. How do you [CREATE] a [TABLE]? D. How do you add PK or FK [CONSTRAINTS]? 10:20 E. [primary key] 13:10 F. [foreign key] 16:05 G. [REFERENCES DELETE CASCADE] H. [REFERENCES UPDATE CASCADE] ============================== **MAKING CHANGES TO A TABLE** ============================== 19:13 I. How do you ALTER a table? J. How do you change data in a table? 21:45 K. INSERT INTO 24:13 L. UPDATE 27:00 M. DELETE FROM 1:13:00 N. DROP O. ALTER TABLE ================================== **HOW DO YOU QUERY A TABLE?** ================================== P. 28:55 Q. SELECT single column 31:25 R. What is the result of a SELECT query? 32:51 S. SELECT multi columns 34:40 T. SELECT DISTINCT 35:30 U. WHERE 36:34 V. Available comparisons/filtering 46:52 W. ORDER BY ================================== **HOW DO YOU IMPLEMENT THESE KEYWORDS/ FUNCTIONS** ============================== 37:30 X. AND / OR 40:00 Y. IN / NOT IN 42:32 Z. BETWEEN 44:00 AA. LIKE 44:35 BB. Wildcards CC. How do you use these built in functions? 50:40 DD. COUNT / MIN / MAX EE. SUM / AVG / STDEV ================================== **MISC** ================================== 53:10 FF. GROUP BY 55:10 GG. HAVING 56:30 HH. SUBQUERIES II. What are NON-CORRELATED subqueries? 1:00:22 JJ. What are CORRELATED subqueries? ================================== **MISC** ================================== 1:03:12 KK. JOINS 1:07:00 LL. What is the difference between INNER and OUTER JOINS 1:15:00 MM. CHECK 1:16:10 NN. What are VIEWS?

[@samanthapikus3178]

Thank you very much for posting these videos..

[@jonesboadu9585]

Dr you too good, wish you run an online school. just cant wait to join

[@myheaven5416]

Very Helpful video..., Thank you so much... Hat off for you, Sir...

[@GurnBograt1986]

Excellent information!! Thank you!!

[@stefturelinckx7324]

very helpfull series so far, thanx!

[@albertshin4859]

Thank you! Awesome videos!

[@TeamBrokeOff]

Watch this series at 2x speed. As a person on the spectrum, this helps processing the speech patterns.

[@yellowsunshine65]

excellent tutorials about database.

[@spinn360]

Thanks! I am getting more info in your videos than I am in my school book. Also like the Big Bang subliminal messaging!

[@munyafiction]

too good! thanks so much!

[@chenliu6279]

database is so much easier for me after watching these 8 lectures, thank you so much. just want to know if there is some place i can down load your ppt?

[@mikestubeviews9973]

Love this series so far. On other videos I have seen people add say PK or FK without actually typing constraint. Is it required?

[@amywang1212]

Excellent lecture!

[@josearieldelosreyes2627]

Thank you very much for sharing this video.👍

[@karnabudhathoki5311]

Thanks...Dr...I learned....a lot.....

[@graceanderson3910]

Thank you Sir for your incredible natural.

[@Karmidzhanov92]

Hello, can you use ‘HAVING totalSales >=10000’ as you already assigned an alias to the result of SUM? Thank you

[@abdullahhuseein2364]

i just wanna tell u Dr: daniel you funtastic and than u so much for prepared this great lucture

[@shazkingdom1702]

Very nice Tutorial!

[@paulasubonteng-manu7248]

wow ..simply wow.

[@nelsonlim1389]

Thank you for sharing this video.

[@11brainy]

I wonder how this video got 1 dislike, must be a dummy

[@yellowsunshine65]

or someone with envy of his teaching skills.

[@MrYosssup]

4 years later, the dumbasses count got up to 21

[@julzedwin3775]

😄😂

[@TheseEyesSeeDarknessClearly]

ManBearPig Now it’s 29. New dumbasses stumble on this video every year lol

[@akhiln1370]

meets the expectations of learners :)

[@MinhVu-ys4hp]

I think there's a slight mistake at 25:33 because it should be the value of the Department ID that's modified, not the Employee ID. Am i right?

[@zaidy5391]

Dear Professor, is it possible to get the power point slides? and thank you for the videos, they are great...

[@kaiwang89]

hi dr. soper, thank you for your tutorial! just wondering if you provide the slides for us to download?

[@ArtR0001]

It has always been my understanding that dashes (-) should NOT be stored in the database unless it is part of a proper name or address. That the User Interface Application should display those as one of it's functions. Likewise, not all numbers should be stored as a numeric datatype UNLESS one plans on performing arithmetic with the stored data.. This is especially true for something like a UPC or EAN code where the leading zeros will be lost if stored as a numeric datatype. Storing this as Character datatype will retain the leading zeros and a proper SORT can be performed. Perhaps the first example is a holdover from days when storage cost an arm and a leg, but I still abide by that rule. In the second example, the User Interface can verify that the user is actually imputing numeric data (the UPC or EAN code) before passing the data to the DBMS.

[@abhishekjain3527]

After watching the first two tutorials liked it before watching it

[@rttharani9033]

Thank you so much great learning

[@daveramai2408]

just excellent.

[@Puerto_Ayacucho_VE]

What is the benefit of using integer for Primary Key instead of char?

[@NicolasIbarra]

Quick question: Is SQL Data Retrieval inside the limits of Data Modification Language or does it belongs to Data Control Language? Just to be clear... Thank you Daniel Soper, these videos are amazing!!

[@kabirbukhari4731]

***** All the CRUD (Create, Read, Update, Delete) related stuff is all DML. DCL is related more with the admin-type stuff. When I say admin type stuff, I mean installing the DBMS, setting up users on the database, making sure what components of the database each user uses, making sure the database is secure, so on and so forth. Typically, the person who is the the Database Administrator (DBA) deals with DCL. The Data Analyst/Developer typically deals with DML and DDL.

[@NicolasIbarra]

Thanx Kabir!

[@Mintos93]

Daniel Soper Works with Sheldon Cooper and Penny?

[@jdhung]

very good database tutorial

[@DaveDugganITPro]

awesome excellence thank you