Category: sql

SQL – Auto Increment Table of content Auto Increment in MySQL Auto Increment in SQL Server ”; Previous Next The SQL Auto Increment is used to automatically add unique sequential values into a column of a table. We usually define the Auto Increment on a column while creating a table. And when we insert new records into the table, the unique values are added to them. When we use Auto Increment on a table column, there is no need to insert NOT NULL values to that column. If we provide such values, they will overwrite the unique identities and the increment will be continued (only) on the NULL values (if any); causing ambiguity on the data. Different RDBMS support the Auto Increment feature in different ways. Auto Increment in MySQL In MySQL, you can add the auto-increment feature to a column of a table using the attribute named AUTO_INCREMENT. By default, when we define the AUTO_INCREMENT attribute on a column, the unique values are generated from “1”; and for each new record we enter into the table, the values in the column will increment by 1. Thus, the first record inserted will have a value of 1, the second record will have a value of 2, and so on. Syntax Following is the syntax to add AUTO_INCREMENT attribute to a column of a table in MySQL − CREATE TABLE table_name( column1 datatype AUTO_INCREMENT, column2 datatype, column3 datatype, ….. columnN datatype ); Example In the query to we are creating a table named CUSTOMERS and adding the AUTO_INCREMENT to the column named ID − CREATE TABLE CUSTOMERS( ID INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2) ); Now, let us insert values into the CUSTOMERS table using the INSERT statement − INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES (“Ramesh”, 32, “Ahmedabad”, 2000.00), (“Khilan”, 25, “Delhi”, 1500.00), (“Kaushik”, 23, “Kota”, 2000.00), (“Chaitali”, 25, “Mumbai”, 6500.00); Verification To verify this, you need to retrieve the contents of the CUSTOMERS using the SELECT query as − SELECT * FROM CUSTOMERS; Output Following is the output of the above query, here you can observe that the ID values are generated automatically − 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 AUTO_INCREMENT on Existing Columns MySQL also allows you to implement the AUTO_INCREMENT attribute on an existing table, using the ALTER TABLE statement. Following query starts incrementing the ID values from 5 in the CUSTOMERS table CUSTOMERS − ALTER TABLE CUSTOMERS AUTO_INCREMENT = 100; Now, let us insert more records to see if the ID values are auto incremented. INSERT INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES (“Hardik”, 27, “Bhopal”, 8500.00), (“Komal”, 22, “MP”, 4500.00), (“Muffy”, 24, “Indore”, 10000.00); To view the above table data, we use the following SELECT query − SELECT * FROM CUSTOMERS; Output The output of the above query is shown below. It shows the auto increment in action. We are getting the ID values of the newly inserted records begins at 100. 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 100 Hardik 27 Bhopal 8500.00 101 Komal 22 Hyderabad 4500.00 102 Muffy 24 Indore 10000.00 Auto Increment in SQL Server In SQL Server, there is no direct command/query to perform Auto Increment. Instead, we use the IDENTITY() property. This property works similar to the AUTO_INCREMENT attribute in MySQL. It generates unique, sequential numbers automatically and it is mostly used on the PRIMARY KEY constraint. Syntax Following is the basic syntax of IDENTITY() property in SQL Server − CREATE TABLE table_name ( column1 datatype IDENTITY [(seed, increment)], column2 datatype, column3 datatype, ….. columnN datatype ); This property accepts two parameters. The same are described below: seed: It sets the starting value for the auto-incrementing column. increment: It specifies how much the value increases by for each new row. Example In the following example, we are using the IDENTITY() property on the ID column of table named CUSTOMERS − CREATE TABLE CUSTOMERS( ID INT PRIMARY KEY IDENTITY(1,1), NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2) ); After creating the table, we are inserting some records using the following query − INSERT INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES (”Ramesh”, 32, ”Ahmedabad”, 2000.00), (”Khilan”, 25, ”Delhi”, 1500.00), (”Kaushik”, 23, ”Kota”, 2000.00), (”Chaitali”, 25, ”Mumbai”, 6500.00); To view the table data, we use the following SELECT query − SELECT * FROM CUSTOMERS; Output Following is an output of the above query, where ID values are generated automatically − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik

SQL – Database Tuning Table of content SQL Database Tuning Database Tuning Techniques Built-In Tuning Tools ”; Previous Next SQL Database Tuning Database Tuning in SQL is a set of activities performed to optimize a database and prevents it from becoming a bottleneck. There are various techniques with which you can configure the optimal performance of a particular database. Database tuning overlaps with query tuning; so, good indexing and avoiding improper queries help in increasing the database efficiency. In addition, increasing storage, updating to latest database versions and investing in a more powerful CPU (if needed) are also some of the general techniques. Database Tuning Techniques We can implement the following techniques to optimize the performance of a database − Database Normalization Normalization is the process of removing of duplicate data from a database. We can normalize a database by breaking down larger tables into smaller related tables. This increases the performance of database as it requires less time to retrieve data from small tables instead of one large table. Proper Indexes In SQL, indexes are the pointers (memory address) to the location of specific data in database. We use indexes in our database to reduce query time, as the database engine can jump to the location of a specific record using its index instead of scanning the entire database. Avoid Improper Queries Choosing the correct query to retrieve data efficiently also improves the performance of a database. For example, choosing to retrieve an entire table when we only need the data in a single column will unnecessarily increase query time. So, query the database wisely. Let us discuss some of the common improper queries made and how to rectify them to optimize the database performance. 1. Use SELECT fields instead of SELECT (*) In large databases, we should always retrieve only the required columns from the database instead of retrieving all the columns, even when they are not needed. We can easily do this by specifying the column names in the SELECT statement instead of using the SELECT (*) statement. 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 ); Let us say we only want the data in ID, NAME and SALARY columns of the CUSTOMERS table. So, we should only specify those three columns in our SELECT statement as shown below − SELECT ID, NAME, SALARY FROM CUSTOMERS; Output The output obtained is as shown below − ID NAME SALARY 1 Ramesh 2000.00 2 Khilan 1500.00 3 Kaushik 2000.00 4 Chaitali 6500.00 5 Hardik 8500.00 6 Komal 4500.00 7 Muffy 10000.00 2. Use Wildcards Wildcards (%) are characters that we use to search for data based on patterns. These wildcards paired with indexes only improves performance because the database can quickly find the data that matches the pattern. Example If we want to retrieve the names of all the customers starting with K from the CUSTOMERS table, then, the following query will provide the quickest result − SELECT ID, NAME FROM CUSTOMERS WHERE NAME LIKE ”K%”; Output Following is the output of the above query − ID NAME 2 Khilan 3 Kaushik 6 Komal 3. Use Explicit Join SQL JOINs are used to combine two tables based on a common column. There are two ways of creating a JOIN implicit join and explicit join. Explicit Join notation use the JOIN keyword with the ON clause to join two tables while the implicit join notation does not use the JOIN keyword and works with the WHERE clause. Performance wise, they are both on the same level. However, in more complicated cases, the implicit join notation might produce completely different results than intended. Therefore, Explicit Joining is preferred. 4. Avoid using SELECT DISTINCT The DISTINCT operator in SQL is used to retrieve unique records from the database. And on a properly designed database table with unique indexes, we rarely use it. But, if we still have to use it on a table, using the GROUP BY clause instead of the DISTINCT keyword shows a better query performance (at least in some databases). 5. Avoid using Multiple OR The OR operator is used to combine multiple conditions when filtering a database. Whenever we use OR in a filter condition each statement is processed separately. This degrades database performance as the entire table must be scanned multiple times to retrieve the data that matches the filter condition. Instead, we can use a more optimized solution; by breaking the different OR conditions into separate queries, which can be processed parallelly by the database. Then, the results from these queries can be combined using UNION. Example For example, let us say we have a requirement of getting the details of all the customers whose age is greater than 25 or whose salary is greater than 2,000. The optimized query would be as show below − SELECT ID, NAME FROM CUSTOMERS WHERE AGE > 25 UNION SELECT ID, NAME FROM CUSTOMERS WHERE SALARY > 2000; Output After executing the above code, we get the following output − ID NAME 1 Ramesh 5 Hardik 4 Chaitali 6 Komal 7 Muffy 6. Use WHERE instead of HAVING The WHERE and HAVING clause are both used to filter data in SQL. However, WHERE clause is more efficient than HAVING. With WHERE clause, only the records that match the condition are retrieved. But with HAVING clause, it first retrieves all the records and then filters them based

SQL – Transactions Table of content SQL Transactions Properties of Transactions Transactional Control Commands The COMMIT Command The ROLLBACK Command The SAVEPOINT Command The RELEASE SAVEPOINT Command The SET TRANSACTION Command ”; Previous Next SQL Transactions A transaction is a unit or sequence of work that is performed on a database. Transactions are accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program. A transaction is the propagation of one or more changes to the database. For example, if you are creating, updating or deleting a record from the table, then you are performing a transaction on that table. It is important to control these transactions to ensure the data integrity and to handle database errors. Practically, you will club many SQL queries into a group and you will execute all of them together as a part of a transaction. Properties of Transactions Transactions have the following four standard properties, usually referred to by the acronym ACID. Atomicity − ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state. Consistency − ensures that the database properly changes states upon a successfully committed transaction. Isolation − enables transactions to operate independently of and transparent to each other. Durability − ensures that the result or effect of a committed transaction persists in case of a system failure. Transactional Control Commands Transactional control commands are only used with the DML Commands such as – INSERT, UPDATE and DELETE. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database. Following commands are used to control transactions. COMMIT − to save the changes. ROLLBACK − to roll back the changes. SAVEPOINT − creates points within the groups of transactions in which to ROLLBACK. SET TRANSACTION − Places a name on a transaction. The COMMIT Command The COMMIT command is the transactional command used to save changes invoked by a transaction. It saves all the transactions occurred on the database since the last COMMIT or ROLLBACK. The syntax for the COMMIT command is as follows. COMMIT; Example Firstly, let us create a table names 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 (18, 2), PRIMARY KEY (ID) ); We are inserting some records into the above-created table − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00), (2, ”Khilan”, 25, ”Delhi”, 1500.00), (3, ”Kaushik”, 23, ”Kota”, 2000.00), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00), (5, ”Hardik”, 27, ”Bhopal”, 8500.00), (6, ”Komal”, 22, ”Hyderabad”, 4500.00), (7, ”Muffy”, 24, ”Indore”, 10000.00); The table will be created as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Following query would delete those records from the table which have AGE as 25 and then COMMIT the changes in the database. DELETE FROM CUSTOMERS WHERE AGE = 25; COMMIT; Verification The two rows from the table would be deleted and if you verify the contents of the CUSTOMERS table using the SELECT statement as − SELECT * FROM CUSTOMERS; The table will be displayed as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 3 Kaushik 23 Kota 2000.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 The ROLLBACK Command The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database. This command can only undo transactions since the last COMMIT or ROLLBACK. The syntax for a ROLLBACK command is as follows − ROLLBACK; Example Consider the CUSTOMERS table having the following records − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 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 query would delete those records from the table where the AGE value is 25 and then ROLLBACK the changes in the database. DELETE FROM CUSTOMERS WHERE AGE = 25; ROLLBACK; Verification The delete operation would not impact the table and the SELECT statement would produce the following result. ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 The SAVEPOINT Command A SAVEPOINT is a logical rollback point in a transaction. Usually, when you execute the ROLLBACK command, it undoes the changes until the last COMMIT. But, if you create save points you can partially roll the transaction back to these points. You can create multiple save points between two commits. The syntax to create a SAVEPOINT among the transactions is as shown below. SAVEPOINT savepoint_name; Then, to roll back to the SAVEPOINT created, you can use the following syntax − ROLLBACK TO savepoint_name; Example Following is an example where you plan to delete the three different records from the CUSTOMERS table. You want to create a SAVEPOINT before each delete, so that you can ROLLBACK to any SAVEPOINT at any time to return the appropriate data to its original state. Consider the CUSTOMERS table having the following records. ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 The following code block contains the series of operations. SAVEPOINT SP1; Query OK, 0 rows affected (0.00 sec) DELETE

SQL – Unique Indexes Table of content SQL Unique Indexes Updating with Duplicate Values Creating a unique index on Multiple Fields ”; Previous Next SQL Unique Indexes The SQL Unique Index ensures that no two rows in the indexed columns of a table have the same values (no duplicate values allowed). A unique index can be created on one or more columns of a table using the CREATE UNIQUE INDEX statement in SQL. Following are the points to be noted before creating a Unique Index on a table − If the unique index is only created on a single column, the rows in that column will be unique. If a single column contains NULL in multiple rows, we cannot create a unique index on that column. If the unique index is created on multiple columns, the combination of rows in these columns will be unique. We cannot create a unique index on multiple columns if the combination of columns contains NULL in more than one row. Syntax Following is the syntax for creating a UNIQUE INDEX in SQL − CREATE UNIQUE INDEX index_name ON table_name (column1, column2, …, columnN); Here, index_name is the name of the index that you want to create. table_name is the name of the table on which you want to create the index. (column1, column2, …., columnN) are the names of one or more columns on which the unique index is being created. Example First of all, let us create a table named CUSTOMERS 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) ); 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”, ”26”, ”Mumbai”, 6500), (5, ”Hardik”,”27”, ”Bhopal”, 8500), (6, ”Komal”, ”22”, ”Hyderabad”, 9000), (7, ”Muffy”, ”24”, ”Indore”, 5500); Once the table is created, let us create a unique index for the column named SALARY in the CUSTOMERS table using the following query − CREATE UNIQUE INDEX UNIQUE_ID ON CUSTOMERS (SALARY); But, when we execute the above query, the output is obtained as follows − ERROR 1062 (23000): Duplicate entry ”2000.00” for key ”customers.UNIQUE_ID” Since a unique index could not be created on SALARY column (due to duplicate values), let us create Unique Index on the NAME column of the same table, using the following query − CREATE UNIQUE INDEX UNIQUE_ID ON CUSTOMERS (NAME); Output When we execute the above query, the output is obtained as follows − Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification Let”s verify whether the unique index for the column NAME is created or not using the following query − SHOW INDEX FROM CUSTOMERS; As you observe the output below, you can find the column NAME along with the ID (PRIMARY KEY) in the list of indexes. Table Non_unique Key_name Seq_in_index Column_name customers 0 PRIMARY 1 ID customers 0 UNIQUE_ID 1 NAME Updating with Duplicate Values If we try to update the columns that have unique index with duplicate values, the database engine generates an error. Example Assume the previously created CUSTOMERS table and create a unique index on the column named ADDRESS using the following query − CREATE UNIQUE INDEX ADD_UNIQUE_INDEX ON CUSTOMERS(ADDRESS); Now, let us update the value in the column named ADDRESS with a duplicate (already existing data) value using the following query − UPDATE CUSTOMERS SET ADDRESS = ”Mumbai” WHERE ADDRESS = ”Delhi”; Output On executing the above query, the output is displayed as follows − ERROR 1062 (23000): Duplicate entry ”Mumbai” for key ”customers.ADD_UNIQUE_INDEX” Creating a unique index on Multiple Fields We can also create a unique index on multiple fields or columns of a table using the CREATE UNIQUE INDEX statement. To do so, you just need to pass the name of the columns (you need to create the index on) to the query. Example Instead of creating a new table, let us consider the previously created CUSTOMERS table. We will create a unique index on the columns NAME and AGE using the following query − CREATE UNIQUE INDEX MUL_UNIQUE_INDEX ON CUSTOMERS(NAME, AGE); Output When we execute the above query, the output is obtained as follows − Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0. Verification Now, let us list all the indexes that are created on the CUSTOMERS table using the following query − SHOW INDEX FROM CUSTOMERS; As you observe you can find the column names NAME, and AGE along with the ID (PRIMARY KEY) in the list of indexes. Table Non_unique Key_name Seq_in_index Column_name customers 0 PRIMARY 1 ID customers 0 MUL_UNIQUE_INDEX 1 NAME customers 0 MUL_UNIQUE_INDEX 2 AGE Print Page Previous Next Advertisements ”;

SQL – Non-Clustered Index Table of content SQL Non-Clustered Indexes Creating Non-Clustered Index on Multiple Columns ”; Previous Next SQL Non-Clustered Indexes The SQL Non-Clustered index is similar to the Clustered index. When defined on a column, it creates a special table which contains the copy of indexed columns along with a pointer that refers to the location of the actual data in the table. However, unlike Clustered indexes, a Non-Clustered index cannot physically sort the indexed columns. Following are some of the key points of the Non-clustered index in SQL − The non-clustered indexes are a type of index used in databases to speed up the execution time of database queries. These indexes require less storage space than clustered indexes because they do not store the actual data rows. We can create multiple non-clustered indexes on a single table. MySQL does not have the concept of Non-Clustered indexes. The PRIMARY KEY (if exists) and the first NOT NULL UNIQUE KEY(if PRIMARY KEY does not exist) are considered clustered indexes in MySQL; all the other indexes are called Secondary Indexes and are implicitly defined. To get a better understanding, look at the following figure illustrating the working of non-clustered indexes − Assume we have a sample database table with two columns named ID and NAME. If we create a non-clustered index on a column named ID in the above table, it will store a copy of the ID column with a pointer that points to the specific location of the actual data in the table. Syntax Following is the syntax to create a non-clustered index in SQL Server − CREATE NONCLUSTERED INDEX index_name ON table_name (column_name) Here, index_name: holds the name of non-clustered index. table_name: holds the name of the table where you want to create the non-clustered index. column_name: holds the name of the column that you want to define the non-clustered index on. Example 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), ); Let us insert some values into the above-created table using the following query − INSERT INTO CUSTOMERS VALUES (7, ”Muffy”, ”24”, ”Indore”, 5500), (1, ”Ramesh”, ”32”, ”Ahmedabad”, 2000), (6, ”Komal”, ”22”, ”Hyderabad”, 9000), (2, ”Khilan”, ”25”, ”Delhi”, 1500), (4, ”Chaitali”, ”25”, ”Mumbai”, 6500), (5, ”Hardik”,”27”, ”Bhopal”, 8500), (3, ”Kaushik”, ”23”, ”Kota”, 2000); The table is successfully created in the SQL database. ID NAME AGE ADDRESS SALARY 7 Muffy 24 Indore 5500.00 1 Ramesh 32 Ahmedabad 2000.00 6 Komal 22 Hyderabad 9000.00 2 Khilan 25 Delhi 1500.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 3 Kaushik 23 Kota 2500.00 Now, let us create a non-clustered index on a single column named ID using the following query − CREATE NONCLUSTERED INDEX NON_CLU_ID ON customers (ID ASC); Output On executing the above query, the output is displayed as follows − Commands Completed Successfully. Verification Let us retrieve all the indexes that are created on the CUSTOMERS table using the following query − EXEC sys.sp_helpindex @objname = N”CUSTOMERS”; As we observe, we can find the column named ID in the list of indexes. index_name index_description index_keys 1 NON_CLU_ID nonclustered located on PRIMARY ID Now, retrieve the CUSTOMERS table again using the following query to check whether the table is sorted or not − SELECT * FROM CUSTOMERS; As we observe, the non-clustered index does not sort the rows physically instead, it creates a separate key-value structure from the table data. ID NAME AGE ADDRESS SALARY 7 Muffy 24 Indore 5500.00 1 Ramesh 32 Ahmedabad 2000.00 6 Komal 22 Hyderabad 9000.00 2 Khilan 25 Delhi 1500.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 3 Kaushik 23 Kota 2500.00 Creating Non-Clustered Index on Multiple Columns Instead of creating a new table, let us consider the previously created CUSTOMERS table. Now, try to create a non-clustered index on multiple columns of the table such as ID, AGE and SALARY using the following query − CREATE NONCLUSTERED INDEX NON_CLUSTERED_ID ON CUSTOMERS (ID, AGE, SALARY); Output The below query will create three separate non-clustered indexes for ID, AGE, and SALARY. Commands Completed Successfully. Verification Let us retrieve all the indexes that are created on the CUSTOMERS table using the following query − EXEC sys.sp_helpindex @objname = N”CUSTOMERS”; As we observe, we can find the column names ID, AGE and SALARY columns in the list of indexes. index_name index_description index_keys 1 NON_CLU_ID nonclustered located on PRIMARY ID, AGE, SALARY Print Page Previous Next Advertisements ”;

SQL – Hosting Table of content SQL Hosting MS SQL Server Oracle MySQL MS Access ”; Previous Next SQL Hosting SQL Hosting is nothing but a means to manage any RDBMS linked to your website using SQL. If the website has an access to a RDBMS, any data from the website you created will be stored and retrieved from this database. There are various SQL hosting plans available if your web server is hosted by an Internet Service Provider (ISP). Following are the most common SQL hosting databases − MS SQL Server Oracle MySQL MS Access MS SQL Server MS SQL Server is created and developed by Microsoft. It is compatible with both virtual and cloud servers. It is very efficient to use with database-driven websites having high traffic. MS SQL Server”s features include − Maximum scalability and security Integrated reporting capabilities Easy to use Powerful Robust Offers more diverse features while hosting Oracle Oracle is a popular database which is suitable to use with high-traffic websites. This database offers various features like, Cost-effective product High-performance Converged, multi-model database management system In-memory MySQL databases Oracle is also a very powerful, robust and full featured SQL database system. MySQL MySQL is one of the most popular RDBMS in the world used to store and manage data. It is compatible with any type of server, say cloud, virtual or dedicated. Features of MySQL are as follows − Easy to use High performance Excellent security Improved speed Cost effective MS Access Microsoft Access is a very simple database which can be used for simple websites. MS Access is neither as powerful as MySQL, MS SQL Server or Oracle. Thus, it is not effective for websites with higher traffic. Print Page Previous Next Advertisements ”;

SQL – Handling Duplicates Table of content Why is Handling Duplicates in SQL Necessary? Preventing Duplicate Entries Counting and Identifying Duplicates Eliminating Duplicates from a Table ”; Previous Next Sometimes, tables or result sets contain duplicate records. While in most cases, duplicates are allowed, there are situations where it is necessary to prevent duplicate records and remove them from a database table. Why is Handling Duplicates in SQL Necessary? Handling duplicates in an SQL database becomes necessary to prevent the following consequences − The existence of duplicates in an organizational database will lead to logical errors. Duplicate data occupies space in the storage, which leads to decrease in usage efficiency of a database. Due to the increased use of resources, the overall cost of the handling resources rises. With increase in logical errors due to the presence of duplicates, the conclusions derived from data analysis in a database will also be erroneous. This chapter will describe how to prevent the occurrence of duplicate records in a table and how to remove the already existing duplicate records. Preventing Duplicate Entries To prevent the entry of duplicate records into a table, we can define a PRIMARY KEY or a UNIQUE Index on the relevant fields. These database constraints ensure that each entry in the specified column or set of columns is unique. Example Let us create a CUSTOMERS table using the following query − CREATE TABLE CUSTOMERS ( FIRST_NAME CHAR(20), LAST_NAME CHAR(20), SEX CHAR(10) ); As we have not defined any constraints on the table, duplicate records can be inserted into it. To prevent such cases, add a PRIMARY KEY constraint on relevant fields (say LAST_NAME and FIRST_NAME together) − ALTER TABLE CUSTOMERS ADD PRIMARY KEY (LAST_NAME, FIRST_NAME); Using INSERT IGNORE Query: Alternatively, we can use the INSERT IGNORE statement to insert records without generating an error for duplicates as shown below − INSERT IGNORE INTO CUSTOMERS (LAST_NAME, FIRST_NAME) VALUES ( ”Jay”, ”Thomas”), ( ”Jay”, ”Thomas”); As you can see below, the table will only consist of a single record (ignoring the duplicate value). FIRST_NAME LAST_NAME SEX Thomas Jay NULL Using REPLACE Query: Or, use the REPLACE statement to replace duplicates as shown in the following query − REPLACE INTO CUSTOMERS (LAST_NAME, FIRST_NAME) VALUES ( ”Ajay”, ”Kumar”), ( ”Ajay”, ”Kumar”); The table will contain the following records − FIRST_NAME LAST_NAME SEX Kumar Ajay NULL Thomas Jay NULL The choice between the INSERT IGNORE and REPLACE statements should be made based on the desired duplicate-handling behaviour. The INSERT IGNORE statement retains the first set of duplicate records and discards any subsequent duplicates. Conversely, the REPLACE statement preserves the last set of duplicates and erases any earlier ones. Using UNIQUE Constraint: Another way to enforce uniqueness in a table is by adding a UNIQUE constraint rather than a PRIMARY KEY constraint − CREATE TABLE BUYERS ( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20) NOT NULL, SEX CHAR(10), UNIQUE (LAST_NAME, FIRST_NAME) ); Counting and Identifying Duplicates To count and identify duplicate records based on specific columns, we can use the COUNT function and GROUP BY clause. Example Following is the query to count duplicate records with FIRST_NAME and LAST_NAME in the BUYERS − SELECT COUNT(*) as repetitions, LAST_NAME, FIRST_NAME FROM BUYERS GROUP BY LAST_NAME, FIRST_NAME HAVING repetitions > 1; This query will return a list of all the duplicate records in the PERSON_TABLE table. To identify sets of values that are duplicated, follow the steps given below − Determine which columns contain the values that may be duplicated. List those columns in the column selection list, along with the COUNT(*). List the columns in the GROUP BY clause as well. Add a HAVING clause that eliminates the unique values by requiring the group counts to be greater than one. Eliminating Duplicates from a Table We can use the DISTINCT keyword along with the SELECT statement to retrieve unique records from a table. SELECT DISTINCT LAST_NAME, FIRST_NAME FROM BUYERS ORDER BY LAST_NAME; Alternatively, you can include a GROUP BY clause specifying the columns you are selecting to eliminate duplicates − SELECT LAST_NAME, FIRST_NAME FROM BUYERS GROUP BY LAST_NAME, FIRST_NAME; Print Page Previous Next Advertisements ”;

SQL – Full Join Table of content The SQL Full Join Joining Multiple Tables with Full Join Full Join with WHERE Clause ”; Previous Next The SQL Full Join SQL Full Join creates a new table by joining two tables as a whole. The joined table contains all records from both the tables and fills NULL values for missing matches on either side. In short, full join is a type of outer join that combines the result-sets of both left and right joins. MySQL does not support Full Outer Join. Instead, you can imitate its working by performing union operation between the result-sets obtained from Left Join and Right Join. Let us understand this concept in detail with the help of a Venn diagram below. Assume that we have two tables as two sets (represented by circles). The result-set (or newly joined table) obtained using full join is nothing but the union of these two sets. You can also achieve the equivalent result-set of FULL JOIN by performing the UNION operation on result-sets of the LEFT JOIN and RIGHT JOIN. Syntax Following is the basic syntax of Full Join in SQL − SELECT column_name(s) FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name; Example Assume we have created a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc.Using the following query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now insert 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 Let us create another table ORDERS, containing the details of orders made and the date they are made on. CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2) ); Using the INSERT statement, insert values into this table as follows − INSERT INTO ORDERS VALUES (102, ”2009-10-08 00:00:00”, 3, 3000.00), (100, ”2009-10-08 00:00:00”, 3, 1500.00), (101, ”2009-11-20 00:00:00”, 2, 1560.00), (103, ”2008-05-20 00:00:00”, 4, 2060.00); The table is displayed 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 joins the two tables CUSTOMERS and ORDERS in SQL Server − SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS FULL JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID; Output The resultant table is produced as follows − ID NAME AMOUNT DATE 1 Ramesh NULL NULL 2 Khilan 1560 2009-11-20 00:00:00 3 Kaushik 3000 2009-10-08 00:00:00 3 Kaushik 1500 2009-10-08 00:00:00 4 Chaitali 2060 2008-05-20 00:00:00 5 Hardik NULL NULL 6 Komal NULL NULL 7 Muffy NULL NULL Joining Multiple Tables with Full Join The Full Join query can also be used to join more than just two tables. To do that, we sequentially combine two tables at a time, until all the tables are joined together. Note that in MySQL database, there is no provision to directly use the FULL JOIN keyword to perform join operation on multiple tables. Instead, calculate the UNION of LEFT JOIN and RIGHT JOIN on two tables at a time, until all the tables are joined. Syntax The syntax to join multiple tables using Full Join is given below − SELECT column1, column2, column3… FROM table1 FULL JOIN table2 ON condition_1 FULL JOIN table3 ON condition_2 …. …. FULL JOIN tableN ON condition_N; Example To demonstrate Full Join, let us consider the sample tables CUSTOMERS and ORDERS that we previously created, and create another table name EMPLOYEE using the following query − CREATE TABLE EMPLOYEE ( EID INT NOT NULL, EMPLOYEE_NAME VARCHAR (30) NOT NULL, SALES_MADE DECIMAL (20) ); Now, we can insert values into this empty tables using the INSERT statement as follows − INSERT INTO EMPLOYEE VALUES (102, ”SARIKA”, 4500), (100, ”ALEKHYA”, 3623), (101, ”REVATHI”, 1291), (103, ”VIVEK”, 3426); The EMPLOYEE table created, will be as shown below − EID EMPLOYEE_NAMENAME SALES_MADE 102 SARIKA 4500 100 ALEKHYA 3623 101 REVATHI 1291 103 VIVEK 3426 Let us join these three tables using the full join query given below − SELECT CUSTOMERS.ID, CUSTOMERS.NAME, ORDERS.DATE, EMPLOYEE.EMPLOYEE_NAME FROM CUSTOMERS FULL JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID FULL JOIN EMPLOYEE ON ORDERS.OID = EMPLOYEE.EID; Through this query, we will display the id, name of the customer along with the date on which the orders are made and the name of the employee who sold the item. Output The resultant table is obtained as follows − ID NAME DATE EMPLOYEE_NAME 1 Ramesh NULL NULL 2 Khilan 2009-11-20 00:00:00 REVATHI 3 Kaushik 2009-10-08 00:00:00 ALEKHYA 3 Kaushik 2009-10-08 00:00:00 SARIKA 4 Chaitali 2008-05-20 00:00:00 VIVEK 5 Hardik NULL NULL 6 Komal NULL NULL 7 Muffy NULL NULL Full Join with WHERE Clause Joins use the ON clause to filter records by default. Let us suppose there is a further requirement to filter these records based on a certain condition/constraint, we can also make use of the WHERE clause with Joins. Syntax The syntax of Full Join when used with WHERE clause is given below − SELECT column_name(s) FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name WHERE condition; Example Consider the previous two tables CUSTOMERS and ORDERS, and join them using the following Full Join query by applying some constraints using the WHERE clause. SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS FULL JOIN ORDERS

SQL – Wildcards ”; Previous Next SQL Wildcards SQL Wildcards are special characters used as substitutes for one or more characters in a string. They are used with the LIKE operator in SQL, to search for specific patterns in character strings or compare various strings. The LIKE operator in SQL is case-sensitive, so it will only match strings that have the exact same case as the specified pattern. Following are the most commonly used wildcards in SQL − S.No. Wildcard & Description 1 The percent sign (%) Matches one or more characters. Note − MS Access uses the asterisk (*) wildcard character instead of the percent sign (%) wildcard character. 2 The underscore (_) Matches one character. Note − MS Access uses a question mark (?) instead of the underscore (_) to match any one character. The percent sign (%) represents zero, one, or multiple characters within a string. The underscore (_) represents a single character or number. These symbols can also be used in combination to perform complex pattern searching and matching in SQL queries. Syntax Following is the basic syntax to use wildcard characters − SELECT * FROM table_name WHERE column_name LIKE [wildcard_pattern]; We can combine N number of conditions using the AND or the OR operators. Here, the [wildcard_pattern] can represent any numeric or string value. The following table demonstrates various ways of using wildcards in conjunction with the LIKE operator within a WHERE clause: S.No. Statement & Description 1 WHERE SALARY LIKE ”200%” Finds any values that start with 200. 2 WHERE SALARY LIKE ”%200%” Finds any values that have 200 in any position. 3 WHERE SALARY LIKE ”_00%” Finds any values that have 00 in the second and third positions. 4 WHERE SALARY LIKE ”2_%_%” Finds any values that start with 2 and are at least 3 characters in length. 5 WHERE SALARY LIKE ”%2” Finds any values that end with 2. 6 WHERE SALARY LIKE ”_2%3” Finds any values that have a 2 in the second position and end with a 3. 7 WHERE SALARY LIKE ”2___3” Finds any values in a five-digit number that start with 2 and end with 3. Example Firstly, 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 (18, 2), PRIMARY KEY (ID) ); The following INSERT query adds records into the CUSTOMERS table − 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 Here, we are displaying all the records from the CUSTOMERS table where the SALARY starts with 200. SELECT * FROM CUSTOMERS WHERE SALARY LIKE ”200%”; Output This would produce the following result. ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 3 Kaushik 23 Kota 2000.00 Print Page Previous Next Advertisements ”;

SQL – Create Index Table of content What is SQL Index? Creating an SQL Index Creating an Index on Multiple Fields ”; Previous Next An index is an effective way to quickly retrieve data from an SQL database. It is a database object that references the data stored in a table, which significantly improves query and application performance of a database. The process of indexing in SQL is similar to that of an index in a book: it is a database object in the form of a table, contains details of data location, and holds a separate storage space. Even though indexes help accelerate search queries, users are not able to directly see these indexes in action. What is SQL Index? An SQL index is a special lookup table that helps in efficiently searching or querying database tables to retrieve required data. For example, when we try to retrieve data from multiple tables using joins, indexes improve the query performance. Indexes are used to optimize the query performance of any Relational Database Management System (RDBMS) as data volumes grow. Hence, they are preferred to be used on frequently queried large database tables. Creating an SQL Index An index can be created on one or more columns of a table in an SQL database using the CREATE INDEX statement. Syntax Following is the syntax of CREATE INDEX statement in SQL − CREATE INDEX index_name ON table_name (column_name1, column_name2,… column_nameN); Here, index_name This specifies the name of the index that you want to create. table_name This specifies the name of the table on which you want to create the index. (column_name1, column_name2…column_nameN) are the names of one or more columns on which the index is being created. Example To create an index on a database table, we first need to create a table. So, in this example, we are creating a table named CUSTOMERS 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)); ); Then, insert some values into the CUSTOMERS 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 for the column named NAME in the CUSTOMERS table using the following query − CREATE INDEX index_name ON CUSTOMERS(NAME); Output When we execute the above query, the output is obtained as follows − Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification The following SHOW INDEX query is used to display all the indexes created on an existing table. SHOW INDEX FROM CUSTOMERS; In the list obtained, you can find the column name NAME, along with the ID in the list of indexes. Table Non_unique Key_name Seq_in_index Column_name customers 0 PRIMARY 1 ID customers 1 index_name 1 NAME Creating an Index on Multiple Fields We can also create an index on multiple fields (or columns) of a table using the CREATE INDEX statement. To do so, you just need to pass the name of the columns (you need to create the index on). Example Instead of creating a new table, let us consider the previously created CUSTOMERS table. Here, we are creating an index on the columns NAME and AGE using the following query − CREATE INDEX mult_index_data on CUSTOMERS(NAME, AGE); Output When we execute the above query, the output is obtained as follows − Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification Now, let us list all the indexes that are created on the CUSTOMERS table using the following SHOW INDEX query − SHOW INDEX FROM CUSTOMERS; As you observe, you can find the column names NAME, and AGE along with ID (PRIMARY KEY), in the list of indexes. Table Non_unique Key_name Seq_in_index Column_name customers 0 PRIMARY 1 ID customers 1 index_name 1 NAME customers 1 mult_index_data 1 NAME customers 1 mult_index_data 2 AGE Print Page Previous Next Advertisements ”;