Untitled


use cia;

/*Oppgave 1*/

/*Velg ut kolonner:*/
select * from cia;
select name, region, gdp from cia;
select name, region, area, population from cia;
select name, population/area from cia;
select name, population/1000000 fom cia;

/*Velg ut rader:*/
select * from cia where region = "Europe";
select name from cia where population = 0;
select name from cia where population >15000000;
select name from cia where region = "Europe" AND population >15000000;
select name from cia where region = "Europe" OR region = "Asia";

/*Velg ut etter mønstre i visse kolonner:*/
select name from cia where name LIKE "b%";
select name from cia where name LIKE "__d%";
select name from cia where length(name) = 4;
select name from cia where name LIKE "%United%";
select name from cia where name LIKE "%ia";

/*Sortering:*/
select name from cia order by name;
select name from cia order by region,name;
select name from cia where population >100000000 order by population;
select name, population from cia order by region, population desc;

/*Aggegering:*/
select COUNT(name) from cia;
select COUNT(name) from cia where region="Europe";
select SUM(population) from cia where region="Europe";
select region, AVG(population) from cia GROUP BY region;
select COUNT(name) from cia where region="Asia";
select SUM(AREA) from cia where population = 0;
select MAX(population) from cia;

/*Gruppering:*/
select region, SUM(area) from cia GROUP BY region;
select region, AVG(area) from cia GROUP BY region;
select region, MAX(area) from cia GROUP BY region;
select region, COUNT(name) from cia GROUP BY region;


/*Oppgave 2*/

select region, SUM(population) from cia GROUP BY region;
select region from cia where region LIKE "%e%" group by region;
select SUM(population) from cia where region LIKE "n%";
select SUM(area) from cia where population>10000000;
select name from cia where population>1000000 or area>10000;
select name from cia where CHAR_LENGTH(name) = 7;
select name from cia where population>area;


/*Oppgave 3*/

/*
<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title>xdd</title>
  </head>
  <body>

    <h1>Søk</h1>

    <?php
    $server = "localhost";
    $username = "root";
    $password = "";
    $dbname = "cia";

    // Create connection
    $conn = mysqli_connect($server, $username, $password, $dbname);

    // Check connection
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }
    ?>

    <p>Denne siden sender SQL kommandoen: "select name from cia where name LIKE "b%";"</p>

    <?php
    if ($_SERVER['REQUEST_METHOD'] == 'POST'){
      $sql = 'select name from cia where name LIKE "b%"';
      $results = mysqli_query($conn, $sql);
      if (!$results) {
          printf("Error: %s\n", mysqli_error($conn));
          exit();
      }
      while($row = mysqli_fetch_array($results)){
        echo $row["name"] . "<br>";
      }
    }
    ?>

  </body>
</html>

*/


/*Oppgave 4*/
/*
<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title>xdd</title>
  </head>
  <body>

    <h1>Søk</h1>

    <?php
    $server = "localhost";
    $username = "root";
    $password = "";
    $dbname = "cia";

    // Create connection
    $conn = mysqli_connect($server, $username, $password, $dbname);

    // Check connection
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }
    ?>

    <form method="POST">
      Søk:<br>
      <input type="text" name="search"><br>
    </form>

    <table>
      <tr>
      <th>Name</th>
      <th>Region</th>
      <th>Area</th>
      <th>Population</th>
      <th>GDP</th>
      </tr>
    <?php
    if ($_SERVER['REQUEST_METHOD'] == 'POST'){
      $sql = 'SELECT * FROM cia where name = "'.$_POST["search"].'"';
      $results = mysqli_query($conn, $sql);
      if (!$results) {
          printf("Error: %s\n", mysqli_error($conn));
          exit();
      }
      while($row = mysqli_fetch_array($results))
      {

        echo "<tr>";
        echo "<td>" . $row['name'] . "</th>";
        echo "<td>" . $row['region'] . "</td>";
        echo "<td>" . $row['area'] . "</td>";
        echo "<td>" . $row['population'] . "</td>";
        echo "<td>" . $row['gdp'] . "</td>";
        echo "<tr>";
      }
    }
    ?>
    </table>

  </body>
</html>
*/