SQL With Examples [Latest]

SQL

SQL is used to work with RDBMS systems. It’s not for No-SQL or document based databases.
We can create database, create table etc. These are data structure definition operations. SQL supports these operations and they come under “data definition language” or DDL operations. Some of the keywords for DDL queries are Create, Alter, Drop, Rename, Truncate etc.
SQL queries are mostly used to retrieve and manipulate data in relational databases. They come under “data manipulation language” or DML statements. Some of the keywords used for DML operations are Select, Insert, Update, Delete etc.
Data Control Language or DCL is used to control access to data stored in a database. It’s very important to make sure only authorized users can access the data. SQL supports DCL through use of Grant and Revoke keywords.
We can store multiple queries in a file and execute them in SQL. SQL file name extension is .sql.
Most popular database systems that support SQL queries are Oracle, MySQL and PostgreSQL.
SQL implementations are incompatible with different databases, mostly because they have adapted to their own data structures. Some of the database implementations have their own data types and clauses, so if you have written queries for MySQL database, it may or may not work for Oracle database. For example MySQL has LIMIT clause that is not present in Oracle. MySQL has DATE, DATETIME and TIMESTAMP types whereas Oracle has only DATE type.

SQL syntax differs a lot based on type of queries. For example, below is the general syntax for SQL select and insert queries.


Select {fields} FROM {tables/views} WHERE {conditions}
INSERT INTO {table} ({column_names}) VALUES ({comma separated values})

We should be aware of some language specific terminologies.

Clause – SQL clauses are the building blocks of sql queries. For example in above syntax examples, Select, Insert, Where are the clauses.
Predicate – they are the conditions to limit the query results. In above example, condition in the where clause is called Predicate.
Queries – SQL statement is also called queries.

That’s all for a quick roundup on SQL.

Reference: Wikipedia