SQL for Testers


SQL (Structured Query Language):

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



SQL Material



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 (CONSTRAINTS, (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

AS SELECT * FROM

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

ON (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 [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 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

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


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


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 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 set =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


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

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 from table_name

EXCEPT

Select 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 ,….

FROM

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....columnNFROMtable_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)




Q & A


1.  To get the list of tables and views in Database

Ans : SELECT * FROM information_schema.tables (will display both tables,views) SELECT * FROM information_schema.views (will display on views)

2.  List the details about “SMITH”

Ans: Select * from employee where last_name=’SMITH’;

3.      List out the employees who are working in department 20 Ans: Select * from employee where department_id=20

4.      List out the employees who are earning salary between 3000 and 4500 Ans: Select * from employee where salary between 3000 and 4500

5.      List out the employees who are working in department 10 or 20

Ans: Select * from employee where department_id in (20,30)

6.  Find out the employees who are not working in department 10 or 30

Ans :Select last_name, salary, commission, department_id from employee where department_id not in (10,30)

7.  List out the employees whose name starts with “S”

Ans: Select * from employee where last_name like ‘S%’

8.  List out the employees whose name start with “S” and end with “H”

 Ans: Select * from employee where last_name like ‘S%H’

9.  List out the employees whose name length is 4 and start with “S”

Ans : Select * from employee where last_name like ‘S___’


10.   List out the employees who are working in department 10 and draw the salaries more than 3500

Ans: Select * from employee where department_id=10 and salary>3500


11.   List out the employees who are not receiving commission. Ans: Select * from employee where commission is Null

12.   List out the employee id, last name in ascending order based on the employee id. Ans: Select employee_id, last_name from employee order by employee_id

13.   List out the employee id, name in descending order based on salary column

Ans : Select employee_id, last_name, salary from employee order by salary desc

14.   list out the employee details according to their last_name in ascending order and salaries in descending order

Ans: Select employee_id, last_name, salary from employee order by last_name, salary desc

15.   list out the employee details according to their last_name in ascending order and then on department_id in descending order.

Ans: Select employee_id, last_name, salary from employee order by last_name, department_id desc

16.   How many employees who are working in different departments wise in the organization Ans : Select department_id, count(*), from employee group by department_id

17.   List out the department wise maximum salary, minimum salary, average salary of the employees

  
Ans: Select department_id, count(*), max(salary), min(salary), avg(salary) from employee group by department_id

18.   List out the job wise maximum salary, minimum salary, average salaries of the employees. Ans: Select job_id, count(*), max(salary), min(salary), avg(salary) from employee group by job_id

19.   List out the no. of employees joined in every month in ascending order.

Ans: Select to_char(hire_date,’month’)month, count(*) from employee group by to_char(hire_date,’month’) order by month

20.   List out the no of employees for each month and year, in the ascending order based on the year, month.

Ans: Select to_char(hire_date,’yyyy’) Year, to_char(hire_date,’mon’) Month, count(*) “No. of employees” from employee group by to_char(hire_date,’yyyy’), to_char(hire_date,’mon’)
Labels: , ,

Followers