Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision Both sides next revision
birds_databaser [2014-04-14 20:58]
Joakim Forsgren 		birds_databaser [2015-04-24 13:14]
Joakim Forsgren
Line 108: Line 108:
 SELECT * FROM Birds; SELECT * FROM Birds;
  
-2.+9.
 SELECT * FROM Observations  SELECT * FROM Observations 
 WHERE UserID=3; WHERE UserID=3;
  
-3.+10.
 SELECT * FROM Users INNER JOIN Observations USING(UserID) SELECT * FROM Users INNER JOIN Observations USING(UserID)
 WHERE Login='JF'; WHERE Login='JF';
  
-4.+11.
 SELECT Name,COUNT(UserID) as Antal  SELECT Name,COUNT(UserID) as Antal 
 FROM Birds INNER JOIN Observations USING(BirdID)  FROM Birds INNER JOIN Observations USING(BirdID) 
Line 123: Line 123:
 ORDER BY Antal DESC; ORDER BY Antal DESC;
  
-SELECT Name,COUNT(UserID) as Antal  FROM Birds INNER JOIN Observations USING(BirdID)  WHERE UserID=1  GROUP BY BirdID  having Antal>4 ORDER BY Antal DESC;+12. 
 +SELECT Name,COUNT(UserID) as Antal  FROM Birds INNER JOIN Observations USING(BirdID)  WHERE UserID=3  GROUP BY BirdID  having Antal>4 ORDER BY Antal DESC;
  
-5.+14.
 SELECT CONCAT(FirstName,' ',LastName) as Name,COUNT(DISTINCT BirdID) as Antal  SELECT CONCAT(FirstName,' ',LastName) as Name,COUNT(DISTINCT BirdID) as Antal 
 FROM Users INNER JOIN Observations USING(UserID)  FROM Users INNER JOIN Observations USING(UserID) 
Line 131: Line 132:
 ORDER BY Antal DESC; ORDER BY Antal DESC;
  
-6.+15.
 SELECT Name,COUNT(DISTINCT UserID) as Antal  SELECT Name,COUNT(DISTINCT UserID) as Antal 
 FROM Birds INNER JOIN Observations USING(BirdID)  FROM Birds INNER JOIN Observations USING(BirdID) 
Line 137: Line 138:
 GROUP BY BirdID ORDER BY Antal DESC; GROUP BY BirdID ORDER BY Antal DESC;
  
-7.+16.
 SELECT CONCAT(FirstName,' ',LastName) as Name,COUNT(DISTINCT BirdID) as Antal  SELECT CONCAT(FirstName,' ',LastName) as Name,COUNT(DISTINCT BirdID) as Antal 
 FROM Users INNER JOIN Observations USING(UserID)  FROM Users INNER JOIN Observations USING(UserID)