What is SQL?
SQL stands for Structured Query Language. SQL is a database language designed for the retrieval and management of data in relational databases. All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server use SQL as standard database language.
SQL Commands:
The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP.
These command can be classified as below groups based on functionality they provide.
DQL – Data Query Language
SELECT: Retrieves records from one or more tables.
DDL – Data Definition Language
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 – Data Manipulation Language
INSERT: Creates a record.
UPDATE: Modifies a record.
DELETE: Deletes a record.
DCL – Data Control Language
GRANT: Gives a privilege to a user.
REVOKE: Takes back privileges from user that were granted before.
During database testing, it is usually required to retrieve, update and delete certain records therefore we will give you a quick start with these SQL commands by listing all the basic SQL Syntax:
SELECT Statement:
It defines WHAT is to be returned (separated by commas)
Example:
SELECT * from EMPLOYEE
* means all columns from table EMPLOYEE
SELECT FIRST_NAME, LAST_NAME from EMPLOYEE
FROM Statement:
It defines the Table(s) or View(s) used by the SELECT or WHERE Statements
Multiple Tables/Views are separated by Commas
Example:
SELECT FIRST_NAME, LAST_NAME from EMPLOYEE
WHERE Clause:
It is an optional clause that defines what records are to be included in the query. You can get records by using different options such as:
Conditional Operators:
=, >, >=, <, ><=, != (<>) =, >, >=, <, ><=, != (<>)
BETWEEN x AND y B
IN (list)
LIKE ‘%string’ (“ % ” is a wild-card)
IS NULL
NOT {BETWEEN / IN / LIKE / NULL}
Multiple Conditions Linked with AND & OR Statements:
“AND” Means All Conditions are TRUE for the Record, “OR” Means at least 1 of the condition is true.
You can also mention strings that are contained Within SINGLE QUOTES.
Examples:
SELECT * FROM employee where age = ‘ 60 ’
SELECT First_Name, Last_Name FROM employee where Annual_Salary > 70000
SELECT state_name, state_population FROM states WHERE state_name LIKE ‘%NORTH% ’
SELECT * FROM report WHERE code IN (‘ Y ’ , ‘ W ’ , ‘ M ’ ) AND annual_summary_year = 2014
ORDER BY Statement:
It defines how the records are to be sorted. It must be in the SELECT statement to be ORDER BY. Default is to order in ASC (Ascending) order. It can also sort in Reverse (Descending) Order with “DESC ” After the Column name.
Example:
SELECT state_code, site_id FROM sites WHERE land_use_type = ‘PUBLIC’ ORDER BY state_code DESC