Category: sql

SQL – Sub Queries Table of content SQL Subqueries Rules to be followed Subqueries with the SELECT Statement Subqueries with the INSERT Statement Subqueries with the UPDATE Statement Subqueries with the DELETE Statement ”; Previous Next SQL Subqueries An SQL Subquery, is a SELECT query within another query. It is also known as Inner query or Nested query and the query containing it is the outer query. The outer query can contain the SELECT, INSERT, UPDATE, and DELETE statements. We can use the subquery as a column expression, as a condition in SQL clauses, and with operators like =, >, <, >=, <=, IN, BETWEEN, etc. Rules to be followed Following are the rules to be followed while writing subqueries − Subqueries must be enclosed within parentheses. Subqueries can be nested within another subquery. A subquery must contain the SELECT query and the FROM clause always. A subquery consists of all the clauses an ordinary SELECT clause can contain: GROUP BY, WHERE, HAVING, DISTINCT, TOP/LIMIT, etc. However, an ORDER BY clause is only used when a TOP clause is specified. It can”t include COMPUTE or FOR BROWSE clause. A subquery can return a single value, a single row, a single column, or a whole table. They are called scalar subqueries. Subqueries with the SELECT Statement Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows − SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name OPERATOR (SELECT column_name [,column_name ] FROM table1 [, table2 ] [WHERE]); Example In the following query, we are creating a table named 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) ); Here, we are inserting records into the above-created table using INSERT INTO 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 is displayed 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 check the following subquery with a SELECT statement. SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500); This would produce the following result − ID NAME AGE ADDRESS SALARY 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 7 Muffy 24 Indore 10000.00 Subqueries with the INSERT Statement We can also use the subqueries along with the INSERT statements. The data returned by the subquery is inserted into another table. The basic syntax is as follows − INSERT INTO table_name [ (column1 [, column2 ]) ] SELECT [ *|column1 [, column2 ] FROM table1 [, table2 ] [ WHERE VALUE OPERATOR ] Example In the following example, we are creating another table CUSTOMERS_BKP with similar structure as CUSTOMERS table − CREATE TABLE CUSTOMERS_BKP ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now to copy the complete records of CUSTOMERS table into the CUSTOMERS_BKP table, we can use the following query − INSERT INTO CUSTOMERS_BKP SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS); The above query produces the following output − Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 Verification Using the SELECT statement, we can verify whether the records from CUSTOMERS table have been inserted into CUSTOMERS_BKP table or not − SELECT * FROM CUSTOMERS_BKP; The table will be displayed 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 Subqueries with the UPDATE Statement A subquery can also be used with the UPDATE statement. You can update single or multiple columns in a table using a subquery. The basic syntax is as follows − UPDATE table SET column_name = new_value [WHERE OPERATOR [VALUE](SELECT COLUMN_NAME FROM TABLE_NAME [WHERE]); Example We have the CUSTOMERS_BKP table available which is backup of CUSTOMERS table. The following example updates SALARY by 0.25 times in the CUSTOMERS table for all the customers whose AGE is greater than or equal to 27. UPDATE CUSTOMERS SET SALARY = SALARY * 0.25 WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 ); Following is the output of the above query − Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0 Verification This would impact two rows and if you verify the contents of the CUSTOMERS using the SELECT statement as shown below. SELECT * FROM CUSTOMERS; The table will be displayed as − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 500.00 2 Khilan 25 Delhi 1500.00 3 kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 2125.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Subqueries with the DELETE Statement The subquery can be used with the DELETE statement as well; like with any other statements mentioned above. The basic syntax is as follows − DELETE FROM TABLE_NAME [WHERE OPERATOR [ VALUE ](SELECT COLUMN_NAME FROM TABLE_NAME)[WHERE)]; Example We have a CUSTOMERS_BKP table available which is a backup of the CUSTOMERS table. The following example deletes the records from the CUSTOMERS table for all the customers whose AGE is greater than or equal to 27. DELETE FROM CUSTOMERS WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 ); The above query generate the following output − OK, 2 rows affected (0.01 sec) Verification If you verify the contents of

SQL – String Functions ”; Previous Next SQL string functions are used primarily for string manipulation. The following table details the important string functions − Sr.No. Function & Description 1 ASCII() Returns numeric value of left-most character 2 CHAR() Returns the character for each integer passed 3 CHARINDEX() Returns the position of a substring within the given string. 4 CONCAT_WS() Returns concatenate with separator 5 CONCAT() Returns concatenated string 6 DIFFERENCE() returns an integer value measuring the difference between the SOUNDEX() values of two different expressions(strings). 7 ESCAPE() Returns a text with escaped characters. 8 FORMAT() Returns the formatted string. 9 LEFT() Returns the extracting string. 10 LEN() Returns the length of the given string. 11 LOWER() Returns returns the lower case of the given string. 12 LTRIM() Returns a string after removing all the white spaces and characters from the string found on the left side. 13 NCHAR() Returns the Unicode character based on the number code. 14 PATINDEX() Returns the position of a pattern in a string. 15 QUOTENAME() Returns a string with a delimiter 16 REPLACE() Returns a new string by replacing all the occurrences of the particular part of a string (substring) with a specified string. 17 REPLICATE() Returns the repeated value. 18 REVERSE() Returns a reversed string. 19 RIGHT() Returns the rightmost characters from the actual(current) string. 20 RTRIM() Returns a string after removing all trailing blanks. 21 SOUNDEX() Returns the Soundex string. 22 SPACE() returns a string consisting of N number of space characters. 23 STR() Returns a number as string. 24 STRING_AGG() Concatenates the values of string expressions and places separator values between them. 25 STRING_SPLIT() Splits a string into rows of substrings. 26 STUFF() Returns a new string by inserting the second expression at the specified deleted place. 27 SUBSTRING() Returns the part of the character. 28 TRANSLATE() Returns a string from the first argument. 29 TRIM() Returns a trimmed string. 30 UNICODE() Returns an integer value of the first character. 31 UPPER() returns a string that has all the characters in upper case. Print Page Previous Next Advertisements ”;

SQL – Logical Functions ”; Previous Next SQL logical functions are used primarily for checking (or testing) the truth of some conditions. A logical function performs a logical operation or comparison on objects and expressions and returns a boolean value − Sr.No. Function & Description 1 CHOSE() Returns a item from the list at the specified index value. 2 IIF() Returns a one of two values, depending on the boolean-expression. 3 NULLIF() Returns NULL if both expressions are equal. Print Page Previous Next Advertisements ”;

SQL – Operators Table of content What is an Operator in SQL? Types of Operator in SQL SQL Arithmetic Operators SQL Comparison Operators SQL Logical Operators SQL Operator Precedence ”; Previous Next What is SQL Operator? An SQL operator is a reserved word or a character used primarily in an SQL statement”s WHERE clause to perform operation(s), such as comparisons and arithmetic operations. These Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement. An SQL operator can be either a unary or binary operator. A unary operator (example unary + or unary – ) uses only one operand to perform the unary operation, whereas the binary operator (example + or – etc) uses two operands to perform the binary operation. Types of Operator in SQL SQL supports following types of operators: Arithmetic operators Comparison operators Logical operators Operators used to negate conditions SQL Arithmetic Operators SQL Arithmetic Operators are used to perform mathematical operations on the numerical values. SQL provides following operators to perform mathematical operations. Here is a list of all the arithmetic operators available in SQL. Operator Description Example + Addition 10 + 20 = 30 – Subtraction 20 – 30 = -10 * Multiplication 10 * 20 = 200 / Division 20 / 10 = 2 % Modulus 5 % 2 = 1 SQL Comparison Operators SQL Comparison Operators test whether two given expressions are the same or not. These operators are used in SQL conditional statements while comparing one expression with another and they return a Boolean value which can be either TRUE or FALSE. The result of an SQL comparison operation can be UNKNOWN when one or another operand has it”s value as NULL. Here is a list of all the comparison operators available in SQL. Operator Description Example = Equal to 5 = 5 returns TRUE != Not equal 5 != 6 returns TRUE <> Not equal 5 <> 4 returns TRUE > Greater than 4 > 5 returns FALSE < Less than 4 < 5 returns TRUE >= Greater than or equal to 4 >= 5 returns FALSE <= Less than or equal to 4 <= 5 returns TRUE !< Not less than 4 !< 5 returns FALSE !> Not greater than 4 !> 5 returns TRUE SQL Logical Operators SQL Logical Operators are very similar to comparison operators and they test for the truth of some given condition. These operators return a Boolean value which can be either a TRUE or FALSE. The result of an SQL logical operation can be UNKNOWN when one or another operand has it”s value as NULL. Here is a list of all the logical operators available in SQL. Operator Description Example ALL TRUE if all of a set of comparisons are TRUE. Example AND TRUE if all the conditions separated by AND are TRUE. Example ANY TRUE if any one of a set of comparisons are TRUE. Example BETWEEN TRUE if the operand lies within the range of comparisons. Example EXISTS TRUE if the subquery returns one or more records Example IN TRUE if the operand is equal to one of a list of expressions. Example LIKE TRUE if the operand matches a pattern specially with wildcard. Example NOT Reverses the value of any other Boolean operator. Example OR TRUE if any of the conditions separated by OR is TRUE Example IS NULL TRUE if the expression value is NULL. Example SOME TRUE if some of a set of comparisons are TRUE. Example UNIQUE The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates). Example SQL Operator Precedence The operator precedence in SQL is the sequence in which the SQL evaluates the different operators in a given expression. The operators with higher precedence get evaluated first. Following table lists all SQL operators as per their precedence. The operators with the highest precedence are at the top and the operators with the lowest precedence are at the bottom. Operator Operation +, – identity, negation *, / multiplication, division +, – addition, subtraction =, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN Comparison NOT logical negation AND conjunction OR inclusion Example Consider the following SQL statement: SELECT 20 – 3 * 5; This will result in the following. Here multiplication operator gets evaluated first and then subtraction happens. 5 Print Page Previous Next Advertisements ”;

SQL – Datatype Functions ”; Previous Next The Data Type functions in SQL are a scalar functions that returns information about various data type values. Following are the SQL date type functions − Sr.No. Function & Description 1 DATELENGTH() Returns the number of bytes used to represent any expression and also counts the leading and trailing spaces of the expression. 2 IDENT_CURRENT() Returns the last identity value generated for a specified table or view on an identity column. 3 IDENT_INCR() Returns the increment value of the identity column. 4 IDENT_SEED() Returns the original seed value. 5 SQL_VARIANT_PROPERTY() Returns the base data types and other information about a sql_variant value. Print Page Previous Next Advertisements ”;

SQL – Useful Resources ”; Previous Next The following resources contain additional information on SQL. Please use them to get more in-depth knowledge on this topic. PostgreSQL, PGadmin, and SQL Online Training with Mini Project 36 Lectures 5 hours Karthikeya T More Detail SQL Programming Training Course 15 Lectures 1.5 hours SUPRIO DUTTA More Detail SQL Básico Para Engenheiro De Dados 40 Lectures 3 hours Vinicius Vale More Detail Print Page Previous Next Advertisements ”;

SQL – Clustered Index Table of content SQL Clustered Indexes Creating Clustered Index on Multiple Columns ”; Previous Next An index in a database is a data structure that helps to improve the speed of retrieving specific data from tables and views. Data in a table is stored in the form of an unordered data structure called a “Heap”, where rows are placed without any specific order. Thus, when retrieving data from a table, the query optimizer must scan the entire table to locate the requested rows. This process can be time-consuming, especially when we are dealing with large tables. To speed up the data retrieval, SQL provides a data object called index that stores and organizes table data in a specific way, allowing faster data access. SQL Clustered Indexes A clustered index in SQL is a type of index that determines the physical order in which the data values will be stored in a table. When a clustered index is defined on a specific column, during the creation of a new table, the data is inserted into that column in a sorted order. This helps in faster retrieval of data since it is stored in a specific order. It is recommended to have only one clustered index on a table. If we create multiple clustered indexes on the same table, the table will have to store the same data in multiple orders which is not possible. When we try to create a primary key constraint on a table, a unique clustered index is automatically created on the table. However, the clustered index is not the same as a primary key. A primary key is a constraint that imposes uniqueness on a column or set of columns, while a clustered index determines the physical order of the data in the table. MySQL database does not have a separate provisions for Clustered and Non-Clustered indexes. Clustered indexes are automatically created when PRIMARY KEY is defined on a table. And when the PRIMARY KEY is not defined, the first UNIQUE NOT NULL key is treated as a Clustered index. Syntax Following is the syntax to create a clustered index with SQL Server − CREATE INDEX index_name ON table_name(column_name [asc|desc]) Where, index_name: specifies the name you want to give to the index being created. column_name: specifies the column(s) that will be indexed in the order specified. asc|desc: specifies the order (asc – ascending, desc – descending) in which the data should be sorted. The default sorting order is ascending order. Example In this example, let us create a clustered index on a table in SQL Server. For that, we need to first 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) ); Now, insert some values into the CUSTOMERS 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 Server 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 clustered index on the column named ID using the following query − CREATE CLUSTERED INDEX CLU_ID ON CUSTOMERS(ID ASC); Output On executing the above query, the output is displayed as follows − Commands Completed Successfully. Verification To verify if the clustered index is defined on ID column, check whether the records of CUSTOMERS table are sorted by retrieving them using the following query − SELECT * FROM CUSTOMERS; The records of the table are sorted in ascending order based on values in the column named ID. ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2500.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 9000.00 7 Muffy 24 Indore 5500.00 Creating Clustered Index on Multiple Columns With the following example, let us understand how clustered index works when it is created on multiple columns of a table. Instead of creating a new table, consider the previously created CUSTOMERS table and define a clustered index on multiple columns of this table, such as AGE and SALARY, using the following query − CREATE CLUSTERED INDEX MUL_CLUS_ID ON CUSTOMERS (AGE, SALARY ASC); Output When we execute the above query, the output is obtained as follows − Commands Completed Successfully. Verification Now, let us verify whether the values in the columns AGE and SALARY is sorted or not − SELECT * FROM CUSTOMERS; As we can observe in the table below, the records are sorted only based on the values in AGE column and not with the values in SALARY column. So, it is recommended to have only one clustered index on a table. ID NAME AGE ADDRESS SALARY 6 Komal 22 Hyderabad 9000.00 3 Kaushik 23 Kota 2500.00 7 Muffy 24 Indore 5500.00 2 Khilan 25 Delhi 1500.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 1 Ramesh 32 Ahmedabad 2000.00 Print Page Previous Next Advertisements ”;

SQL – Date & Time Table of content Date & Time Datatypes in SQL Date & Time Functions in SQL ”; Previous Next SQL provides multiple datatypes and functions to handle Date and Time values in a database. This is because Date and Time values are represented in various formats. For instance, there are two common ways to represent a date value: DD/MM/YYYY and MM/DD/YYYY. Similarly, there is more than a single way to represent time values. For a database to recognize such data given in any format, we make use of multiple datatypes and functions. The only tricky part about storing the Date and Time data in a database is making sure that the values are inserted in the tables with the same format as the datatype. Different database systems use different datatypes and functions to store and handle the Date and Time data. Date & Time Datatypes in SQL Date and time datatypes are used in SQL to store date and time values in various formats. The datatypes available in SQL are listed below. S.No. Datatype & Description Storage 1 datetime It stores date and time both from January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds; with the format: YYYY-MM-DD HH:MI:SS. 8 bytes 2 datetime2 It stores date and time both from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds. 6 – 8 bytes 3 smalldatetime It stores date and time both from January 1, 1900 to June 6, 2079 with an accuracy of 1 minute. It is stored in the format of YYYY-MM-DD HH:MI:SS. 4 bytes 4 date It stores date only from January 1, 0001 to December 31 9999, in the format: YYYY-MM-DD. 3 bytes 5 time It store time only to an accuracy of 100 nanoseconds. 3 – 5 bytes 6 datetimeoffset It is the same of the datetime2 with the addition of the time zone offset. 8 – 10 bytes 7 timestamp It stores the unique number that gets updated every time a row gets created or modified. It does not correspond to real time and is based on internal time. Each table may have only one timestamp variable. Example In the following example, let us create a table named SALES_DETAILS which accepts only date and time values in different formats. CREATE TABLE SALES_DETAILS( orderDate DATE, shippingDate DATETIME, deliveredDate TIMESTAMP, time TIME ); To insert values into this table, use the following query − INSERT INTO SALES_DETAILS VALUES (”2023-02-01”, ”2023-02-01 :10:00”,”2023-02-03 :18:00”, ”18:00”); Output The table will be created as follows − orderDate shippingDate deliveredDate time 2023-02-01 2023-02-01 :10:00 2023-02-03 :18:00 18:00 Date & Time Functions in SQL SQL also provides multiple functions to handle date and time values. For instance, there are different functions to retrieve the current timestamp in different formats. Let us see some of such functions below − CURDATE() Function To get the current date, we use the CURDATE() function in MySQL. The format of the resultant date will be ”YYYY-MM-DD” (string) or YYYYMMMDD (numeric). SELECT CURDATE(); Output When we execute the above query, we get the current days date − CURDATE() 2023-08-22 NOW() Function The MySQL NOW() function will retrieve the current date and time value as a timestamp based on the context and, the value returned will be in either of the two formats: ”YYYY-MM-DD hh:mm:ss” and ”YYYYMMDDhhmmss”. SELECT NOW(); Output When we execute the above SQL query, we get the current date with time as follow − NOW() 2023-08-22 15:30:25 CURRENT_TIMESTAMP() Function The MySQL CURRENT_TIMESTAMP() function is used to get the current timestamp. The value returned will be in ”YYYY-MM-DD hh:mm:ss” (string) or YYYYMMDDhhmmss (numeric) format. This function is a synonym for NOW(). SELECT CURRENT_TIMESTAMP(); Output When we run the above SQL query, we get the following output − CURRENT_TIMESTAMP() 2023-08-22 15:31:32 Print Page Previous Next Advertisements ”;

SQL – Quick Guide ”; Previous Next SQL – Overview SQL is a language to operate databases; it includes database creation, deletion, fetching rows, modifying rows, etc. SQL is an ANSI (American National Standards Institute) standard language, but there are many different versions of the SQL language. What is SQL? SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database. SQL is the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language. Also, they are using different dialects, such as − MS SQL Server using T-SQL, Oracle using PL/SQL, MS Access version of SQL is called JET SQL (native format) etc. Why SQL? SQL is widely popular because it offers the following advantages − Allows users to access data in the relational database management systems. Allows users to describe the data. Allows users to define the data in a database and manipulate that data. Allows to embed within other languages using SQL modules, libraries & pre-compilers. Allows users to create and drop databases and tables. Allows users to create view, stored procedure, functions in a database. Allows users to set permissions on tables, procedures and views. A Brief History of SQL 1970 − Dr. Edgar F. “Ted” Codd of IBM is known as the father of relational databases. He described a relational model for databases. 1974 − Structured Query Language appeared. 1978 − IBM worked to develop Codd”s ideas and released a product named System/R. 1986 − IBM developed the first prototype of relational database and standardized by ANSI. The first relational database was released by Relational Software which later came to be known as Oracle. SQL Process When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your request and SQL engine figures out how to interpret the task. There are various components included in this process. These components are − Query Dispatcher Optimization Engines Classic Query Engine SQL Query Engine, etc. A classic query engine handles all the non-SQL queries, but a SQL query engine won”t handle logical files. Following is a simple diagram showing the SQL Architecture − SQL Commands The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into the following groups based on their nature − DDL – Data Definition Language Sr.No. Command & Description 1 CREATE Creates a new table, a view of a table, or other object in the database. 2 ALTER Modifies an existing database object, such as a table. 3 DROP Deletes an entire table, a view of a table or other objects in the database. DML – Data Manipulation Language Sr.No. Command & Description 1 SELECT Retrieves certain records from one or more tables. 2 INSERT Creates a record. 3 UPDATE Modifies records. 4 DELETE Deletes records. DCL – Data Control Language Sr.No. Command & Description 1 GRANT Gives a privilege to user. 2 REVOKE Takes back privileges granted from user. SQL – RDBMS Concepts What is RDBMS? RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. What is a table? The data in an RDBMS is stored in database objects which are called as tables. This table is basically a collection of related data entries and it consists of numerous columns and rows. Remember, a table is the most common and simplest form of data storage in a relational database. The following program is an example of a CUSTOMERS table − +—-+———-+—–+———–+———-+ | 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 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +—-+———-+—–+———–+———-+ What is a field? Every table is broken up into smaller entities called fields. The fields in the CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY. A field is a column in a table that is designed to maintain specific information about every record in the table. What is a Record or a Row? A record is also called as a row of data is each individual entry that exists in a table. For example, there are 7 records in the above CUSTOMERS table. Following is a single row of data or record in the CUSTOMERS table − +—-+———-+—–+———–+———-+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | +—-+———-+—–+———–+———-+ A record is a horizontal entity in a table. What is a column? A column is a vertical entity in a table that contains all information associated with a specific field in a table. For example, a column in the CUSTOMERS table is ADDRESS, which represents location description and would be as shown below − +———–+ | ADDRESS | +———–+ | Ahmedabad | | Delhi | | Kota | | Mumbai | | Bhopal | | MP | | Indore | +—-+——+ What is a NULL value? A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains

SQL – JSON Functions ”; Previous Next JSON (JavaScript Object Notation) is a simple format for exchanging data. It is self-descriptive, understandable in any language, and irrespective of language. It serves as an XML substitute. JSON is currently a popular data transfer standard. The data that returned by modern services is JSON text. SQL Server JSON is the need for data developers to return JSON in SQL. The built-in JSON support in the SQL server is distinct from the native JSON type. List of Functions Following is a list of scalar functions that returns information about cursors in the SQL server − Sr.No. Function & Description 1 ISJSON Is used check a string”s validity for JSON (JavaScript Object Notation) syntax. 2 JSON_OBJECT Is used create JSON objects from the results of evaluating the SQL expressions of the arguments. 3 JSON_ARRAY It accepts an expression or a comma-separated list of expressions and returns a JSON array containing those values. 4 JSON_VALUE It accepts a JSON string and returns a scalar value. 5 JSON_QUERY Is used to extract data from a JSON object. 6 JSON_MODIFY Is used to change JSON data kept in a column of a SQL Server table. 7 JSON_PATH_EXISTS Is used to determine whether a particular JSON route is present within a JSON string. Print Page Previous Next Advertisements ”;