SQL – Quick Guide For Beginners

SQL implies Structured Query Language. It is a domain-specific language used for retrieving the data stored in the database. SQL handles the data stored in the relational database management system (RDBMS), i.e., the data which involves relations between entities and variables. The structured query language is compatible with all major operating systems

Earlier, data manipulation was done using ISAM (Indexed Sequential Access Method) and VSAM (Virtual Storage Access Method). The structured query language has more benefits than ISAM and VSM. Using one single command in SQL, you can retrieve multiple records or data. Another advantage is it does not require to specify how to reach the data. 

What is SQL?

SQL is a language for creating, manipulating, and maintaining the database. It also helps to insert the data, delete it, or modify it. This language comprises multiple sublanguages, like data definition language (DDL), data manipulation language (DML), data query language (DQL), and data control language (DCL).

A SQL consists of a data query, data definition, data manipulation, and data access control. A data query is a command used to retrieve any data from the database. Data definition involves defining database schema, and data manipulation includes inserting, updating, and deleting data. 

In 1986, SQL became the American National Standards Institute (ANSI) standard. In the following year, it became the International Organization for Standardization (ISO) standard. In 1970, Donald D. Chamberlin and Raymond F. Boyce developed SQL at IBM.

Earlier, it was known as SEQUEL (Structured English Query Language). It was specially designed for storing and retrieving IBM’s data. There are different SQL versions released in 1989, 1992, 1996, 1999, 2003, 2006, 2008, 2011, and 2016. 

SQL is the core language for all relational database management systems, like MySQL, Oracle, Sybase, SQL Server, MS Access, Postgres, and Informix. Using this query dialect has multiple benefits. SQL enables users to access the information from any relational database management system.

Users can define the data, modify it, or delete the data using SQL. You can also embed this dialect in any other programming language, where data retrieving is required. It permits users to generate and set permissions on stored procedures, functions, and views. 

We now know about SQL and its history. Let us now focus on the relational database management system (RDBMS). PL/SQL, Procedural Language extension to SQL, helps to extend SQL and provides it procedural capabilities

RDBMS Databases

We have seen that SQL is used by many RDBMS databases, like MySQL, SQL Server, Oracle, MS Access, etc. Therefore, in this section, we will learn a few RDBMS databases. We will see the features of each database, and then we can acknowledge the difference between them. 

1. MySQL:

A Swedish company, MySQL AB, developed the MySQL database, which is free to use and open-source. MySQL is compatible with all the major systems, like Microsoft Windows, Linux, Unix, and macOS operating systems. You can utilize its commercial or non-commercial versions. According to versions, you are provided with the features. It incorporates a sturdy, faster, multi-threaded, and multi-user database server. 

In 1994, Micheal Widenius and David Axmark developed MySQL and is pronounced in the context of ‘my ess-que-ell.’ On 23rd May 1995, the first release of the MySQL database was launched. This database was compatible to run with the Windows system on 8th January 1998. It was first released for Windows 95 and NT. 

The MySQL version 3.23: beta was released between June 2000 to January 2001. The next version, 4.0: beta, launched between August 2002 to March 2003. From June 2004 to October 2004, version 4.1: beta was released. The following version, 5.1: beta, was produced from March 2005 to October 2005. Later in 2008, MySQL was acquired by Sun Microsystems, and then the latest version 5.2: beta was launched. 

Features:

MySQL offers high performance, high availability, and flexibility to run anything. It also supports powerful transactions to carry out. All data in the MySQL database is protected and highly secured. It is freely available and open-source. 

2. Oracle:

Oracle is one of the widely used relational databases having multi-user and large management systems. Oracle Corporation launched the oracle relational database. This DBMS is best suited for client/server operations. Several clients can access and send data over the network. Oracle is compatible with client and server systems, like UNIX, UnixWare, OS/2, NetWare, and MSDOS. 

Larry Ellison, Ed Oates, and Bob Miner developed the Oracle database in 1977 and established Software Development Laboratories. In 1979, the 2.0 Oracle version was released in the next two years, the first commercial Oracle version. They changed the name Software Development Laboratories to Relational Software Inc. (RSI). This organization was later named Oracle Corporation in 1982. The next version, 3.0, was launched in 1983 in the C language. Oracle version 4.0, developed in 1984, includes more advanced features, like multi-version read consistency, concurrency control, etc. Oracle 11g, the latest version, was launched in 2007.

Oracle relational database involves more advanced features, like concurrency, locking mechanism, SQL Plus, resource manager, data mining, partitioning, data warehousing, parallel execution, etc. 

3. MS SQL Server:

The relational database from Microsoft is MS SQL Server. The primary objective of this database is to store and retrieve the data in the database. It is compatible with Microsoft Windows, Linux, and Windows Server operating systems. The MS SQL Server allows any software application, present on the same computer or the different one or across the network, to retrieve the data. It uses ANSI SQL and T-SQL database languages.

Initially, in 1987, SQL Server was developed for UNIX systems by Sybase. In the following year, Microsoft, Aston-Tate, and Sybase released SQL Server 1.0 for OS/2. For Windows 3.0 users, version 1.1 of SQL Server was launched in 1990. Later, Aston-Tate left the organization, and Micorosft released SQL Server 2000 in 2000. In the next year, XML for this database was developed. SQLXML 2.0 developed in 2002 and later released SQLXML 3.0. The latest version, SQL Server 2005, was developed on 7th November 2005. 

MS SQL Server features database mirroring, DDL triggers, seamless XML integration, database mail, high performance, and high availability. 

4. MS Access:

Another popular relational database is MS Access. Like the above database, MS Access is also the product of Microsoft. This DBMS is used for small-scale businesses and is exceptionally inexpensive. It incorporates a Jet database engine. This engine is also called Jet SQL, as it uses SQL language. 

In 1992, the first MS Access version 1.0 was developed. In the next year, it was evolved with the Access Basic programming language for improving compatibility. Later, Access 2007 was launched, which used the ACCDB format. This MS Access version is advanced and includes complex data types and multi-valued fields. 

MS Access uses macros for connected tables, forms, reports, and queries. Using this system, you can export and import your data into several formats, like ASCII, Oracle, ODBC, SQL Server, etc. The Jet format holds the application and its data together in one file. Therefore, sharing any application is effortless. It does not support database triggers, stored procedures, and transaction logging, as it is a file server-based system. 

What is RDBMS?

Almost all the current database systems, like SQL, Oracle, MySQL, Microsoft Access, etc., use the relational database management system. This database system is based on the relational model. E. F. Codd proposed the relational database management system. There are certain elements in RDBMS you need to know before we move towards SQL. Let us discuss each aspect below.

1. Table:

In RDBMS, the data is stored in the form of a table. This table is referred to as an object. It is also called a relation. A table in RDBMS has rows and columns. Each table in the RDBMS has a specific table name. A table is considered the simplest data storage form.

Let us consider the table having the table name, ‘Student.’ We will store the student information in the Student table, like Name, Age, Student_ID, and City.

NameAgeStudent_IDCity
Steve231092New York
Oliver241093Los Angeles
Robert211094Chicago
Mary 251095California
Jennifer231096New York
James221097Washington DC
John211098California
Jhonny241099Chicago

The above table provides information about students. 

2. Field:

The next RDBMS element is the field. A field is the smaller entity of the table. For example, in the above table, ‘Student,’ Name, Age, Student_ID, and City are fields. Therefore, each column in the table is a field, which stores the particular information or record. 

3. Row:

A row is also referred to as a record. The record or row contains information about the particular entity. In the above table, ‘Student,’ there are eight different rows or records. In other terms, the above table holds the data of eight different students. A single horizontal entity is a row or record. The following is a row or record from the table ‘Student.’

John      21       1098       California

4. Column:

We have seen that a row is a horizontal entity. Unlike the row, the column is a vertical entity. A column holds all the data related to the field. In the ‘Student’ table, Name, Age, Student_ID, and City are fields. Therefore, a column with the field ‘Name’ contains the names of students. This column is represented as follows:

Name
----------------------
Steve
Robert
Mary
Jennifer
James
John
Jhonny

5. NULL:

When there is no data in a particular field or has a blank space in the field, such value is the NULL value. The NULL value is not the same as the zero. It is kept blank while creating the record. 

SQL Basics

We shall learn all SQL basics in this segment, like syntax, query processing, constraints, commands, and database normalization.

SQL Syntax

A SQL query contains several elements for retrieving the data from the database. Below are the components involved in the query.

  1. Clauses:

Clauses are the optional part of the query. They are the elements of the query. For example, UPDATE, SET, WHERE, etc., are the clauses. 

  1. Expressions:

Expressions in the query results in the data in the form of a table having rows and columns. A row is called ‘tuple,’ and a column is referred to as an ‘attribute.’

  1. Predicates:

Predicates are conditions, which produce three-valued logic (True/ False/ Unknown). 

  1. Queries:

Queries fetch the required data from the database using conditions. 

  1. Statements:

Statements control the flow of the program, transactions, connections, etc. It may also involve a semicolon ‘;.’

  1. Insignificant whitespaces:

Unwanted whitespaces are not considered in the SQL query, making it more understandable and readable. 

Here we will see small examples using three clauses, update, set, and where. 

  1. UPDATE age;

The above query updates the age attribute. 

  1. SET age = age+2;

The above involves an expression ‘age+2,’ which increases the age by two, and the result is assigned to the age. 

  1. WHERE emp_name = ‘John’;

Here, ‘emp_name = ‘John’ is a predicate, and ‘John’ is an expression. 

Query Processing

Queries in SQL are processed to convert them into low-level expressions, which are understandable by computers. Any query processing includes several components, like parser, translator, evolution engine, database, etc. The high-level query is transformed into expressions that are compatible with the file system’s physical level. Below is the diagram illustrating how the query is processed and optimized to implement it at the physical level. 

Firstly, the high-level query is converted into an understandable expression, i.e., algebraic expression. During this conversion, the query passes through several phases, as shown in the above diagram. At the first stage, the parser checks the query’s syntax and observes whether the relation and attributes involved are correct. The translator transforms the query into an internal representation, which is given to the optimizer. 

The query optimizer converts the query’s internal representation into expressions, which can be executed. These expressions are then executed by the DBMS engine or query evaluation engine. The result of the DBMS engine is given to the database’s physical level. 

SQL Clauses

The clause in the SQL is a component of the query.  There are several clauses in SQL. Let us see each clause in detail with the syntax below.

  1. SELECT Clause:
See also  Download Turbo C++ for Windows 7, 8, 8.1, and Windows 10

When you want to retrieve the data from the specified columns or rows, you can use the SELECT clause. It can also retrieve the data from the rows of multiple tables or views. The result set of the select statement is also a table. 

Syntax:

SELECT column1, column2, column3, ...columnN FROM table-name;
  1. DISTINCT Clause:

The DISTINCT clause is used with the SELECT clause. It returns unique and different values. No duplicate values are present in the result set. 

Syntax:

SELECT DISTINCT column1, column2, …FROM table-name;
  1. WHERE Clause:

The Where clause is used to specify the condition for retrieving the data. It displays the only rows that are specified by the condition. 

Syntax:

SELECT column1, column2, column3, ...columnN FROM table-name WHERE condition;
  1. GROUP BY Clause:

This clause groups the rows that are having similar values into one result set. The GROUP BY clause is always used in a query with functions like SUM, MAX, MIN, COUNT, and AVG. 

Syntax:

SELECT column_name FROM table-name WHERE condition GROUP BY column_name;
  1. ORDER BY:

When there is a need to sort the result into ascending or descending order, you can use the ORDER BY statement. By default, this statement sorts the result in ascending order. If you wish to arrange the result set in descending order, you can specify ‘desc’ in the query. 

Syntax:

SELECT column1, column2, ... columnN FROM table-name ORDER BY column1, column2, ...columnN ASC|DESC;
  1. HAVING:

The primary objective of the HAVING keyword is it is used with only aggregate functions, like SUM, MIN, MAX, COUNT, and AVG. The WHERE keyword is not compatible with aggregate functions. 

Syntax:

SELECT column_name FROM table-name WHERE condition GROUP BY column_name HAVING (Arithmetic function condition); 

Commands in SQL

The SQL commands are categorized into five different types, DDL, DML, DQL, TCL, and DCL. We will know each of these three types in detail below. 

  1. DDL:

The data definition language consists of four SQL commands, create, alter, truncate, and drop. These commands are used for changing the table structure. They all are auto-committed, i.e., the changes made in the table structure are saved permanently.

The create table statement is used to create the new table, view of the table, or any other object in the database. 

Syntax:

CREATE TABLE table-name (COLUMN_NAME1 DATATYPE, COLUMN_NAME2 DATATYPE, ...);

Example:

CREATE TABLE Student (Name varchar(20), Age int, Student_ID int, City varchar(30));

Another command, alter, is used to modify or make changes in an existing view or table. Using the alter command, you can add a new column to the table or change the existing column of a particular table. 

Syntax:

ALTER TABLE table-name ADD column_name datatype;

Example:

ALTER TABLE Student ADD Phone_no int;

The truncate DDL command deletes all rows from the table. Note that it does not delete the table. 

Syntax:

TRUNCATE TABLE table name;

Example:

TRUNCATE TABLE Student;

Lastly, the drop command deletes the table or view. It completely deletes the table structure and the data stored in the table.

Syntax:

DROP TABLE table-name;

Example:

DROP TABLE Student;
  1. DML:

The data manipulation language comprises three commands, insert, delete, and update. These commands modify the table data. Unlike the DDL commands, DML commands are not auto-committed. When you use any of the three commands, you need to use the ‘commit’ keyword to save the changes permanently. 

The INSERT command inserts the data into the table. Whenever any new data gets added to the table, the number of tuples get increased. 

Syntax:

INSERT INTO TABLE table-name ( col1, col2, col3, ...) VALUES ( value1, value2, value3, ...);

Or

INSERT INTO table-name VALUES ( value1, value2, value3, ...);

Example:

INSERT INTO Student ( Name, Age, Student_ID, City) VALUES ("John", 23, 1098, "California");

Next is the update statement. This statement updates or modifies the existing value of a single attribute. 

Syntax:

UPDATE table-name SET [column_name1 = value1, column_name2 = value2, column_name3 = value3, ...][WHERE CONDITION];

Example:

UPDATE Students SET Age = 25 WHERE Student_ID = 1098;

Finally, the delete statement deletes specific rows from the table. 

Syntax:

DELETE FROM table-name [WHERE CONDITION];

Example:

DELETE FROM Student WHERE Student_ID = 1098;
  1. DCL:

In the SQL, we assign some authorities or rights to people for manipulating, accessing, or deleting the data from the database. We can grant and take back the authorities using GRANT and REVOKE commands. 

The GRANT statement is used to grant the right to access database. Users can access database if they are granted permission. 

Syntax:

GRANT SELECT, UPDATE ON table-name TO SOME_USER, ANOTHER_USER;

The REVOKE statement is opposite to the GRANT statement. It takes away all privileges from the user to access the database

REVOKE SELECT, UPDATE ON table-name FROM USER1, USER2;
  1. DQL:

There is only one command in the data query language, SELECT. This command is used to select specific attributes from the table. It selects attributes depending upon the WHERE condition specified. 

Syntax:

SELECT expressions FROM table-name WHERE condition;

Example:

SELECT Name FROM Student WHERE Age > 20;
  1. TCL:

The transaction control language involves three commands, commit, rollback, and savepoint. You can use the TCL statements only with the DML commands. They cannot be used with DDL statements, as they automatically save the changes permanently in the database. 

The commit statement saves all the changes permanently made to the table in the database. 

Syntax:

COMMIT;

Example:

DELETE FROM Student WHERE Age = 25;
COMMIT;

When you desire to undo any previous transaction that is not committed in the database, the rollback statement can be executed. 

Syntax:

ROLLBACK;

Example:

DELETE FROM Student WHERE Age = 29;
ROLLBACK;

Lastly, the SAVEPOINT statement carries out an efficient rollback of transactions. It rolls back the specific transaction to the desired point, not needing the entire transaction to roll back.

Syntax:

SAVEPOINT SAVEPOINT_NAME;

Data Integrity

Data Integrity in the database implies accuracy and on-redundancy. The data present in the database should be accurate and non-repeated. For these purposes, we use constraints. Generally, constraint means limitations or restrictions. In SQL, constraint implies regulations or rules on columns of the table. In other words, the constraints specify what data should be added to the table. Applying constraints to the data column ensures the reliability and correctness of the data. 

SQL

The above diagram illustrates different constraints that are grouped into three categories, Entity integrity, Referential integrity, and domain integrity. 

You can apply constraints to the columns of the table or directly to the table. If you prefer to apply constraints to the columns, it is referred to as column-level constraints. You can apply column-level constraints to one or two columns. And using constraints to the entire table is called table-level constraints. When you apply table-level constraints, it gets applied to all the columns of the table. 

Let us now know all the above three data integrity categories. 

  1. Entity Integrity:

In the entity integrity, each tuple of the table is identified uniquely using the below three constraints:

  • NOT NULL Constraint: It ensures that there is no NULL value in the table or column. 
  • UNIQUE Constraint: This rule specifies that the column in the table will have unique values. No value in the column will be repeated. 
  • PRIMARY Key: When you apply this constraint to any column of the table, that column will uniquely identify each row or tuple in the table.
  1. Referential Integrity:

When you wish to relate two different tables in the database, you can use referential integrity. 

  • FOREIGN Key: It will uniquely identify a row or tuple of another table. The primary key of one table should be present as an attribute in the other table. Such an attribute is referred to as FOREIGN Key. 
  1. Domain Integrity:

The domain integrity is used to ensure that all values in the table follow the defined rules. 

  • DEFAULT Constraint: It assigns the default value to the column when the specific value is not stated. 
  • CHECK Constraints: When you wish to verify that all the column values satisfy the specified condition, you can apply the CHECK constraint. 

INDEX: When you desire to fetch or create the database’s data, you can use the INDEX.

Database Normalization

Database normalization is the process of organizing the data in the database in a well-structured format. There are different normal forms in database normalization, which helps to eliminate redundancy and enhance data integrity. E. F. Codd proposed the database normalization concept. There are two primary reasons for database normalization.

  • Removing repeated data from the database.
  • Ensuring that all the data dependencies are correct and make sense. 

Removing redundant data will help in freeing up the disk space. Using database normalization, you can create a good database and store the data in it logically. There are different normal forms in database normalization, which help you to create a well-structured database. These normal forms are given below:

  • First Normal Form
  • Second Normal Form
  • Third Normal Form
  • Boyce Codd Normal Form
  • Fourth Normal Form
  • Fifth Normal Form

SQL Data Types

SQL data type defines the type of data being used. There are six different kinds of data types in SQL: numeric, date and time, character and string, Unicode character, binary, and miscellaneous. These data types are used while creating the table in the database. Each column is assigned to the data type and should contain the data of the specified type only. 

Every relational database does not support all SQL data types. Oracle relational database does not support DATETIME data type, and the MySQL database does not support CLOB data type. There are some specific databases, which have additional separate data types. In Microsoft SQL Server, other data types are ‘money’ and ‘smallmoney.’ Let us now discuss each data type below.

1. Numeric Data Type

There are eleven different numeric data types in SQL, which are categorized into two groups. The first group contains exact numeric data types, and the second has approximate numeric data types. The exact numeric data types include bigint, int, smallint, tinyint, decimal, money, numeric, smallmoney, and bit. And the approximate numeric data types involve float and real.  The below tables will illustrate the ranging values of exact and approximate data types. 

Exact Data Types

Data TypeFrom ToStorage
bit1
bigint-9,223,372,036,854,775,8089,223,372,036,854,775,8078 bytes
tinyint2551 byte
smallmoney-214,748.3648+214,748.36474 bytes
money-922,337,203,685,477.5808+922,337,203,685,477.58078 bytes
smallint-32,76832,7672 bytes
int-2,147,483,6482,147,483,6474 bytes
numeric-10^38 +110^38-15 – 17 bytes
decimal-10^38 +110^38-15 – 17 bytes

Approximate Data Types

Data TypeFromToStorage
real-3.40E + 383.40E + 384 or 8 bytes
float-1.79E + 3081.79E + 3084 bytes

2. Date and Time

The date and time data type has four different data types, datetime, date, time, and smalldatetime. Let us now know the ranging values of each date and time data type. 

Data TypeFromToStorage
smalldatetimeJan 1, 1900Jun 6, 20794 bytes
datetimeJan 1, 1753Dec 31, 99998 bytes
datetime2Jan 1, 0001Dec 31, 99996 – 8 bytes
dateThis data type stores the date in the form of June 30, 1991.3 bytes
timeThis data type stores the time in form 12:30 P.M.3 – 5 bytes

3. Character and String

Character and String data type is used to insert alphabets and words. There are four distinct Character and String data types as follows:

  • char

The char data type is of the fixed-length and has a maximum size limit of 8,000 characters. 

  • varchar

This data type is a variable-length, having a maximum size limit of 8,000 characters.

  • text

The text data type has variable-length, and a maximum size limit is up to 2,147,483,647 characters. 

  • varchar(max) 

This data type is also of variable-length, having the maximum size limit of 2E + 31. 

All of the above data types are non-Unicode. 

4. Binary:

There are four binary data types in SQL: binary, varbinary, varbinary(max), and image. Below is a brief description of each of these data types.

  • binary

This data type is of fixed-length, having a maximum length limit of 8,000 bytes. 

  • varbinary

varbinary is another binary data type, which has a variable length. It has a maximum size limit of 8,000 bytes. 

  • varbinary(max)

Like varbinary, varbinary(max) is also a variable-length data type, having the maximum size limit of 2E + 31 bytes. It is used in only SQL Server 2005. 

  • image

image is also a variable-length binary data with a maximum limit of 2,147,483,647 bytes. 

5. Unicode Data Type

This group also has four distinct data types: nchar, nvarchar, nvarchar(max), and ntext. 

  • nchar

nchar is a fixed-length Unicode data type, having a maximum length of 4,000 bytes. 

  • nvarchar

Unlike nchar, nvarchar is a variable-length Unicode with a maximum size limit of 4,000 bytes. 

  • nvarchar(max)

nvarchar(max) is compatible with only the SQL Server 2005 database. It has a maximum size limit of 2E + 31 and has a variable length. 

  • ntext

ntext is a variable-length Unicode with a maximum length of 1,073,741,823 bytes. 

6. Miscellaneous

The following are the data types that fall under the miscellaneous group. 

  • sql_variant: It holds the values of all the data types supported by the SQL Server database, except timestamp, text, and ntext. 
  • uniqueidentifier: This data type holds a GUID, a globally unique identifier. 
  • cursor: It refers to a cursor object. 
  • timestamp: It is a unique number that gets changed whenever any row in the database is modified. 
  • xml: It is supported by SQL Server 2005 and holds xml data. 
  • table: It contains a result set, which is used for future purposes. 
See also  Download Turbo C++ for Windows 7, 8, 8.1, and Windows 10

SQL Operators

Operators are the reserved words or characters. They are meant for performing specific operations. Usually, an operator is used in the WHERE clause to specify the condition. An operator can be arithmetic, logical, or comparative. You can also utilize an operator to combine two conditions in the same statement. In SQL, there are three different operator types, arithmetic, comparison,  and logical operators.

1. Arithmetic Operators

An arithmetic operator can perform arithmetic operations, like addition, subtraction, multiplication, modulus, and division. Let us take ‘a, having the value 15’ as one variable and ‘b, having the value 30’ as another variable. We shall see an example of each arithmetic operator below. 

OperatorDescriptionExample
+‘+’ operator performs the addition of the two variables. a+b=15+30=45
‘-’ operator subtracts the right variable from the left variable. a-b =15-30=-15
%‘%’ produces the remainder as to its result when the left variable is divided by the right variable. b/a=30%15=0
/‘/’ performs division. The left variable value is divided by the right variable value.b/a =30/15=2
*‘*’ multiplies two variables.a*b=15*30=450

2. Logical Operators

Here, we will see all the logical operators used in SQL with their brief descriptions. 

  • AND: AND is used for combining several conditions in one SQL statement. 
  • BETWEEN: This operator is used for specifying the value range. You can set the minimum and maximum value using the BETWEEN keyword. 
  • IN: When you apply the IN keyword, it compares a single value with all other literal values present in a specified list. 
  • NOT: This operator is used with other logical operators, like EXISTS, BETWEEN. For example, you can apply NOT EXISTS, NOT BETWEEN, NOT IN, etc. 
  • IS NULL: When you wish to compare any value with the NULL value, you can use IS NULL. 
  • ALL: This operator is used for comparing a value in one set with all other values in another set. 
  • ANY: It compares a value with any practical value in the list. 
  • EXISTS: It checks whether a particular row is present in the table. 
  • LIKE: When you desire to compare a value with a similar value, utilize the LIKE keyword. 
  • OR: Like AND, OR also combines several conditions in the WHERE clause. 
  • UNIQUE: This keyword verifies the uniqueness of each row in the table. 

3. Comparison Operators

For acknowledging comparison operators, we shall take two variables, a and b. Let ‘a’ be 15 ‘b’ be 20. 

OperatorDescriptionExample
<>It checks if the values of the two variables are equal or not. If they are not equal, it returns true, else not true. (a<>b) =(15<>30)=true
==Like <>, == also checks the two variables for equal values. If the values are equal, it returns true. (a==b)=(15==30)=not true
!=‘!=’ is similar to the ‘<>.’ It also produces true if both variables’ values are not equal. (a<>b) =(15<>30)=true
>If the left variable’s value is greater than the right variable, ‘>’ returns true. (a>b)=(15>30)= not true
!>‘!>’ is opposite to the ‘>’ operator. It returns true if the left variable value is not greater than the right variable value. (a!>b)=(15!>30)= true
>=If the left operand is greater than or equal to the right operand, the condition becomes true. (a<=b)=(15>=30)= not true
<If the left variable value is less than the right variable value, ‘<’ returns true. (a<b)=(15<30)= true
!<‘!<’ is opposite to the ‘<’ operator. It returns true if the left variable value is not less than the right variable value. (a<b)=(15!<30)= not true
<=If the left operand is less than or equal to the right operand, the condition becomes true. (a<=b)=(15<=30)= true

SQL Expressions

In SQL, an expression is composed of operators, one or more values, and SQL functions. They are always specified in the WHERE clause. Below is the syntax illustrating the use of expressions in the database query. 

Syntax:

SELECT column1, column2, columN FROM table-name WHERE [CONDITION | EXPRESSION];

There are three kinds of expressions used in SQL, boolean, numeric, and date. 

1. Boolean Expressions

The boolean expression retrieves the data that matches a single value. It retrieves the rows that match the value specified in the EXPRESSION. 

Syntax:

SELECT column1, column2, columN FROM Table-name WHERE [SINGLE VALUE MATCHING EXPRESSION];

Earlier, we have created the table, having a table-name, ‘Student.’ We will retrieve its data using the SELECT statement.  

SELECT * from Student;

Output:

Name        Age    Student_ID        City
--------------------------------------------------------
Steve        23        1092         New York
Oliver       24        1093         Los Angeles
Robert       21        1094         Chicago
Mary         25        1095         California
Jennifer     23        1096         New York
James        22        1097         Washington DC
John         21        1098         California
Jhonny       24        1099         Chicago
8 rows in set (0.00 sec)

Now, we will fetch the data that matches the value Student_ID = 1098. Only one row will be displayed as follows. 

SELECT * FROM Student WHERE Student_ID = 1098;

Result:

Name        Age    Student_ID        City
-------------------------------------------------------
John        21        1098         California
1 row in set (0.00 sec)

2. Numeric Expression

Numeric expressions are mathematical operations. It is expressed as follows:

Syntax:

SELECT numerical_expression as OPERATION_NAME [FROM table-name WHERE CONDITION];

You can perform any mathematical operation using numeric expressions. Here is one example illustrating the mathematical operation using a numeric expression. 

SELECT (20+30) AS ADDITION;

Output:

ADDITION
-------------------------------
    50
1 row in set (0.00 sec)

You can also include aggregate functions in numeric expression: avg(), max(), count(), min(), and sum(). Now, we will count the number of records or rows in the ‘Student’ table. 

SELECT COUNT(*) AS "No Of Records" FROM Student;

Output:

No Of Records
-------------------------------
          8
1 row in set (0.00 sec)

3. Date Expressions

Date expressions are the most desirable expression in SQL, which provides the system’s current date and time. Let us see the query for displaying the current date and time of the system.

SELECT CURRENT_TIMESTAMP;

Output:

Current_Timestamp
-------------------------------
2020-11-23 01:48:45
1 row in set (0.00 sec)

Create, Drop, and USE Database statements in SQL

1. Create Database

Before we create tables, we need to create the database first. Firstly, create the database in which you wish to work. We will now see how to create the database. 

Syntax:

CREATE DATABASE DatabaseName;

You must make sure that the database name should be unique and different. It should not be used before. 

Let us now create the database having the name <softwaretesttips>. 

CREATE DATABASE softwaretesttips;

For creating any database, you must have the admin privilege. You can view all the databases created by using the ‘SHOW DATABASES’ command. We have created multiple databases earlier: school_info, softwaretesttips, test, company, and origin. 

SHOW DATABASES;

Result:

Database
-----------------------------------
school_info
softwaretesttips
test
company
origin
5 rows in set (0.00 sec)

The above output displayed the list of databases created. 

2. Drop Database

Suppose we wish to delete one of the above databases. For this purpose, the ‘DROP DATABASE’ statement is used. 

Syntax:

DROP DATABASE DatabaseName;

Consider that we wish to delete the ‘test’ database from the SQL Schema. When you delete any database, all the data present in it would be deleted. You must have the admin privilege for deleting the database as well. 

DROP DATABASE test;

We have deleted the ‘test’ database. Let us now look at the list of databases present in the schema. 

SHOW DATABASES;

Output:

Databases
-------------------------------
school_info
softwaretesttips
company
origin
4 rows in set (0.00 sec)

3. Use and Select Database

In our schema, there are several databases. Suppose we wish to work with only one specific database; you have to choose the one from all databases. After choosing one database, you can perform all DDL operations in it. For selecting the database, there is a USE statement. 

Syntax:

USE DatabaseName;

We will select one database ‘softwaretesttips’ from our database list in which we will perform all database operations. 

USE softwaretesttips;

Anything you create, tables or view, will be present in the ‘softwaretesttips’ database. 

This was all the quick tutorial about SQL. We will now create one table in the ‘softwaretesttips’ database and perform all SQL operations on that table.  

Create Table

We have seen the create table syntax and the example. This statement is written as follows,

CREATE TABLE table-name (
column1 datatype,
column2 datatype,
column3 datatype,
....
columnN datatype,
PRIMARY KEY ( one or more columns)
);

In the create table statement, we specify the table name, attributes, and data types. We can even apply constraints on the attributes using the create table statement. Make sure that the table name is unique and not used before. 

We will now create the table using the ‘create table’ statement. Let us create an ‘EMPLOYEE’ table, which stores employees’ data. We will create five attributes for this table: Emp_ID, Name, Age, City, and Salary.

CREATE TABLE EMPLOYEE(
EMP_ID INT                       NOT NULL,
NAME VARCHAR(15)                 NOT NULL,
AGE INT                          NOT NULL,
CITY VARCHAR(15),
SALARY DECIMAL(16, 3),
PRIMARY KEY (EMP_ID)
);

We have created the table ‘EMPLOYEE,’ with EMP_ID, NAME, AGE, CITY, and SALARY as its attributes. The primary key of the ‘EMPLOYEE’ table is EMP_ID, which should not be NULL. Additionally, NAME and AGE attributes should also not be NULL. 

After writing the above create table statement, you will see the message ‘table created successfully’ on the screen. When you desire to see the attributes of the table or table schema, type the below command:

DESC EMPLOYEE;

Output:

Field    Type           Null    Key    Default   Extra
--------------------------------------------------------
EMP_ID   int(11)         NO     PRI
NAME     varchar(15)     NO
AGE      int(11)         NO
CITY     char(25)        YES             NULL
SALARY   decimal(16,3)   YES             NULL
5 rows in set (0.00 sec)

We can drop the table ‘EMPLOYEE’ using the DROP statement. 

DROP EMPLOYEE;
Query OK, 0 rows affected (0.01 sec)


After dropping the table, it gets deleted from the database. If you try to run the ‘DESC’ command, it will result in an error as follows:

DESC EMPLOYEE;
ERROR 1146 (42S02): Table 'SOFTWARETESTTIPS.EMPLOYEE' doesn't exist

Insert Values in Table

We have learned how to create a table in a particular database and drop it from the database. Now, we will focus on inserting the data into the table. In the early section of this article, we have seen how to write the insert statement. Here, we shall learn to write the insert statement using the example. 

We will take the above table, ‘EMPLOYEE,’ and insert employees’ data into it using the insert keyword. There are two ways of inserting the data into the table. 

INSERT INTO table-name (column1, column2, column3, ...columnN) VALUES (value1, value2, value3, ...valueN);

Another simplest form of writing the INSERT statement is:

INSERT INTO table-name VALUES (value1, value2, value3, ...valueN);

In the first format, we have specified columns and values. We have only specified values in the second format. While using the second format, make sure that you insert the values in the correct order as you have specified the attributes during table creation. 

Let us now insert employees’ data in the EMPLOYEE table. 

INSERT INTO EMPLOYEE (EMP_ID, NAME, AGE, CITY, SALARY) VALUES (401, 'James', 45, 'Chicago', 30,000);
INSERT INTO EMPLOYEE (EMP_ID, NAME, AGE, CITY, SALARY) VALUES ( 402, 'Johnny', 34, 'Washington DC', 45,000);
INSERT INTO EMPLOYEE (EMP_ID, NAME, AGE, CITY, SALARY) VALUES ( 403, 'Robert', 55, 'California', 60,000);
INSERT INTO EMPLOYEE (EMP_ID, NAME, AGE, CITY, SALARY) VALUES (404, 'Steve', 44, 'Chicago', 30,000);
INSERT INTO EMPLOYEE (EMP_ID, NAME, AGE, CITY, SALARY) VALUES (405, 'Robin', 37, 'Los Angeles', 40,000);
INSERT INTO EMPLOYEE (EMP_ID, NAME, AGE, CITY, SALARY) VALUES ( 406, 'Williams', 46, 'New York', 35,000);
COMMIT;

We have inserted the data of six employees using the first format. Let us insert an employee’s data using the second format. 

INSERT INTO VALUES ( 407, 'Mary', 32, 'California', 50,000);
COMMIT;

Now, there are seven records or rows in the EMPLOYEE table. For displaying the data in the EMPLOYEE table, the SELECT statement is used. Let us now focus on the SELECT statement. 

SELECT Statement

The SELECT statement is used for retrieving the data from a particular table. When we fetch the data using the SELECT statement, it returns the data in the table format. Earlier, we have how to write the SELECT statement. 

SELECT column1, column2
FROM table-name;

The above statement will display only column1 and column2 from the specified table. If you wish to show all the data of a specific table, ‘*’ is used instead of specifying all columns. 

SELECT * FROM table-name;

We will display all the data from the EMPLOYEE table. 

SELECT * FROM EMPLOYEE;

Output:

EMP_ID   NAME    AGE     CITY            SALARY
---------------------------------------------------------
401     James    45     Chicago          30,000
402     Johnny   34     Washington DC    45,000
403     Robert   55     California       60,000
404     Steve    44     Chicago          30,000
405     Robin    37     Los Angeles      40,000
406     Williams 46     New York         35,000
407     Mary     32     California       50,000

Suppose you wish to retrieve only the name and ID of employees; you can specify the EMP_ID and NAME in the SELECT statement. 

SELECT EMP_ID, NAME
FROM EMPLOYEE;

Output:

EMP_ID       NAME
---------------------------
401          James
402          Johnny
403          Robert
404          Steve
405          Robin
406          Williams
407          Mary

WHERE Clause

The WHERE clause is utilized to define the condition for retrieving the data. It is also utilized to combine two different tables. When you apply the specific condition in the WHERE clause, the data is retrieved only if the condition is true. The WHERE keyword is commonly used when users need specific information. This keyword is used in DELETE and UPDATE statements. 

SELECT column1, column2, columnN
FROM table-name
WHERE [condition];

In this condition, we can use logical and comparison operators. Consider the EMPLOYEE table. Let us now retrieve all employee IDs, names, and salaries whose salary is greater than or equal to 40,000. Therefore, we will specify the condition as salary >= 40,000 in the WHERE clause. 

SELECT EMP_ID, NAME, SALARY
FROM EMPLOYEE
WHERE SALARY >= 40,000;

Output:

EMP_ID         NAME         SALARY
--------------------------------------------
402            Johnny       45,000
403            Robert       60,000
405            Robin        40,000
407            Mary         50,000

The result set contains all employee IDs, names, and salaries, whose salary is greater than or equal to 40,000. 

See also  Download Turbo C++ for Windows 7, 8, 8.1, and Windows 10

If your condition in the WHERE statement contains String, write it in the single quotes (‘ ‘). Let us retrieve the name, ID, and age of an employee having the name Mary. 

SELECT EMP_ID, NAME, AGE
FROM EMPLOYEE
WHERE NAME = 'Mary';

Output:

EMP_ID     NAME         SALARY
--------------------------------------
407        Mary         50,000

AND and OR Operators

AND and OR operators combine two or more different conditions. When you require precise and concise data from the table, you can join several conditions using AND or OR operators. 

When you combine conditions using the AND operator, it returns the result only if all the conditions are satisfied. It will not return the data if any of the conditions are satisfied. We know that operators are used in the WHERE keyword. It is written as follows:

SELECT column1, column2, columnN
FROM EMPLOYEE
WHERE [condition1] AND [condition2] AND [condition3];

You can specify several conditions. Consider the EMPLOYEE table. Let us now retrieve the name, ID, age, and salary of employees whose age is greater than 40 and the salary is greater than or equal to 35,000. 

SELECT EMP_ID, NAME, AGE, SALARY
FROM EMPLOYEE
WHERE AGE > 40 AND SALARY >= 35,000;

Output:

EMP_ID   NAME      AGE      SALARY
-----------------------------------------
403      Robert     55      60,000
406      Williams   46      35,000

The employee data is retrieved when both conditions hold. 

The OR operator also combines two or more conditions. Unlike the AND keyword, there is no need for all the conditions to be true that are specified in the WHERE keyword using the OR clause. Even if a single condition is true, it will write the data in the result set. You can specify several conditions using the OR keyword as given below:

SELECT column1, column2, column
FROM EMPLOYEE
WHERE [condition1] OR [condition2] OR [condition3];

From the table EMPLOYEE, we will retrieve the name, city, and salary, whose city is California or salary is 30,000. 

SELECT NAME, CITY, SALARY
FROM EMPLOYEE
WHERE CITY =  'California' OR SALARY = 30,000;

Output:

NAME         CITY            SALARY
-------------------------------------------
James        Chicago         30,000
Robert       California      60,000         
Steve        Chicago         30,000
Mary         California      50,000

The above result set contains all employee names who either live in California or have a salary of 30,000.

HAVING Clause

When you wish to filter your result to more specific data, the HAVING clause is used. You can apply only aggregate functions in the HAVING keyword, which will specify the condition for data retrieval. The following is the form of writing the HAVING keyword:

SELECT column1, column2
FROM table1, table2
WHERE [conditions]
GROUP BY column1, column2
HAVING [condition]
ORDER BY column1, column2;

We will take the table, having the table-name, EMPLOYEE2. The table is as follows:

EMP_ID   NAME    AGE     CITY            SALARY
---------------------------------------------------------
401     James    45     Chicago          30,000
402     Johnny   34     Washington DC    45,000
403     Robert   55     California       60,000
410     Oliver   34     Los Angeles      45,000
404     Steve    44     Chicago          30,000
405     Robin    37     Los Angeles      40,000
406     Williams 46     New York         35,000
407     Mary     35     California       55,000
SELECT EMP_ID, NAME, AGE, CITY, ADDRESS
FROM EMPLOYEE2
GROUP BY AGE
HAVING COUNT(AGE) >= 2;

Output:

EMP_ID   NAME    AGE     CITY            SALARY
-----------------------------------------------------
402     Johnny   34     Washington DC    45,000

Aggregate Functions

Aggregate functions in the database management system take multiple data from records and return a single value after calculations. There are five different aggregate functions: sum(), avg(), count(), min(), and max(). All these functions can be applied only on the numerical data, except the count() function. Let us discuss each function in detail below. We will take the EMPLOYEE table for understanding aggregate functions with examples. 

EMP_ID   NAME    AGE     CITY            SALARY
---------------------------------------------------------
401     James    45     Chicago          30,000
402     Johnny   34     Washington DC    45,000
403     Robert   55     California       60,000
404     Steve    44     Chicago          30,000
405     Robin    37     Los Angeles      40,000
406     Williams 46     New York         35,000
407     Mary     32     California       50,000
  1. Sum(): The sum() function returns the sum of all numbers present in the column. In the above table, we will sum all employees’ salaries using the sum() function. It takes multiple data and returns a single value. 
SELECT SUM(SALARY)
FROM EMPLOYEE;

Result:

290,000
  1. Avg(): The avg() function also returns a single value. It calculates the sum of all data numbers present in a column and divides it by the total number of columns. The avg() function calculates the average of all numeric data. 
SELECT AVG(SALARY)
FROM EMPLOYEE;

Result:

41,428.57
  1. Max(): When you need to find the maximum value from the data set, you can apply the max() function to a particular column. It also returns a single value in the result set. 
SELECT MAX(SALARY)
FROM EMPLOYEE;

Output:

60,000
  1. Min(): The min() function is opposite to that of the max() function. It returns the minimum value from the particular column. 
SELECT MIN(SALARY)
FROM EMPLOYEE;

Output:

30,000
  1. Count(): Count() is used to count the number of records in the table. You can apply the count() function on all types of data in the table. It also considers NULL and duplicate data. If you wish to remove duplicate data, you have to add the DISTINCT keyword. 
SELECT COUNT(*) FROM EMPLOYEE

Output:

7

The count() function returns the number of types in the EMPLOYEE table. 

SELECT COUNT( DISTINCT CITY)
FROM EMPLOYEE;

Output:

5

Here, we used the DISTINCT keyword with the count() function on the CITY column. It returned the count of the distinct city names. You can also add the WHERE phrase with the count() function. 

SELECT COUNT(AGE)
FROM EMPLOYEE
WHERE AGE > 40;

Output:

4

We have got a number of employees whose age is greater than 40. 

Update Keyword

You can utilize the update keyword if you wish to modify the data present in the table. With the update keyword, you can also add the WHERE keyword. Using the WHERE keyword, you can update only those records that you desire to modify. If you do not add the WHERE keyword, all the records will be updated. You can write the update query in the following way:

UPDATE table-name
SET column1 = value1, column2 = value2, columnN = valueN
WHERE [condition];

If you need to apply the changes to two or more records, you can apply AND and OR operators. Take the EMPLOYEE table. We will update the age of an employee, having ID 405. We will set the age of 37 to 40. 

UPDATE EMPLOYEE
SET AGE = 40
WHERE EMP_ID = 405; 

When you update the age of an employee, having ID 405, this change will be reflected in the table. We shall know how the EMPLOYEE table will look after modifying. 

Output:

EMP_ID   NAME    AGE     CITY            SALARY
---------------------------------------------------------
401     James    45     Chicago          30,000
402     Johnny   34     Washington DC    45,000
403     Robert   55     California       60,000
404     Steve    44     Chicago          30,000
405     Robin    40     Los Angeles      40,000
406     Williams 46     New York         35,000
407     Mary     32     California       50,000

Now suppose you wish to change the salary and age of an employee having ID 407. We will set the salary to 55,000 and age to 35. 

UPDATE EMPLOYEE
SET AGE = 35, SALARY = 55,000
WHERE EMP_ID = 407;

Output:

EMP_ID   NAME    AGE     CITY            SALARY
------------------------------------------------
401     James    45     Chicago          30,000
402     Johnny   34     Washington DC    45,000
403     Robert   55     California       60,000
404     Steve    44     Chicago          30,000
405     Robin    37     Los Angeles      40,000
406     Williams 46     New York         35,000
407     Mary     35     California       55,000

The above table contains the updated data. 

DELETE Query

When you wish to delete any record from the table, the delete keyword is used. If you want to delete the specific tuple or row, add the WHERE keyword for specifying the condition. Combine several conditions using AND or OR operators. You can write the delete query as given below:

DELETE FROM table-name
WHERE [condition];

For example, take the EMPLOYEE table. We will delete the employee record having the employee ID 405. 

DELETE FROM EMPLOYEE
WHERE EMP_ID = 405;

The table will not include the employee record having the employee ID 405 after executing the above query. 

Output:

EMP_ID   NAME    AGE     CITY            SALARY
-------------------------------------------------
401     James    45     Chicago          30,000
402     Johnny   34     Washington DC    45,000
403     Robert   55     California       60,000
404     Steve    44     Chicago          30,000
406     Williams 46     New York         35,000
407     Mary     35     California       55,000

 If you desire to delete all the rows from the table, use the below query,

DELETE FROM EMPLOYEE;

After executing this query, only data will be deleted, not the table schema. 

ORDER BY Clause

You can order your column data in an ascending or descending order using the ORDER BY phrase. By default, the column data is arranged in ascending order. Follow the below format for writing the ORDER BY phrase. 

SELECT column-list
FROM table-name
[WHERE condition]
[ORDER BY column1, column2, ..., columnN] [ASC | DESC];

The column-list should contain all columns that you desire to sort or arrange in order. Take the same EMPLOYEE table for understanding this phrase with a simple example. We will add the column name, NAME, and SALARY to the column-list. 

SELECT * FROM EMPLOYEE
ORDER BY NAME, SALARY;

The table will be arranged according to the names in ascending order. 

Output:

EMP_ID   NAME    AGE     CITY            SALARY
-------------------------------------------------
401     James    45     Chicago          30,000
402     Johnny   34     Washington DC    45,000
407     Mary     35     California       55,000
403     Robert   55     California       60,000
405     Robin    37     Los Angeles      40,000
404     Steve    44     Chicago          30,000
406     Williams 46     New York         35,000

Now, we will generate the table in descending order using the NAME column. 

SELECT * FROM EMPLOYEE
ORDER BY NAME DESC;

Output:

EMP_ID   NAME    AGE     CITY            SALARY
------------------------------------------------
406     Williams 46     New York         35,000
404     Steve    44     Chicago          30,000
403     Robert   55     California       60,000
405     Robin    37     Los Angeles      40,000
407     Mary     35     California       55,000
402     Johnny   34     Washington DC    45,000
401     James    45     Chicago          30,000

GROUP BY Clause

For identical data grouping, The GROUP BY clause is used. The WHERE keyword is always followed by the GROUP BY clause, and the GROUP BY phrase is followed by the ORDER BY clause. It groups similar data into groups specified by the condition in the WHERE clause. The GROUP BY clause is written as follows:

SELECT column1, column2
FROM table-name
WHERE [condition]
GROUP BY column1, column2
ORDER BY column1, column2;

We will apply the GROUP BY phrase on the other table. Consider the below table EMPLOYEE1.

EMP_ID   NAME    AGE     CITY            SALARY
------------------------------------------------
401     James    45     Chicago          30,000
402     Johnny   34     Washington DC    45,000
403     Robert   55     California       60,000
404     Steve    44     Chicago          30,000
405     Robin    37     Los Angeles      40,000
406     Williams 46     New York         35,000
407     Mary     32     California       50,000
408     Steve    44     California       35,000

Here, Steve works in Chicago as well as California. Therefore, we can group Steve’s data using the GROUP BY phrase, which will display his name and overall salary. 

SELECT NAME, SUM(SALARY)
FROM EMPLOYEE1
GROUP BY NAME;

Output:

Name        Salary
-----------------------------------
James       30,000
Johnny      45,000
Robert      60,000
Steve       65,000
Robin       40,000
Williams    35,000
Mary        50,000

DISTINCT Keyword

The DISTINCT keyword removes all the duplicate values from the table. Many tables may contain redundant or duplicate data. In such situations, using the DISTINCT keyword will help to retrieve unique data. You can specify columns from which you need to fetch the unique data. The following is the form of writing the DISTINCT keyword:

SELECT DISTINCT column1, column2, ...columnN
FROM table-name
WHERE [condition];

For understanding how the DISTINCT keyword works, let us take the EMPLOYEE table. First, we will arrange the table in ascending order by the salary. 

SELECT SALARY
FROM EMPLOYEE
ORDER BY SALARY;

Output:

SALARY
--------------
30,000
30,000
35,000
40,000
45,000
55,000
60,000

In the above result set, there is duplicate data, i.e., 30,000 is present in two different rows. When we apply the DISTINCT keyword, the result will not contain any duplicate data. 

SELECT DISTINCT SALARY
FROM EMPLOYEE
ORDER BY SALARY;

Output:

SALARY
---------------
30,000
35,000
40,000
45,000
55,000
60,000

SQL JOIN

IN SQL, join combines records from two or more tables into a single table. A JOIN is a source for combining tables using the common columns in tables. We shall see an example of acknowledging how the JOIN is carried out. 

We will take two tables and join these two tables based on the common attribute between them. Take the EMPLOYEE table. We will create another table, DEPARTMENT. 

EMP_ID        DEPT                  POSITION
----------------------------------------------
401         Marketing                Manager
402         Finance                  Auditor
404         Sales                    Telemarketer
407         Marketing                Analyst
410         Finance                  Manager
412         Sales                    Manager

Now we have two tables. We will join these two tables and display the result set. There is a common attribute in both tables, EMP_ID. 

SELECT EMP_ID, NAME, AGE, CITY, SALARY, DEPT
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.EMP_ID =DEPARTMENT.EMP_ID;

The result is as follows:

EMP_ID NAME    AGE    CITY         SALARY        DEPT
--------------------------------------
401   James    45    Chicago       30,000      Marketing  
402   Johnny   34    Washington DC 45,000      Finance 
404   Steve    44    Chicago       30,000      Sales
407   Mary     32    California    55,000      Marketing

In the WHERE clause, we can join tables or relations using several operators: <, >, <=, >=, BETWEEN, !<, !>, NOT, etc. The join can be divided into several types. The INNER join joins tables and returns the result of the data that matches between the two relations. Another join is the LEFT join, which returns all the data from the left table. If there is no match for the left table with the right table, NULL is written. The RIGHT join produces all data from the right table. For the right table whose match is not available, write NULL. 

Left Join

The left join combines two relations having a common attribute in them. You can specify the columns to display from both the relations. All the data from the left table is present in the result set. For the right table data not matching with the left table data, NULL is written. We will see an example of the left join using EMPLOYEE and DEPARTMENT. 

EMP_ID   NAME    AGE     CITY            SALARY
---------------------------------------------------------
401     James    45     Chicago          30,000
402     Johnny   34     Washington DC    45,000
403     Robert   55     California       60,000
404     Steve    44     Chicago          30,000
405     Robin    37     Los Angeles      40,000
406     Williams 46     New York         35,000
407     Mary     32     California       50,000

DEPARTMENT

EMP_ID        DEPT                  POSITION
-----------------------------------------------
401         Marketing                Manager
402         Finance                  Auditor
404         Sales                    Telemarketer
407         Marketing                Analyst
410         Finance                  Manager
411         Sales                    Manager

We will now write the query for joining these two relations using the LEFT JOIN keyword. 

SELECT EMP_ID, NAME, DEPT, POSITION
FROM EMPLOYEE
LEFT JOIN DEPARTMENT
ON EMPLOYEE.EMP_ID = DEPARTMENT.EMP_ID;

Result:

EMP_ID   NAME       DEPT           POSITION  
---------------------------------------------
401      James      Marketing      Manager
402      Johnny     Finance        Auditor
403      Robert     NULL           NULL
404      Steve      Sale           Telemarketer
405      Robin      NULL           NULL
406      Williams   NULL           NULL
407      Mary       Marketing      Analyst

Right Join

The right join also joins two tables. When we apply the right join on two relations, it will display all the right table’s data matching with the left table. For the left table not matching with the right table data, NULL is written. We will take the same two relations, EMPLOYEE and DEPARTMENT, to understand the right join keyword. 

Output:

EMP_ID    NAME      AGE            DEPT   
------------------------------------------------- 
401       James      45           Marketing
402       Johnny     34           Finance
404       Steve      44           Sales
407       Mary       32           Marketing
410       NULL       NULL         Finance
411       NULL       NULL         Sales

Conclusion

A structured query language (SQL) is used by all the major databases: MySQL, MS Access, Oracle, etc. This language enables users to create tables in the database and manipulate them using several clauses, operators, statements. This article is the SQL quick reference for beginners. Novice users can learn all SQL basics from this tutorial. 

This post is the SQL quick reference, which will provide basic knowledge about manipulating the data in the database. We have seen all the basic syntaxes of DML, DDL, TCL, DQL, DCL commands. Later, we learned different clauses, data integrity, data types, operators, and expressions. In the later section, we have seen examples for creating, manipulating, and dropping the data in the database. 

Recommended Articles