オンラインSQLエディタ

-- Basic SELECT query
SELECT 'Hello' AS greeting, 42 AS answer;

-- Select with WHERE
SELECT * FROM sqlite_master WHERE type = 'table';

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age INTEGER,
  grade REAL
);

INSERT INTO students VALUES (1, 'Alice', 20, 3.8);
INSERT INTO students VALUES (2, 'Bob', 22, 3.5);
INSERT INTO students VALUES (3, 'Charlie', 21, 3.9);

SELECT * FROM students;

CREATE TABLE products (id INT, name TEXT, price REAL, category TEXT);
INSERT INTO products VALUES
  (1, 'Laptop', 999.99, 'Electronics'),
  (2, 'Book', 19.99, 'Education'),
  (3, 'Phone', 699.99, 'Electronics'),
  (4, 'Pen', 2.99, 'Office');

SELECT name, price FROM products
WHERE price > 20 AND category = 'Electronics';

CREATE TABLE sales (product TEXT, region TEXT, amount REAL);
INSERT INTO sales VALUES
  ('Widget', 'North', 100), ('Widget', 'South', 150),
  ('Gadget', 'North', 200), ('Gadget', 'South', 80);

SELECT product, SUM(amount) AS total
FROM sales
GROUP BY product
ORDER BY total DESC;

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);

-- Insert rows
INSERT INTO users VALUES (1, 'Alice', 'alice@example.com');
INSERT INTO users VALUES (2, 'Bob', 'bob@example.com');

-- Update a row
UPDATE users SET email = 'alice@newmail.com' WHERE id = 1;

SELECT * FROM users;

CREATE TABLE scores (student TEXT, subject TEXT, score INT);
INSERT INTO scores VALUES
  ('Alice', 'Math', 92), ('Alice', 'Science', 88),
  ('Bob', 'Math', 78), ('Bob', 'Science', 95);

SELECT student,
  AVG(score) AS average,
  MAX(score) AS best,
  MIN(score) AS worst
FROM scores GROUP BY student;

CREATE TABLE employees (id INT, name TEXT, salary REAL, dept TEXT);
INSERT INTO employees VALUES
  (1, 'Alice', 90000, 'Eng'), (2, 'Bob', 75000, 'Mktg'),
  (3, 'Charlie', 95000, 'Eng'), (4, 'Diana', 80000, 'Mktg');

SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

CREATE TABLE orders (id INT, total REAL, status TEXT);
INSERT INTO orders VALUES
  (1, 250, 'shipped'), (2, 50, 'pending'), (3, 500, 'delivered');

SELECT id, total,
  CASE
    WHEN total >= 200 THEN 'Large'
    WHEN total >= 100 THEN 'Medium'
    ELSE 'Small'
  END AS size
FROM orders;

CREATE TABLE logs (id INTEGER PRIMARY KEY, ts TEXT, message TEXT, level TEXT);
INSERT INTO logs VALUES
  (1, '2024-01-01', 'App started', 'INFO'),
  (2, '2024-01-01', 'Error occurred', 'ERROR'),
  (3, '2024-01-02', 'Request received', 'INFO');

CREATE INDEX idx_level ON logs(level);

SELECT * FROM logs WHERE level = 'ERROR';

CREATE TABLE depts (id INT, name TEXT);
INSERT INTO depts VALUES (1,'Engineering'),(2,'Marketing');

CREATE TABLE emps (id INT, name TEXT, dept_id INT, salary INT);
INSERT INTO emps VALUES (1,'Alice',1,90000),(2,'Bob',2,75000),(3,'Charlie',1,95000);

SELECT e.name, d.name AS department, e.salary
FROM emps e
INNER JOIN depts d ON e.dept_id = d.id
ORDER BY e.salary DESC;