🦦 ponchos blog.

Example SQL Script

This is a simple example of a SQL script that creates a table, insert data, update the data, select data and delete data from the table.

-- create a new table called "employees"
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  hire_date DATE NOT NULL,
  salary DECIMAL(10, 2) NOT NULL
);

-- insert data into the "employees" table
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary)
VALUES (1, 'John', 'Wicks', 'johnwicks@chapter4.com', '2021-01-01', 50000),
       (2, 'Frodo', 'Baggins', 'frodobaggins@firesmaug.com', '2021-02-01', 55000),
       (3, 'Fred', 'Weasley', 'fredweasley@potter.com', '2021-03-01', 60000);

-- update the email address of an employee
UPDATE employees
SET email = 'johnwicks1@example.com'
WHERE employee_id = 1;

-- select all columns and rows from the "employees" table
SELECT * FROM employees;

-- select only certain columns and rows from the "employees" table
SELECT first_name, last_name, salary FROM employees
WHERE hire_date >= '2021-02-01';

-- delete an employee from the "employees" table
DELETE FROM employees
WHERE employee_id = 3;

It is important to note that the script above is for demonstration purposes only and that it may not be compatible with all SQL implementations.

More detailed information

The first statement creates a new table called "employees" with 6 columns: employee_id, first_name, last_name, email, hire_date, and salary. The employee_id column is defined as the primary key, which means it must be unique and non-null for each row in the table. The other columns are also defined with various constraints, such as NOT NULL and UNIQUE.

The second statement inserts 3 rows of data into the "employees" table. Each row contains values for all 6 columns, and the values are specified using the VALUES keyword.

The third statement updates the email address of the employee with an employee_id of 1. This statement uses the SET keyword to change the value of the email column and the WHERE keyword to specify which row should be updated.

The fourth statement selects all columns and all rows from the "employees" table. This statement uses the SELECT keyword followed by an asterisk (*) to indicate that all columns should be selected, and the FROM keyword to specify the table from which the data should be selected.

The fifth statement selects only certain columns (first_name, last_name, and salary) and certain rows (where the hire_date is greater than or equal to '2021-02-01') from the "employees" table. This statement uses the SELECT keyword followed by the specific column names to indicate which columns should be selected, the FROM keyword to specify the table from which the data should be selected, and the WHERE keyword to specify a condition for selecting the rows.

The last statement deletes a row from the "employees" table where the employee_id is 3. This statement uses the DELETE keyword to delete the row, and the WHERE keyword to specify the condition for the row that should be deleted.

It is important to remember that all SQL statements must be terminated by a semicolon and that the above script is an example, different RDBMS may have different syntax for creating table and doing the CRUD operation.

- 1 toast