SQL Basics: Create table and insert data

Create database

To create a database in SQL, you can use the CREATE DATABASE statement. Here is an example:

CREATE DATABASE mydatabase;

Create table

Here is an example of a CREATE TABLE statement in SQL:

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    password TEXT NOT NULL,
    email TEXT NOT NULL
);

This creates a table called users with four columns: user_id, username, password, and email. The user_id column is an integer and is the primary key for the table, meaning that it is unique for each row. The username, password, and email columns are all text values and cannot be NULL.

Insert data

To insert data into the table, you can use an INSERT statement like this:

INSERT INTO users (username, password, email)
VALUES ('john', 'password123', 'john@example.com');

Update statement

To update a record in a database table, you can use the UPDATE statement in SQL. Here is the general syntax for an UPDATE statement. For example, to update the email address of a user with a specific user_id in the users table, you can use the following UPDATE statement:

UPDATE users
SET email = 'newemail@example.com'
WHERE username = 'john';

Delete records

To delete data from a database table in SQL, you can use the DELETE statement. Here is the general syntax for a DELETE statement. For example, to delete a user with a specific user_id from the users table, you can use the following DELETE statement:

DELETE FROM users WHERE user_id = 123;

This will delete the row with user_id 123 from the users table.

And for complete example list this query will retrieve all columns from the "users" table, order the rows by the id column in descending order and return only the first 5 rows from the result set.

SELECT * FROM "users" ORDER BY id DESC LIMIT 5;