Basic SQL Commands

Basic SQL Commands

Create/Drop /Selecting Database

Create Database – used to create new SQL Database. The Syntax and example to create studentDB is given below.

Syntax: CREATE database databasename;

Example: mysql>create database

studentDB;

The database created now can be viewed using the following Syntax:

mysql>Show databases;

As a result, the newly created studentDB will be listed.

Drop Database – used to remove any of the existing SQL Database. The Syntax and example to delete student DB is given below.

Syntax: DROP database databasename;

Example: mysql>DROP database

studentDB;

The deleted database will not be viewed,when we list all databases using the Syntax, Show databases;

Select Database – Many databases are available in the repository, from which the suitable database is selected using the below command.

Syntax: USE databasename;

Example: mysql>USE studentDB;

Once the database is selected, multiple operations are performed as per the needs of the application.

• Insert Record

Database will have multiple tables. Tables are created using the create command with various fields added to it as per their need. Any table is complete only with the record available in it. So new row are added to the table using the Insert command. The Syntax and example for inserting new record into the table is given below:

Syntax 1:

INSERT INTO tablename (column1, column2, column3)

VALUES (value1, value2, value3);

Syntax 2:

INSERT INTO tablename VALUES (value1, value2, value3);

Consider we have a table named Biodata, which has three columns namely firstname, lastname and age. Now the new record is added to the table using either Syntax1 or Syntax2 as shown below.

mysql>INSERT INTO Biodata (firstname, lastname, age)

VALUES (Krishna, Sam, 10);

(or)

mysql>INSERT INTO Biodata VALUES (Krishna, Sam, 10);

• Select Record

From the multiple records available in the table, the enquired data are retrieved from the table-using the SELECT command with some conditions specified in it. We can retrieve all the fields of a record or specify the necessary fields in a table. The records of any table are retrieved using the SELECT Syntax given below in Table 3.18 and 3.19

Syntax1: SELECT * from tablename;

Example: mysql>SELECT * from Biodata;

Syntax2: SELECT column1, column2 from tablename;

Example: mysql>SELECT firstname, age from Biodata;

Deleting Record

The existing record in a table is removed from the table using DELETE command. Entire record or specified columns in the table can be deleted. If we want to perform delete operation on specific columns, then the condition is given using WHERE clause. If the condition is not specified, then the entire data will be deleted. See Table 3.20 and 3.21

Syntax1: DELETE from tablename WHERE columnname=”value”; 

Example: mysql>DELETE from Biodata WHERE firstname=”Mani”;

Syntax2: DELETE from tablename;

Example: mysql>DELETE from Biodata ;

Modifying Record

SQL provides us with modifying and updating the existing records in a table using UPDATE command. The age of Krishna in Biodata table is changed using the below Syntax.

Syntax1: UPDATE tablename

SET column1=”new value”

Where column2=”value2”;

Example: mysql>UPDATE Biodata SET age=13 WHERE firstname=”Krishna”;

WHERE Clause

In SQL command WHERE clause is used to specify the selection criteria Based on that data’s are retrieved or modified as per the query. In the WHERE conditions, operations like =, !=, >, >=, < , <= are used to frame the query statement. WHERE clause is used in SELECT and UPDATE query statement for the condition. The number of records updated in a table depends on the WHERE condition.

Using Operators

While forming the SQL query we use major operators like Arithmetic, Comparison and Logical in the WHERE clause. The purpose of each operator is listed below in Table 3.22.

Sorting Records

The Query results are listed in Ascending or Descending order using the command ORDER BY clause. In some databases the results are sorted by default in Ascending order and is given in Syntax1. The results are displayed in descending order as per Syntax2. See Table 3.23 and 3.24

Syntax1: select * from tablename ORDER BY columnname;

Example: select * from Biodata ORDER BY firstname;

Syntax2: select * from tablename ORDER BY columnname DESC;

Example: select * from Biodata ORDER BY firstname DESC;

Grouping Records, Having Clause

In SQL we can have identical data in a group. Consider a table named Exams with fields Rollno, Subject and Marks. In the below table multiple rows of records are available for different subjects. Using the GROUP BY command, the rollno’s are grouped and marks are added up against the Rollno with SUM( marks). See Table 3.25 and 3.26.

Example: Select Rollno, SUM(Marks) from Exams GROUP BY Rollno;

Joining Tables

When we have to select data from more than 2 tables SQL JOIN clause is used. Consider two tables Exams and Profile. The Exams table has the fields Rollno, Subject and Marks. The data of Profile table is shown below in Table 3.27 & 3.28.

Example: SELECT Profile.Name, Profile.

Hobby, SUM(Exams.Marks)As Total

FROM Profile, Exams

WHERE Profile.Rollno = Exams.Rollno

GROUP BY Profile.Name, Profile.Hobby;

In the Query Statement, the marks are added and listed under the column name Total for each of the Rollno from both the tables.