Download - Notes on SQL
![Page 1: Notes on SQL](https://reader035.vdocuments.us/reader035/viewer/2022062309/56813ff8550346895dab279b/html5/thumbnails/1.jpg)
Notes on SQL
Please use speaker notes for additional information!
![Page 2: Notes on SQL](https://reader035.vdocuments.us/reader035/viewer/2022062309/56813ff8550346895dab279b/html5/thumbnails/2.jpg)
![Page 3: Notes on SQL](https://reader035.vdocuments.us/reader035/viewer/2022062309/56813ff8550346895dab279b/html5/thumbnails/3.jpg)
![Page 4: Notes on SQL](https://reader035.vdocuments.us/reader035/viewer/2022062309/56813ff8550346895dab279b/html5/thumbnails/4.jpg)
![Page 5: Notes on SQL](https://reader035.vdocuments.us/reader035/viewer/2022062309/56813ff8550346895dab279b/html5/thumbnails/5.jpg)
The table I selected is tablestu so it knows that I am going to SELECT information FROM tablestu. Note that the semi-colon ends SQL statements.
![Page 6: Notes on SQL](https://reader035.vdocuments.us/reader035/viewer/2022062309/56813ff8550346895dab279b/html5/thumbnails/6.jpg)
SELECT * means to select all fields so we see all fields from all records in the table tablestu.
![Page 7: Notes on SQL](https://reader035.vdocuments.us/reader035/viewer/2022062309/56813ff8550346895dab279b/html5/thumbnails/7.jpg)
The code:
SELECT idno, name, major, gpa, credits FROM tablestu;
means that only the named fields will be part of the query.
![Page 8: Notes on SQL](https://reader035.vdocuments.us/reader035/viewer/2022062309/56813ff8550346895dab279b/html5/thumbnails/8.jpg)
SELECT idno, name, gpa, credits, major FROM tablestu;
![Page 9: Notes on SQL](https://reader035.vdocuments.us/reader035/viewer/2022062309/56813ff8550346895dab279b/html5/thumbnails/9.jpg)
WHERE lets me select specific records. In this example, I want to select the records where the major field contains CI.
Note that the output only has the records where the major = “CI” which means that only 7 of the original 14 records are shown.
SELECT idno, name, major FROM tablestuWHERE major = “CI”;
![Page 10: Notes on SQL](https://reader035.vdocuments.us/reader035/viewer/2022062309/56813ff8550346895dab279b/html5/thumbnails/10.jpg)
This query is showing the idno, name, major and gpa for all students with a gpa > 3. Note that I could also have used a decimal, for example gpa > 3.5.
![Page 11: Notes on SQL](https://reader035.vdocuments.us/reader035/viewer/2022062309/56813ff8550346895dab279b/html5/thumbnails/11.jpg)
![Page 12: Notes on SQL](https://reader035.vdocuments.us/reader035/viewer/2022062309/56813ff8550346895dab279b/html5/thumbnails/12.jpg)
This first line got displayed because the major = “CI”. Since the gpa is not > 3.25 it would not have been displayed if both criteria had to be true.
The third line got displayed because gpa > 3.25. Since the major is BU, it would not have been displayed if both criteria had to be true.
![Page 13: Notes on SQL](https://reader035.vdocuments.us/reader035/viewer/2022062309/56813ff8550346895dab279b/html5/thumbnails/13.jpg)
In the AND SELECT and the OR SELECT on the previous slide, we had two questions. When you have 2 questions, there are four possible outcomes and they are handled differently depending on whether there is an AND condition or an OR condition.
AND:
Ques 1 Ques 2
Y Y Processed when Ques 1 AND Ques 2 must both be true
Y N
N Y
N N
Not processed when Ques 1 AND Ques 2 must both be true
OR:
Ques 1 Ques 2
Y Y
Y N
N Y
N N
Processed when Ques1 OR Ques2 must be true
Not processed when Ques 1 OR Ques 2 must be true
![Page 14: Notes on SQL](https://reader035.vdocuments.us/reader035/viewer/2022062309/56813ff8550346895dab279b/html5/thumbnails/14.jpg)
All records have CI as their major since this is a requirement in our criteria.
Record 2 does not meet the gpa requirement but it does meet the credits requirement. Since one or the other had to be true, it is showing.
Record 3 does not meet the credits requirement but it does meet the gpa requirement. Since one or the other had to be true, it is showing.
![Page 15: Notes on SQL](https://reader035.vdocuments.us/reader035/viewer/2022062309/56813ff8550346895dab279b/html5/thumbnails/15.jpg)
Record 5 is outside the range of 20 to 40 because 3 is less than 20.
Record 1 has credits of 15 which means it is less than 20.
Record 2 has credits of 45 which means it is greater than 40