DB Operate

index<--

DB Operate

There are several regular operations we do against a database. This is: add new data, query data and update or delete data. For this we use DML = "Data Manipulation Language". The person who use DML is called database operator.

DML: INSERT

This SQL command is used create new data into a table. The new data can be created using data literals or can be procured from data sources. You can insert one row or multiple rows in batch. Data source can be a query that retrieve data from pther tables, views or files.

Example:

Remember our Agile database? Let's try to insert some data in our tables. First thing we need is a team. Then one or more developers, then a project. If this use-case fail we have to review our database structure.

-- demo script for adding new data
START TRANSACTION;

INSERT INTO team 
    (name)
VALUES
    ("Sage-Code");

SET @team_id = LAST_INSERT_ID();

INSERT INTO developer 
    (team_id, name, skill)
VALUES
    (@team_id,"Elucian Moise", "SQL, Rust, Julia, Python");

INSERT INTO developer 
    (team_id, name, skill)
VALUES
    (@team_id,"Laura Moise", "SQL, Java");

COMMIT;

Note:In this script I have also used commands START TRANSACTION and COMMIT that are part of TCL. We can combine many SQL commands into one comprehensive script that can be run against a database to create new data.

DML: SELECT

The SELECT command is used create a query statement. This can retrieve data from one or many data sources. The data source can be a local or remote table or view. A query has a result dataset that looks like a readonly table.

Example:

Now we have data so we can create our first query.

-- query tabe: developer
SELECT * FROM developer;

Output:


1,1,"Elucian Moise","SQL, Julia, Rust"
2,1,"Laura Moise","SQL, Java"

Homework: open the actual csv file:developer.csv

SQL: WHERE

Things start to get intresting. We will create our first join between two tables by using WHERE query clause. Usual the join is made between primary key of master table and the foreign key of detail table. This is one of many clauses you can use in a select statement.

-- query tabe: developer
SELECT  
    team.name as team_name, 
    developer.name as developer_name       
  FROM developer, team 
 WHERE team.id = developer.team_id;

Output:


"Sage-Code","Elucian Moise"
"Sage-Code","Laura Moise"

Note: The output actually depends on the tool you are using. Some tools enable you to save the data result on the disk as file in a particular format that you chose. These tools are using the SQL dataset and transform the data internaly before display pr export.

Homework: open the actual csv file:members.csv

SQL: DELETE

Sometimes we need to remove one or more records. For this we use DELETE with WHERE clause. In the next example we start a transaction and we remove data. After this we COMMIT to make modifications permanent.

-- clear data from tables
START TRANSACTION
DELETE FROM developer WHERE id = 2;
DELETE FROM developer WHERE id = 1;
DELETE FROM developer TEAM;
COMMIT;

Read next: Maintain