Category: sql

SQL – NOT Operator Table of content The SQL NOT Operator SQL NOT Operator with LIKE SQL NOT Operator with IN SQL NOT Operator with IS NULL SQL NOT Operator with BETWEEN SQL NOT Operator with EXISTS ”; Previous Next Most of the times, there is a need to use two or more conditions to filter required records from a table; but sometimes satisfying one of the conditions would be enough. There are also scenarios when you need to retrieve records that do not satisfy the conditions specified. SQL provides logical connectives for this purpose. They are listed below − AND − Operator OR − Operator NOT − Operator With the help of these logical connectives, one can retrieve records that are required and also create exceptions for the records that are not needed to be retrieved. The SQL NOT Operator SQL NOT is a logical operator/connective used to negate a condition or Boolean expression in a WHERE clause. That is, TRUE becomes FALSE and vice versa. The most common scenario where this operator can be used occurs when there is a specification of what NOT to include in the result table, instead of what to include. For instance, in an Indian voting system, people younger than 18 years of age are NOT allowed to vote. Therefore, while retrieving the information of all people who are eligible to vote, using the NOT operator, we can create an exception to minors since it is the only specification. The NOT operator is always used in a WHERE clause so its scope within the clause is not always clear. Hence, a safer option to exactly execute the query is by enclosing the Boolean expression or a subquery by parentheses. Syntax Following is the syntax for SQL NOT operator − NOT [CONDITION or BOOLEAN EXPRESSION]; Example In the following example, let us first create a table to demonstrate the usage of NOT operator. Using the query below, we are creating a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc. − 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 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 SQL query below retrieves all rows from the ”CUSTOMERS” table where the ”SALARY” column is not greater than 2000.00 − SELECT * FROM CUSTOMERS WHERE NOT (SALARY > 2000.00); Output Following is the output of the above query − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 SQL NOT Operator with LIKE The LIKE operator uses wildcards to perform pattern matching on the records of a table before extracting the matched records. However, to negate this operation (to extract the unmatched records instead), we can use the NOT operator along with LIKE in the form of NOT LIKE keyword. Example Using the following query, we are retrieving all rows from the ”CUSTOMERS” table where the ”NAME” column does not start with the letter ”K” − SELECT * FROM CUSTOMERS WHERE NAME NOT LIKE ”K%”; Output On executing the query above, the table will be displayed as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 7 Muffy 24 Indore 10000.00 SQL NOT Operator with IN The IN operator returns TRUE if the values in a table column belong to a range of numbers specified in the WHERE clause. To negate this operation, we can use the NOT IN operator instead. With this, the Boolean expression returns TRUE if the records are not present in the given range. Example The following SQL query selects all rows from the ”CUSTOMERS” table where the ”AGE” column does not have values 25, 26, or 32 − SELECT * FROM CUSTOMERS WHERE AGE NOT IN (25, 26, 32); Output The result table is displayed as follows − ID NAME AGE ADDRESS SALARY 3 Kaushik 23 Kota 2000.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 SQL NOT Operator with IS NULL The IS NULL operator is used to check whether the records in a table are NULL. If a NULL value is encountered, it returns TRUE; and FALSE otherwise. Using NOT operator with the IS NULL operator, we can extract all the records that does not contain NULL values. Example This SQL query retrieves all rows from the ”CUSTOMERS” table where the ”AGE” column is not null, i.e. it contains valid age values − SELECT * FROM CUSTOMERS WHERE AGE IS NOT NULL; Output The result table is exactly as the original table as it contains no NULL values − 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 However, if the table contains any NULL values, the rows containing it will be omitted in the resultant table. SQL NOT Operator with BETWEEN BETWEEN operator is used to establish a range as a condition. When used with WHERE clause, this operator acts like a Boolean expression. That is, if values of a table column fall in

SQL – TRUNCATE TABLE Table of content The SQL TRUNCATE TABLE Statement TRUNCATE vs DELETE TRUNCATE vs DROP ”; Previous Next SQL provides command to TRUNCATE a table completely in one go instead of deleting table records one by one which will be very time consuming and cumbersome process. The SQL TRUNCATE TABLE Statement The SQL TRUNCATE TABLE command is used to empty a table. This command is a sequence of DROP TABLE and CREATE TABLE statements and requires the DROP privilege. You can also use DROP TABLE command to delete a table but it will remove the complete table structure from the database and you would need to re-create this table once again if you wish you store some data again. Syntax The basic syntax of a TRUNCATE TABLE command is as follows. TRUNCATE TABLE table_name; Example First let”s create a table CUSTOMERS which can store the personal details of customers including their name, age, address and salary etc. 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 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 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 SQL TRUNCATE TABLE CUSTOMER statement will remove all the records of the CUSTOMERS table − TRUNCATE TABLE CUSTOMERS; Verification Now, the CUSTOMERS table is truncated and the output from SELECT statement will be as shown in the code block below − SELECT * FROM CUSTOMERS; Following will be the output − Empty set (0.00 sec) TRUNCATE vs DELETE Even though the TRUNCATE and DELETE commands work similar logically, there are some major differences that exist between them. They are detailed in the table below. DELETE TRUNCATE The DELETE command in SQL removes one or more rows from a table based on the conditions specified in a WHERE Clause. SQL”s TRUNCATE command is used to remove all of the rows from a table, regardless of whether or not any conditions are met. It is a DML(Data Manipulation Language) command. It is a DDL(Data Definition Language) command. There is a need to make a manual COMMIT after making changes to the DELETE command, for the modifications to be committed. When you use the TRUNCATE command, the modifications made to the table are committed automatically. It deletes rows one at a time and applies same criteria to each deletion. It removes all of the information in one go. The WHERE clause serves as the condition in this case. The WHERE Clause is not available. All rows are locked after deletion. TRUNCATE utilizes a table lock, which locks the pages so they cannot be deleted. It makes a record of each and every transaction in the log file. The only activity recorded is the deallocation of the pages on which the data is stored. It consumes a greater amount of transaction space compared to TRUNCATE command. It takes comparatively less amount of transaction space. If there is an identity column, the table identity is not reset to the value it had when the table was created. It returns the table identity to a value it was given as a seed. It requires authorization to delete. It requires table alter permission. When it comes to large databases, it is much slower. It is much faster. TRUNCATE vs DROP Unlike TRUNCATE that resets the table structure, DROP command completely frees the table space from the memory. They are both Data Definition Language (DDL) operations as they interact with the definitions of database objects; which allows the database to automatically commit once these commands are executed with no chance to roll back. However, there are still some differences exist between these two commands, which have been summarized in the following table − DROP TRUNCATE The DROP command in SQL removes an entire table from a database including its definition, indexes, constraints, data etc. The TRUNCATE command is used to remove all of the rows from a table, regardless of whether or not any conditions are met and resets the table definition. It is a DDL(Data Definition Language) command. It is also a DDL(Data Definition Language) command. The table space is completely freed from the memory. The table still exists in the memory. All the integrity constraints are removed. The integrity constraints still exist in the table. Requires ALTER and CONTROL permissions on the table schema and table respectively, to be able to perform this command. Only requires the ALTER permissions to truncate the table. DROP command is much slower than TRUNCATE but faster than DELETE. TRUNCATE command is faster than both DROP and DELETE commands. Print Page Previous Next Advertisements ”;

SQL – DROP Database Table of content SQL DROP Database Statement SQL DROP DATABASE IF EXISTS Statement Dropping the Database that doesn”t Exist Deleting Multiple Databases ”; Previous Next The SQL DROP DATABASE statement is used to delete an existing database along with all the data such as tables, views, indexes, stored procedures, and constraints. SQL DROP Database Statement Following are the important points to remember before you delete an existing database − Make sure you have taken proper backup of the database before you delete it. Make sure no other application is connected and using this database. Make sure you have the necessary privilege to delete the database. Usually an admin can delete the databaase. Syntax Following is the syntax to delete a database in SQL − DROP DATABASE DatabaseName; Here, the DatabaseName is the name of the database that you want to delete. A database name is always unique within the RDBMS. Example First of all, let us create multiple databases into database system using the following SQL queries − CREATE DATABASE testDB1; CREATE DATABASE testDB2; CREATE DATABASE testDB3; CREATE DATABASE testDB4; Let us verify whether the databases are created or not using the following query − SHOW DATABASES; This will list down all the available databases: Database information_schema mysql performance_schema testDB1 testDB2 testDB3 testDB4 Now, let us try to delete the testDB1 database using the SQL DROP DATABASE statement − DROP DATABASE testDB1; Once we have deleted the testDB1 database, we can verify whether it is deleted or not using the SQL SHOW DATABASES statement − SHOW DATABASES; This will list down all the available databases: Database information_schema mysql performance_schema testDB2 testDB3 testDB4 That”s it! we have successfully deleted a database in SQL. SQL DROP DATABASE IF EXISTS Statement The SQL DROP DATABASE IF EXISTS statement includes a condition to check whether the database exists before trying to delete it. If the database does not exist in the database system, the “DROP DATABASE IF EXISTS” statement does not raise an error, but it simply terminates without taking any action. Syntax Following is the syntax of the DROP DATABASE IF EXISTS statement in SQL − DROP DATABASE IF EXISTS DatabaseName; Here, the DatabaseName is the name of the database that you want to delete. Example Let us try to delete an existing database testDB2 in the database system using the following SQL statement − DROP DATABASE IF EXISTS testDB2; When we execute the above SQL statement, the output is obtained as follows − Query OK, 0 rows affected, 3 warnings (0.024 sec) Dropping the Database that doesn”t Exist Let us try to drop a database testDB2 that doesn”t exist in the database system using the following SQL statement − DROP DATABASE IF EXISTS testDB2; When we execute the above SQL statement, the output is obtained as follows − Query OK, 0 rows affected, 1 warning (0.000 sec) Deleting Multiple Databases You can drop multiple databases using the SQL DROP DATABASE statement as follows: DROP DATABASE testDB3, testDB4; Print Page Previous Next Advertisements ”;

SQL – RDBMS Concepts Table of content What is RDBMS? What is a Table? What is a Field? What is a Record or a Row? What is a Column? What is a NULL Value? SQL Constraints Data Integrity Database Normalization ”; Previous Next 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 in 1970. What is a Table? The data in an RDBMS is stored in database objects known 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. Following is an example of a CUSTOMERS table which stores customer”s ID, Name, Age, Salary, City and Country − ID Name Age Salary City Country 1 Ramesh 32 2000.00 Hyderabad India 2 Mukesh 40 5000.00 New York USA 3 Sumit 45 4500.00 Muscat Oman 4 Kaushik 25 2500.00 Kolkata India 5 Hardik 29 3500.00 Bhopal India 6 Komal 38 3500.00 Saharanpur India 7 Ayush 25 3500.00 Delhi India 8 Javed 29 3700.00 Delhi India What is a Field? Every table is broken up into smaller entities called fields. A field is a column in a table that is designed to maintain specific information about every record in the table. For example, our CUSTOMERS table consists of different fields like ID, Name, Age, Salary, City and Country. 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 − ID Name Age Salary City Country 1 Ramesh 32 2000.00 Hyderabad India 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, our CUSTOMERS table have different columns to represent ID, Name, Age, Salary, City and Country. 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 spaces. A field with a NULL value is the one that has been left blank during a record creation. Following table has three records where first record has NULL value for the salary and second record has a zero value for the salary. ID Name Age Salary City Country 1 Ramesh 32 Hyderabad India 2 Mukesh 40 00.00 New York USA 3 Sumit 45 4500.00 Muscat Oman SQL Constraints Constraints are the rules enforced on data columns on a table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Constraints can either be column level or table level. Column level constraints are applied only to one column whereas, table level constraints are applied to the entire table. Following are some of the most commonly used constraints available in SQL − S.No. Constraints 1 NOT NULL Constraint Ensures that a column cannot have a NULL value. 2 DEFAULT Constraint Provides a default value for a column when none is specified. 3 UNIQUE Key Ensures that all the values in a column are different. 4 PRIMARY Key Uniquely identifies each row/record in a database table. 5 FOREIGN Key Uniquely identifies a row/record in any another database table. 6 CHECK Constraint Ensures that all values in a column satisfy certain conditions. 7 INDEX Constraint Used to create and retrieve data from the database very quickly. Data Integrity The following categories of data integrity exist with each RDBMS − Entity Integrity − This ensures that there are no duplicate rows in a table. Domain Integrity − Enforces valid entries for a given column by restricting the type, the format, or the range of values. Referential integrity − Rows cannot be deleted, which are used by other records. User-Defined Integrity − Enforces some specific business rules that do not fall into entity, domain or referential integrity. Database Normalization Database normalization is the process of efficiently organizing data in a database. There are two reasons of this normalization process − Eliminating redundant data, for example, storing the same data in more than one table. Ensuring data dependencies make sense. Both these reasons are worthy goals as they reduce the amount of space a database consumes and ensures that data is logically stored. Normalization consists of a series of guidelines that help guide you in creating a good database structure. Normalization guidelines are divided into normal forms; think of a form as the format or the way a database structure is laid out. The aim of normal forms is to organize the database structure, so that it complies with the rules of first normal form, then second normal form and finally the third normal form. It is your choice to take it further and go to the Fourth Normal Form, Fifth Normal Form and so on, but in general, the Third Normal Form is more than enough for a normal Database Application. First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF) Print Page Previous Next Advertisements ”;

SQL Databases Table of content SQL Databases SQL Database Table Structure Types of SQL Databases MySQL MS SQL Server ORACLE MS ACCESS PostgreSQL SQLite Benefits of Using SQL Database ”; Previous Next SQL Databases SQL or Relational databases are used to store and manage the data objects that are related to one another, i.e. the process of handling data in a relational database is done based on a relational model. This relational model is an approach to manage data in a structured way (using tables). A system used to manage these relational databases is known as Relational Database Management System (RDBMS). SQL Database Table Structure SQL database server stores data in table form. Tables are database objects used to collect data in Row and Column format. Rows represent the entities whereas columns define the attributes of each entity in a table. Columns: Columns are vertical elements in a table. Each column in a table holds specific attribute information, and column properties such as column name and data types (Integer, Char, String, etc). Rows: Rows are horizontal elements in a table and users can add data or retrieve by executing SQL queries. Types of SQL Databases There are many popular RDBMS available to work with. Some of the most popular RDBMS are listed below − MySQL MS SQL Server ORACLE MS ACCESS PostgreSQL SQLite This SQL databases tutorial gives a brief overview of these RDBMS specified above. This would help you to compare their basic features. MySQL MySQL is an open source SQL database, which is developed by a Swedish company, MySQL AB. MySQL is pronounced as “my ess-que-ell,” in contrast with SQL, pronounced “sequel.” MySQL is supporting many different platforms including Microsoft Windows, the major Linux distributions, UNIX, and Mac OS X. MySQL has free and paid versions, depending on its usage (non-commercial/commercial) and features. MySQL comes with a very fast, multi-threaded, multi-user and robust SQL database server. History 1994 – Development of MySQL by Michael Widenius & David Axmark beginning in 1994. 1995 – First internal release on 23rd May 1995. 1998 – Windows Version was released on the 8th January 1998 for Windows 95 and NT. 2001 – Version 3.23 released beta from June 2000, and production release January 2001. 2003 – Version 4.0 was released on August 2002 as beta, and as production release on March 2003 (unions). 2004 – Version 4.1 was released on June 2004 as beta, and as production release on October 2004. 2005 – Version 5.0 was released on March 2005 as beta, and as a production release on October 2005. 2008 – Sun Microsystems acquired MySQL AB on the 26th February 2008, and Oracle Version 5.1 had its production release on 27th November 2008. 2010 – Oracle acquired Sun Microsystems on 27th January 2010 and general availability of version 5.5 was on 3rd December 2010. 2013 – General availability of Version 5.6 was enabled on 5th February 2013 2015 – General availability of Version 5.7 was enabled on 21st October 2015 2018 – General availability of Version 8.0 was enabled on 19th April 2018, and is the latest version of MySQL. Features High Performance. High Availability. Scalability and Flexibility Run anything. Robust Transactional Support. Web and Data Warehouse Strengths. Strong Data Protection. Comprehensive Application Development. Management Ease. Open Source Freedom and 24 x 7 Support. Lowest Total Cost of Ownership. MS SQL Server MS SQL Server is a Relational Database Management System developed by Microsoft Inc. Its primary query languages are − T-SQL ANSI SQL History 1987 – Sybase releases SQL Server for UNIX. 1988 – Microsoft, Sybase, and Aston-Tate port SQL Server to OS/2. 1989 – Microsoft, Sybase, and Aston-Tate release SQL Server 1.0 for OS/2. 1990 – SQL Server 1.1 is released with support for Windows 3.0 clients. 2000 – Microsoft releases SQL Server 2000. 2001 – Microsoft releases XML for SQL Server Web Release 1 (download). 2002 – Microsoft releases SQLXML 2.0 (renamed from XML for SQL Server). 2002 – Microsoft releases SQLXML 3.0. 2006 – Microsoft releases SQL Server 2005 on January 14, 2006. 2008 – Microsoft releases SQL Server 2008 on November 6, 2008. And R2 version is released on July 20, 2010. 2012 – Microsoft releases SQL Server 2012 on May 20, 2012. 2014 – Microsoft releases SQL Server 2014 on June 5, 2014. 2016 – Microsoft releases SQL Server 2016 on June 1, 2016. 2017 – Microsoft releases SQL Server 2017 on September 29, 2017. 2019 – Microsoft releases SQL Server 2019 on November 4, 2019. 2022 – Microsoft releases SQL Server 2022 on November 16, 2022. Features High Performance High Availability Database mirroring Database snapshots CLR integration Service Broker DDL triggers Ranking functions Row version-based isolation levels XML integration TRY…CATCH Database Mail ORACLE Oracle is a very large multi-user based database management system. Oracle is a relational database management system developed by ”Oracle Corporation”. Oracle works to efficiently manage its resources, a database of information among the multiple clients requesting and sending data in the network. It is an excellent database server choice for client/server computing. Oracle supports all major operating systems for both clients and servers, including MSDOS, NetWare, UnixWare, OS/2 and most UNIX flavors. History Oracle began in 1977 and celebrating its 46 wonderful years in the industry (from 1977 to 2023). 1977 – Larry Ellison, Bob Miner and Ed Oates founded Software Development Laboratories to undertake development work. 1979 – Version 2.0 of Oracle was released and it became first commercial relational database and first SQL database. The company changed its name to Relational Software Inc. (RSI). 1981 – RSI started developing tools for Oracle. 1982 – RSI was renamed to Oracle Corporation. 1983 – Oracle released version 3.0, rewritten in C language and ran on multiple platforms. 1984 – Oracle version 4.0 was released. It contained features like concurrency control – multi-version read consistency, etc. 1985 – Oracle version 4.0 was released. It contained features like concurrency control – multi-version read consistency, etc. 2007 – Oracle

SQL – SELECT Database, USE Statement Table of content The USE DATABASE Statement Selecting a Non Existing Database ”; Previous Next To work with a database in SQL, we need to first select the database we want to work with. After selecting the database, we can perform various operations on it such as creating tables, inserting data, updating data, and deleting data. The USE DATABASE Statement The SQL USE DATABASE statement is used to select a database from a list of databases available in the system. Once a database is selected, we can perform various operations on it such as creating tables, inserting data, updating data, and deleting data. Syntax Following is the syntax of the USE DATABASE statement in SQL − USE DatabaseName; Here, the DatabaseName is the name of the database that we want to select. The database name is always unique within the RDBMS. Example First of all we will create a database using the following SQL CREATE DATABASE query − CREATE DATABASE testDB; Now, we can list all the available databases as follws − SHOW DATABASES; The output will be displayed as − Database master performance_schema information_schema mysql testDB Example: Select/Switch Database Following query is used to select/switch the current database to testDB − USE testDB; Output Database changed Once we finish switching to the database testDB we can perform operations such as creating a table, and inserting data in that table as shown below −. CREATE TABLE CALENDAR(MONTHS DATE NOT NULL); Now, let us insert some records in the CALENDAR table using SQL INSERT statements as shown in the query below − INSERT INTO CALENDAR(MONTHS) VALUES(”2023-01-01”); INSERT INTO CALENDAR(MONTHS) VALUES(”2023-02-01”); INSERT INTO CALENDAR(MONTHS) VALUES(”2023-03-01”); INSERT INTO CALENDAR(MONTHS) VALUES(”2023-04-01”); INSERT INTO CALENDAR(MONTHS) VALUES(”2023-12-01”); Let”s verify the operation by listing all the records from CALENDAR table using SQL SELECT statement as shown below − SELECT * FROM CALENDAR; Output The output will be displayed as − MONTHS 2023-01-01 2023-02-01 2023-03-01 2023-04-01 2023-12-01 Selecting a Non Existing Database An attempt to select a non-existent database will result in an error. In the following query we are trying to switch to the database which does not exist − Example USE unknownDatabase; Output On executing the above query, the output will be displayed as − ERROR 1049 (42000): Unknown database ”unknownDatabase” Print Page Previous Next Advertisements ”;

SQL – Data Types Table of content What are SQL Data Types? Defining a Data Type Types of SQL Data Types Data Types in MySQL, SQL Server, Oracle and MS Access Databases MySQL Data Types MS SQL Server Data Types Oracle Data Types MS Access Data Types ”; Previous Next What are SQL Data types? An SQL data type refers to the type of data which can be stored in a column of a database table. In a column, the user can store numeric, string, binary, etc by defining data types. For example integer data, character data, monetary data, date and time data, binary strings, and so on. While creating a database table in a database, we need to specify following two attributes to define a table column: Name of the column Data type of the column A database table”s column defines the data, whereas database table rows populate data into the table. For example, if you want to store student name in a column then you should give column name something like student_name and it”s data type will be char(50) which means it can store a string of characters up to 50 characters. The data type provide guidelines for SQL to understand what type of data is expected inside each column, and hence, prevents the user from entering any unexpected or invalid data in a column. For example, if we want a column to store only integer values, we can specify its data types as INT. SQL will show an error if any other value apart from an integer is inserted into that particular column. Different RDBMS supports different type of data types to define their tables. This SQL Data types tutorial will list down different data types available in MySQL, Oracle, MS SQL Server, and MS Access databases. Defining a Data Type SQL Data types are defined during the creation of a table in a database. While creating a table, it is required to specify its respective data type and size along with the name of the column. Following is the syntax to specify a data type in MySQL − CREATE TABLE table_name(column1 datatype, column2 datatype….) Let us look at an example query below to understand better. CREATE TABLE Customers (Name VARCHAR (25), Age INT); In the above SQL query, we are creating a table Customers. And since the Name column only stores string values, we are specifying its data type as “VARCHAR”. The VARCHAR data type represents string values in SQL. Similarly, we define the Age column with the integer data type, “INT”. When we assign the appropriate data type to a column, we can make efficient use of memory by allocating only the amount of system memory required for the data in the relevant column. Types of SQL Data Types There are three main types of SQL data types available in any RDBMS. They are listed below − String Numeric Date and Time Data Types in MySQL, SQL Server, Oracle, and MS Access Databases Let”s discuss the data types in MySQL, SQL Server, Oracle (PL/SQL) and MS Access Databases in detail. All the three main types (String, Numeric, Date and Time) of SQL data types explained below- MySQL Data Types There are three main data types available in MySQL Database: string, numeric, and date and time. Following section lists down all the data types available in MySQL Version 8.0 MySQL – String Data Types Data type Description CHAR(size) A fixed length string which can have letters, numbers, and special characters. The size parameter specifies the column length in characters which can vary from from 0 to 255. Default size is 1 VARCHAR(size) A variable length string which can contain letters, numbers, and special characters. The size parameter specifies the maximum string length in characters which can vary from 0 to 65535. BINARY(size) This is equal to CHAR(), but stores binary byte strings. The size parameter specifies the column length in bytes. Default size is 1 VARBINARY(size) This is equal to VARCHAR(), but stores binary byte strings. The size parameter specifies the maximum column length in bytes. TINYTEXT This holds a string with a maximum length of 255 characters TEXT(size) This holds a string with a maximum length of 65,535 bytes LONGTEXT This holds a string with a maximum length of 4,294,967,295 characters TINYBLOB This represents a small BLOBs (Binary Large Objects). Max length is 255 bytes BLOB(size) This represents a BLOBs (Binary Large Objects). Holds up to 65,535 bytes of data MEDIUMTEXT This holds a string with a maximum length of 16,777,215 characters MEDIUMBLOB This represents a medium BLOBs (Binary Large Objects). Holds up to 16,777,215 bytes of data LONGBLOB This represents a large BLOBs (Binary Large Objects). Holds up to 4,294,967,295 bytes of data ENUM(val1, val2, val3, …) A string object that can contain only one value, chosen from a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted. The values are sorted in the order you enter them SET(val1, val2, val3, …) A string object that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values in a SET list MySQL – Numeric Data Types Data type Description INT A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from -2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. You can specify a width of up to 11 digits. TINYINT A very small integer that can be signed or unsigned. If signed, the allowable range is from -128 to 127. If unsigned, the allowable range is from 0 to 255. You can specify a width of up to 4 digits. SMALLINT A small integer that can be signed or unsigned. If signed, the allowable range is from -32768 to 32767. If unsigned, the allowable range is from 0

SQL – Overview Table of content What is SQL? Why SQL? A Brief History of SQL How SQL Works? ”; Previous Next What is SQL? SQL (Structured Query Language) is a language to operate databases; it includes Database Creation, Database Deletion, Fetching Data Rows, Modifying & Deleting Data rows, etc. SQL stands for Structured Query Language which is a computer language for storing, manipulating and retrieving data stored in a relational database. SQL was developed in the 1970s by IBM Computer Scientists and became a standard of the American National Standards Institute (ANSI) in 1986, and the International Organization for Standardization (ISO) in 1987. Though SQL is an ANSI (American National Standards Institute) standard language, but there are many different dialects of the SQL language like MS SQL Server is using T-SQL and Oracle is using PL/SQL. SQL is the standard language to communicate with Relational Database Systems. 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. 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 (SQL) 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. 1987 − SQL became the part of the International Organization for Standardization (ISO). How SQL Works? 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 − Print Page Previous Next Advertisements ”;

SQL Tutorial Table of content SQL Tutorial SQL Examples Online SQL Editor SQL Basic Commands Why to Learn SQL? SQL Applications Who Should Learn SQL Prerequisites to Learn SQL SQL Online Quizzes SQL Jobs and Opportunities Frequently Asked Questions about SQL PDF Version Quick Guide Resources Job Search Discussion SQL Tutorial Our SQL tutorial helps you learn SQL (Structured Query Language) in simple and easy steps so that you can start your database programming quickly. It covers most of the important concepts related to SQL for a basic to advanced understanding of SQL and to get a feel of how SQL works. SQL (Structured Query Language) is a programming language which is used to manage data stored in relational databases like MySQL, MS Access, SQL Server, Oracle, Sybase, Informix, Postgres etc. SQL is a database computer language designed for the retrieval and management of data in a relational databases like MySQL, MS Access, SQL Server, Oracle, Sybase, Informix, Postgres etc. SQL stands for Structured Query Language. SQL was developed in the 1970s by IBM Computer Scientists. SQL is not a database management system, but it is a query language which is used to store and retrieve the data from a database or in simple words SQL is a language that communicates with databases. SQL Examples Consider we have following CUSTOMERS table which stores customer”s ID, Name, Age, Salary, City and Country − ID Name Age Salary City Country 1 Ramesh 32 2000.00 Maryland USA 2 Mukesh 40 5000.00 New York USA 3 Sumit 45 4500.00 Muscat Oman 4 Kaushik 25 2500.00 Kolkata India 5 Hardik 29 3500.00 Bhopal India 6 Komal 38 3500.00 Saharanpur India 7 Ayush 25 3500.00 Delhi India SQL makes it easy to manipulate this data using simple DML (Data Manipulation Language) Statements. For example, if we want to list down all the customers from USA then following will be the SQL query. SELECT * FROM CUSTOMERS WHERE country = ”USA”; This will produce the following result: ID Name Age Salary City Country 1 Ramesh 32 2000.00 Maryland USA 2 Mukesh 40 5000.00 New York USA SQL Online Editor We have provided SQL Online Editor which helps you to Edit and Execute the SQL code directly from your browser. Try to click the icon to run the following SQL code to be executed on CUSTOMERS table and print the records matching with the given condition. SELECT * FROM CUSTOMERS WHERE country = ”USA”; So now, you do not need to do a sophisticated setup to execute all the examples given in this tutorial because we are providing you Online SQL Editor, which allows you to edit your code and compile it online. You can try our Online SQL Editor. SQL Basic Commands We have a list of standard SQL commands to interact with relational databases, These commands are CREATE, SELECT, INSERT, UPDATE, DELETE, DROP and TRUNCATE and can be classified into the following groups based on their nature − Data Definition Language (DDL) A Data Definition Language (DDL) is a computer language which is used to create and modify the structure of database objects which include tables, views, schemas, and indexes etc. Command Description Demo CREATE Creates a new table, a view of a table, or other object in the database. Demo ALTER Modifies an existing database object, such as a table. Demo DROP Deletes an entire table, a view of a table or other objects in the database. Demo TRUNCATE Truncates the entire table in a go. Demo Data Manipulation Language (DML) A Data Manipulation Language (DML) is a computer programming language which is used for adding, deleting, and modifying data in a database. Command Description Demo SELECT Retrieves certain records from one or more tables. Demo INSERT Creates a record. Demo UPDATE Modifies records. Demo DELETE Deletes records. Demo Data Control Language (DCL) Data Control Language (DCL) is a computer programming language which is used to control access to data stored in a database. Command Description Demo GRANT Gives a privilege to user Demo REVOKE Takes back privileges granted from user. Demo Why to Learn SQL? SQL (Structured Query Language) is a MUST for the students and working professionals to become a great Software Engineer specially when they are working in Software Development Domain. SQL is the most common language used almost in every application software including banking, finance, education, security etc. to store and manipulate data. SQL is fairly easy to learn, so if you are starting to learn any programming language then it is very much advised that you should also learn SQL and other Database related concepts to become a complete Software Programmer. There are many good reasons which makes SQL as the first choice of any programmer − SQL is the standard language for any Relational Database System. All the Relational Data Base Management Systems (RDBMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language. Also, software industry is using different dialects of SQL, such as − MS SQL Server using T-SQL, Oracle using PL/SQL, MS Access version of SQL is called JET SQL (native format) etc. SQL Applications SQL is one of the most widely used Query Language over the databases. SQL provides following functionality to the database programmers − Execute different database queries against a database. Define the data in a database and manipulate that data. Create data in a relational database management system. Access data from the relational database management system. Create and drop databases and tables. Create and maintain database users. Create view, stored procedure, functions in a database. Set permissions on tables, procedures and views. Who Should Learn SQL This SQL tutorial will help both students as well as working professionals who want to develop applications based on some databases like banking systems, support systems, information systems, web websites, mobile apps or personal blogs etc. We recommend reading this tutorial, in the sequence listed in the left side menu. Today, SQL is an