SQL tutotrial that are mostly used

Database can either be a fun or a daunting task depending up on your skills. If you have the required database skills, then it is the most desirable niche to work on. There are several SQL queries that are commonly used and each and every database administrator should know about it. Let’s go through the top ten most basic as well as simple queries in SQL. Even though studies suggest the ten commonly used SQL queries to be only 3% of the total query set, these SQL queries contribute almost a majority of the database operations.

As we all know, SQL is nothing but an interface that is used for establishing communication with the system’s database by making use of queries. It is desirable to go for some popular SQL engines like SQLite3 which then has to be run from the command line by entering in the details as sqlite test.db. Once done, users will be able to view a SQL prompt within the screen.

Initially an empty table has to be created by entering the following query namely CREATE TABLE student (id INTEGER PRIMARY KEY , name TEXT, age INTEGER);. A table with title as ‘Student’ would be created with ‘0’ rows within it. The student record would be stored within this table. The table would be comprised of student ID, student name as well as student age. The structure would be displayed as ID(Primary key)NameAge. In this primary key would be the column ID which means it can have only values that are unique and also this column cannot be left blank. Moreover, the Name column should be comprised of characters and the Age column should be represented in numbers.

CREATE TABLE student (id INTEGER PRIMARY KEY , name TEXT, age INTEGER);

Inserting Records in a Table

Data can be inserted within a table very easily. Users will have to type in the query stated as INSERT INTO student (id, name, age) VALUES (‘1’, ‘name’, 17); in which users will have to specify the column name in an orderly manner after the INSERT INTO statement. After then the values that is required after the VALUES keyword will have to be entered.

INSERT INTO student (id, name, age) VALUES (‘1’, ‘mark’, 17);

insert into student (id, name, age) values (‘2’, 'amy', ‘26’);
insert into student (id, name, age) values (‘3’, 'bob', ‘27’);
insert into student (id, name, age) values (‘4’, 'chris', ‘28’);
insert into student (id ,name, age) values (‘5’, 'dan', ‘26’);

Table is

ID Name Age
1 mark 17
2 Amy 26
3 Bob 27
4 Chris 28
5 Dan 26

Viewing Records from Table

This query is the most commonly used one and is the simplest of all. Users will just have to type the query as SELECT * FROM student; and the query results would be displayed for all the rows available within the table. The results being displayed can be rearranged in a particular order by making use of ‘Order By’ clause within the ‘SELECT’ statement. For example, if the user enters ‘SELECT * FROM student ORDER BY age;’ then it will display the results based on the age in an increasing order. However the results can be displayed in a descending order too by entering the DESC keyword.

SELECT * FROM student;

Result

ID Name Age
1 mark 17
2 Amy 26
3 Bob 27
4 Chris 28
5 Dan 26

SELECT * FROM student ORDER BY age;

ID Name Age

1 mark 17
2 Amy 26
5 Dan 26
3 Bob 27
4 Chris 28

Viewing only selected records from a table

If users want to specifically choose only selected records from a table especially from a large number of rows within a table, then users can do so by entering an option provided by SQL for viewing only selected records. Users will have to enter ‘SELECT COUNT(1) FROM student;’ and the output for the specific records would be displayed. It has to be taken note that users cannot make use of MAX, MIN and SUM functions with the text column as it will result in an error. Therefore such functions can be used only with the numeric columns.

SELECT COUNT(1) FROM student;

ID Name Age

1 mark 17

SELECT id , name , MAX(age) FROM student;

ID Name Age

4 Chris 28

SELECT sum(age)FROM student;

124

Deleting records from a table

Enter the query as ‘DELETE FROM student WHERE name = ‘mention the name here’; ‘. Thus the rows from table ‘Student’ with the ‘name’ columns having value with the desired name being entered by the users would be deleted by the query.

DELETE FROM student WHERE name = ‘mark’;

ID Name Age

2 Amy 26
5 Dan 26
3 Bob 27
4 Chris 28

Changing data within the existing records in a Table

During instances wherein the user will have to change the name or age of a student within the existing table, then the query for changing the data in the records within a table can be used. The query used is ‘UPDATE student SET age = ‘mention the age in numeric’ WHERE name = ‘mention the name’; ‘. However users will have to ensure more attention when it comes to using UPDATE or DELETE queries with ‘WHERE’ clause since all students with the same name will have their age updated. Therefore it is desirable to make use of PRIMARY KEY in WHERE clause while updating as well as deleting.

UPDATE student SET age = 22 WHERE name = ‘amy’;

ID Name Age

2 Amy 22

Viewing Records in a table without knowing Exact Details

There can be instances wherein a user wants to view the records of a student whose name is familiar but the spelling is not clear. In such instances, the ‘LIKE’ operator can be provided by the SQL. The following query has to be entered namely SELECT * FROM student WHERE name LIKE ‘specify the name’; .

SELECT * FROM student WHERE name LIKE ‘m%%k';

ID Name Age
1 mark 17

Retrieving Records using more than one condition in WHERE clause

During instances wherein it would be unable for a user to fetch a unique record just by making use of the name value in WHERE clause, there is a greater need to merge multiple conditions in WHERE clause that can be easily performed by making use of conditional keywords such as ‘AND’ and ‘OR’. Users will get the perfect output by entering the query as ‘SELECT * FROM student WHERE name = ‘mention the name here’ AND age = ‘mention the age in numeric’.

SELECT * FROM student WHERE name = ‘mark’;

ID Name Age
1 mark 17

Viewing only selected columns from a table

Users will be able to view only the columns they have selected from a table by entering any of the following queries.

• SELECT name FROM student WHERE age > enter the age limit in numeric;
  
• SELECT name, age FROM student;
  
• SELECT age, name FROM student;
  

Understand the structure of a table

Users can now understand the entire structure of a table and its details by entering a simple query. However different SQL engines have different commands.

For SQLite3, the command would be ‘.schema student;’ whereas MySQL makes use of the command namely ‘describe student;’ in which the ‘student’ would be the name of the table.

Analyzing the performance of Query

It is considered to be an advanced query and is useful during circumstances in which the user wants to identify the reason behind the slow performance of a query. The command is ‘EXPLAIN QUERY PLAN SELECT * FROM student;’ in which the ‘Explain’ is used for gathering the details regarding the breakup of the timings of various parts of a query.