SQL is a domain-specific language that engineers use to handle data stored in relational databases or for stream processing in relational data stream management systems. You can store data in a relational database through tables. The first thing to do in this process is to create the database on which you want to work on. Then we create tables in which the actual data will be stored. A table in such a database will have both rows and columns.
There will be a particular datatype associated with each column that determines the kind of data that it can store. While creating the table, we have to specify the name and datatype of each column. You can create a table using the SQL CREATE TABLE command in both MySQL and PostgreSQL databases.
Let's take a deeper look at the CREATE TABLE SQL statement.
How do I create a table with SQL?
You can create a table in MySQL with the following syntax:
CREATE TABLE table_name(
column_1 data_type default value column_constraint,
column_2 data_type default value column_constraint,
...,
…,
table_constraint
);
The basic things that you should have while creating a table are its name and at least one column name. There should only be one database table with a particular table name. The database will show you an error message if you try to make two tables with the same name.
Inside the brackets, we specify the names of the columns we want to make in the table, along with the data type of the data that will be entered into it. Commas will separate the names of these columns. The name, data type, the default value of the column, and one or more column restrictions make up each column.
The column constraints control the actual data value that can be stored in the column. For instance, the NOT NULL constraint guarantees that no NULL values exist in that particular column. You can have more than one constraint for a single column. For instance, a column can contain both the NOT NULL and UNIQUE restrictions.
If you want to apply constraints to all the columns in the table, you can use the table constraints. Some examples of the same are FOREIGN KEY, CHECK, and UNIQUE. Each table can have a primary key that is made up of one or more than one columns. This primary key is used to uniquely identify each record of a table. Normally, you list the primary key column first and then the others. You must declare the PRIMARY KEY constraint as a table constraint if it is made up of two or more columns.
Let's look at an example:
CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
Here, the name of the table is "CUSTOMERS", and its primary key is ID. The ID, NAME, and AGE columns have a constraint of NOT NULL. The length of the name should be less than 20 characters. Now, we have created a table with the SQL CREATE TABLE command and we can insert the relevant data into it and the database.
Can you have a table inside a table in MySQL/PostgreSQL?
Having a table inside a table would be called the nesting of tables. For this, we would have to create a table inside another table with the SQL CREATE TABLE command. This is a concept that does not exist in MySQL. However, we can achieve the same result as that of having nested tables by using a primary key and a foreign key. This creates a parent-child relationship among the two tables.
A field or a group of fields in one table that references the PRIMARY KEY of another table is known as a FOREIGN KEY. The table with the main primary key is referred to as the parent table, while the one with the foreign key is referred to as the child table.
For example, assume we have a parent table Vehicle, whose Primary key is VehId. We have two more tables, Car, with primary key CarId, and Truck, with primary key TruckId, respectively. If we put VehId as a foreign key in both Car and Truck, then Vehicle will become its parent table, thus creating a parent-child relationship.
Parent table Vehicle {VehId}
Child tables
1. Car {VehId,CarId}
2. Truck {VehId,TruckId}
Is it possible to select into a SQL table without creating it first?
To choose data from a database, we use the SELECT statement. The data from this is kept in a result table called the result set. This is used to select a portion of a table or the whole table entirely. When you are coding and have created a table with the SQL CREATE TABLE command, you can use the SELECT command to view the tables you have created.
Its syntax is like this:
SELECT column_1, column_2, …
FROM table_name;
For example, the following command selects the entire table.
SELECT * FROM table_name;
SELECT INTO
Data from one table is transferred into another table using the SELECT INTO statement. It can copy all the columns of one table into another. This is usually used when the table to which the data is being copied already exists.
To select all the columns of a table:
SELECT *
INTO new_table [IN externaldb]
FROM old_table
WHERE condition;
To select only a few columns into the new table:
SELECT column_1, column_2, column_3, ...
INTO new_table [IN externaldb]
FROM old_table
WHERE condition;
So, can we select into a table if we haven't created it yet with SQL CREATE TABLE?
Logically, the answer should be no. But this is not the case, and we can select into a table even if we haven't created it yet. When a table has to be formed, and data from one table requires to be transferred into the newly generated table, this approach is utilized. The same data types as the chosen columns are used to generate the new table.
How do you create a product table in SQL?
A product table in SQL can be created in various ways. Utilizing the SQL CREATE TABLE statement is one option. The product information will be contained in a table that this statement will create. The SELECT command can also be used to view and get the data from the table. The syntax for both of these has been mentioned above.
The INSERT command can be used to insert new data into the table as well. Thanks to the INSERT statement, the data will be inserted into the table at the designated position. The data can also be taken out of the table using the SELECT query and then added to another table using the INSERT statement.
INSERT INTO
We use the INSERT INTO statement to add new records to a table. The syntax of the INSERT statement is:
INSERT INTO table_name (column_1, column_2, ...)
VALUES (value_1, value_2, ...);
What makes a product table different is only the design aspect of the same. The product's name, model, class, model year, and list price are all stored in the products database. Every item is a part of the brand identified by the brand id column. Consequently, a brand could have one product or many. Each product is a member of a category that is identified by its category id in the table.
What types of data are suitable for SQL databases?
While using the SQL CREATE TABLE command, we have to mention the data types of the columns. The data that a column can include depends on this data type. Examples include integer, character, binary, date and time, and more. A database table must have a name as well as a data type for every column.
When building a table, a SQL programmer must determine what kind of data will be contained in each column. The data type specifies how SQL will interface with the data stored. It helps in determining what kind of data is anticipated inside each column. The three primary data types in MySQL 8.0 are string, numeric, and date and time.
String data types
String data types are generally a set of characters that belong to the English alphabet. The main string data types are:
- CHAR(size)
- VARCHAR(size)
- BINARY(size)
- VARBINARY(size)
- TINYBLOB
- TINYTEXT
- TEXT(size)
- BLOB(size)
- MEDIUMTEXT
- MEDIUMBLOB
- LONGTEXT
- LONGBLOB
- ENUM(val_1, val_2, val_3, ...)
- SET(val_1, val_2, val_3, ...)
Here the SIZE stands for the maximum size the stored data can take. A CHAR is a string of fixed length, while a VARCHAR is a string of variable string length. These are the most used string data types. A BLOB is a Binary Large Object.
Numeric data types
Numeric data type variables are used to hold numerical data. They are further divided into two kinds of data types - Exact and Approximate. Exact data types are used to hold the data value in its literal form. While real numbers are contained in approximate data types, the information is not literally saved as a copy of the true values. The main numerical data types are:
- BIT(size)\
- TINYINT(size)
- BOOL
- BOOLEAN
- SMALLINT(size)
- MEDIUMINT(size)
- INT(size)
- INTEGER(size)
- BIGINT(size)
- FLOAT(size, d)
- FLOAT(p)
- DOUBLE(size, d)
- DOUBLE PRECISION(size, d)
- DECIMAL(size, d)
- DEC(size, d)
The most commonly used numerical data type is INT. It is used to hold non-decimal numbers, while variables with data types of FLOAT are used to hold decimal numbers. In the BOOL data type, zero is considered FALSE, and non-zero values are considered TRUE.
Date and time
The date and time data types are used to hold date data. The main date and time data types are:
- DATE
- DATETIME(fsp)
- TIMESTAMP(fsp)
- TIME(fsp)
- YEAR
The SQL server data types are similar to the above-given data types but with small syntactical differences.
What are the types of variables in the SQL server?
Two categories of variables exist in the MS SQL server:
Local variable
A user declares a local variable. It always begins with @. Each local variable's scope is constrained to the batch or process that is now running within a particular session. A Transact-SQL local variable is a sort of object that can store only one particular kind of data value. Scripts and batches frequently employ variables:
- as a counter, either to manage the loop's repetition rate or to keep track of how many times it is executed.
- to store a data value that will be put through a control-of-flow check.
- to hold a data value that a function return value will return.
Global variable
The system keeps up the global variable. Users cannot make them public. @@ is where the global variable begins. It keeps data about sessions.
Declaring a variable
It would help if you defined any variable prior to using it in a batch or process. The memory location replacement variable is declared using the DECLARE command. A variable can only be utilized after it has been declared in the succeeding batch or procedure step.
The TSQL Syntax to declare a variable looks like this:
DECLARE { @LOCAL_VARIABLE[AS] data_type [ = value ] }
The programmer can also decide the value of a defined variable by :
- In the process of variable declaration with the DECLARE keyword
- Using SET
- Using SELECT
Why are data types important in SQL?
The data type is among the essential components of any coding language, whether it be a programming language such as C or a data manipulation language like SQL. In actuality, SQL supports over 30 data types that may store various forms of actual data. It would be best if you had a firm grasp of the various SQL data types before you can begin dealing with data.
The kinds of information that can be held in database objects like tables are specified by SQL data types. Every column in a table has a name and a data type, and every table has columns. They form the backbone of any language, as you cannot manipulate data without using them.
You should know that not all data types are supported by database systems. Therefore you must check before employing any particular data type. For instance, you cant use DateTime in Oracle as it does not allow this data type.
Similarly, MySQL does not use Unicode as a data type. You can use extra data types that certain databases have. For instance, "money" and "smallmoney" in Microsoft SQL Server can be substituted for "float" and "real," but these terms are database-specific and not present in other database systems. Sometimes, specific data types are referred to have various names in some databases. Oracle, for instance, refers to "decimal" as "number" and "blob" as "raw."
Can multiple tables have the same primary key?
Yes. In several tables, the primary key can share the same column name. Inside a table, column names must be distinct. Because it determines the integrity of the Entities, a table can only contain one primary key. Every table may contain a primary key, but it is not required. No two rows may share the same primary key, thanks to the column or columns designated as the primary key. The primary key of one table can be used to identify the records of another table and be a part of the second one's primary key.
How do I use "order by" on a primary key?
The ORDER BY command can be used to get the result set sorted. This can be in either ascending or descending order. Records are typically sorted using the ORDER BY keyword in ascending order. You can use the DESC keyword to arrange the entries in descending order.
The syntax of order looks like:
SELECT column_1, column_2, ...
FROM table_name
ORDER BY column_1, column_2, ... ASC|DESC;
For example, assume there is a table called Users; you can sort it in ascending or descending order based on the city of the users.
SELECT * FROM Users
ORDER BY City;
To sort the same in descending order:
SELECT * FROM Users
ORDER BY City DESC;
ORDER BY on primary key
Simply use ORDER BY like usual on the name of the primary key, such as "RollID":
SELECT * FROM my_table WHERE col_1 < 5 ORDER BY RollID;
The query optimizer might or might not decide to use the primary key index structure to analyze the sorting rather than doing an active sorting on the query result set to address the ORDER BY based on how it decides to assess the query.
The majority of single-table queries without an ORDER BY clause will return results in the order of the Primary key since MySQL InnoDB stores tables in what approaches to native primary key order. However, you can still utilize an ORDER BY if your application genuinely requires Primary Key ordering.
No code development
The no-code development methodology is one of the main factors promoting the democratization of coding. These days, more individuals have access to computing in principle without knowing any coding. It facilitates the building of responsive websites as well as mobile applications.
AppMaster can help you generate source code automatically. The code is always available for you to view and examine. You have the choice to modify project details using a programming language with AppMaster. We also give users the option to export the code. This is a promise that the software you are developing with AppMaster is entirely within your control and ownership.
Conclusion
SQL has several applications that make it very attractive. Creating data integration scripts, designing and executing analytical queries, and accessing subsets of data from a database for insights and transaction processing are some of its most prominent purposes. It can also be used for inserting, modifying, and removing rows and columns of data in a database.
Before getting into the depths of SQL, we need to familiarise ourselves with its basic commands and syntax. By going through the above article, we can get to know more about the basic statements used in SQL, like CREATE TABLE, INSERT INTO, SELECT, and more.
