Category: sql

SQL – Group By vs Order By Table of content The SQL Group By Clause The SQL Order By Clause Group by vs Order by ”; Previous Next In SQL, we have two commonly used clauses that help us to manipulate data; Group By clause and Order By clause. A Group By clause is used to arrange the identical data/records into groups and the Order By clause is used to sort the data in ascending or descending order. The SQL Group By Clause Using the GROUP BY clause we can organize the data in a table into groups (based on a column) and perform required calculations on them. This clause is often used with the aggregate functions such as MIN(), MAX(), SUM(), AVG(), and COUNT() etc. It is often used with the SELECT statement, and it is placed after the WHERE clause or before the HAVING clause. If we use the Order By clause, the Group By clause should precede the Order By clause. Syntax Following is the syntax of the SQL Group By clause − SELECT column_name, aggregate_function() FROM table_name WHERE condition GROUP BY column_name; The aggregate_function() and the WHERE clause are optional in the above syntax. Example Assume we have created a table named CUSTOMERS that contains records of customers such as NAME, AGE, ADDRESS, and SALARY etc.., using the following CREATE statement − 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, we are inserting values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00), (2, ”Khilan”, 25, ”Delhi”, 1500.00), (3, ”Kaushik”, 23, ”Kota”, 2000.00), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); The CUSTOMERS table will be created as − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 In the SQL query below, we are using the Group by clause to group the rows based on their salaries from the CUSTOMERS table and counting the number of records in each group − SELECT SALARY, COUNT(SALARY) from CUSTOMERS GROUP BY SALARY; Output When we execute the above query, the following result will be displayed − SALARY MAX(SALARY) 2000.00 2 1500.00 1 6500.00 1 8500.00 1 4500.00 1 10000.00 1 The SQL Order By Clause The ORDER BY clause is used to sort the query results. This clause is used at the end of a SELECT statement, following the WHERE, HAVING and GROUP BY clauses. We can sort the table column in ascending or descending order with the by specifying the sort order as ASC and DESC respectively. If we do not specify any order, it defaults to ascending order. Syntax Following is the syntax to sort the column value in ascending/descending order using the SQL ORDER BY clause − SELECT column_name FROM table_name ORDER BY ASC/DSC; Example In the following query, we are retrieving the ID and NAME from the CUSTOMERS table and using the ORDER BY clause, we are sorting the names in ascending order. SELECT ID, NAME FROM CUSTOMERS ORDER BY NAME; Output When we run the above query, we can see that the resultant table is sorted by name in ascending order. ID NAME 4 Chaitali 5 Hardik 3 Kaushik 2 Khilan 6 Komal 7 Muffy 1 Ramesh Example In the following example, we are retrieving the NAME, calculating the AVG SALARY, and using the GROUP BY clause to group the table by NAME. SELECT NAME, AVG(SALARY) FROM CUSTOMERS GROUP BY NAME; Output When we run the above query, we get the name and average salary. The average salary is the same as the actual salary because there are no two or more than two records with the same name. As a result, the average salary is the same as the actual salary, and the table is grouped by name. as shown in the table below. NAME AVG(SALARY) Ramesh 2000.000000 Khilan 1500.000000 Kaushik 2000.000000 Chaitali 6500.000000 Hardik 8500.000000 Komal 4500.000000 Muffy 10000.000000 Example In the following example, we are retrieving, NAME, AGE, and SALARY and using the ORDER BY clause to sort the AGE in the ascending order. SELECT NAME, AGE, SALARY FROM customers ORDER BY AGE; Output The table generated by the above query is as shown below − NAME AGE SALARY Komal 22 4500.00 Kaushik 23 2000.00 Muffy 24 10000.00 Khilan 25 1500.00 Chaitali 25 6500.00 Hardik 27 8500.00 Ramesh 32 2000.00 Group by vs Order by Following table summarizes the differences between the Group By clause and Order by clause − S.No. Group By Order By 1 It is applied to group rows with same values. It sorts the columns in either ascending or descending order. 2 It could be allowed in the create view statement. It is not allowed to create view statement. 3 The attribute cannot be assigned to the aggregate function in the Group By statement. The attribute can be assigned to the aggregate function in the Order By statement. 4 It is always used before the Order by clause in the select statement. It is always used after the Group by clause in the select statement. 5 Here grouping is done based on the similarity among the rows attribute value. Here, the result-set is sorted based on the columns attribute value either ascending or descending order. 6 It controls the presentation of the row It controls the presentation of the column. 7 We can use the aggregate function in the Group by. Here its not mandatory to use the aggregate function in the Order by. Print Page Previous Next Advertisements

SQL – Expressions Table of content What is SQL Expression? SQL Boolean Expressions SQL Numeric Expressions SQL Date Expressions ”; Previous Next What is SQL Expression? An SQL expression is a combination of one or more values, operators and SQL functions that are all evaluated to a value. These SQL EXPRESSION(s) are like formulae and they are written in query language. You can also use them to query the database for a specific set of data. Expressions are used in WHERE clause of an SQL query. As you might have already known, a WHERE clause specifies a condition that needs to be satisfied for the purpose of filtering records from a database table. This condition is comprised of either single or multiple expressions. These expressions are further classified into three types − Boolean Expressions Numeric Expressions Date and time Expressions Let us discuss each of these expressions in detail further in this chapter. Syntax Consider the basic syntax of the SELECT statement containing some expressions as follows − SELECT column1, column2, columnN FROM table_name WHERE [CONDITION|EXPRESSION]; SQL Boolean Expressions SQL Boolean Expressions are SQL expressions that return only Boolean Datatype as a result. These expressions can be of two types − Boolean Expressions that check for equality of two values using SQL comparison operators. Here, equality of these values is a condition. Boolean Expressions can also contain one value paired with an SQL logical operator. In this case, the logic specified acts like a condition. They return either TRUE, FALSE or UNKNOWN as the result. If the condition is met, these expressions return TRUE; and FALSE otherwise. UNKNOWN is returned when either of the operands in the expression is a NULL value. Syntax Following is the syntax of Boolean Expression − SELECT column1, column2, columnN FROM table_name WHERE BOOLEAN EXPRESSION; 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 The following query is a simple example showing the usage of an SQL Boolean Expression − SELECT * FROM CUSTOMERS WHERE SALARY = 10000; Output The output will be displayed as − ID NAME AGE ADDRESS SALARY 7 Muffy 24 Indore 10000.00 SQL Numeric Expressions SQL Numeric expressions are comprised of two operands and an SQL Arithmetic Operator. These expressions are used to perform any mathematical operation in any query. Hence, the operands must always be numerals and the return value will always be a number as well. Syntax Following is the syntax − SELECT numerical_expression as OPERATION_NAME FROM table_name WHERE NUMERICAL EXPRESSION ; Here, the numerical_expression is used for a mathematical expression or any formula. Example Following is a simple example showing the usage of SQL Numeric Expressions − SELECT 15 + 6; Output The output table is retrieved as − 21 Example There are several built-in functions like avg(), sum(), count(), etc., to perform what is known as the aggregate data calculations against a table or a specific table column. SELECT COUNT(*) FROM CUSTOMERS; Output The output is displayed as follows − 7 SQL Date Expressions SQL Date Expressions are used to compare date related values with current system date and time values. For instance, in a manufacturing company, items manufactured per year can be segregated by using date expressions in a WHERE clause. Counting from the first day of an year to the last day, the count of each item will be retrieved; once the required information is gathered, the company can use this information for their own purposes. Syntax Following is the syntax − SELECT column_name(s) FROM table_name WHERE DATE EXPRESSION ; Example In this example we are trying to simply retrieve the current timestamp of the system using CURRENT_TIMESTAMP. SELECT CURRENT_TIMESTAMP; Output The output table is displayed as − Current_Timestamp 2009-11-12 06:40:23 Example Consider the following Customer Order records in an ORDERS table: ORDER_ID CUSTOMER_ID DATE ORDER_AMOUNT ITEM_COUNT 102 3 2009-10-08 00:00:00 3000 4 100 3 2009-10-08 00:00:00 1500 2 101 2 2009-11-20 00:00:00 1560 7 103 4 2008-05-20 00:00:00 2060 3 Now let”s retrieve the records before 1st June, 2008: SELECT * FROM ORDERS WHERE DATE < ”2008/06/01”; Output The output table is displayed as − ORDER_ID CUSTOMER_ID DATE ORDER_AMOUNT ITEM_COUNT 103 4 2008-05-20 00:00:00 2060 3 Print Page Previous Next Advertisements ”;

SQL – Injection Table of content SQL Injection Preventing SQL Injection ”; Previous Next If you take a user input through a webpage and insert it into an SQL database, there is a chance that you have left yourself wide open for a security issue known as the SQL Injection. This chapter will teach you how to help prevent this from happening and help you secure your scripts and SQL statements in your server side scripts such as a PERL Script. SQL Injection SQL Injection is a type of security attack that exploits a vulnerability in a database by executing malicious queries. This will allow attackers to access sensitive data, tamper it and also delete it permanently. Injection usually occurs when you ask a user for input, like their name and instead of a name they give you a SQL statement that you will unknowingly run on your database. Never trust user provided data, process this data only after validation; as a rule, this is done by Pattern Matching. Example In the example below, the name is restricted to the alphanumerical characters plus underscore and to a length between 8 and 20 characters (you can modify these rules as needed). if (preg_match(“/^w{8,20}$/”, $_GET[”username”], $matches)) { $result = mysqli_query(“SELECT * FROM CUSTOMERS WHERE name = $matches[0]”); } else { echo “user name not accepted”; } To demonstrate the problem, consider this excerpt − // supposed input $name = “Qadir”; DELETE FROM CUSTOMERS;”; mysqli_query(“SELECT * FROM CUSTOMSRS WHERE name=”{$name}””); The function call is supposed to retrieve a record from the CUSTOMERS table where the name column matches the name specified by the user. Under normal circumstances, $name would only contain alphanumeric characters and perhaps spaces. But here, by appending an entirely new query to $name, the call to the database turns into disaster; the injected DELETE query removes all records from the CUSTOMERS table. Fortunately, if you use MySQL, the mysqli_query() function does not permit query stacking or executing multiple SQL queries in a single function call. If you try to stack queries, the call fails. However, other PHP database extensions, such as SQLite and PostgreSQL happily perform stacked queries, executing all the queries provided in one string and creating a serious security problem. Preventing SQL Injection You can handle all escape characters smartly in scripting languages like PERL and PHP. The MySQL extension for PHP provides the function mysql_real_escape_string() to escape input characters that are special to MySQL. if (get_magic_quotes_gpc()) { $name = stripslashes($name); } $name = mysql_real_escape_string($name); mysqli_query(“SELECT * FROM CUSTOMERS WHERE name=”{$name}””); The LIKE Quandary To address the LIKE quandary, a custom escaping mechanism must convert user-supplied ”%” and ”_” characters to literals. Use addcslashes(), a function that lets you specify a character range to escape. $sub = addcslashes(mysql_real_escape_string(“%str”), “%_”); // $sub == %str_ mysqli_query(“SELECT * FROM messages WHERE subject LIKE ”{$sub}%””); Print Page Previous Next Advertisements ”;

SQL – Common Table Expression (CTE) Table of content The SQL Common Table Expression The MySQL WITH Clause CTE from Multiple Tables Recursive CTE Advantages of CTE Disadvantages of CTE ”; Previous Next A Common Table Expression (CTE) can make it easier to manage and write complex queries by making them more readable and simple, like database views and derived tables. We can reuse or rewrite the query by breaking down the complex queries into simple blocks. The SQL Common Table Expression The WITH clause in MySQL is used to specify a Common Table Expression. A Common Table Expression (CTE) in SQL is a one-time result set, i.e. it is a temporary table that exists only during the execution of a single query. It allows us to work with data specifically within that query, such as using it in SELECT, UPDATE, INSERT, DELETE, CREATE, VIEW, OR MERGE statements. CTE is temporary because it cannot be stored anywhere for later use; once the query is executed, it is lost. The MySQL WITH Clause To specify common table expressions, we use WITH clause that consists of one or more comma-separated subclauses. Within each subclause, we can present a subquery that produces a result set and assigns a name to this subquery. You cannot use the WITH clause in MySQL versions before 8.0. Syntax Following is the syntax to create a CTE using WITH clause − WITH CTE_NAME (column_name) AS (query) SELECT * FROM CTE_NAME; Where, CTE_NAME − It is the name assigned to the CTE. column_name − It is the column names for the CTE, which can be useful for improving query readability. query − It defines the CTE and it can be any valid SQL query. After defining the CTE, you can reference it in subsequent queries within the same session. Example Assume we have created a table named 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) ); Now, 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 created is as shown below − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Here, we are creating a Common Table Expression (CTE) named CUSTOMER_AGE that selects all customers with an age of 23. We are then retrieving the ID, NAME, and AGE of these customers from the CTE. WITH CUSTOMER_AGE AS ( SELECT * FROM customers WHERE AGE = 23) SELECT ID, NAME, AGE FROM CUSTOMER_AGE; Output Following is the output of the above query − ID NAME AGE 3 Kaushik 23 CTE from Multiple Tables We can also create a Common Table Expression (CTE) that combines data from multiple tables by using JOIN operations within the CTE”s subquery. To do this, we need to use the comma operator to separate each CTE definition, effectively merging them into a single statement. Syntax Following is the basic syntax for multiple Common Table Expression (CTE) − WITH CTE_NAME1 (column_name) AS (query), CTE_NAME2 (column_name) AS (query) SELECT * FROM CTE_NAME1 UNION ALL SELECT * FROM CTE_NAME2; We can use multiple Common Table Expressions (CTEs) with various SQL operations, such as UNION, UNION ALL, JOIN, INTERSECT, or EXCEPT. Example In here, we are defining two CTEs namely ”CUSTOMERS_IN_DELHI” and ”CUSTOMERS_IN_MUMBAI” to segregate customers based on their addresses in Delhi and Mumbai. Then, we are using the UNION ALL operator to combine the results from both CTEs into a single result set, retrieving customer information from both cities. WITH CUSTOMERS_IN_DELHI AS ( SELECT * FROM CUSTOMERS WHERE ADDRESS = ”Delhi”), CUSTOMERS_IN_MUMBAI AS ( SELECT * FROM CUSTOMERS WHERE ADDRESS = ”Mumbai”) SELECT ID, NAME, ADDRESS FROM CUSTOMERS_IN_DELHI UNION ALL SELECT ID, NAME, ADDRESS FROM CUSTOMERS_IN_MUMBAI; Output Output of the above query is as shown below − ID NAME ADDRESS 2 Khilan Delhi 4 Chaitali Mumbai Recursive CTE A common table expression is a query that keeps referring back to its own result in a loop repeatedly until it returns an empty result. A recursive query continually iterates across a subset of the data during its execution, and defines itself in a self-referencing manner. This self-referencing mechanism allows it to repeatedly process and expand its results until a stopping condition is met. To make a CTE recursive, it must include a UNION ALL statement and provide a second definition of the query that utilizes the CTE itself. This allows the CTE to repeatedly reference to its own results, creating a recursive behaviour in the query.

SQL – Using Sequences Table of content Sequences in MySQL Starting a Sequence at a Particular Value in MySQL Sequences in SQL Server ”; Previous Next Sequences in SQL are database objects that generate a sequence of unique integer values. They are frequently used in databases because many applications require that each row in a table must contain unique values and sequences provide an easy way to generate them. Sequences are a feature of many SQL database management systems, such as Oracle, PostgreSQL, SQL server, and IBM DB2. MySQL does not support the CREATE SEQUENCE statement to create sequences for table rows or columns. Instead, we can use AUTO_INCREMENT attribute. Sequences in MySQL In MySQL, we use the AUTO_INCREMENT attribute to generate unique integer values (sequences) for a column. By default, the sequence starts with an initial value of 1 and increments by 1 for each new row. Syntax Following is the syntax of AUTO_INCREMENT attribute in MySQL − CREATE TABLE table_name ( column1 datatype AUTO_INCREMENT, column2 datatype, column3 datatype, … columnN datatype ); Example In the following example, we are creating a table named CUSTOMERS. In addition to that, we are defining AUTO_INCREMENT on ID column of the table. CREATE TABLE CUSTOMERS ( ID INT AUTO_INCREMENT, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Here, we are adding some records into the above created table − INSERT INTO CUSTOMERS VALUES (NULL, ”Ramesh”, 32, ”Ahmedabad”, 2000.00), (NULL, ”Khilan”, 25, ”Delhi”, 1500.00), (NULL, ”Kaushik”, 23, ”Kota”, 2000.00), (NULL, ”Chaitali”, 25, ”Mumbai”, 6500.00), (NULL, ”Hardik”, 27, ”Bhopal”, 8500.00), (NULL, ”Komal”, 22, ”Hyderabad”, 4500.00), (NULL, ”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 As we can see in the above table, the values in the ID column are auto incremented. Starting a Sequence at a Particular Value in MySQL By default, MySQL sequences start from 1. To start a sequence with a different value, we use the AUTO_INCREMENT in combination with the ALTER statement. Syntax Following is the syntax to start the sequence with different value − ALTER TABLE table_name AUTO_INCREMENT = value; In the following query, we are creating a table named BUYERS with AUTO_INCREMENT defined on the ID column. CREATE TABLE BUYERS ( ID INT AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Here, we are making the sequence start with 100 using the ALTER Statement as shown below − ALTER TABLE BUYERS AUTO_INCREMENT=100; Now, we are adding records into the BUYERS table using the INSERT INTO statement − INSERT INTO BUYERS VALUES (”Ramesh”, 32, ”Ahmedabad”, 2000.00), (”Khilan”, 25, ”Delhi”, 1500.00), (”Kaushik”, 23, ”Kota”, 2000.00), (”Chaitali”, 25, ”Mumbai”, 6500.00), (”Hardik”, 27, ”Bhopal”, 8500.00), (”Komal”, 22, ”Hyderabad”, 4500.00), (”Muffy”, 24, ”Indore”, 10000.00); The table will be created as − ID NAME AGE ADDRESS SALARY 100 Ramesh 32 Ahmedabad 2000.00 101 Khilan 25 Delhi 1500.00 102 Kaushik 23 Kota 2000.00 103 Chaitali 25 Mumbai 6500.00 104 Hardik 27 Bhopal 8500.00 105 Komal 22 Hyderabad 4500.00 106 Muffy 24 Indore 10000.00 As observed in the table above, the values in the “ID” column begin with 100 instead of 1. Sequences in SQL Server In SQL server, a sequence can be created using the CREATE SEQUENCE statement. The statement specifies the name of the sequence, the starting value, the increment, and other properties of the sequence. Syntax Following is the syntax to create a sequence in SQL − CREATE SEQUENCE Sequence_Name START WITH Initial_Value INCREMENT BY Increment_Value MINVALUE Minimum_Value MAXVALUE Maximum_Value CYCLE|NOCYCLE; Here, Sequence_Name − This specifies the name of the sequence. Initial_Value − This specifies the starting value from where the sequence should start. Increment_Value − This specifies the value by which the sequence will increment by itself. This can be valued positively or negatively. Minimum_Value − This specifies the minimum value of the sequence. Maximum_Value − This specifies the maximum value of the sequence. Cycle − When the sequence reaches its Maximum_Value, it starts again from the beginning. Nocycle − An exception will be thrown if the sequence exceeds the Maximum_Value. Example First of all, let us create a table named CUSTOMERS using the following query − CREATE TABLE CUSTOMERS ( ID INT, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), ); We are inserting some records in the above-created table using INSERT INTO statement as shown in the query below − INSERT INTO CUSTOMERS VALUES (NULL, ”Ramesh”, 32, ”Ahmedabad”, 2000.00), (NULL, ”Khilan”, 25, ”Delhi”, 1500.00), (NULL, ”Kaushik”, 23, ”Kota”, 2000.00), (NULL, ”Chaitali”, 25, ”Mumbai”, 6500.00), (NULL, ”Hardik”, 27, ”Bhopal”, 8500.00), (NULL, ”Komal”, 22, ”Hyderabad”, 4500.00), (NULL, ”Muffy”, 24, ”Indore”, 10000.00 ); The table is successfully created in the SQL database. ID NAME AGE ADDRESS SALARY NULL Ramesh 32 Ahmedabad 2000.00 NULL Khilan 25 Delhi 1500.00 NULL Kaushik 23 Kota 2000.00 NULL Chaitali 25 Mumbai 6500.00 NULL Hardik 27 Bhopal 8500.00 NULL Komal 22 Hyderabad 4500.00 NULL Muffy 24 Indore 10000.00 Now, create a sequence using the following query − CREATE SEQUENCE My_Sequence AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 7 CYCLE; In the above query, the sequence is named My_Sequence and it starts with the value 1 and increments by 1 each time a value is generated. The sequence has a maximum value of 5 and cycles back to the starting value when it reaches the maximum value. Once the sequence is created, it can be used to generate unique integer values. Now, let us update the data in the ID column of the CUSTOMERS table using the following query − UPDATE CUSTOMERS SET ID = NEXT VALUE FOR my_Sequence; Output When you execute the above query, the

SQL – Statistical Functions ”; Previous Next SQL server Statistical functions work on SQL server tables and views and exploit all database parallelism, scalability, user privileges and security schemes. Hence the SQL statistical functions can be included and exposed within SQL queries − Sr.No. Function & Description 1 @@CONNECTIONS() Returns number of attempted connections. 2 @@CPU_BUSY() Returns amount of time, SQL server spent on an active operation. 3 @@IDLE() returns the total idle time of this SQL server. 4 @@IO_BUSY() Returns the total spent time by this SQL server on performing the input and output operations. 5 @@PACK_RECEIVED() Returns the number of input packets. 6 @@PACK_SENT() Returns the number of output packets. 7 @@PACKET_ERRORS() Returns the number of network packet errors. 8 @@TIMETICKS() Returns the number of microseconds per tick. 9 @@TOTAL_ERRORS() Returns the number of disk errors. 10 @@TOTAL_READ() Returns the number of disk reads. 11 @@TOTOAL_WRITE() Returns the number of disk writes. Print Page Previous Next Advertisements ”;

SQL – Date Functions ”; Previous Next The Date & time functions are built-in functions in the SQL server that allows you to manipulate and perform operations on date and time values in the SQL server. These functions can be used in SQL Server queries to perform various date and time operations, such as filtering records based on dates, calculating date differences, and formatting dates for display purposes. Sr.No. Function & Description 1 @@DATEFIRST This function is used to retrieve the first day of the week which is set by the SET DATEFIRST function. 2 CURRENT_TIMESTAMP Is used to retrieve the current date and time. 3 CURRENT_TIMEZONE() This function is used to retrieve the current time zone offset from Coordinated Universal Time (UTC). 4 CURRENT_TIMEZONE_ID() Is used to retrieve the current time zone ID observed by a server or an instance. 5 DATE_BUCKET() Is used to group data into groups that correspond to fixed periods of time. 6 DATEADD() Is used to add a specific number of intervals to a given date or time value. 7 DATEDIFF() Is used to calculate the difference between two date values and returns in a int data type. 8 DATEDIFF_BIG() Is used to calculate the difference between two dates values and return in a bigint data type. 9 DATEFROMPARTS() This function is used to retrieve a date from individual segments such as year, month, and day. 10 DATENAME() This function is used to retrieve a specified part of a date or time value as string. 11 DATEPART() Is used to return a specified part of a date or time value as integer. 12 DATETIME2FROMPARTS() Is used to construct a datetime2 value from an individual date and time segments. 13 DATETIMEFROMPARTS() Is used to construct a datetime value from an individual date and time segments. 14 DATETIMEOFFSETFROMPARTS() Is used to extract a datetimeoffset value from each of a date”s component parts. 15 DATETRUNC() Is used to truncate a date or time value to a specified datepart (such as year, month, day, etc.). 16 DAY() This function is used to get the day of the month for a specified date. 17 EOMONTH() This function is used to get the last day of the month for a specified date value. 18 GETDATE() This function is used to get the current database system date and time. 19 GETUTCDATE() This function is used to get the current database system UTC date and time. 20 ISDATE() Is used to determine whether a value is a valid date or not. 21 MONTH() This function is used to get the month part for a specified date (value can be from 1 to 12). 22 SMALLDATETIMEFROMPARTS() Is used to construct a new datetime value from individual segments (such as year, month, etc.). 23 SWITCHOFFSET() This function is used to get datetimeoffset values that has been changed from the stored time zone offset to a given new time zone offset. 24 SYSDATETIME() This function is used to get the date and time of the SQL Server. 25 SYSDATETIMEOFFSET() This function is used to get a value of DATETIMEOFFSET(7) that provides the current system date and time and also timezone of the SQL server. 26 SYSUTCDATETIME() This function is used to get date and time of the computer on which the instance of SQL Server is running. 27 TIMEFROMPARTS() Is used to construct a time value from individual segments. 28 TODATETIMEOFFSET() This function is used to get a datetimeoffset value that is translated from a datetime2 expression. 29 YEAR() This function is used to get an integer number that represents the year of the given date. Print Page Previous Next Advertisements ”;

SQL – Indexes Table of content The SQL Indexes The CREATE INDEX Statement Types of Indexes The DROP INDEX Statement When should indexes be avoided? ”; Previous Next The SQL Indexes SQL Indexes are special lookup tables that are used to speed up the process of data retrieval. They hold pointers that refer to the data stored in a database, which makes it easier to locate the required data records in a database table. SQL Indexes work similar to the index of a book or a journal. While an index speeds up the performance of data retrieval queries (SELECT statement), it slows down the performance of data input queries (UPDATE and INSERT statements). However, these indexes do not have any effect on the data. SQL Indexes need their own storage space within the database. Despite that, the users cannot view them physically as they are just performance tools. The CREATE INDEX Statement An index in SQL can be created using the CREATE INDEX statement. This statement allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in an ascending or descending order. Preferably, an index must be created on column(s) of a large table that are frequently queried for data retrieval. Syntax The basic syntax of a CREATE INDEX is as follows − CREATE INDEX index_name ON table_name; Types of Indexes There are various types of indexes that can be created using the CREATE INDEX statement. They are: Unique Index Single-Column Index Composite Index Implicit Index Unique Indexes Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. It is automatically created by PRIMARY and UNIQUE constraints when they are applied on a database table, in order to prevent the user from inserting duplicate values into the indexed table column(s). The basic syntax is as follows. CREATE UNIQUE INDEX index_name on table_name (column_name); Single-Column Indexes A single-column index is created only on one table column. The syntax is as follows. CREATE INDEX index_name ON table_name (column_name); Composite Indexes A composite index is an index that can be created on two or more columns of a table. Its basic syntax is as follows. CREATE INDEX index_name on table_name (column1, column2); Implicit Indexes Implicit indexes are indexes that are automatically created by the database server when an object is created. For example, indexes are automatically created when primary key and unique constraints are created on a table in MySQL database. The DROP INDEX Statement An index can be dropped using SQL DROP command. Dropping an index can effect the query performance in a database. Thus, an index needs to be dropped only when it is absolutely necessary. The basic syntax is as follows − DROP INDEX index_name; When should indexes be avoided? Although indexes are intended to enhance a database”s performance, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered. Indexes should not be used on small tables. They should not be used on tables that have frequent, large batch updates or insert operations. Indexes should not be used on columns that contain a high number of NULL values. Columns that are frequently manipulated should not be indexed. Print Page Previous Next Advertisements ”;

SQL – Text & Image functions ”; Previous Next SQL Text and Image functions operate on text and image data. The text and image functions are − Sr.No. Function & Description 1 TEXTPTR() Returns the pointer value of the text or images. 2 TEXTVALID() Returns an integer value(1 for valid, 0 for non-valid). Print Page Previous Next Advertisements ”;

SQL – IN vs EXISTS Table of content The SQL IN Operator The SQL EXISTS Operator IN vs EXISTS ”; Previous Next In SQL, we use the IN operator to simplify queries and reduce the need for multiple OR conditions. It allows us to match a value against a list of values. On the other hand, the EXISTS operator checks whether one or more rows exist in a subquery and returns either true or false based on this condition. If the subquery finds at least one row, the EXISTS operator returns true; otherwise, it returns false. The SQL IN Operator The IN operator in SQL is used to check if a particular value matches any within a given set. This set of values can be specified individually or obtained from a subquery. We can use the IN operator with the WHERE clause to simplify queries and reduce the use of multiple OR conditions. Suppose we have a table named CUSTOMERS and we want to retrieve customer details based on their IDs. In this scenario, we can use the IN operator with the WHERE clause to fetch the details of these specific IDs. Syntax Following is the syntax of the SQL IN operator − SELECT column_name FROM table_name WHERE column_name IN (value1, value2, valueN); In the above syntax, the column_name matches every value (value1, value2, … valueN). If the matches occur, The IN operators returns true; otherwise, false. 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 (18, 2), PRIMARY KEY (ID) ); Now, add records into the above created table using the INSERT INTO statement as shown below − 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 The following query retrieves the NAME and SALARY columns from the CUSTOMERS table for rows where the ID is 1, 2, or 3. SELECT NAME, SALARY FROM CUSTOMERS WHERE ID IN(1, 2, 3); Output On executing the query above, the output is displayed as − Name Salary Ramesh 2000.00 Khilan 1500.00 Kaushik 2000.00 The SQL EXISTS Operator The EXISTS operator is used to look for the existence of a row in a given table that satisfies a set of criteria. It is a Boolean operator that compares the result of the subquery to an existing record and returns true or false. The returned value is true, if the subquery fetches single or multiple records; and false, if no record is matched. EXISTS operator follows the querys efficiency features, i.e. when the first true event is detected, it will automatically stop processing further. We can use the EXISTS operator with the SELECT, UPDATE, INSERT and DELETE queries. Syntax Following is the basic syntax of SQL EXISTS operator − SELECT column_name FROM table_name WHERE EXISTS ( SELECT column_name FROM table_name WHERE condition ); Example First of all, consider the CUSTOMERS table, and create another table named EMPLOYEES using the following query − CREATE TABLE EMPLOYEES ( EID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, CITY CHAR (25), CONTACT INT, PRIMARY KEY (EID) ); Now, let us insert some records into the EMPLOYEES table using the INSERT INTO statement as shown below − INSERT INTO EMPLOYEES VALUES (1, ”Varun”, 32, ”Ahmedabad”, 12345), (2, ”Mahesh”, 22, ”Kashmir”, 34235 ), (3, ”Suresh”, 43, ”Kerala”, 12355 ); The table will be created as follows − EID NAME AGE CITY CONTACT 1 Varun 32 Ahmedabad 12345 2 Mahesh 22 Kashmir 34235 3 Suresh 43 Kerala 12355 In the following query, we are using the EXISTS operator to fetch the names and ages of CUSTOMERS whose AGE is same as the AGE in the EMPLOYEES table. SELECT NAME, AGE FROM CUSTOMERS WHERE EXISTS( SELECT * FROM EMPLOYEES WHERE CUSTOMERS.AGE = EMPLOYEES.AGE ); Output Following is the output of the above query − NAME AGE Ramesh 32 Komal 22 IN vs EXISTS Following table summarizes all the differences between IN and EXISTS − S.No. IN EXISTS 1 It is applied to the SQL query to remove the multiple OR conditions. It is used to find whether the data in the subquery truly exist. 2 It executes all values contained within the IN block. If the value is matched, displays the details of the given value. It will terminate the further process if the condition is met. 3 It can be used for the comparison of a null value because it returns true, false, and a null value. It cannot be used for the comparison of a null value because it returns only true and false values. 4 It can be used with subqueries as well as with values. It can be used only with subqueries. 5 It executes faster when the subquery is smaller. It executes faster when the subquery is larger. Because it is more efficient than IN and returns only a Boolean value. Print Page Previous Next Advertisements ”;