Guess for how long SQL has been a major database management language? 20 years? 30 years? Nope, but for more than 50 years! Although Structured Query Language or SQL was formally released in 1974 by Donald D. Chamberlin and Raymond F. Boyce from IBM, the concept was introduced by Edger F. Codd who’s often called the father of relational databases. One lesser-known fact is that SQL was initially named SEQUEL (Structured English Query Language) and was designed to manipulate the data in System R, IBM’s original quasi-relational database management system. Let’s learn what is SQL and How to create a SQL table?
What is SQL?
As a domain-specific language for managing data stored in RDBMS (Relational Database Management System), SQL is particularly beneficial in handing structured data and offers two notable advantages over the pre-existing read-write APIs, for example, ISAM or VSAM. So which functionality made SQL mainstream. Firstly, SQL introduced accessing multiple memory records with just one command. In addition to eliminating the need to specify how to find a particular record without an index. These two features gave SQL an edge over traditional ISAM and VSAM.
What’s more interesting is that SQL is one of the first commercial languages to use Edgar Codd’s relational model, as described in his influential paper “Relational Model Of Data For Large Shared Data Banks” published in the 1970s. However, SQL did not quite adhere to the relational model as described by Codd, regardless of which, SQL became a highly-used database management language which further made it standardized in American National Standards Institute (ANSI) in 1986. Since then, the initial standard has been revised to accommodate a broader set of features.
What is a Primary Key?
Primary Keys are added to every SQL Table creation to identify unique rows or records. If a column in the table is defined as a primary key, it cannot contain null. Each SQL table can have only one primary key with one or multiple columns. Similarly, if the user has defined Primary Key on any single column, then two records of that column cannot have the same value. Every entry of a column must be unique; therefore, users need to choose the primary key accordingly. For example, in a table containing STUDENT ID as a column, every entry needs to be unique to identify each student. Now let’s understand how to create a SQL table?
Quick Introduction To SQL Server CREATE TABLE Statement
In SQL, tables are primarily used to store data in the database and are uniquely named with a schema. Each table contains one or more columns and each column consists of an associated data type that defines what kind of data it can store. This can include everything from standard numbers to strings, to temporal data. How to create a SQL table, Let’s begin
Step 01: To create a table in SQL, you need to use the CREATE TABLE statement as shown in the IDE layout below.
CREATE TABLE [database_name.][schema_name.]table_name ( pk_column data_type PRIMARY KEY, column_1 data_type NOT NULL, column_2 data_type, ..., table_constraints );
Step 02: In this syntax, you need to specify the exact name of the database in which the table will be created. Here, database_name must be the name of the existing database. Otherwise, it will default to the current database.
Step 03: Now you need to specify the schema where the table belongs and specify the name of the new table as well. Each table is denoted with a primary key consisting of one or more columns. As a general rule of thumb, primary key columns are listed first followed by other columns. For the primary key having one column, use the PRIMARY KEY keyword after the column name. The same goes for primary keys having two or more columns but, in that case, you need to specify the PRIMARY KEY constraint as a table constraint, such as the NOT NULL and UNIQUE constraints.
Step 04: Keep in mind, that a table may feature some unique constraints that are specified in the table constraint section such as FOREIGN KEY, PRIMARY KEY, UNIQUE, and CHECK. Therefore, it may have a more complex syntax than above.
Let’s Now Understand SQL Server CREATE TABLE with an Example
Shown as an example below, is a statement that creates a table named SALES_VISITS to track in-store visits.
CREATE TABLE SALES_VISITS ( VISIT_IDINT PRIMARY KEY IDENTITY (1, 1), FIRST_NAME VARCHAR (50) NOT NULL, LAST_NAME VARCHAR (50) NOT NULL, VISIETD_AT DATETIME, PHONE VARCHAR(20), STORE_ID INT NOT NULL, FOREIGN KEY (STORE_ID) REFERENCES SALES_STORES (STORE_ID) );
Here’s a breakdown of the given example
Since we haven’t explicitly specified the name of the database in which the table SALES_VISIT is created, it’s created in the Bike Stores database by default. However, for the schema, we need to explicitly specify where it’s created.
In this example, the visits table contains six columns:
- The VISIT_ID is the first and primary key of the column in the generated table. In order to instruct the server to generate integer numbers in increasing order for every row, we need to enter the IDENTITY(1,1) syntax.
- The FIRST_NAME and LAST_NAME, as the syntaxes suggest, are character string columns with VARCHAR type. Here the columns will denote name and surname respectively having a capacity of 50 characters each.
- To record the date and time when a customer visited the store, we have VISITED_AT as a DATETIME column. The column PHONE is a varying character string column with up to 13 characters which accepts only NULL.
- Finally, there’s a FOREIGN KEY (attributes referring to primary keys of another table) constraint that ensures that values in the STORE_ID column in the visits table are available in the STORE_ID column in the store table.
Creating a SQL Server Table Using Another Table
To copy an existing table with just one command, a SQL developer selects all columns or specific columns in which the new table gets the same column signature as the old table. The primary syntax for duplicating a SQL table creation is:
CREATE TABLE table_name AS SELECT column1, column2, … FROM old_table_name WHERE, … ; // The following SQL creates a copy of the STORE VISITS table // CREATE TABLE StoreVisitsCopy AS SELECT VISIT_ID, FIRST_NAME, LAST_NAME, VISITED_AT, … FROM StoreVisits;
And congratulations on creating your first database using the SQL CREATE TABLE function! Now that you know How to create a SQL table and duplicate an existing one, the next step on your SQL journey would be learning how to add values or automatically populate columns so that you, as a SQL developer can store, manage, and manipulate databases.
SQL is a major part of the programming and development domain, requiring extensive knowledge, experience, and skills. And what better platform could be there to learn from other than Geekster. If you are interested in web development courses online, check out Geekster’s Full Stack Web Development or Advanced Web development and fast-track your career to get placed by top recruiters from PayPal, Qualcomm, and Zomato, and more. From coding to soft skills, Geekster offers you the resources, talents, and referrals to learn programming from the comfort of your home.
Ans. A SQL aggregate function performs a calculation on one or more values and returns a single value while ignoring the NULL values in a SQL table when it performs the calculation except for the COUNT function. Few notable SQL aggregate functions are AVG, COUNT, SUM, MIN, MAX, etc.
Ans. In SQL normalization, the process is to eliminate redundancy and enhance integrity in data. SQL normalization also helps to collect and organize data in a database, and it’s a multi-step process that sets the data into a tabular form and trims out any unnecessary duplicate, copied, or redundant data from the relation tables in the database.
Ans. While the functions of SQL are primarily used as a DBMS (Database Management System), it’s fundamentally a language and a collection of syntaxes that stores a specific set of structured data. However, SQL can be termed as a standalone database as well – or a relational database – since it’s a rigid and structured way of organizing data.
Ans. In SQL, the RANK() function is a window function dedicated to assigning a rank to each row within a partition that has the same values. For example, the rank of the first row is one, where the SQL rank function adds the number of tied rows to tied ranks and calculates the rank of the new row. However, ranks may not always be consecutive.
- 10 React Projects For Beginners – Build Your First ReactJS Project In Less Than An Hour
- How To Create a SQL Table In Less Than 15 Minutes – Ultimate Guide
- Looking To Learn Stack And Queue In Data Structures? Here’s Your Ultimate Guide To Help You Understand Better!
- What is Data Structure And Algorithms – Learn More About The Key To Your Success In 2022!
- 15 Data Structure And Algorithm Interview Questions To Practice Before Your Interview