SQL Tutorial
SQL tutorial provides basic and advanced concepts of SQL. Our SQL tutorial is designed for both beginners and professionals.
SQL (Structured Query Language) is used to perform operations on the records stored in the database, such as updating records, inserting records, deleting records, creating and modifying database tables, views, etc.
SQL is not a database system, but it is a query language.
Suppose you want to perform the queries of SQL language on the stored data in the database. You are required to install any database management system in your systems, for example, Oracle, MySQL, MongoDB, PostgreSQL, SQL Server, DB2, etc.
What is SQL?
SQL is a short-form of the structured query language, and it is pronounced as S-Q-L or sometimes as See-Quell.
This database language is mainly designed for maintaining the data in relational database management systems. It is a special tool used by data professionals for handling structured data (data which is stored in the form of tables). It is also designed for stream processing in RDSMS.
You can easily create and manipulate the database, access and modify the table rows and columns, etc. This query language became the standard of ANSI in the year of 1986 and ISO in the year of 1987.
If you want to get a job in the field of data science, then it is the most important query language to learn. Big enterprises like Facebook, Instagram, and LinkedIn, use SQL for storing the data in the back-end.
Why SQL?
Nowadays, SQL is widely used in data science and analytics. Following are the reasons which explain why it is widely used:
- The basic use of SQL for data professionals and SQL users is to insert, update, and delete the data from the relational database.
- SQL allows the data professionals and users to retrieve the data from the relational database management systems.
- It also helps them to describe the structured data.
- It allows SQL users to create, drop, and manipulate the database and its tables.
- It also helps in creating the view, stored procedure, and functions in the relational database.
- It allows you to define the data and modify that stored data in the relational database.
- It also allows SQL users to set the permissions or constraints on table columns, views, and stored procedures.
History of SQL
"A Relational Model of Data for Large Shared Data Banks" was a paper which was published by the great computer scientist "E.F. Codd" in 1970.
The IBM researchers Raymond Boyce and Donald Chamberlin originally developed the SEQUEL (Structured English Query Language) after learning from the paper given by E.F. Codd. They both developed the SQL at the San Jose Research laboratory of IBM Corporation in 1970.
At the end of the 1970s, relational software Inc. developed their own first SQL using the concepts of E.F. Codd, Raymond Boyce, and Donald Chamberlin. This SQL was totally based on RDBMS. Relational Software Inc., which is now known as Oracle Corporation, introduced the Oracle V2 in June 1979, which is the first implementation of SQL language. This Oracle V2 version operates on VAX computers.
Process of SQL
When we are executing the command of SQL on any Relational database management system, then the system automatically finds the best routine to carry out our request, and the SQL engine determines how to interpret that particular command.
Structured Query Language contains the following four components in its process:
- Query Dispatcher
- Optimization Engines
- Classic Query Engine
- SQL Query Engine, etc.
A classic query engine allows data professionals and users to maintain non-SQL queries. The architecture of SQL is shown in the following diagram:
Some SQL Commands
The SQL commands help in creating and managing the database. The most common SQL commands which are highly used are mentioned below:
- CREATE command
- UPDATE command
- DELETE command
- SELECT command
- DROP command
- INSERT command
CREATE Command
This command helps in creating the new database, new table, table view, and other objects of the database.
UPDATE Command
This command helps in updating or changing the stored data in the database.
DELETE Command
This command helps in removing or erasing the saved records from the database tables. It erases single or multiple tuples from the tables of the database.
SELECT Command
This command helps in accessing the single or multiple rows from one or multiple tables of the database. We can also use this command with the WHERE clause.
DROP Command
This command helps in deleting the entire table, table view, and other objects from the database.
INSERT Command
This command helps in inserting the data or records into the database tables. We can easily insert the records in single as well as multiple rows of the table.
SQL vs No-SQL
The following table describes the differences between the SQL and NoSQL, which are necessary to understand:
SQL | No-SQL |
---|---|
1. SQL is a relational database management system. | 1. While No-SQL is a non-relational or distributed database management system. |
2. The query language used in this database system is a structured query language. | 2. The query language used in the No-SQL database systems is a non-declarative query language. |
3. The schema of SQL databases is predefined, fixed, and static. | 3. The schema of No-SQL databases is a dynamic schema for unstructured data. |
4. These databases are vertically scalable. | 4. These databases are horizontally scalable. |
5. The database type of SQL is in the form of tables, i.e., in the form of rows and columns. | 5. The database type of No-SQL is in the form of documents, key-value, and graphs. |
6. It follows the ACID model. | 6. It follows the BASE model. |
7. Complex queries are easily managed in the SQL database. | 7. NoSQL databases cannot handle complex queries. |
8. This database is not the best choice for storing hierarchical data. | 8. While No-SQL database is a perfect option for storing hierarchical data. |
9. All SQL databases require object-relational mapping. | 9. Many No-SQL databases do not require object-relational mapping. |
10. Gauges, CircleCI, Hootsuite, etc., are the top enterprises that are using this query language. | 10. Airbnb, Uber, and Kickstarter are the top enterprises that are using this query language. |
11. SQLite, Ms-SQL, Oracle, PostgreSQL, and MySQL are examples of SQL database systems. | 11. Redis, MongoDB, Hbase, BigTable, CouchDB, and Cassandra are examples of NoSQL database systems. |
Advantages of SQL
SQL provides various advantages which make it more popular in the field of data science. It is a perfect query language which allows data professionals and users to communicate with the database. Following are the best advantages or benefits of Structured Query Language:
1. No programming needed
SQL does not require a large number of coding lines for managing the database systems. We can easily access and maintain the database by using simple SQL syntactical rules. These simple rules make the SQL user-friendly.
2. High-Speed Query Processing
A large amount of data is accessed quickly and efficiently from the database by using SQL queries. Insertion, deletion, and updation operations on data are also performed in less time.
3. Standardized Language
SQL follows the long-established standards of ISO and ANSI, which offer a uniform platform across the globe to all its users.
4. Portability
The structured query language can be easily used in desktop computers, laptops, tablets, and even smartphones. It can also be used with other applications according to the user's requirements.
5. Interactive language
We can easily learn and understand the SQL language. We can also use this language for communicating with the database because it is a simple query language. This language is also used for receiving the answers to complex queries in a few seconds.
6. More than one Data View
The SQL language also helps in making the multiple views of the database structure for the different database users.
Disadvantages of SQL
With the advantages of SQL, it also has some disadvantages, which are as follows:
1. Cost
The operation cost of some SQL versions is high. That's why some programmers cannot use the Structured Query Language.
2. Interface is Complex
Another big disadvantage is that the interface of Structured query language is difficult, which makes it difficult for SQL users to use and manage it.
3. Partial Database control
The business rules are hidden. So, the data professionals and users who are using this query language cannot have full database control.
SQL Syntax
When you want to do some operations on the data in the database, then you must have to write the query in the predefined syntax of SQL.
The syntax of the structured query language is a unique set of rules and guidelines, which is not case-sensitive. Its Syntax is defined and maintained by the ISO and ANSI standards.
Following are some most important points about the SQL syntax which are to remember:
- You can write the keywords of SQL in both uppercase and lowercase, but writing the SQL keywords in uppercase improves the readability of the SQL query.
- SQL statements or syntax are dependent on text lines. We can place a single SQL statement on one or multiple text lines.
- You can perform most of the action in a database with SQL statements.
- SQL syntax depends on relational algebra and tuple relational calculus.
SQL Statement
SQL statements tell the database what operation you want to perform on the structured data and what information you would like to access from the database.
The statements of SQL are very simple and easy to use and understand. They are like plain English but with a particular syntax.
Simple Example of SQL statement:
Each SQL statement begins with any of the SQL keywords and ends with the semicolon (;). The semicolon is used in the SQL for separating the multiple Sql statements which are going to execute in the same call. In this SQL tutorial, we will use the semicolon (;) at the end of each SQL query or statement.
Most Important SQL Commands and Statements
- Select Statement
- Update Statement
- Delete Statement
- Create Table Statement
- Alter Table Statement
- Drop Table Statement
- Create Database Statement
- Drop Database Statement
- Insert Into Statement
- Truncate Table Statement
- Describe Statement
- Distinct Clause
- Commit Statement
- Rollback Statement
- Create Index Statement
- Drop Index Statement
- Use Statement
Let's discuss each statement in short one by one with syntax and one example:
1. SELECT Statement
This SQL statement reads the data from the SQL database and shows it as the output to the database user.
Syntax of SELECT Statement:
Example of SELECT Statement:
This example shows the Emp_ID, First_Name, Last_Name, Salary, and City of those employees from the Employee_details table whose Salary is 100000. The output shows all the specified details according to the ascending alphabetical order of Last_Name.
3. UPDATE Statement
This SQL statement changes or modifies the stored data in the SQL database.
Syntax of UPDATE Statement:
Example of UPDATE Statement:
This example changes the Salary of those employees of the Employee_details table whose Emp_ID is 10 in the table.
3. DELETE Statement
This SQL statement deletes the stored data from the SQL database.
Syntax of DELETE Statement:
Example of DELETE Statement:
This example deletes the record of those employees from the Employee_details table whose First_Name is Sumit in the table.
4. CREATE TABLE Statement
This SQL statement creates the new table in the SQL database.
Syntax of CREATE TABLE Statement:
Example of CREATE TABLE Statement:
This example creates the table Employee_details with five columns or fields in the SQL database. The fields in the table are Emp_Id, First_Name, Last_Name, Salary, and City. The Emp_Id column in the table acts as a primary key, which means that the Emp_Id column cannot contain duplicate values and null values.
5. ALTER TABLE Statement
This SQL statement adds, deletes, and modifies the columns of the table in the SQL database.
Syntax of ALTER TABLE Statement:
The above SQL alter statement adds the column with its datatype in the existing database table.
The above 'SQL alter statement' renames the old column name to the new column name of the existing database table.
The above SQL alter statement deletes the column of the existing database table.
Example of ALTER TABLE Statement:
This example adds the new field whose name is Designation with size 18 in the Employee_details table of the SQL database.
6. DROP TABLE Statement
This SQL statement deletes or removes the table and the structure, views, permissions, and triggers associated with that table.
Syntax of DROP TABLE Statement:
The above syntax of the drop statement deletes specified tables completely if they exist in the database.
Example of DROP TABLE Statement:
This example drops the Employee_details table if it exists in the SQL database. This removes the complete information if available in the table.
7. CREATE DATABASE Statement
This SQL statement creates the new database in the database management system.
Syntax of CREATE DATABASE Statement:
Example of CREATE DATABASE Statement:
The above example creates the company database in the system.
8. DROP DATABASE Statement
This SQL statement deletes the existing database with all the data tables and views from the database management system.
Syntax of DROP DATABASE Statement:
Example of DROP DATABASE Statement:
The above example deletes the company database from the system.
9. INSERT INTO Statement
This SQL statement inserts the data or records in the existing table of the SQL database. This statement can easily insert single and multiple records in a single query statement.
Syntax of insert a single record:
Example of insert a single record:
This example inserts 101 in the first column, Akhil in the second column, Sharma in the third column, 40000 in the fourth column, and Bangalore in the last column of the table Employee_details.
Syntax of inserting a multiple records in a single query:
Example of inserting multiple records in a single query:
This example inserts the records of three employees in the Employee_details table in the single query statement.
10. TRUNCATE TABLE Statement
This SQL statement deletes all the stored records from the table of the SQL database.
Syntax of TRUNCATE TABLE Statement:
Example of TRUNCATE TABLE Statement:
This example deletes the record of all employees from the Employee_details table of the database.
11. DESCRIBE Statement
This SQL statement tells something about the specified table or view in the query.
Syntax of DESCRIBE Statement:
Example of DESCRIBE Statement:
This example explains the structure and other details about the Employee_details table.
12. DISTINCT Clause
This SQL statement shows the distinct values from the specified columns of the database table. This statement is used with the SELECT keyword.
Syntax of DISTINCT Clause:
Example of DISTINCT Clause:
This example shows the distinct values of the City and Salary column from the Employee_details table.
13. COMMIT Statement
This SQL statement saves the changes permanently, which are done in the transaction of the SQL database.
Syntax of COMMIT Statement:
Example of COMMIT Statement:
This example deletes the records of those employees whose Salary is 30000 and then saves the changes permanently in the database.
14. ROLLBACK Statement
This SQL statement undo the transactions and operations which are not yet saved to the SQL database.
Syntax of ROLLBACK Statement:
Example of ROLLBACK Statement:
This example deletes the records of those employees whose City is Mumbai and then undo the changes in the database.
15. CREATE INDEX Statement
This SQL statement creates the new index in the SQL database table.
Syntax of CREATE INDEX Statement:
Example of CREATE INDEX Statement:
This example creates an index idx_First_Name on the First_Name column of the Employee_details table.
16. DROP INDEX Statement
This SQL statement deletes the existing index of the SQL database table.
Syntax of DROP INDEX Statement:
Example of DROP INDEX Statement:
This example deletes the index idx_First_Name from the SQL database.
17. USE Statement
This SQL statement selects the existing SQL database. Before performing the operations on the database table, you have to select the database from the multiple existing databases.
Syntax of USE Statement:
Example of USE DATABASE Statement:
This example uses the company database.
SQL Data Types
Data types are used to represent the nature of the data that can be stored in the database table. For example, in a particular column of a table, if we want to store a string type of data then we will have to declare a string data type of this column.
Data types mainly classified into three categories for every database.
- String Data types
- Numeric Data types
- Date and time Data types
Data Types in MySQL, SQL Server and Oracle Databases
MySQL Data Types
A list of data types used in MySQL database. This is based on MySQL 8.0.
MySQL String Data Types
CHAR(Size) | It is used to specify a fixed length string that can contain numbers, letters, and special characters. Its size can be 0 to 255 characters. Default is 1. |
VARCHAR(Size) | It is used to specify a variable length string that can contain numbers, letters, and special characters. Its size can be from 0 to 65535 characters. |
BINARY(Size) | It is equal to CHAR() but stores binary byte strings. Its size parameter specifies the column length in the bytes. Default is 1. |
VARBINARY(Size) | It is equal to VARCHAR() but stores binary byte strings. Its size parameter specifies the maximum column length in bytes. |
TEXT(Size) | It holds a string that can contain a maximum length of 255 characters. |
TINYTEXT | It holds a string with a maximum length of 255 characters. |
MEDIUMTEXT | It holds a string with a maximum length of 16,777,215. |
LONGTEXT | It holds a string with a maximum length of 4,294,967,295 characters. |
ENUM(val1, val2, val3,...) | It is used when a string object having only one value, chosen from a list of possible values. It contains 65535 values in an ENUM list. If you insert a value that is not in the list, a blank value will be inserted. |
SET( val1,val2,val3,....) | It is used to specify a string that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values at one time in a SET list. |
BLOB(size) | It is used for BLOBs (Binary Large Objects). It can hold up to 65,535 bytes. |
MySQL Numeric Data Types
BIT(Size) | It is used for a bit-value type. The number of bits per value is specified in size. Its size can be 1 to 64. The default value is 1. |
INT(size) | It is used for the integer value. Its signed range varies from -2147483648 to 2147483647 and unsigned range varies from 0 to 4294967295. The size parameter specifies the max display width that is 255. |
INTEGER(size) | It is equal to INT(size). |
FLOAT(size, d) | It is used to specify a floating point number. Its size parameter specifies the total number of digits. The number of digits after the decimal point is specified by d parameter. |
FLOAT(p) | It is used to specify a floating point number. MySQL used p parameter to determine whether to use FLOAT or DOUBLE. If p is between 0 to24, the data type becomes FLOAT (). If p is from 25 to 53, the data type becomes DOUBLE(). |
DOUBLE(size, d) | It is a normal size floating point number. Its size parameter specifies the total number of digits. The number of digits after the decimal is specified by d parameter. |
DECIMAL(size, d) | It is used to specify a fixed point number. Its size parameter specifies the total number of digits. The number of digits after the decimal parameter is specified by d parameter. The maximum value for the size is 65, and the default value is 10. The maximum value for d is 30, and the default value is 0. |
DEC(size, d) | It is equal to DECIMAL(size, d). |
BOOL | It is used to specify Boolean values true and false. Zero is considered as false, and nonzero values are considered as true. |
MySQL Date and Time Data Types
DATE | It is used to specify date format YYYY-MM-DD. Its supported range is from '1000-01-01' to '9999-12-31'. |
DATETIME(fsp) | It is used to specify date and time combination. Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from '1000-01-01 00:00:00' to 9999-12-31 23:59:59'. |
TIMESTAMP(fsp) | It is used to specify the timestamp. Its value is stored as the number of seconds since the Unix epoch('1970-01-01 00:00:00' UTC). Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. |
TIME(fsp) | It is used to specify the time format. Its format is hh:mm:ss. Its supported range is from '-838:59:59' to '838:59:59' |
YEAR | It is used to specify a year in four-digit format. Values allowed in four digit format from 1901 to 2155, and 0000. |
SQL Server Data Types
SQL Server String Data Type
char(n) | It is a fixed width character string data type. Its size can be up to 8000 characters. |
varchar(n) | It is a variable width character string data type. Its size can be up to 8000 characters. |
varchar(max) | It is a variable width character string data types. Its size can be up to 1,073,741,824 characters. |
text | It is a variable width character string data type. Its size can be up to 2GB of text data. |
nchar | It is a fixed width Unicode string data type. Its size can be up to 4000 characters. |
nvarchar | It is a variable width Unicode string data type. Its size can be up to 4000 characters. |
ntext | It is a variable width Unicode string data type. Its size can be up to 2GB of text data. |
binary(n) | It is a fixed width Binary string data type. Its size can be up to 8000 bytes. |
varbinary | It is a variable width Binary string data type. Its size can be up to 8000 bytes. |
image | It is also a variable width Binary string data type. Its size can be up to 2GB. |
SQL Server Numeric Data Types
bit | It is an integer that can be 0, 1 or null. |
tinyint | It allows whole numbers from 0 to 255. |
Smallint | It allows whole numbers between -32,768 and 32,767. |
Int | It allows whole numbers between -2,147,483,648 and 2,147,483,647. |
bigint | It allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. |
float(n) | It is used to specify floating precision number data from -1.79E+308 to 1.79E+308. The n parameter indicates whether the field should hold the 4 or 8 bytes. Default value of n is 53. |
real | It is a floating precision number data from -3.40E+38 to 3.40E+38. |
money | It is used to specify monetary data from -922,337,233,685,477.5808 to 922,337,203,685,477.5807. |
SQL Server Date and Time Data Type
datetime | It is used to specify date and time combination. It supports range from January 1, 1753, to December 31, 9999 with an accuracy of 3.33 milliseconds. |
datetime2 | It is used to specify date and time combination. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds |
date | It is used to store date only. It supports range from January 1, 0001 to December 31, 9999 |
time | It stores time only to an accuracy of 100 nanoseconds |
timestamp | It stores a unique number when a new row gets created or modified. The time stamp value is based upon an internal clock and does not correspond to real time. Each table may contain only one-time stamp variable. |
SQL Server Other Data Types
Sql_variant | It is used for various data types except for text, timestamp, and ntext. It stores up to 8000 bytes of data. |
XML | It stores XML formatted data. Maximum 2GB. |
cursor | It stores a reference to a cursor used for database operations. |
table | It stores result set for later processing. |
uniqueidentifier | It stores GUID (Globally unique identifier). |
Oracle Data Types
Oracle String data types
CHAR(size) | It is used to store character data within the predefined length. It can be stored up to 2000 bytes. |
NCHAR(size) | It is used to store national character data within the predefined length. It can be stored up to 2000 bytes. |
VARCHAR2(size) | It is used to store variable string data within the predefined length. It can be stored up to 4000 byte. |
VARCHAR(SIZE) | It is the same as VARCHAR2(size). You can also use VARCHAR(size), but it is suggested to use VARCHAR2(size) |
NVARCHAR2(size) | It is used to store Unicode string data within the predefined length. We have to must specify the size of NVARCHAR2 data type. It can be stored up to 4000 bytes. |
Oracle Numeric Data Types
NUMBER(p, s) | It contains precision p and scale s. The precision p can range from 1 to 38, and the scale s can range from -84 to 127. |
FLOAT(p) | It is a subtype of the NUMBER data type. The precision p can range from 1 to 126. |
BINARY_FLOAT | It is used for binary precision( 32-bit). It requires 5 bytes, including length byte. |
BINARY_DOUBLE | It is used for double binary precision (64-bit). It requires 9 bytes, including length byte. |
Oracle Date and Time Data Types
DATE | It is used to store a valid date-time format with a fixed length. Its range varies from January 1, 4712 BC to December 31, 9999 AD. |
TIMESTAMP | It is used to store the valid date in YYYY-MM-DD with time hh:mm:ss format. |
Oracle Large Object Data Types (LOB Types)
BLOB | It is used to specify unstructured binary data. Its range goes up to 232-1 bytes or 4 GB. |
BFILE | It is used to store binary data in an external file. Its range goes up to 232-1 bytes or 4 GB. |
CLOB | It is used for single-byte character data. Its range goes up to 232-1 bytes or 4 GB. |
NCLOB | It is used to specify single byte or fixed length multibyte national character set (NCHAR) data. Its range is up to 232-1 bytes or 4 GB. |
RAW(size) | It is used to specify variable length raw binary data. Its range is up to 2000 bytes per row. Its maximum size must be specified. |
LONG RAW | It is used to specify variable length raw binary data. Its range up to 231-1 bytes or 2 GB, per row. |
SQL OperatorsEvery database administrator and user uses SQL queries for manipulating and accessing the data of database tables and views. The manipulation and retrieving of the data are performed with the help of reserved words and characters, which are used to perform arithmetic operations, logical operations, comparison operations, compound operations, etc. What is SQL Operator?The SQL reserved words and characters are called operators, which are used with a WHERE clause in a SQL query. In SQL, an operator can either be a unary or binary operator. The unary operator uses only one operand for performing the unary operation, whereas the binary operator uses two operands for performing the binary operation. Syntax of Unary SQL Operator Syntax of Unary SQL Operator Note: SQL operators are used for filtering the table's data by a specific condition in the SQL statement.What is the Precedence of SQL Operator?The precedence of SQL operators is the sequence in which the SQL evaluates the different operators in the same expression. Structured Query Language evaluates those operators first, which have high precedence. In the following table, the operators at the top have high precedence, and the operators that appear at the bottom have low precedence.
For Example, In the above SQL example, salary is assigned 5, not 85, because the * (Multiplication) Operator has higher precedence than the - (subtraction) operator, so it first gets multiplied with 3*5 and then subtracts from 20. Types of OperatorSQL operators are categorized in the following categories:
Let's discuss each operator with their types. SQL Arithmetic OperatorsThe Arithmetic Operators perform the mathematical operation on the numerical data of the SQL tables. These operators perform addition, subtraction, multiplication, and division operations on the numerical operands. Following are the various arithmetic operators performed on the SQL data:
SQL Addition Operator (+)The Addition Operator in SQL performs the addition on the numerical data of the database table. In SQL, we can easily add the numerical values of two columns of the same table by specifying both the column names as the first and second operand. We can also add the numbers to the existing numbers of the specific column. Syntax of SQL Addition Operator: Let's understand the below example which explains how to execute Addition Operator in SQL query: This example consists of an Employee_details table, which has four columns Emp_Id, Emp_Name, Emp_Salary, and Emp_Monthlybonus.
In this query, we have performed the SQL addition operation on the single column of the given table.
In this query, we have added two columns with each other of the above table. SQL Subtraction Operator (-)The Subtraction Operator in SQL performs the subtraction on the numerical data of the database table. In SQL, we can easily subtract the numerical values of two columns of the same table by specifying both the column names as the first and second operand. We can also subtract the number from the existing number of the specific table column. Syntax of SQL Subtraction Operator: Let's understand the below example which explains how to execute Subtraction Operator in SQL query: This example consists of an Employee_details table, which has four columns Emp_Id, Emp_Name, Emp_Salary, and Emp_Monthlybonus.
In this query, we have performed the SQL subtraction operation on the single column of the given table.
SQL Multiplication Operator (*)The Multiplication Operator in SQL performs the Multiplication on the numerical data of the database table. In SQL, we can easily multiply the numerical values of two columns of the same table by specifying both the column names as the first and second operand. Syntax of SQL Multiplication Operator: Let's understand the below example which explains how to execute Multiplication Operator in SQL query: This example consists of an Employee_details table, which has four columns Emp_Id, Emp_Name, Emp_Salary, and Emp_Monthlybonus.
In this query, we have performed the SQL multiplication operation on the single column of the given table.
In this query, we have multiplied the values of two columns by using the WHERE clause. SQL Division Operator (/)The Division Operator in SQL divides the operand on the left side by the operand on the right side. Syntax of SQL Division Operator: In SQL, we can also divide the numerical values of one column by another column of the same table by specifying both column names as the first and second operand. We can also perform the division operation on the stored numbers in the column of the SQL table. Let's understand the below example which explains how to execute Division Operator in SQL query: This example consists of an Employee_details table, which has three columns Emp_Id, Emp_Name, and Emp_Salary.
In this query, we have performed the SQL division operation on the single column of the given table. SQL Modulus Operator (%)The Modulus Operator in SQL provides the remainder when the operand on the left side is divided by the operand on the right side. Syntax of SQL Modulus Operator: Let's understand the below example which explains how to execute Modulus Operator in SQL query: This example consists of a Division table, which has three columns Number, First_operand, and Second_operand.
SQL Comparison OperatorsThe Comparison Operators in SQL compare two different data of SQL table and check whether they are the same, greater, and lesser. The SQL comparison operators are used with the WHERE clause in the SQL queries Following are the various comparison operators which are performed on the data stored in the SQL database tables:
SQL Equal Operator (=)This operator is highly used in SQL queries. The Equal Operator in SQL shows only data that matches the specified value in the query. This operator returns TRUE records from the database table if the value of both operands specified in the query is matched. Let's understand the below example which explains how to execute Equal Operator in SQL query: This example consists of an Employee_details table, which has three columns Emp_Id, Emp_Name, and Emp_Salary.
In this example, we used the SQL equal operator with WHERE clause for getting the records of those employees whose salary is 30000. SQL Equal Not Operator (!=)The Equal Not Operator in SQL shows only those data that do not match the query's specified value. This operator returns those records or rows from the database views and tables if the value of both operands specified in the query is not matched with each other. Let's understand the below example which explains how to execute Equal Not Operator in SQL query: This example consists of an Employee_details table, which has three columns Emp_Id, Emp_Name, and Emp_Salary.
In this example, we used the SQL equal not operator with WHERE clause for getting the records of those employees whose salary is not 45000. SQL Greater Than Operator (>)The Greater Than Operator in SQL shows only those data which are greater than the value of the right-hand operand. Let's understand the below example which explains how to execute Greater ThanOperator (>) in SQL query: This example consists of an Employee_details table, which has three columns Emp_Id, Emp_Name, and Emp_Salary.
Here, SQL greater than operator displays the records of those employees from the above table whose Employee Id is greater than 202. SQL Greater Than Equals to Operator (>=)The Greater Than Equals to Operator in SQL shows those data from the table which are greater than and equal to the value of the right-hand operand. Let's understand the below example which explains how to execute greater than equals to the operator (>=) in SQL query: This example consists of an Employee_details table, which has three columns Emp_Id, Emp_Name, and Emp_Salary.
Here,'SQL greater than equals to operator' with WHERE clause displays the rows of those employees from the table whose Employee Id is greater than and equals to 202. SQL Less Than Operator (<)The Less Than Operator in SQL shows only those data from the database tables which are less than the value of the right-side operand. This comparison operator checks that the left side operand is lesser than the right side operand. If the condition becomes true, then this operator in SQL displays the data which is less than the value of the right-side operand. Let's understand the below example which explains how to execute less than operator (<) in SQL query: This example consists of an Employee_details table, which has three columns Emp_Id, Emp_Name, and Emp_Salary.
Here,SQL less than operator with WHERE clause displays the records of those employees from the above table whose Employee Id is less than 204. SQL Less Than Equals to Operator (<=)The Less Than Equals to Operator in SQL shows those data from the table which are lesser and equal to the value of the right-side operand. This comparison operator checks that the left side operand is lesser and equal to the right side operand. Let's understand the below example which explains how to execute less than equals to the operator (<=) in SQL query: This example consists of an Employee_details table, which has three columns Emp_Id, Emp_Name, and Emp_Salary.
Here, SQL less than equals to the operator with WHERE clause displays the rows of those employees from the table whose Employee Id is less than and equals 202. SQL Logical OperatorsThe Logical Operators in SQL perform the Boolean operations, which give two results True and False. These operators provide True value if both operands match the logical condition. Following are the various logical operators which are performed on the data stored in the SQL database tables:
SQL ALL OperatorThe ALL operator in SQL compares the specified value to all the values of a column from the sub-query in the SQL database. This operator is always used with the following statement:
Syntax of ALL operator: Let's understand the below example which explains how to execute ALL logical operators in SQL query: This example consists of an Employee_details table, which has three columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.
Here, we used the SQL ALL operator with greater than the operator. SQL AND OperatorThe AND operator in SQL would show the record from the database table if all the conditions separated by the AND operator evaluated to True. It is also known as the conjunctive operator and is used with the WHERE clause. Syntax of AND operator: Let's understand the below example which explains how to execute AND logical operator in SQL query: This example consists of an Employee_details table, which has three columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.
SQL OR OperatorThe OR operator in SQL shows the record from the table if any of the conditions separated by the OR operator evaluates to True. It is also known as the conjunctive operator and is used with the WHERE clause. Syntax of OR operator: Let's understand the below example which explains how to execute OR logical operator in SQL query: This example consists of an Employee_details table, which has three columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.
Here, SQL OR operator with WHERE clause shows the record of employees whose salary is 25000 or the city is Delhi. SQL BETWEEN OperatorThe BETWEEN operator in SQL shows the record within the range mentioned in the SQL query. This operator operates on the numbers, characters, and date/time operands. If there is no value in the given range, then this operator shows NULL value. Syntax of BETWEEN operator: Let's understand the below example which explains how to execute BETWEEN logical operator in SQL query: This example consists of an Employee_details table, which has three columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.
Here, we used the SQL BETWEEN operator with the Emp_Salary field. SQL IN OperatorThe IN operator in SQL allows database users to specify two or more values in a WHERE clause. This logical operator minimizes the requirement of multiple OR conditions. This operator makes the query easier to learn and understand. This operator returns those rows whose values match with any value of the given list. Syntax of IN operator: Let's understand the below example which explains how to execute IN logical operator in SQL query: This example consists of an Employee_details table, which has three columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.
Here, we used the SQL IN operator with the Emp_Id column.
Here, we used the SQL NOT IN operator with the Emp_Id column. SQL NOT OperatorThe NOT operator in SQL shows the record from the table if the condition evaluates to false. It is always used with the WHERE clause. Syntax of NOT operator: Let's understand the below example which explains how to execute NOT logical operator in SQL query: This example consists of an Employee_details table, which has four columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.
In this example, we used the SQL NOT operator with the Emp_City column.
In this example, we used the SQL NOT operator with the Emp_City column. SQL ANY OperatorThe ANY operator in SQL shows the records when any of the values returned by the sub-query meet the condition. The ANY logical operator must match at least one record in the inner query and must be preceded by any SQL comparison operator. Syntax of ANY operator: SQL LIKE OperatorThe LIKE operator in SQL shows those records from the table which match with the given pattern specified in the sub-query. The percentage (%) sign is a wildcard which is used in conjunction with this logical operator. This operator is used in the WHERE clause with the following three statements:
Syntax of LIKE operator: Let's understand the below example which explains how to execute LIKE logical operator in SQL query: This example consists of an Employee_details table, which has four columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.
In this example, we used the SQL LIKE operator with Emp_Name column because we want to access the record of those employees whose name starts with s.
SQL Set OperatorsThe Set Operators in SQL combine a similar type of data from two or more SQL database tables. It mixes the result, which is extracted from two or more SQL queries, into a single result. Set operators combine more than one select statement in a single query and return a specific result set. Following are the various set operators which are performed on the similar data stored in the two SQL database tables:
SQL Union OperatorThe SQL Union Operator combines the result of two or more SELECT statements and provides the single output. The data type and the number of columns must be the same for each SELECT statement used with the UNION operator. This operator does not show the duplicate records in the output table. Syntax of UNION Set operator: Let's understand the below example which explains how to execute Union operator in Structured Query Language: In this example, we used two tables. Both tables have four columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.
Table: Employee_details1
Table: Employee_details2
SQL Union ALL OperatorThe SQL Union Operator is the same as the UNION operator, but the only difference is that it also shows the same record. Syntax of UNION ALL Set operator: Let's understand the below example which explains how to execute Union ALL operator in Structured Query Language: In this example, we used two tables. Both tables have four columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.
Table: Employee_details1
Table: Employee_details2
SQL Intersect OperatorThe SQL Intersect Operator shows the common record from two or more SELECT statements. The data type and the number of columns must be the same for each SELECT statement used with the INTERSECT operator. Syntax of INTERSECT Set operator: Let's understand the below example which explains how to execute INTERSECT operator in Structured Query Language: In this example, we used two tables. Both tables have four columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.
Table: Employee_details1
Table: Employee_details2 Suppose, we want to see a common record of the employee from both the tables in a single output. For this, we have to write the following query in SQL: SQL Minus OperatorThe SQL Minus Operator combines the result of two or more SELECT statements and shows only the results from the first data set. Syntax of MINUS operator: Let's understand the below example which explains how to execute INTERSECT operator in Structured Query Language: In this example, we used two tables. Both tables have four columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.
Table: Employee_details1
Table: Employee_details2 Suppose, we want to see the name of employees from the first result set after the combination of both tables. For this, we have to write the following query in SQL: SQL Unary OperatorsThe Unary Operators in SQL perform the unary operations on the single data of the SQL table, i.e., these operators operate only on one operand. These types of operators can be easily operated on the numeric data value of the SQL table. Following are the various unary operators which are performed on the numeric data stored in the SQL table:
SQL Unary Positive OperatorThe SQL Positive (+) operator makes the numeric value of the SQL table positive. Syntax of Unary Positive Operator Let's understand the below example which explains how to execute a Positive unary operator on the data of SQL table: This example consists of anEmployee_details table, which has four columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.
SQL Unary Negative OperatorThe SQL Negative (-) operator makes the numeric value of the SQL table negative. Syntax of Unary Negative Operator Let's understand the below example which explains how to execute Negative unary operator on the data of SQL table: This example consists of an Employee_details table, which has four columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.
SQL Bitwise NOT OperatorThe SQL Bitwise NOT operator provides the one's complement of the single numeric operand. This operator turns each bit of numeric value. If the bit of any numerical value is 001100, then this operator turns these bits into 110011. Syntax of Bitwise NOT Operator Let's understand the below example which explains how to execute the Bitwise NOT operator on the data of SQL table: This example consists of aStudent_details table, which has four columns Roll_No, Stu_Name, Stu_Marks, and Stu_City.
If we want to perform the Bitwise Not operator on the marks column of Student_details, we have to write the following query in SQL: SQL Bitwise OperatorsThe Bitwise Operators in SQL perform the bit operations on the Integer values. To understand the performance of Bitwise operators, you just knew the basics of Boolean algebra. Following are the two important logical operators which are performed on the data stored in the SQL database tables:
Bitwise AND (&)The Bitwise AND operator performs the logical AND operation on the given Integer values. This operator checks each bit of a value with the corresponding bit of another value. Syntax of Bitwise AND Operator Let's understand the below example which explains how to execute Bitwise AND operator on the data of SQL table: This example consists of the following table, which has two columns. Each column holds numerical values. When we use the Bitwise AND operator in SQL, then SQL converts the values of both columns in binary format, and the AND operation is performed on the converted bits. After that, SQL converts the resultant bits into user understandable format, i.e., decimal format.
Bitwise OR (|)The Bitwise OR operator performs the logical OR operation on the given Integer values. This operator checks each bit of a value with the corresponding bit of another value. Syntax of Bitwise OR Operator Let's understand the below example which explains how to execute Bitwise OR operator on the data of SQL table: This example consists of a table that has two columns. Each column holds numerical values. When we used the Bitwise OR operator in SQL, then SQL converts the values of both columns in binary format, and the OR operation is performed on the binary bits. After that, SQL converts the resultant binary bits into user understandable format, i.e., decimal format.
SQL Create DatabaseIn SQL, the 'Create Database' statement is a first step for storing the structured data in the database. The database developers and the users use this statement in SQL for creating the new database in the database systems. It creates the database with the name which has been specified in the Create Database statement. Syntax of Create Database statement in SQLIn this syntax, Database_Name specifies the name of the database which we want to create in the system. We have to type the database name in query just after the 'Create Database' keyword. Following are the most important points which are required to learn while creating a database:
Syntax of Create Database statement in MySQLThe same command is used in MySQL to create the new database for storing the structured data. Syntax of Create Database in OracleThere is no need to create the database in Oracle systems. In the Oracle database, we can directly create the database tables. Examples of Create Database statement in SQLIn this article, we took the following two examples which will help how to run and perform the Create Database query in SQL: Example 1: This example creates the Student database. To create the Student database, you have to type the following command in Structured Query Language: When this query is executed successfully, then it will show the following output: Database created successfully You can also verify that your database is created in SQL or not by using the following query: SQL does not allow developers to create the database with the existing database name. Suppose if you want to create another Student database in the same database system, then the Create Database statement will show the following error in the output: Can't create database 'Student'; database exists So, firstly you have to delete the existing database by using the Drop Statement. You can also replace the existing database with the help of Replace keyword. If you want to replace the existing Student database, then you have to type the following SQL query: Example 2: Suppose, we want to create the Employee database in the system. Firstly, we have to type the following command in Structured Query Language: When this query is executed successfully, then it will show the following output: Database created successfully You can also check that your database is created in SQL by typing the following query: We know that SQL does not allow developers to create the database with the existing database name. Suppose, we want to create another Employee database in the same database system, firstly, we have to delete the existing database using a drop statement, or we have to replace the existing Employee database with the help of the 'replace' keyword. To replace the existing Employee database with a new Employee database, we have to type the following query in SQL: SQL DROP DatabaseThe SQL Drop Database statement deletes the existing database permanently from the database system. This statement deletes all the views and tables if stored in the database, so be careful while using this query in SQL. Following are the most important points which are required to learn before removing the database from the database system:
Syntax of Drop Database Statement in SQLIn this SQL syntax, we have to specify the name of that database which we want to delete permanently from the database system. We have to write the name of the database after the DROP DATABASE keyword in every example. We can also delete multiple databases easily by using the single DROP syntax: Using this statement, we have no need to write multiple statements for deleting multiple databases. We can specify all the databases by using a comma in a single statement, as shown in the above syntax. Examples of Drop Database Statement in SQLIn this article, we took the following two examples that will help how to run and perform the Drop Database query in SQL: Example1: Suppose, we want to delete the Student database with all its data from the database system so, firstly we have to check that the Student database exists in the system or not by using the following statement: If the Student database is shown in the output, then we have to type the following query in SQL for removing the Student database: If the Student database does not exist in the database system and we run the above query in SQL, then the query will show the following output: Can't drop database 'Student'; database doesn't exist Example2: Suppose, we want to delete the College database with all its tables and views from the database system, firstly we have to check that if the College database exists in the system or not by using the following statement: If the College database is shown in the output, then you have to type the following query in SQL for removing the College database permanently: If the College database does not exist in the database system, and we run the above query in SQL, then this query will show the following output: Can't drop database 'College'; database doesn't exist SQL RENAME DatabaseIn some situations, database users and administrators want to change the name of the database for some technical reasons. So, the Rename Database statement in SQL is used to change the name of the existing database. Sometimes, the Rename Database statement is used because the developers think that the original name is not more relevant to the data of the database, or they want to give a temporary name to that database. Syntax of Rename Database in SQLSyntax of Rename Database in MySQLThis syntax is used when we want to change the name of the database in MySQL. Examples of Rename Database in SQLIn this article, we have taken the following two examples which will help you how to run and perform the Rename Database query in SQL: Example 1: Suppose we want to rename the Student Database. For this, we have to type the following query in SQL: This query will change the name of the database from Student to College. To run this query, we must ensure that the database Student exists in the current database server. If not, then it will show an error in the output. Example 2: Suppose we want to rename the Department Database. For this, we have to type the following query in SQL: This query changes the name of the database from Department to Company. To run this query, we must ensure that the database Department exists in the current database server. If not, then it will show an error in the output. SQL SELECT DatabaseSuppose database users and administrators want to perform some operations on tables, views, and indexes on the specific existing database in SQL. Firstly, they have to select the database on which they want to run the database queries. Any database user and administrator can easily select the particular database from the current database server using the USE statement in SQL. Syntax of USE statement in SQLIn this syntax, we have to define the name of the database after the USE keyword and the name of the database must be unique. Syntax of USE statement in MySQLSyntax of USE statement in OracleThere is no need to select the database in Oracle. Examples of USE statement in SQLIn this article, we have taken the following three examples which will help you how to run and perform USE statement in SQL: Example 1: Suppose, you want to work with the Hospital database. For this firstly, you have to check that if the Hospital database exists on the current database server or not by using the following query: If the Hospital database is shown in the output, then you have to execute the following query to select the Hospital database: Example 2: Suppose, you want to work with another College database in SQL. For this firstly, you have to check that the College database exists on the current database server or not by using the following query: If the College database is shown in the result, then you have to execute the following query to select the College database: Example 3: Suppose you want to work with another School database in SQL. For this firstly, you have to check that the School database exists on the current database server or not by using the following query: If the School database is shown in the result, then you have to execute the following query to select the School database: SQL TableTable is a collection of data, organized in terms of rows and columns. In DBMS term, table is known as relation and row as tuple. Note: A table has a specified number of columns, but can have any number of rows.Table is the simple form of data storage. A table is also considered as a convenient representation of relations. Let's see an example of an employee table:
In the above table, "Employee" is the table name, "EMP_NAME", "ADDRESS" and "SALARY" are the column names. The combination of data of multiple columns forms a row e.g. "Ankit", "Lucknow" and 15000 are the data of one row. SQL TABLE VariableThe SQL Table variable is used to create, modify, rename, copy and delete tables. Table variable was introduced by Microsoft. It was introduced with SQL server 2000 to be an alternative of temporary tables. It is a variable where we temporary store records and results. This is same like temp table but in the case of temp table we need to explicitly drop it. Table variables are used to store a set of records. So declaration syntax generally looks like CREATE TABLE syntax. When a transaction rolled back the data associated with table variable is not rolled back. A table variable generally uses lesser resources than a temporary variable. Table variable cannot be used as an input or an output parameter. Topics of SQL TABLE StatementSQL TABLE Variable What TABLE variable can do? SQL CREATE TABLEHow to create a table using SQL query> SQL DROP TABLEHow to drop a table? SQL DELETE TABLEHow to delete all the records of a table? How to rename a table? SQL TRUNCATE TABLEHow to truncate a table? SQL COPY TABLEHow to copy a table? SQL TEMP TABLEWhat is temporary table? What are the advantage of temporary table? SQL ALTER TABLEHow to add, modify, rename and drop column. |
No comments:
Post a Comment