SQL – Structured Query Language: Basics of SQL
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 :
|
1 |
SELECT [column_names] FROM [table_name] WHERE [condition_if_applied]; |
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:
|
1 2 3 4 5 6 7 8 |
// statement to select all columns from table students. SELECT * FROM students. // statement to select student id and student name from table students. SELECT student_id, student_name FROM students; // statement to select student id and student name from table students of a student named "John". SELECT student_id, student_name FROM students WHERE student_name = "John"; OR SELECT student_id, student_name FROM students WHERE student_name LIKE "John"; |
SQL INSERT statement:
Syntax of INSERT statement in Structured Query Language:
|
1 |
INSERT INTO [table_name] ([column_names]) VALUES ([column_values]); |
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:
|
1 2 3 4 |
// statement to insert values respective of column names. INSERT INTO students (student_name, student_subject) VALUES ('Suzain', 'Mathematics'); // statement to insert values according to sequence in table structure INSERT INTO students VALUES ('100', 'Mathew', 'Geography'); |
Structured Query Language UPDATE statement:
Syntax of UPDATE statement in Structured Query Language:
|
1 |
UPDATE [table_name] SET [column_name1] = [value1], [column_name2] = [value2] ... WHERE [condition_if_applied]; |
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:
|
1 2 3 4 |
// statement to update subject of student named 'Suzain' UPDATE students SET student_subject = 'History' WHERE student_name LIKE 'Suzain'; // statement to update subject of student with id = 100 UPDATE students SET student_subject = 'Music' WHERE student_id = 100; |
SQL DELETE statement:
Syntax of DELETE statement in Structured Query Language:
|
1 |
DELETE FROM [table_name] WHERE [condition_if_applied]; |
Explanation: To delete values from a table we use conditions to delete certain rows otherwise all the rows will be deleted.
Examples:
|
1 2 3 4 |
// statement to delete data of student named 'Suzain' DELETE FROM students WHERE student_name LIKE 'Suzain'; // statement to delete data of student with id = 100 DELETE FROM students WHERE student_id = 100; |
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.
- MySQL CHAR And VARCHAR Types
- How To Extend Group_concat() Limit In MySQL & PhpMyAdmin
- How To Export MySQL Database Using Command Prompt Or Batch File
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:
|
1 2 3 4 |
CREATE TABLE [table_name] ( column_name1 data_type(length if applicable), column_name2 data_type(length if applicable), ); |
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:
|
1 2 3 4 5 |
CREATE TABLE students ( student_id int NOT NULL, student_name VARCHAR(100) NOT NULL, student_subject VARCHAR(100) NOT NULL ); |
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:
|
1 |
ALTER TABLE [table_name] ADD column_name1 data_type(length if applicable) , ADD column_name2 data_type(length if applicable); |
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:
|
1 2 3 4 5 |
/** to add single column */ ALTER TABLE students ADD student_grade VARCHAR(20) NOT NULL; /* to add more than one column */ ALTER TABLE students ADD student_grade2 VARCHAR(10) NULL DEFAULT NULL, ADD student_grade3 VARCHAR(10) NULL DEFAULT NULL ; |
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:
|
1 2 |
ALTER TABLE [table_name] DROP column_name1, DROP column_name2, ...; |
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:
|
1 2 3 4 |
// Drop a single column student_grade2 ALTER TABLE students DROP student_grade2; // Drop multiple columns single column student_grade and student_grade1 ALTER TABLE students DROP student_grade, DROP student_grade1; |