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.