BASIC SQL QUERIES-
What is a query?
A query is a command/statement used to fetch some specified data from tables of any particular database.
1. Query to create a database:
For this, we need to follow the syntax-
create database database_name;
As you press enter key a database of given name will be created. For example, we have to create a database naming db;
create database db;
Hence, the database is created.
2. Query to use the database:
To use the database just created, you need to issue a command-
use db;
As the above query is executed, a statement is shown in result 'Database changed' which means that database is currently in use and further changes will be reflected in this database only.
3. Create a table:
We are creating a table naming T1 having columns ID, FirstName, LastName, Age and Salary and the datatypes are integer for numbers, char for alphabets and decimal for salary figures along with data size respectively.
The syntax to create a table is as follows-
create table T1(ID integer(10),FirstName char(20),LastName char(20),Age integer(10),Salary decimal);
Hence, the table is created and you can check by using following command:
show tables;
So, the database db has only one table i.e., T1.
4. Inserting data into table:
To insert data we need to execute the following syntax-
insert into T1 values(101,"Aman","Sharma",26,25000);
-Always remember to put char values inside " ".
Hence, the value is inserted in the table. Similarly, you can insert several other values into table.
5. Display data from table:
To display the data we use "select" command and it retrieves everything stored from a table. We need to specify asterisk in the select list-
select * from T1;
All the data entered is displayed.
6. Rename table:
To rename the existing table you need to execute the query mentioned below-
rename table t1 to employeeinfo;
Hence, the existing table T1 is renamed as employeeinfo.
7. Retrieve particular rows:
To retrieve particular rows from table based on some condition we use clauses.
What is a clause?
Any well-defined parts of SQL statements having specific condition is called a clause.
Types of SQL Clauses-
- WHERE CLAUSE- It is used to select specific rows.
For example, You can select particular rows from a table by specifying condition through where clause of the select statement.
select *from employeeinfo
where age=24;
- ORDER BY CLAUSE- It is used to sort the results of a query.
For example, to display the list of employees in the alphabetical order of their FirstName, we can use this command-
select *from employeeinfo
order by FirstName;
We can see that the FirstName of all employees is arranged alphabetically.
- GROUP BY CLAUSE- It is used to group the results of data in terms of count function of a query. Now, execute the following query-
select FirstName, count(*)
from employeeinfo
group by age;
We can see that two same ages are grouped in front of a single employee name.
- DISTINCT CLAUSE- The "distinct" keyword eliminates the duplicate rows from the results of a select statement. For example-
select distinct age from employeeinfo;
Now, you can see that it eliminates one entry of age having a similar value i.e., 24.
8. Query to describe a table:
Once you have created a table, you can view its structure by using DESCRIBE command of MySQL or in short DESC.
DESC employeeinfo;
9. Query to delete table data: If you want to delete the data of table without deleting its structure then you have to use "truncate" command of SQL. After executing the truncate command if you run the select query then it will show you empty set as all the data of existing table has been deleted.
truncate table employeeinfo;
10. Query to delete the table: To delete the structure of the table we use "DROP" command. After we delete the table, we can check by using show tables command in the current database which will show empty set as the table is already deleted.
To delete any existing table use the code-
drop table employeeinfo;
11. Query to delete the database: Lastly, if we want to delete the entire database with all its existing tables and constraints permanently, we can use "DROP" database command. If we run "show databases" command after that then the list of databases won't include the name of deleted database.
drop database db;
show databases;
We can clearly see that the list of databases have no database naming db as it has been already deleted from the system.
All these queries together comprise of basic queries of MySQL.
| 
| 
