MySQL SQL – Structured Query Language: Basics of SQL

SQL – Structured Query Language: Basics of SQL

SQL – Structured Query Language

SQL is an abbreviation for Structured Query Language, usually pronounced as separate letters “S-Q-L” or “Sequel“. SQL is a query language as reflected by its name, is used for database accessing and its modification which can include any operation. The operations can be – Create data, Read data, Update Data, Delete Data, etc.

Donald D. Chamberlin and Raymond F. Boyce developed SQL at IBM in the early 1970s. Relational Software, Inc. (now Oracle) introduced the first commercially available implementation of SQL in 1979. Now, SQL is known as the standard RDBMS language.

SQL actually allows the user to work with data at the logical level. Furthermore, it has a lot of operations to do with the databases. However when we start learning SQL, the basic operations we focus on are Select, Insert, Update and Delete.

Structured Query Language offers statements for a variety of operations, which include:

  • Querying/Selection of data
  • Insert, update and delete operations on rows in a table.
  • Create, replace, alter and drop object operations.
  • Controlling access to the database and its objects
  • Guaranteeing database consistency and integrity

All the major relational database management systems support SQL. SQL is platform-independent and programs written in SQL are portable. Hence, they can easily be moved from one database to another with very little modification. Web-based and desktop-based applications both use SQL quite widely.

Now get some hands-on practice, i.e., programming concepts.

“Hope you have gone through the database tutorial as it will let you understand the concept of rows and columns.”

As mentioned above “when we start learning SQL, the basic operations we focus on are Select, Insert, Update and Delete”. Let’s first start with learning syntax. SQL syntax depends on the type of statements (Select, Insert, Update and Delete).

Structured Query Language SELECT statement:

Syntax of SELECT statement in Structured Query Language :

Explanation: To select (read/retrieve) any data from the database we use SELECT followed by column names. If you need to select all the columns use “*” in place of ‘.’

Examples:

SQL INSERT statement:

Syntax of INSERT statement in Structured Query Language: 

Explanation: To insert values in all columns, providing column names is not necessary instead data in values section will be inserted in the columns in the sequence of table structure.

Examples:

Structured Query Language UPDATE statement:

Syntax of UPDATE statement in Structured Query Language: 

Explanation: To update values in a table we use conditions to update certain rows otherwise the update will be applied on all the rows.

Examples:

SQL DELETE statement:

Syntax of DELETE statement in Structured Query Language: 

Explanation: To delete values from a table we use conditions to delete certain rows otherwise all the rows will be deleted.

Examples:

SQL statements explained above are used to manipulate data in the database. Now let’s have a look at the statements which define and change(alter) the structure of a database table.

SQL CREATE statement: 

SQL CREATE statements are used to define or say create a table in a database which contains information about the number of columns and their data types.

Syntax of CREATE statement in Structured Query Language: 

Below is an example in which I am creating a table named ‘students’ with some columns. These columns are –

  • student_id of type integer cannot be NULL
  • student_name of type VARCHAR that can have a maximum of 100 characters and can’t be NULL.
  • student_subject of type VARCHAR that can have a maximum of 100 characters which can’t be NULL.

Example:

Structured Query Language ALTER statement: 

SQL ALTER statements are used to make changes in the structure of any table. It can be any operation like adding one or more columns, drop(deleting) columns. Changing datatype or any other property of a column, making INDEX or any keys, etc. It has a wide variety of operations but I am showing an ADD column and DROP column.

SQL ALTER statement to ADD a new column:

To ADD columns in a table structure ADD is used.

Syntax of ALTER statement to ADD a new column:

Below is an example in which I am adding more columns.

Firstly, I am going to add a column named student_grade of type VARCHAR that can have a maximum of 20 characters which can’t be NULL.

Afterward, I added two columns named student_grade1 and student_grade3. Both columns are of type VARCHAR that can have a maximum of 10 characters having NULL as default value.

Example:

SQL ALTER statement to DROP columns:

Here, to delete any column from the table structure we use DROP statement.

Syntax of ALTER statement to DROP columns:

Below is an example in which I have added two columns in the table. The columns are – ‘students’, student_grade and student_grade2. Both columns are of type VARCHAR that can have a maximum of 10 characters with default value NULL.

Example:

Leave a Reply