SQL basics workshop: Create database

SQL, or Structured Query Language, is a domain-specific language used in programming and managing relational databases. It is employed to handle and manipulate structured data, which means the data is organized into tables. Almost all modern RDBMS (Relational Database Management Systems) like MySQL, PostgreSQL, SQL Server, and Oracle use SQL as their standard database language.

SQL sublanguages

SQL can be divided into several sublanguages, each responsible for specific tasks:

1. DDL (Data Definition Language):

   • Concerned with the structure (schema) of the database.

   • Includes commands like:

     • CREATE: to create objects in the database.

     • ALTER: to alter objects of the database.

     • DROP: to delete objects from the database.

2. DML (Data Manipulation Language):

   • Deals with the manipulation of data stored in the database.

   • Includes commands like:

     • MERGE: to insert new records or update existing records based on conditions.

     • INSERT: to insert data into a table.

     • UPDATE: to modify data in a table.

     • DELETE: to delete data from a table.

3. DCL (Data Control Language):

   • Concerned with rights, permissions, and other controls of the database system.

   • Includes commands like:

     • GRANT: to grant a specific type of permission.

     • REVOKE: to take back permissions granted to a user or role.

4. TCL (Transaction Control Language):

   • Deals with the transaction operations within databases.

   • Includes commands like:

     • COMMIT: to save all the transactions to the database.

     • ROLLBACK: to undo specific transactions that haven't been saved to the database yet.

     • SAVEPOINT: to set a point within a transaction to which you can later roll back.

     • SET TRANSACTION: to specify characteristics for the transaction.

5. DQL (Data Query Language):

   • Deals with the data fetch operations within databases.

   • Includes commands like:

     • SELECT: to display static data or retrieve data from a table, based on certain parameters

SQL also has other clauses, operators, constraints, etc., that support and extend the functionalities of the primary commands in these sublanguages.

SQL became a standard of the American National Standards Institute (ANSI) in 1986 and has since undergone multiple updates and enhancements. When using SQL with specific RDBMS platforms, you might encounter extensions or variations, but the core commands and syntax remain largely consistent across systems.

SQL Data Types

SQL (Structured Query Language) uses various data types to define the kind of data that can be stored in different columns of a table. Here's an overview of some of the common SQL data types:

1. Numeric Types:

   • INTEGER (or INT): Represents whole numbers, e.g., 1, 100, -30.

   • SMALLINT: Represents smaller whole numbers, often with a smaller storage size than INT.

   • BIGINT: For very large whole numbers.

   • DECIMAL (or NUMERIC): Fixed precision and scale (number of digits to the right of the decimal point) numbers. E.g., DECIMAL(5,2) could represent 123.45.

   • FLOAT or REAL: Floating-point numbers. Suitable for approximations rather than exact values.

   • DOUBLE PRECISION: Floating-point numbers with more precision than FLOAT or REAL.

2. Character Types:

   • CHAR(n): Fixed-length character string where n defines the string length.

   • VARCHAR(n): Variable-length character string with a maximum length of n.

   • TEXT: Variable-length character string without a specific limit (limits depend on the specific DBMS).

3. Date and Time Types:

   • DATE: Represents a date (year, month, day).

   • TIME: Represents a time (hours, minutes, seconds).

   • TIMESTAMP: Represents a date and time together.

   • INTERVAL: Represents a duration of time.

4. Boolean Type:

   • BOOLEAN: Represents true or false values.

5. Binary Data Types:

   • BINARY(n): Fixed-length binary string.

   • VARBINARY(n): Variable-length binary string.

   • BLOB: Stands for Binary Large Object. Used to store large amounts of binary data, like images.

6. Miscellaneous Types:

   • ENUM: A string object that can have only one value, chosen from a list of predefined values.

   • SET: Similar to ENUM but can have multiple values.

7. Spatial Types (in databases like MySQL that support spatial features):

   • GEOMETRY: A type that can store any kind of spatial data.

   • POINT, LINESTRING, POLYGON: More specific spatial data types.

8. JSON Types (in databases like MySQL and PostgreSQL that support JSON):

   • JSON: Used to store JSON-formatted data.

It's important to note that the availability and exact behavior of these data types can vary between different database management systems (DBMSs). Always consult the documentation specific to the DBMS you're working with to understand the nuances and capabilities of each data type.

Hands-On Lab: Create the database and table

1. Create studentdemo database with the following command:

    create database studentdemo;
   

   

2. We cannot have multiple databases with the same name. If you try to run the query again, you'll get the following error:

   

3. To have a GUI interface to interact with database, you can use the DBeaver tool. Establish connection to the database with MySQL connector:

   

4. Now we need to add a table called Student to the database studentdemo with the following details:

   • Student name

   • Student ID

   • Grade

   • Age

   • Course

To set the current database as studentdemo, enter the following code:

    use studentdemo;

To create the Student table use the DBeaver tool. Run the following code in SQL script:

CREATE TABLE Student (
    StudentID CHAR(4) PRIMARY KEY,
    StudentName VARCHAR(30),
    grade CHAR(1),
    age INT,
    course VARCHAR(50)
);

StudentID is designated as the primary key, meaning each value in the StudentID column must be unique and cannot be null. This ensures every record in the Student table can be distinctly identified using StudentID.

Now you can see the table in your DBeaver navigator pane:

Populate your table with the data

To insert data into our newly created table use the INSERT command:

USE studentdemo;
INSERT INTO Student (StudentID, StudentName, grade, age, course) VALUES ('0001', 'Maxat Akbanov', '5', '33', 'Systems Engineering');

If you check the database in DBeaver, you can see the inserted data row:

To insert multiple values use the following code:

INSERT INTO Student (StudentID, StudentName, grade, age, course) 
VALUES 
('S005', 'John Doe', 'A', 30, 'Biology'),
('S006', 'Jane Smith', 'C', 32, 'Chemistry'),
('S007', 'Emily Johnson', 'B', 29, 'History');

References

1. dbeaver : Public Key Retrieval is not allowed

2. Troubleshoot Access Denied Error: Connect DBeaver to a local MySQL database

3. What Is SQL (Structured Query Language)?