SQL Basics: Overview and Introduction
What is SQL
SQL stands for Structured Query Language and can be pronounced as SQL or sequel – (Structured Query Language) . Defined, SQL is a query language used for accessing and modifying information in one or more data tables and rows of a database.
SQL DataBase
In a simple manner, SQL is a non-procedural, English-like language that processes data in groups of records rather than one record at a time. Few SQL functions of are:
- Store data
- Modify data
- Retrieve data
- Delete data
- Create tables and other database objects
A Brief History of SQL
-
1970 − Dr. Edgar F. "Ted" Codd of IBM is known as the father of relational databases. He described a relational model for databases.
-
1974 − Structured Query Language appeared.
-
1978 − IBM worked to develop Codd's ideas and released a product named System/R.
-
1986 − IBM developed the first prototype of relational database and standardized by ANSI. The first relational database was released by Relational Software which later came to be known as Oracle.
SQL Database Design
IBM first developed SQL in 1970s. Also it is an ANSI/ISO standard. It has become a Standard Universal Language used by most of the relational database management systems (RDBMS). Some of the RDBMS systems are: Oracle, Microsoft SQL server, Sybase etc.
Most of these have provided their own implementation extensions, thus enhancing their RDBMS
system features and making it a powerful tool. These RDBMS systems, all use the popular SQL
commands SELECT, UPDATE, DELETE, INSERT, WHERE in similar format.
SQL Database Table
SQL database is constructed of a number of tables. In a business, SQL tables would be used to divide and simplify the different areas of the operation: Table for Customers, one for Vendors, Employees and so on.
SQL Database Table Columns
Each SQL table is made up of a number of columns, referred to as fields and run along the top of the table. Sql columns or fields have their content (object/data/info) defined into character types; such as text, date, numeric, integer, length to name a few.
SQL Database Table Rows
Each SQL table row, referred to a record, is located in the left column of the table. Sql
record row will contain a string of data containing data matching up to each column field across the top.
So, in a Customer table each customer record would consist of one row with data for the
customer ID number, customer name, address, phone ... email and so on.
Simple Overview of various SQL query
SQL Query Types
| SELECT Statement | Retrieve records from a table |
| SELECT LIMIT Statement | Retrieve records from a table and limit results |
| SELECT TOP Statement | Retrieve records from a table and limit results |
| INSERT Statement | Insert records into a table |
| UPDATE Statement | Update records in a table |
| DELETE Statement | Delete records from a table |
| TRUNCATE TABLE Statement | Delete all records from a table (no rollback) |
| UNION Operator | Combine 2 result sets (removes duplicates) |
| UNION ALL Operator | Combine 2 result sets (includes duplicates) |
| INTERSECT Operator | Intersection of 2 result sets |
| MINUS Operator | Result set of one minus the result set of another |
| EXCEPT Operator | Result set of one minus the result set of another |
SQL Comparison Operators
| Comparison Operators | Operators such as =, <>, !=, >, <, and so on |
SQL Joins
| JOIN Tables | Inner and Outer joins |
SQL Aliases
| ALIASES | Create a temporary name for a column or table |
SQL Clauses
| DISTINCT Clause | Retrieve unique records |
| FROM Clause | List tables and join information |
| WHERE Clause | Filter results |
| ORDER BY Clause | Sort query results |
| GROUP BY Clause | Group by one or more columns |
| HAVING Clause | Restrict the groups of returned rows |
SQL Functions
| COUNT Function | Return the count of an expression |
| SUM Function | Return the sum of an expression |
| MIN Function | Return the min of an expression |
| MAX Function | Return the max of an expression |
| AVG Function | Return the average of an expression |
SQL Conditions
| AND Condition | 2 or more conditions to be met |
| OR Condition | Any one of the conditions are met |
| AND & OR | Combining AND and OR conditions |
| LIKE Condition | Use wildcards in a WHERE clause |
| IN Condition | Alternative to multiple OR conditions |
| NOT Condition | Negate a condition |
| IS NULL Condition | Test for NULL value |
| IS NOT NULL Condition | Test for NOT NULL value |
| BETWEEN Condition | Retrieve within a range (inclusive) |
| EXISTS Condition | Condition is met if subquery returns at least one row |
SQL Tables and Views
| CREATE TABLE | Create a table |
| CREATE TABLE AS | Create a table from another table's definition and data |
| ALTER TABLE | Add, modify or delete columns in a table; rename a table |
| DROP TABLE | Delete a table |
| GLOBAL TEMP Tables | Tables that are distinct within SQL session |
| LOCAL TEMP Tables | Tables that are distinct within modules and embedded SQL program |
| SQL VIEW | Virtual tables (views of other tables) |