Knowing how to code and manage related databases and database-driven applications is an important skill for any technology career. This article is designed to help the reader understand the most common database management language, SQL.
A relational database is a database divided into logical units called tables, where the tables are linked together in the database. Relational databases allow data to be broken down into smaller, logical and manageable units for easier maintenance and better performance.
Tables are linked together by common keys or fields in a relational database system, so that even if the data you want is in multiple tables, it can be easily joined these to get an integrated data set using a query.
SQL became a standard of the American National Standards Institute (ANSI) in 1986 and of the International Organization for Standardization (ISO) in 1987. However, it is not fully portable between some different database systems without adjustments.
SQLite Studio
SQLite Studio is an open source, compact, standard and easy to install cross-platform database management system. The download link is https://SQLitestudio.pl/.
This article provides the basics needed to effectively use SQLite in any database environment. We’ll start with the basics — what is a database and how to create one; what are tables, rows, columns, constraints, and how to create a table and execute it with some keyboard shortcuts for quick results.
Features of SQLite Studio
Free and open source: It is free for everyone, for any purpose (including commercial).
Advanced SQL Code Editor: The SQL Editor window highlights and offers hints on SQL syntax, providing a print code formatter, and flags syntax errors.
Hassle-free multiple databases: SQL statements that refer to multiple databases can be executed in a single query, thanks to the transparent database connection mechanism built into the SQL editor of SQLite Studio.
Drag and drop between databases: It is possible to drag objects (tables, indexes,…) between databases to copy or move them, with or without data.
Multi-platform: Works on three main platforms: Windows, MacOS X, Linux.
Portable distribution: No installation is required. Just download the package, unzip and run it. No administrator rights required.
Plugins: SQLite Studio supports plugins of different categories. For example, another script language could be added along with it.
What’s a database?
Data can be facts related to any object. For example, your name, address, salary, date of birth, etc, is some of the data related to you. A picture, image, file, etc, can also be considered data.
An SQL Server database consists of a collection of tables that store a specific set of structured data. As an example, an electrical service provider uses a database to manage billing, customer issues and their addresses. A phone book uses a database to store peoples’ names, phone numbers, contact details and addresses.
To create a database, click on Database > Add Database (Ctrl + O) > New_Database
Table: Data is stored in database objects called tables in RDBMS. This table is a collection of related data items, and consists of rows and columns. It is the most common and simplest form of storing data in a RDBMS.
An example of a ‘Customer’ table is given in Figure 3.
Row: A row (records or tuple) in a table represents a single, implicitly structured data.
Column: A column (or field) in a table is a vertical entity that contains all the information associated with a specific field. For example, a column in the ‘Customer’ table is NAME, which represents the names of all the customers.
‘Null’ value: A ‘null’ value in a table is a value that appears empty. It doesn’t mean ‘zero’ value but just the lack of a value.
SQL constraints
Constraints are the rules that apply to the data columns in a table. They are used to limit the type of data that can be included in a table. This ensures the accuracy and reliability of the database data.
Constraints can be at column or table level. Column-level restrictions apply to a single column, while table-level restrictions apply to the entire table.
- Not null: Ensures that the column cannot have a null value.
- Primary key: Uniquely identifies each row in a database table.
- Foreign key: Uniquely identifies a row/table in any other database table.
- Unique: Ensures that all values in a column are different.
- Check: Ensures that all values in a column satisfy certain conditions.
- Default: Provides a default value for a column when none is specified.
Data integrity
- Entity integrity: There are no duplicate rows in a table.
- Domain integrity: Applies valid entries for a given column, limiting the type, format or range of values.
- Reference integrity: Rows cannot be deleted; other records use them.
- User defined integrity: Implements certain business policies that are not related to the integrity of the entity, domain, or reference.
Create a table
Open an SQL editor (Alt + E). CREATE TABLE is the keyword that tells the database system what you want to do. In the parentheses is the list that defines each column in the table and what kind of data it displays. For example:
CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, . . columnN datatype constraint, PRIMARY KEY (one or more columns) );
To execute the query, go to F9 (Figure 5).
In the next article, we will see how to insert data in a table, fetch specific records and delete/alter tables. We will also look at various operations on the tables, see how to use most common clauses such as WHERE, GROUP BY, HAVING; and learn how to join two or more tables.
