Manipulating Data in Sql

SQL is the standard query language for relational database management systems. Relational database management systems or RDBMS maintain existing and incoming and existing data. It is a method used to interact with a database to manipulate its data.

The database system is made up of objects or tables that contain columns and rows. A good practice for retrieving data is to only get what is needed. There are several commands that can be used to execute queries on a database through a SQL server. Commands such as CREATE, INSERT,and SELECT can be utilized to achieve most data manipulation.

CREATE Statement

A CREATE TABLE statement creates a new instance of a table inside of a schema. A schema is an independent container of table objects. It’s an effective object projection tool that increases the amount of security surrounding our data. The columns in the table maintain the attributes and the rows maintain the data for each column.

//create an instance of a database in the schema
CREATE DATABASE company;
//initiate the database
USE company;
//create an instance of a teams table
CREATE TABLE TEAMS (
  //attribute - sql datatype keyword - constraint  
  ID int NOT NULL,
  DEPT varchar(20) NOT NULL,
  // primary key constraint for the id integer
  PRIMARY KEY (id)
);

//create an instance of a members table
CREATE TABLE MEMBERS (
  ID int NOT NULL,
  NAME varchar(20) NOT NULL,
  ROLE varchar(20) NOT NULL,
  PRIMARY KEY (id)

);

// run DESC TEAMS to see table
// this command displays the columns as rows only to describe its identifiers
SQL> DESC TEAMS;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ID      | int           | NO   | PRI |         |       |
| DEPT    | varchar(20)   | NO   |     |         |       |
+---------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

SQL> DESC MEMBERS;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ID      | int           | NO   | PRI |         |       |
| NAME    | varchar(20)   | NO   |     |         |       |
| ROLE    | varchar(20)   | NO   |     |         |       |
+---------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

SELECT Statement

The most effective way to fetch data from a table A SELECT statement. The data retrieved will be returned as its own table called a result set. Basic syntax from a select statement consists of the column fields of the data to retrieve and the table to fetch the data FROM.

//run SELECT statement
SQL> SELECT DEPT FROM TEAMS;
+------------+
| DEPT       |
+------------+
|SALES       |
|PR          |
|HR          |
|MANAGEMENT  |
+----+-------+

INSERT Statement

Now it's time to add some team members to the member table. To add new rows use an INSERT INTO statement. IF the desire is to add values for all of the named columns of the table there is no need to specify the column name. Just ensure the order of the values is the same as the way they are listed in the table. Breaking up the statement into multiple lines is okay. Just be sure to end the query with a semicolon.

//run SELECT statement
SQL> INSERT INTO MEMBERS
VALUES (1, 'Tatiyana', 'CEO');

SQL> INSERT INTO MEMBERS
VALUES (2, 'Toni', 'Copywriter');

SQL> INSERT INTO MEMBERS
VALUES (3, 'Mark', 'Editor');

// Use an asterisk in the SELECT statement to view the table with the added values
SQL> SELECT * FROM MEMBERS
+----+----------+-------------+
| ID | NAME     | ROLE        |
+----+----------+-------------+
|  1 | Tatiyana | CEO         |
|  2 | Toni     | Copywriter |
|  3 | Mark     | Editor      |
+----+----------+-------------+

Conclusion

There are many tools that have attempted to replace SQL but it continues its rain. SQLs collaborative nature and updates over the years has yet to be duplicated making it the preferred method to interface data systems. Its popularity along with the ease of learning the language gives SQL my recommendation for a new skill to add to an up and coming developers tool kit. Thanks for reading and as always happy coding!

Credits:
https://www.tutorialspoint.com/sql/index.htm
https://www.sqltutorial.org/
https://www.techonthenet.com/sql/index.php

Blog

Manipulating Data in Sql

Jessa Daggs

CREATE Statement

SELECT Statement

INSERT Statement

Conclusion

Join Our Newsletter. No Spam, Only the good stuff.

Related