SQL (Structured Query Language) For Test Engineer

           

This is a standard language for accessing the database which is used for retrieval and management of data. It includes database creation, deletion, fetching rows and modifying rows etc.

SQL is the standard language for Relation Database System.

DBMS and RDBMS:

DBMS: Database Management System is where the data is stored in the form of Flat files and having a Parent Child relationship. It’s not that in DBMS, the data cannot be stored in tables, but it is that even though the data is stored in tables, it will not have any relation between them

RDBMS: Relational Database Management System is where the data is always stored in the form of tables. The table is a collection of related data entries and it consists of columns and rows.

 DDL Commands:

Command	Description
CREATE	Creates a new table, a view of a table, or other object in database
ALTER	Modifies an existing database object, such as a table.
DROP	Deletes an entire table, a view of a table or other object in the database.

 DML Commands:

Command	Description
INSERT	Creates a record
UPDATE	Modifies records
DELETE	Deletes records

DQL Commands:

Command	Description
SELECT	Selects a dataset based on the conditions

1.1 DDL Commands: Data Definition Language (DDL) is a part of SQL that is used to create, modify, and delete database objects such as table, view, and index. Below are the most common DDL commands:

1.1.1  CREATE TABLE: Every data is stored in a table in the database. The way we do it is by CREATE TABLE statement. A table is made up of rows and columns. Each row represents one piece of data, and each column can be thought of as representing a component of that piece of data. 

The Syntax for creating the table is as below:

CREATE TABLE <TABLE_NAME>
(<COLUMN 1><DATATYPE>CONSTRAINTS,
(<COLUMN 1><DATATYPE>CONSTRAINTS,
... )

Below is the Example:

CREATE TABLE Customer
(Customer_Idint,

First Name char(50),
Last Name char(50),
Address char(50),
City char(50),
Country char(25,
Birth Datedatetime);

Six types of constraints can be placed when creating a table:

o   NOT NULL Constraint: Ensures that a column cannot have NULL value.

o   DEFAULT Constraint: Provides a default value for a column when none is specified.

o   UNIQUE Constraint: Ensures that all values in a column are different.

o   CHECK Constraint: Makes sure that all values in a column satisfy certain criteria.

o   Primary Key Constraint: Used to uniquely identify a row in the table.

o   Foreign Key Constraint: Used to ensure referential integrity of the data.

1.1.2 CREATE VIEW: View is a virtual table. A view consists of rows and columns just like a table. The difference between a view and a table is that views are built on top of other tables and do not hold data themselves. If data is changing in the underlying table, the same changes are reflected in the view. A view can be built on top of a single table or multiple tables. 

The Syntax for Creating a View is as below:

CREATE VIEW <VIEW NAME>

AS SELECT * FROM <TABLENAME>

Below is the Example:

CREATE VIEW Customer_VW 
As

Select * from Customer

1.1.3 CREATE INDEX: The INDEX is used to create and retrieve data from the database very quickly. Index can be created by using single or group of columns in a table. When index is created, it is assigned a ROWID for each row before it sorts out the data. Proper indexes are good for performance in large databases, but you need to be careful while creating index. Selection of fields depends on what you are using in your SQL queries.

The Syntax for Creating and Index is as below:

CREATE INDEX<VIEW NAME>

ON <TABLE_NAME>(COLUMN_NAME1,COLUMN_NAME2,…)

Below is the Example:

CREATE INDEX idx1 on Customer(birth_date)

1.1.4 ALTER TABLE: The ALTER TABLE command is used to add, delete or modify columns in an existing table. You would also use ALTER TABLE command to add and drop various constraints on an existing table.

The constraints that are explained above can also be used for creating as well for altering.

The Syntax for Alter Table is as below:

ALTER TABLE <TABLE_NAME> [ADD|DROP|MODIFY] COLUMN_NAME [DATATYPE]

Below is the Example:

ALTER TABLE Customer ADD Age int

ALTER TABLE Customer DROP COLUMN Age

ALTER TABLE Customer MODIFY COLUMN Age varchar(2)

The same ADD/DROP/MODIFY commandsare used to modify the constraints that are required

1.1.5          ALTER VIEW: The ALTER VIEW command is used to alter the query that was used to create a view

The Syntax for Alter View is as below:

ALTER VIEW <VIEW_NAME>As

SELECT QUERY ON TABLES

Below is the Example:

ALTER VIEW as Select Customer_id, Age from Customer

1.1.6          ALTER INDEX: You cannot alter the index by using any command. All you have to do is to drop the index first and then recreate the index based on the requirement

1.1.7          DROP TABLE: Sometimes we need to clear off the table from the database where it is not necessary. This is when we need to get rid of the table from the database which helps DBA in maintenance activities.

The Syntax for DROP TABLE is as below:

DROP TABLE<TABLENAME>

Below is the Example:

DROP TABLE Customer

DROP TABLE Customer

1.1.8          DROP VIEW: When you have a view in the database, you need a way to drop the view if it is no longer needed.

The syntax is as given below:

DROP VIEW <VIEW_NAME>

Below is the Example:

DROP VIEW Customer_VW

1.1.9          DROP INDEX: An index can be dropped if you don’t require the index on a particular column or if you want to alter the index. Care should be taken when dropping an index because performance may be slowed or improved.

The syntax is as given below:

DROP INDEX <INDEX_NAME>

Below is the Example:

ALTER TABLE Customer

DROP INDEX idx1

1.2 DML Commands: Data Manipulation Language (DML) is used for managing data in the database. These commands can be rolled back. But in SQL they are committed unless you write them in a transaction.

1.2.1          INSERT: This command is used to insert the data into the table.

The syntax for the INSERT command is as below:

INSERT INTO <TABLENAME>VALUES (Value1,Value2…etc)

Below is the Example:

INSERT INTO CUSTOMER values(‘Rahul’,’A’,’ADDRESS’,’HYDERABAD’,’INDIA’,1985-01-01’)

1.2.2          UPDATE:

This command updates the already existing rows in the table.

The Syntax for the Update command is as below:

UPDATE <TABLENAME> set <COLUMN>=Value

Below is the Example:

UPDATE CUSTOMER SET CUSTOMER_NAME=’RAJU’ WHERE CUSTOMER_NO=1001

 1.2.3 DELETE:  This command deletes the rows from the table.

Syntax for this command is as below:

DELETE FROM <TABLENAME>

Below is the Example:

DELETE FROM CUSTOMER

1.2.4 TRUNCATE TABLE: Sometimes we need to delete all the data in the table when we don’t need the particular data.

Syntax for TRUNCATE TABLE is as below:

TRUNCATE TABLE<TABLENAME>

Below is the Example:

TRUNCATE TABLE Customer

Difference between DELETE and TRUNCATE:

Both of these statements deletes the rows in the table but Delete can also use with the condition where it can delete some rows that satisfies the condition.

But there is one more difference in these two statements where the DELETE requires more system resources, and hence takes longer to complete, because the RDBMS has to record all changes one row at a time in the transaction log, while a TRUNCATE TABLE operation does not record the change one row at a time, so it can be completed quicker. 

Difference between DROP and TRUNCATE:

DROP TABLE will delete the table from the database which means the table will not be physically existing in the database whereas TRUNCATE will delete all the rows in the table and table will physically exist in the database.

1.2.5 Except: This is used to find the difference between the dataset in between two tables.

The Syntax is as below:

Select <Columns>from table_name

EXCEPT

Select <Columns>from table_name

Below is the Example:

Select * from Customer

Except

Select * from Employee

1.3 DQL Commands: Data Query Language (DQL) is used for selecting data from the database. This command is used to select the data from the tables that are present on the Database even by putting the conditions.

1.3.1          SELECT:

This command is used to select the data from one or more tables.

The syntax for the SELECT command is as below:

SELECT <COLUMNS_NAME1>,….<COLUMN_NAMEn>

FROM

<TABLE_NAME>

Below is the Example:

SELECT * from Customer

SELECT Customer_Id,FirstName,Last Name from Customer

The Select Statement can have many clauses that can be used to fetch the correct data for the Requirement.

1.3.2          TOP Clause: This Clause will pick up the Top n number of rows from the Table in the Select statement

The syntax is as below:

SELECT TOP(required number of rows) column1, column2....columnNFROMtable_name

Below is the Example:

SELECT TOP(100) First Name, Last Name from Customer

To select all the rows from the table the query can be written as below

SELECT TOP(100)*  from Customer

Here * denotes all the columns in the Customer Table

1.3.3          DISTINCT Clause: This Clause as the name itself gives the distinct values from the Table in the Select statement.

The syntax is as below:

SELECT DISTINCT column1, column2....column FROM table_name

Below is the Example:

SELECT DISTINCT First Name, Last Name from Customer

1.3.4          WHERE Clause: This Clause is used for fetching the data based on a certain condition.

The Syntax is as below:

SELECT column1, column2....columnN FROM table_name WHERE CONDITION

Below is the Example:

SELECT Customer_Id, First Name, Last Name from Customer where  Age>18

There can also be n number of conditions that can be Specified in the where clause where the data set that comes out as an output satisfies all the conditions OR a certain number of Conditions when we use a AND /OR clause in between the conditions

1.3.5          IN Clause:  This Clause is used to fetch the dataset from the Where clause where the data set contains the data present in the IN Clause.

The Syntax is as below:

SELECT column1, column2....columnN FROM table_name

WHERE column_name IN (val-1, val-2,...val-N)

Below is the Example:

SELECT Customer_Id, First Name, Last Name from Customer where  Age  IN (18,20,21)

1.3.6          BETWEEN Clause: This Clause is used to fetch the dataset from the Where clause where the Data output is in between the given Values inclusive of the Start and the End value.

The Syntax is as below:

SELECT column1, column2....columnNFROMtable_name

WHERE column_name BETWEEN val-1 AND val-2

Below is the Example:

SELECT Customer_Id, First Name, LastName from Customer where Age Between 18 and 23

1.3.7          LIKEClause: This Clause is used to fetch the dataset from the Where clause where the Data output likely matches with the given format. This need not be the exact match.

The Syntax is as below:

SELECT column1, column2....columnN FROM table_name

WHERE column_name LIKE { PATTERN }

Below is the Example:

SELECT Customer_Id, First Name, LastName from Customer where First Name like ‘%Ram%’

1.3.8          ORDER BY Clause: This Clause is used to sort the dataset either in the Ascending order or on the Descending order..If No order is specified, then its Ascending, If DESC is specified, its Descending order which means by default its Ascending.

The Syntax is as below:

SELECT column1, column2.... columnN FROM table_name

WHERE CONDITION

ORDER BY column_name {ASC|DESC}

Below is the Example:

SELECT Customer_Id, First Name, Last Name from Customer  Order by Customer_ID

SELECT Customer_Id, First Name, Last Name from Customer  Order by Customer_IDdesc

1.3.9          GROUP BY Clause: This Clause is used to group a particular dataset based on the requirement to find the statistics.

The Syntax is as below:

SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name;

Below is the Example:

SELECT SUM(marks) from Student  group by  student_name

SELECT SUM(marks) from Student   where roll_no between  1 and 5 group by  student_name

1.3.10      COUNT Clause: This Clause is used to give the count of the dataset that satisfies the condition.

The Syntax is as below:

SELECT COUNT(column_name) FROM table_name WHERE CONDITION

Below is the Example:

SELECT COUNT(*) from Customer

1.3.11      HAVING Clause: This Clause is used to satisfy the condition after grouping the data where the condition is dependent on the grouping.

The Syntax is as below:

SELECT SUM(column_name) FROM table_name WHERE CONDITION

GROUP BY column_nameHAVING (arithematic function condition)

Below is the Example:

SELECT SUM(marks) from Student   where roll_no between  1 and 5 group by  student_name having SUM(Marks)>150

1.3.12      CASE Statement :  This Clause is used to validate the data for a certain condition which in simple terms can be called as an If Else condition.

The Syntax is as below:

SELECT

CASE Column1

WHEN Expression1 THEN Value1

WHEN Expression2 THEN Value2

WHEN Expression3 THEN Value3

ELSE Value4

END,

Column2,Column3 From TableName

Below is the Example:

SELECT EmpId,

Gender=

CASE

WHEN Gender='M' THEN 'Male'

WHEN Gender='F' THEN 'Female'

ELSE ''

END,

Designation

FROM Employeedetails

1.3.13      Date Functions:

GETDATE(): Gives the current date of the Server

Select getdate()
Result : 2015-05-15 07:38:01.340

Sometimes we may have to convert the date in the desired format and below are some examples for it

SELECT CONVERT(VARCHAR(11),GETDATE(),6) 

Result : 15 May 15

SELECT CONVERT(VARCHAR(11),GETDATE(),106)

Result : 15 May 2015

SELECT CONVERT(VARCHAR(10),GETDATE(),10)

Result : 05-15-15

SELECT CONVERT(VARCHAR(10),GETDATE(),110)

 Result : 05-15-2015

SELECT CONVERT(VARCHAR(24),GETDATE(),113)

Result : 15 May 2015 12:13:27:234

DATEPART() : This function will give the part of the date which means Day,Month and year of the date as desired

select DATEPART(Day,GetDate())  ---15

select DATEPART(Month,GetDate()) ---5

select DATEPART(Year,GetDate())----2015

DATEADD(): This function adds or subtracts the time interval from given date.

The Syntax is as below:

SELECT DATEADD(Datepart,Number,date)

Below is the Example:

Select DATEADD(dd,10,getdate()) –This adds 10 days to the current date.

DATEDIFF(): This functions returns the tme interval between two dates.

The Syntax is as below:

SELECT DATEDIFF(datepart,startdate,Enddate)

Below is the Example :

Select datediff(Day,’2014-01-01’,’2015-01-01’) –This will return the number of days from the start date to the end date which means 365 days.

Main Concepts of the Group By and Having Clause:

·           To use Group By Clause, we need to use at least one aggregate function.

·           All columns that are not used by aggregate function(s) must be in the Group By list.

·           We can use Group By Clause with or without Where Clause.

·           To use Having Clause, we have to use Group By Clause since it filters data that we get from Group By Clause.

Joins

SQL Joins are used to relate information in different tables. A Join condition is a part of the SQL query that retrieves rows from two or more tables. A SQL Join condition is used in the SQL WHERE Clause of select, update, delete statements.

A SQL join is used to combine rows from two or more tables, based on a common field between them and it can actually perform this for more tables as well.

The Syntax for Joins is as below:

SELECT col1, col2, col3...
FROM table_name1, table_name2
WHERE table_name1.col2 = table_name2.col1

The Syntax can also be in the below format as well:

SELECT col1, col2, col3...
FROM table_name1  join table_name2
on ( table_name1.col2 = table_name2.col1)

Types of Joins that are used in SQL:

·         Inner Join

·         Left Join

·         Outer Join

·         Full Outer Join

The join will result in a Cartesian product if the joining does not be on the Columns.Let us now take an example and then see how the Joins represent the data.

Customer Table:

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000
2	Khilan	25	Delhi	1500
3	Kaushik	23	Kota	2000
4	Chaitali	25	Mumbai	6500
5	Hardik	27	Bhopal	8500
6	Komal	22	MP	4500
7	Muffy	24	Indore	10000

Orders Table:

OID	DATE	CUSTOMER_ID	AMOUNT
102	2014-10-08	3	3000
100	2014-10-08	3	1500
101	2014-11-20	2	1560
103	2014-05-20	4	2060

Inner Join: Returns all rows when there is at least one match in both tables.

                SELECT C.ID, O.OID, C.NAME, C.ADDRESS, O.AMOUNT

FROM CUSTOMER C  INNER JOIN ORDERS O

ON (C. ID=O.CUSTOMER_ID)

This query will return the rows where the Id matched with the customer id in the order table.

The output of the Inner join will be as below:

ID	OID	NAME	Address	AMOUNT
3	102	kaushik	Kota	3000
3	100	kaushik	Kota	1500
2	101	Khilan	Delhi	1560
4	103	Chaitali	Mumbai	2060

Left Outer Join: Returns all rows from the left table, and the matched rows from the right table.

                                SELECT C.ID, O.OID, C.NAME, C.ADDRESS, O.AMOUNT

FROM CUSTOMER C  LEFT OUTER JOIN ORDERS O

ON (C. ID=O.CUSTOMER_ID)

This Query returns the all the rows with the matched data of the two tables then remaining rows of the Left table and NULL for the Right tables column.

The Output of the Left Outer join is as below:

ID	OID	NAME	Address	AMOUNT
1	NULL	Ramesh	Ahmedabad	NULL
2	101	Khilan	Delhi	1560
3	102	kaushik	Kota	3000
3	100	kaushik	Kota	1500
4	103	Chaitali	Mumbai	2060
5	NULL	Hardik	Bhopal	NULL
6	NULL	Komal	MP	NULL
7	NULL	Muffy	Indore	NULL

Right Outer Join: Returns all rows from the Right table, and the matched rows from the left table.

                                SELECT C.ID, O.OID, C.NAME, C.ADDRESS, O.AMOUNT

FROM CUSTOMER C  RIGHT OUTER JOIN ORDERS O

ON (C. ID=O.CUSTOMER_ID)

This Query returns the all the rows with the matched data of the two tables then remaining rows of the Right table and NULL for the Left  tables column.

The Output of the Right Outer join is as below:

ID	OID	NAME	Address	AMOUNT
3	102	kaushik	Kota	3000
3	100	kaushik	Kota	1500
2	101	Khilan	Delhi	1560
4	103	Chaitali	Mumbai	2060

Full Outer Join: Returns rows when there is a match in one of the tables.

                                SELECT C.ID, O.OID, C.NAME, C.ADDRESS, O.AMOUNT

FROM CUSTOMER C  FULL OUTER JOIN ORDERS O

ON (C. ID=O.CUSTOMER_ID)

This Query returns the all the rows with the matched data of the two tables then remaining rows of the Left table and Right table .

The Output of the Full Outer join is as below:

ID	OID	NAME	Address	AMOUNT
1	NULL	Ramesh	Ahmedabad	NULL
2	101	Khilan	Delhi	1560
3	102	kaushik	Kota	3000
3	100	kaushik	Kota	1500
4	103	Chaitali	Mumbai	2060
5	NULL	Hardik	Bhopal	NULL
6	NULL	Komal	MP	NULL
7	NULL	Muffy	Indore	NULL

Sub Query

A subquery is a SQL query nested inside a larger query.

·         The subquery can be used  inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.

·         A subquery is usually added within the WHERE Clause of another SQL SELECT statement.

·         You can use the comparison operators, such as >, <, or =. The comparison operator can also be a multiple-row operator, such as IN, ANY, or ALL.

·         A subquery can be treated as an inner query

·         The inner query executes first before its parent query so that the results of inner query can be passed to the outer query.

The Syntax is as below:

SELECT (columnnames) from Tablename

Where column name IN (select columnname from tablename)

Below is the Example:

SELECT EmpId,Age,design,gender from Employee

Where Empid IN (Select empid in department where deptno=20)