SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL OPERATORS SQL COMMANDS SQL Queries

SQL Table

SQL TABLE SQL CREATE TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL UPDATE TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT INTO Statement

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

SQL TABLE

SQL TABLE

Structured Query Language (SQL) is a relational database (RDBMS) where data is stored in the form of tables, that is, in rows and columns. These tables are known as tuples, where each row in the table is cited as a tuple. There are certain operations that can be carried out on these SQL tables. Some of them are listed below.

SQL TEMP TABLE

The temporary table concept was initiated in the SQL server. Developers use temporary tables, and it helps them in a number of ways.

Temporary tables can perform all types of operations that a normal table is capable of doing. These tables are generated in the tempdb database, and they can be developed during runtime.

The notion of temporary tables is only supported by MySQL versions 3.23 and above. However, in older versions, there was the concept of heap tables.

Temporary tables can be diverged into two types based on their behaviour and scope.

  1. Local temporary table
  2. Global temporary table

Local temporary table

A Local Temporary Table is available during the present connection time only, and they are deleted automatically once the user disconnects. This type of table is started with a hash (#) symbol.

CREATE TABLE #local table (
 user_id int,
 user_name varchar (100),
 user_addrs varchar (150)
 ); 

The following is an instance of generating a local temporary table.

Global temporary table

A Global temporary table is initiated by a double hash (##) symbol. This type of table does not get deleted and is present for all users. It behaves like a permanent table.

CREATE TABLE #global table (
 user_id int,
 user_name varchar (100),
 user_addrs varchar (150)
 ); 

The following is an instance of generating a global temporary table.

Deleting temporary table

A temporary table can be deleted in two ways. It can be deleted automatically as well as manually.

A local temporary table is inevitably deleted immediately after the user disconnects from the server.

The temporary table can be deleted manually as well by using the DROP TABLE command.

DROP TABLE #tablename

The following is the instance for deleting a temporary table. It is the same as deleting a regular table.

SQL CLONE TABLE

It is possible to replicate or clone one table from another SQL table in the same server. This is done by making use of the SELECT statement.

SELECT *
 INTO <new_table>
 FROM <old_table>; 

The following is the syntax for generating the copy of one table from another table.

The above statement will copy all the content of the old table into the new table.

The following is the syntax to copy specific columns from the old table to the new table.

SELECT column1, column2, column3, …
 INTO <new_table>
 FROM <old_table>; 

The WHERE clause can also be used with the above statements, and certain conditions can be specified as well. Also, new column names can be given using the AS clause.

Example:

Let’s consider the following Source_table.

IDFnameLnameProjectIDEmailProfileCity
1HarryKaneA1[email protected]SESKolkata
2RonWesleyB2[email protected]SDEMumbai
3DobbySaneC3[email protected]SDEPune
4AlbusDolbyD4[email protected]HRAgra
5SnapeWrightE5[email protected]SDEDelhi

Query:

CREATE TABLE Contact LIKE Source_table;

The following query will create an empty structure with the same attributes as that of the Source_table.

Output:

IDFnameLnameProjectIDEmailProfileCity

Query:

INSERT INTO Contact SELECT *
 FROM Source_table; 

Now, the following query will clone all the contents of the Source_table into the new Contact table.

Output:

IDFnameLnameProjectIDEmailProfileCity
1HarryKaneA1[email protected]SESKolkata
2RonWesleyB2[email protected]SDEMumbai
3DobbySaneC3[email protected]SDEPune
4AlbusDolbyD4[email protected]HRAgra
5SnapeWrightE5[email protected]SDEDelhi

Creating a clone table helps in various database operations like testing since this table does not affect the records of the original table. Hence, the data in the original table remains intact.

These are some of the advanced SQL TABLE statements which are used by developers to deal with the tables in the database. These statements are essential and should be handled carefully.

These statements make database operations like testing, storing data temporarily and all much easier and faster.



ADVERTISEMENT
ADVERTISEMENT