Category: sql

SQL – UNION vs UNION ALL Table of content What is UNION? What is UNION ALL ”; Previous Next UNION and UNION ALL operators are just the SQL implementation of algebraic set operators. Both of them are used to retrieve the rows from multiple tables and return them as one single table. The difference between these two operators is that UNION only returns distinct rows while UNION ALL returns all the rows present in the tables. However, for these operators to work on these tables, they need to follow the conditions given below − The tables to be combined must have the same number of columns with the same datatype. The number of rows need not be the same. Once these criterion are met, UNION or UNION ALL operator returns the rows from multiple tables as a resultant table. Column names of first table will become column names of the resultant table, and contents of second table will be merged into resultant columns of same data type. What is UNION? UNION is a type of operator/clause in SQL, that works similar to the union operator in relational algebra. It just combines the information from multiple tables that are union compatible. Only distinct rows from the tables are added to the resultant table, as UNION automatically eliminates all the duplicate records. Syntax Following is the syntax of UNION operator in SQL − SELECT * FROM table1 UNION SELECT * FROM table2; Example Let us first create two tables COURSES_PICKED and EXTRA_COURSES_PICKED with the same number of columns having the same data types. Create table COURSES_PICKED using the following query − CREATE TABLE COURSES_PICKED( STUDENT_ID INT NOT NULL, STUDENT_NAME VARCHAR(30) NOT NULL, COURSE_NAME VARCHAR(30) NOT NULL ); Insert values into the COURSES_PICKED table with the help of the query given below − INSERT INTO COURSES_PICKED VALUES (1, ”JOHN”, ”ENGLISH”), (2, ”ROBERT”, ”COMPUTER SCIENCE”), (3, ”SASHA”, ”COMMUNICATIONS”), (4, ”JULIAN”, ”MATHEMATICS”); The table will be displayed as shown below − STUDENT_ID STUDENT_NAME COURSE_NAME 1 JOHN ENGLISH 2 ROBERT COMPUTER SCIENCE 3 SASHA COMMUNICATIONS 4 JULIAN MATHEMATICS Now, let us create another table EXTRA_COURSES_PICKED using the following query − CREATE TABLE EXTRA_COURSES_PICKED( STUDENT_ID INT NOT NULL, STUDENT_NAME VARCHAR(30) NOT NULL, EXTRA_COURSE_NAME VARCHAR(30) NOT NULL ); Following is the query to insert values into the “EXTRA_COURSES_PICKED” table − INSERT INTO EXTRA_COURSES_PICKED VALUES (1, ”JOHN”, ”PHYSICAL EDUCATION”), (2, ”ROBERT”, ”GYM”), (3, ”SASHA”, ”FILM”), (4, ”JULIAN”, ”MATHEMATICS”); The table will be created as shown below − STUDENT_ID STUDENT_NAME COURSES_PICKED 1 JOHN PHYSICAL EDUCATION 2 ROBERT GYM 3 SASHA FILM 4 JULIAN MATHEMATICS Now, let us combine both of these tables using the UNION query as follows − SELECT * FROM COURSES_PICKED UNION SELECT * FROM EXTRA_COURSES_PICKED; Output The resultant table obtained after performing the UNION operation is as follows − STUDENT_ID STUDENT_NAME COURSE_NAME 1 JOHN ENGLISH 2 ROBERT COMPUTER SCIENCE 3 SASHA COMMUNICATIONS 4 JULIAN MATHEMATICS 1 JOHN PHYSICAL EDUCATION 2 ROBERT GYM 3 SASHA FILM What is UNION ALL? UNION ALL is also an operator/clause in SQL, that is used to combine multiple tables into one table. However, this operator also preserves the duplicate rows in the resultant tables. Suppose there are two tables, one of which contains the number of games a player competed in internationally and the other contains the number of games a player played nationally. As we can see in the tables above, Kohli played 234 matches internationally and 234 matches nationally. Even though the data in these columns is the same, they are all separate matches. There is a need to include both rows in the resultant table displaying the total matches played by a player. So, we use the UNION ALL operator in such cases. Syntax Following is the syntax of UNION ALL operator in SQL − SELECT * FROM table1 UNION ALL SELECT * FROM table2; Example In the following example, let us perform UNION ALL operation on the same sample tables given above: “COURSES_PICKED” and “EXTRA_COURSES_PICKED”, using the given query below − SELECT * FROM COURSES_PICKED UNION ALL SELECT * FROM EXTRA_COURSES_PICKED; Output The resultant table is displayed as follows − STUDENT_ID STUDENT_NAME COURSE_NAME 1 JOHN ENGLISH 2 ROBERT COMPUTER SCIENCE 3 SASHA COMMUNICATIONS 4 JULIAN MATHEMATICS 1 JOHN PHYSICAL EDUCATION 2 ROBERT GYM 3 SASHA FILM 4 JULIAN MATHEMATICS Print Page Previous Next Advertisements ”;

SQL – Having Clause Table of content The SQL HAVING Clause HAVING with GROUP BY Clause HAVING with ORDER BY Clause HAVING Clause with COUNT() Function HAVING Clause with AVG() Function HAVING Clause with MAX() Function ”; Previous Next The SQL HAVING Clause The SQL HAVING clause is similar to the WHERE clause; both are used to filter rows in a table based on specified criteria. However, the HAVING clause is used to filter grouped rows instead of single rows. These rows are grouped together by the GROUP BY clause, so, the HAVING clause must always be followed by the GROUP BY clause. Moreover, the HAVING clause can be used with aggregate functions such as COUNT(), SUM(), AVG(), etc., whereas the WHERE clause cannot be used with them. Syntax Following is the basic syntax of the SQL HAVING clause − SELECT column1, column2, aggregate_function(column) FROM table_name GROUP BY column1, column2 HAVING condition; The following code block shows the position of the HAVING Clause in a query − SELECT FROM WHERE GROUP BY HAVING ORDER BY HAVING with GROUP BY Clause We can use the HAVING clause with the GROUP BY clause to filter groups of rows that meet certain conditions. It is used to apply a filter to the result set after the aggregation has been performed. Example Assume we have created a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary, 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 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 created 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 Now, we are grouping the records of the CUSTOMERS table based on the columns ADDRESS and AGE and filtering the groups where the AGE value is less than 25. SELECT ADDRESS, AGE, MIN(SALARY) AS MIN_SUM FROM CUSTOMERS GROUP BY ADDRESS, AGE HAVING AGE > 25; Output The result produced is as follows − ADDRESS AGE MIN_SUM Ahmedabad 32 2000.00 Bhopal 27 8500.00 HAVING with ORDER BY Clause The ORDER BY clause is used to arrange/sort the records of the result of a SELECT query based on a specific column (either in ascending order or in descending order). If we use the ORDER BY clause with the HAVING clause we can sort the filtered groups in the desired order. Example Following query groups the records of the CUSTOMERS table based on the columns AGE and ADDRESS, filters the groups where the SALARY value is less than 5000 and, arranges the remaining groups in descending order based the total salaries of each group. SELECT ADDRESS, AGE, SUM(SALARY) AS TOTAL_SALARY FROM CUSTOMERS GROUP BY ADDRESS, AGE HAVING TOTAL_SALARY >=5000 ORDER BY TOTAL_SALARY DESC; Output The result produced is as follows − ADDRESS AGE TOTAL_SALARY Indore 24 10000.00 Bhopal 27 8500.00 Mumbai 25 6500.00 HAVING Clause with COUNT() Function The HAVING clause can be used with the COUNT() function to filter groups based on the number of rows they contain. Example Following query groups the records of the CUSTOMERS table based on the AGE column and, retrieves the details of the group that has more than two entities − SELECT AGE, COUNT(AGE) FROM CUSTOMERS GROUP BY AGE HAVING COUNT(age) > 2; Output This would produce the following result − Query OK, 0 rows affected (0.02 sec) HAVING Clause with AVG() Function The HAVING clause can also be used with the AVG() function to filter groups based on the average value of a specified column. Example Now, we are retrieving the city of the customers whose average salary is greater than 5240 − SELECT ADDRESS, AVG(SALARY) as AVG_SALARY FROM CUSTOMERS GROUP BY ADDRESS HAVING AVG(SALARY) > 5240; Output Following is the output of the above query − ADDRESS AVG_SALARY Mumbai 6500.000000 Bhopal 8500.000000 Indore 10000.000000 HAVING Clause with MAX() Function We can also use the HAVING clause with MAX() function to filter groups based on the maximum value of a specified column. Example Now, we are retrieving the city of the customers whose maximum salary is greater than 5240 − SELECT ADDRESS, MAX(SALARY) as MAX_SALARY FROM CUSTOMERS GROUP BY ADDRESS HAVING MAX(SALARY) > 5240; Output The result obtained is as follows − ADDRESS MAX_SALARY Mumbai 6500.00 Bhopal 8500.00 Indore 10000.00 Print Page Previous Next Advertisements ”;

SQL – Joins Table of content The SQL Join Clause Types of joins in SQL ”; Previous Next The SQL Join Clause The SQL Join clause is used to combine data from two or more tables in a database. When the related data is stored across multiple tables, joins help you to retrieve records combining the fields from these tables using their foreign keys. The part of the Join clause that specifies the columns on which records from two or more tables are joined is known as join-predicate. This predicate is usually specified along with the ON clause and uses various comparison operators such as, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT etc. We can also connect multiple join predicates with logical operators AND, OR, and NOT. We can use JOINs along with update and delete, SQL queries to update and delete records from across multiple tables. When you retrieve a table using joins, the resultant table displayed is not stored anywhere in the database. Syntax Following is the basic syntax of a the SQL JOIN CLAUSE − SELECT column_name(s) FROM table1 JOIN table2; Example Assume we have created a CUSTOMERS table that contains details of the customers of an organization 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 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 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 is another table ORDERS which contains the order details made by the customers. 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 ORDERS table will be created as follows − 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 Following query performs the join operation on the tables CUSTMERS and ORDERS − SELECT ID, NAME, AGE, AMOUNT FROM CUSTOMERS JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID; Output By executing the query above, the resultant table is displayed and contains the values present in ID, NAME, AGE fields of CUSTOMERS table and AMOUNT field of ORDERS table. ID NAME AGE AMOUNT 3 Kaushik 23 3000 3 Kaushik 23 1500 2 Khilan 25 1560 4 Chaitali 25 2060 Types of joins in SQL SQL provides various types of Joins that are categorized based on the way data across multiple tables are joined together. They are listed as follows − Inner Join An INNER JOIN is the default join which retrieves the intersection of two tables. It compares each row of the first table with each row of the second table. If the pairs of these rows satisfy the join-predicate, they are joined together. Outer Join An Outer Join retrieves all the records in two tables even if there is no counterpart row of one table in another table, unlike Inner Join. Outer join is further divided into three subtypes – Left Join, Right Join and Full Join. Following are the different types of outer Joins − LEFT JOIN − returns all rows from the left table, even if there are no matches in the right table. RIGHT JOIN − returns all rows from the right table, even if there are no matches in the left table. FULL JOIN − returns rows when there is a match in one of the tables. Other Joins In addition to these there are two more joins − SELF JOIN − is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement. CROSS Join − returns the Cartesian product of the sets of records from the two or more joined tables. Print Page Previous Next Advertisements ”;

SQL – EXCEPT Table of content The SQL EXCEPT Operator EXCEPT with BETWEEN Operator Except with IN Operator EXCEPT with LIKE Operator ”; Previous Next The SQL EXCEPT Operator The EXCEPT operator in SQL is used to retrieve all the unique records from the left operand (query), except the records that are present in the result set of the right operand (query). In other words, this operator compares the distinct values of the left query with the result set of the right query. If a value from the left query is found in the result set of the right query, it is excluded from the final result. For better understanding consider two tables with records as shown in the following image − If we perform the EXCEPT operator on the above two tables to retrieve the names, it will display the distinct records only from the first table which are not in common with the records of the second table. Here, “Dev” is common in both tables. So, the EXECPT operator will eliminate it and retrieves only “Sara” and “Jay” as output. MySQL database does not support the EXCEPT operator. Instead of this, we can use the DISTINCT keyword along with the LEFT JOIN clause to retrieve distinct values from the left table. Syntax Following is the SQL syntax of the EXCEPT operator in Microsoft SQL server − SELECT column1, column2,…, columnN FROM table1, table2,…, tableN [Conditions] //optional EXCEPT SELECT column1, column2,…, columnN FROM table1, table2,…, tableN [Conditions] //optional The number and order of columns in both SELECT statements should be the same. Example First of all, let us create a table named STUDENTS using the following query − CREATE TABLE STUDENTS( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, SUBJECT VARCHAR(20) NOT NULL, AGE INT NOT NULL, HOBBY VARCHAR(20) NOT NULL, PRIMARY KEY(ID) ); Let”s insert some values into the table using the following query − INSERT INTO STUDENTS VALUES (1, ”Naina”, ”Maths”, 24, ”Cricket”), (2, ”Varun”, ”Physics”, 26, ”Football”), (3, ”Dev”, ”Maths”, 23, ”Cricket”), (4, ”Priya”, ”Physics”, 25, ”Cricket”), (5, ”Aditya”, ”Chemistry”, 21, ”Cricket”), (6, ”Kalyan”, ”Maths”, 30, ”Football”), (7, ”Aditya”, ”Chemistry”, 21, ”Cricket”), (8, ”Kalyan”, ”Chemistry”, 32, ”Cricket”); The table produced is as shown below − ID NAME SUBJECT AGE HOBBY 1 Naina Mathematics 24 Cricket 2 Varun Physics 26 Football 3 Dev Mathematics 23 Cricket 4 Priya Physics 25 Cricket 5 Aditya Chemistry 21 Cricket 6 Kalyan Mathematics 30 Football 7 Aditya Chemistry 21 Cricket 8 Kalyan Chemistry 32 Cricket Now, let us create another table named STUDENTS_HOBBY using the following query − CREATE TABLE STUDENTS_HOBBY( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, HOBBY VARCHAR(20) NOT NULL, AGE INT NOT NULL, PRIMARY KEY(ID) ); Once the table is created, let us insert some values to the table using the query below − INSERT INTO STUDENTS_HOBBY VALUES (1, ”Vijay”, ”Cricket”, 18), (2, ”Varun”, ”Football”, 26), (3, ”Surya”, ”Cricket”, 19), (4, ”Karthik”, ”Cricket”, 25), (5, ”Sunny”, ”Football”, 26), (6, ”Dev”, ”Cricket”, 23); The table created is as follows − ID NAME HOBBY AGE 1 Vijay Cricket 18 2 Varun Football 26 3 Surya Cricket 19 4 Karthik Cricket 25 5 Sunny Football 26 6 Dev Cricket 23 Now, let us perform the except operation on the above two tables − SELECT NAME, HOBBY, AGE FROM STUDENTS EXCEPT SELECT NAME, HOBBY, AGE FROM STUDENTS_HOBBY; Output Output of the above query is as shown below − NAME HOBBY AGE Aditya Cricket 21 Kalyan Cricket 32 Kalyan Football 30 Naina Cricket 24 Priya Cricket 25 EXCEPT with BETWEEN Operator We can use the EXCEPT operator with the BETWEEN operator in SQL to exclude records that fall within a specified range. Example In the following SQL query, we are retrieving the records of students aged between 20 and 30 from the STUDENTS table, excluding those who are also aged between 20 and 30 from the STUDENTS_HOBBY table − SELECT NAME, HOBBY, AGE FROM STUDENTS WHERE AGE BETWEEN 20 AND 30 EXCEPT SELECT NAME, HOBBY, AGE FROM STUDENTS_HOBBY WHERE AGE BETWEEN 20 AND 30 Output When we execute the program query, the output is obtained as follows − NAME HOBBY AGE Aditya Cricket 21 Kalyan Football 30 Naina Cricket 24 Priya Cricket 25 Except with IN Operator The IN operator is used to filter a result set based on a list of specified values. We can also use the EXCEPT operator with the IN operator in SQL to exclude records that matches values in the specified list. Example Here, we are retrieving the records of students with Cricket as a hobby, from the STUDENTS table, excluding those who also have Cricket as hobby from the STUDENTS_HOBBY table − SELECT NAME, HOBBY, AGE FROM STUDENTS WHERE HOBBY IN(”Cricket”) EXCEPT SELECT NAME, HOBBY, AGE FROM STUDENTS_HOBBY WHERE HOBBY IN(”Cricket”) Output Following is the output of the above query − NAME HOBBY AGE Aditya Cricket 21 Kalyan Cricket 32 Naina Cricket 24 Priya Cricket 25 EXCEPT with LIKE Operator The LIKE operator is used to perform pattern matching on a string. The EXCEPT operator can also be used with the LIKE operator in SQL to exclude rows that matches with the specified pattern. Example In here, we are retrieving records from the STUDENTS table where the values in the HOBBY column starts with ”F”, while excluding similar rows from the STUDENTS_HOBBY table − SELECT ID, NAME, HOBBY, AGE FROM STUDENTS WHERE HOBBY LIKE ”F%” EXCEPT SELECT ID, NAME, HOBBY, AGE FROM STUDENTS_HOBBY WHERE HOBBY LIKE ”F%”; Output The output for the above query is produced as given below − ID NAME HOBBY AGE 6 Kalyan Football 30 Print Page Previous Next Advertisements ”;

SQL – Show Indexes Table of content The SQL Show Index Statement Showing Indexes in SQL Server ”; Previous Next The SQL Show Index Statement The SHOW INDEX is the basic SQL statement to retrieve the information about the indexes that have been defined on a table. However, the SHOW INDEX statement only works on MySQL RDBMS and is not a valid statement in the SQL Server. To list the indexes created on a table in SQL Server, a system stored procedure sp_helpindex is used. The result-set obtained from querying the SHOW INDEX statement on a MySQL table contains the index information. Syntax Following is the syntax of the SHOW INDEX statement in MySQL − SHOW INDEX FROM table_name; Example Following example demonstrates the working of SHOW INDEX statement in MySQL. First, create a table with the name CUSTOMERS in the MySQL database using the CREATE query below − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (20, 2), PRIMARY KEY (ID) ); Let us now insert some values into the above created table using the following query − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, ”32”, ”Ahmedabad”, 2000), (2, ”Khilan”, ”25”, ”Delhi”, 1500), (3, ”Kaushik”, ”23”, ”Kota”, 2000), (4, ”Chaitali”, ”25”, ”Mumbai”, 6500), (5, ”Hardik”,”27”, ”Bhopal”, 8500), (6, ”Komal”, ”22”, ”Hyderabad”, 9000), (7, ”Muffy”, ”24”, ”Indore”, 5500); Once the data is inserted, create an index for the column NAME in the CUSTOMERS table using the following query − CREATE INDEX INDEX_NAME ON CUSTOMERS(NAME); Now, you can list all the indexes that are defined on the CUSTOMERS table using the following query − SHOW INDEX FROM CUSTOMERS; Output On executing the above query, the output is displayed as follows − Table Non_unique Key_name Seq_in_index Column_name customers 0 PRIMARY 1 ID customers 1 index_name 1 NAME Showing Indexes in SQL Server In SQL server, the system stored procedure sp_helpindex is used to retrieve the information about the indexes that have been defined on a table. It returns the result as a table that contains detailed information about each index, including the name, type, and columns. Syntax Following is the basic syntax to list indexes defined on a table in SQL Server − sp_helpindex [ @objname = ] ”name” Here, [ @objname = ] ”name” specifies the name of the table for which the index information is being retrieved. The index information includes − index_name is the names of the columns that are included in index. index_description is the brief description of the index such as the type of index (like clustered or non-clustered). index_keys is the keys that are included in the index. Example CREATE INDEX INDEX_NAME on CUSTOMERS(NAME); Now, let us list all the indexes that are created on the CUSTOMERS table using the system stored procedure sp_helpindex as shown below − EXEC sys.sp_helpindex @objname = N”CUSTOMERS”; Output On executing the above query, the output is displayed as follows − index_name index_description index_keys INDEX_NAME nonclustered located on PRIMARY NAME PK__CUSTOMER__ 3214EC27755869D9 clustered, unique, primary key located on PRIMARY ID Print Page Previous Next Advertisements ”;

SQL – NOT NULL Constraint Table of content The SQL NOT NULL Constraint Creating NOT NULL Constraint On a Table Removing a NOT NULL Constraint From the Table Adding a NOT NULL Constraint to the Existing Table ”; Previous Next In a table, columns can typically accept NULL values by default. However, if you want to ensure that a particular column does not contain NULL values, you need to add the NOT NULL constraint/condition on that column. The SQL NOT NULL Constraint The NOT NULL constraint in SQL is used to ensure that a column in a table doesn”t contain NULL (empty) values, and prevent any attempts to insert or update rows with NULL values. Usually, if we don”t provide value to a particular column while inserting data into a table, by default it is considered as a NULL value. But, if we add the NOT NULL constraint on a column, it will enforce that a value must be provided for that column during the data insertion, and attempting to insert a NULL value will result in a constraint violation error. Syntax Following is the basic syntax of NOT NULL constraint while creating a table − CREATE TABLE table_name ( column1 datatype NOT NULL, column2 datatype, column3 datatype NOT NULL, … ); Creating NOT NULL Constraint On a Table To add the NOT NULL constraint on a column of a table, we just need to add the keyword “NOT NULL” after the column”s data type in the column definition. Example First of all, let us create a table named CUSTOMERS 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 (20, 2), PRIMARY KEY (ID) ); Let”s insert some values into the above created table using the following INSERT query − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, ”32”, ”Ahmedabad”, 2000), (2, ”Khilan”, ”25”, ”Delhi”, 1500), (3, ”Kaushik”, ”23”, ”Kota”, 2500), (4, ”Chaitali”, ”25”, ”Mumbai”, 6500), (5, ”Hardik”,”27”, ”Bhopal”, 8500), (6, ”Komal”, ”22”, ”Hyderabad”, 9000), (7, ”Muffy”, ”24”, ”Indore”, 5500); The table will be created 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 Verification To display the structure of a table in MySQL database, we use the DESCRIBE command. The DESCRIBE command provides a summary of the columns, data types, and various attributes of the table as shown below − DESCRIBE CUSTOMERS; As we can see in the output below, the table shows information about the column names of the table, their types, and whether they are nullable or not. Field Type Null Key Default Extra ID int NO PRI NULL NAME varchar(20) NO NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(20,2) YES NULL Removing a NOT NULL Constraint From the Table In SQL, to remove a NOT NULL constraint of a column in an existing table, we need to use the ALTER TABLE statement. Using this statement, we can modify the definition of a column i,e you can change the name, data type or constraint of an existing column. One of a way to remove the NOT NULL constraint on a column is to changing it to NULL. Syntax Following is the syntax to remove a not null constraint from the table in MySQL database − ALTER TABLE table_name MODIFY COLUMN column_name datatype NULL; Were, table_name is the name of the table that contains the columns we want to modify. column_name is the name of the column that has the NOT NULL constraint you want to remove. datatype is the data type of the column. Example Following is the query to modify the constraint on the NAME column of the CUSTOMERS table to NULL in MySQL database − ALTER TABLE CUSTOMERS MODIFY COLUMN NAME VARCHAR(20) NULL; Output On executing the above query, the output is displayed as follows − Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification Now, let us display the structure of the table named “CUSTOMERS” using the following query − DESCRIBE CUSTOMERS; As we can see in the table below, the column “NAME” is modified to nullable, which means NULL values are allowed in this column. Field Type Null Key Default Extra ID int NO PRI NULL NAME varchar(20) YES NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(20,2) YES NULL Adding a NOT NULL Constraint to the Existing Table In the previous section, we have removed the NOT NULL constraint on a column by changing its definition using the ALTER TABLE statement. Similarly, we can add a NOT NULL constraint to a column in an existing table using the ALTER TABLE statement. Syntax Following is the SQL syntax to add the NOT NULL constraint to the existing column in MySQL database − ALTER TABLE table_name MODIFY COLUMN column_name datatype NOT NULL; Example Assume the previously created table CUSTOMERS and let us modify the ADDRESS column ensuring that it does not allow null values using the following query − ALTER TABLE CUSTOMERS MODIFY COLUMN ADDRESS CHAR(25) NOT NULL; Output When we execute the above query, the output is obtained as follows − Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification We can display the structure of the CUSTOMERS table using the following query − DESCRIBE CUSTOMERS; As we can see in the output below, the column “ADDRESS” is modified, which means NULL values are NOT allowed in this column. Field Type Null Key Default Extra ID int NO PRI NULL NAME varchar(20) NO NULL AGE int NO NULL ADDRESS char(25) NO NULL SALARY decimal(20,2) YES NULL Print Page Previous Next Advertisements ”;

SQL – Syntax Table of content What is SQL Syntax? Case Sensitivity SQL Table SQL Statements ”; Previous Next What is SQL Syntax? SQL syntax is a unique set of rules and guidelines to be followed while writing SQL statements. This tutorial gives you a quick start with SQL by listing all the basic SQL Syntax. All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (;). Case Sensitivity The most important point to be noted here is that SQL is case insensitive, which means SELECT and Select have same meaning in SQL statements. Whereas, MySQL makes difference in table names. So, if you are working with MySQL, then you need to give table names as they exist in the database. SQL Table Let us consider a table with the name CUSTOMERS shown below, and use it as a reference to demonstrate all the SQL Statements on the same. 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 SQL Statements This tutorial lists down various SQL statements. Most of them are compatible with MySQL, Oracle, Postgres and SQL Server databases. All the SQL statements require a semicolon (;) at the end of each statement. Semicolon is the standard way to separate different SQL statements which allows to include multiple SQL statements in a single line. All the SQL Statements given in this tutorial have been tested with a MySQL server on Linux and Windows. SQL CREATE DATABASE Statement To store data within a database, you first need to create it. This is necessary to individualize the data belonging to an organization. You can create a database using the following syntax − CREATE DATABASE database_name; Let us try to create a sample database sampleDB in SQL using the CREATE DATABASE statement − CREATE DATABASE sampleDB SQL USE Statement Once the database is created, it needs to be used in order to start storing the data accordingly. Following is the syntax to change the current location to required database − USE database_name; We can set the previously created sampleDB as the default database by using the USE statement in SQL − USE sampleDB; SQL DROP DATABASE Statement If a database is no longer necessary, you can also delete it. To delete/drop a database, use the following syntax − DROP DATABASE database_name; You can also drop the sampleDB database by using the DROP DATABASE statement in SQL − DROP DATABASE sampleDB; SQL CREATE TABLE Statement In an SQL driven database, the data is stored in a structured manner, i.e. in the form of tables. To create a table, following syntax is used − CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ….. columnN datatype, PRIMARY KEY( one or more columns ) ); The following code block is an example, which creates a CUSTOMERS table given above, with an ID as a primary key and NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table − 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) ); SQL DESC Statement Every table in a database has a structure of its own. To display the structure of database tables, we use the DESC statements. Following is the syntax − DESC table_name; The DESC Statement, however, only works in few RDBMS systems; hence, let us see an example by using DESC statement in the MySQL server − DESC CUSTOMERS; SQL INSERT INTO Statement The SQL INSERT INTO Statement is used to insert data into database tables. Following is the syntax − INSERT INTO table_name( column1, column2….columnN) VALUES ( value1, value2….valueN); The following example statements would create seven records in the empty CUSTOMERS table. INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500), (3, ”kaushik”, 23, ”Kota”, 2000), (4, ”Chaitali”, 25, ”Mumbai”, 6500), (5, ”Hardik”, 27, ”Bhopal”, 8500), (6, ”Komal”, 22, ”Hyderabad”, 4500), (7, ”Muffy”, 24, ”Indore”, 10000); SQL SELECT Statement In order to retrieve the result-sets of the stored data from a database table, we use the SELECT statement. Following is the syntax − SELECT column1, column2….columnN FROM table_name; To retrieve the data from CUSTOMERS table, we use the SELECT statement as shown below. SELECT * FROM CUSTOMERS; SQL UPDATE Statement When the stored data in a database table is outdated and needs to be updated without having to delete the table, we use the UPDATE statement. Following is the syntax − UPDATE table_name SET column1 = value1, column2 = value2….columnN=valueN [ WHERE CONDITION ]; To see an example, the following query will update the ADDRESS for a customer whose ID number is 6 in the table. UPDATE CUSTOMERS SET ADDRESS = ”Pune” WHERE ID = 6; SQL DELETE Statement Without deleting the entire table from the database, you can also delete a certain part of the data by applying conditions. This is done using the DELETE FROM statement. Following is the syntax − DELETE FROM table_name WHERE {CONDITION}; The following code has a query, which will DELETE a customer, whose ID is 6. DELETE FROM CUSTOMERS WHERE ID = 6; SQL DROP TABLE Statement To delete a table entirely from a database when it is no longer needed, following syntax is used − DROP TABLE table_name; This query will drop the CUSTOMERS table from the database. DROP TABLE CUSTOMERS; SQL TRUNCATE TABLE Statement The TRUNCATE TABLE statement is implemented in SQL to delete the data of the table but not the table itself. When this SQL statement is used, the table stays in the database like an empty table. Following is the syntax − TRUNCATE TABLE table_name; Following query delete

UNION vs JOIN Table of content Working of UNION Working of JOIN UNION Vs JOIN ”; Previous Next SQL provides various relational operators to handle data that is spread across multiple tables in a relational database. Out of them, UNION and JOIN queries are fundamentally used to combine data from multiple tables. Even though they are both used for the same purpose, i.e. to combine tables, there are many differences between the working of these operators. The major difference is that the UNION operator combines data from multiple similar tables irrespective of the data relativity, whereas, the JOIN operator is only used to combine relative data from multiple tables. Working of UNION UNION is a type of operator/clause in SQL, that works similar to the union operator in relational algebra. It does nothing more than just combining information from multiple tables that are union compatible. The tables are said to be union compatible if they follow the conditions given below − The tables to be combined must have same number of columns with the same datatype. The number of rows need not be same. Once these criteria are met, UNION operator returns all the rows from multiple tables, after eliminating duplicate rows, as a resultant table. Note − Column names of first table will become column names of resultant table, and contents of second table will be merged into resultant columns of same data type. Syntax Following is the syntax of the SQL UNION operator − SELECT * FROM table1 UNION SELECT * FROM table2; Example Let us first create two table “COURSES_PICKED” and “EXTRA_COURSES_PICKED” with the same number of columns having same data types. Create table COURSES_PICKED using the following query − CREATE TABLE COURSES_PICKED( STUDENT_ID INT NOT NULL, STUDENT_NAME VARCHAR(30) NOT NULL, COURSE_NAME VARCHAR(30) NOT NULL ); Insert values into the COURSES_PICKED table with the help of the query given below − INSERT INTO COURSES_PICKED VALUES (1, ”JOHN”, ”ENGLISH”), (2, ”ROBERT”, ”COMPUTER SCIENCE”), (3, ”SASHA”, ”COMMUNICATIONS”), (4, ”JULIAN”, ”MATHEMATICS”); Create table EXTRA_COURSES_PICKED using the following query − CREATE TABLE EXTRA_COURSES_PICKED( STUDENT_ID INT NOT NULL, STUDENT_NAME VARCHAR(30) NOT NULL, EXTRA_COURSE_NAME VARCHAR(30) NOT NULL ); Following is the query to insert values into the EXTRA_COURSES_PICKED table − INSERT INTO EXTRA_COURSES_PICKED VALUES (1, ”JOHN”, ”PHYSICAL EDUCATION”), (2, ”ROBERT”, ”GYM”), (3, ”SASHA”, ”FILM”), (4, ”JULIAN”, ”PHOTOGRAPHY”); Now, let us combine the tables COURSES_PICKED and EXTRA_COURSES_PICKED, using the UNION query as follows − SELECT * FROM COURSES_PICKED UNION SELECT * FROM EXTRA_COURSES_PICKED; Output The resultant table obtained after performing the UNION operation is − STUDENT_ID STUDENT_NAME COURSE_NAME 1 Jhon English 1 Jhon Physical Education 2 Robert Computer Science 2 Robert Gym 3 Shasha Communications 3 Shasha Film 4 Julian Mathematics 4 Julian Photography Working of JOIN The Join operation is used to combine information from multiple related tables into one, based on their common fields. This operation can be used with various clauses like ON, WHERE, ORDER BY, GROUP BY etc. There are two types of Joins − Inner Join Outer Join The basic type of join is an Inner Join, which only retrieves the matching values of common columns. It is a default join. The result table of the Outer join includes both matched and unmatched rows from the first table. It is divided into subtypes like Left Join, Right Join, and Full Join. Syntax Following is the basic syntax of a Join operation in SQL − SELECT column_name(s) FROM table1 JOIN table2 ON table1.column_name = table2.column_name; Example In the following example, we will join the same tables we created above, i.e., COURSES_PICKED and EXTRA_COURSES_PICKED, using the query below – SELECT c.STUDENT_ID, c.STUDENT_NAME, COURSE_NAME, COURSES_PICKED FROM COURSES_PICKED c JOIN EXTRA_COURSES_PICKED e ON c.STUDENT_ID = e.STUDENT_ID; Output The resultant table will be displayed as follows − STUDENT_ID STUDENT_NAME COURSE_NAME COURSE_PICKED 1 Jhon ENGLISH Physical Education 2 Robert COMPUTER SCIENCE Gym 3 Shasha COMMUNICATIONS Film 4 Julian MATHEMATICS Photography UNION Vs JOIN As we saw in the examples given above, the UNION operator is only executable on tables that are union compatible, whereas, the JOIN operator joins two tables that need not be compatible but should be related. Let us summarize all the difference between these queries below − UNION JOIN UNION operation is only performed on tables that are union compatible, i.e., the tables must contain same number of columns with same data type. JOIN operation can be performed on tables that has at least one common field between them. The tables need not be union compatible. The data combined will be added as new rows of the resultant table. The data combined will be adjoined into the resultant table as new columns. This works as the conjunction operation. This works as an intersection operation. UNION removes all the duplicate values from the resultant tables. JOIN retains all the values from both tables even if they”re redundant. UNION does not need any additional clause to combine two tables. JOIN needs an additional clause ON to combine two tables based on a common field. It is mostly used in scenarios like, merging the old employees list in an organization with the new employees list. This is used in scenarios where merging related tables is necessary. For example, combining tables containing customers list and the orders they made. Print Page Previous Next Advertisements ”;

SQL – DROP or DELETE View Table of content The DROP VIEW Statement The IF EXISTS clause Deleting Rows from a View ”; Previous Next SQL allows you to drop an exiting view and delete records from a view in a database. SQL uses DROP statement to delete all the records from the view along with its definition and using the DELETE statement, only the records are deleted while the view definition of the view remains unchanged. And note that if a record is deleted from a view, it is also deleted from its corresponding base table. The DROP VIEW Statement The SQL DROP VIEW statement is used to delete an existing view, along with its definition and other information. Once the view is dropped, all the permissions for it will also be removed. We can also drop indexed views with this statement. Suppose a table is dropped using the DROP TABLE command and it has a view associated to it, this view must also be dropped explicitly using the DROP VIEW command. While trying to perform queries, the database engine checks all the objects referenced in that statement are valid and exist. So, if a view does not exist in the database, the DROP VIEW statement will throw an error. To drop a table in a database, one must require ALTER permission on the said table and CONTROL permissions on the table schema. Syntax The basic syntax of this DROP VIEW statement is as follows − DROP VIEW view_name; Example Assume we have created a table named CUSTOMERS using the CREATE TABLE statement 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 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 ); Assume we have created 3 views using the CREATE VIEW statement as shown below − CREATE VIEW CUSTOMERS_VIEW1 AS SELECT * FROM CUSTOMERS; CREATE VIEW CUSTOMERS_VIEW2 AS SELECT * FROM CUSTOMERS; CREATE VIEW CUSTOMERS_VIEW3 AS SELECT * FROM CUSTOMERS; You can verify the list of all the views using the following query − SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA=”tutorials”; This will display the list of views as follows − TABLE_SCHEMA TABLE_NAME tutorials CUSTOMERS_VIEW1 tutorials CUSTOMERS_VIEW2 tutorials CUSTOMERS_VIEW3 Now, lets drop two views from the above created views using the DROP VIEW statement. DROP VIEW CUSTOMERS_VIEW1; DROP VIEW CUSTOMERS_VIEW2; Verification Once we have deleted all the views if you try to retrieve the list of views you will get an empty set as shown below − SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA=”tutorials”; The remaining list of views is as follows − TABLE_SCHEMA TABLE_NAME tutorials CUSTOMERS_VIEW3 The IF EXISTS clause While deleting an existing view, you can use the IF EXISTS clause in the DROP VIEW statement. This clause, when specified in the DROP VIEW query, will automatically check whether the view exists in the current database and then drops it, if yes. If the view does not exist in the database, the query will be ignored. Syntax Following is the basic syntax of DROP VIEW IF EXISTS − DROP VIEW [IF EXISTS] view_name; Example If you try to drop a view that doesn”t exist in the database, without using the IF EXISTS clause, as shown below − DROP VIEW DEMO_VIEW; An error will be generated − ERROR 1051 (42S02): Unknown table ”tutorials.demo_view” But if you use the IF EXISTS clause along with the DROP VIEW statement as shown below, the specified event will be dropped; and if a view with the given name doesn”t exist the query will be ignored. DROP VIEW IF EXISTS DEMO_VIEW; The query will be ignored with the following output displayed − Query OK, 0 rows affected, 1 warning (0.04 sec) Deleting Rows from a View Instead of removing an entire view, we can also delete selected rows of a view using the DELETE statement. Syntax Following is the syntax of the DELETE statement − DELETE FROM view_name WHERE condition; Example Following query deletes a record from the third_view created on the CUSTOMERS table created above. The changes made to the data in view will finally be reflected in the base table CUSTOMERS. DELETE FROM CUSTOMERS_VIEW3 WHERE AGE = 22; This would ultimately delete a row from the base table CUSTOMERS and the same would reflect in the view itself. Now, query the base table and the SELECT statement would produce the following result − SELECT * FROM CUSTOMERS; The CUSTOMERS table is displayed as − ID NAME AGE ADDRESS SALARY 1 Ramesh 35 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 7 Muffy 24 Indore 10000.00 Print Page Previous Next Advertisements ”;

SQL – Drop Index Table of content Dropping an SQL Index DROP INDEX with IF EXISTS Removing indexes created by PRIMARY KEY or UNIQUE ”; Previous Next The DROP statement in SQL is used to remove or delete an existing database object such as a table, index, view, or procedure. Whenever we use DROP statement with any of the database objects, it will remove them permanently along with their associated data. And when that database object is an index, the DROP INDEX statement in SQL is used. Dropping an SQL Index An SQL Index can be dropped from a database table using the DROP INDEX statement. It is important to understand that dropping an index can have a significant impact on the performance of your database queries. Therefore, only try to remove an index if you are sure that it is no longer required. Note − We cannot delete the indexes created by PRIMARY KEY or UNIQUE constraints. In order to delete them, you need to drop the constraints entirely using ALTER TABLE statement. Syntax Following is the syntax of the DROP INDEX command in SQL − DROP INDEX index_name ON table_name; Here, index_name is the name of the index that you want to drop. table_name is the name of the table that the index is associated with. Example In this example, we will learn how to drop an index on a table named CUSTOMERS, which can be created using the following query − CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR(15) NOT NULL, AGE INT NOT NULL, ADDRESS VARCHAR(25), SALARY DECIMAL(10, 4), PRIMARY KEY(ID)); ); Now, insert some values into the above created table using the following query − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, ”32”, ”Ahmedabad”, 2000), (2, ”Khilan”, ”25”, ”Delhi”, 1500), (3, ”Kaushik”, ”23”, ”Kota”, 2000), (4, ”Chaitali”, ”25”, ”Mumbai”, 6500), (5, ”Hardik”,”27”, ”Bhopal”, 8500), (6, ”Komal”, ”22”, ”Hyderabad”, 9000), (7, ”Muffy”, ”24”, ”Indore”, 5500); Once the table is created, create an index on the column NAME in the CUSTOMERS table using the following query − CREATE INDEX INDEX_NAME on CUSTOMERS(NAME); Now, verify if the index is created on the CUSTOMERS table using the following SHOW INDEX query − SHOW INDEX FROM CUSTOMERS; On executing the above query, the index list is displayed as follows − Table Non_unique Key_name Seq_in_index Column_name customers 0 PRIMARY 1 ID customers 1 index_name 1 NAME Then, drop the same index INDEX_NAME in the CUSTOMERS table using the following DROP INDEX statement − DROP INDEX INDEX_NAME ON CUSTOMERS; Output If we compile and run the above query, the result is produced as follows − Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification Verify if the index for the column NAME is dropped using the following query − SHOW INDEX FROM CUSTOMERS; In the following list of indexes, you can observe that name of the column Name is missing. Table Non_unique Key_name Seq_in_index Column_name customers 0 PRIMARY 1 ID DROP INDEX with IF EXISTS The DROP INDEX IF EXISTS statement in SQL is used to drop an index only if it exists in the table. This statement is specifically useful when you want to drop an index, but you are not sure if the index exists. This clause is not supported by MySQL. The IF EXISTS clause ensures that the statement only removes the index if it exists. If the index does not exist, it simply terminates the execution. Syntax Following is the syntax of the DROP INDEX IF EXISTS in SQL − DROP INDEX IF EXISTS index_name ON table_name; Here, index_name is the name of the index that you want to drop. table_name is the name of the table that the index is associated with. Example In this example, let us try to drop an index in the SQL Server database. Let us consider the previously created table CUSTOMERS and let us create an index for the NAME column in the table using the following query − CREATE INDEX INDEX_NAME on CUSTOMERS(NAME); Then, let us drop it using the following query − DROP INDEX IF EXISTS INDEX_NAME ON CUSTOMERS; Output When we execute the above query, the output is obtained as follows − Commands completed successfully. Verification Let”s verify whether the index for the NAME is dropped or not using the following query − EXEC sys.sp_helpindex @objname = N”CUSTOMERS”; As you observe, the column NAME is deleted from the list of indexes. index_name index_description index_keys PK__CUSTOMER__3214EC27CB063BB7 clustered, unique, primary key locatedPRIMARY on PRIMARY ID Example Now, let us delete an index that doesn”t exist in the CUSTOMERS table using the following query − DROP INDEX IF EXISTS INDEX_NAME ON CUSTOMERS; Output Since no indexes with the specified name exist in the database, so the above query simply terminates the execution without giving any error. Commands completed successfully. Removing indexes created by PRIMARY KEY or UNIQUE The DROP INDEX statement does not drop indexes created by PRIMARY KEY or UNIQUE constraints. To drop indexes associated with them, we need to drop these constraints entirely. And it is done using the ALTER TABLE… DROP CONSTRAINT statement. Syntax Following is the syntax of the ALTER TABLE… DROP CONSTRAINT statement in SQL − ALTER TABLE table_name DROP CONSTRAINT constraint_name; Here, table_name is the name of the table that contains the PRIMARY KEY constraint. constraint_name is the name of the PRIMARY KEY constraint that you want to drop. Example Assume the previously created table (CUSTOMERS) and let us first list all the indexes that are created on the table using the following query − EXEC sys.sp_helpindex @objname = N”CUSTOMERS”; The list is displayed as follows − index_name index_description index_keys PK__CUSTOMER__3214EC27CB063BB7 nonclustered located on PRIMARYID ID Here, the PK__CUSTOMER__3214EC27CB063BB7 is the name of the PRIMARY KEY constraint that was created on the ID column of the CUSTOMERS table. Now, let us drop the index created by the PRIMARY KEY constraint. ALTER TABLE customers DROP CONSTRAINT PK__CUSTOMER__3214EC27CB063BB7; Output When we execute the above query, the output is obtained