Category: sql

SQL – CREATE Table Table of content The SQL CREATE TABLE Statement SQL CREATE TABLE IF NOT EXISTS Creating a Table from an Existing Table ”; Previous Next This tutorial will teach you how to use SQL to create tables in RDBMS. We use CREATE TABLE command to create a Table in a Database. In RDBMS, Database tables are used to store the data in the form of some structures (fields and records). Here, a field is a column defining the type of data to be stored in a table and record is a row containing actual data. In simple words, we can say a Table is a combination of rows and columns. SQL provides various queries to interact with the data in a convenient way. We can use SQL statements to create and delete tables, inserting, updating and deleting data in these tables. For a more detail on different concepts related to RDBMS please check RDBMS Concepts tutorial. The SQL CREATE TABLE Statement SQL provides the CREATE TABLE statement to create a new table in a given database. An SQL query to create a table must define the structure of a table. The structure consists of the name of a table and names of columns in the table with each column”s data type. Note that each table must be uniquely named in a database. Syntax CREATE TABLE statement is used to create a new table in a database. − CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ….. columnN datatype, PRIMARY KEY( one or more columns ) ); Here are the key points- CREATE TABLE is the keyword telling the database system what you want to do. In this case, you want to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement. The column parameters (e.g. column1, column2, column3, etc.) specify the names of the columns of the table. The datatype parameter specifies the type of data the column can hold (e.g. integer, varchar, string, etc.). PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. Example: Creating Table in SQL CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Here are the key points- The following code block is an example, which creates a CUSTOMERS table with column name ID, NAME, AGE, ADDRESS and, SALARY and ID as a primary key. NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table. Verification Once your table is created, you can check if it has been created successfully or not. You can use SQL DESC table_name command to list down the description of the table as follows: DESC CUSTOMERS; This will display the structure of the table created: column names, their respective data types, constraints (if any) etc. Field Type Null Key Default Extra ID int(11) NO PRI NULL NAME varchar(20) NO NULL AGE int(11) NO NULL ADDRESS char(25) YES NULL SALARY decimal(18,2) YES NULL Now, you have CUSTOMERS table available in your database which you can use to store the required information related to customers. SQL CREATE TABLE IF NOT EXISTS Consider a situation where you will try to create a table which already exists, in such situation MySQL will throw the following error. ERROR 1050 (42S01): Table ”CUSTOMERS” already exists So to avoid such error we can use SQL command CREATE TABLE IF NOT EXISTS to create a table. Syntax Following is the basic syntax of a CREATE TABLE IF NOT EXISTS statement − CREATE TABLE IF NOT EXISTS table_name( column1 datatype, column2 datatype, column3 datatype, ….. columnN datatype, PRIMARY KEY( one or more columns ) ); Example: Creating Table if Not Exists The following SQL command will create the CUSTOMERS table only when there is no table exists with the same name otherwise it will exit without any error. CREATE TABLE IF NOT EXISTS CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Creating a Table from an Existing Table Instead of creating a new table every time, one can also copy an existing table and its contents including its structure, into a new table. This can be done using a combination of the CREATE TABLE statement and the SELECT statement. Since its structure is copied, the new table will have the same column definitions as the original table. Furthermore, the new table would be populated using the existing values from the old table. Syntax The basic syntax for creating a table from another table is as follows − CREATE TABLE NEW_TABLE_NAME AS SELECT [column1, column2…columnN] FROM EXISTING_TABLE_NAME WHERE Condition; Here, column1, column2… are the fields of the existing table and the same would be used to create fields of the new table. Example: Creating Table from an Existing Table Following is an example, which would create a table SALARY using the CUSTOMERS table and having the fields customer ID and customer SALARY − CREATE TABLE SALARY AS SELECT ID, SALARY FROM CUSTOMERS; This will create a new table SALARY which will have the following structure − Field Type Null Key Default Extra ID int(11) NO PRI NULL SALARY decimal(18,2) YES NULL Print Page Previous Next Advertisements ”;

SQL – Cursors Table of content Cursors in SQL Properties of Cursors Life Cycle of the Cursor Declare Cursor Statement Open Cursor Statement Fetch Cursor Statement Close Cursor Statement ”; Previous Next A database cursor solves the problem of impedance mismatch. It acts as a filter between the result of a SQL query and the statements that process this result. Cursors in SQL A Cursor is a temporary memory that is allocated by the database server at the time of performing the Data Manipulation Language operations on a table, such as INSERT, UPDATE and DELETE etc. It is used to retrieve and manipulate data stored in the SQL table. In MySQL, you cannot declare a cursor directly outside of a stored procedure or function. Cursors are generally declared within stored procedures, functions, or blocks of SQL code in MySQL database. Using cursors, we can perform multiple operations on each row of a result set, with or without returning the original data. Properties of Cursors Following are the properties of MySQL Cursors − READ ONLY − We cannot update or modify any records in the table using the MySQL cursors. We can just fetch and process data from a table. Non-Scrollable − We can retrieve records from a table in a single direction, i.e. from the first record or the last. We cannot move backward or jump to a specific position within the result set. Asensitive Cursor − An asensitive cursor operates directly on the actual data in the database, it does not create a copy of the data. If any change is made to the data by other connections, it can affect the data that the cursor is working with. In addition to the Asensitive cursor there is another type known as Insensitive Cursor. An insensitive cursor uses a temporary copy of the data. Therefore, these cursors are insensitive (not affected) to the changes that are made in the table. Life Cycle of the Cursor There are four steps to manage these cursors. Following diagram illustrates the lifecycle of an SQL cursor − Now, let us discuss the phases of life cycle of the cursor one-by-one. Declare Cursor Statement In MySQL we can declare a cursor using the DECLARE statement and associate it with a SELECT statement to retrieve records from a database table. However, this SELECT statement associated with a cursor does not use the INTO clause, as it”s purpose is to fetch and process rows rather than assigning values to variables. Syntax Following is the syntax to declare a cursor in MySQL database − DECLARE cursor_name CURSOR FOR select_statement; Open Cursor Statement After declaring a cursor in MySQL, the next step is to open the cursor using the OPEN statement. It initializes the result-set, allowing us to fetch and process rows from the associated SELECT statement in the cursor. Syntax Following is the syntax to open a cursor in MySQL database − OPEN cursor_name; Fetch Cursor Statement Then, we can use the FETCH statement to retrieve the current row pointed by the cursor, and with each FETCH, the cursor moves to the next row in the result set. This allows us to process each row one by one. Syntax Following is the syntax to fetch a cursor in MySQL database − FETCH cursor_name INTO variable_list; Close Cursor Statement Once all the rows are fetched, we must close the cursor to release the memory associated with it. We can do this using the CLOSE statement. Syntax Following is the syntax to close a cursor in MySQL database − CLOSE cursor_name; Example In this example, let us see how to manage a cursor in a stored procedure. Assume we have created a table with the name CUSTOMERS using the CREATE TABLE statement as follows − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, let us insert some records into the CUSTOMERS table using the INSERT statement as follows − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ); Now, we will create a backup table named ”CUSTOMERS_BACKUP” to store customer data − CREATE TABLE CUSTOMERS_BACKUP ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, PRIMARY KEY (ID) ); Here, we are creating a stored procedure named FetchCustomers to fetch customer names from the CUSTOMERS table and inserting them one by one into the BACKUP table. We are using a cursor to iterate through the rows and a handler to detect the end of the result-set, ensuring all names are processed − DELIMITER // CREATE PROCEDURE FetchCustomers() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE customer_id INT; DECLARE customer_name VARCHAR(255); DECLARE auto_id INT; — Declare cursor DECLARE MY_CURSOR CURSOR FOR SELECT id, name FROM CUSTOMERS; — Declare exit handler DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; — Open cursor OPEN MY_CURSOR; — Fetch and insert rows read_loop: LOOP FETCH MY_CURSOR INTO customer_id, customer_name; IF done = 1 THEN LEAVE read_loop; END IF; — Insert the fetched data into the backup table INSERT INTO customers_backup VALUES (customer_id, customer_name); — Get the last auto-generated ID used in the insertion SET auto_id = LAST_INSERT_ID(); END LOOP; — Close cursor CLOSE MY_CURSOR; END // DELIMITER ; Once we create the procedure successfully, we can execute it using the CALL statement as shown

SQL – NULL Values Table of content Creating a Table without NULL Values Updating NULL Values in a Table Deleting Records with NULL Values ”; Previous Next SQL uses the term NULL to represent a non-existent data value in the database. These values are not the same as an empty string or a zero. They don”t hold any space in the database and are used to signify the absence of a value or the unknown value in a data field. Some common reasons why a value may be NULL − The value may not be provided during the data entry. The value is not yet known. Since the NULL values are basically non-existent, you cannot use comparison operators such as = , <, or > with them. However, you can check if a value is NULL using the IS NULL, “NOT NULL” or IS NOT NULL operators. Creating a Table without NULL Values NULL values can be inserted in any column of a table as they are not associated with any specific data type. However, when a column is defined with the “NOT NULL” keyword, an error is raised whenever you try to insert NULL values into that specific column. Syntax The basic syntax of NOT NULL while creating a table is as follows − CREATE TABLE table-name ( column1 datatype NOT NULL, column2 datatype NOT NULL, … columnN datatype ); Here, NOT NULL signifies that column should always accept an explicit value of the given data type. You can insert NULL values into the columns where we did not use NOT NULL. Example Let us create a table with the name CUSTOMERS in the SQL database using the CREATE statement as shown in the query below − CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Let us insert some values into the above created table using the following query − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00), (2, ”Khilan”, 25, ”Delhi”, 1500.00), (3, ”Kaushik”, 23, ”Kota”, 2000.00), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00), (5, ”Hardik”, 27, ”Bhopal”, 8500.00), (6, ”Komal”, 22, ”Hyderabad”, NULL), (7, ”Muffy”, 24, ”Indore”, NULL); The table is successfully created in the database. ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad NULL 7 Muffy 24 Indore NULL Now, let us retrieve the records present in the table that are not null using the IS NOT NULL operator − SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE SALARY IS NOT NULL; The above query would produce the following result − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 You can also retrieve the NULL records present in the table using IS NULL operator in the SELECT query as shown below − SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE SALARY IS NULL; The above query would produce the following result − ID NAME AGE ADDRESS SALARY 6 Komal 22 Hyderabad NULL 7 Muffy 24 Indore NULL Updating NULL Values in a Table You can update the NULL values present in a table using the UPDATE statement in SQL. To do so, you can use the IS NULL operator in your WHERE clause to filter the rows containing NULL values and then set the new value using the SET keyword. Example Consider the previously created table and update the NULL value(s) present in the table using the UPDATE statement as shown below − UPDATE CUSTOMERS SET SALARY = 9000 WHERE SALARY IS NULL; Output When you execute the above query, the output is obtained as follows − Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0 Verification Let us verify whether the specified record(s) in the table is updated or not using the following query − SELECT * FROM CUSTOMERS; On executing the above query, the output is displayed as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 9000.00 7 Muffy 24 Indore 9000.00 Deleting Records with NULL Values You can delete records containing NULL values from a table using the DELETE FROM statement. You first check whether the table consists of NULL values using the IS NULL operator in WHERE clause and delete the records that are filtered. Example Consider the previously created CUSTOMERS table and delete the NULL value(s) present in the table using the DELETE statement as shown below − DELETE FROM CUSTOMERS WHERE SALARY IS NULL; Output When you execute the above query, the output is obtained as follows − Query OK, 2 rows affected (0.01 sec) Verification Let us verify whether the filtered record(s) in the table is deleted or not, by displaying the table using a SELECT statement. SELECT * FROM CUSTOMERS; The table will be displayed as − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 Print Page Previous Next Advertisements ”;

SQL Backup Database Table of content SQL Backup Database Statement Types of Backups in SQL Full Backup Differential Backup Transaction Log Backup(T-log) Restore Database From Backup MySQL and MS SQL Database Backup and Restore Backup MySQL Database Restore MySQL Database Backup MS SQL Database Restore MS SQL Database ”; Previous Next In this SQL Backup Database tutorial, we will explain how we can take a backup of a database in MySQL and MS SQL Server. It is very important and basic development practice to have a backup of the database in case the original is corrupted or lost due to power surges or disk crashes etc. By practicing this, the database can be recovered as it was before the failure. SQL Backup Database Statement In SQL, the BACKUP DATABASE statement is used to create database backups. Syntax Following is the syntax of SQL Backup Database Statement − BACKUP DATABASE database_name TO DISK = ”filepath” GO Here, the SQL command creates a backup file of the database_name database. Types of Backups in SQL In SQL, there are three types of database backups available. These are: Full Backup Differential Backup Transaction Log (T-log) backup SQL Full Backup A full backup is a complete backup of an SQL server database. Syntax Following is the syntax of SQL Full Backup − BACKUP DATABASE database_name TO medium = ”filepath” GO Here, database_name is the name of the database, medium refers to the storage medium such as disk, tape or url. SQL Differential Backup In Sql, you can also backup only the new changes by using the WITH DIFFERENTIAL command. Syntax Following is the syntax of Sql Differential Backup − BACKUP DATABASE my_db TO medium = ”filepath” WITH DIFFERENTIAL; GO Here, database_name is the name of the database, medium refers to storage device such as disk, tape or url. Transaction Log (T-log) backup A transaction log backup includes all the transactions since the last transaction log backup. BACKUP LOG comnmand is used to perfom the Transaction Log backup. Syntax Following is the syntax of Sql transaction log backup − BACKUP LOG database_name TO medium = ”filepath”; GO Here, database_name is the name of the database, medium refers to storage device such as disk Restore Database From Backup To restore a backup file in Database, we can use the RESTORE DATABASE command. Syntax Following is the syntax of Restore Database From Backup − RESTORE DATABASE database_name FROM DISK = ”filepath”; GO Here, database_name is the name of the database, medium refers to disk, tape or url. MySQL and MS SQL Database Backup and Restore Here is the process to create backup in MySQL and MS Sql databases. Backup MySQL Database MySQL mysqldump command can be used to take complete backup of a given database. This operation will be performed from command line and will require database user name and password, preferably admin privilege. $ mysqldump -u username -p”password” -R testDB > testDB.sql We are using the -p flag immediately followed by our password to connect to the database with no space between. The -R is required to tell mysqldump to copy stored procedures and functions along with the normal data from the database. Depending on the database size, above command may take sometime to create a final output file testDB.sql. Once command is completed, you will have a complete database dump in testDB.sql file which you can keep safe anywhere you like. Later this file can be used to restore the database. Restore MySQL Database If we have a database dump then we can use the following two step process to restore our database. First step is to create our new database using mysqladmin prompt command as follows: $ mysqladmin -u username -p”password” create tutorialsDB; The next step is to import old database into new database shown below : $ mysql -u username -p”password” tutorialsDB < testDB.sql; If you want to keep your database name same as the old one then you will have to drop old database and then re-create it before importing old data into this database, but make sure you don”t have any data in this database which you do not want to loose. Backup MS SQL Database If you are working with MS SQL Server then to create a backup for an existing database, SQL provides us with a simple SQL BACKUP DATABASE command. Syntax Following is the syntax of the BACKUP DATABASE command in SQL − BACKUP DATABASE database_name TO DISK = ”filepath” GO Example Following is an example to create a backup file for the database testDB on D drive. SQL> BACKUP DATABASE testDB TO DISK = ”D:testDB.bak” GO To perform a backup or restore you should have admin sysadmin privileges. You should also back up the database onto a different disk other than the actual database. Even if the disk crashes, we will not lose our backup file along with the database. Output When we execute the above query, the output is obtained as follows − Processed 344 pages for database ”testDB”, file ”testDB” on file 1. Processed 2 pages for database ”testDB”, file ”testDB_log” on file 1. BACKUP DATABASE successfully processed 346 pages in 0.011 seconds (245.383 MB/sec). Restore MS SQL Database If you have a proper backup of an MS SQL database then youc an easily restore it when needed. Syntax Following is the syntax of the RESTORE DATABASE command in SQL − RESTORE DATABASE database_name FROM DISK = ”filepath” [WITH REPLACE] GO Here WITH REPLACE option can be given if you want to overwrite the existing database. Example Following is an example to restore a database from a backup file testDB.bak available on D drive. SQL> RESTORE DATABASE testDB FROM DISK = ”D:testDB.bak” WITH REPLACE GO Print Page Previous Next Advertisements ”;

SQL – Clone Tables Table of content Simple Cloning in MySQL Shallow Cloning in MySQL Deep Cloning in MySQL Table Cloning in SQL Server ”; Previous Next There may be a situation when you need an exact copy of a table with the same columns, attributes, indexes, default values and so forth. Instead of spending time on creating the exact same version of an existing table, you can create a clone of the existing table. SQL Cloning Operation allows to create the exact copy of an existing table along with its definition. There are three types of cloning possible using SQL in various RDBMS; they are listed below − Simple Cloning Shallow Cloning Deep Cloning Simple Cloning in MySQL Simple cloning operation creates a new replica table from the existing table and copies all the records in newly created table. To break this process down, a new table is created using the CREATE TABLE statement; and the data from the existing table, as a result of SELECT statement, is copied into the new table. Here, clone table inherits only the basic column definitions like the NULL settings and default values from the original table. It does not inherit the indices and AUTO_INCREMENT definitions. Syntax Following is the basic syntax to perform simple cloning in MySQL− CREATE TABLE new_table SELECT * FROM original_table; Example Consider the following existing CUSTOMERS table which will be cloned in next new few steps. ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Now let”s use the following SQL statement to create NEW_CUSTOMERS table using the existing table CUSTOMERS. CREATE TABLE NEW_CUSTOMERS SELECT * FROM CUSTOMERS; Output The output is displayed as − Query OK, 7 rows affected (0.06 sec) Records: 7 Duplicates: 0 Warnings: 0 Verification To verify whether the table has been cloned successfully, we can use the following SELECT query − SELECT * FROM NEW_CUSTOMERS; If NEW_CUSTOMERS table is created successfully, then it should get all the records which are available in CUSTOMERS table. Shallow Cloning in MySQL Shallow cloning operation creates a new replica table from the existing table but does not copy any data records into newly created table, so only new but empty table is created. Here, the clone table contains only the structure of the original table along with the column attributes including indices and AUTO_INCREMENT definition.. Syntax Following is the basic syntax to perform shallow cloning in MySQL RDBMS − CREATE TABLE new_table LIKE original_table; Example Following is an example to create a shallow clone copy of the existing table CUSTOMERS. CREATE TABLE SHALL_CUSTOMERS LIKE CUSTOMERS; Output The output is displayed as − Query OK, 0 rows affected (0.06 sec) Verification To verify whether the table has been cloned successfully, we can use the following DESC table_name query − DESC SHALL_CUSTOMERS; This will display the following information about the SHALL_CUSTOMERS table which is just a replica of CUSTOMERS table − Field Type Null Key Default Extra ID int(11) NO PRI NULL NAME varchar(20) NO NULL AGE int(11) NO NULL ADDRESS char(25) YES NULL SALARY decimal(18,2) YES NULL Deep Cloning in MySQL Deep cloning operation is a combination of simple cloning and shallow cloning. It not only copies the structure of the existing table but also its data into the newly created table. Hence, the new table will have all the contents from existing table and all the attributes including indices and the AUTO_INCREMENT definitions. Since it is a combination of shallow and simple cloning, this type of cloning will have two different queries to be executed: one with CREATE TABLE statement and one with INSERT INTO statement. The CREATE TABLE statement will create the new table by including all the attributes of existing table; and INSERT INTO statement will insert the data from existing table into new table. Syntax Following is the basic syntax to perform deep cloning in MySQL RDBMS − CREATE TABLE new_table LIKE original_table; INSERT INTO new_table SELECT * FROM original_table; Example Following is an example to create a deep clone copy of the existing table CUSTOMERS. First step is to create a shallow clone of the existing table. CREATE TABLE DEEP_CUSTOMERS LIKE CUSTOMERS; The output is displayed as − Query OK, 0 rows affected (0.06 sec) Now second step is to copy all the records from the CUSTOMERS table to DEEP_CUSTOMERS. INSERT INTO DEEP_CUSTOMERS SELECT * FROM CUSTOMERS; Output The output is displayed as − Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 Verification To verify whether the table has been cloned successfully, we can use the following SELECT query − SELECT * FROM DEEP_CUSTOMERS; If DEEP_CUSTOMERS table is cloned successfully, then it should get all the records which are available in CUSTOMERS. Table Cloning in SQL Server However, there is no direct way to fully clone a table in an SQL server. However, we have some work around to handle the situation. SELECT…INTO STATEMENT MS SQL Server can make use of the SELECT…INTO statement to create a new table and copies the data from an existing table into it. However, this command only copies the data and not the definition of it, thus, omitting constraints, indexes etc., if any. They need to be added separately if one wishes to have the exact same structure of the original table in their new table. You can use the SELECT…INTO command to copy a table within the same database as well as across different databases. Syntax Following is the basic syntax of the SELECT…INTO statement − SELECT * INTO new_table FROM original_table; The above SQL command will create a table new_table using the structure of original_table and then it will copy all the data from original_table to new_table. Example Consider the following existing CUSTOMERS table which will be cloned in this section. ID NAME AGE ADDRESS

SQL – Rename Table Table of content The SQL RENAME TABLE Statement The SQL ALTER TABLE Statement Renaming a Table in SQL Server Rules to be followed while Renaming Tables ”; Previous Next SQL provides two ways to rename an MySQL table. You can use either SQL RENAME TABLE or ALTER TABLE statement to change a table name in MySQL RDBMS. The SQL RENAME TABLE Statement You can change a MySQL table name using SQL RENAME TABLE statement. Syntax Following is the syntax of the SQL RENAME TABLE Statement − RENAME TABLE table_name TO new_table_name; Where, table_name is the current name of an existing table and new_table_name is the new name of the table. Example: SQL RENAME TABLE Statement Let us create a table with the name CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. as shown below − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, let us insert few records into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); The table will be created as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Following SQL Query changes the name of the CUSTOMERS table to BUYERS − RENAME TABLE CUSTOMERS to BUYERS; Verification Once you change the name of a table, you can start using the new table name in your SQL queries. SELECT * FROM BUYERS; If table name got changed successfully, then it should list down all the records which were available in CUSTOMERS table. The SQL ALTER TABLE Statement The ALTER TABLE statement can be used to change or modify the structure of an existing table i.e. using this statement you can add/delete columns, create/destroy indexes, change the datatypes of the existing columns, rename the columns and, we can even rename the table. Syntax Following is the syntax of the SQL ALTER TABLE statement to rename an existing table − ALTER TABLE table_name RENAME [TO|AS] new_table_name Example: SQL ALTER TABLE Statement Following SQL ALTER TABLE statement will change the table name from BUYERS to CUSTOMERS. ALTER TABLE BUYERS RENAME TO CUSTOMERS; Verification Once you change the name of the table to CUSTOMERS, you can start using this name in your SQL queries. SELECT * FROM CUSTOMERS; This will produce the following result: ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Renaming a Table in SQL Server There isn”t a query in SQL Server that can rename a table directly. However, it does give you access to a stored procedure called sp_rename that enables you to rename a table. The sp_rename is a system stored procedure (set of pre-built subroutines that perform tasks within the database) in SQL that can be used to rename various database objects including tables, columns, indexes, and constraints. Syntax Following is the basic syntax to rename a table in SQL Server − EXEC sp_rename ”old_table_name”, ”new_table_name” Here, you must ensure that old table name is present in the database and that new table name does not already exist. Otherwise, it will issue a warning. Second important point is to make sure that the table is not locked and there is no active transaction involving this table. Example: Renaming a Table in SQL Server Assume we already have the CUSTOMERS table in our database. Now, we are going to rename this table from CUSTOMERS to WORKERS, using the following query − EXEC sp_rename ”CUSTOMERS”, ”WORKERS”; Output The result obtained is as shown below − Completion time: 2023-08-15T19:21:49.1144163+05:30 Verification We can verify whether the changes are reflected by retrieving its contents using the SELECT statement as follows − SELECT * FROM WORKERS; This will list down all the records available in WORKERS table as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Because you have renamed the table to WORKERS so if you try to get the details by using the old table name, it will throw an error showing that the table does not exist. Rules to be followed while renaming tables When renaming tables in SQL, there are some rules and best practices that should be followed to ensure that the renaming process goes smoothly and does not cause any unintended consequences or issues. Avoid renaming system tables − System tables are tables that are created and used by the database management system itself. Renaming these tables can cause issues with the functioning of the database system, so it is generally not recommended to rename system tables. Update all references to the table − After renaming a table, any stored procedures, views, triggers, or other database objects that reference the table will need to be updated to use the new name of the table. Failure to update these references can result in errors or issues with the functioning of the database system. Test thoroughly − Before renaming a table in a production environment, it is important to test the renaming process thoroughly in a development or testing environment to ensure that all references to the table have been updated correctly and that

SQL – Conversion Functions ”; Previous Next In SQL, the conversion functions are used to convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype TO datatype. The first datatype is the input datatype. The second datatype is the output datatype. List of Functions Following is a list of functions available in the SQL server for Conversion of one datatype to another − Sr.No. Function & Description 1 CAST Is used to transform the numerical data into character or string data. 2 CONVERT Is used to transform an expression from one data type to another. 3 PARSE Is used to convert a string data to the desired data format and returns the outcome as an expression. 4 TRY_CAST Is used to return the expression in the chosen data type. 5 TRY_CONVERT Is used to change the datatype of an expression. 6 TRY_PARSE Is used to return a result of an expression that has been converted to the specified data type, or NULL if the conversion is unsuccessful. sql-conversion-functions.htm Print Page Previous Next Advertisements ”;

SQL – Null Functions Table of content SQL NULL Functions The ISNULL() Function The COALESCE() Function The NULLIF() Function The IFNULL() Function ”; Previous Next SQL NULL functions are used to perform operations on NULL values that are stored in the database tables. A NULL value serves as a placeholder in the database when data is absent or the required information is unavailable. It is a flexible value not associated to any specific data type and can be used in columns of various data types, including string, int, varchar, and more. Following are the various features of a NULL value − The NULL value is different from a zero value or a field containing a space. A record with a NULL value is one that has been left empty or unspecified during record creation. The NULL value assists us in removing ambiguity from data. Thus, maintaining the uniform datatype across the column. SQL NULL Functions To handle these NULL values in a database table, SQL provides various NULL functions. They are listed as follows − ISNULL() COALESCE() NULLIF() IFNULL() The ISNULL() Function The SQL ISNULL() function returns 0 and 1 depending on whether the expression is null or not. If the expression is null, then this function returns 1; otherwise, it returns 0. Syntax Following is the syntax for the ISNULL() function − ISNULL(column_name) Example First of all let us create a table named CUSTOMERS, containing the personal details of customers including their name, age, address and salary etc., using the following query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, insert records into this table using the INSERT INTO statement as follows − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, NULL ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, NULL ), (7, ”Indore”, 24, ”Indore”, 10000.00 ); The table will be created as − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota NULL 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad NULL 7 Indore 24 Indore 10000.00 Following is the query to check whether SALARY is NULL or not − SELECT SALARY, ISNULL(SALARY) AS Null_value FROM CUSTOMERS; Output On execution of the above query, we get the column “SALARY” and Null_value. If the SALARY is NULL, then their null value is 1; otherwise, it is 0. − SALARY Null_value 2000.00 0 1500.00 0 NULL 1 6500.00 0 8500.00 0 NULL 1 10000.00 0 The COALESCE() Function The SQL COALESCE() function returns the first occurred NON-NULL expression among its arguments. If all the expressions are NULL, then the COALESCE() function will return NULL. An integer is evaluated first in the COALESCE() function, and an integer followed by a character expression always produces an integer as the output. Syntax Following is the syntax for the COALESCE() function − COALESCE(expression_1, expression_2, expression_n); Example In the following query, we are returning the first occurred NON-NULL value − SELECT COALESCE (NULL, ”welcome”, ”tutorialspoint”) AS Result; Output On executing the above query, we get “welcome” as a result, because it is the first NON-NULL value − Result welcome Example In the following query, we are using the COALESCE() function on the SALARY and AGE columns of CUSTOMERS table. The first NON-NULL values evaluated from these two columns are displayed in another column named “Result”. SELECT NAME, SALARY, AGE, COALESCE(SALARY, AGE) AS Result FROM CUSTOMERS; Output When you execute the above query, we get the following table as a result − NAME SALARY AGE Result Ramesh 2000.00 32 2000.00 Khilan 1500.00 25 1500.00 Kaushik NULL 23 23.00 Chaitali 6500.00 25 6500.00 Hardik 8500.00 27 8500.00 Komal NULL 22 22.00 Indore 10000.00 24 10000.00 The NULLIF() Function The SQL NULLIF() function compares two expressions. If both expressions are the same, it returns NULL. Otherwise, it returns the first expression. This function can be used directly with clauses like SELECT, WHERE, and GROUP BY. Syntax Following is the syntax of NULLIF() function − NULLIF(expression_1, expression_2); Example The following SQL query uses NULLIF() function to compare values in NAME and ADDRESS columns of the CUSTOMERS table. If the NAME value matches the ADDRESS value, the result is NULL; otherwise, it returns the NAME value. The result values are stored in another column called “Result”. SELECT NAME, ADDRESS, NULLIF(NAME, ADDRESS) AS Result FROM CUSTOMERS; Output When you execute the above query, we get the following table as a result − NAME ADDRESS Result Ramesh Ahmedabad Ramesh Khilan Delhi Khilan Kaushik Kota Kaushik Chaitali Mumbai Chaitali Hardik Bhopal Hardik Komal Hyderabad Komal Indore Indore NULL The IFNULL() Function The IFNULL() function replaces the NULL values in a database table with a specific value. This function accepts two arguments. If the first argument is a NULL value, it is replaced with the second argument. Otherwise, the first argument is returned as it is. This function does not work in the SQL Server database. If both the arguments are NULL, the result of this function is also NULL. Syntax Following is the syntax for IFNULL() function − IFNULL(column_name, value_to_replace); Example The following query evaluates the values in SALARY column of the CUSTOMERS table. Using the IFNULL() function, we are replacing the NULL values in this column (if any) with the value 5500 − SELECT NAME, SALARY, IFNULL(SALARY, 5500) AS Result FROM CUSTOMERS; Output Following is the output of the above query − NAME SALARY Result Ramesh 2000.00 2000.00 Khilan 1500.00 1500.00 Kaushik NULL 5500.00 Chaitali 6500.00 6500.00 Hardik 8500.00 8500.00 Komal NULL 5500.00 Indore 10000.00 10000.00 Print Page Previous Next Advertisements ”;

SQL – Default Constraint Table of content The SQL DEFAULT Constraint Passing “DEFAULT” as Value Adding Default Constraint to an Existing Column Dropping Default Constraint ”; Previous Next The SQL DEFAULT Constraint The SQL DEFAULT Constraint is used to specify the default value for a column of a table. We usually set default value while creating the table. The default values are treated as the column values if no values are provided while inserting the data, ensuring that the column will always have a value. We can specify default values for multiple columns in an SQL table. Syntax Following is the syntax of the SQL DEFAULT Constraint − CREATE TABLE table_name ( column1 datatype DEFAULT default_value, column2 datatype DEFAULT default_value, column3 datatype, ….. columnN datatype ); Example In the following query we are creating the CUSTOMERS table using the CREATE TABLE statement. Here, we are adding a default constraint to the columns NAME, AGE, ADDRESS, and SALARY − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL DEFAULT ”Ramesh”, AGE INT NOT NULL DEFAULT ”32”, ADDRESS CHAR (25) DEFAULT ”Ahmedabad”, SALARY DECIMAL (18, 2) DEFAULT ”2000”, PRIMARY KEY (ID) ); Following query inserts values into this table using the INSERT statement − INSERT INTO CUSTOMERS (ID) VALUES (1); INSERT INTO CUSTOMERS VALUES (2, ”Khilan”, 25, ”Delhi”, 1500.00 ); The table is created with default values in the NAME, AGE, ADDRESS, and SALARY columns for the first row as shown below − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 Passing “DEFAULT” as Value While inserting data into a table, if the column names are not included in the INSERT query, to insert the default value into the record we need to pass “DEFAULT” as a value, as shown below − INSERT INTO CUSTOMERS VALUES (3, ”Kaushik”, DEFAULT, DEFAULT, 2000.00), (4, ”Chaitali”, DEFAULT, DEFAULT, DEFAULT); The table obtained is as shown below − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 32 Ahmedabad 2000.00 4 Chaitali 32 Ahmedabad 2000.00 Adding Default Constraint to an Existing Column We can also add default constraints to an existing column of a table using the ALTER TABLE statement. This allows us to modify the structure of existing table by specifying default values, ensuring data consistency in the database. Syntax Following is the syntax for adding a default constraint to a column in an existing table − ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT ”default_value”; Example Assume we have created another table named BUYERS using the CREATE TABLE statement as shown below − CREATE TABLE BUYERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Following query, we adds a default constraint to the ADDRESS column of the BUYERS table. ALTER TABLE BUYERS ALTER ADDRESS SET DEFAULT ”Delhi”; Following INSERT statement inserts a record into the BUYERS table by providing values to all the columns except ADDRESS − INSERT INTO BUYERS (ID, NAME, AGE, SALARY) VALUES (01, ”Rahul”, 27, 50000); Verification After inserting the record if you retrieve it back, you can observe the default value (“Delhi”) in the address column − SELECT * FROM BUYERS WHERE ID = 01; Output The table obtained is as follows − ID NAME AGE ADDRESS SALARY 01 Rahul 27 Delhi 50000.00 Dropping Default Constraint We can delete the default constraint from a table using the ALTER TABLE… DROP statement. Syntax Following is the syntax to delete the default constraint from a table − ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT; Example In here, we are removing the default constraint from the ADDRESS column of the CUSTOMERS table − ALTER TABLE CUSTOMERS ALTER ADDRESS DROP DEFAULT; Verification We can verify the table details (structure) and check whether there is a default constraint or not using the following query − DESC CUSTOMERS; The table obtained is as shown below − Field Type Null Key Default Extra ID int NO PRI NULL NAME varchar(20) NO Ramesh AGE int NO 32 ADDRESS char(25) YES NULL SALARY decimal(18,2) YES 2000.00 Print Page Previous Next Advertisements ”;

SQL – Show Tables (Listing Tables) Table of content MySQL – Listing Tables SQL Server – Listing Tables Oracle – Listing Tables ”; Previous Next There are several instances when you need to retrieve a list of tables from your database. This could be done for testing purposes, to identify any existing tables before adding or removing any, or for any other reason. This tutorial will discuss how we can list down all the table in MySQL, SQL Server and Oracle using simple SQL commands. MySQL – Listing Tables You can use SQL SHOW TABLES statements in MySQL to list down all the tables available in a selected database. Syntax Following is the syntax to list all the tables in SQL in MySQL − SHOW TABLES; Example Following is an example which will list down all the tables from a testDB database. USE testDB; SHOW TABLES; This will display the following output depending on the number of tables available in your database. Tables_in_testDB CALENDAR CUSTOMERS COMPANIES SALARY SQL Server – Listing Tables SQL Server does not provide SHOW TABLE command in an SQL Server. Instead, we can use the “SELECT” statement to retrieve information about tables in a database. We have three different commands to use with the SELECT statement to list all the tables in a database − sys.tables information_schema.tables sysobjects The SYS.TABLES View Following is the syntax to list down all the tables in SQL using the SYS.TABLES view − SELECT * FROM SYS.TABLES; Following is the output of the above query − name object_id principal_id schema_id CUSTOMER 4195065 NULL 1 ORDERS 68195293 NULL 1 COMPANIES 100195407 NULL 1 SALARY 2107154552 NULL 1 The INFORMATION_SCHEMA.TABLES View Following is the syntax to list down all the tables in SQL using the INFORMATION_SCHEMA.TABLES view − SELECT table_name, table_type FROM INFORMATION_SCHEMA.TABLES; Following is the output of the above query − table_name table_type CUSTOMER BASE TABLE ORDERS BASE TABLE COMPANIES BASE TABLE SALARY BASE TABLE The SYSOBJECTS View You can use SYSOBJECTS view to retrieve the information of all the objects created in SQL Server database, including stored procedures, views, system tables and user-defined tables. Following is the basic syntax of using sysobjects view − SELECT name, id, xtype FROM sysobjects WHERE xtype = ”U”; Click here to see the different value of xtype Value Meaning AF Aggregate function (CLR) C CHECK constraint D Default or DEFAULT constraint F FOREIGN KEY constraint L Log FN Scalar function FS Assembly (CLR) scalar-function FT Assembly (CLR) table-valued function IF In-lined table-function IT Internal table P Stored procedure PC Assembly (CLR) stored-procedure PK PRIMARY KEY constraint (type is K) RF Replication filter stored procedure S System table SN Synonym SQ Service queue TA Assembly (CLR) DML trigger TF Table function TR SQL DML Trigger TT Table type U User table UQ UNIQUE constraint (type is K) V View X Extended stored procedure This will produce following result − name id xtype CUSTOMER 4195065 U ORDERS 68195293 U COMPANIES 100195407 U SALARY 2107154552 U Oracle – Listing Tables There are following three SQL SELECT statements which you can use to list down the tables available in Oracle. Listing ALL Tables Following is the SQL SELECT statement which will list down all the available tables in an Oracle Database. SELECT owner, table_name FROM ALL_TABLES Listing DBA Tables Following is the SQL SELECT statement which will list down all the DBA related tables in an Oracle Database. SELECT owner, table_name FROM DBA_TABLES Listing USER Tables Following is the SQL SELECT statement which will list down all the USER created tables in an Oracle Database. SELECT owner, table_name FROM USER_TABLES Listing ALL Views Following is the SQL SELECT statement which will list down all the views available in an Oracle Database. SELECT view_name FROM ALL_VIEWS; Print Page Previous Next Advertisements ”;