SQL Introduction

sql training

Structured Query Language or SQL is the standard database language which is there for creating, maintaining and retrieving data from relational database. All relational database systems like MySQL, MS Access, Oracle, Sybase, SQL Server use SQL as standard database language which we can get training for.

In relation to this other different dialects are also being used some of which are given below:

  • Oracle by using the PL/SQL
  • MS SQL Server with the help of T-SQL
  • JET SQL for MS Access and so on
SQL USES:
  • It is there to create and drop databases and relations
  • There for describing the data
  • There for defining and manipulating the data
  • Used for accessing the data which is present in the relational databases
  • It can embed with other languages with the help of the libraries, pre compileras and the modules.
SQL PROCESS:

When we execute a command in SQL we deal with many components. The components are as follows Query dispatcher, optimization engine, SQL query engine, etc. Below we have an SQL Architecture

Introduction to SQL | SQL Training in Delhi and Gurgaon - PST Analytics

COMMANDS IN SQL:

In SQL for training with the relational databases we have some standard commands like CREATE, DROP, SELECT, INSERT, UPDATE and DELETE. According to the nature of the commands these are classified into the following groups.

Data Definition Language:

COMMAND DESCRIPTION
CREATE It is used for creation of objects in a database
ALTER It alters the structure of the database
DROP It is used for removing objects from the table
RENAME Used to rename an object

Data Manipulation Language:
COMMAND DESCRIPTION
INSERT It is use for inserting data into the table
UPDATE Used for updating existing data in the table
DELETE It is used for deleting records from the table

Data Control Language:
COMMAND DESCRIPTION
GRANT It offers a certain privilege to the user
REVOKE It takes the given privilege back from the user


Data Query Language:

COMMAND DESCRIPTION
SELECT Used for retrieving data from the database
CONCEPTS OF RDBMS:

In order to get training for SQL first of all we should understand the concept of Relational Database Management System or RDBMS. It is the basis on which many database systems like SQL, MySQL, Oracle, Microsoft Access and MS SQL Server rely on. It is based on the relational model introduced by E.F.Codd.

First we will look into some of the concepts of RDBMS.

TABLE:

In RDBMS the data is stored in the form of tables or relations which are basically database objects. A table or a relation is a collection of related entries. Now let us look at an example of a STUDENT TABLE.

ID NAME ADDRESS PHONE AGE
1 Manish Delhi 8879675645 24
2 Gopal Noida 9878787656 22
3 Rahim Gurgaon 7898675645 23
4 Gaurav Delhi 9785643423 25

Now one by one we will discuss the components of a table:

  • Attributes/Field – These are the properties that define a relation or a table. Example – ID, NAME, ADDRESS, PHONE and AGE are all fields.
  • Tuple/Row – These are the individual entries which exist in a table.
  • Column – It is a set of values which are under a particular attribute. Example in the above table the entries under the ID field is a column.
  • Degree – The number of attributes in a table is known as the degree of the table. Example the above table has a degree of 5.
  • Cardinality – It is the number of rows present in the table. Example the above table has cardinality 4.
  • Null value – It is the value in a field which is left blank. It has no value. A field containing 0 or spaces cannot be considered null. 
SQL CONSTRAINTS:

Constraints are the rules which keep a check on the type of data which goes into a table. It ensures that the data is accurate and reliable. Constraints are of two types Column level and Table level. Column level constraints are only confined to the columns whereas the Table level constraints are for the entire table.

Now we will look into the common constraints used in SQL.

NOT NULL:

A column Can hold null values by default but it can be changed. We need to define a constraint on the column specifying NULL is not allowed for this column.

CREATE TABLE STUDENT(

ID INT                 NOT NULL,

NAME VARCHAR (25)      NOT NULL,

ADDRESS CHAR (30),

PHONE INT

AGE INT                NOT NULL,

PRIMARY KEY (ID)

);

Incase the Table is already present and we need to modify it the following command will be used.

ALTER TABLE STUDENT

MODIFY ADDRESS CHAR (25) NOT NULL;

DEFAULT:

It is the constraint which gives a value when there is no value provided by the user using the INSERT INTO statement.

Example: Suppose we have to create a STUDENT Table and the AGE cannot be left blank and so a default value of 18 has to be provided for AGE field.

CREATE TABLE STUDENT(

ID INT                 NOT NULL,

NAME VARCHAR (25)      NOT NULL,

ADDRESS CHAR (30),

PHONE INT

AGE INT                NOT NULL, DEFAULT 18

PRIMARY KEY (ID)

);

In case the table STUDENT is already present and we need to modify the following command will be used.
ALTER TABLE STUDENT

MODIFY AGE INT DEFAULT 18

DROP DEFAULT:

If we want to DROP the DEFAULT which we have set the we will use the following command

ALTER TABLE STUDENT

ALTER COLUMN AGE DROP DEFAULT;

UNIQUE Constraint:

This constraint prevents repetition of values in a column. Example: In the STUDENT table if we want that two students should not have identical phone numbers then the following command will be used.

CREATE TABLE STUDENT(

ID INT                 NOT NULL,

NAME VARCHAR (25)      NOT NULL,

ADDRESS CHAR (30),

PHONE INT              UNIQUE,

AGE INT                NOT NULL, DEFAULT 18

PRIMARY KEY (ID)

);

In case the table is already present and we want to make changes the following command will be used.

ALTER TABLE STUDENT

MODIFY AGE INT UNIQUE;

DROP UNIQUE Constraint:

This is used for dropping a UNIQUE constraint

ALTER TABLE STUDENT

DROP CONSTRAINT myUniqueConstraint;

PRIMARY KEY:

Primary key is a field in the table which has unique values and no NULL values. It is used for identifying each record in the table. A table has only one primary key but can consist of single or multiple fields. A primary key consisting of multiple fields is known as a composite key.

Creating a Primary Key:

Below we have the command for creating a primary key ID for the table STUDENT.

CREATE TABLE STUDENT(

ID INT                 NOT NULL,

NAME VARCHAR (25)      NOT NULL,

ADDRESS CHAR (30),

PHONE INT

AGE INT                NOT NULL,

PRIMARY KEY (ID)

);

In case we already have a table and need to add a primary key use the following command

ALTER TABLE STUDENT ADD PRIMARY KEY (ID);

For creation of a composite PRIMARY KEY we will use the following command

CREATE TABLE STUDENT(

ID INT                 NOT NULL,

NAME VARCHAR (25)      NOT NULL,

ADDRESS CHAR (30),

PHONE INT

AGE INT                NOT NULL,

PRIMARY KEY (ID, NAME)

);

In case the STUDENT table exists beforehand use the following syntax

ALTER TABLE STUDENT

ADD CONSTRAINT PK_STID PRIMARY KEY (ID, NAME);

Delete Primary Key:

The primary key can be removed using the following syntax:

ALTER TABLE STUDENT DROP PRIMARY KEY;

FOREIGN KEY:

Foreign key or referencing key is used to create a link between two tables. This Key may be a column or a combination of columns whose value matches with the primary key of a different table.

Let’s look at an example to understand the concept.

Below are two tables which will help us understand the concept of FOREIGN key.

STUDENT TABLE

CREATE TABLE STUDENT(

ID INT                 NOT NULL,

NAME VARCHAR (25)      NOT NULL,

ADDRESS CHAR (30),

PHONE INT

AGE INT                NOT NULL,

PRIMARY KEY (ID)

);

FEES TABLE

CREATE TABLE FEES(

ID INT               NOT NULL,

DATE  DATETIME,

AMOUNT  double,

PRIMARY KEY  (ID)

);

Incase the FEES table is already created and the foreign key is not specified then we will use the following syntax:

ALTER TABLE FEES

ADD FOREIGN KEY (Student_ID) REFERENCES STUDENT (ID);

DROP FOREIGN KEY:

In order to DROP a FOREIGN KEY we will use the following syntax

ALTER TABLE FEES

DROP FOREIGN KEY;

CHECK Constraint:

This constraint is useful in order to check the value which is being entered into the table. If the condition is not fulfilled i.e. if the condition is false then the constraint is violated and the value is not entered into the table.

Let us look into an example to understand this better.

In the previous table i.e. the STUDENT table suppose we need to set the age limit to 18 years. In order to achieve this, we will use the following syntax

CREATE TABLE STUDENT(

ID INT                 NOT NULL,

NAME VARCHAR (25)      NOT NULL,

ADDRESS CHAR (30),

PHONE INT

AGE INT                NOT NULL CHECK (AGE >=18),

PRIMARY KEY (ID)

);

In case the STUDENT table has already been created we will use the following syntax:

ALTER TABLE STUDENT,

MODIFY AGE INT NOT NULL CHECK (AGE >= 18);

DROP CHECK Constraint:

In order to drop a CHECK constraint, we use the following syntax:

ALTER TABLE STUDENT

DROP CONSTRAINT myCheckConstraint;

INDEX:

INDEX is used for creating and retrieving data from the database at a quick pace. It can be created by using a single or a group of columns in a table. Whenever we create an index an unique ROWID is assigned for each row before sorting out the data.

Let us look at an example to understand it better. We will create a table STUDENT.

CREATE TABLE STUDENT(

ID INT                 NOT NULL,

NAME VARCHAR (25)      NOT NULL,

ADDRESS CHAR (30),

PHONE INT

AGE INT                NOT NULL,

PRIMARY KEY (ID)

);

For creating an INDEX on the AGE column, we will use the following syntax:

CREATE INDEX idx_age

ON CUSTOMERS (AGE);

DROP INDEX Constraint:

In order to drop an INDEX constraint, we will use the following syntax:

ALTER TABLE STUDENT

DROP INDEX idx_age;

DATA INTEGRITY:

In RDBMS we have different categories of data integrity. The different categories are listed below.

  • Entity Integrity – It ensures that there are no duplicate rows in a table.
  • Domain Integrity – It sees to it that the entries are valid in a table by restricting the format, type and the range of the values.
  • Referential Integrity – Ensures that the rows which are used by other records cannot be deleted.
  • User-Defined Integrity – It is used to enforce that some specific rules are followed which do not fall into the above three integrities.
RDBMS DATABASES IN SQL:

We have many popular RDBMS databases but here we will be discussing some of them in order to compare them and find out their features.

MySQL:

Developed by the Swedish company MySQL AB it is an open source database. It supports a large number of different platforms like UNIX, MAC OS X, Microsoft Windows and some of the major Linux distributions. MySQL has a robust, multi-thread, multi-user and fast database server.

Now we will discuss some of the features of MySQL:

  • It is an open source database and has a 24×7 support.
  • Its availability is very high.
  • Has a high performance.
  • It provides a strong data protection.
  • It is easy to manage.
  • Gives us a robust support for transactions.
  • It is scalable and can run anything because of its flexibility.
  • Provides us with a strong web and data warehouse storage system.
  • The total ownership cost is the lowest.
MS SQL SERVER:

It is a RDBMS developed by Microsoft having the primary query languages T-SQL and ANSI SQL.

Now let us see some of the features of MS SQL Server:

  • It has a high performance.
  • It has a high availability.
  • We can take snapshots of the database.
  • The option of database mirroring is also provided.
  • It has a ranking function.
  • The MS SQL Server comes with CLR integration and XML integration.
  • It has DDL triggers.
  • It provides encryption in rest, transit and use.
  • We can control the access in this.
  • In Linux it has a disaster recovery system.
  • It provides a adaptive query processing.
  • It has the least critical database.
ORACLE:

It was developed by the Oracle Corporation. It has a multi-user and large database management system. If you are doing client/server computing it is the best choice as a database server. It efficiently manages its resources and a database of information within a network of multiple clients requesting and sending the data.

It supports major OS for both the server and the client such as UnixWare, Netware, MSDOS and so on. Now we will look at some of the features of ORACLE.

  • It provides portability.
  • It provides for reading consistency.
  • Helps in Analytical SQL.
  • It provides for data mining applications.
  • It has the feature of parallel execution.
  • Partitioning is also one of its features.
  • The database is self managing in nature.
  • It has a locking mechanism.
  • Provides a materialized view.
  • It has the feature which helps in compressing the tables.
MS ACCESS:

It is developed by Microsoft and a very popular database system. It’s a database management system for entry level. It is suitable for small scale projects and is inexpensive in nature. It uses Jest database engine and uses SQL dialect JET SQL. As it is of entry level it is easy to use and has a intuitive graphical interface.

Some of the features of MS Access are given below.

  • It is possible to import and export data to and from a large number of formats such as Excel, ASCII, Outlook, Oracle, etc.
  • It is possible to run this in disconnected environment as the data and the application are kept in one file.
  • It’s possible to create tables, queries, forms and reports. We can also connect them using macros function.
  • It provides us with parameterized queries.
  • It is a file-server based database and not a client-server based one.
SQL SYNTAX:

Syntax is a set of rules and guidelines in SQL for training. The SQL statements have one of these at the start INSERT, UPDATE, SELECT, DELETE, ALTER, DROP, USE, CREATE, SHOW. The statements end with a semicolon (;).

SQL is case insensitive but in case of MySQL it makes difference in table names i.e. table names should be what they are in the database.

SQL SELECT Statement and training:

SELECT column1, column2….columnN

FROM table_name;

SQL DISTINCT Clause and training:

SELECT DISTINCT column1, column2….columnN

FROM table_name;

WHERE Clause and training:

SELECT column1, column2….columnN

FROM table_name

WHERE CONDITION;

AND/OR Clause and training:

SELECT column1, column2….columnN

FROM table_name

WHERE CONDITION-1 {AND|OR} CONDITION-2;

IN Clause and training:

SELECT column1, column2….columnN

FROM table_name

WHERE column_name IN (val-1, val-2,…val-N);

BETWEEN Clause and training

SELECT column1, column2….columnN

FROM table_name

WHERE column_name BETWEEN val-1 AND val-2;

LIKE Clause and training:

SELECT column1, column2….columnN

FROM table_name

WHERE column_name LIKE { PATTERN };

ORDER BY Clause and training:

SELECT column1, column2….columnN

FROM table_name

WHERE CONDITION

ORDER BY column_name {ASC|DESC};

GROUP BY Clause and training:

SELECT SUM(column_name)

FROM table_name

WHERE CONDITION

GROUP BY column_name;

COUNT Clause and training:

SELECT COUNT(column_name)

FROM table_name

WHERE CONDITION;

HAVING Clause and training:

SELECT SUM(column_name)

FROM table_name

WHERE CONDITION

GROUP BY column_name

HAVING (arithematic function condition);

CREATE TABLE Statement and training:

CREATE TABLE table_name(

column1 datatype,

column2 datatype,

column3 datatype,

…..

columnN datatype,

PRIMARY KEY( one or more columns )

);

Code 2 –

SQL DROP TABLE Statement and training:

DROP TABLE table_name;

CREATE INDEX Statement and training:

CREATE UNIQUE INDEX index_name

ON table_name ( column1, column2,…columnN);

DROP INDEX Statement and training:

ALTER TABLE table_name

DROP INDEX index_name;

DESC Statement and training:

DESC table_name;

TRUNCATE TABLE Statement and training:

TRUNCATE TABLE table_name;

ALTER TABLE Statement and training:

ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype};

sql ALTER TABLE Statement (Rename):

ALTER TABLE table_name RENAME TO new_table_name;

SQL INSERT INTO Statement and training:

INSERT INTO table_name( column1, column2….columnN)

VALUES ( value1, value2….valueN);

SQL UPDATE Statement:

UPDATE table_name

SET column1 = value1, column2 = value2….columnN=valueN

[ WHERE CONDITION ];

DELETE Statement:

DELETE FROM table_name

WHERE {CONDITION};

CREATE DATABASE Statement:

CREATE DATABASE database_name;

DROP DATABASE Statement:

DROP DATABASE database_name;

USE Statement:

USE DATABASE database_name;

COMMIT Statement:

COMMIT;

ROLLBACK Statement:

ROLLBACK;

DATA TYPES IN SQL:

In SQL all objects are specified by different data types. Whether it be a column, variable or expression it has a related data type in SQL. Dta types are choosen based on the requirement of the user.

We have six data type categories which will be discussed one by one below.

Exact Numeric Data Types:
DATA TYPE FROM TO
Bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807
Int -2,147,483,648 2,147,483,647
Smallint -32,768 32,767
Tinyint 0 255
Bit 0 1
Decimal -10^38 +1 10^38 -1
Numeric -10^38 +1 10^38 -1
Money -922,337,203,685,477.5808 +922,337,203,685,477.5807
Smallmoney -214,748.3648 +214,748.3647
Approximate Numeric Data Types:
DATA TYPE FROM TO
Float -1.79E + 308 1.79E + 308
Real -3.40E + 38 3.40E + 38

Date and Time Data Types:
DATA TYPE FROM TO
Datetime Jan 1, 1753 Dec 31, 9999
Smalldatetime Jan 1, 1900 Jun 6, 2079
Date Used for storing a date
Time Used for storing a time of the day

Character Strings Data Types:
DATA TYPE FROM TO
Char Char Maximum length of 8,000 characters. ( Fixed length non-Unicode characters)
Varchar Varchar Maximum of 8,000 characters. (Variable-length non-Unicode data).
Varchar(max) Varchar(max) Maximum length of 231characters, Variable-length non-Unicode data (SQL Server 2005 only).
Text text Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.

Unicode Character Strings Data Type:
DATA TYPE DESCRIPTION
Nchar Maximum length of 4,000 characters. ( Fixed length Unicode)
Nvarchar Maximum length of 4,000 characters. (Variable length Unicode)
Nvarchar(max) Maximum length of 231characters (SQL Server 2005 only). ( Variable length Unicode)
Ntext Maximum length of 1,073,741,823 characters. (Variable length Unicode)

Binary Data Types:
DATA TYPE DESCRIPTION
Binary Maximum length of 8,000 bytes (Fixed-length binary data)
Varbinary Maximum length of 8,000 bytes. (Variable length binary data)
Varbinary(max) Maximum length of 231 bytes (SQL Server 2005 only). (Variable length Binary data)
Image Maximum length of 2,147,483,647 bytes. (Variable length Binary Data)

Miscellaneous Data Types:
DATA TYPE DESCRIPTION
Sql_variant Stores values of various SQL Server-supported data types, except text, ntext, and timestamp
Timestamp Stores a database-wide unique number that gets updated every time a row gets updated
Uniqueidentifier Stores a globally unique identifier (GUID)
Xml Stores XML data. You can store xml instances in a column or a variable (SQL Server 2005 only).
Cursor Reference to a cursor object
Table Stores a result set for later processing
SQL OPERATORS:

Operators are special characters used for SQL statement to perform a certain operation. It is used for specifying conditions in SQL statements. They also serve as a conjunction for many conditions in the statements.

Now we will look at some of the operators in SQL.

ARITHMETIC OPERATORS:

These are the operators which are used for arithmetic functions like addition, subtraction, multiplication, division, etc. Below we will discuss the operators with examples. In the examples we have taken two variables x and y which holds 20 and 10 respectively.

OPERATOR DESCRIPTION EXAMPLES
+ Addition operator adds up the left hand and the right hand operand x + y gives 30
Subtraction operators deducts the right hand operand from the left one x – y gives 10
/ Division operators divides the operand on the left side of operator with the right one x / y gives 2
* Multiplication operator multiplies both the values x * y gives 200
% Modulus operator divides the operand on the left hand side with the right one and gives back the remainder x % y gives 0
COMPARISON OPERATORS:

These are the operators which are used for comparing two values. In the following example two variables are taken x and y which holds 20 and 10 respectively.

OPERATOR DESCRIPTION EXAMPLES
= This operator checks whether two operands are equal or not (x=y) holds not true
!= This operator checks whether two operands are equal or not, in case values are not equal it returns true (x!=y) holds true
<>  This operator checks whether two operands are equal or not, in case values are not equal it returns true (x<>y) holds true
This operator checks whether the left hand operand value is greater than the right one, in case it is it will return true (x>y) holds true
This operator checks whether the left hand operand value is lesser than the right one, in case it is it will return true (x<y) holds not true
>= This operator checks whether the left hand operand is greater or equal to the right hand operand, in case it is yes then the result is true (x>=y) holds true
<= This operator checks whether the left hand operand is lesser or equal to the right hand operand, in case it is yes then the result is true (x<=y) holds not true
!< This operator checks whether the left hand operand is not lesser than the right hand operand, in case it is yes then the result is true (x!<y) holds true
!> This operator checks whether the left hand operand is not greater than the right hand operand, in case it is yes then the result is true (x!>y) holds false
LOGICAL OPERATORS:

These are used for performing multiple operations like checking SQL statements depending on conditions like AND, OR, Between, etc. It can also be used for comparison of different statements of SQL.

OPERATOR DESCRIPTION
ALL This operator is used for comparing one value to all values present in a different dataset.
AND This operator is used for comparing multiple conditions in case all conditions are true then the result will be displayed
ANY This is used for comparing a value to any other applicable value according to the condition
BETWEEN It is used for searching values present within a set of values when the minimum and maximum values are given
EXISTS This operator searches for a row in a specific table which can fulfill a given criteria
IN This operator compares a value to the specified list of literal values
LIKE This operator uses wildcard operators and compares a value to similar values
NOT This operator is a negative. It is used for reversing the meaning of a logical operator
OR Used for combining multiple conditions
IS NULL Used when we have to compare a value to a null one
UNIQUE It is used for searching for unique i.e one which has no duplicates in a specific table
EXPRESSIONS IN SQL:

Expressions in SQL are a combination of one or more values, operators and SQL functions which evaluate to a value. These are similar to formulas and are written in query language. They can also be used for querying database for a specific set of data.

We will consider the following syntax for the SELECT statement

SELECT column1, column2, columnN

FROM table_name

WHERE [CONDITION|EXPRESSION];

We have the following types of SQL expressions:

BOOLEAN EXPRESSIONS:

These expressions match a single value and on that basis takeout the data. The following syntax is used for Boolean expressions:

SELECT column1, column2, columnN

FROM table_name

WHERE SINGLE VALUE MATCHTING EXPRESSION;

NUMERIC EXPRESSIONS:

These expressions are used for performing mathematical operations. The following syntax is used for numeric expressions:

SELECT numerical_expression as OPERATION_NAME

[FROM table_name

WHERE CONDITION] ;

DATE EXPRESSIONS:

These return the current system date and time. We have two date expressions:

SQL> SELECT CURRENT_TIMESTAMP;

Another one is,

SQL> SELECT GETDATE () ;;

CREATE DATABASE:

The statement CREATE DATABASE is used for creating a new database in SQL. Now we will look at the syntax of creating a database with the help of an example. Suppose we want to create a database <studentDB> then it will be created in the following way.

SQL> CREATE DATABASE studentDB;

We should also keep in mind that for creating a database we should have admin privileges.The created database can be checked as follows:

SQL> SHOW DATABASES;

DROP DATABASE:

The DROP database statement is used for deleting an existing database in SQL. We should keep in mind that the database name should be unique within the RDBMS.

The syntax used for deleting a database can be explained using an example. Let us consider we have to delete the database <studentDB> which we created in the previous example, we will use the following statement:

SQL> DROP DATABASE studentDB;

Before deleting a database it should be known that if we delete a database all the information will be lost. Also admin privileges are required for deletion of a database.

In order to check whether the database is dropped we will use the following method:

SQL> SHOW DATABASES;

DROP DATABASE:

The statement DROP DATABASE is used for deleting a database which is already present in the SQL.

The basic syntax which is used for deleting a database is as follows:

DROP DATABASE Nameofdatabase;

Let us now look into this with an example:

Suppose we want to delete the database which we created <studentDB>, in this case we will use the following statement:

SQL> DROP DATABASE studentDB;

It should be noted that dropping or deleting a database will result in loss of complete data in the database dropped. Also admin privileges are necessary for dropping a database.

SELECT DATABASE:

In cases where there are multiple databases present in the SQL schema, we have to select a database at the start of the operation to specify on which database the operations will be performed.

In order to select a database, we will use the USE statement. The syntax for selecting a database is given below:

USE NameofDatabase;

CREATE TABLE:

In order to create a table we have to name it and define the columns and each data type in the columns. To create a table we use the CREATE TABLE statement.Introduction to SQL | SQL Training in Delhi and Gurgaon - PST Analytics

Now we will look at how to create a copy of an existing table by using a combination of CREATE TABLE and SELECT statements. The syntax which is used is as follows:Create Table

Now let us look at an example where we create a STUDENT table where ID is the primary key.

CREATE TABLE STUDENT(

ID INT                 NOT NULL,

NAME VARCHAR (25)      NOT NULL,

ADDRESS CHAR (30),

PHONE INT

AGE INT (11)           NOT NULL,

PRIMARY KEY (ID)

) ;

In order to verify whether the table has been created or not we will use the following statement:

SQL> DESC CUSTOMERS;

+———+—————+——+—–+———+——-+

| Field   | Type          | Null | Key | Default | Extra |

+———+—————+——+—–+———+——-+

| ID      | int           | NO   | PRI |         |       |

| NAME    | varchar(25)   | NO   |     |         |       |

| ADDRESS | char(25)      | YES  |     | NULL    |       |

| PHONE   | int           | YES  |     | NULL    |       |

| AGE     | int(11)       | NO   |     |         |       |
+———+—————+——+—–+———+——-+

Now we will use this table to create another table NAME having the fields ID and NAME.

SQL> CREATE TABLE NAME AS

SELECT ID, NAME

FROM STUDENT;

This creates a table NAME which has the ID and Name of the students from the STUDENT table.

+—-+———-+

| ID | NAME     |

+—-+———-+

| 1  | Manish   |

| 2  | Gopal    |

| 3  | Rahim    |

| 4  | Gaurav   |

+—-+———-+

DROP TABLE:

This command is used when we have to delete a table including all the data present in the table, constraints, triggers, permissions and indexes.

The syntax which is used for dropping a table is as follows:

DROP TABLE name_table;

Now we will drop the STUDENT table from the database:

SQL> DROP TABLE STUDENT;

Query OK, 0 rows affected (0.01 sec)

Now if we use the DESC command we will get an error as follows:

SQL> DESC STUDENT;

ERROR 1146 (42S02): Table ‘TEST.STUDENT’ doesn’t exist

INSERT QUERY:

INSERRT INTO statement is used for inserting new rows in the table.

Now let us look at an example to understand how to insert rows into a table.

Suppose we have to add an extra row to the STUDENT table which we created earlier, we will use the following syntax:

INSERT INTO STUDENT (ID,NAME,ADDRESS,PHONE,AGE)

VALUES (1, ’Manish’, ’Delhi’, 8879675645, 24);

INSERT INTO STUDENT (ID,NAME,ADDRESS,PHONE,AGE)

VALUES (2, ’Gopal’, ’Noida’, 9878787656, 22);

INSERT INTO STUDENT (ID,NAME,ADDRESS,PHONE,AGE)

VALUES (3, ’Rahim’, ’Gurgaon’, 7898675645, 23);

INSERT INTO STUDENT (ID,NAME,ADDRESS,PHONE,AGE)

VALUES (4, ’Gaurav’, ’Delhi’, 9785643423, 25);

+—-+———-+———+————+—–+

| ID | NAME     | ADDRESS | PHONE      | AGE |

+—-+———-+———+————+—–+

| 1 | Manish    | Delhi   | 8879675645 | 24  |

| 2 | Gopal     | Noida   | 9878787656 | 22  |

| 3 | Rahim     | Gurgaon | 7898675645 | 23  |

| 4 | Gaurav    | Delhi   | 9785643423 | 25  |

+—-+———-+———+————+—–+

SELECT QUERY:

SELECT is used for fetching data present in the table in a database. The data is returned in the form of a table and it is called a result set.

The syntax used for SELECT statement is as follows:

SELECT QUERY

In the above statement we have specified the fields we want to select. We can also select the wholetable using the following syntax,

SELECT QUERY

WHERE CLAUSE:

WHERE is used when we have to specify a condition when we are fetching data from a table or we are joining multiple tables. This clause is ised for filtering data from the table and only obtaining useful information. In case the condition is not satisfied we will not get any result from the table, result will be displayed only if the condition is satisfied.

The WHERE statement can be used with SELECT, UPDATE, DELETE statements. Now we will see the syntax for using WHERE clause with SELECT statement.WHERE CLAUSE

AND & OR Operators:

The AND and the OR operators are known as conjunctive operators. These are used for combining multiple conditions in order to make the SQL statements shorter. Now we will look at them individually.

AND Operator:

This operator makes the existence of more than one conditions in an SQL statement when we are using WHERE clause. The basic syntax we use is as follows:AND Operator

OR Operator:

This operator comes into play when we have to combine many statements. The syntax used for this is as follows:OR Operator

UPDATE QUERY:

This statement is used when we already have a table and we want to modify it. By default all the rows of the table is affected but we cam use the WHERE clause combined with the UPDATE statement in order to modify only a selected rows.

The syntax used for modifying a table is a s follows;UPDATE QUERY

In case we want to update the whole table we will remove the WHERE condition from the syntax.

DELETE QUERY:

This query is used when we have to delete the records which are already present in a table. In case we have to delete selected rows we take the help of WHERE clause or all records are deleted by default.

The syntax used for deleting data from tables is as follows:

DELETE QUERY

In case we have to delete the whole records from the table just remove the WHERE condition from the syntax.

LIKE Clause:

The LIKE clause is used when we have to compare one value to similar values with the help of special operators called wildcard operators. There are two wildcard operators % (percentage) and _ (underscore) which are used along with LIKE  clause.

‘%’ represents either zero, one or multiple characters whereas the’ _’ represents a number or character. We can combine the wildcard operators.

The syntax used while using % and _ are as follows:

Introduction to SQL | SQL Training in Delhi and Gurgaon - PST Analytics

TOP Clause:

The TOP clause is used for obtaining a certain number or percentage of TOP records from the table. We should remember that all databases does not have the TOP function. MySQL has LIMIT clause whereas Oracle has ROWNUM for obtaining a specific number of records.

The syntax used for TOP clause along with SELECT statement is as follows:TOP Clause

ORDER BY Clause:

We use the ORDER BY clause in SQL for sorting data either in ascending or descending order. In some cases, the data is sorted in ascending order by default.

The syntax for using ORDER BY clause is as follows,

ORDER BY Clause

GROUP BY:

When we have identical data and we have to group them we use the GROUP BY clause along with the SELECT statement.

The GROUP BY statement comes before the ORDER BY clause and after the WHERE clause. The syntax used for GROUP BY statement is as follows,

Introduction to SQL | SQL Training in Delhi and Gurgaon - PST Analytics

DISTINCT KEYWORD:

When we have duplicates in the table and we have to remove them we use the DISTINCT keyword along with the SELECT statement. It is also used for fetching the unique records.

The syntax used for fetching unique records is as follows,

DISTINCT KEYWORD

JOINS in SQL:

When we have to combine records from multiple tables, we use the JOIN clause. It uses the common values in order to combine fields from different tables.

Now we will look at the types of joins which are available in SQL.

TYPES OF JOINS:

There are Many different types of joins present in SQL. These are discussed below one by one.

INNER JOIN:

This is one of the most frequently used and important joins. Also, also known as EQUIJOIN. This join combines two tables and gives a result table depending on the join predicate. In case the join predicate is satisfied, the column values of the matched pair of rows of two tables is combined into a result row.

The syntax which is used for INNER JOIN is as follows:

Introduction to SQL | SQL Training in Delhi and Gurgaon - PST Analytics

LEFT JOIN:

This join returns all the values present in the left table irrespective of it matches with the right table or not. This also implies that the LEFT JOIN will return all the values from the left table and the values matched from the right table.

The syntax used for LEFT JOIN is as follows,

Introduction to SQL | SQL Training in Delhi and Gurgaon - PST Analytics

RIGHT JOIN:

This is opposite of what LEFT JOIN was. In this all the values from the right table will be returned irrespective of matches in the left table. This also means that it will return all values from the right table and also include the values which are matched from the left table.

The syntax used for RIGHT JOIN is as follows,

Introduction to SQL | SQL Training in Delhi and Gurgaon - PST Analytics

FULL JOIN:

This join combines the results which are obtained from both the left and right outer joins. It contains the records from both the tables and in case of NULL values they are filled.

The syntax used for applying FULL JOIN is as follows,

Introduction to SQL | SQL Training in Delhi and Gurgaon - PST Analytics

SELF JOIN:

In SQL SELF JOIN joins a table to itself treating the tables as two tables. The duolicate table is renamed temporarily.

The syntax used for SELF JOIN is as follows,

SELF JOIN

CARTESIAN JOIN:

CARTESIAN JOIN is also known as CROSS JOIN. This join will return cartesian product of sets of records from the joined tables. This join equates to inner join in case the join condition is satisfied (TRUE) or when join condition is not present.

The syntax used for INNER JOIN is as follows,

Introduction to SQL | SQL Training in Delhi and Gurgaon - PST Analytics

Leave a Reply

Your email address will not be published. Required fields are marked *