Category: sql

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 – Self Join Table of content The SQL Self Join Self Join with ORDER BY Clause ”; Previous Next Self Join, as its name suggests, is a type of join that combines the records of a table with itself. Suppose an organization, while organizing a Christmas party, is choosing a Secret Santa among its employees based on some colors. It is designed to be done by assigning one color to each of its employees and having them pick a color from the pool of various colors. In the end, they will become the Secret Santa of an employee this color is assigned to. As we can see in the figure below, the information regarding the colors assigned and a color each employee picked is entered into a table. The table is joined to itself using self join over the color columns to match employees with their Secret Santa. The SQL Self Join The SQL Self Join is used to join a table to itself as if the table were two tables. To carry this out, alias of the tables should be used at least once. Self Join is a type of inner join, which is performed in cases where the comparison between two columns of a same table is required; probably to establish a relationship between them. In other words, a table is joined with itself when it contains both Foreign Key and Primary Key in it. Unlike queries of other joins, we use WHERE clause to specify the condition for the table to combine with itself; instead of the ON clause. Syntax Following is the basic syntax of SQL Self Join − SELECT column_name(s) FROM table1 a, table1 b WHERE a.common_field = b.common_field; Here, the WHERE clause could be any given expression based on your requirement. Example Self Join only requires one table, so, let us create a CUSTOMERS table containing the customer details like their names, age, address and the salary they earn. 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 Now, let us join this table using the following Self Join query. Our aim is to establish a relationship among the said Customers on the basis of their earnings. We are doing this with the help of the WHERE clause. SELECT a.ID, b.NAME as EARNS_HIGHER, a.NAME as EARNS_LESS, a.SALARY as LOWER_SALARY FROM CUSTOMERS a, CUSTOMERS b WHERE a.SALARY < b.SALARY; Output The resultant table displayed will list out all the customers that earn lesser than other customers − ID EARNS_HIGHER EARNS_LESS LOWER_SALARY 2 Ramesh Khilan 1500.00 2 Kaushik Khilan 1500.00 6 Chaitali Komal 4500.00 3 Chaitali Kaushik 2000.00 2 Chaitali Khilan 1500.00 1 Chaitali Ramesh 2000.00 6 Hardik Komal 4500.00 4 Hardik Chaitali 6500.00 3 Hardik Kaushik 2000.00 2 Hardik Khilan 1500.00 1 Hardik Ramesh 2000.00 3 Komal Kaushik 2000.00 2 Komal Khilan 1500.00 1 Komal Ramesh 2000.00 6 Muffy Komal 4500.00 5 Muffy Hardik 8500.00 4 Muffy Chaitali 6500.00 3 Muffy Kaushik 2000.00 2 Muffy Khilan 1500.00 1 Muffy Ramesh 2000.00 Self Join with ORDER BY Clause After joining a table with itself using self join, the records in the combined table can also be sorted in an order, using the ORDER BY clause. Syntax Following is the syntax for it − SELECT column_name(s) FROM table1 a, table1 b WHERE a.common_field = b.common_field ORDER BY column_name; Example Let us join the CUSTOMERS table with itself using self join on a WHERE clause; then, arrange the records in an ascending order using the ORDER BY clause with respect to a specified column, as shown in the following query. SELECT a.ID, b.NAME as EARNS_HIGHER, a.NAME as EARNS_LESS, a.SALARY as LOWER_SALARY FROM CUSTOMERS a, CUSTOMERS b WHERE a.SALARY < b.SALARY ORDER BY a.SALARY; Output The resultant table is displayed as follows − ID EARNS_HIGHER EARNS_LESS LOWER_SALARY 2 Ramesh Khilan 1500.00 2 Kaushik Khilan 1500.00 2 Chaitali Khilan 1500.00 2 Hardik Khilan 1500.00 2 Komal Khilan 1500.00 2 Muffy Khilan 1500.00 3 Chaitali Kaushik 2000.00 1 Chaitali Ramesh 2000.00 3 Hardik Kaushik 2000.00 1 Hardik Ramesh 2000.00 3 Komal Kaushik 2000.00 1 Komal Ramesh 2000.00 3 Muffy Kaushik 2000.00 1 Muffy Ramesh 2000.00 6 Chaitali Komal 4500.00 6 Hardik Komal 4500.00 6 Muffy Komal 4500.00 4 Hardik Chaitali 6500.00 4 Muffy Chaitali 6500.00 5 Muffy Hardik 8500.00 Not just the salary column, the records can be sorted based on the alphabetical order of names, numerical order of Customer IDs etc. Print Page Previous Next Advertisements ”;

SQL – DISTINCT Keyword Table of content The SQL DISTINCT Keyword DISTINCT Keyword on Single Columns DISTINCT Keyword on Multiple Columns DISTINCT Keyword with COUNT() Function DISTINCT Keyword with NULL Values ”; Previous Next The SQL DISTINCT Keyword The SQL DISTINCT keyword is used in conjunction with the SELECT statement to fetch unique records from a table. We use DISTINCT keyword with the SELECT statetment when there is a need to avoid duplicate values present in any specific columns/tables. When we use DISTINCT keyword, SELECT statement returns only the unique records available in the table. The SQL DISTINCT Keyword can be associated with SELECT statement to fetch unique records from single or multiple columns/tables. Syntax The basic syntax of SQL DISTINCT keyword is as follows − SELECT DISTINCT column1, column2,…..columnN FROM table_name; Where, column1, column2, etc. are the columns we want to retrieve the unique or distinct values from; and table_name represents the name of the table containing the data. DISTINCT Keyword on Single Columns We can use the DISTINCT keyword on a single column to retrieve all unique values in that column, i.e. with duplicates removed. This is often used to get a summary of the distinct values in a particular column or to eliminate redundant data. Example Assume we have created a table with name CUSTOMERS in 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 First, let us retrieve the SALARY values from the CUSTOMERS table using the SELECT query − SELECT SALARY FROM CUSTOMERS ORDER BY SALARY; This would produce the following result. Here, you can observe that the salary value 2000 is appearing twice − SALARY 1500.00 2000.00 2000.00 4500.00 6500.00 8500.00 10000.00 Now, let us use the DISTINCT keyword with the above SELECT query and then see the result − SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY; Output This would produce the following result where we do not have any duplicate entry − SALARY 1500.00 2000.00 4500.00 6500.00 8500.00 10000.00 DISTINCT Keyword on Multiple Columns We can also use the DISTINCT keyword on multiple columns to retrieve all unique combinations of values across those columns. This is often used to get a summary of distinct values in multiple columns, or to eliminate redundant data. Example In the following query, we are retrieving a list of all unique combinations of customer”s age and salary using the DISTINCT keyword − SELECT DISTINCT AGE, SALARY FROM CUSTOMERS ORDER BY AGE; Output Though the AGE column have the value “25” in two records, each combination of “25” with it”s specific ”salary” is unique, so both rows are included in the result set − AGE SALARY 22 4500.00 23 2000.00 24 10000.00 25 1500.00 25 6500.00 27 8500.00 32 2000.00 DISTINCT Keyword with COUNT() Function The COUNT() function is used to get the number of records retuned by the SELECT query. We need to pass an expression to this function so that the SELECT query returns the number of records that satisfy the specified expression. If we pass the DISTINCT keyword to the COUNT() function as an expression, it returns the number of unique values in a column of a table. Syntax Following is the syntax for using the DISTINCT keyword with COUNT() function − SELECT COUNT(DISTINCT column_name) FROM table_name WHERE condition; Where, column_name is the name of the column for which we want to count the unique values; and table_name is the name of the table that contains the data. Example In the following query, we are retrieving the count of distinct age of the customers − SELECT COUNT(DISTINCT AGE) as UniqueAge FROM CUSTOMERS; Output Following is the result produced − UniqueAge 6 DISTINCT Keyword with NULL Values In SQL, when there are NULL values in the column, DISTINCT treats them as unique values and includes them in the result set. Example First of all let us update two records of the CUSTOMERS table and modify their salary values to NULL UPDATE CUSTOMERS SET SALARY = NULL WHERE ID IN(6,4); The resultant CUSTOMERS table would 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 NULL 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad NULL 7 Muffy 24 Indore 10000.00 Now, we are retrieving the distinct salary of the customers using the following query − SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY; Output Following is the output of the above query − SALARY NULL 1500.00 2000.00 8500.00 10000.00 Print Page Previous Next Advertisements ”;

Left Join vs Right Join Table of content Working of Left Join Working of Right Join Left Join Vs Right Join ”; Previous Next The main difference between the Left Join and Right Join can be observed in the way tables are joined. They are both types of Outer Joins; that is, they retain unmatched rows in one table and discard the unmatched rows of another. Left Join preserves the unmatched rows of left table while Right join preserves the unmatched rows of right table. Working of Left Join Left Join or Left Outer Join in SQL combines two or more tables, where the first table is returned as it is; but, only the record(s) that have counterparts in first table are returned from consequent tables. If the ON clause matches zero records in consequent tables with the rows in first table, left join will still return these rows of first table in the result, but with NULL in each column from the right table. Syntax Following is the basic syntax of Left Join in SQL − SELECT table1.column1, table2.column2… FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; Example The example below demonstrates the Left Join operation on two relative tables. Here, the first table contains the salary information while the second table contains marital status information. Since Alex”s status is unknown, it is not recorded in the table. When both tables are joined using the Left Join query, since there is no record matching Alex”s Status, the value is recorded as NULL in the final table. Working of Right Join Right Join or Right Outer Join query in SQL returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in left table with the records in right table; right join will still return the rows of right table in the result, but with a NULL value in each column of the left table. Syntax Following is the basic syntax of a Right Join in SQL − SELECT table1.column1, table2.column2… FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name; Example Now in this example, the Right Join operation is performed on the same tables. Here, we are starting the join from the right table; since, the right table does not contain the record value matching Alex”s row, the row is discarded from the final table. The final table only consists of two rows as the right table consists of two rows only. Left Join Vs Right Join Let us summarize all the differences between the Left Join and Right Join in the table below − Left Join Right Join Left Join matches the data of the first table or the left table with the data in second table. If the data is matched, the records are combined; otherwise, NULL is recorded. Right Join matches the data of the second table or right table with the data in first table. If the data is matched, the records are combined; otherwise, NULL is recorded. If the first table has less rows than the second table, extra unmatched rows from the second table are discarded. If the second table has less rows than the first table, extra unmatched rows from the first table are discarded. This Join is also known as Left Outer Join This Join is also known as Right Outer Join *= is used in Transact SQL, instead of using the LEFT JOIN or LEFT OUTER JOIN query. =* is used in Transact SQL, instead of using the RIGHT JOIN or RIGHT OUTER JOIN query. As we can observe from the summary, there aren”t wide range of differences between the Left and Right joins. Every difference between them zeroes down to the way the tables are joined and the join point of view. Print Page Previous Next Advertisements ”;

SQL – Foreign Key Table of content The SQL Foreign Key Foreign Key Constraint on an Existing Column Dropping a FOREIGN KEY Primary Key vs Foreign Key ”; Previous Next The SQL Foreign Key In SQL, a Foreign Key is a column in one table that matches a Primary Key in another table, allowing the two tables to be connected together. A foreign key also maintains referential integrity between two tables, making it impossible to drop the table containing the primary key (preserving the connection between the tables). The foreign key can reference the unique fields of any table in the database. The table that has the primary key is known as the parent table and the key with the foreign key is known as the child table. Let”s consider an example scenario, assume we have two tables namely CUSTOMERS (ID, NAME, AGE, ADDRES, SALARY) and ORDERS (ID, DATE, CUSTOMER_ID, AMOUNT). Here the id of the customer is primary key (ID) in the CUSTOMERS table and foreign key in the ORDERS (CUSTOMER_ID) table observe the following diagram − Features of Foreign Key Following is the of features of Foreign Key − A Foreign Key is used to reduce the redundancy (or duplicates) in the table. It helps to normalize (or organize the data in a database) the data in multiple tables. Syntax Following is the basic syntax to add Foreign Key constraints on a column of a table in MySQL database − CREATE TABLE table_name ( column1 datatype, column2 datatype, … CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES referenced_table(referenced_column) ); Example Let us create two tables with the names CUSTOMERS and ORDERS. The following query creates a table with the name CUSTOMERS − 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) ); Output Following is the output of the above SQL statement − Query OK, 0 rows affected (0.02 sec) Now, let us create the ORDERS table. While doing so, we add the foreign key constraint on column CUSTOMER_ID reference on column ID of the CUSTOMERS table as shown in the statement below − CREATE TABLE ORDERS ( ID INT NOT NULL, DATE DATETIME, CUSTOMER_ID INT, CONSTRAINT FK_CUSTOMER FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMERS(ID), AMOUNT DECIMAL, PRIMARY KEY (ID) ); Output The above statement produces the following output − Query OK, 0 rows affected (0.04 sec) Verification We have created a Foreign Key Constraint on a column named CUSTOMER_ID in the ORDERS table that references the column named ID of the CUSTOMERS table; so you can”t drop table1 (CUSTOMERS) before dropping the table2 (ORDERS). First of all, let”s drop the CUSTOMERS table without dropping the ORDERS table using the DROP TABLE statement − DROP TABLE CUSTOMERS; If you verify the error message below, you will observe that it says that the table can not be dropped because it is referenced by a FOREIGN KEY constraint − ERROR 3730 (HY000): Cannot drop table ”customers” referenced by a foreign key constraint ”FK_CUSTOMER” on table ”orders”. Foreign Key Constraint on an Existing Column We can also create a Foreign key constraint on a column of an existing table. This is useful when you forget to add a Foreign Key constraint on a column while creating a table, or when you want to add this constraint on another column even if one Foreign Key column exists in a table. Syntax Using the ALTER TABLE statement we can add a Foreign Key constraint on an existing column in a table in MySQL database as shown below − ALTER TABLE TABLE2 ADD CONSTRAINT[symbol] FOREIGN KEY(column_name) REFERENCES TABLE1(column_name); Here, FK_ORDERS is the name of the foreign key constraint. It is optional to specify the name of a constraint but it comes in handy while dropping the constraint. Example Assume the CUSTOMERS and ORDERS tables have already been created in the SQL database. Now, we will add a Foreign Key Constraint on the ID column of the ORDERS table. Following is the SQL query to add the foreign key constraint on an the column of an existing table − ALTER TABLE ORDERS ADD CONSTRAINT FK_ORDERS FOREIGN KEY(ID) REFERENCES CUSTOMERS(ID); Output Following is the output of the above program − Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification We have created a Foreign Key Constraint on a column named CUSTOMER_ID in the ORDERS table that references the column name ID of the CUSTOMERS table. So, you can”t drop table1 (CUSTOMERS) before dropping the table2 (ORDERS). First of all, let us drop the CUSTOMERS table without dropping the ORDERS table by executing the following statement − DROP TABLE CUSTOMERS; This generates an error message saying that the table can not be dropped because it is referenced by a FOREIGN KEY constraint − ERROR 3730 (HY000): Cannot drop table ”customers” referenced by a foreign key constraint ”FK_CUSTOMER” on table ”orders”. Dropping a FOREIGN KEY You can drop the foreign key from a table, without dropping that entire table, using the ALTER TABLE statement. Syntax Following is the syntax to drop the FOREIGN key constraint from the column of the table using the ALTER TABLE statement− ALTER TABLE table_name DROP FOREIGN KEY (constraint symbol); Where, FK_NAME is the name of the foreign key constraint you need to drop. Example The SQL query to drop the

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