DAT702 W4L1

DAT702


create table marvin select * from grades;
copies new table, does not copy constraint
create table marcin2 select grades,stunum,code
create table marvin3 select * from grades where 1 = 1
create marvin three and get the same thing where this is very usefull as we create marvin4
1= 21 this will copy an empty table, but its the same table.

“We just did table copy” ExamHint -table alias will be important

Select What about the repeated names” AnotherExamHint


In lab 3 like a list of the same students as the first name of all the other students were lookinga the students that have the same first name as the other students, think of ur students table, somewhere you have mary and another mary somewhere else at the table, databases process one record at a time, how will it process those two, we are going to deal with this but dealing with one table it will not work, the way we do this, back to the things we talk about before,

Select * from students,students; < Cartesian product when no where clause,

If the students table at 40 record cheat give table alias
Select * from students s1,students s2; < Cartesian product

We are going to only show certain things

Select s1.firstname,s1.lastname,s2.firstname,s2.lastname from students s1.students s2 where s1.firstname = s2.firstname and s1.stunum <> s2.stunum;


Select distinct s1.firstname,s1,lastname,s2.firstname,s2.lastname from students s1.students s2 where s1.firstname = s2.firstname and s1.stunum <> s2.stunum order by s1.firstname;

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Needed

Select “Another example of a join” AnotherHInt;

Select * from grades where code = “hwd101”:

Select stunum. From grades where code = “hwd101”;

Select * from grades g1,grades g2 where g1.code = “hwd101” and g2.code = “ops200”;

Select distinct g1.stunum from grades g1,grades g2 where g1.code = “hwd101” and g2.code = “ops200”;


Select “ Now the easy way” ExamHint;

Select * from student innter join grades on students.stunum = grades.stunum;

If we rewrite and put left join it will show a bunch of people with null

Inner join takes students and student number , left joins is finding the Nulls, if we want to make it nicer you isolate the nulls and do a where clause


Where Grades.code is null


Select “Now the other way” examexam;

Select stunum from grades; 1 column 1 field, every one took a course, its one field,

Select * from students where stunum not in (select stunum from grades);
All people that didn’t take the course, called a sub query

Select * code from grades where stunum in (Select max(grade)from grades where code = “HWD101” and grade in (select max(grade) from grades where code = “hwd101”));

In brackeys sub query, back to front

Select max(grade)from grades where code = “HWD101” and grade in (select max(grade) from grades where code = “hwd101”);


Select students.* from students, grades
Where students.stunum = grades.stunum and grades.code in
(select code from grades where stunum in
(select stunum from grades where code = “hwd101” and grade in
(select max(grades) from grades where code = “hwd101”)));

^^^^end of sql this end of mysql, writing queries, in most term test do something with a left join and a sub query, run a practical test on Thursday,

Want to know everybody who taken ops400 and hwd101

Select stunum from grades where code = “ops400” and stunum in
(Select stunum from grades where code = “HWD101”);

^^^^^^runs is twice did a sub query that took one of courses, runs it again finds the ops400 now it knows each one of those took ops400 and 101

Select * from students where stunum in
(Select stunum from grades where code = “ops400” and stunum in
(Select stunum from grades where code = “HWD101”));

^^^^^^^^^^Shows the whole table

Practical test based on lab four