SQL – Structured Query Language designed to allow both technical and non-technical users work with a database to manage, manipulate and transform data in a Relational Database Management System (RDBMS), particularly handling entities/variables which related to each other. Because of its simplicity, SQL databases provide safe, secure storage and easy access for millions of websites and applications.
After its standardization in 1986 by ANSI and by ISO in 1987, many features added to SQL. Though standard most SQL code is not completely portable among different database systems but support all major operation of SQL. SQL introduced the concept of accessing multiple records with one single command and eliminates the need to specify how to reach a record with or without an index.
SQL commands informally classified into sub-languages depending on their operation such as
Data query language (DQL)
A data query languages commands used to make queries i.e. fetch data from tables based on certain conditions. Like command SELECT used to retrieve data from one or more tables.
Data definition language (DDL)
A data definition language syntax similar to a programming language for defining database schemas. This commands involve in modifying database by adding, deleting or updating the tables.
Some DDL commands include:
- CREATE: The create command used to create a new database, table, index, functions, views or stored procedure.
- DROP: The DROP command delete an existing database, table, index, or view. Unlike DELETE and TRUNCATE commands just delete data but leave table in database.
- ALTER: The ALTER command used to modify an existing database object.
- TRUNCATE: The TRUNCATE used to delete all data from a table but not table itself. It is faster than DELETE.
All DDL commands changes are permanent in the database.
Data control language (DCL)
A data control language commands used to allow or deny access to data stored in a database.
Some DCL commands include:
- GRANT: grant permission to specified user.
- REVOKE: deny or invoke previously granted permissions.
In addition, ROLLBACK and COMMIT are also DCL commands.
Data manipulation language (DML)
A data manipulation language commands used insert, delete, merge and update data stored in a table but not the table itself in database.
Some DML commands include:
- INSERT: used to insert a new row in a table
- MERGE: used to merge two rows or tables
- UPDATE: used to update existing row in table
- DELETE: used to delete a row in table
DML commands are not permanent can be rolled back anytime.
There are many SQL database systems including SQLite, Oracle, PostgreSQL, MySQL and Microsoft SQL Server. But all of them support major SQL commands, each implementation may differ in some features and storage types.
What is RDBMS?
RDBMS is a database management system (DBMS) designed to manage related data based on the relational model. RDBMS is the basis for all databases such as SQL, MS SQL Server, IBM Oracle, DB2, MySQL, and Microsoft Access.
The data in RDBMS is stored in database objects as a collection of related data entities in columns and rows called tables. For example consider famous Northwind sample database “Customers” table :
SELECT * FROM Customers;
ID Company First Name Last Name Business Phone Job Title 1 Company A Anna Bedecs 555-0100 Owner 2 Company B Antonio Gratacos 555-0101 Owner 3 Company C Thomas Axen 555-0102 Purchasing 4 Company D Christina Lee 555-0103 Purchasing 5 Company E Martin O’Donne 555-0104 Owner
A table is a collection of rows and columns containing related data called fields. The fields in the above Customers table consist of ID, Company name, First n Last name, Business phone and Job title.
A record or a row, is single horizontal entity that exists in a table. For example
1 Company A Anna Bedecs (123)555-0100 Owner
A column is a whole vertical entity in a table that contains all information specific field in a table.
First Name Anna Antonio Thomas Christina Martin
SQL too follow rules enforced on data columns used to limit the type of data goes into the table which ensures accuracy and reliability of data. Constraints are enforced either on column or on table level.
Following are some constraints used commonly in SQL:
- NOT NULL: column cannot have a NULL value.
- INDEX: create and retrieve data from the database.
- DEFAULT: Provides default value to column when input not given.
- UNIQUE: Ensures all the values in a column are unique.
- PRIMARY Key: Uniquely identifies each row/record in a database table.
- FOREIGN Key: Uniquely identifies a row/record in any another database table.
Data integrity is the assurance of accuracy and consistency of data and is a critical aspect to the design, implementation and usage of any database system.
The following categories of data integrity with RDBMS:
- Entity: No duplicate rows in a table.
- Domain: check for valid entries for a given column by restricting the type, format, or range of values.
- Referential: Row/record cannot be deleted, which are used by other records.
- User-Defined: user defined apart from already exist to meet requirement.
Database normalization is the process of organizing data efficiently in a database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly.
Normalization consists series of guidelines that help in creating a good database structure. These guidelines are divided into 4 normal forms.
Here are the most commonly used normal forms:
- First normal form(1NF)
- Second normal form(2NF)
- Third normal form(3NF)
- Boyce & Codd normal form (BCNF)
There are many RDBMS available to work let’s study overview and basic features of some of them now.
MySQL is an open source database developed by a MySQL AB (Swedish software company overtaken by Oracle). MySQL supports different platforms including Microsoft Windows, Linux , UNIX, and Mac OS X.
MySQL has free and paid versions, comes with a fast, multi-threaded, multi-user and robust SQL database server.
- High Performance.
- High Availability.
- Scalability and Flexibility Run anything.
- Strong Data Protection.
- Comprehensive Application Development.
- Web and Data Warehouse Strengths.
- Robust Transactional Support.
- Lowest Total Cost of Ownership.
MS SQL Server
MS SQL Server is a Database System developed by Microsoft Inc.
- High Performance
- High Availability
- XML integration
- Database mirroring
- Database snapshots
- DDL triggers
- Ranking functions
- Row version-based isolation levels
- CLR integration
- Service Broker
- Database Mail
Oracle is a RDBMS system developed by ‘Oracle Corporation‘. It is a very large multi-user database management system.
It is best database server choice for client/server computing. Oracle database supports all major OS for both clients and servers, including DOS, UnixWare, NetWare, and UNIX.
- Resource Manager
- Data Warehousing
- Read Consistency
- Parallel Execution
- Quiesce Database
- Analytic SQL
- Data mining
- Locking Mechanisms
- Self-managing database
- Materialized views
- Bitmap indexes
- Table compression
Microsoft Access is an entry-level, inexpensive, powerful database management software for small-scale projects. This is the most popular Microsoft products uses the Jet database engine, which utilizes a specific SQL language dialect.
- It allows users to create tables, forms, queries, reports and connect them with macros.
- It allows opening table and scrolling through the records contained within it.
- Forms provide a quick and easy way to modify records into databases.
- capable to execute more complex queries and combine data from multiple tables and place specific conditions on the data retrieved.
- User-friendly form interface allow user to enter information in a graphical form and have that information transparently passed to the database.