Master SQL Basics: From Zero to Simple Queries with Real Examples

What Is SQL?

SQL (Structured Query Language) is the standard language for managing relational database management systems (RDBMS). It lets you access and manipulate data, including inserting, querying, updating, and deleting records.

Simple SELECT Example

A quick way to see the current database time is using the SELECT SYSDATE FROM DUAL statement, which returns the system date and time. SELECT SYSDATE FROM DUAL; You can also use it as a calculator, e.g., SELECT 365 * 24 FROM DUAL to compute the number of hours in a year.

SELECT 365 * 24 FROM DUAL;

Creating a Table

Define a table to store data. The example creates a bookshelf table with columns for book ID, name, type, author, and entry date.

CREATE TABLE bookshelf (
  BOOK_ID NUMBER,
  BOOK_NAME VARCHAR2(100),
  BOOK_TYPE VARCHAR2(100),
  AUTHOR VARCHAR2(100),
  INTIME DATE
);

Basic CRUD Operations

Insert

Insert a single book record using INSERT INTO and commit the transaction.

INSERT INTO bookshelf (book_id, book_name, book_type, author, intime)
VALUES (1, '飘', '长篇小说', '玛格丽特·米切尔', SYSDATE);
COMMIT;

Update

Correct a mistake by updating the author name.

UPDATE bookshelf SET author = 'Margaret Mitchell' WHERE book_name = '飘';
COMMIT;

Delete

Remove a specific book from the table.

DELETE FROM bookshelf WHERE book_name = '从你的全世界路过';
COMMIT;

Using WHERE Clauses

The WHERE clause filters rows that meet a condition. Example: find the book titled "倾城之恋".

SELECT * FROM bookshelf WHERE BOOK_NAME = '倾城之恋';

Batch Inserts and Queries

Insert multiple books at once and query them individually.

INSERT INTO bookshelf (book_id, book_name, book_type, author, intime) VALUES
  (1, '飘', '长篇小说', '玛格丽特·米切尔', SYSDATE),
  (2, '倾城之恋', '爱情小说', '张爱玲', SYSDATE),
  (3, '从你的全世界路过', '短篇小说', '张嘉佳', SYSDATE);
COMMIT;

SELECT * FROM bookshelf WHERE BOOK_NAME = '倾城之恋';

Fun SQL Graphics

SQL can generate simple ASCII‑style graphics. Below are queries that draw a five‑pointed star, Olympic rings, and a monthly calendar using hierarchical queries and CONNECT BY clauses.

-- Five‑pointed star (adjust the number 5 to draw other polygons)
WITH a AS (
  SELECT DISTINCT round(SUM(x) OVER (ORDER BY n)) x,
                  round(SUM(y) OVER (ORDER BY n)) y
  FROM (
    SELECT n,
           cos(trunc(n/20)*(1-1/5)*3.1415926)*2 x,
           sin(trunc(n/20)*(1-1/5)*3.1415926) y
    FROM (SELECT rownum-1 n FROM all_objects WHERE rownum <= 20*5)
  )
)
SELECT REPLACE(sys_connect_by_path(point,'/'), '/', NULL) star
FROM (
  SELECT b.y, b.x,
         DECODE(a.x, NULL, ' ', '*') point
  FROM a, (
    SELECT * FROM (
      SELECT * FROM (
        SELECT rownum FROM all_objects WHERE rownum <= (SELECT MAX(x)-MIN(x)+1 FROM a)
      )
    )
  ) b
WHERE a.x(+) = b.x AND a.y(+) = b.y
START WITH x = (SELECT MIN(x) FROM a)
CONNECT BY PRIOR y = y AND PRIOR x = x+1;

-- Olympic rings (similar structure, different trigonometric parameters)
-- (code omitted for brevity)

-- Print current month calendar
SELECT MAX(DECODE(dow,1,d,NULL)) sun,
       MAX(DECODE(dow,2,d,NULL)) mon,
       MAX(DECODE(dow,3,d,NULL)) tue,
       MAX(DECODE(dow,4,d,NULL)) wed,
       MAX(DECODE(dow,5,d,NULL)) thu,
       MAX(DECODE(dow,6,d,NULL)) fri,
       MAX(DECODE(dow,7,d,NULL)) sat
FROM (
  SELECT rownum d,
         rownum-2+TO_NUMBER(TO_CHAR(TRUNC(SYSDATE,'MM'),'D')) p,
         TO_CHAR(TRUNC(SYSDATE,'MM')-1+rownum,'D') dow
  FROM all_objects
  WHERE rownum <= TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(SYSDATE)),'DD'))
)
GROUP BY TRUNC(p/7)
ORDER BY sun NULLS FIRST;

Conclusion

After completing these examples, you should understand the four basic CRUD operations in SQL and feel confident to start experimenting with your own queries and tables.