Written by Sai Sandeep Thota on December 29, 2012 in Programming > SQL

Learn Basic SQL Queries

GET ALERTS:

Get our Latest updates delivered to your mailbox!

If you are just getting started with SQL, these are some of the basic SQL queries that you have to know and be aware of in order to make your life easy. These SQL queries, are some of the most used in daily life and are demanded to be aware of by most of professional IT companies that are existing.

SQL – Structured Query Language is a programming language used to manage a database. In this article we’ll discuss basic SQL Queries like CREATE, INSERT, SELECT and DELETE. We’ll be coming up with more similar tutorials, that not only covers the basic SQL Queries, but covers some of the advanced SQL queries.

Learn Basic SQL Queries

How to Create a Table in a Database?

Before creating a Table, We need a Database. So lets see how to create a Database.

sql>CREATE DATABASE mydb;
Query OK, 1 row affected (0.01 sec)

Here ‘CREATE DATABASE’ are keywords and they are not case-sensitive.

Creating a Table in Database:

sql>CREATE TABLE employees (id int, empname varchar(100), phone int);
Query OK, 0 rows affected (0.05 sec)

Insert Data, Values or Records into a Table:

To insert data into a table, you need to follow the same order of columns. If you don’t remember the order, you can view it by executing the following:

sql>DESC employees;

DESC – means description of the table employees.

sql>INSERT INTO employees VALUES(48430,'Ram',9990090990);
Query OK, 1 row affected (0.00 sec)

In the above query, We have included the name in Single Quotes ” as it is a Text value with varchar datatype.

In case ‘No database selected’ error is encountered then:

sql>USE mydb;
Database changed

View Records Present in the Database Table:

You can display all the records present in the table or else you can filter the results by placing clauses.

sql>SELECT * from employes;

Output:
+——-+———+————+
| id | empname | phone |
+——-+———+————+
| 48430 | Ram | 9990090990 |
+——-+———+————+
1 row in set (0.00 sec)

Delete a Record in Database Table:

You can delete all the Records at once using the following Query:

sql>DELETE FROM employees;
Query OK, 1 row affected (0.00 sec)

If you want to delete a Specific Record, Say with Employee id 48430 then:

sql>DELETE FROM employees where id=48430;
Query OK, 1 row affected (0.00 sec)

If you want to Delete a Table then:

sql>DROP TABLE employees;
Query OK, 0 rows affected (0.01 sec)

In case of Deleting the Database:

sql>DROP DATABASE mydb;
Query OK, 0 rows affected (0.33 sec)

Lets get bit advanced now by Updating Records and Altering Tables, Datatypes.

Update a Record:

If we need to change the name or phone number of an Employee:

sql>UPDATE employees SET name='Rama' where id='48430';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

In the above Query we are changing the Name field of the Record with id 48430.

Insert a Field:

If you need to insert a field, Say email address of the Employee then:

sql>ALTER TABLE employees ADD email varchar(50);
Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

Delete or Drop a Field:

If we don’t want a field, Say email:

sql>ALTER TABLE employees DROP COLUMN email;
Query OK, 1 row affected (0.11 sec)
Records: 1  Duplicates: 0  Warnings: 0

Change the Data type of a Field:

For example we have id defined with int data type here, If we want to change it to varchar then:

sql>ALTER TABLE employees MODIFY id varchar(10);
Query OK, 1 rows affected (0.11 sec)
Records: 1  Duplicates: 0  Warnings: 0

Additional Queries:

List all the Tables present in the Database:

sql>show tables;

+—————-+
| Tables_in_mydb |
+—————-+
| employees |
+—————-+
1 row in set (0.00 sec)

The above command show tables will only work for MySQL, If you want to list all the tables present in Oracle then:

sql>SELECT * from user_objects where object_type='TABLE';

Count number of Records present in a Table:

sql>SELECT COUNT(*) from Students;

+———-+
| COUNT(*) |
+———-+
| 1 |
+———-+
1 row in set (0.00 sec)

Rename a Table:

If you want to Rename a table in MySQL, Say lets Rename Employees to Ind_Employees.

sql>ALTER TABLE employees RENAME ind_employees;
Query OK, 0 rows affected (0.01 sec)

If you want to rename a table in Oracle SQL then:

sql>RENAME employees to ind_employees;

Rename a Column:

If you want to Rename a Column in MySQL, say empname to emp_name.

sql>ALTER TABLE employees CHANGE empname emp_name varchar(50);

That means the default query will look like tis:

sql>ALTER TABLE table_name CHANGE oldname newname datatype;

In the case of Oracle:

sql>ALTER TABLE employees RENAME COLUMN empname to emp_name;

Default query:

sql>ALTER TABLE table_name RENAME COLUMN oldname to newname;

Note:
1. All the keywords in the queries are written in Capital-Letters. Query OK, affected rows are the messages displayed if the written Query is correct.
2. The above Queries works on MySQL and some queries may not work on Oracle SQL.
Thus, these are some of the must known basic SQL queries to get accustomed if you have started learning SQL.

{ 2 comments… read them below or add one }

Praveen Soni January 9, 2013 at 6:40 pm

Hello Sandeep,
Nice article for those who just wanted to start from the DBMS Queries.
Nice tutorial,
And thanks for sharing 🙂

Reply

Sai Sandeep Thota January 9, 2013 at 6:43 pm

I am glad you liked it Praveen! Stay tuned for great content.. 🙂

Reply

Leave a Comment

Previous post:

Next post: