Category: sql

SQL – SORTING Results Table of content Sorting Results in Ascending Order Sorting Results in Descending Order Sorting Results in a Preferred Order ”; Previous Next The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. By default, some databases sort the query results in an ascending order. In addition to that, ORDER BY clause can also sort the data in a database table in a preferred order. This case may not sort the records of a table in any standard order (like alphabetical or lexicographical), but, they could be sorted based on any external condition. For instance, in an ORDERS table containing the list of orders made by various customers of an organization, the details of orders placed can be sorted based on the dates on which those orders are made. This need not be alphabetically sorted, instead, it is based on “first come first serve”. Syntax The basic syntax of the ORDER BY clause which would be used to sort the result in an ascending or descending order is as follows − SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]; You can use more than one column in the ORDER BY clause. Make sure that whatever column you are using to sort, that column should be in the column-list. Sorting Results in Ascending Order Using Order By Clause in SQL, the records in a database table can be sorted in ascending order, either by default or by specifying the “ASC” keyword in the clause condition. Let us see an example to understand this. Example Assume we have created a table named CUSTOMERS using the CREATE TABLE statement 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, insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 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 − 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 is an example, which would sort the result in an ascending order by NAME and SALARY. SELECT * FROM CUSTOMERS ORDER BY NAME; Output This would produce the following result − ID NAME AGE ADDRESS SALARY 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 3 kaushik 23 Kota 2000.00 2 Khilan 25 Delhi 1500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 1 Ramesh 32 Ahmedabad 2000.00 Sorting Results in Descending Order But, to sort the records in a database table in descending order, we need to specify the “DESC” keyword in the clause condition. Let us see an example to understand this. Example The following query sorts the records of the CUSTOMERS tables in descending order based on the column NAME. SELECT * FROM CUSTOMERS ORDER BY NAME DESC; Output This would produce the following result − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 7 Muffy 24 Indore 10000.00 6 Komal 22 Hyderabad 4500.00 2 Khilan 25 Delhi 1500.00 3 kaushik 23 Kota 2000.00 5 Hardik 27 Bhopal 8500.00 4 Chaitali 25 Mumbai 6500.00 Sorting Results in a Preferred Order One can also sort the records of a table in their own preferred order using the CASE statement within the ORDER BY clause. All the values are specified in the clause along with the position they are supposed to be sorted in; if the values are not given any number, they are automatically sorted in ascending order. Example To fetch the rows with their own preferred order, the SELECT query used would be as follows − SELECT * FROM CUSTOMERS ORDER BY ( CASE ADDRESS WHEN ”DELHI” THEN 1 WHEN ”BHOPAL” THEN 2 WHEN ”KOTA” THEN 3 WHEN ”AHMEDABAD” THEN 4 WHEN ”Hyderabad” THEN 5 ELSE 100 END) ASC, ADDRESS DESC; Output This would produce the following result − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 5 Hardik 27 Bhopal 8500.00 3 kaushik 23 Kota 2000.00 1 Ramesh 32 Ahmedabad 2000.00 6 Komal 22 Hyderabad 4500.00 4 Chaitali 25 Mumbai 6500.00 7 Muffy 24 Indore 10000.00 This will sort the customers by ADDRESS in your own order of preference first, and in a natural order for the remaining addresses. Also, the remaining Addresses will be sorted in the reverse alphabetical order. Print Page Previous Next Advertisements ”;

SQL – ORDER BY Clause Table of content The SQL ORDER BY Clause ORDER BY Clause with ASC ORDER BY Clause with DESC ORDER BY Clause on Multiple Columns ORDER BY with WHERE Clause ORDER BY with LIMIT Clause Sorting Results in a Preferred Order ”; Previous Next The SQL ORDER BY Clause The SQL ORDER BY clause is used to sort the data in either ascending or descending order, based on one or more columns. This clause can sort data by a single column or by multiple columns. Sorting by multiple columns can be helpful when you need to sort data hierarchically, such as sorting by state, city, and then by the person”s name. ORDER BY is used with the SQL SELECT statement and is usually specified after the WHERE, HAVING, and GROUP BY clauses. Following are the important points about ORDER BY Clause − Some databases sort the query results in an ascending order by default. To sort the data in ascending order, we use the keyword ASC. To sort the data in descending order, we use the keyword DESC. In addition to sorting records in ascending order or descending order, the ORDER BY clause can also sort the data in a database table in a preferred order. This preferred order may not sort the records of a table in any standard order (like alphabetical or lexicographical), but they could be sorted based on external condition(s). For instance, in the CUSTOMERS table containing the details of the customers of an organization, the records can be sorted based on the population of the cities they are from. This need not be alphabetically sorted, instead, we need to define the order manually using the CASE statement. Syntax The basic syntax of the ORDER BY clause is as follows − SELECT column-list FROM table_name [ORDER BY column1, column2, .. columnN] [ASC | DESC]; Where, column-list is list of the columns we want to retrieve; and ASC or DESC specifies the sort order. Note: We can use more than one column in the ORDER BY clause, but we need to make sure that the column we are using to sort is specified in the column-list. ORDER BY Clause with ASC We can sort the result-set of a query in ascending order (based on one or more columns) using the SQL ORDER BY clause by specifying ASC as the sort order. ASC is the default sort order for this clause, i.e. while using the ORDER BY clause if you do not explicitly specify the sort order, the data will be sorted in ascending order. Example Assume we have created a table with name CUSTOMERS in the MySQL database using CREATE TABLE statement 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) ); Following query inserts values into this table using the INSERT statement − 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 obtained is as shown below − 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 In the following query, we are sorting the records of the CUSTOMERS table in ascending order based on the column NAME − SELECT * FROM CUSTOMERS ORDER BY NAME ASC; Output This would produce the following result − ID NAME AGE ADDRESS SALARY 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 3 Kaushik 23 Kota 2000.00 2 Khilan 25 Delhi 1500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 1 Ramesh 32 Ahmedabad 2000.00 ORDER BY Clause with DESC To sort the result-set of a query in descending order (based on one or more columns), we need to use the ORDER BY clause by specifying DESC as the sort order. Example The following query sorts the records of the CUSTOMER table based on the descending order of the name of the customers − SELECT * FROM CUSTOMERS ORDER BY NAME DESC; Output This would produce the result as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 7 Muffy 24 Indore 10000.00 6 Komal 22 Hyderabad 4500.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 5 Hardik 27 Bhopal 8500.00 4 Chaitali 25 Mumbai 6500.00 ORDER BY Clause on Multiple Columns We can use the ORDER BY clause to sort the result-set of a query by multiple (more than one) columns. When sorting by multiple columns, the sorting is done in the order that is specified in the ORDER BY clause. In other words, the table will be sorted based on the first column (specified in the query), then the second column, and so on. Example In the following query, we are retrieving all records from the CUSTOMERS table and sorting them first by their address in ascending order, and then by their salary in descending order − SELECT * FROM CUSTOMERS ORDER BY AGE ASC, SALARY DESC; Output Following is the result produced − ID NAME AGE ADDRESS SALARY 6 Komal 22 Hyderabad 4500.00 3 Kaushik 23 Kota 2000.00 7 Muffy 24 Indore 10000.00 4 Chaitali 25 Mumbai 6500.00 2 Khilan 25 Delhi 1500.00 5 Hardik 27 Bhopal 8500.00 1 Ramesh 32 Ahmedabad 2000.00 ORDER BY with WHERE Clause We can also use the WHERE clause with the ORDER BY clause to sort the rows that meet certain conditions. This can be useful when we want to sort a subset of the data in a table based on the specific criteria. Example Now, we are retrieving all records from the CUSTOMERS table where the age of

SQL – INSERT Query Table of content The SQL INSERT INTO Statement Inserting Data into a Table Using Another Insert Data Only in Specified Columns ”; Previous Next The SQL INSERT INTO Statement The SQL INSERT INTO Statement is used to add new rows of data into a table in the database. Almost all the RDBMS provide this SQL query to add the records in database tables. Each value in the records we are inserting in a table using this statement should be of the same datatype as the respective column and satisfy the constraints of the column (if any). The values passed using an insert statement should match the number of columns in the table or, the number of columns mentioned in the current query. If any of these conditions are not satisfied, this statement generates an error. Syntax There are two basic syntaxes of the SQL INSERT INTO statement which are shown below − INSERT INTO TABLE_NAME (column1, column2…columnN) VALUES (value1, value2…valueN); Here, column1, column2, column3,…columnN are the names of the columns in the table into which you want to insert the data. There is another syntax of INSERT INTO statement where you can specify only column values without column names. But, make sure the order of the values is in the same order as the columns in the table. Following is second syntax of the SQL INSERT Query − INSERT INTO TABLE_NAME VALUES (value1,value2…valueN); Example To see an example, let us create a table with name CUSTOMERS in the MySQL database using the CREATE TABLE statement 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) ); The following SQL INSERT INTO statements will create three records in the empty CUSTOMERS table. INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ”Khilan”, 25, ”Delhi”, 1500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, ”Kaushik”, 23, ”Kota”, 2000.00 ); We can also insert multiple rows at once using the following query as shown below − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ); Following query adds another record in the CUSTOMERS table using the second syntax as shown below − INSERT INTO CUSTOMERS VALUES (7, ”Muffy”, 24, ”Indore”, 10000.00 ); Verification To check if the records are inserted into the CUSTOMERS table, use the SELECT query − SELECT * FROM CUSTOMERS; The table will be displayed with all the records included in it. 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 Inserting Data into a Table Using Another Sometimes, you just need to copy the data from an existing table to another table in the same database. SQL provides convenient ways to do so − Using INSERT… SELECT Using INSERT… TABLE The INSERT… SELECT Statement You can populate the data into a table through the select statement using an already existing another table; provided the other table has a set of fields, which are required to populate the first table. Here is the syntax − INSERT INTO first_table_name [(column_name(s))] SELECT column1, column2, …columnN FROM second_table_name [WHERE condition]; Example The following statement would create another table named BUYERS with the same structure as CUSTOMERS table − 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) ); Now using the INSERT… INTO statement, let us insert all the records from the CUSTOMERS table into the BUYERS table. INSERT INTO BUYERS (ID, NAME, AGE, ADDRESS, SALARY) SELECT * FROM CUSTOMERS; Output The output will be displayed as − Query OK, 7 rows affected (0.07 sec) Records: 7 Duplicates: 0 Warnings: 0 Verification To verify if the records are inserted properly or not, use the following SELECT query − SELECT * FROM BUYERS; The table will be displayed containing the same records as CUSTOMERS − 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 The INSERT… TABLE Statement If you have two tables structure exactly same, then instead of selecting specific columns you can insert the contents of one table into another using the INSERT…TABLE statement. Following is the syntax to do so − INSERT INTO first_table_name TABLE second_table_name; Example In this example, let us use the same CUSTOMERS table we have created in the previous example and copy its contents into another table named SHOPPERS. For that, let”s create the table SHOPPERS with the same structure as CUSTOMERS table − CREATE TABLE SHOPPERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now use the following statement to insert all the records from the CUSTOMERS table into SHOPPERS table − INSERT INTO SHOPPERS TABLE CUSTOMERS; Output This query will generate the following output − Query OK, 7 rows affected (0.13 sec) Records: 7 Duplicates: 0 Warnings: 0 Verification If you verify the contents of the SHOPPERS table using the SELECT statement shown below − SELECT * FROM SHOPPERS; The table will be displayed with the newly inserted values 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 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Insert Data Only in Specified Columns You can select only particular columns from a table to insert

SQL – Select Into Statement Table of content The SQL Select Into Statement Copying Data From Specific Columns Copying Data From Multiple Tables Copying Specific Records ”; Previous Next The SQL Select Into Statement The SQL SELECT INTO Statement creates a new table and inserts data from an existing table into the newly created table. The new table is automatically created based on the structure of the columns in the SELECT statement and can be created in the same database or in a different database. However, it”s important to note that the SELECT INTO statement does not preserve any indexes, constraints, or other properties of the original table, and the new table will not have any primary keys or foreign keys defined by default. Therefore, you may need to add these properties to the new table manually if necessary. MySQL doesn”t support the SELECT … INTO TABLE Sybase SQL extension i.e. in MySQL you cannot use the SELECT … INTO statement to insert data from one table to another. Instead of this, we can use INSERT INTO … SELECT statement or, CREATE TABLE … SELECT. Syntax Following is the basic syntax of the SQL SELECT INTO statement in SQL Server − SELECT * INTO new_table_name FROM existing_table_name Example Let us create the CUSTOMERS table 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, insert values 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 CUSTOMERS table will be creates 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 The following SELECT INTO statement creates a new table called CUSTOMER_BACKUP and copies the data from the CUSTOMERS table into it − SELECT * INTO CUSTOMER_BACKUP FROM CUSTOMERS; Output We get the following result. We can observe that 7 rows have been modified. (7 rows affected) Verification We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMER_BACKUP table − SELECT * from CUSTOMER_BACKUP; The table displayed is 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 Copying Data From Specific Columns We can also copy data from specific columns from an existing table into the new table using the SQL SELECT INTO statement. To do so, we just need to include the required column names after the select keyword. Syntax Following is the syntax − SELECT column1, column2, …, columnN INTO new_table_name FROM existing_table_name; Example In the following query, we are creating a new table called CUSTOMER_DETAILS with only the NAME, AGE, and ADDRESS columns from the CUSTOMERS table, and populate it with the corresponding data. SELECT name, age, address INTO CUSTOMER_DETAILS FROM CUSTOMERS; Output We get the following result. We can observe that 7 rows have been modified. (7 rows affected) Verification We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMER_DETAILS table − SELECT * from CUSTOMER_DETAILS; The table displayed is as follows − NAME AGE ADDRESS Ramesh 32 Ahmedabad Khilan 25 Delhi Kaushik 23 Kota Chaitali 25 Mumbai Hardik 27 Bhopal Komal 22 Hyderabad Muffy 24 Indore Note: The new table will not include any other columns from the original table. Also the original table remains unchanged. Copying Data From Multiple Tables Using the SQL SELECT INTO statement we can also copy data from multiple tables to a new table. This is accomplished using the JOIN clause which combines the data from multiple tables (based on a common column). Syntax Following is the syntax to copy data from multiple tables using the SELECT INTO statement − SELECT column1, column2, …, columnN INTO new_table_name FROM table1 JOIN table2 ON table1.column = table2.column Example First of all, let us create another table named ORDERS − CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2)); Using the INSERT statement, insert values into this table as follows − INSERT INTO ORDERS VALUES (102, ”2009-10-08 00:00:00”, 3, 3000.00), (100, ”2009-10-08 00:00:00”, 3, 1500.00), (101, ”2009-11-20 00:00:00”, 2, 1560.00), (103, ”2008-05-20 00:00:00”, 4, 2060.00); The table is created as − OID DATE CUSTOMER_ID AMOUNT 102 2009-10-08 00:00:00 3 3000.00 100 2009-10-08 00:00:00 3 1500.00 101 2009-11-20 00:00:00 2 1560.00 103 2008-05-20 00:00:00 4 2060.00 Now, we are creating a new table called CUSTOMER_ORDERS that includes the customer name from the CUSTOMERS table and the customer id from the ORDERS table, where the id of customers from the CUSTOMERS table matches with the id of customers from the ORDERS table − SELECT CUSTOMERS.Name, ORDERS.customer_id INTO CUSTOMER_ORDERS FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.customer_id; Output We get the following result. We can observe that 8 rows have been modified. (8 rows affected) Verification We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMER_ORDERS table − SELECT * FROM CUSTOMER_ORDERS; The table displayed is as

SQL – SELECT Query Table of content The SQL SELECT Statement Computing Using SELECT Aliasing a Column in SELECT Statement ”; Previous Next The SQL SELECT Statement The SQL SELECT Statement is used to fetch the data from a database table which returns this data in the form of a table. These tables are called result-sets. CLAUSES and OPERATORS available in SQL can be used with the SELECT statement in order to retrieve the filtered records of a database table. Syntax The basic syntax of the SELECT Query is as follows − SELECT column1, column2, columnN FROM table_name; Here, column1, column2… are the fields of a table whose values you want to fetch. If you want to fetch all the columns available in a table, then you can use the following syntax − SELECT * FROM table_name; Example Assume we have created a table named CUSTOMERS using the CREATE TABLE statement 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, insert values 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 − 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 Retrieving Selected Fields/Columns The following statement fetches the ID, Name and Salary fields of the records available in CUSTOMERS table. SELECT ID, NAME, SALARY FROM CUSTOMERS; Output The above query would produce the following table − ID NAME Salary 1 Ramesh 2000.00 2 Khilan 1500.00 3 Kaushik 2000.00 4 Chaitali 6500.00 5 Hardik 8500.00 6 Komal 4500.00 7 Muffy 10000.00 Retrieving All Fields/Columns If you want to fetch all the fields of the CUSTOMERS table, then you should use the query of SELECT statement with an Asterisk (*) instead of the column names, as shown below − SELECT * FROM CUSTOMERS; Output The resultant table will be − 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 Computing Using SELECT The SQL SELECT statement can also be used to retrieve the results of various mathematical computations in the form of a table. In such cases, you do not need to specify any database table in the statement. Following is the syntax to do so − SELECT mathematical_expression; Example Following is an example which multiply two given numbers using SQL statement. SELECT 56*65; The query above produces the following output − 56*65 3640 Aliasing a Column in SELECT Statement Whenever a column name in a table is too difficult to read and understand, SQL provides a method to alias this column name into another understandable and relative name. This is done using the AS keyword. You can use the AS keyword in a SELECT statement to display the column names of a table as an alias name. Following is the syntax to do so − SELECT column_name AS alias_name FROM table_name; You can also use an alias to display SELECT expressions with the same syntax; you should use a mathematical statement instead of column_name. Example In the example below, we are trying to retrieve customer details NAME and AGE in a single column of the resultant table using the concat() expression and aliasing the column as DETAILS along with the customer addresses from the CUSTOMERS table. This will be done using SELECT statement in the following query − SELECT CONCAT(NAME,” ”,AGE) AS DETAILS, ADDRESS FROM CUSTOMERS ORDER BY NAME; The query above produces the following output − DETAILS ADDRESS Chaitali 25 Mumbai Hardik 27 Bhopal Kaushik 23 Kota Khilan 25 Delhi Komal 22 Hyderabad Muffy 24 Indore Ramesh 32 Ahmedabad Print Page Previous Next Advertisements ”;

SQL – Delete Table Table of content SQL DELETE TABLE Statement SQL DELETE TABLE with WHERE Clause Deleting rows based on multiple conditions Deleting all the records in a table ”; Previous Next The SQL DELETE is a command of Data Manipulation Language (DML), so it does not delete or modify the table structure but it delete only the data contained within the table. Therefore, any constraints, indexes, or triggers defined in the table will still exist after you delete data from it. SQL DELETE TABLE Statement The SQL DELETE TABLE statement is used to delete the existing records from a table in a database. If you wish to delete only the specific number of rows from the table, you can use the WHERE clause with the DELETE statement. If you omit the WHERE clause, all rows in the table will be deleted. The SQL DELETE statement operates on a single table at a time. Syntax Following is the basic syntax for using the SQL DELETE command in SQL − DELETE FROM table_name; SQL DELETE TABLE with WHERE Clause We can use the SQL DELETE statement to delete specific rows from a table based on a single condition using the WHERE clause. Syntax Following is the syntax for deleting specific rows based on single condition − DELETE FROM table_name WHERE condition; Example Assume we have creating a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc. 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 values 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 ); If you retrieve the contents of the above created table using the SELECT * FROM CUSTOMERS statement you will get the following output − 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 try to delete all the customers with the name ”Hardik” as shown in the query below − DELETE FROM CUSTOMERS WHERE NAME=”Hardik”; Output We get the following result. We can observe that 1 row has been deleted. Query OK, 1 row affected (0.05 sec) Verification Now if you retrieve the contents of the CUSTOMERS table using the SELECT * FROM CUSTOMERS command you will get the following output − 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 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Deleting rows based on multiple conditions We can also use the SQL DELETE TABLE statement to delete specific rows from a table based on multiple conditions using the WHERE clause. This is useful when we want to remove a subset of rows from a table that meet a certain criterion. When using multiple conditions, we can use the comparison operators such as AND, OR, and NOT to refine our conditions. This way, only rows that satisfy the conditions will be deleted. Syntax Following is the basic syntax for deleting specific rows based on multiple conditions which can be connected using either AND or OR operators − DELETE FROM table_name WHERE condition1 AND condition2 OR … conditionN; Here, table_name is the name of the table from which we want to delete rows, and condition1 through conditionN are the conditions that must be met for the rows to be deleted. The AND or OR operators can be used to join the conditions together. Example In the following query we are trying to delete all the customers whose name is either ”Komal” or their address is ”Mumbai” − DELETE FROM CUSTOMERS WHERE NAME=”Komal” OR ADDRESS=”Mumbai”; Output We get the following result. We can observe that 2 rows has been deleted. Query OK, 2 rows affected (0.03 sec) Verification Now if you retrieve the contents of the CUSTOMERS table using the SELECT * FROM CUSTOMERS command you will get the following output − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Deleting all the records in a table We can use the SQL DELETE TABLE statement without a WHERE clause to delete all records in a table in SQL. This statement will remove all the rows from the specified table, effectively resetting the table to its original state (containing only the structure and its constraints). However, it”s important to note that this operation cannot be undone, and all the data in the table will be permanently deleted. Example In here, we are trying to delete all the records from the CUSTOMERS table − DELETE FROM CUSTOMERS; Output Following is the result produced by executing the above query − Query OK, 4 rows affected (0.13 sec) Verification Now, if you retrieve the contents of the CUSTOMERS table using the SELECT * FROM CUSTOMERS statement you will get the following result − Empty set (0.00 sec) Print Page Previous Next Advertisements ”;

SQL – DROP Table Table of content The SQL DROP Table Statement The IF EXISTS Clause DROP – TEMPORARY TABLE ”; Previous Next SQL provides command to DROP an existing table completely in a database. Once SQL DROP command is issued then there is no way back to recover the table including its data, so be careful before issuing this command in production system. The SQL DROP Table Statement The SQL DROP TABLE statement is a Data Definition Language (DDL) command that is used to remove a table”s definition, and its data, indexes, triggers, constraints and permission specifications (if any). Note − You should be very careful while using this command because once a table is deleted then all the information available in that table will also be lost forever. If the table is partitioned, the statement removes the table definition, all its partitions, all data stored in those partitions, and all partition definitions. To drop a table in a database, one must require ALTER permission on the said table and CONTROL permissions on the table schema. Even though it is a data definition language command, it is different from TRUNCATE TABLE statement as the DROP statement completely frees the table from the memory. DROP TABLE causes an implicit commit, except when used with the TEMPORARY keyword. Syntax The basic syntax of this DROP TABLE statement is as follows − DROP TABLE table_name; Example Assume we have created a table named CUSTOMERS using the CREATE TABLE statement 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) ); Let us first verify the CUSTOMERS table using the DESC command then we will delete it from the database − DESC Table If the table is created successfully the DESC command displays the structure of the table as shown below − 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 This means that the CUSTOMERS table is available in the database, so let us now drop it as shown below. DROP TABLE CUSTOMERS; Output The output is displayed as follows − Query OK, 0 rows affected (0.001 sec) Verification Now, to verify if the table is actually dropped, you can use the DESC CUSTOMERS command as shown − DESC CUSTOMERS; Following error is displayed − ERROR 1146 (42S02): Table ”tutorials.CUSTOMERS” doesn”t exist When a MySQL table is dropped using SQL DROP command, privileges granted specifically for the table are not automatically dropped. They must be dropped manually. The IF EXISTS Clause Instead of always checking if the table exists or not in a database before dropping it, you can use the IF EXISTS clause in the DROP TABLE statement. This clause, when specified in the DROP TABLE query, will automatically check whether the table exists in the current database and then drops it, if yes. If the table does not exist in the database, the query will be ignored. Syntax Following is the basic syntax of DROP TABLE IF EXISTS − DROP TABLE [IF EXISTS] table_name; Example If you try to drop a table that doesn”t exist in the database, without using the IF EXISTS clause, as shown below − DROP TABLE CUSTOMERS; An error will be generated − ERROR 1051 (42S02): Unknown table ”tutorials.CUSTOMERS” If you use the IF EXISTS clause along with the DROP TABLE statement as shown below, the specified table will be dropped and if a table with the given name, doesn”t exist the query will be ignored. But if you try to drop a table that does not exist in a database, using the IF EXISTS clause, as shown below − DROP TABLE IF EXISTS CUSTOMERS; The query will be ignored with the following output displayed − Query OK, 0 rows affected, 1 warning (0.001 sec) DROP – TEMPORARY TABLE You can include TEMPORARY keyword with DROP TABLE statement which will drop only TEMPORARY tables. Including the TEMPORARY keyword is a good way to prevent accidentally dropping non-TEMPORARY tables. Syntax DROP TEMPORARY TABLE TEMP_TABLE; Example Following is an example to delete a temporary table CUSTOMERS. DROP TEMPORARY TABLE CUSTOMERS; Print Page Previous Next Advertisements ”;

SQL – Constraints Table of content SQL Constraints Introduction SQL Create Constraints NOT NULL Constraint UNIQUE Key Constraint DEFAULT Value Constraint PRIMARY Key Constraint FOREIGN Key Constraint CHECK Value Constraint INDEX Constraint Dropping SQL Constraints Data Integrity Constraints ”; Previous Next SQL Constraints SQL Constraints are the rules applied to a data columns or the complete table to limit the type of data that can go into a table. When you try to perform any INSERT, UPDATE, or DELETE operation on the table, RDBMS will check whether that data violates any existing constraints and if there is any violation between the defined constraint and the data action, it aborts the operation and returns an error. We can define a column level or a table level constraints. The column level constraints are applied only to one column, whereas the table level constraints are applied to the whole table. SQL Create Constraints We can create constraints on a table at the time of a table creation using the CREATE TABLE statement, or after the table is created, we can use the ALTER TABLE statement to create or delete table constraints. CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, …. columnN datatype constraint ); Different RDBMS allows to define different constraints. This tutorial will discuss about 7 most important constraints available in MySQL. NOT NULL Constraint When applied to a column, NOT NULL constraint ensure that a column cannot have a NULL value. Following is the example to create a NOT NULL constraint: CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2) ); Check further detail on NOT NULL Constraint UNIQUE Key Constraint When applied to a column, UNIQUE Key constraint ensure that a column accepts only UNIQUE values. Following is the example to create a UNIQUE Key constraint on column ID. Once this constraint is created, column ID can”t be null and it will accept only UNIQUE values. CREATE TABLE CUSTOMERS ( ID INT NOT NULL UNIQUE, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2) ); Check further detail on Unique Key Constraint DEFAULT Value Constraint When applied to a column, DEFAULT Value constraint provides a default value for a column when none is specified. Following is the example to create a DEFAULT constraint on column NAME. Once this constraint is created, column NAME will set to “Not Available” value if NAME is not set to a value. CREATE TABLE CUSTOMERS ( ID INT NOT NULL UNIQUE, NAME VARCHAR (20) DEFAULT ”Not Available”, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2) ); Check further detail on DEFAULT Value Constraint PRIMARY Key Constraint When applied to a column, PRIMARY Key constraint ensure that a column accepts only UNIQUE value and there can be a single PRIMARY Key on a table but multiple columns can constituet a PRIMARY Key. Following is the example to create a PRIMARY Key constraint on column ID. Once this constraint is created, column ID can”t be null and it will accept only unique values. 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) ); Check further detail on PRIMARY Key Constraint FOREIGN Key Constraint FOREIGN Key constraint maps with a column in another table and uniquely identifies a row/record in that table. Following is an example to create a foreign key constraint on column ID available in CUSTOMERS table as shown in the statement below − CREATE TABLE ORDERS ( ID INT NOT NULL, DATE DATETIME, CUSTOMER_ID INT FOREIGN KEY REFERENCES CUSTOMERS(ID), AMOUNT DECIMAL, PRIMARY KEY (ID) ); Check further detail on FOREIGN Key Constraint CHECK Value Constraint When applied to a column, CHECK Value constraint works like a validation and it is used to check the validity of the data entered into the particular column of the table. table and uniquely identifies a row/record in that table. Following is an example to create a CHECK validation on AGE column which will not accept if its value is below to 18. CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL CHECK(AGE>=18), ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Check further detail on CHECK Value Constraint INDEX Constraint The INDEX constraints are created to speed up the data retrieval from the database. An Index can be created by using a single or group of columns in a table. A table can have a single PRIMARY Key but can have multiple INDEXES. An Index can be Unique or Non Unique based on requirements. Following is an example to create an Index on Age Column of the CUSTOMERS table. CREATE INDEX idx_age ON CUSTOMERS ( AGE ); Check further detail on INDEX Constraint Dropping SQL Constraints Any constraint that you have defined can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option. For example, to drop the primary key constraint from the CUSTOMERS table, you can use the following command. ALTER TABLE CUSTOMERS DROP CONSTRAINT PRIMARY KEY; Some RDBMS allow you to disable constraints instead of permanently dropping them from the database, which you may want to temporarily disable the constraints and then enable them later. Data Integrity Constraints Data integrity constraints are used to ensure the overall accuracy, completeness, and consistency of data. Now a days data integrity also refers to the data safety in regard to regulatory compliance, such as GDPR compliance etc. Data integrity is handled in a relational database through the concept of referential integrity. There are many types of integrity constraints that play a role in Referential Integrity (RI). These constraints include Primary Key, Foreign Key, Unique Constraints and other constraints which are mentioned above. Print Page Previous Next

SQL – Temporary Tables Table of content What are Temporary Tables? Creating Temporary Tables in MySQL Dropping Temporary Tables in MySQL Temporary Tables in SQL Server ”; Previous Next What are Temporary Tables? Temporary tables are pretty much what their name describes: they are the tables which are created in a database to store temporary data. We can perform SQL operations similar to the operations on permanent tables like CREATE, UPDATE, DELETE, INSERT, JOIN, etc. But these tables will be automatically deleted once the current client session is terminated. In addition to that, they can also be explicitly deleted if the users decide to drop them manually. Various RDBMS, like MySQL, support temporary tables starting from version 3.23 onwards. If you are using an older version of MySQL than 3.23, you can”t use temporary tables, but you can use heap tables. As stated earlier, temporary tables will only last as long as the client session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQL database server through a MySQL client program, then the temporary table will exist until you close the client connection or manually destroy the table. Creating Temporary Tables in MySQL To create temporary tables in MySQL, we follow the same query as creating regular database tables. However, instead of using the CREATE TABLE statement, you use CREATE TEMPORARY TABLE statement. Syntax Following is the syntax to create a temporary table − CREATE TEMPORARY TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ….. columnN datatype, PRIMARY KEY( one or more columns ) ); Example Following is the SQL Query to create a temporary table in MySQL database − CREATE TEMPORARY 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) ); Just like normal tables you can insert data into a temporary table using the INSERT statement. Following query inserts 3 records into the above created temporary table − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ); The temporary table CUSTOMERS will be created and will have following records − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 When you issue a SHOW TABLES command, then your temporary table will not be displayed in the list of tables. To verify whether the temporary table is created you need to retrieve its data using the SELECT statement. Since all the temporary tables will be removed when the current session is closed, if you log out of the MySQL session and then issue a SELECT command, you will not find temporary table in the database. Dropping Temporary Tables in MySQL Though all the temporary tables are deleted by MySQL when your database connection gets terminated, still, if you want to delete them manually, then you can do so by issuing a DROP TEMPORARY TABLE command. Syntax Following is the basic syntax to delete a temporary table: DROP TEMPORARY TABLE table_name; Example Following query drops the temporary table CUSTOMERS created in the previous example − DROP TEMPORARY TABLE CUSTOMERS; Verification Since we have removed the temporary table CUSTOMERS, if you try to retrieve the contents of it using the SELECT statement, it will generate an error saying the table does not exist. SELECT * FROM CUSTOMERS; This will produce following result − ERROR 1146: Table ”TUTORIALS.CUSTOMERS” doesn”t exist Temporary Tables in SQL Server The temporary table created in MySQL is visible only within the current session. But, in Microsoft SQL Server you can create two types of temporary tables. Local Temporary Tables: A Local Temporary Table is accessible only in the session that has created it. It is automatically deleted when the connection that has created it gets closed. If the Temporary Table is created inside the stored procedure, it get dropped automatically upon the completion of stored procedure execution. Global Temporary Tables: Global Temporary Tables are visible to all connections and Dropped when the last connection referencing the table is closed. Syntax of the Local Temporary Tables To create Local Temporary Table in SQL Server a single # is used as the prefix of a table name, as shown below − CREATE TABLE #table_name( column1 datatype, column2 datatype, column3 datatype, ….. columnN datatype, PRIMARY KEY( one or more columns ) ); Example of the Local Temporary Tables Following query creates a Local temporary table named CUSTOMERS in the SQL server − 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) ); Syntax of the Global Temporary Tables To create a Global Temporary Table, we need to add the prefix ## before the table name, as shown below − CREATE TABLE ##table_name( column1 datatype, column2 datatype, column3 datatype, ….. columnN datatype, PRIMARY KEY( one or more columns ) ); Example of the Global Temporary Tables Following query creates a Global temporary table named Buyers in the SQL server − 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) ); Dropping Temporary Tables in SQL Server If you want to drop a temporary table in SQL Server manually, you need to execute the DROP TABLE statement by placing # before the local temporary table name and ## before the global temporary table name. Example Following query removes the Local temporary table Customers created in the previous example. DROP TABLE #Customers; Whereas, following query removes the global temporary table Buyers. DROP TABLE ##Buyers; Print Page Previous Next Advertisements ”;

SQL – ALTER TABLE Table of content SQL − ALTER TABLE Statement ALTER TABLE − ADD Column ALTER TABLE − DROP COLUMN ALTER TABLE − ADD INDEX ALTER TABLE − DROP INDEX ALTER TABLE − ADD PRIMARY KEY ALTER TABLE − DROP PRIMARY KEY ALTER TABLE − ADD CONSTRAINT ALTER TABLE − DROP CONSTRAINT ALTER TABLE − RENAME COLUMN ALTER TABLE − MODIFY DATATYPE ”; Previous Next SQL − ALTER TABLE Statement The SQL ALTER TABLE command is a part of Data Definition Language (DDL) and modifies the structure of a table. The ALTER TABLE command can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. The ALTER TABLE command can also change characteristics of a table such as the storage engine used for the table. We will make use of the following table in our examples 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 Syntax Following is the basic syntax of an ALTER TABLE command − ALTER TABLE table_name [alter_option …]; Where, the alter_option depends on the type of operation to be performed on a table. This article will discuss such important operations one by one. ALTER TABLE − ADD Column If you need to add a new column to a table, you should use the ADD COLUMN option along with ALTER TABLE statement as shown below − ALTER TABLE table_name ADD column_name datatype; Example Following is the example to ADD a New Column to an existing table − ALTER TABLE CUSTOMERS ADD SEX char(1); Output Executing the query above will produce the following output − Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification To verify whether the CUSTOMERS table is altered by adding a new column SEX, use the SELECT statement to retrieve the records of the table − SELECT * FROM CUSTOMERS; Now, the CUSTOMERS table will be displayed as follows − ID NAME AGE ADDRESS SALARY SEX 1 Ramesh 32 Ahmedabad 2000.00 NULL 2 Khilan 25 Delhi 1500.00 NULL 3 Kaushik 23 Kota 2000.00 NULL 4 Chaitali 25 Mumbai 6500.00 NULL 5 Hardik 27 Bhopal 8500.00 NULL 6 Komal 22 Hyderabad 4500.00 NULL 7 Muffy 24 Indore 10000.00 NULL ALTER TABLE − DROP COLUMN If you need to drop an existing column from a table, you should use the DROP COLUMN option along with ALTER TABLE statement as shown below. ALTER TABLE table_name DROP COLUMN column_name; Example Following is the example to DROP sex column from the existing table. ALTER TABLE CUSTOMERS DROP COLUMN SEX; Output Executing the query above will produce the following output − Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification To verify whether the CUSTOMERS table is altered by dropping an existing column SEX, use the SELECT statement to retrieve the records of the table − SELECT * FROM CUSTOMERS; Now, the CUSTOMERS table is changed and following would be the output from the SELECT statement. 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 ALTER TABLE − ADD INDEX You can add index to an existing column of a table using the ADD INDEX statement along with the ALTER statement − ALTER TABLE table_name ADD INDEX index_name [index_type] Example Following query adds an index on the column NAME of CUSTOMERS table − ALTER TABLE CUSTOMERS ADD INDEX name_index (NAME); Output The output will be displayed as − Query OK, 0 rows affected (0.003 sec) Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE − DROP INDEX You can drop an existing index from a table using the DROP INDEX statement along with the ALTER statement − ALTER TABLE table_name DROP INDEX index_name; Example Following query adds an index on the column NAME of CUSTOMERS table − ALTER TABLE CUSTOMERS DROP INDEX name_index; Output The output will be displayed as − Query OK, 0 rows affected (0.003 sec) Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE − ADD PRIMARY KEY Following is the syntax to add a primary key in an existing table of a database − ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2…); Example Before we add a primary key to an existing table, first let”s create a new table called EMPLOYEES as follows: CREATE TABLE EMPLOYEES( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2) ); Following query adds primary key constraint on the column ID of EMPLOYEES table − ALTER TABLE EMPLOYEES ADD CONSTRAINT MyPrimaryKey PRIMARY KEY(ID); This will produce the following output − Query OK, 0 rows affected, 1 warning (0.003 sec) Records: 0 Duplicates: 0 Warnings: 1 Verification To verify the above query if you describe the table using the DESC EMPLOYEES command − DESC EMPLOYEES; 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 ALTER TABLE − DROP PRIMARY KEY Following is the syntax to delete a primary key from an existing table of a database − ALTER TABLE table_name DROP PRIMARY KEY; Example Following query deletes primary key constraint from the column ID of EMPLOYEES table − ALTER TABLE EMPLOYEES DROP PRIMARY KEY; This will produce the following output − Query OK, 0 rows affected, 1 warning (0.003 sec) Records: 0 Duplicates: 0 Warnings: 1 ALTER TABLE − ADD CONSTRAINT Following is the syntax to add a unique constraint to a column of an