Python3 Programming – Exercise 24

SQL

SQL = Structured Query Language.

This, we may say, is the language we shall use to talk to the database. Read more on about databases here and also learn SQL from Sololearn.

All we are interested in is CRUD. We want to learn how to cre…


This content originally appeared on DEV Community and was authored by Otu Michael

SQL

SQL = Structured Query Language.

This, we may say, is the language we shall use to talk to the database. Read more on about databases here and also learn SQL from Sololearn.

All we are interested in is CRUD. We want to learn how to create (insert), read (select), update and delete data. To continue any further, Download the SQLite Browser. It makes the work here easier.

Create a sample table

Let's create a database, sample.db, and save it into a folder of any choice. We recommend the folder in which you have done the practicals.

Copy and paste this SQL code into windows ( text area) when we click on the Execute SQL tab.

CREATE TABLE `test_tb` (
  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `name` TEXT
);

The code above

  • This code creates a table with the name, test_tb
  • There are two fields in the table, id and name
  • The id field has some properties
    • INTEGER - the type of data to store
    • NOT NULL - the column value must not be empty or null
    • PRIMARY KEY - makes every row unique
    • AUTOINCREMENT - increase the PRIMARY KEY sequentially. Thus we ignore the values for the id field because it is PRIMARY KEY and AUTOINCREMENT
  • The name field has only one property, ie. the data type is a TEXT

We shall use this table in this discussion.

Insert

Add a row

We may add data (a row) to the table by inserting.

INSERT INTO `test_tb` (`name`) VALUES('John Doe');

Add multiple rows 

INSERT INTO `test_tb` (`name`) VALUES ('Swift Python'), ('kirito'), ('kevin'), ('spit fire');

Read

Reading is done by selecting.

Read all rows and columns

This will read all the data and with all the field displaying.

SELECT * FROM `test_tb`;

Read all rows and a particular column

This will read all the data but display only the name field.

SELECT `name` FROM `test_tb`;

And this will read all the data but display only the id field.

SELECT `id` FROM `test_tb`;

Read rows WHERE some column's value is given (condition)

This will read all the data where the name field is equal to John Doe

SELECT * FROM `test_tb` WHERE `name` = 'John Doe';

This will read a row whose column (id) value equals 3

SELECT * FROM `test_tb` WHERE `id` = 3;

This will read a row whose column (id) value greater than 3

SELECT * FROM `test_tb` WHERE `id` > 3;

Update

Let us update a row, with id = 1 and change the name value to Terry

UPDATE `test_tb` SET `name` = 'Terry' WHERE `id` = 1;

Delete

Delete the row with id = 1

DELETE FROM `test_tb` WHERE `id` = 1;

Delete the row with name = 'kirito'

DELETE FROM `test_tb` WHERE `name` = 'kirito';

Delete all data

Be careful when we do this.

DELETE FROM `test_tb;

Note

SQL is case insensitive

Practical

use the DB Browser to create some tables and experiment with them.

Summary

  • SQL is the language of the databases.
  • Inserting, reading, updating and deleting data is feasible using SQL on SQLite.

Resources


This content originally appeared on DEV Community and was authored by Otu Michael


Print Share Comment Cite Upload Translate Updates
APA

Otu Michael | Sciencx (2021-04-07T17:32:00+00:00) Python3 Programming – Exercise 24. Retrieved from https://www.scien.cx/2021/04/07/python3-programming-exercise-24/

MLA
" » Python3 Programming – Exercise 24." Otu Michael | Sciencx - Wednesday April 7, 2021, https://www.scien.cx/2021/04/07/python3-programming-exercise-24/
HARVARD
Otu Michael | Sciencx Wednesday April 7, 2021 » Python3 Programming – Exercise 24., viewed ,<https://www.scien.cx/2021/04/07/python3-programming-exercise-24/>
VANCOUVER
Otu Michael | Sciencx - » Python3 Programming – Exercise 24. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2021/04/07/python3-programming-exercise-24/
CHICAGO
" » Python3 Programming – Exercise 24." Otu Michael | Sciencx - Accessed . https://www.scien.cx/2021/04/07/python3-programming-exercise-24/
IEEE
" » Python3 Programming – Exercise 24." Otu Michael | Sciencx [Online]. Available: https://www.scien.cx/2021/04/07/python3-programming-exercise-24/. [Accessed: ]
rf:citation
» Python3 Programming – Exercise 24 | Otu Michael | Sciencx | https://www.scien.cx/2021/04/07/python3-programming-exercise-24/ |

Please log in to upload a file.



There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.

Related Posts