Help Center/ TaurusDB_TaurusDB for PostgreSQL/ Kernel/ Babelfish/ Examples of Common SQL Statements
Updated on 2025-11-19 GMT+08:00
View PDF
Share

Examples of Common SQL Statements

Data Query

  • Basic query 
    SELECT * FROM users;
  • Conditional query 
    SELECT name, age FROM users WHERE age > 30;
  • Sorting 
    SELECT * FROM users ORDER BY create_time DESC;
  • Group aggregation 
    SELECT age, COUNT(*) FROM users GROUP BY age;
  • TOP syntax 
    SELECT TOP 10 * FROM users;

Data Insertion and Modification

  • Inserting data 
    INSERT INTO users (name, age) VALUES ('Alice',  25);
  • OUTPUT syntax 
    INSERT INTO t(name) OUTPUT INSERTED.* VALUES('abc');
  • Updating data 
    UPDATE users SET age = age + 1 WHERE name = 'Alice';
  • Deleting data 
    DELETE FROM users WHERE age < 18;

Table Operations

  • Creating a table 
    CREATE TABLE users (id INT PRIMARY KEY, name NVARCHAR(50), age INT);
  • Dropping a table 
    DROP TABLE users;
  • Adding a column 
    ALTER TABLE users ADD email NVARCHAR(100);
  • Deleting a column 
    ALTER TABLE users DROP COLUMN email;
  • Modifying a column type 
    ALTER TABLE users ALTER COLUMN age SMALLINT;
  • Creating a global temporary table 
    CREATE TABLE ##tmp(x INT);
  • Creating a local temporary table 
    CREATE TABLE #tmp(x INT);

Data Integrity and Indexes

  • Adding a primary key 
    ALTER TABLE users ADD CONSTRAINT pk_users PRIMARY KEY (id);
  • Creating a unique index 
    CREATE UNIQUE INDEX idx_users_name ON users(name);
  • Adding a foreign key 
    ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id);

Views and Stored Procedures

  • Creating a view 
    CREATE VIEW adult_users AS SELECT * FROM users WHERE age >= 18;
  • Dropping a view 
    DROP VIEW adult_users;
  • Creating a stored procedure 
    CREATE PROCEDURE get_adult_users AS
SELECT * FROM users WHERE age >= 18;
  • Executing a stored procedure 
    EXEC get_adult_users;

Database Operations

  • Querying the version 
    SELECT @@version;
  • Switching over the database 
    USE testdb;
Parent Topic: Babelfish